In case someone else will need such code, this is my rexx from 2012 to scan
the db2 catalog for tables and table structures and build a csv file out of
it.
See the parse statement for the variables passed to the program. As this is
a rexx, you can run multiple iterations under single tso batch step.
Enjoy,
ITschak
/* MUGIREXX V1.2 TSO ADBL2 */
QUERYTABLE:
SIGNAL QUERYTABLE.MAIN
QUERYTABLE.DOC:
------------------------------------------------------------------
COPYRIGHT (C) SECURITEAM SOFTWARE LTD. 1999-2012, ISRAEL
------------------------------------------------------------------
QUERYTABLE.MAIN:
PARSE ARG DB2SUBSYS DB2CREATOR DB2TABNAME DB2FSIZE DB2PEXT ,
DB2SEXT DB2SAMP
HEX00 = ' '
FILEIDX = 0
TOTALREC = 0
FIELDDLM = '<>'
LINEDLM = '/>'
M$DBANME = DWDBDB2W
CALL DB2CONFIG
CALL DB2CONNECT
CALL SQLQUERY
RETURN
------------------------------------------------------------------
SQLQUERY:
CMDIX = 0
XQUERYA = "SELECT NAME,CREATOR,TYPE,TSNAME",
"FROM SYSIBM.SYSTABLES",
"WHERE NAME = '"DB2TABNAME"' AND TYPE = 'T'",
"AND CREATOR = '"DB2CREATOR"'"
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :XQUERYA"
ADDRESS DSNREXX "EXECSQL DESCRIBE S1 INTO:SQLDA"
ADDRESS DSNREXX "EXECSQL OPEN C1"
IF (SQLCODE <> 100) THEN DO
ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :SQLDA"
TABLENAME1 = SQLDA.1.SQLDATA
TABCMD.1 = "SET QUOTED_IDENTIFIER ON;"
TABCMD.2 = "USE" M$DBANME ";"
TABCMD.3 = "GO"
TABCMD.4 = 'CREATE TABLE' SQLDA.1.SQLDATA '('
TABIDX = 4
TABOWNER = STRIP(SQLDA.2.SQLDATA)
TABTSNAME = STRIP(SQLDA.4.SQLDATA)
ADDRESS DSNREXX "EXECSQL CLOSE C1"
QUERY2 = "SELECT NAME,COLTYPE,LENGTH,",
"SCALE,NULLS,DEFAULT,KEYSEQ",
"FROM SYSIBM.SYSCOLUMNS",
"WHERE TBNAME = '"DB2TABNAME"' AND ",
"TBCREATOR = '"TABOWNER"' ORDER BY COLNO "
FIELDLST = ''
PRIMKEY = ''
ADDRESS DSNREXX "EXECSQL DECLARE C2 CURSOR FOR S2"
ADDRESS DSNREXX "EXECSQL PREPARE S2 FROM :QUERY2"
ADDRESS DSNREXX "EXECSQL DESCRIBE S2 INTO:SQLVA"
ADDRESS DSNREXX "EXECSQL OPEN C2"
ADDRESS DSNREXX "EXECSQL FETCH C2 USING DESCRIPTOR :SQLVA"
DO WHILE (SQLCODE <> 100)
TABIDX = TABIDX + 1
TABCMD.TABIDX = '"'SQLVA.1.SQLDATA'"'
TABCMD.TABIDX = LEFT(TABCMD.TABIDX,24)
XFIELD = STRIP(SQLVA.1.SQLDATA)
FIELD.XFIELD = SQLVA.2.SQLDATA SQLVA.3.SQLDATA
FIELDLST = FIELDLST SQLVA.1.SQLDATA
IF (SQLVA.2.SQLDATA = 'CHAR') THEN DO
TABCMD.TABIDX = TABCMD.TABIDX "CHAR("SQLVA.3.SQLDATA")"
END
IF (SQLVA.2.SQLDATA = 'DECIMAL') THEN DO
TABCMD.TABIDX = TABCMD.TABIDX "DECIMAL("SQLVA.3.SQLDATA","||,
SQLVA.4.SQLDATA")"
END
IF (SQLVA.2.SQLDATA = 'TIMESTMP') THEN DO
TABCMD.TABIDX = TABCMD.TABIDX "DATETIME"
END
IF (SQLVA.2.SQLDATA = 'TIME') THEN DO
TABCMD.TABIDX = TABCMD.TABIDX "TIME"
END
IF (SQLVA.2.SQLDATA = 'DATE') THEN DO
TABCMD.TABIDX = TABCMD.TABIDX "DATE"
END
IF (SQLVA.2.SQLDATA = 'INTEGER') THEN DO
TABCMD.TABIDX = TABCMD.TABIDX "INTEGER"
END
IF (SQLVA.2.SQLDATA = 'SMALLINT') THEN DO
TABCMD.TABIDX = TABCMD.TABIDX "SMALLINT"
END
IF (SQLVA.5.SQLDATA = 'N') THEN DO
TABCMD.TABIDX = TABCMD.TABIDX "NOT NULL,"
END
IF (SQLVA.5.SQLDATA = 'Y') THEN DO
TABCMD.TABIDX = TABCMD.TABIDX ","
END
IF (SQLVA.7.SQLDATA > '0') THEN DO
PRIMKEY = PRIMKEY SQLVA.7.SQLDATA
END
LASTTYPE = SQLVA.2.SQLDATA
ADDRESS DSNREXX "EXECSQL FETCH C2 USING DESCRIPTOR :SQLVA"
END
IF (PRIMKEY ^= '') THEN DO
TABIDX = TABIDX + 1
TABCMD.TABIDX = "PRIMARY KEY("PRIMKEY"),"
END
TABCMD.TABIDX = TRANSLATE(TABCMD.TABIDX,' ',',')
TABIDX = TABIDX + 1
TABCMD.TABIDX = ");"
TABIDX = TABIDX + 1
TABCMD.TABIDX = "GO"
/* DO I = 1 TO TABIDX
SAY TABCMD.I
END */
ADDRESS DSNREXX "EXECSQL CLOSE C2"
QUERY3 = "SELECT * FROM WPRD."DB2TABNAME
/* "WHERE REQ_NUM = '05103107'"*/
ADDRESS DSNREXX "EXECSQL DECLARE C3 CURSOR FOR S3"
ADDRESS DSNREXX "EXECSQL PREPARE S3 FROM :QUERY3"
ADDRESS DSNREXX "EXECSQL DESCRIBE S3 INTO:SQLXA"
ADDRESS DSNREXX "EXECSQL OPEN C3"
ADDRESS DSNREXX "EXECSQL FETCH C3 USING DESCRIPTOR :SQLXA"
OUTIDX = 0
MIDX = 0
MAXBLK = 0
/*DO WHILE LOOPCOND */
DO WHILE (SQLCODE <> 100)
MIDX = MIDX + 1
IF (DB2SAMP ^= 0) THEN DO
IF (MIDX > DB2SAMP) THEN DO
LEAVE
END
END
IF (MIDX%100000*100000 = MIDX) THEN DO
SAY 'ST0025I' MIDX 'LINES REACHED,BLKSIZE =' MAXBLK
END
IF (MIDX%DB2FSIZE*DB2FSIZE = MIDX) THEN DO
SAY 'ST0021I SPLITING FILE, NUMBER OF RECORDS EXCEEDED',
DB2FSIZE
MSGMODE = MSG('OFF')
"FREE F(DB2OUT)"
FILEIDX = FILEIDX + 1
DB2FNAME = UserID()"."DB2TABNAME".UNLOAD.X"FILEIDX
"DELETE '"DB2FNAME"'"
MSGMODE = MSG('ON')
"ALLOC F(DB2OUT) DA('"DB2FNAME"') NEW" ,
"CYLINDERS SPACE("DB2PEXT","DB2SEXT")" ,
"LRECL("MAXBLK+20")",
"RECFM(V B) UNIT(3390) VOLUME(ML0221,ML0307,ML0308) CATALOG"
"EXECIO * DISKW DB2OUT (STEM OUTREC. FINIS"
MSGMODE = MSG('OFF')
TOTALREC = TOTALREC + OUTIDX
/* SAY 'ST0006I NUMBER OF DATABASE RECORDS WRITTEN IS' OUTIDX
*/
DROP OUTREC.
OUTIDX =0
END
OUTRECORD = ''
RESULTSETSIZE = SQLXA.SQLD
/*SAY RIGHT(MIDX,5) 'RESULTS:' RESULTSETSIZE*/
DO I = 1 TO RESULTSETSIZE
XFIELD = STRIP(SQLXA.I.SQLNAME)
IF (I = 452) THEN DO
/* SAY ' ' LEFT(MIDX,4) LEFT(XFIELD,18) SQLXA.I.SQLDATA*/
NOP
END
/* IF (I = 73) THEN DO
SAY XFIELD
SAY SQLXA.I.SQLDATA';' LENGTH(SQLXA.I.SQLDATA)
SAY '----'
END */
PARSE VAR FIELD.XFIELD FTYPE FLENGTH
IF (FTYPE = 'TIMESTMP') THEN DO
SQLXA.I.SQLDATA = OVERLAY(':',SQLXA.I.SQLDATA,14,1)
SQLXA.I.SQLDATA = OVERLAY(':',SQLXA.I.SQLDATA,17,1)
SQLXA.I.SQLDATA = OVERLAY(' ',SQLXA.I.SQLDATA,11,1)
SQLXA.I.SQLDATA = SUBSTR(SQLXA.I.SQLDATA,1,23)
/* SAY '>' SQLXA.I.SQLDATA */
END
IF (FTYPE = 'TIME') THEN DO
IF (POS('.',SQLXA.I.SQLDATA) = 0) THEN DO
SQLXA.I.SQLDATA = '00.00.01'
END
IF (SUBSTR(SQLXA.I.SQLDATA,1,2) = '24') THEN DO
/* ------------------------------------------------ */
/* TRANSACT SQL DO NOT ACCEPT TIME OVER 23:59:59 */
/* ------------------------------------------------ */
SQLXA.I.SQLDATA = '23.59.59'
END
PARSE VAR SQLXA.I.SQLDATA HX '.' MX '.' SX
SQLXA.I.SQLDATA = HX':'MX':'SX
END
IF (FTYPE = 'DATE') THEN DO
/* IF (POS(HEX00,SQLXA.I.SQLDATA) > 0) THEN DO*/
IF (SUBSTR(SQLXA.I.SQLDATA,3,1) <> '.') THEN DO
SQLXA.I.SQLDATA= '01.01.3000'
END
PARSE VAR SQLXA.I.SQLDATA DX '.' MX '.' YX
SQLXA.I.SQLDATA = YX'-'MX'-'DX
IF (LENGTH(SQLXA.I.SQLDATA) > 26) THEN DO
SQLXA.I.SQLDATA = SUBSTR(SQLXA.I.SQLDATA,1,26)
END
/* SAY '>' SQLXA.I.SQLDATA*/
END
IF (FTYPE = 'CHAR') THEN DO
CALL LOGICALREVERSE
IF (SQLXA.I.SQLDATA = '') THEN DO
SQLXA.I.SQLDATA= ' '
END
END
OUTRECORD = OUTRECORD||SQLXA.I.SQLDATA||FIELDDLM
RECLEN = LENGTH(OUTRECORD)
END
IF (LASTTYPE = 'CHAR') THEN DO
OUTRECORD = substr(OUTRECORD,1,Length(OUTRECORD)-2)
End
BLKSIZE = LENGTH(OUTRECORD)
IF (BLKSIZE > MAXBLK) THEN DO
SAY 'ST0010I BLKSIZE CHANGED FROM' MAXBLK 'TO' BLKSIZE,
'AT RECORD' LEFT(MIDX,8)
MAXBLK = BLKSIZE
END
OUTIDX = OUTIDX + 1
OUTREC.OUTIDX = OUTRECORD
ADDRESS DSNREXX "EXECSQL FETCH C3 USING DESCRIPTOR :SQLXA"
END
ADDRESS DSNREXX "EXECSQL CLOSE C3"
QUERY4 = "SELECT NAME,UNIQUERULE FROM SYSIBM.SYSINDEXES",
"WHERE TBNAME = '"DB2TABNAME"' AND TBCREATOR = '"TABOWNER"'"
/* SAY QUERY4*/
ADDRESS DSNREXX "EXECSQL DECLARE C4 CURSOR FOR S4"
ADDRESS DSNREXX "EXECSQL PREPARE S4 FROM :QUERY4"
ADDRESS DSNREXX "EXECSQL DESCRIBE S4 INTO:SQLXA"
ADDRESS DSNREXX "EXECSQL OPEN C4"
IX = 0
ADDRESS DSNREXX "EXECSQL FETCH C4 USING DESCRIPTOR :SQLDA"
DO WHILE (SQLCODE <> 100)
IX = IX + 1
IXNAME = SQLDA.1.SQLDATA
INDEX.IX = IXNAME
INDEX.IXNAME = ' '
IF (SQLDA.2.SQLDATA= 'U') THEN DO
INDEX.IXNAME = 'UNIQUE'
END
SAY 'ST0022I INDEX' RIGHT(IX,2) 'NAME='LEFT(IXNAME,18),
'UNIQUE='INDEX.IXNAME
ADDRESS DSNREXX "EXECSQL FETCH C4 USING DESCRIPTOR :SQLDA"
END
SAY 'ST0023I NUMBER OF INDEXES DISCOVERED IS 'IX
DO IB = 1 TO IX
QUERY5 = "SELECT COLNAME FROM SYSIBM.SYSKEYS ",
"WHERE IXNAME = '"INDEX.IB"'",
"AND IXCREATOR = '"DB2CREATOR"'",
"ORDER BY COLSEQ"
/* SAY QUERY5*/
ADDRESS DSNREXX "EXECSQL DECLARE C5 CURSOR FOR S5"
ADDRESS DSNREXX "EXECSQL PREPARE S5 FROM :QUERY5"
ADDRESS DSNREXX "EXECSQL DESCRIBE S5 INTO:SQLXA"
ADDRESS DSNREXX "EXECSQL OPEN C5"
IXNAME = INDEX.IB
TABIDX = TABIDX + 1
TABCMD.TABIDX = "CREATE" INDEX.IXNAME "INDEX" IXNAME ,
"ON" DB2TABNAME "("
ADDRESS DSNREXX "EXECSQL FETCH C5 USING DESCRIPTOR :SQLDA"
DO WHILE (SQLCODE <> 100)
IXRESULTSETSIZE = SQLXA.SQLD
DO I1 = 1 TO IXRESULTSETSIZE
TABIDX = TABIDX + 1
TABCMD.TABIDX = " " SQLDA.I1.SQLDATA","
ADDRESS DSNREXX "EXECSQL FETCH C5 USING DESCRIPTOR :SQLDA"
END
END
TABCMD.TABIDX = TRANSLATE(TABCMD.TABIDX,' ',',')
TABIDX = TABIDX + 1
TABCMD.TABIDX = " );"
TABIDX = TABIDX + 1
TABCMD.TABIDX = "GO"
ADDRESS DSNREXX "EXECSQL CLOSE C5"
END
QUERY6 = "SELECT CREATOR,NAME FROM SYSIBM.SYSTABLES",
"WHERE TYPE = 'V' AND TSNAME = '"TABTSNAME"'"
/* SAY 'TS NAME=' TABTSNAME*/
/* SAY QUERY6*/
ADDRESS DSNREXX "EXECSQL DECLARE C6 CURSOR FOR S6"
ADDRESS DSNREXX "EXECSQL PREPARE S6 FROM :QUERY6"
ADDRESS DSNREXX "EXECSQL DESCRIBE S6 INTO:SQLXA"
ADDRESS DSNREXX "EXECSQL OPEN C6"
IV = 0
ADDRESS DSNREXX "EXECSQL FETCH C6 USING DESCRIPTOR :SQLDA"
DO WHILE (SQLCODE <> 100)
IV = IV + 1
IVNAME = SQLDA.2.SQLDATA
VIEW.IV = IVNAME
VIEW.IVNAME = SQLDA.1.SQLDATA
/* SAY 'VIEW' IVNAME 'CREATOR IS' SQLDA.1.SQLDATA'TS=' TABTSNAME
*/
ADDRESS DSNREXX "EXECSQL FETCH C6 USING DESCRIPTOR :SQLDA"
END
QUERYONVIEW:
SAY 'ST0024I THERE ARE' RIGHT(IV,2) 'VIEWS FOR THIS TABLE'
DO I7 = 1 TO IV
VIEWNAME = VIEW.I7
VCREATOR = STRIP(VIEW.VIEWNAME)
QUERY7 = "SELECT TEXT FROM SYSIBM.SYSVIEWS",
"WHERE TYPE = 'V' AND CREATOR = '"VCREATOR"' AND ",
"NAME = '"VIEWNAME"'"
ADDRESS DSNREXX "EXECSQL DECLARE C7 CURSOR FOR S7"
ADDRESS DSNREXX "EXECSQL PREPARE S7 FROM :QUERY7"
ADDRESS DSNREXX "EXECSQL DESCRIBE S7 INTO:SQLXA"
ADDRESS DSNREXX "EXECSQL OPEN C7"
ADDRESS DSNREXX "EXECSQL FETCH C7 INTO :VIEWDEF"
DO WHILE (SQLCODE <> 100)
IX = 30
IS = 1
VIEWDEF = SPACE(VIEWDEF)
XLEN = LENGTH(DB2CREATOR)+ 1
/* SAY '=VIEWDEF=' VIEWDEF*/
DO FOREVER
XPOS = POS(DB2CREATOR'.',VIEWDEF)
IF (XPOS = 0) THEN DO
LEAVE
END
VIEWDEF = DELSTR(VIEWDEF,XPOS,XLEN)
END
VIEWDEF = SPACE(VIEWDEF)
DO FOREVER
XV = POS(' ',VIEWDEF,IX)
IF (XV = 0) THEN DO
TABIDX = TABIDX + 1
TABCMD.TABIDX = VIEWDEF
LEAVE
END
TABIDX = TABIDX + 1
TABCMD.TABIDX = SUBSTR(VIEWDEF,1,XV)
VIEWDEF = SUBSTR(VIEWDEF,XV+1)
END
ADDRESS DSNREXX "EXECSQL FETCH C7 INTO :VIEWDEF"
END
END
/* SAY 'WE ARE HERE'*/
IF (TABCMD.TABIDX ^= '') THEN DO
TABIDX = TABIDX + 1
END
TABCMD.TABIDX = ";"
TABIDX = TABIDX + 1
TABCMD.TABIDX = "GO"
/* SAY TABIDX*/
CALL SAVEFILES
EXIT
SAVEFILES:
XFILECNT = FILEIDX + 1
IX = 0
DO XFILECNT
IX = IX + 1
TABIDX = TABIDX + 1
TABCMD.TABIDX = "BULK INSERT" TABLENAME1
TABIDX = TABIDX + 1
TABCMD.TABIDX = " FROM '\\MODNAS\MALANAPP\HASAVA\FTPSAP\"||,
DB2TABNAME".UNLOAD"IX".TXT'"
TABIDX = TABIDX + 1
TABCMD.TABIDX = " WITH ("
TABIDX = TABIDX + 1
TABCMD.TABIDX = " FIELDTERMINATOR='"FIELDDLM"', "
TABIDX = TABIDX + 1
TABCMD.TABIDX = " CODEPAGE='1255');"
TABIDX = TABIDX + 1
TABCMD.TABIDX = "GO"
END
MSGMODE = MSG('OFF')
"FREE F(DB2OUT,CMDOUT)"
/* "DELETE "'"UserID()"."DB2TABNAME".UNLOAD'"*/
"DELETE "'"UserID()"."DB2TABNAME".CMD'"
IX1 = FILEIDX + 1
DB2FNAME = UserID()"."DB2TABNAME".UNLOAD.X"IX1
"DELETE '"DB2FNAME"'"
MSGMODE = MSG('ON')
"ALLOC F(DB2OUT) DA('"DB2FNAME"') NEW" ,
"CYLINDERS SPACE("DB2PEXT","DB2SEXT")",
"LRECL("MAXBLK+20") RECFM(V B)",
"UNIT(3390) VOLUME(ML0221,ML0307) CATALOG"
"ALLOC F(CMDOUT) DA("'"UserID()"."DB2TABNAME".CMD') NEW" ,
"CYLINDERS SPACE(1,1) LRECL(80) RECFM(F B)",
"UNIT(3390) VOLUME(ML0235) CATALOG"
MSGMODE = MSG('OFF')
"EXECIO * DISKW DB2OUT (STEM OUTREC. FINIS"
TOTALREC = TOTALREC + OUTIDX
"EXECIO * DISKW CMDOUT (STEM TABCMD. FINIS"
"FREE F(DB2OUT,CMDOUT)"
FTPCMD.1 = "FTPSAP"
FTPCMD.2 = "SAPFTP"
FTPCMD.3 = "CD SAP"
FTPCMD.4 = "PUT "'"UserID()"."DB2TABNAME".CMD'" DB2TABNAME".CMD.SQL"
JX1 = 4
DO IX1 = 1 TO FILEIDX
DB2FNAME = UserID()"."DB2TABNAME".UNLOAD.X"IX1
JX1 = IX1 + 4
FTPCMD.JX1 = "PUT '"DB2FNAME"'" DB2TABNAME".UNLOAD"IX1".TXT"
END
JX1 = JX1 + 1
DB2FNAME = UserID()"."DB2TABNAME".UNLOAD.X"IX1
FTPCMD.JX1 = "PUT '"DB2FNAME"'" DB2TABNAME".UNLOAD"IX1".TXT"
"DELETE '"DB2FNAME"'"
MSGMODE = MSG('ON')
"ALLOC F(DB2OUT) DA('"DB2FNAME"') NEW" ,
"CYLINDERS SPACE("DB2PEXT","DB2SEXT")" ,
"LRECL("MAXBLK+20")",
"RECFM(V B) UNIT(3390) VOLUME(ML0221,ML0307) CATALOG"
"EXECIO * DISKW DB2OUT (STEM OUTREC. FINIS"
MSGMODE = MSG('OFF')
JX1 = JX1 + 1
FTPCMD.JX1 = "CLOSE"
JX1 = JX1 + 1
FTPCMD.JX1 = "QUIT"
"EXECIO * DISKW FTPPARM (STEM FTPCMD. FINIS"
SAY 'ST0007I NUMBER OF DATABASE RECORDS WRITTEN IS' TOTALREC
RETURN
ADDRESS DSNREXX "EXECSQL DECLARE S1 INTO: SQLDA",
"FROM :QUERY2"
DO I = 1 TO RESULTSETSIZE
END
RETURN
DB2CONFIG:
HEBOLD = '???????????????????????º???'
HEBNEW = '?abcdefghikjlnmpoqrtsvuwxyz'
ENGLISH = ' .-/ABCDEFGHIJKLMNOPQRSTUVWXYZ'
ALLNOTNUM = '"'"\|;:?^{}+=-_)(*€?%$#@!r`<>/?'"||,
'???????????????????????º???'
NUMERICS = '0123456789,.-'
RETURN
------------------------------------------------------------------
DB2CONNECT:
CONNECT = RXSUBCOM("ADD","DSNREXX","DSNREXX")
IF (CONNECT > 0) THEN DO
SAY 'ST0001E ERROR CONNECTING TO REXX-DB2.'
EXIT 20
END
ADDRESS DSNREXX "CONNECT" DB2SUBSYS
IF (RC > 0) THEN DO
SAY 'ST0002E ERROR CONNECTING TO DB2' DB2SUBSYS'.'
EXIT 20
END
SAY 'ST0003I NOW CONNECTED TO DB2' DB2SUBSYS'.'
RETURN
------------------------------------------------------------------
LOGICALREVERSE:
/*--------------------------------------------------------------*/
/*REVERSE CHAR FIELDS IN LOGICAL MANNER IN ORDER TO PRESERVE */
/*NUMERIC ORDER */
/*--------------------------------------------------------------*/
NUMWORDS = WORDS(SQLXA.I.SQLDATA)
KEEPLENGTH = LENGTH(SQLXA.I.SQLDATA)
SQLXA.I.SQLDATA = REVERSE(,
TRANSLATE(SQLXA.I.SQLDATA,HEBOLD,HEBNEW))
SQLXA.I.SQLDATA = STRIP(SQLXA.I.SQLDATA)
SQLXA.I.SQLDATA = STRIP(SQLXA.I.SQLDATA)
DO XINDEX = 1 TO NUMWORDS
/* HANDLE NUMERICS */
XWORD = WORD(SQLXA.I.SQLDATA,XINDEX)
IF (VERIFY(XWORD,ALLNOTNUM) = 0) THEN DO
ITERATE
END
SQLXA.I.SQLDATA = SUBWORD(SQLXA.I.SQLDATA,1,XINDEX-1),
REVERSE(XWORD) SUBWORD(SQLXA.I.SQLDATA,XINDEX+1)
END
SQLXA.I.SQLDATA = STRIP(SQLXA.I.SQLDATA)
IF (NUMWORDS < 2) THEN DO
/* HANDLE EMPTY AND SHORTER THEN 2 WORDS STRINGS */
SQLXA.I.SQLDATA = STRIP(SQLXA.I.SQLDATA)
RETURN
END
/* SAY RIGHT(I,5) ':' RIGHT(NUMWORDS,5) ':' SQLXA.I.SQLDATA
*/
/* ---------------------------------------------------- */
/* HANDLE ENGLISH WORDS THAT CHANGED POS DUE TO REVERSE */
/* ---------------------------------------------------- */
WORDF = ''
CNTF = 0
DO IV = 1 TO NUMWORDS
WORDC = WORD(SQLXA.I.SQLDATA,IV)
IF (VERIFY(WORDC,ENGLISH) = 0) THEN DO
WORDF = WORDF WORDC
CNTF = CNTF + 1
ITERATE
END
LEAVE
END
WORDL = ''
CNTL = 0
DO IV = NUMWORDS TO 1 BY -1
WORDC = WORD(SQLXA.I.SQLDATA,IV)
IF (VERIFY(WORDC,ENGLISH) = 0) THEN DO
WORDL = WORDL WORDC
CNTL = CNTL + 1
ITERATE
END
LEAVE
END
WORDF = STRIP(WORDF)
WORDL = STRIP(WORDL)
IF (CNTF = NUMWORDS) THEN DO
/* ------------------------------------------- */
/* STRINGIS ALL ENGLISH... */
/* ------------------------------------------- */
RETURN
END
/* WORDF = WORD(SQLXA.I.SQLDATA,1)
WORDL = WORD(SQLXA.I.SQLDATA,NUMWORDS)
*/
IF (NUMWORDS = 2) THEN DO
/* ------------------------------------------------- */
/* HANDLE STRING WITH ONLY TWO WORDS */
/* ------------------------------------------------- */
IF (WORDF ^= '') THEN DO
IF (WORDL ^= '') THEN DO
SQLXA.I.SQLDATA = STRIP(WORDL WORDF)
RETURN
END
SQLXA.I.SQLDATA = STRIP(WORDL WORDF)
RETURN
END
IF (WORDL ^= '') THEN DO
SQLXA.I.SQLDATA = STRIP(WORDL WORD(SQLXA.I.SQLDATA,1))
RETURN
END
SQLXA.I.SQLDATA = STRIP(SQLXA.I.SQLDATA)
RETURN
END
IF (WORDL ^= '') THEN DO
/* ----------------------------------- */
/* LAST WORD IS ENGLISH, MOVE TO FIRST */
/* ----------------------------------- */
IF (WORDF ^= '') THEN DO
/* ----------------------------------- */
/* FIRST WORD IS ENGLISH, MOVE TO LAST */
/* ----------------------------------- */
WORDX = NUMWORDS - CNTF - CNTL
SQLXA.I.SQLDATA = WORDL ,
SUBWORD(SQLXA.I.SQLDATA,CNTF+1,WORDX) ,
WORDF
SQLXA.I.SQLDATA = STRIP(SQLXA.I.SQLDATA)
RETURN
END
WORDX = NUMWORDS - CNTL
SQLXA.I.SQLDATA = WORDL ,
SUBWORD(SQLXA.I.SQLDATA,1,WORDX)
SQLXA.I.SQLDATA = STRIP(SQLXA.I.SQLDATA)
RETURN
END
IF (WORDF ^= '') THEN DO
/* ----------------------------------- */
/* FIRST WORD IS ENGLISH, MOVE TO LAST */
/* ----------------------------------- */
SQLXA.I.SQLDATA = SUBWORD(SQLXA.I.SQLDATA,CNTF+1) WORDF
SQLXA.I.SQLDATA = STRIP(SQLXA.I.SQLDATA)
RETURN
END
SQLXA.I.SQLDATA = STRIP(SQLXA.I.SQLDATA)
SQLXA.I.SQLDATA = SUBSTR(SQLXA.I.SQLDATA' ',1,KEEPLENGTH)
RETURN
ITschak Mugzach
*|** IronSphere Platform* *|* *Information Security Continuous Monitoring
for z/OS, x/Linux & IBM I **| z/VM coming soon *
On Thu, Feb 9, 2023 at 5:43 PM Paul Gorlinsky <[email protected]> wrote:
> You already have tools on zOS DB2 to do this ...
>
> SELECT LASTNAME, X'05',
> FIRSTNAME, X'05'
> DEPARTMENT, X'05'
> STATUSDATE
>
> using SPUFI
>
> Use multiple SPUFI jobs.
>
> One to list the columns of the tables you're interested in; REXX to
> reformat that output in to more SELECT statements with tabs or double
> quotes as need...
>
> or use DSNTIAUL https://ibmmainframes.com/about9681-0.html
>
> here are some other suggestions: https://ibmmainframes.com/about62292.html
>
> Good luck ...
>
> ----------------------------------------------------------------------
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to [email protected] with the message: INFO IBM-MAIN
>
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to [email protected] with the message: INFO IBM-MAIN
