Q1,What is DB2?
DB2 is a popular Database Management System used for the MVS Operating System where the DB2 is a subsystem of the MVS Operating System.
Read: Difference Between SQLite and MySQL
Q2. What are the major components of DB2?
DB2 is an enormous relational database the board framework with numerous segments in it. The four noteworthy segments of DB2 are:
- System services component: It underpins framework tasks, other correspondence, logging, and comparative capacities. It handles startup and shutdowns.
- Locking services component: It gives the fundamental controls to overseeing simultaneous access to information. This part is known as the Internal Resource Lock Manager (IRLM), and it deals with the simultaneousness issues and gridlocks.
- Database services component: It bolsters the definition, recovery, and update of client and framework information. It is in charge of the execution of SQL explanations, and it additionally deals with the buffer pool.
- Distributed data facility component: It gives DB2's, circulated database support.
Q3. Explain the purpose of using COMMIT?
The information changes can be caused perpetual by utilizing To submit. It likewise allows information to be gotten to by different applications that can reference the submitted information.
What are the complex SQL queries?
Q4.Explain PACKAGE in DB2 and its advantages?
PACKAGE:They are units, which consists of many executable codes that are meant for SQL statements for one respective DBRM (Database request module).
Below are some advantages of Packages:
- It ensures that you do not have to bring a large collection of DBRM members together for a particular plan.
- In case of making changes in a program that will eventually lead to errors, then the decrease in fallback complexities can be made with the help of Package.
- It is advisable to go for a small collection instead of a large one. Abstain from the cost of large collection of a bind.
- In the course of automatic binding or rebinding of a specific plan, ensure that the total transactions attached to the process are unavailable.
Q5.Describe major components of DB2?
DB2 is the huge relational database management system with many components in it. The four major components of DB2 are:
- System services component: It supports system operations, other communication, logging and similar functions. It handles startup and shutdowns.
- Locking services component:It provides the necessary controls for managing concurrent access to data. This component is known as Internal Resource Lock Manager (IRLM) and it manages the concurrency issues and deadlocks.
- Database services component: It supports the definition, retrieval, and update of user and system data. It is responsible for the execution of SQL statements and it also manages the buffer pool.
- Distributed data facility component: It provides DB2’s, distributed database support.
Download Free : DB2 Interview Questions PDF
Q6.Give a brief description of DB2 Isolation Levels?
UR- Uncommitted Read:
- It is good for accessing read-only tables and read-only queries.
- No record Locking is done.
CS- Cursor Stability:
- It is a default isolation level.
- It locks or unlocks each row at a time.
- It guarantees to return only data which was committed at the time of reading.
RS- Read Stability:
- It releases Locks on rows that do not satisfy the query predicates.
- It is used for result set stability or when the future actions on returned rows may be taken.
RR- Repeatable Read:
- It allows the application to retrieve and operate on rows as many times as needed.
- It locks the entire table.
Q7.Why mainframe is call has z os?
Due to zero downtime mainframe is call has z os
Q8. What is the -204 SQL error message?
Table not found
Q9.What is the -551 error message?
Lack of access issue.
Q10.What are the utility in db2?
Image copy, load, REROG, RUSTAT.
Q11. Define what SQLCA is.
SQL Communication Area is a structure of factors, which are refreshed after each execution of SQL explanations. For an application that contains executable SQL proclamations, only one SQLCA is required. FORTRAN needs more than one SQLCA to be given. For Java, SQLCA isn't pertinent.
Q12. Name a few fields from SQLCA?
SQLCODE, SQLERRM, SQLERRD
Q13. Discuss the DB2 bind?
The procedure that assembles get to ways to the DB2 table is known as the bind. The quandary utilizes Database Request Modules from the DB2 pre-arrange venture as information and produces an application plan. It checks for client confirmation and approves SQL explanations in the DBRM(s). DB2 bind procedure does the accompanying-
- Validates:- SQL statements are approved for valid tables, view, and section names. Linguistic structure blunders are checked so that these can be remedied.
- Verifies:- Bind procedure checks that the client or the procedure is approved to bind the arrangement.
- Selects:- Selects the entranceway from the application program to the DB2 objects.
Q14.Explain various types of Locks in DB2?
The lock modes in DB2 are used to determine whether one lock is compatible to another lock.
- S Lock (Share): This mode allows the Lock owner and any concurrent process to read but not change the locked page or row.
- U Lock (Update): This mode allows the Lock owner to read but not change the locked page. This lock mode reduces the chance of deadlocks.
- X Lock (Exclusive): This mode allows the Lock owner to read or change the Locked page or row.
Q15.Mention and explain the contention situations caused by locks?
Locks play a vital role in maintaining the concurrency in the DB2 environment. Below are the contention situation caused by Locks which can degrade the DB2 performance.
- Suspension: This situation is encountered by an application when it requests the lock that is already held by another application and cannot be shared. This suspended process temporarily stops running and resumes when all the processes that hold the conflicting lock release them.
- Timeout: This situation is encountered by an application process when it terminates because of a suspension that exceeds the preset level. DB2 then terminates the process, issues message and returns the error codes.
- Deadlock: This situation occurs when two or more application processes each hold locks on resources that others need and without which they cannot proceed.
16. Explain about RCT in DB2?
RCT stands for Resource Control Table and is defined in the DB2 region. It is the component that consists of features that are gathered through macros of DSNCRCT. It matches with the transaction Id that of CICS, with the authorization Id that of DB2. It should also be matched with plan Id.
Q17. What is the -805 issue?
Q18.What is the 904 issue?
Q19. What Is the 910 issue?
Time out issue
Q20.What is the -905?
CPU asquint resource limits
Q21. What is the -911?
Q22.What is the -205?
Column name not found
Q23..What is the -924?
Db2 connection issue
Q24. What is 818?
Plan and program time stamp mismatch
Q25.What is -206?
Column does not there in select