Running SQL In REXX



IBM's Command List programming language & Restructured Extended Executor

Running SQL In REXX

Postby arshadhrashid » Wed Sep 02, 2015 2:09 am

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.
arshadhrashid
 
Posts: 70
Joined: Tue Jul 28, 2009 5:03 am
Has thanked: 0 time
Been thanked: 0 time

Re: Running SQL In REXX

 

Re: Running SQL In REXX

Postby NicC » Wed Sep 02, 2015 2:32 pm

Have you Googled "db2 rexx"? Loads of stuff there. Also one of the DB2 user manuals has an entire section devoted to Rexx.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Running SQL In REXX

Postby arshadhrashid » Wed Sep 02, 2015 8:47 pm

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.
arshadhrashid
 
Posts: 70
Joined: Tue Jul 28, 2009 5:03 am
Has thanked: 0 time
Been thanked: 0 time

Re: Running SQL In REXX

Postby Mickeydusaor » Wed Sep 02, 2015 8:52 pm

DB2 for OS/390 Rexx Language Support, program number is 5655-DB2
User avatar
Mickeydusaor
 
Posts: 29
Joined: Fri Feb 24, 2012 11:24 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Running SQL In REXX

Postby NicC » Wed Sep 02, 2015 8:58 pm

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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Running SQL In REXX

Postby arshadhrashid » Thu Sep 03, 2015 2:09 am

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
/*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,
arshadhrashid
 
Posts: 70
Joined: Tue Jul 28, 2009 5:03 am
Has thanked: 0 time
Been thanked: 0 time

Re: Running SQL In REXX

Postby Mickeydusaor » Thu Sep 03, 2015 3:10 am

Try this one SC26-8966-03 and just search for Rexx
User avatar
Mickeydusaor
 
Posts: 29
Joined: Fri Feb 24, 2012 11:24 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Running SQL In REXX

Postby NicC » Thu Sep 03, 2015 4:10 am

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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Running SQL In REXX

Postby arshadhrashid » Tue Sep 15, 2015 3:59 am

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

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'"
arshadhrashid
 
Posts: 70
Joined: Tue Jul 28, 2009 5:03 am
Has thanked: 0 time
Been thanked: 0 time


Return to CLIST & REXX

 


  • Related topics
    Replies
    Views
    Last post