Excel in your career with our DB2 interview questions. In this blog, our experts have gathered and listed out the most frequently asked DB2 interview questions that you may come across in the interview. Reading these questions may give you a better understanding and knowledge of DB2. Without further delay, let's start with the most frequently asked questions in the DB2 interview.
DB2 is a database management product which offers advanced data management as well as the analytical capability to manage transactions. DB2 is used for providing actionable information, providing reliability, high performance, and data availability using Unix, Linux, and Windows operating systems.
Data types used in DB2 are as follows:
DB2 is the enormous relational database management system with a number of components. There are four main components of DB2:
System Service Component: It is responsible for system operations, logging, other communications, and similar functions. It manages startup and shutdowns.
Locking Services Component: It offers the controls required to manage simultaneous access to data. It is referred to as the Internal Resource Lock Manager (IRLM), and it handles the deadlocks and concurrency issues.
Database services component: It supports defining, retrieving, and updating user and system data. It is used for executing SQL statements and handles the buffer pool as well.
Distributed data facility component: If offers DB2's distributed database support
SPUFI stands for SQL Processing Using File Input. It enables the direct input of the SQL commands in the time-sharing option environment.
DCLGEN refers to DeCLarations GENerator. It is used for creating the host language copy books for table definitions. It creates DECLARE table.
Locks are essential to the maintenance of concurrency in the DB2 environment. Following are the contention situations caused by locks that may degrade the performance of the DB2.
Suspension: This is the situation that arose by the application when the application requests lock, which is already held by other applications and could not be shared. This suspended process pauses temporarily and will resume when all processes with the conflicting Lock release them.
Timeout: This is the situation that occurs when the application process is terminated due to a suspension that exceeds the predetermined level. DB2 will end the process, send a message and return the error codes.
Deadlock: This is the situation that occurs when two or more application processes hold locks on resources that are required by others without which they may not proceed further.
COMMIT is used to permanently modify data by committing that database changes occur within the current transaction and that permanent changes occur.
DB2 Optimizer is used for processing of the SQL statement. DB2 Optimizer enables the improvement of SQL performance.
This is used to access the read-only queries and read-only tables.
Record locking is not done.
This is the default isolation level.
It locks or unlocks every row simultaneously.
It ensures that only the data committed at the time of reading is returned.
It releases locks on the rows which do not meet the query predicates.
It is used for the result set stability or when future auctions on the returned row can be done.
It enables the application to fetch and run on the rows often as per the requirement.
It is used to lock the entire table.
UNION: This function is used for eliminating the duplicate values from the table.
UNION ALL: This function is used for retaining the duplicate values in the table.
Both these functions are used for combining results from more than one SELECT statement.
Lock modes within DB2 are used for determining if a lock is compatible with another lock.
S Lock: S indicates share. This mode enables the lock owner and any simultaneous process to read but not edit the locked page or the row.
U Lock: U indicates Update. It allows the owner of the Lock to read but not edit the locked page. It decreases the risk of deadlocks.
X Lock: X indicates Exclusive. It allows the owner of the Lock to read or edit the Locked page or the row.
Check constraint: It is used for inserting and updating the table that guarantees the integrity of the data by considering which values are allowed.
Buffer pool: The buffer pool meets the need to buffer one or more indexes or spaces of the table. It may be created by 4K or 32K pages.
RCT refers to the Resource Control Table. It is defined within the DB2 region. It consists of the features that are gathered by DSNCRCT macros. It will match with the CICS transaction Id with the DB2's authorization Id. It must also be matched with the plan ID.
SQL Communication Area is the structure of variables that are updated each time SQL queries are executed. If the application includes executable SQL statements, then only one SQLCA is required. FORTRAN requires more than one SQLCA. In Java, SQLCA cannot be used.
EXEC SQL DECLARE TABLE statement that provides the layout of the table according to the DB2 data type. Host language copybook, which provides definitions of the host variable to the column name.
The maximum size for any CHAR data type within DB2 is 254 bytes.
The maximum size for any VARCHAR data type within DB2 is 4046 bytes.
These are the units that are made up of many executable codes which are intended for the SQL statements for a respective Database Request Module.
The following are some of the benefits of the packages:
A collection is the user-defined name which functions as a package anchor but is not physically present. It is used to group packages.
There are two tasks that can be done using value:
COPY PENDING status refers to an image copy to be taken still on hold, and thus until this calendar may not be updated. In order to remove this status, you can obtain the copy of the image. The REPAIR command removes the status of COPY PENDING.
A CURSOR is the programming device which assists the SELECT statement to find a set of rows but shows them simultaneously. That's because the host language can only handle one row at a time.
Like statement: The purpose of the LIKE statement is to perform partial searches, like searching for an employee by their name.
OPEN CURSOR statement: In an OPEN CURSOR statement, the CURSOR will be placed on the table's first row. However, when we add the ORDER BY clause, the rows are retrieved, available, and sorted to the FETCH statement.
A recovery log is responsible for identifying any changes made within the database. A recovery log may restore the sequence of events which occurred prior to the failure when a system failure occurred.
The simple tablespace and the segmented tablespace can be used for accomodating multiple tables. Records are kept in one single statement within segments contained in pages of the segmented tablespaces.
STOGROUP: STOGROUP must be used for index spaces and tablespaces in the database, otherwise known as the DASD volume collection.
DBRM: DBRM refers to Database Request Module. It is the component of DB2 that is created by DB2's pre-compiler. DBRMs form inputs which are useful in the binding process.
PLAN: PLAN is the result of BIND process. It has executable code for the SQL statements in the DBRM.
AUTO COMMIT is an option of SPUFI which automatically commits the SQL statements effects if they run successfully.
Bind is a process which creates paths for DB2 tables. It checks the authority of the user and validates the SQL statement. A bind will use the database request modules of the DB2 precompiling step as the input and generates an application plan.
Lock escalation is a process to promote page lock sizes for the table space or table lock sizes when the transaction acquires many locks than the specified ones in NUMLKTS. The Locks should be taken over objects in a single tablespace for the escalations to occur.
A point in a program where DB2 may acquire or release locks against the tables and tablespaces. It includes intent locks.
EXPLAIN is mainly used to show the access path by the optimizer for a SQL statement. Moreover, EXPLAIN may be used in SPUFI or even at the BIND step. Prior to issuing the EXPLAIN statement, we must ensure that PLAN_TABLE has been created already in AUTHID.
Buffer Manager: It is a component of DB2 that is used to transfer data between an external medium and the storage physically. It is responsible for minimizing the amount of physical input or the output that is actually performed with sophisticated buffering techniques like look-aside buffering and read-ahead buffering.
Data Manager: It is a component of DB2 that is used to manage the physical database. It does logging and locking by using other components of the system.
Following are three reasons why SELECT * is not used in embedded SQL programs:
If we modify the table structure, i.e., to add a field, it will be necessary to change the program.
The program can fetch columns that it may not use, that leads to an I/O overhead.
The possibility of the index-only scan is lost.
All the above are some of the frequently asked DB2 Interview Questions. I hope these questions and answers will help you to clear your interview related to DB2. If you could not find the answer to any question related to cyber security, feel free to comment in the comment section. We will get back to you with the answer.
Batch starts on 7th Jul 2022, Weekday batch
Batch starts on 11th Jul 2022, Weekday batch
Batch starts on 15th Jul 2022, Fast Track batch