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