IBM DB2 Interview Questions And Answers

Bind parameters are:
MEMBER - In bind package,
LIRARY - DBRM library name ,
ACTION(add/replace)- package or plan can be add or replace.
ISOLATION - Determines the duration of the page lock.
AQUIRE - Lock a tableon use
RELEASE - releases when the plan terminates
VALIDATE - It will be check about authorization.
EXPLAIN - loads the access path selected by the optimizer
in table

Updating a column: You can update columns in the rows that
you retrieve. Updating a row after you use a cursor to
retrieve it is called a positioned update. If you intend to
perform any positioned updates on the identified table,
include the FOR UPDATE clause. The FOR UPDATE clause has
two forms:
• The first form is FOR UPDATE OF column-list. Use
this form when you know in advance which columns you need
to update.
• The second form is FOR UPDATE, with no column list.
Use this form when you might use the cursor to update any
of the columns of the table.
For example, you can use this cursor to update only the
SALARY column of the employee table:

EXEC SQL
DECLARE C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
FROM DSN8810.EMP X
WHERE EXISTS
(SELECT *
FROM DSN8810.PROJ Y
WHERE X.EMPNO=Y.RESPEMP
AND Y.PROJNO=:GOODPROJ)
FOR UPDATE OF SALARY;

If you might use the cursor to update any column of the
employee table, define the cursor like this:

EXEC SQL
DECLARE C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
FROM DSN8810.EMP X
WHERE EXISTS
(SELECT *
FROM DSN8810.PROJ Y
WHERE X.EMPNO=Y.RESPEMP
AND Y.PROJNO=:GOODPROJ)
FOR UPDATE;

DB2 must do more processing when you use the FOR UPDATE
clause without a column list than when you use the FOR
UPDATE clause with a column list.

There is no difference. But it is always better to declare
Cursor in Working-Storage Section because you will not code
Open Cursor before Declare Cursor by mistake. It is just a
standard to declare Cursor in WSS. As best practice to
avoid oversight.

when u bind , 5 queries has 5 sql statements in 1 DBRM, its
regroup into 1 package, 1 plan...Plan is a collection of
packages..
correct me if i'm wrong..

You should pass CHECKpoint frequency value from JCL to
cobol program.Intern cobol program will have the table of
retart logic.
Table contents(coloumns)be: 1.No of records ,2.No of
records + 1, 3.no of records processed etc.
Once the updattion or insertion got stucked while
processing ,All the relative data will be stored the above
mentioned table.

So check the record from table .Fix the abend and restart
your job for the failed step.

This is mainly production support work .manually u have to
check the record .and get the records info from the table
and restart the job from the failed step

Arrays are not supported by sql so trying to add an array in
a where clause of sql may throw some errors...
only dot operators are possibly used.

Thru SPUFI/QMF/FileAID you can insert bulk records by
selecting the records from one table and inserting into
other one.

select * from sysibm.syscolumns where name = <name you 3
columns here>

The above query to metadata will show you the list of table
names where these 3 columsn present

when we are tying to update a table having 100 rows.
if the program abends when updating 51 row . how to start
updating again from the 51 row .
what was the logic


Ans: The Possible answer would be..if you had used COMMIT
before 51st ROW .. the Former records
would have been updated in the table .. If No COMMIt was
used.. The whole transaction would have been
ROLLBACKED.

Now If you want to start a fresh Transaction and want to
start Updating directly from 51st Row
Then There are two ways

1> Perform a loop to scroll till u have read 50 rows
Then Point ur cursor as CURRENT to the 51st Row
Start Updating the Records Till end of table.

or

2> Declare a Scrollable cursor & use FETCH ABSOLUTE option
to fetch a particular row directly


EXEC SQL FETCH ABSOLUTE +51 C1
INTO :TEMP1, :TEMP2, :TEMP3;

using select in embedded sql with where clause should fetch
only one row , but cursor can be used when we need more rows
to be retrieved one at a time.

In case more than one row is retrieved in a select clause it
will throw -811 sql error.

The first thing is Host languages.
Whatever we are using in cobol (other than cobol langauge
command) that are called HOST language.

DB2 is also one of the host language.
COBOL compiler does not know the host language and does not
compile the same.
we will take cobol-db2 program..
Here, we are introduce PRE-COMPILER....
Pre-compiler will spilt the cobol db2 program into two
module.
1. Cobol program (fully cobol,all the host language
commands will get replaced with "MOVE and CALL" statements.
2. DBRM (DataBast Request Module)- only those commands,
which are code within 'EXEC SQL .. END-EXEC.

Now, we have spited and we have separate for each..(COBOL
and DB2)..
We know about compilation process for COBOL.
Now come to BIND process....
Bind is nothing but, compilation process of DBRM.
The output of this compilation process(BIND) is Package.
If we bind the packages then we will get Plan/Application
plan.
When we do the link-edit the cobol program, a thread will
be created between the load module of cobol and plan.

Online program will not work and it will give you abend
AEY9 when retry to use if connection has not been
established.

a nonmatching index scan if ACCESSTYPE = I

DeCLarations GENerator: used to create the host language copy books for the table definitions. Also creates the DECLARE table.

YES we can use and Group by must come in order to code query.Ex. select * from tab01 group by col1 order by col02

YES we can use and Group by must come in order to code query.Ex. select * from tab01 group by col1 order by col02

Yes.

A multiple index is not one index but two indexes for two different columns of a table. A composite index is one index made up of combined values from two columns in a table. If two columns in a table will often be accessed together a composite index will be efficient.

High resource utilization if large updates are to be done

1. Based on the lock-size given while creating the tablespace
2. Programmer can direct the DB2 what lock-size to use
3. If lock-size ANY is specified, DB2 usually chooses a lock-size of PAGE

Tables related with a foreign key are called delete-connected because a deletion in the primary key table can affect the contents of the foreign key table.

Precompilor-:

Precompilor's main purpose is to check the syntactical error of COBOL programes which contains embeded SQL statements.It first compiles & then differentiates cobol & DB2 statements.It sends all the DB2 queries to DBRM(database request module) and in that cobol program control makes those query lines as comment lines & issues CALL statement & that moves to MSC(modified sourse code).

Plan is generated when you compile the DB2-SQL program.

This plan is stored in the DBRM and binded to database as packages. (stored in syscat.syspackages - pkgname)

DBRM: Data base request module is generated by precompiler which contains the sql statements which are separated from the source program.

PACKAGE: By binding the DBRM package is generated.Package contains the internal structure of the original sql statements.

PLAN: It is the combination of packages that are bind to form a PLAN.

A VARCHAR column REMARKS would be defined as follows:
...
10 REMARKS.
49 REMARKS-LEN PIC S9(4) USAGE COMP.
49 REMARKS-TEXT PIC X(1920).

Never. New primary key values are not a problem. However, the values of foreign key inserts must have corresponding primary key values in their related tables. And updates of primary key values may require changes in foreign key values to maintain referential integrity

These are attributes of one table that have matching values in a primary key in another table, allowing for relationships between tables.

Keys (columns) that exist on one table and that are primary keys on another table.