/* Rexx Generate Idcams DELETE statements */ /* AUTHOR: RICH FAZIO / Bob Treski */ /* WRITTEN: 2004-11-05 */ /* Published: 2004-11-05 V1.0 */ /*-------------------------------------------------------------------*/ /* Return code explanation: */ /* 0 - No issues...All objects are present and follow conventions */ /* 4 - Some action is required...but no serious errors */ /* 5 - Intervention required to completely resolve situation */ /* 7 - Serious 'soft' error has occurred */ /* 8 - Hard Error */ /* */ /* //TSDROP EXEC PGM=IKJEFT1B,REGION=3M, */ /* // PARM='TSDROP DBT5' */ /* //STEPLIB DD DISP=SHR,DSN=xxxxxxxx.SDSNEXIT */ /* // DD DISP=SHR,DSN=xxxxxxxx.SDSNLOAD */ /* //SYSEXEC DD DISP=SHR,DSN=your.rexx.library */ /* //SYSTSPRT DD SYSOUT=* */ /* //SYSPRINT DD SYSOUT=* */ /* //SYSUDUMP DD SYSOUT=* */ /* //SYSTSIN DD DUMMY */ /* //TSDROP DD DSN=your.output.dataset.here, */ /* // DISP=(MOD,CATLG),SPACE=(CYL,(5,15),),UNIT=SYSDA */ /* //WORKLIST DD * */ /* dbname.tsname I'm a comment */ /* dbn_me.ts_ame Wildcarding here */ /* dbna%.% Lot's of objects dropped here */ /*-------------------------------------------------------------------*/ PARSE UPPER ARG MAIN_ARGS Say "RFMI0001 - TSDROP Args " MAIN_ARGS Call INITIALIZE /* HOUSEKEEPING */ Call PROCESS /* Master Process loop */ Call TERMINATION /* END OF TASK PROCESSING */ Return; /*-------------------------------------------------------------------*/ /* Generate "DROP" JCL for the intended list (or parm) */ /*-------------------------------------------------------------------*/ PROCESS: Do WORK_ID = 1 to WORK_LIST.0 DB_NME = Strip(Word(WORK_LIST.WORK_ID,1)) /* DB in 1st word */ TS_NME = Strip(Word(WORK_LIST.WORK_ID,2)) /* TS in 2nd word */ Say "RFMI0101 - TSDROP Start of process: DROP TS Mask accepted: ", DB_NME||"."||TS_NME Call PROCESS_TS /* Check for Tablespaces to delete */ Call PROCESS_IX /* Check for Indexes to delete */ Say "RFMI0102 - TSDROP End of process: Drop JCL Built for ", DB_NME||"."||TS_NME End Return; /*-----------------------------------------------------*/ /* Pull in Tablespace Dataset information */ /*-----------------------------------------------------*/ PROCESS_TS: SQLSTMT = "" /* INITIALIZE SQLSTMT TEXT */ SQLSTMT.0 = 9 /* SET NUMBER OF LINES IN SQL STMT */ SQLSTMT.1 = "SELECT TP.VCATNAME, TP.DBNAME, TP.TSNAME " SQLSTMT.2 = " , TP.IPREFIX, TP.PARTITION, TB.CREATOR, TB.NAME " SQLSTMT.3 = " FROM SYSIBM.SYSTABLEPART TP " SQLSTMT.4 = " , SYSIBM.SYSTABLES TB " SQLSTMT.5 = " WHERE TB.DBNAME LIKE '"||Strip(DB_NME)||"'" SQLSTMT.6 = " AND TB.TSNAME LIKE '"||Strip(TS_NME)||"'" SQLSTMT.7 = " AND TB.DBNAME = TP.DBNAME " SQLSTMT.8 = " AND TB.TSNAME = TP.TSNAME " SQLSTMT.9 = " AND TB.TYPE = 'T' " Do X = 1 TO SQLSTMT.0;SQLSTMT = SQLSTMT||" "||SQLSTMT.X;END /* */ Address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT" /* PREPARE ABOVE STMT */ If SQLCODE \= "0" Then Do /* ERROR OCCURRED */ ERROR_NOTE = "PREPARE CURSOR FAILED " ERROR_AID = " NONE " Call SQLERR_RTN_EXIT End Address DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1" /* DECLARE CURSOR */ If SQLCODE \= "0" Then Do /* ERROR OCCURRED */ ERROR_NOTE = "DECLARE CURSOR FAILED " ERROR_AID = " NONE " Call SQLERR_RTN_EXIT End Address DSNREXX "EXECSQL OPEN C1" /* OPEN CURSOR */ If SQLCODE \= "0" Then Do /* ERROR OCCURRED */ ERROR_NOTE = "OPEN CURSOR FAILED " ERROR_AID = " NONE " Call SQLERR_RTN_EXIT End /*-------------------------------------------------------------------*/ TSI_EOF_FLG = "X" /* Default - no data retrieved */ Do Until TSI_EOF_FLG = "Y" /* Process all open requests */ Address DSNREXX "EXECSQL FETCH C1 INTO :TS_VCATNAME, :TS_DBNAME, ", ":TS_TSNAME,:TS_IPREFIX,:TS_PARTITION, ", ":TB_CREATOR, :TB_NAME " If SQLCODE = 0 Then, Do TSI_EOF_FLG = "N" /* Some data retrieved-not EOF */ If TS_PARTITION < 2 Then Do /* Want to stop the whole TS */ /* Generate -STOP DATABASE command */ Queue "-STOP DB("||Strip(TS_DBNAME)||") SPACE(", ||Strip(TS_TSNAME)||")" X = DROPSTEM.0 /* Save Stem Position */ X = X + 1 /* Bump Stem By +1 */ /* Generate Comment of table involved in DROP */ DROPSTEM.X = "-- The following DROP is for this table: ", Strip(TB_CREATOR)||"."||Strip(TB_NAME) X = X + 1 /* Bump Stem By +2 */ DROPSTEM.0 = X /* Save stem value */ /* Generate DROP TABLESPACE command */ DROPSTEM.X = " DROP TABLESPACE "||TS_DBNAME||"."TS_TSNAME||";" End If TS_PARTITION = 0 Then TS_PARTITION = 1 /* Segmented gets "1"*/ DSNAME = Strip(TS_VCATNAME)||".DSNDBC.", /* Build DSNAME */ ||Strip(TS_DBNAME)||".", ||Strip(TS_TSNAME)||".", ||Strip(TS_IPREFIX)||"0001.A", ||Right(TS_PARTITION,3,"0") X = VDELSTEM.0 /* Save Stem Position */ X = X + 1 /* Bump Stem By +1 */ VDELSTEM.0 = X /* Save stem value */ VDELSTEM.X = " DELETE ("||DSNAME||")" /* Build Delete stmt */ End ELSE, If SQLCODE = +100 Then, Do If TSI_EOF_FLG = "X" Then, /* Soft error....no work to do */ Do Say "RFMW0201 - TSDROP No work to process" Say " There are no Tablespaces matching", " the input criteria - Skipping " If ERROR_LEVEL < 4 Then, /* No work to process */ ERROR_LEVEL = 4 /* "Warning" Level Set */ TSI_EOF_FLG = "Y" /* Forced EOF .................*/ End Else TSI_EOF_FLG = "Y" /* Normal EOF .................*/ END Else, Do ERROR_NOTE = "SELECT Table name failed" ERROR_AID = " NONE " Call SQLERR_RTN_EXIT End End Address DSNREXX "EXECSQL CLOSE C1" /* CLOSE CURSOR */ Return; /*-----------------------------------------------------*/ /* Pull in Indexspace Dataset information */ /*-----------------------------------------------------*/ PROCESS_IX: SQLSTMT = "" /* INITIALIZE SQLSTMT TEXT */ SQLSTMT.0 = 11 /* SET NUMBER OF LINES IN SQL STMT */ SQLSTMT.1 = "SELECT VCATNAME, IX.DBNAME " SQLSTMT.2 = " , INDEXSPACE, IPREFIX, PARTITION " SQLSTMT.3 = " FROM SYSIBM.SYSINDEXES IX " SQLSTMT.4 = " INNER JOIN SYSIBM.SYSTABLES TB " SQLSTMT.5 = " ON TB.NAME = IX.TBNAME " SQLSTMT.6 = " AND TB.CREATOR = IX.TBCREATOR " SQLSTMT.7 = " INNER JOIN SYSIBM.SYSINDEXPART IP " SQLSTMT.8 = " ON IX.NAME = IP.IXNAME " SQLSTMT.9 = " AND IX.CREATOR = IP.IXCREATOR " SQLSTMT.10 = " WHERE TB.DBNAME LIKE '"||Strip(DB_NME)||"'" SQLSTMT.11 = " AND TB.TSNAME LIKE '"||Strip(TS_NME)||"'" Do X = 1 TO SQLSTMT.0;SQLSTMT = SQLSTMT||" "||SQLSTMT.X;END /* */ Address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT" /* PREPARE ABOVE STMT */ If SQLCODE \= "0" Then Do /* ERROR OCCURRED */ ERROR_NOTE = "PREPARE CURSOR FAILED " ERROR_AID = " NONE " Call SQLERR_RTN_EXIT End Address DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1" /* DECLARE CURSOR */ If SQLCODE \= "0" Then Do /* ERROR OCCURRED */ ERROR_NOTE = "DECLARE CURSOR FAILED " ERROR_AID = " NONE " Call SQLERR_RTN_EXIT End Address DSNREXX "EXECSQL OPEN C1" /* OPEN CURSOR */ If SQLCODE \= "0" Then Do /* ERROR OCCURRED */ ERROR_NOTE = "OPEN CURSOR FAILED " ERROR_AID = " NONE " Call SQLERR_RTN_EXIT End /*-------------------------------------------------------------------*/ IXI_EOF_FLG = "X" /* Default - no data retrieved */ Do Until IXI_EOF_FLG = "Y" /* Process all open requests */ Address DSNREXX "EXECSQL FETCH C1 INTO :IX_VCATNAME, :IX_DBNAME, ", ":IX_IXSPACE,:IX_IPREFIX,:IX_PARTITION " If SQLCODE = 0 Then, Do IXI_EOF_FLG = "N" /* Some data retrieved-not EOF */ If IX_PARTITION < 2 Then Do /* Want to stop the whole IX */ Queue "-STOP DB("||Strip(IX_DBNAME)||") SPACE(", ||Strip(IX_IXSPACE)||")" End If IX_PARTITION = 0 Then IX_PARTITION = 1 /* Segmented gets "1"*/ DSNAME = Strip(IX_VCATNAME)||".DSNDBC.", /* Build DSNAME */ ||Strip(IX_DBNAME)||".", ||Strip(IX_IXSPACE)||".", ||Strip(IX_IPREFIX)||"0001.A", ||Right(IX_PARTITION,3,"0") X = VDELSTEM.0 /* Save Stem Position */ X = X + 1 /* Bump Stem By +1 */ VDELSTEM.0 = X /* Save stem value */ VDELSTEM.X = " DELETE ("||DSNAME||")" /* Build Delete stmt */ End ELSE, If SQLCODE = +100 Then, Do If IXI_EOF_FLG = "X" Then, /* Soft error....no work to do */ Do Say "RFMW0201 - TSDROP No work to process" Say " There are no Indexes for these objects", " (If any)" If ERROR_LEVEL < 4 Then, /* No work to process */ ERROR_LEVEL = 4 /* "Warning" Level Set */ IXI_EOF_FLG = "Y" /* Forced EOF .................*/ End Else IXI_EOF_FLG = "Y" /* Normal EOF .................*/ END Else, Do ERROR_NOTE = "SELECT Table name failed" ERROR_AID = " NONE " Call SQLERR_RTN_EXIT End End CLOSE_CURSOR: Address DSNREXX "EXECSQL CLOSE C1" /* CLOSE CURSOR */ Return; /*-------------------------------------------------------------------*/ /* END OF TASK PROCESSING */ /*-------------------------------------------------------------------*/ TERMINATION: Address DSNREXX "DISCONNECT" /* CUT REXX/ DB2 LINK */ S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX') Queue "END " Queue "/* " Queue "" /* TERMINATE STACK */ Address MVS "EXECIO * DISKW TSDROP " /* DUMP STACK TO FILE */ /* ----------------------------------------------------------------- */ /* Now - Dump the stem with all the delete jcl/stmts */ /* ----------------------------------------------------------------- */ Address MVS "EXECIO * DISKW TSDROP (STEM VDELSTEM. )" /* ----------------------------------------------------------------- */ /* Lastly - Dump the stem with all the DROP TABLESPACE SQL */ /* ----------------------------------------------------------------- */ X = DROPSTEM.0 /* Save Stem Position */ X = X + 1 /* Bump Stem By +1 */ DROPSTEM.0 = X /* Save stem value */ DROPSTEM.X = "/* " /* Steam terminator */ Address MVS "EXECIO * DISKW TSDROP (STEM DROPSTEM. FINIS)" Say "RFMI9999 - TSDROP Process Completed!" Exit(ERROR_LEVEL) Return; /*-------------------------------------------------------------------*/ /* START OF TASK PROCESSING */ /*-------------------------------------------------------------------*/ INITIALIZE: ERROR_LEVEL = 0 /* Default Error level - ok */ Address MVS "DELSTACK" Address MVS "NEWSTACK" NBR_PARMS = Words(MAIN_ARGS) /* Determin # of parms */ If NBR_PARMS < 1 Then, Do Say "RFMS0001 - TSDROP Critical Error " Say " Input parms missing" Say " Syntax: %TSDROP xxxx " Say " Where xxxx is the SSID " Say " Terminating" Address MVS "DELSTACK" Exit(8) End /*-------------------------------------------Default options - begin */ VERBOSE = "N" /* Chatty messages */ DEBUG = "N" /* Debug mode off */ USER_ID = SYSVAR(sysUID) /* Pull TSO Id */ /*---------------------------------------------Default options - End */ SSID = Word(MAIN_ARGS,1) /* Postn PARM... MUST BE 1st PARM */ Do I = 2 TO NBR_PARMS /* CHECK FOR KEYWORD PARMS */ PRM_VLD = "N" /* ASSUME PARM INVALID */ PARM.I = WORD(MAIN_ARGS,I) /* Pull OUT PARM */ /* Keyword parms can have sub operands surrounded by parens */ Parse VAR PARM.I KEY_WORD "(" SUB_OPT ")" If KEY_WORD = "VERBOSE" Then, Do;VERBOSE = "Y" ; PRM_VLD="Y";End If KEY_WORD = "DEBUG" Then, Do;DEBUG = "Y";debug code here = SUB_OPT; PRM_VLD="Y";End /* ----------------------------------------------------------------- */ /* Debug processing notes: (Not Used) */ /* By default, "debug" mode is off. */ /* If Debug is desired a parm "DEBUG(xx)" is specified. */ /* ----------------------------------------------------------------- */ If PRM_VLD = "N" Then Do Say "RFMI0010 - TSDROP Ignoring unrecognized keyword: ", KEY_WORD End End Call SSIDCHKR SSID /* Validate/Translate */ If RESULT = 8 Then Exit(8) /* Bad return code from SSIDCHKR - Exit */ SSID = WORD(RESULT,1) /* "RESULT" has validated SSID in it */ DB2_VERSION = WORD(RESULT,2) /* DB2 version is here */ MVS_SMFID = WORD(RESULT,3) /* Not used, just noting extra data */ Call SSIDLIBS SSID /* Get libraries used by this ssid */ If RESULT = 8 Then Exit(8) /* Check If error - bail */ SDSNLOAD = Word(RESULT,1) /* Pull out SDSNLOAD Lib */ SDSNEXIT = Word(RESULT,2) /* Pull out SDSNEXIT Lib */ RUNLIB = Word(RESULT,3) /* Pull out RUNLIB Lib */ Address MVS /* REXX/DB2 TALKING? CONNECT TO DB2 ENVIRONMENT */ "SUBCOM DSNREXX" If RC Then S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX') Say "RFMI0014 - TSDROP Requesting to Connect to DB2 Subsytem ", SSID Address DSNREXX "CONNECT" ""||SSID||"" If RC /= 0 Then Do /* ERROR? */ Say "RFMS0015 - TSDROP Failure to connect to database. RC: "||RC Say " SSID: " SSID Exit(8) End Call PRIME_STACK_STEMS /* Pre-load JCL/CNTL for each step */ Call BUILD_WORK_LIST /* Load the work list into STEM */ Return; /*-------------------------------------------------------------------*/ /* Pre-populate data stack/stem variables for to be built onto. */ /*-------------------------------------------------------------------*/ PRIME_STACK_STEMS: /* Start the data stack with JOBCARD/JCL to -STOP objects */ Queue "//"||USER_ID||"A JOB (CDB,0,T),'DATA SERVICES',CLASS=A, " Queue "// MSGCLASS=T,REGION=6M,TIME=1,NOTIFY=&SYSUID" Queue "//STOP EXEC PGM=IKJEFT1A " Queue "//STEPLIB DD DISP=SHR,DSN="||SDSNEXIT Queue "// DD DISP=SHR,DSN="||SDSNLOAD Queue "//SYSUDUMP DD SYSOUT=* " Queue "//SYSTSPRT DD SYSOUT=* " Queue "//SYSPRINT DD SYSOUT=* " Queue "//SYSIN DD DUMMY " Queue "//SYSTSIN DD * " Queue " DSN S("||SSID||")" /* Prime Stem variables to retain the IDCAMS delete statements */ VDELSTEM.0 = 3 /* Set up Stem to hold deletes */ VDELSTEM.1 = "//DELETE EXEC PGM=IDCAMS " VDELSTEM.2 = "//SYSPRINT DD SYSOUT=* " VDELSTEM.3 = "//SYSIN DD * " /* Prime Stem variables to retain the DROP TABLESPACE SQL Stmts */ DROPSTEM.0 = 8 /* Set up Stem to hold DROPs */ DROPSTEM.1 = "/* " /* 'End of stream' for prior step */ DROPSTEM.2 = "//DROPTS EXEC PGM=IRXJCL,PARM='SQLPROC "||SSID||"'" DROPSTEM.3 = "//STEPLIB DD DISP=SHR,DSN="SDSNEXIT DROPSTEM.4 = "// DD DISP=SHR,DSN="SDSNLOAD DROPSTEM.5 = "//SYSEXEC DD DISP=SHR,DSN=U0664.BATCH.REXX " DROPSTEM.6 = "//SYSTSPRT DD SYSOUT=* " DROPSTEM.7 = "//SYSPRINT DD SYSOUT=* " DROPSTEM.8 = "//SQLSTMT DD *" Return; /*-------------------------------------------------------------------*/ /* Build work list from input file */ /* 1) Read input list and store into Stem variable */ /* 2) Ensure a period (.) exists in input stream */ /*-------------------------------------------------------------------*/ BUILD_WORK_LIST: Address MVS "EXECIO * DISKR WORKLIST (STEM INPUT_TXT. FINIS)" /*Input*/ Say "RFMI0101 - TSDROP Start of process: Pre-Processor - ", "The following list has been accepted" Say " Database | Tablespace | Comments" Say "----------+------------+----------------------------------------" WORK_LIST.0 = INPUT_TXT.0 /* Preset number entries on worklist */ Do WORK_ID = 1 to INPUT_TXT.0 /* Process all input candidates */ X = POS(".",INPUT_TXT.WORK_ID) /* Check to see if period exists */ If X = 0 Then Do Say "RFMS0001 - TSDROP Expecting DBNAME.TSNAME ", "or masked value of DBNAME.TSNAME -Found:" INPUT_TXT.WORK_ID Say " Missing Period" Exit(8) End Parse VAR INPUT_TXT.WORK_ID DB_NME "." TS_NME " " COMMENT_TXT DB_NME = Strip(DB_NME,B) /* Remove any leading/trailing blanks */ TS_NME = Strip(TS_NME,B) /* Remove any leading/trailing blanks */ If Length(DB_NME) > 9 Then Do Say "RFMS0001 - TSDROP Expecting DBNAME.TSNAME ", "or masked value of DBNAME.TSNAME -Found:" INPUT_TXT.WORK_ID Say " Database Name is too long (9 max)" Exit(8) End If Length(TS_NME) > 9 Then Do Say "RFMS0001 - TSDROP Expecting DBNAME.TSNAME ", "or masked value of DBNAME.TSNAME -Found:" INPUT_TXT.WORK_ID Say " Tablespace Name is too long (9 max)" Exit(8) End WORK_LIST.WORK_ID = DB_NME||" "||TS_NME Say Left(DB_NME,10)||"| "||Left(TS_NME,11)||"|"||COMMENT_TXT End Say "----------+------------+----------------------------------------" Say "RFMI0102 - TSDROP End of process: Pre-processor" Return; /*-------------------------------------------------------------------*/ /* HARD ERROR- DISPLAY DOC; TERMINATE PROCESSING W /RC=8 */ /*-------------------------------------------------------------------*/ SQLERR_RTN_EXIT: Call SQLERR_RTN_DISPLAY Exit(8) Return; /*-------------------------------------------------------------------*/ /* SOFT ERROR- DISPLAY DOC; KEEP PROCESSING */ /*-------------------------------------------------------------------*/ SQLERR_RTN_DISPLAY: Say "RFMI0020 - TSDROP SQLCODE diagnostics are about to be displayed" Say "SQL STATEMENT RECEIVEING ERROR FOLLOWS" Say "--------------------------------------------------" Do X = 1 TO SQLSTMT.0;Say SQLSTMT.X;End Say "--------------------------------------------------" Say "APPLICATION DIAGNOSTICS" Say ERROR_NOTE Say ERROR_AID Say "DB2 DIAGNOSTICS FOLLOW:" Say "--------------------------------------------------" /* Package SQLCA for DSNTIAR usage */ NUMERIC DIGITS 10 /* Allow for big numbers in SQLCA */ SQL_ERRD = "";Do I = 1 To 6;SQL_ERRD = SQL_ERRD||D2C(SQLERRD.I,4);End SQL_WARN = "";Do I = 0 To 10;SQL_WARN = SQL_WARN||LEFT(SQLWARN.I,1);End SQLCA = 'SQLCA '||D2C(136,4)||D2C(SQLCODE,4)||D2C(70,2), ||LEFT(SQLERRMC,70)||'DSN '||SQL_ERRD||SQL_WARN||LEFT(SQLSTATE,5) /* If the length is beyond DSNTIAR possible values (72-240), reset */ If MSG_LEN < 72 | MSG_LEN > 240 Then MSG_LEN = 120 /* Outside scope */ If MSG_LEN = "MSG_LEN" Then MSG_LEN = 120 /* Default msg length 120 */ DB2_ERR_MSG = D2C(MSG_LEN * 12,2) || COPIES(' ',MSG_LEN * 12) DB2_ERR_LEN = D2C(MSG_LEN,4) Address /* Execute DSNTIAR program with SQLCA/Parm data */ Address LINKPGM "DSNTIAR SQLCA DB2_ERR_MSG DB2_ERR_LEN" If RC < 5 Then, Do If RC = 4 Then Say "DSNTIAR RC=4 Message Area Truncated" S_POS = 3 /* Bypass the length bytes in Message Area */ Do I = 1 to 12 /* Loop through all lines of message */ MSG_TEXT = Substr(DB2_ERR_MSG,S_POS,MSG_LEN) /* Pick out text */ MSG_TEXT = Strip(MSG_TEXT,T," ") /* Remove trailing blanks */ If MSG_TEXT > " " Then Say MSG_TEXT /* Echo msg to terminal */ Else Iterate /* Some msg lines are blank, skip */ S_POS = S_POS + MSG_LEN /* Skip to next "line" of MSG data */ End I End Else, Do /* If DSNTIAR fails for any reason, print SQLCA info anyway */ Say "Call to DSNTIAR - Failed. RC=" RC Say 'SQLCODE ='SQLCODE; Say 'SQLERRM ='SQLERRMC Say 'SQLERRP ='SQLERRP; Say 'SQLSTATE='SQLSTATE XX = "SQLERRD =";Do I = 1 to 6 ; XX = XX||SQLERRD.I||',';End;Say XX XX = "SQLWARN =";Do I = 0 to 10; XX = XX||SQLWARN.I||',';End;Say XX End Address Return;