20 DB2 Interview Questions and Answers

How to execute stored procedures?

The following is the process to execute the stored procedures:
- From the command line type- Type the entire script. After completion of entering the script save the script
- Execute the script by invoking the CALL command from Command editor:

What is SQLCA?

- SQLCA stands for SQL Communication Area.
- SQLCA is a structure of variables which are updated after every SQL statement’s execution.
- Exactly only one SQLCA need to be provided to an application that contains executable SQL statements.
- SQLCA is not applicable to JAVA application.
- More than one SQLCA need to be provided for FORTRAN application.

What is the max length of SQLCA?

- The maximum length of SQLCA is 136.

What does SQLCABC has?

- SQLCABC is one of the fields available in SQLCA.
- It is an INTEGER type.
- It contains the length of the SQLCA.

Is it possible to alter a table – for example adding a column, when another user is accessing or updating some columns?

- It is possible to alter a table.
- The DB2 tables will not re-structure until any transaction is committed.
- A new column is defined and identified by the database.
- The new column will be included only after the transactions of the table are committed.
- All the altering table statements will be received from other users and are stored.
- The commit status is updated by the database engine and then the new columns are added.

How and when does the Db2 enforces the unique key?

- DB2 utilizes unique index to avoid identical key values storage in database.
- A unique index must be created whenever a table is to be created with primary key.
- DB2 marks the table as unavailable until the needed indexes are created explicitly.
- DB2 prevents the duplicate entities into the table that has unique key.
- A unique key is declared by using the UNIQUE clause of CREATE TABLE command.
- DB2 enforces the unique index when INSERT,UPDATE statements are applied to the table.
- DB2 also enforces the unique index during the LOAD utility execution.

What is CHECK constraint. Explain with example.

- A CHECK constraint is one of the constraints to ensure data integrity
- CHECK constraint is specified as a condition / criteria
- The value that is to be inserted or updated in a table, need to be tested by the CHECK constraint
- If the value is meeting the criteria, then the data is inserted or updated in the designated column of a table
- CHECK constraint is used at the time of creation of a table
- The following example illustrates the use of CHECK constraint:

What are the advantages of using a PACKAGE?

- Packages are the alternative to creating procedures and functions.
- They are stand-alone schema objects.
The advantages of packages are:
1. Modularity:
All the procedures and functions belongs to a particular module of an application can be encapsulated in a package.

2. Easy to design the application:
The package body and package specification can be coded and compiled separately without its body. Stored procedures are compiled and executed by qualifying the package names.

3. Hiding Information:
The programming elements declared in the specification of the package is public. The package body elements are declared as private, i.e., they are invisible to the application. Hence they are secured.

4. Added Functionality:
Public variables and cursors are persisted for a session, which enables the sharing of data by all programs of the calling environment.

5. Better Performance:
Packaged sub programs are invoked for the first time by loading the entire package into the memory. Disk I/O operations are not needed for calling related sub programs in the package later.

6. Overloading:
Multiple programs can be placed in the same package with different number of parameters or types or parameters.

What is DBRM? What it contains? When it will be created?

- DBRM stands for Database Request Module.
- The output of pre-compile process is represented as DBRM.
- The SQL statements are extracted from the host language by the pre-compiler.

What it contains?
- It contains executable host SQL statements.
- During the pre-compilation process, the SQL code is embedded in the COBOL and will be extracted and moved into DBRM.

When it will be created?
- The DBRM is created after pre-compilation of SQL statements.

What are sqlcodes –803,-805, -811, -818,-904,-911,-913,-101, +100?

-803: INSERT or UPDATE operations are performed in constrained UNIQUE INDEX columns with certain unique values.

-805: An attempt to the application program that uses a DBRM or a package. The location-name, collection-id, dbrm-name, consistency-token are not found.

-811: An embedded select statement’s result is a table with more than one row or the result of subquery’s predicate is exceeding one value.

-818: Variation of consistency token in DBRM and the load modules are mismatching / different.

-904: The resource is unavailable for the execution of SQL code.

-911: The current transaction has been rolled out because of deadlock or time out.

-913: Execution is unsuccessful due to deadlock or timeout.

-101: Complicated or long SQL or long SQL query. Need to rephrase the query.

+100: Row was not found for a fetch. Update/delete/query is resulting an empty table.

What do you mean by NOT NULL WITH DEFAULT? When will you use it?

- NOT NULL WITH DEFAULT is a clause of CREATE INDEX.
- It is ensured that the uniqueness of the key column, that is defined with index. But allows the NULL values in the column.
- It indicates that the column could not contain a NULL, when a user does not enter any value. DB2 generates the default value.
- It ensures that no null values are persisted.
- NOT NULL WITH DEFAULT is used when there must be a value in the columns. For example every product should have product name, every employee should have employee name.

Are view updateable?

- Certain views are updateable.
- A single table view can be updatable.
- Views with joins, aggregate functions, having GROUP BY clause are non-updateable views.

What is COPY PENDING status?

- A status occurs when image copy on a table needs to be taken.
- The table is available only for queries and can not be updated.
- COPY PENDING status can be removed by taking away the image copy or by using REPAIR utility.

What is REORG? When is it used?

- Data reorganization on physical storage is done by REORG.
- It is used for reclaiming the space by restoring the free space.
- Rows can be clustered using REORG.
- The overblown rows can be positioned in their proper sequence.
- REORG is advantageous to be used after heavy updates, inserts and delete operations.
- Useful followed by segments of a segmented table spaces.

How is a typical DB2 batch program executed?

- DSN utility can be used to run a DB2 batch program from native TSO.
- The following example illustrates the execution of a batch program in DB2:- To run the DSN command in JCL, use IKJEFT01 utility program.

Is DECLARE TABLE in DCLGEN necessary? Why it used?

- Declaration of table in DCLGEN is not necessary.
- DECLARE TABLE in DCLGEN is necessary to validate the table-name, view-name, column-name … during pre-compilation by the pre-compiler.

How do you leave the cursor open after issuing a COMMIT? (For DB2 2.3 or above only)

- Cursor can be left open after issuing a COMMIT.
- By using WITH HOLD option in the DECLARE CURSOR statement.
- The pseudo-conversational CICS programs will not be effected.

What does it mean if the null indicator has -1,0,2?

- -1 indicates the field is null.
- 0 indicates the field is not null.
- 2 indicates the field is truncated.

What is the difference between Cursor Stability and Repeatable Read isolation levels?

CURSOR STABILITY:
- CS is a row level locking.
- Acquires an exclusive lock on the row that is to be updated.
- When the control is moved to the next updatable row, the lock is released.

REPEATABLE READ:
- RR is a page level locking.
- Acquires an exclusive lock on the entire page which is the source of row availability.
- When the control is moved to the next updatable page the lock is released.

What is the difference between SPUFI and QMF?

SPUFI:
- Several queries can be executed at once.
- The result of the query will be stored in PS or in PDS member.
- SQL Code is known after the executing the query.
- SPUFI is a quick and dirty SQL execution engine.

QMF:
- More than one query can not be executed.
- The result of the query can not be persisted.
- SQL Code of the query can not be known.
- QMF is a query or reporting environment and supports formatting of reports.