1. What is sqlcode -811?

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

2. What is sqlcode -922 ?

Authorization failure

3. What is the difference between primary key & unique index ?

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

4. What is a clustering index ?

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

5. A user
defined name that is the anchor for packages. It has not physical existence. Main usage is to group packages.

In SPUFI suppose you want to select max. of 1000 rows , but the select returns only 200 rows.

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

6. What is the self-referencing constraint?

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.

7. Is DECLARE CURSOR executable?

No.

8. What are foreign keys in DB2?

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.

9. When can an insert of a new primary key value threaten referential integrity?

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

10. Give the COBOL definition of a VARCHAR field.

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

Download Interview PDF

11. What is the diff bet plan,package,dbrm?

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.

12. What is a precompiler?

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

13. What are delete-connected tables?

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.

14. How does DB2 determine what lock-size to use?

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

15. What are the disadvantages of PAGE level lock?

High resource utilization if large updates are to be done

16. What is a composite index and how does it differ from a multiple index?

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.

17. Can you have more than one cursor open at any one time in a program ?

Yes.

18. Can GROUP BY and ORDERED BY used in a single query?

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

19. What is DCLGEN ?

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

20. EXPLAIN has output with MATCHCOLS = 0. What does it mean? ?

a nonmatching index scan if ACCESSTYPE = I

21. When DB2 connection fails, will online program work or give
errors?

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

22. What are Bind concepts in DB2 cobol?

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.

23. what is the difference between normal select query and
currosor

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.

24. 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?

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;

25. I have some 3 particular fields ..i want to know which all
tables have those 3 fields. Is there any way to identify..
can we know by quering system tables.

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

Download Interview PDF

26. Can i insert bulk records into a db2 table using qmf of
spufi only.

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

27. Can you search give an array in the WHERE clause of a db2
query?

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.

28. could you give me an example how, where i code CHECKPOINT
and restart. I need and example

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

29. If I have 5 Queries in a DB2 Cobol program , while
precompiling how many DBRMs will get created and How many
Plans and Packages will get created while Bind Process?

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

30. Cursors can be declared in both working-storage & procedure
division, Agreed.
But is there any difference? If could you please suggest
what is the difference.
TIA

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.

31. Is it Possible to declare or create a cursor for UPDATE of
table? If yes tell me how? If no Tell me why?

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.

32. what are the bind parameters IBM DB2?

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