IBM Mainframe Forum

[arshadhrashid]

Hi there,
I am unable to find an example that can clearly list the steps to run some SQL commands of db2 v10 main-frame database from with in a REXX script.
I could not figure out its DENREXX or EXESQL or connection etc etc.

I need to write a rexx script that will query a database table and will process the rows returned and save them in an output file.
I would highly appreciate if someone can share such an example code.

Thanks.

[NicC]

Have you Googled "db2 rexx"? Loads of stuff there. Also one of the DB2 user manuals has an entire section devoted to Rexx.

[arshadhrashid]

yes Sir,
I googled a lot of stuff. Problem is information is scattered all over and I could not find one single script that will do the job. Mostly its for REXX working with some other types of databases.
I am not sure about db2 manual I will check there too but I was hoping if someone has some example that I can use. I have spent a lot of time trying to find one.

[Mickeydusaor]

DB2 for OS/390 Rexx Language Support, program number is 5655-DB2

[NicC]

The first link I got was probably what you needed. To get to the manual click on IBM Manuals at the top and bottom of each page on this forum, scroll down to databases, select the DB2 Programming Guide nearest to your level - it has hardly changed over the years - and you need section 2.4.6.

BTW - I am sure that there are samples withn the forum.

[arshadhrashid]

Hi Mickeydusaor
Thanks for your response but I have so far not found that document 5655-DB2. I am still searching.

Hi NicC
Thanks for your time and effort too. But the manual page in this web site lists only up to v5 of database db2 for z/os.
Even in that its only generic syntax and not any example.

When I use this generic syntax like

Select all

/*REXX*/
TRACE ALL
PARSE ARG DB2SYS
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT "DB2SYS
SQLSTR="SELECT SYSDATE FROM SYSDUMMY1"
EXECSQL "SELECT SYSDATE FROM SYSDUMMY1"
ADDRESS DSNREXX "DISCONNECT "

I get

IKJ56500I COMMAND EXECSQL NOT FOUND,

[Mickeydusaor]

Try this one SC26-8966-03 and just search for Rexx

[NicC]

You obviously did not look hard enough - I went through that route so that I cpuld write it out for you. The manuals start at V5 and go up to V9 - you obviously did not scroll down far enough.

EXECSQL is a 'command' that is understood by DSNREXX. Therefore you heve to pass it to DSNREXX. Apart from not looking hard enough for the manual you are obviously not reading it carefully enough.

[arshadhrashid]

Thanks every one, I figured out what was needed. I am pasting my code just in case someone can benefit from it.
Thanks.

Select all

ADDRESS DSNREXX
CONNECT DB2SYS
SQLSTMT = "SELECT NAME,COLTYPE FROM SYSIBM.SYSCOLUMNS
           WHERE TBNAME = '"||TNAME||"'"
"EXECSQL DECLARE C1 CURSOR FOR S1"
"EXECSQL PREPARE S1 FROM :SQLSTMT"
"EXECSQL OPEN C1"
"EXECSQL FETCH C1 INTO :COLUMN,:CTYPE"
I=1
DO WHILE (SQLCODE = 0)

IF CTYPE <> 'CHAR' THEN COLUMN='CHAR('||COLUMN||')'
         IF FIRSTTIME='TRUE' THEN
         DO
         SELSTMT=SELSTMT||COLUMN||COMAAPPEND
         FIRSTTIME='FALSE'
              SELVAL.I="SELECT "||COLUMN||COMAAPPEND
/*            SAY "SELVAL="SELVAL.I */              I=I+1
         END
ELSE
         DO
/*       SELSTMT=SELSTMT||','||COLUMN||COMAAPPEND */
         SELSTMT=SELSTMT||',STRIP('||COLUMN||')'||COMAAPPEND
         SELVAL.I=',STRIP('||COLUMN||')'||COMAAPPEND
         I=I+1
/* SAY "SELVAL.="SELVAL.I */
END

"EXECSQL FETCH C1 INTO :COLUMN,:CTYPE"
END
SELSTMT=SELSTMT||" FROM "||SCHEMA||"."||TNAME
"EXECSQL CLOSE C1"
S_RC = RXSUBCOM(DELETE,DSNREXX,DSNREXX)

I am feting all columns of a given table to make a SELECT statement that has comma appended after every column so that it can generated a comma separated list of data.
Dont forget to switch your DSN environment if you need something like write output to a file.

ADDRESS TSO
OUTDSN="'FQ1.KA.FILE1.SQL'"