|
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:
Want to get certified in DB2. Learn from our experts and do excel in your career with HKR'S DB2 Online Training
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.
Uncommitted Read:
This is used to access the read-only queries and read-only tables.
Record locking is not done.
Cursor Stability:
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.
Read Stability:
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.
Repeatable Read:
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.
Ans. It is an SQL statement created during the program's execution. Using Dynamic SQL, DB2 develops and executes the program's SQL statements when running. It is helpful when a user doesn't know the SQL statement's format before writing or running a program.
Ans. The total length of SQLCA is 136 bytes.
Ans. DB2 contains four buffer pools:-
Ans. In DB2, ALTER is the SQL Command helpful for modifying the DB2 object's definition.
Ans. It is an index that finds the number of table rows and decides about grouping the rows within the tablespace.
Ans. The index is stored in B-tree format in DB2.
Ans. The Aggregate functions in DB2 are the built-in mathematical tools helpful in performing calculations on a set of values. It returns a single value and is primarily used in the DB2 SELECT statements.
Ans. In DB2, predicates help enhance query performance. It states the condition of the given value, row, or group, whether true, false, or unknown.
Ans. In DB2, Concurrency is the ability to simultaneously access the same data by more than one application process. However, Concurrency must be managed to prevent the lost updates' access to unrepeatable reads and uncommitted data.
Ans. In DB2, a Role is a database object that groups many privileges and can be allotted to other groups or users using a GRANT statement.
Ans. MAX() function is an aggregate function in DB2 that returns the maximum or highest value from the set of values. The MAX function returns NULL as a value if the group is empty.
Ans. The following are the different storage lengths of these data types.:-
Ans. RUNSTATS in DB2 is a DB2 utility helpful in gathering data about the table space or the index efficiency.
Ans. It is a database where sorting operations take place in DB2. Further, it includes external storage along with the sort-work area.
Ans. It is a subquery in DB2 that reconsiders while inspecting a new row or a group of rows as it carries out the outer SELECT statement. A subquery is a nested query, and it is considered a Correlated subquery when the subquery refers to a prime question for each execution.
In other words, correlated subqueries include a reference to a table outside the range of a subquery.
Conclusion:
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 4th Apr 2023, Weekday batch
Batch starts on 8th Apr 2023, Weekend batch
Batch starts on 12th Apr 2023, Weekday batch
©2023 HKR Trainings. All rights Reserved.