Interview questions in SQL

shammiullah · New User Joined: 13 Jun 2005 Posts: 24

HI,
1.I'M DECLARING A CURSOR AND OPENED IT IN A MAIN PGM.THEN I'M CALLING THE SUB PGM AND PERFORMING SELECT OPERATION.IS IT POSSIBLE.
2.I HAVE A EMBEDDED SQL PGM......IN THAT A MAINPGM IS CALLING 3 SUBPROGRAMS.EVERYTHINGZ FINE WITH THIS PROGRAM.NOW I'M MODIFYING 3RD PROGRAM.SO,INSTEAD OF BINDING PLAN AGAIN IT'LL B ENOUGH IF I BIND THE PARTICULAR PACKAGE.MY QUES IS HOW TO KNOW THAT PARTICULAR PACKAGE......
3.WHATZ THE ROLE OF COLLECTION ID IN EMBEDDED SQL PGM.

GUYZ ....DO LET ME KNOW THE ANSWER IF ANYONE OF KNOW....THANX IN ADVANCE FOR THE REPLIES......

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

Hi frnd,

1. Yes it is possible if u have declared the cursor in the sub program.

2. I can't understand ur question.. u can bind an individual plan in a package so ur query is vise versa. i don't know. a package can nerver b executed directly.

3. since the Package contains a location identifier, collection ID and a package Id. the collection id represents a logical grouping of package. so with the help of this it ties the package to the program to which it applies.

reshma · Posted: Wed Nov 23, 2005 10:55 am

hi khamarutheen,

if u have declared WITHHOLD option with the cursor declaration ,there is no need to declare the cursor onceagain in the subprogram ,the cursor will remain opened when u return the nexttime after we perform tht select operation in the subprogram.

regards,
reshma

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

Hi frnd,
Thank u reshma.
WITH HOLD Prevents the cursor from being closed as a consequence of a commit operation.
When WITH HOLD is specified, a commit operation commits all the changes in
the current unit of work, but releases only locks that are not required to
maintain the cursor.

EG:

EXEC SQL DECLARE C3 CURSOR WITH HOLD FOR
SELECT * FROM DSN8710.EMP
FOR UPDATE OF WORKDEPT, PHONENO, JOB, EDLEVEL, SALARY;

One more
If the stored procedure returns result sets, the cursors that are associated
with the result sets must have been defined as WITH HOLD to be usable
after the commit.

shammiullah · New User Joined: 13 Jun 2005 Posts: 24

thank u khamartheen and reshma......i've got one more ques....

let me take an embedded sql pgm...i've precompiled it today.if i bind it tommorrow will it give any mismatch abend or it'll work fine...plz gimme the reason along with ur answer......
thanx

k_vikram07 · New User Joined: 23 Nov 2005 Posts: 35

The bind and execution work well. No problems.

The reason:

When we precompile, it creates the timestamps in the dbrm and the modified code. And binding doesn't have anything to do with these timestamps. When we execute, the timestamp on dbrm and the timestamp from objmodule should match. Otherwise it is inconsistent code and so it gives the sql error.

Thanks
Vik.

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

hi frnd,
As per k_vikram07 there will not be any error up to my knowledge. U may think b/w the timestamp. so as per vikram

shammiullah · New User Joined: 13 Jun 2005 Posts: 24

thnk u very much for ur prompt reply..........

shammiullah · New User Joined: 13 Jun 2005 Posts: 24

hi,
1. how to give db2 table as input file in jcl and what is the need of this?

2. what r the utilities used to take backup of db2 table,and replacing that backed up data in to a table and load a vsam file to db2 table.....
3.while taking backup of db2 table is it like back up shuld b taken only in to a flat file......

warm regards,
shammi....

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

Hi frnd,