Db2, also known as Database 2 is defined as a set of relational database products built and offered by IBM. This particular database management system helps various enterprises in creating declarative data models accessible. This is done through queries. To enable this, IBM developed the popular Structured Query Language or SQL.
Db2 elements consist of operational databases, data warehouses, data lakes, and fast data. Db2 also has advanced features with a robust security regime. As a result, it is now a popular database product in the modern software industry. Hence, db2 interview questions play an essential role in an IT job.
DB2 interview questions for Freshers
1. How to locate the rows in a Db2 table?
One can locate the rows by using the SELECT COUNT (*) on the Db2 query.
2. What is IBM Database 2?
Database 2 is defined as a subsystem that comes under the MVS system. Database 2 is a database management system (DBMS) for the MVS operating system.
3. How does one eliminate duplicate values from Db2 SELECT?
One can erase the copy-pasted values present in Db2 SELECT. This can be done by using the ‘SELECT DISTINCT’.
4. Define access path.
An access path is a path that is used in the database to reach the specified data in the SQL statements.
5. Explain the ‘Aggregate’ function.
‘Aggregate’ functions are defined as built-in mathematical tools. These are used in the Db2 SELECT clause.
6. Define an alias.
Alias is an alternate name. This name is used in the statements of SQL training in Hyderabad. This name refers to a table or view, usually present in the DB2 system.
7. Can one use MAX on a CHAR column?
Yes, one can use MAX on a CHAR column.
8. What is a plan?
A plan in Db2 is produced during the binding process. It associates one or more database request modules with a plan name.
9. Why is SELECT not generally used in Embedded SQL programs?
Here are the reasons why SELECT is not generally used in Embedded SQL programs:
Using SELECT to retrieve the column will not be of use when the table structure is changed by the addition or deletion of a field leading to the modification of the program.
Therefore, the chances of the index-only scan are also erased from the Input-Output overhead.
10. Explain the information that is used as input in the binding process.
The information used as input in the binding process is the database request module produced during pre-compile. It also uses the SYSIBM.SYSSTMT table in the Db2 catalog.
11. Explain the use of the LIKE statement.
Partial searches such as a search of the employee’s name are done using the LIKE statement. Here, partial-string matches will work. There is no need no indicate the complete name.
12. Define attachment facility.
The attachment facility is an interface. It is present between Db2 and TSO, IMS/VS, CICS or also batch address spaces. This facility allows applications to access Db2.
13. Explain how the value function is used.
The two tasks that can be performed using the ‘value’ function are as follows:
The value function helps in avoiding negative SQLCODEs. This is done by handling the NULLs and 0s in the computation.
The value function also helps in substituting numeric values in place of NULLS used in the computation.
14. What is AUTO COMMIT?
AUTO COMMIT is defined as a SPUFI option. It helps in committing the effects of SQL statements automatically provided they are successfully executed.
15. Explain UNION and UNION ALL?
UNION and UNION ALL are functions that are used in combining the results generated by various SELECT statements.
The UNION function helps in eliminating the duplicates from the table.
The UNION ALL helps in keeping the duplicates in the table.
DB2 interview questions for intermediate candidates:
16. How are the FIRSTNAME and the LASTNAME from the EMP table used to generate a full name?
The statement that is used to generate the full name from the FIRSTNAME and the LASTNAME is:
FIRSTNAME II ** II LASTNAME FROM EMP
17. Define predicate.
A predicate is defined as an element that is a part of a search condition. This expresses or implies a comparison operation.
18. Why does the MySQL statement ‘SELECT AVG (SALARY) FROM EMP’ generate inaccurate output?
The statement generates an inaccurate output because the SALARY function is not declared to have NULL values. The employees whose salary is unknown are also counted.
19. Explain the meaning of a recovery log.
A recovery log is defined as a collection of records. These records describe the sequence of events occurring in Db2. This information is necessary for the recovery of information if there is a failure during execution.
20. What is the CURSOR function?
A CURSOR is defined as a programming device. It helps the SELECT statement in finding a set of rows. But these rows are displayed one at a time. This is due to the host language being able to deal with only one row at a time.
21. What is a Resource Control Table (RCT)?
The Resource Control Table is a table that is assigned to one Db2 or cics region. It consists of characteristics. These characteristics are assembled through DSN CRCT macros. The CICS transaction ID is matched to its respective Db2 authorization ID and plan ID through the RCT matched. This is done through the CICS attachment facility.
22. Can there be more than one cursor function open for any program?
Yes, the developer can open more than one cursor function for any program.
23. Which location are the plans stored in?
Each plan is located uniquely. They are stored in the SYSTEM SYSPLANS table respective to the transactions which execute that plan.
24. What are the Remarks for the VARCHAR column?
Here is how they are defined:
10 REMARKS
USAGE COMP 49 REMARKS-LEN PIC S9(4)
49 REMARKS-TEXT PIC X(1920)
25. What is a repeatable read?
Repeatable read is defined as the action taken when an application with repeatable read protection is run. When this is done, the rows referenced by the program cannot be changed by other programs. These rows cannot be changed until the program reaches a commit point.
26. Mention the physical storage length of each data type, DATE, TIME AND TIMESTAMP.
The physical storage length of each data type is as follows:
The physical storage length of DATA is 4 bytes.
The physical storage length of TIME is 3 bytes.
The physical storage length of TIMESTAMP is 10 bytes.
27. What is a storage group (STOGROUP)?
A STOGROUP is a collection of names. This consists of DASD volumes which are used by table spaces and index spaces in databases. These volumes are required to be of the same device type.
28. Explain the meaning of DCLGEN.
DCLGEN is also known as the Declaration Generator. This is used in creating the host language copy books which are further used for the table definitions. It also creates a DECLARE table.
29. How does one use STOGROUP to transport a desk to an exclusive DASD extent reserved to that tablespace?
The statement to be used if the tablespace used is only reserved to that STOGROUP:
The user is required to create a new STOGROUP that points to the new volume. Then, the tablespace must be altered, reorganised and recovered.
Mainstream interview questions for experienced candidates:
30. Mention some of the main fields in SQLA.
Some of the main fields in SQLA are as follows:
SQLCODE
SQLERRM
SQLERRD
31. Explain how a typical Db2 batch program is executed.
The two methods available to execute a Db2 – batch program are as follows:
The developer can use the DSN utility to execute a Db2 batch program from a native TSO.
The developer can use the utility program of IKJEFT01 and run the DSN command.
32. What is the EXPLAIN function?
The EXPLAIN function is used to display the access path. This path is displayed as determined by the optimizer for SQL statements. For single SQL statements, when there is SPUFI, the function can be used.
33. How do you replicate the explain function of an embedded SQL statement in SPUFI or QMF? Provide an example of the above.
The user must add a question mark in place of a host carriable or an unknown value to simulate the EXPLAIN function of an Embedded SQL statement in SPUFU or QMF.
34. How does one perform the EXPLAIN function for a Dynamic SQL statement?
The SPUFI or QMF statements can be used to perform the EXPLAIN function for Dynamic SQL statements. Users can also include the EXPLAIN function in the Embedded Dynamic SQL statements.
35. Name the isolation levels that are possible to achieve.
The isolation levels possible are:
Cursor Stability which is denoted as CS
Repeatable Read which is denoted as RR
36. What is the difference between the two isolation levels?
The Cursor Stability or the CS releases the lock on the page after it is used.
The Repeatable Read or the RR retains all the locks till the end of the transaction.
37. When and how are the isolation levels specified?
The isolation levels are specified at the time of the BIND process. The ISOLATION LEVEL uses a parameter in the binding process.
The statement used is ISOLATION (CS)
Or
ISOLATION (RR)
38. Define Lock Escalation.
Lock escalation is known as the process of promoting page lock sizes to tables. It is also used in promoting the page lock sizes to table space lock size when the transaction has more locks than those mentioned in NUMLKTS. For the escalation to take place, the locks must be taken on objects in a single tablespace.
39. On what basis does Db2 determine the lock size to be used?
The three methods used to determine the lock size are as follows:
The lock size can be determined based on the lock size provided during the time of the creation of the tablespace.
The lock size can be determined by programming. This directs Db2 in terms of the lock size to be used.
When a lock size is specified, the Db2 uses a lock size the same as the PAGE.
Conclusion
The depth of Db2 has continued growth and there are a range of offerings in the cloud, on-site, with public cloud providers and private cloud options, and in a variety of data centers worldwide. At times, Db2 is still viewed as an old product hence overlooked for its old-school charm amongst its modern peers.
These are the common db2 interview questions asked in recruiting beginners, intermediate candidates and experienced candidates. The knowledge for these questions and answers is crucial at the time of job interviews and tests. These db2 interview questions will also help candidates while working with the system as it will enhance their skills further.
More Resources : work from home jobs for women | Trivandrum company job vacancy | engineering student resume | mechanical engineer resume sample
