Explore topic-wise InterviewSolutions in .

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.

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:

The QUERY to DELETE all the ROWS from a table in DB2 is:

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:

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.

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.