IBM DB2

  Home  Databases Programming  IBM DB2


“DB2 is one of the families of relational database management system (RDBMS), DB2 Interview Questions and Answers will guide us that DB2 software products within IBM's broader Information Management Software line. Although there are different "editions" and "versions" of DB2, which run on devices ranging from handhelds to mainframes, most often DB2 refers to the DB2 Enterprise Server Edition, So get start learning IBM DB2 with DB2 Interview Questions with Answers Guide”



32 IBM DB2 Questions And Answers

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

 167 views

2⟩ 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

 158 views

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

 153 views

5⟩ 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;

 160 views

9⟩ 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

 138 views

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

 163 views

12⟩ What is DCLGEN ?

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

 164 views

13⟩ 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

 162 views

17⟩ 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

 159 views

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

 165 views