DB2 query in REXX
Lindy Mayfield
There is also an edit macro called DRAW that will write the
appropriate SQL (select, insert, delete, load) into your edit. I use it for SPUFI, stuff like that.
It was floating around somewhere on some tools page. If you don’t have it and want me to send it, let me know.
Ciao,
Lindy
-----Original Message-----
From: TSO REXX Discussion List [mailto:TSO-...@VM.MARIST.EDU] On Behalf Of Isaac Yassin
Sent: Wednesday, January 14, 2004 18:11
To: TSO-...@VM.MARIST.EDU
Subject: Re: DB2 query in REXX
Hi,
Maybe a little bit long - but well worth it :-)
Isaac Yassin
/* REXX */
/*------------------------------------------------------------------*/
/* DESCRIBE TABLE INFO. */
/*------------------------------------------------------------------*/
/* (C) ISAAC YASSIN - 1998-2004. */
/*------------------------------------------------------------------*/
/*
SIGNAL ON ERROR
SIGNAL ON FAILURE
SIGNAL ON SYNTAX
SIGNAL ON NOVALUE
*/
TRACE O
PARSE UPPER ARG SSID TBNAME REST
/* ADD-ON SECTION */
IF LEFT(TBNAME,3)='SYS' THEN TBNAME='SYSIBM.'||TBNAME
/* END ADD-ON SECTION */
PARSE VAR TBNAME TBCR '.' TBNM
/*------------------------------------------------------------------*/
/*------- M A I N --------------------------------------------------*/
/*------------------------------------------------------------------*/
U = USERID() ;
JUNK=MSG(OFF)
DO 1
CALL ALOCFREE
CALL ALLOCF
CALL GETROWS
CALL ALOCFREE
END
RETURN;
/*------------------------------------------------------------------*/
ALLOCF:
DSN="'"||U".DESC.T"||RIGHT(TIME(L),6)||"'"
IF SYSDSN("DSN") <> 'OK' THEN
"ALLOC F(JOB1) DA("DSN") NEW TRACKS SP(1 1) ",
"BLKSIZE(8880) LRECL(80) RECFM(F B) DSORG(PS)"
ELSE
"ALLOC DDNAME(JOB1) DA("DSN") SHR REUSE"
RETURN
/*------------------------------------------------------------------*/
ALOCFREE:
"FREE F(JOB1)"
RETURN
/*------------------------------------------------------------------*/
GETROWS:
'SUBCOM DSNREXX' /* DB2 SQL HOST CMD ENV AVAILABLE? */
/* IF NOT, MAKE IT AVAILABLE */
IF RC THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX /* ADD HOST CMD ENVIRONMENT */
/* BY DEFAULT SEND COMMANDS OTHER */
/* THAN REXX INSTRUCTIONS TO DSNREXX */
"CONNECT "SSID /* CONNECT TO THE GIVEN SUBSYSTEM */
IF RC <> 0 THEN
DO;
SAY "CONNECT FAILED, RC="RC
EXIT
END
"EXECSQL SET CURRENT PACKAGESET='DSNREXUR'"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM SET PKG.'
SQLSTMT1 = ,
"SELECT NAME,COLTYPE,LENGTH,DEFAULT,NULLS,COLNO "||,
" ,SUBSTR(DEFAULTVALUE,1,8) "||,
"FROM SYSIBM.SYSCOLUMNS "||,
"WHERE TBCREATOR='"STRIP(TBCR)"' "||,
"AND TBNAME ='"STRIP(TBNM)"' "||,
"ORDER BY COLNO "
SQLSTMT2 = ,
"SELECT IX.CREATOR,IX.NAME,IX.UNIQUERULE "||,
" ,IX.CLUSTERING,IX.CLUSTERRATIO "||,
" ,IX.FIRSTKEYCARD,IX.FULLKEYCARD "||,
" ,IX.NLEAF,IX.NLEVELS,IX.INDEXSPACE "||,
" ,XK.COLNAME,XK.COLSEQ,XK.ORDERING "||,
" ,XP.LEAFDIST,XP.LEAFNEAR,XP.LEAFFAR "||,
" ,XP.EXTENTS,XP.PSEUDO_DEL_ENTRIES "||,
" ,CL.COLCARD,HEX(IX.COPYLRSN) "||,
" ,XP.PCTFREE,XP.FREEPAGE,IX.STATSTIME "||,
"FROM SYSIBM.SYSINDEXES IX "||,
" ,SYSIBM.SYSINDEXPART XP "||,
" ,SYSIBM.SYSKEYS XK "||,
" ,SYSIBM.SYSCOLUMNS CL "||,
"WHERE CL.TBCREATOR='"STRIP(TBCR)"' "||,
"AND CL.TBNAME ='"STRIP(TBNM)"' "||,
"AND IX.TBCREATOR='"STRIP(TBCR)"' "||,
"AND IX.TBNAME ='"STRIP(TBNM)"' "||,
"AND IX.CREATOR = XK.IXCREATOR "||,
"AND IX.NAME = XK.IXNAME "||,
"AND IX.CREATOR = XP.IXCREATOR "||,
"AND IX.NAME = XP.IXNAME "||,
"AND CL.NAME = XK.COLNAME "||,
"ORDER BY IX.CREATOR,IX.NAME,XK.COLSEQ "
SQLSTMT3 = ,
"SELECT TS.DBNAME,TS.NAME,TS.PGSIZE,TS.BPOOL "||,
" ,TS.PARTITIONS,TS.NTABLES,TS.NACTIVE "||,
" ,TS.SEGSIZE,TP.EXTENTS,TP.PAGESAVE "||,
" ,TS.LOCKRULE,TS.LOCKMAX,TB.CARDF "||,
" ,TS.STATSTIME "||,
"FROM SYSIBM.SYSTABLESPACE TS "||,
" ,SYSIBM.SYSTABLES TB "||,
" ,SYSIBM.SYSTABLEPART TP "||,
"WHERE TB.CREATOR='"STRIP(TBCR)"' "||,
"AND TB.NAME ='"STRIP(TBNM)"' "||,
"AND TB.DBNAME = TS.DBNAME "||,
"AND TB.TSNAME = TS.NAME "||,
"AND TB.DBNAME = TP.DBNAME "||,
"AND TB.TSNAME = TP.TSNAME "||,
"ORDER BY 1 , 2 "
SQLSTMT4 = ,
"SELECT COUNT(*) "||,
"FROM SYSIBM.SYSINDEXES "||,
"WHERE TBCREATOR='"STRIP(TBCR)"' "||,
"AND TBNAME ='"STRIP(TBNM)"' "
"EXECSQL DECLARE C1 CURSOR FOR S1"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM DECLARE C1'
"EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :SQLSTMT1"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM PREPARE C1'
"EXECSQL OPEN C1"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM OPEN C1'
"EXECSQL DECLARE C2 CURSOR FOR S2"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM DECLARE C2'
"EXECSQL PREPARE S2 INTO :OUTSQLDA FROM :SQLSTMT2"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM PREPARE C2'
"EXECSQL OPEN C2"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM OPEN C2'
"EXECSQL DECLARE C3 CURSOR FOR S3"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM DECLARE C3'
"EXECSQL PREPARE S3 INTO :OUTSQLDA FROM :SQLSTMT3"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM PREPARE C3'
"EXECSQL OPEN C3"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM OPEN C3'
"EXECSQL DECLARE C4 CURSOR FOR S4"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM DECLARE C4'
"EXECSQL PREPARE S4 INTO :OUTSQLDA FROM :SQLSTMT4"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM PREPARE C4'
"EXECSQL OPEN C4"
IF SQLCODE <> 0 THEN CALL SQL_ERROR 'FROM OPEN C4'
/* INITIALIZE */
SQLCODE=0 ;
"EXECSQL FETCH C4 INTO :IXNUM "
"EXECSQL CLOSE C4 "
QUEUE " "TBNAME" - #INDEXES: "IXNUM
QUEUE COPIES('=',70)
DO UNTIL(SQLCODE <> 0)
"EXECSQL FETCH C3 INTO "||,
" :DBNAM,:TSNAME,:PGSIZE,:BPOOL "||,
",:PARTN,:NTABS,:NACTIVE "||,
",:SEGSIZE,:EXTENTS,:PGSAVE "||,
",:LOCKR,:LOCKMX,:CARDF,:STATIME "
IF SQLCODE = 0 THEN
DO
SELECT
WHEN LOCKR='A' THEN LOCKR='ANY'
WHEN LOCKR='R' THEN LOCKR='ROW'
WHEN LOCKR='P' THEN LOCKR='PAGE'
WHEN LOCKR='L' THEN LOCKR='LOB'
WHEN LOCKR='T' THEN LOCKR='TABLE'
WHEN LOCKR='S' THEN LOCKR='TBLSPACE'
END
QUEUE " DBNAME......: "SUBSTR(DBNAM,1,8)"| TSNAME......: "TSNAME
QUEUE " BUFFERPOOL..: "SUBSTR(BPOOL,1,8)"| PAGESIZE....: "PGSIZE
QUEUE " PARTITIONS..: "SUBSTR(PARTN,1,8)"| SEGMENT SIZE: "SEGSIZE
QUEUE " #TABLES.....: "SUBSTR(NTABS,1,8)"| EXTENTS.....: "EXTENTS
QUEUE " LOCKRULE....: "SUBSTR(LOCKR,1,8)"| LOCKMAX.....: "LOCKMX
QUEUE " ACTIVE PAGES: "SUBSTR(COMMA(NACTIVE),1,10) ,
"/ %COMPRESS: "PGSAVE"%"
DECDOT=POS('.',CARDF)
IF DECDOT > 0 THEN CARDF=SUBSTR(CARDF,1,DECDOT-1)
QUEUE " RECORDS.....: "COMMA(CARDF)" / STATS TIME: "STATIME
END /* SQLCODE=0 */
END /* DO UNTIL */
"EXECSQL CLOSE C3"
SQLCODE=0 ;
QUEUE COPIES('=',70)
QUEUE " COLUMN NAME COLTYPE DB2LEN NULL? DFLT?"||,
" DFLTVALUE"
QUEUE COPIES('=',70)
DO UNTIL(SQLCODE <> 0)
"EXECSQL FETCH C1 INTO "||,
" :COLNAME,:COLTYPE,:COLLEN,:COLDFLT,:COLNULL,:COLNO,:DFLTVAL "
IF SQLCODE = 0 THEN
DO
IF COLDFLT='I' THEN DFLTVAL='IDN. ALWAYS '
IF COLDFLT='J' THEN DFLTVAL='IDN. DEFAULT'
QUEUE " "COPIES(' ',3-LENGTH(COLNO))||COLNO". "||,
" "SUBSTR(COLNAME,1,18)" "COLTYPE||,
" "COPIES(' ',8-LENGTH(COLLEN))||COLLEN||,
" "COLNULL" "COLDFLT" "DFLTVAL
END
END /* DO UNTIL */
"EXECSQL CLOSE C1"
QUEUE COPIES('=',70)
PRVIX=' '
SQLCODE=0
DO UNTIL(SQLCODE <> 0)
"EXECSQL FETCH C2 INTO "||,
" :IXCREATOR,:IXNAME,:IXUNIQUERULE "||,
",:IXCLUSTERING,:IXCLUSTERRATIO "||,
",:IXFIRSTKEYCARD,:IXFULLKEYCARD "||,
",:IXNLEAF,:IXNLEVELS,:IXINDEXSPACE"||,
",:XKCOLNAME,:XKCOLSEQ,:XKORDERING "||,
",:LEAFDIST,:LEAFNEAR,:LEAFFAR "||,
",:EXTENTS,:PSEUDODEL,:COLCARD "||,
",:COPYLRSN,:PCTFREE,:FREEPAGE,:STATSTIME "
IF SQLCODE = 0 THEN
DO
IF PRVIX <> STRIP(IXCREATOR)||STRIP(IXNAME) THEN
DO
PRVIX = STRIP(IXCREATOR)||STRIP(IXNAME)
SELECT
WHEN IXUNIQUERULE = 'U' THEN EXPU='UNIQUE'
WHEN IXUNIQUERULE = 'P' THEN EXPU='UNIQUE & PRIMARY'
WHEN IXUNIQUERULE = 'D' THEN EXPU='DUPLICATES ALLOWED'
WHEN IXUNIQUERULE = 'C' THEN EXPU='UNQ ENFORCE CONSTRAINT'
WHEN IXUNIQUERULE = 'N' THEN EXPU='UNQ WHERE NOT NULL'
WHEN IXUNIQUERULE = 'R' THEN EXPU='UNQ NON-PRIMARY KEY'
WHEN IXUNIQUERULE = 'G' THEN EXPU='UNQ ROWID DEFAULT'
OTHERWISE EXPU='???'
END
QUEUE "====="
QUEUE " "STRIP(IXCREATOR)||'.'||STRIP(IXNAME)
QUEUE " "COPIES('-',30)
QUEUE " UNIQUERULE....: "IXUNIQUERULE" -" EXPU ,
"/ COPYLRSN.: "COPYLRSN
QUEUE " CLUSTERING....: "IXCLUSTERING" -" IXCLUSTERRATIO"%" ,
" INDEXSPACE....: "IXINDEXSPACE
QUEUE " NLEVELS.......: "IXNLEVELS ,
" NLEAF.........: "COMMA(IXNLEAF)
QUEUE " EXTENTS.......: "EXTENTS ,
" PSEUDO_DEL....: "COMMA(PSEUDODEL)
QUEUE " FREEPAGE/PCTFREE..:" FREEPAGE" / "PCTFREE ,
" STATSTIME.....: "SUBSTR(STATSTIME,1,19)
QUEUE " FIRST/FULL-KEYCARD: "COMMA(IXFIRSTKEYCARD) ,
" / "COMMA(IXFULLKEYCARD)
QUEUE " LEAF-DIST/NEAR/FAR: "COMMA(LEAFDIST),
" / "COMMA(LEAFNEAR)" / "COMMA(LEAFFAR)
QUEUE " "
END /* PRVCR <> IXCREATOR */
IF XKORDERING='A' THEN XKORDERING='ASC '
ELSE XKORDERING='DESC'
QUEUE " "XKCOLSEQ" "SUBSTR(XKCOLNAME,1,18)" "XKORDERING ,
" "COMMA(COLCARD)
END /* SQLCODE */
END /* DO UNTIL */
"EXECSQL CLOSE C2"
ADDRESS TSO
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
LINES = QUEUED() /*FIND NUMBER OF STACK LINES */
"EXECIO "LINES" DISKW JOB1(FINIS " /*COPY STACK TO OUTPUT DATASET*/
"ISPEXEC VIEW DATASET("DSN") MACRO(RESET)"
"FREE F(JOB1) DELETE "
RETURN
/******************************************************************/
COMMA: PROCEDURE; PARSE ARG _,C; IF C=='' THEN C=","; N=_'.9'
DO J=VERIFY(N,1234567890,,VERIFY(N,1234567890.,'M'))-4 TO ,
VERIFY(N,987654321,"M") BY -3;_=INSERT(C,_,J)
END
RETURN _
/******************************************************************/
SQL_ERROR:
/* MSG-MESSAGE , RETURN_CMD-HOW TO RETURN */
PARSE UPPER ARG MSG
SAY ' ERROR FROM: ' MSG
SAY 'SQLCODE =' SQLCODE
SAY 'SQLSTATE=' SQLSTATE
SAY 'SQLERRP =' SQLERRP
SAY 'SQLERRMC=' SUBSTR(SQLERRMC,1,70)
SAY 'SQLERRD1=' SQLERRD.1
SAY 'SQLERRD2=' SQLERRD.2
SAY 'SQLERRD3=' SQLERRD.3
SAY 'SQLERRD4=' SQLERRD.4
SAY 'SQLERRD5=' SQLERRD.5
SAY 'SQLERRD6=' SQLERRD.6
SAY ' '
SAY 'SQLWARN0=' SQLWARN.0
SAY 'SQLWARN1=' SQLWARN.1
SAY 'SQLWARN2=' SQLWARN.2
SAY 'SQLWARN3=' SQLWARN.3
SAY 'SQLWARN4=' SQLWARN.4
SAY 'SQLWARN5=' SQLWARN.5
SAY 'SQLWARN6=' SQLWARN.6
SAY 'SQLWARN7=' SQLWARN.7
SAY 'SQLWARN8=' SQLWARN.8
SAY 'SQLWARN9=' SQLWARN.9
SAY 'SQLWARN10=' SQLWARN.10
SAY ' '
SAY 'OUTSQLDA.SQLD=' OUTSQLDA.SQLD
COLCOUNT1 = OUTSQLDA.SQLD
IF COLCOUNT1 = 'OUTSQLDA.SQLD' THEN RETURN
DO I = 1 TO COLCOUNT1
COLNAME1.I = OUTSQLDA.I.SQLNAME
COLNAME = COLNAME1.I
DB2TYPE = OUTSQLDA.I.SQLTYPE
CALL CONVERT_COLTYPE
COLTYPE1.COLNAME = DB2TYPE
COLLENGTH1.COLNAME = OUTSQLDA.I.SQLLEN
COLPRECISION1.COLNAME = OUTSQLDA.I.SQLLEN.SQLPRECISION
COLSCALE1.COLNAME = OUTSQLDA.I.SQLLEN.SQLSCALE
COLCCSID1.COLNAME = OUTSQLDA.I.SQLCCSID
COLNULL1.COLNAME = OUTSQLDA.I.SQLTYPE//2
IF COLNULL1.COLNAME = 0 THEN COLNULL1.COLNAME = 'N' /* NOT NULL */
ELSE COLNULL1.COLNAME = 'Y' /* NULLS */
SAY 'COLNAME1.I ='COLNAME1.I
SAY 'COLNAME ='COLNAME
SAY 'DB2TYPE ='DB2TYPE
SAY 'COLTYPE1.COLNAME ='COLTYPE1.COLNAME
SAY 'COLLENGTH1.COLNAME ='COLLENGTH1.COLNAME
SAY 'COLPRECISION1.COLNAME ='COLPRECISION1.COLNAME
SAY 'COLSCALE1.COLNAME ='COLSCALE1.COLNAME
SAY 'COLCCSID1.COLNAME ='COLCCSID1.COLNAME
SAY 'COLNULL1.COLNAME ='COLNULL1.COLNAME
SAY ' '
END /* DO I = 1 TO COLCOUNT1 */
RETURN
/******************************************************************/
CONVERT_COLTYPE:
/*----------------------------------------------------------------*/
CONVERT_COLTYPE: PROCEDURE EXPOSE DB2TYPE
/*----------------------------------------------------------------*/
NULL = DB2TYPE//2
IF NULL THEN DB2TYPE = DB2TYPE - 1
SELECT
WHEN DB2TYPE = 448 THEN DB2TYPE = "VARCHAR"
WHEN DB2TYPE = 452 THEN DB2TYPE = "CHAR"
WHEN DB2TYPE = 456 THEN DB2TYPE = "LONG VARCHAR"
WHEN DB2TYPE = 464 THEN DB2TYPE = "VARGRAPHIC"
WHEN DB2TYPE = 468 THEN DB2TYPE = "GRAPHIC"
WHEN DB2TYPE = 472 THEN DB2TYPE = "LONG VARGRAPHIC"
WHEN DB2TYPE = 480 THEN DB2TYPE = "FLOAT"
WHEN DB2TYPE = 484 THEN DB2TYPE = "DECIMAL"
WHEN DB2TYPE = 496 THEN DB2TYPE = "INTEGER"
WHEN DB2TYPE = 500 THEN DB2TYPE = "SMALLINT"
WHEN DB2TYPE = 384 THEN DB2TYPE = "DATE"
WHEN DB2TYPE = 388 THEN DB2TYPE = "TIME"
WHEN DB2TYPE = 392 THEN DB2TYPE = "TIMESTAMP"
OTHERWISE DO
DB2TYPE = "UNKNOWN TYPE"
END
END /* SELECT */
RETURN
/*******************************************************************/
Isaac Yassin
IBM Certified solution expert
DB2 V7 for OS/390 & Z/OS
-----Original Message-----
From: TSO REXX Discussion List [mailto:TSO-...@VM.MARIST.EDU] On Behalf Of
karthick I
Sent: Wednesday, January 14, 2004 6:46 PM
To: TSO-...@VM.MARIST.EDU
Subject: DB2 query in REXX
Hi Listers,
I would like to know if its possible to execute a DB2 query using a
REXX and store the output onto a dataset.
Can anyone share a sample prgm if you might have.
Thanks a lot
Regards,
Karthick.I
_________________
Unless expressly stated to the contrary, the views expressed in this email
are not necessarily the views of National Grid Transco plc or any of its
subsidiaries or affiliates (Group Companies), and the Group Companies,
their directors, officers and employees make no representation and accept
no liability for its accuracy or completeness.
This e-mail, and any attachments are strictly confidential and intended for
the addressee(s) only. The content may also contain legal, professional or
other privileged information. If you are not the intended recipient, please
notify the sender immediately and then delete the e-mail and any
attachments. You should not disclose, copy or take any action in reliance
on this transmission.
You may report the matter by calling us on + 44(0) 1455 230999
Please ensure you have adequate virus protection before you open or detach
any documents from this transmission. The Group Companies do not accept
any liability for viruses. An e-mail reply to this address may be subject
to monitoring for operational reasons or lawful business practices.
----------------------------------------------------------------------
For TSO-REXX subscribe / signoff / archive access instructions,
send email to LIST...@VM.MARIST.EDU with the message: INFO TSO-REXX
----------------------------------------------------------------------
For TSO-REXX subscribe / signoff / archive access instructions,
send email to LIST...@VM.MARIST.EDU with the message: INFO TSO-REXX
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date: 1/2/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date: 1/2/2004
----------------------------------------------------------------------
For TSO-REXX subscribe / signoff / archive access instructions,
send email to LIST...@VM.MARIST.EDU with the message: INFO TSO-REXX
Isaac Yassin
Thanks in advance :-)
I would like to have it.
Isaac Yassin
-----Original Message-----
From: TSO REXX Discussion List [mailto:TSO-...@VM.MARIST.EDU] On Behalf Of
Lindy Mayfield
Sent: Sunday, January 18, 2004 7:07 PM
To: TSO-...@VM.MARIST.EDU
Subject: Re: DB2 query in REXX
That's very useful. I like it.
There is also an edit macro called DRAW that will write the
appropriate SQL (select, insert, delete, load) into your edit. I use it for
SPUFI, stuff like that.
It was floating around somewhere on some tools page. If you don't have it and
want me to send it, let me know.
Ciao,
Lindy
----------------------------------------------------------------------