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 <p...@atsmigrations.com> 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 lists...@listserv.ua.edu with the message: INFO IBM-MAIN > ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN