Dynamic query in COBOL-DB2 program



IBM's flagship relational database management system

Dynamic query in COBOL-DB2 program

Postby letmelive » Wed Dec 19, 2012 2:31 am

Hi,
I have a requirement where I need to query the SYSCOLUMNS table for the given table name and schema name & write the output into a file.
Table name and Schema name are input to the program via JCL. I wrote the following code to acheive the same. But the fetch gives sqlcode 100 for the very first time itself whereas if I execute the same query in toad it returns several rows. I am trying to figure out the cause . Could anyone please tell me what could be the probable reason?
01  WS-QUERY.                                             
    49 WS-QUERY-LENGTH            PIC S9(4)  COMP SYNC.   
    49 WS-QUERY-SQL               PIC X(6000) VALUE SPACES.
..
DECLARE C1 CURSOR FOR QRYSTM
....
STRING   'SELECT NAME, COLNO, COLTYPE '  DELIMITED BY SIZE,
        ' FROM SYSIBM.SYSCOLUMNS '       DELIMITED BY SIZE, 
        ' WHERE TBNAME = '''             DELIMITED BY SIZE, 
          WS-IN-TBNAME                   DELIMITED BY SPACES,
        ''' AND TBCREATOR = '''          DELIMITED BY SIZE, 
          WS-IN-TBCREATOR                DELIMITED BY SPACES,
        ''' FOR FETCH ONLY WITH UR'      DELIMITED BY SIZE   
    INTO  WS-QUERY-SQL                                       
  ON OVERFLOW                                               
     DISPLAY '** STR OVRFLW IN WS-QUERY : TABLE '           
                      WS-IN-TBNAME                           
  END-STRING.                                               

MOVE ZEROES                 TO L         
INSPECT FUNCTION REVERSE(WS-QUERY-SQL)   
        TALLYING L FOR LEADING SPACES   
COMPUTE L = LENGTH OF WS-QUERY-SQL - L   
COMPUTE WS-QUERY-LENGTH = L.             
...

PREPARE QRYSTM FROM :WS-QUERY
...
OPEN C1
...
FETCH C1 INTO   
  :WH-COLNAME,   
  :WH-COLNO,     
  :WH-COLTYPE


Thanks,
LML
letmelive
 
Posts: 20
Joined: Thu Oct 04, 2012 1:27 am
Has thanked: 6 times
Been thanked: 0 time

Re: Dynamic query in COBOL-DB2 program

Postby Pandora-Box » Wed Dec 19, 2012 10:36 am

Where do you get the data for

WS-IN-TBNAME
WS-IN-TBCREATOR

??
User avatar
Pandora-Box
 
Posts: 65
Joined: Fri Feb 10, 2012 8:30 pm
Location: Mars
Has thanked: 3 times
Been thanked: 6 times

Re: Dynamic query in COBOL-DB2 program

Postby GuyC » Wed Dec 19, 2012 3:15 pm

the first question is: why do you need a dynamic statement for this ?
Couldn't you just
EXEC SQL
Declare cursor C1 for
   SELECT NAME, COLNO, COLTYPE
    FROM SYSIBM.SYSCOLUMNS
   WHERE TBNAME = :WS-IN-TBNAME
     AND TBCREATOR = :WS-IN-TBCREATOR
   FOR FETCH ONLY WITH UR
END-EXEC
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Dynamic query in COBOL-DB2 program

Postby letmelive » Thu Dec 20, 2012 11:51 pm

Thanks for your replies.
Pandora-box,
I supply the values for table name and creator/schema in JCL. I was able to figure out the problem and it was in the design i followed in receiving the parm values inside the program. working storage variable for schema got junk values in it which was not visible in the spool and that caused the problem.

GuyC,
Yes, I would not need a dynamic query in first place. Thank you so much. I just realized it now. I've been working for a while on adhoc stuffs that need dynamic queries. I have not come out of that yet.

Thanks & Regards,
LML
letmelive
 
Posts: 20
Joined: Thu Oct 04, 2012 1:27 am
Has thanked: 6 times
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post