IBM DB2 Interview Preparation Guide

Download PDF

SELECT statement has resulted in retrieval of more than one row.

Authorization failure

Primary : a relational database constraint. Primary key consists of one or more columns that uniquely identify a row in the table. For a normalized relation, there is one designated primary key.

Unique index: a physical object that stores only unique values. There can be one or more unique indexes on a table

Causes the data rows to be stored in the order specified in the index. A mandatory index defined on a partitioned table space.

100 ( for successful completion of the query ), 0 (for successful COMMIT if AUTOCOMMIT is set to Yes).

A31. The self-referencing constraint limits in a single table the changes to a primary key that the related foreign key defines. The foreign key in a self referencing table must specify the DELETE CASCADE rule.

No.

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.

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

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

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.

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

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.

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

High resource utilization if large updates are to be done

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.

Yes.

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

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

a nonmatching index scan if ACCESSTYPE = I

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

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.

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.

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;

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