1. What is DB2?
DB2 is a family of data management products, including database servers, developed by IBM. It provides a reliable and efficient platform for managing and retrieving data in a structured format.
2. What are the different types of DB2 servers?
DB2 servers include the Community Edition, Express Edition, Workgroup Server Edition, and Advanced Edition, each catering to different usage scenarios and feature sets.
3. What are the different data types available in DB2?
There is a total of 8 data types in DB2. They are SMALLINT, INTEGER, FLOAT, DECIMAL, CHAR, VARCHAR, DATE, and TIME.
4. What is the difference between SMALLINT and INTEGER?
The difference is in the range and precision. While the SMALLINT can store the numbers up to a precision of 15 bits with a range of -32768 to +32767, the INTEGER data type can store the numbers up to a precision of 31 bits having the range from -2,147,483,648 to +2147483648. There is also one more data type to store the integer type of data. It is called BIGINT and its range is even higher than INTEGER.
5. What is the difference between CHAR and VARCHAR?
As the name suggests, the main difference is that CHAR is of fixed length whereas VARCHAR is of variable length. This means that char always has a fixed length to store the text whereas the VARCHAR adjusts its length according to the length of the text and this helps in saving the memory. Also, the CHAR data type can have a maximum size of 254 bytes whereas VARCHAR can have a maximum size of 4046 bytes.
6. Write a Query to find the number of rows in a DB2 table.
Here, the interviewer might ask you to write down the query on a piece of paper or a google doc or any other method. Mostly, the interviewers do not ask you to write a query upon a pre-existing table.
The query is:
SELECT COUNT * FROM tablename7. Write a query to delete all the rows from a table in DB2.
The query to delete all the rows from a table in DB2 is:
DELETE * FROM table-name8. Write a query to delete the entire table from the database in DB2.
The query to delete the entire table from the database is given below:
DROP TABLE table-name9. What is the difference between the above two queries i.e. DELETE and DROP?
The DELETE * FROM table-name query deletes all the rows from the table but, there is still an empty table with no rows and no columns in the database. However, the DROP TABLE table-name query deletes all the rows and columns and along with that, the table gets deleted from the database too i.e. the table is completely removed (deleted) from the database.
10. What are UNION and UNION ALL in DB2? How do you differentiate between them?
The union command is used to merge 2 or more SELECT statements and the select statements can be applied on a single table as well as on multiple tables. The major difference between UNION and UNION ALL is that UNION removes the duplicate rows when it is applied on the tables whereas UNION ALL retains the duplicates.
11. What is concurrency in terms of DB2?
Concurrency means that more than one application process of DB2 can access the data at the same time. Concurrency can cause some issues like lost updates, access to unrepeatable reads and uncommitted data.
12. What is a CURSOR and What is the use of it?
CURSOR is a programming device that is used to find a set of rows corresponding to a SELECT statement. However, the rows are displayed one at a time.
13. What is MAX in DB2? Is it possible to use MAX in a CHAR column?
MAX() is an aggregate function that returns the maximum value from a set of values. For example, if we have a database of movies, we can apply MAX(rating) on the rating attribute and it will select the rows in which we have the movies with the highest ratings. Yes, MAX can be used on a CHAR column.
14. How can you move a tablespace to a different DASD volume allocated to that tablespace if you have to use STOGROUP?
The method to move the tablespace to a different DASD volume allocated to that tablespace is:
ALTER STOGROUP - ADD VOLUME (new volume) DELETE VOLUME (old volume)
REORG TABLESPACE or RECOVER TABLESPACE
So, basically, the method is that we create a new STOGROUP that points to the new volume and ALTER the tablespace and REORG or RECOVER the tablespace.
15. What are the two levels of isolation and what is the major difference between the 2 levels?
The two levels of isolation are Cursor Stability (CS) and Repeatable Read (RR). The major difference between these 2 levels is that the Cursor Stability (CS) releases the lock on a page after the use whereas the Repeatable Read (RR) retains all the locks till the end of a transaction.
16. As you know there are different sizes of locks. Can you tell how DB2 decides which lock size should be used?
The lock size is determined by three methods. These methods are:
- The lock size can be decided based on the lock size given while creating a new TABLESPACE.
- Also, Programming directs DB2 about the lock size.
- If the lock size ANY is specified, then DB2 (in most of the cases) chooses the lock size of PAGE.
17. What is DCLGEN in DB2?
DCLGEN stands for Declarations generator. The programs that we write should declare the tables and views that it accesses. The declarations generator i.e. DCLGEN produces these DECLARE statements for C, Cobol and PL/I programs so that one does not need to code the statements manually. It also generates corresponding host variable structures. When DCLGEN is used to generate a table declaration, the DB2 uses the DB2 catalog to get the relevant information from it. DCLGEN, then uses this information to create an SQL DECLARE TABLE statement for the table/view.
18. How is INNER JOIN different from simply writing a JOIN?
If we do not specify the type of join, by default, the join is an INNER JOIN. So, there is no difference between writing INNER JOIN or simply writing JOIN.
Now that we have seen some of the most popular interview questions of DB2 for freshers, let us move to some intermediate and advanced questions too that are asked in the interviews from an experienced candidate.
19. Can more than one cursor be opened in a program?
Yes, more than one cursor can be opened in the same program.
20. What is SQLCA?
The full form of SQLCA is the SQL communication area or Structured Query Language Communication Area. It is a structure of variables that is updated after every execution of an SQL statement.
21. How many SQLCA must be provided to an application that contains SQL statements (executable)?
Only one SQLCA is needed for an application that contains executable SQL statements. However, SQLCA is not applicable to Java applications.
22. Do you know any programming language where more than one SQLCA is used?
Yes, FORTRAN applications require more than one SQLCA.
23. What is the maximum length of SQLCA?
The maximum length of SQLCA is 136.
24. Is it possible for you to alter the table (adding a column to it) while some other person is accessing the table and even updating some values in it?
To be short and crisp, the answer is yes, we can alter the table at the same time when some other transaction is taking place but this does not exactly happen as it sounds to be.
The DB2 table will not change its structure in any form till a transaction or multiple transactions are taking place. The table-altering commands will be stored by the database engine and will not be executed till all the transactions are complete. After one user has modified the values in the table, then the column will be added.
25. Let us say that we have a Student Database in which we have the details of the students like their names, address, attendance percentage, etc. Now we run an SQL Query that says: SELECT AVG ATTENDANCE FROM STUDENTS.
Can you tell me any issues that may be here?
Yes, there might be an issue. For instance, if there is such a case where we have set that the attendance of any student cannot be null and at the same time we have some students in the table whose attendance we don’t know, the AVG function will calculate these students as well. Hence, we will get an answer but, it might not be correct.
26. What are ACQUIRE and RELEASE in BIND?
A certain point in a program when DB2 acquires or releases the locks against the tables and/or tablespaces. These include the intent locks as well.
27. In a COBOL-DB2 program, where are all the SQL statements coded?
In a COBOL-DB2 program, all the statements are coded in Area B.
28. What are SPUFI and QMF? What is the difference between them?
- SPUFI: SPUFI is an execution engine for SQL where the SQL commands are executed. Several SQL queries can be executed at once and the result of the queries can be stored in PS or PDS members. Also, the SQL is known to us even after the execution of the query.
- QMF: It is a reporting environment and supports the formatting of reports. More than one query cannot be executed and the result of the query cannot be persisted. Also, the SQL code of the query cannot be known.
29. Let us say that I have a program it uses dynamic SQL. It has been working fine till now, however, lately, I have found that the performance has been affected and deteriorated. Can you guess what can be the reason behind this?
There can be 2 possibilities:
- RUN STATS is not done and it seems that the program is using an incorrect/wrong index due to incorrect/wrong stats.
- It might also be the case that the RUN STATS has been done but the optimizer has chosen a wrong access path based on the latest statistics.
30. What is the RUN STATS that was mentioned in the previous answer?
RUN STATS is a DB2 utility used to collect statistics about the data value in TABLES. It is used by the optimizer to decide the access path. RUN STATS also collects statistics that are used for space management. These statistics are stored in the DB2 catalog tables.
Usually, the RUN STATS is run after the following:
- After a load.
- After a mass update.
- After any major deletions, insertions, or
- After REORG the table.
31. Is there any specific reason why SELECT is not preferred in embedded SQL programs?
The SELECT statement is not preferred in embedded SQL programs for 3 reasons:
- If there is a change in the structure of the table by addition or deletion of a field, the program gets modified and using the SELECT statement might retrieve those columns that the user may not even use.
- The use of SELECT in the embedded SQL programs can lead t the input-output overhead and,
- The chances of an index-only scan are eliminated by the use of SELECT in embedded SQL programs.
32. What is DSNDB07? What does it do?
DSNDB07 is a database where the DB2 performs its sorting operations. It includes the sort work-area of DB2 and external storage.
33. Code Snippet: Select Query
SELECT column1, column2
FROM table_name
WHERE condition;