DB2 Interview Questions

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.

DB2 is a family of data management tools developed by IBM. However, if we specifically talk about the databases, DB2 is a relational database that was released in 1989 by IBM. DB2 was written using many programming languages that include C, C++, Java, and even Assembly Language. It has an operating system that works on Linux and Windows.

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.

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.

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.

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.

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.

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.

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.

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. 

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. 

The query to delete the entire table from the database is given below:

DROP TABLE table-name

The query to delete all the rows from a table in DB2 is:

DELETE * FROM table-name

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 tablename

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.

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.

There is a total of 8 data types in DB2. They are SMALLINT, INTEGER, FLOAT, DECIMAL, CHAR, VARCHAR, DATE, and TIME.

We can achieve the record level locking in DB2 versions where the record level locking is not allowed by having the length of the record greater than that of a page itself.

Now that we have so much knowledge from the questions that we have studied, let us now move to practice some of the MCQs that will help test your knowledge too.

Useful Interview Resources

• Interview Questions on MainFrame
• Interview Questions on COBOL
• Interview Questions on SQL
• Interview Questions on DBMS
• Interview Questions on Oracle
• Interview Questions on Database Testing
• Char vs Varchar

DSNDB07 is a database where the DB2 performs its sorting operations. It includes the sort work-area of DB2 and external storage. 

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. 

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.

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.

• 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. 

In a COBOL-DB2 program, all the statements are coded in Area B.

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.

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.

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.

The maximum length of SQLCA is 136.

Yes, FORTRAN applications require more than one SQLCA.

Only one SQLCA is needed for an application that contains executable SQL statements. However, SQLCA is not applicable to Java applications.

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.

Yes, more than one cursor can be opened in the same program.