1) What is DB2? Explain.
Db2 which is also called IBM Db2 is an RDBMS that complies with SQL and has been developed by IBM. It is used where data have to be stored, analyzed and accessed in a very efficient manner.
https://techzone360.shop/db2-interview-questions-2/2) Which components manage deadlocks in DB2?
The “Internal Resource Lock Manager” (IRLM) component manages deadlocks and concurrency issues by providing locking services.
3) In DB2 context, how can the locks be classified?
DB2 related locks can be categorized under size, duration, and mode.
4) How many types of page locks are allowed to be held in DB2?
Three types of page locks can be held in DB2:
Exclusive
Update
Share
5) On which levels locks can be applied?
Locks can be applied at the page, table, or table space levels.
6) What is COMMIT in DB2 used for?
COMMIT is used to make the changes in a transaction permanent, effectively committing the changes to the database.
7) What are the multiple types of data stored in DB2?
DB2 supports the following data types:
SMALLINT
INTEGER
FLOAT
DECIMAL
CHAR
VARCHAR
DATE
TIME
8) What is the picture clause of Null indicator variable?
The picture clause that goes with a null indicator variable is S9(4)COMP.
9) What is the use of DB2 Optimizer?
The DB2 Optimizer is the one to execute SQL statements and it can be used to enhance SQL performance.
10) Which of these components are used to run SQL statements?
The Database Services component executes SQL statement and manages the actual buffer pool.
11) Which component does DB2 startup and shutdown?
The System Services component handles the startup and shutdown of DB2.
12) What is SQLCA?
SQLCA or SQL Communication Area, is set of variables that become updated after the execution of each and every SQL statement.
13) What is the maximum length of SQLCA?
The SQLCA has a maximum length of 136.
14) Give the name of some fields form SQLCA.
Common fields in SQLCA include:
SQLCODE
SQLERRM
SQLERRD
15) What is CHECK constraint in DB2?
CHECK constraint is very important in data integrity because it restricts specific value that can be entered in the column
16) What is DB2 Bind?
DB2 Bind is the mechanism that configures an access path to the data, and codes this path into a package as a part of the DB2 catalog.
17) What is DBRM?
DBRM (Database Request Module) is created by the DB2 precompiler and contains SQL source statements extracted from an application program.
18) What is buffer Pool?
A Buffer pool is a memory area in the database main memory that is designed and allocated by the DBMS to cache table and index information from the disk.
19) What is data manager?
The Data Manager is actually a component of DB2 that is actually used for handling the physical database and it is used to log and lock.
20) What do you mean by storage group (STOGROUP)?
A Storage Group is a defined storage path where data is stored, and a table space can be assigned to a storage group.
21) What is the use of predicate?
Predicates are used in order to improve the performance of a query because they allow for better data filtering.
22) What is the physical storage length of TIME data type?
The TIME data type uses 3 bytes of physical storage.
23) What is the physical length of the DATE data type for storage?
DATE data type consumes 4 bytes of physically storage space in the database.
24) What is length of physical storage for the TIMESTAMP data type?
The TIMESTAMP data type requires 10 bytes, and its default format is YYYY-MM-DD: HH:MM:SS-NNNNNN.
25) What is DCLGEN (Declaration Generator)?
DCLGEN is a utility that generates SQL Declare Table statements and host language declarations for tables or views.
26) What is the usual page size of the buffer pools?
The default page size for buffer pools is 4KB, with predefined pools like BP0, BP1, BP2, and BP32.
27) Which component processes SQL statements and selects access paths?
The DB2 Optimizer determines access path and process query statements of SQL.
28) What is concurrency in DB2?
Concurrency is the capacity that allows several applications to try to update a given file or database at the same time.
29) Which isolation level give maximum concurrency?
Uncommitted read provides maximum concurrency.
30) Which isolation level provides highest data integrity?
Repeatable Read ensures the highest data integrity by locking rows until a COMMIT is issued.
31) What is RCT?
The Resource Control Table (RCT) controls the CICS DB2 interface and which can be created using DSNCRCT.
32) What happens when a program aborts in the middle of a transaction?
DB2 performs an automatic rollback when a program aborts during a transaction.
33) How can you get number of rows in a table?
By applying the following query:
SELECT COUNT(*) FROM TAB
34) In which section of a COBOL-DB2 program can a cursor be identified?
A cursor can be defined either in the Working Storage Section or in the Procedure Division.
35) What is the maximum size of a CHAR data type in DB2?
The maximum size limit of a CHAR data type it is 254 bytes.
36) What is the maximum size of VARCHAR Data type allowed in DB2?
The maximum size of VARCHAR data type cannot exceed 4046 bytes.
37) What is SPUFI?
SPUFI stands for SQL Processor Using File Input.
38) What is role in DB2?
In DB2, a role is actually an object that cluster privileges; they are permitted to users and groups by the GRANT statement.
39) What is cursor stability in DB2?
Cursor stability guarantees that once the activation group opens a row with a different commitment definition from other users, the row cannot be read until committed.
DB2 Interview Questions
A list of top frequently asked DB2 interview questions and answers are given below.
1) What is DB2? Explain.
Db2 which is also called IBM Db2 is an RDBMS that complies with SQL and has been developed by IBM. It is used where data have to be stored, analyzed and accessed in a very efficient manner.
2) Which components manage deadlocks in DB2?
The “Internal Resource Lock Manager” (IRLM) component manages deadlocks and concurrency issues by providing locking services.
3) In DB2 context, how can the locks be classified?
DB2 related locks can be categorized under size, duration, and mode.
4) How many types of page locks are allowed to be held in DB2?
Three types of page locks can be held in DB2:
Exclusive
Update
Share
5) On which levels locks can be applied?
Locks can be applied at the page, table, or table space levels.
6) What is COMMIT in DB2 used for?
COMMIT is used to make the changes in a transaction permanent, effectively committing the changes to the database.
7) What are the multiple types of data stored in DB2?
DB2 supports the following data types:
SMALLINT
INTEGER
FLOAT
DECIMAL
CHAR
VARCHAR
DATE
TIME
8) What is the picture clause of Null indicator variable?
The picture clause that goes with a null indicator variable is S9(4)COMP.
9) What is the use of DB2 Optimizer?
The DB2 Optimizer is the one to execute SQL statements and it can be used to enhance SQL performance.
10) Which of these components are used to run SQL statements?
The Database Services component executes SQL statement and manages the actual buffer pool.
11) Which component does DB2 startup and shutdown?
The System Services component handles the startup and shutdown of DB2.
12) What is SQLCA?
SQLCA or SQL Communication Area, is set of variables that become updated after the execution of each and every SQL statement.
13) What is the maximum length of SQLCA?
The SQLCA has a maximum length of 136.
14) Give the name of some fields form SQLCA.
Common fields in SQLCA include:
SQLCODE
SQLERRM
SQLERRD
15) What is CHECK constraint in DB2?
CHECK constraint is very important in data integrity because it restricts specific value that can be entered in the column
16) What is DB2 Bind?
DB2 Bind is the mechanism that configures an access path to the data, and codes this path into a package as a part of the DB2 catalog.
17) What is DBRM?
DBRM (Database Request Module) is created by the DB2 precompiler and contains SQL source statements extracted from an application program.
18) What is buffer Pool?
A Buffer pool is a memory area in the database main memory that is designed and allocated by the DBMS to cache table and index information from the disk.
19) What is data manager?
The Data Manager is actually a component of DB2 that is actually used for handling the physical database and it is used to log and lock.
20) What do you mean by storage group (STOGROUP)?
A Storage Group is a defined storage path where data is stored, and a table space can be assigned to a storage group.
21) What is the use of predicate?
Predicates are used in order to improve the performance of a query because they allow for better data filtering.
22) What is the physical storage length of TIME data type?
The TIME data type uses 3 bytes of physical storage.
23) What is the physical length of the DATE data type for storage?
DATE data type consumes 4 bytes of physically storage space in the database.
24) What is length of physical storage for the TIMESTAMP data type?
The TIMESTAMP data type requires 10 bytes, and its default format is YYYY-MM-DD: HH:MM:SS-NNNNNN.
25) What is DCLGEN (Declaration Generator)?
DCLGEN is a utility that generates SQL Declare Table statements and host language declarations for tables or views.
26) What is the usual page size of the buffer pools?
The default page size for buffer pools is 4KB, with predefined pools like BP0, BP1, BP2, and BP32.
27) Which component processes SQL statements and selects access paths?
The DB2 Optimizer determines access path and process query statements of SQL.
28) What is concurrency in DB2?
Concurrency is the capacity that allows several applications to try to update a given file or database at the same time.
29) Which isolation level give maximum concurrency?
Uncommitted read provides maximum concurrency.
30) Which isolation level provides highest data integrity?
Repeatable Read ensures the highest data integrity by locking rows until a COMMIT is issued.
31) What is RCT?
The Resource Control Table (RCT) controls the CICS DB2 interface and which can be created using DSNCRCT.
32) What happens when a program aborts in the middle of a transaction?
DB2 performs an automatic rollback when a program aborts during a transaction.
33) How can you get number of rows in a table?
By applying the following query:
SELECT COUNT(*) FROM TAB
34) In which section of a COBOL-DB2 program can a cursor be identified?
A cursor can be defined either in the Working Storage Section or in the Procedure Division.
35) What is the maximum size of a CHAR data type in DB2?
The maximum size limit of a CHAR data type it is 254 bytes.
36) What is the maximum size of VARCHAR Data type allowed in DB2?
The maximum size of VARCHAR data type cannot exceed 4046 bytes.
37) What is SPUFI?
SPUFI stands for SQL Processor Using File Input.
38) What is role in DB2?
In DB2, a role is actually an object that cluster privileges; they are permitted to users and groups by the GRANT statement.
39) What is cursor stability in DB2?
Cursor stability guarantees that once the activation group opens a row with a different commitment definition from other users, the row cannot be read until committed.
Leave a Reply