/* +-----------------------------------------------------------------+*/ /* ! !*/ /* ! Programm-Name : REORG.CMD !*/ /* ! !*/ /* ! Funktion : Reorganisieren Tabellen mit hilfe des REORGCHK !*/ /* ! Aufruf : REORG.CMD DBNAME !*/ /* ! !*/ /* ! !*/ /* ! !*/ /* ! Auther : Ulrich Braeuer !*/ /* ! Date written : 30.09.1994 !*/ /* ! !*/ /* ! !*/ /* ! Aenderungen !*/ /* ! !*/ /* ! Name : Datum : !*/ /* ! Aenderung : !*/ /* ! !*/ /* ! Name : Datum : !*/ /* ! Aenderung : !*/ /* ! !*/ /* ! !*/ /* ! !*/ /* ! !*/ /* +-----------------------------------------------------------------+*/ /* Laden REXX Utilities */ call RxFuncAdd 'SysLoadFuncs', 'rexxutil', 'SysLoadFuncs' call sysloadfuncs parse version rexxType . parse source platform . if platform == 'AIX/6000' & rexxType == 'REXXSAA' then do rcy = SysAddFuncPkg("db2rexx") End Else Do if Rxfuncquery('SQLDBS') <> 0 then rcy = rxfuncadd('SQLDBS', 'DB2AR', 'SQLDBS') if Rxfuncquery('SQLDB2') <> 0 then rcy = rxfuncadd('SQLDB2','DB2AR','SQLDB2') if Rxfuncquery('SQLEXEC') <> 0 then rcy = rxfuncadd('SQLEXEC','DB2AR','SQLEXEC') End Parse upper arg Datenbank Test . If Datenbank = "" Then Call Help Call INITPROG /* If Test <> "TEST" Then Call ReorgCheck */ Call ReorgCheck Work = "" Drop TableList. TableList.0 = 0 TBI = 0 do while lines(CHECK) > 0 esatz = TRANSLATE(linein(check)) If Substr(Esatz, 1, 20) = '--------------------' Then Do If Work = "TB" Then Do Work = "IX" /* leave */ end If Work = "" Then Work = "TB" End If Work = "" Then Iterate Select When Work = "TB" & Words(Esatz) > 10 Then Do TableList.0 = TableList.0 + 1 TBI = TableList.0 TableList.TBI.TableName = Word(ESatz, 1)"."Word(ESatz, 2) TableList.TBI.ReorgTable = "N" TableList.TBI.ReorgIndex = "N" Select When DBVersion < 8 Then Do Parse Value Esatz With SCHEMA , NAME , CARD , OV , NP , FP , TSIZE , F1 , F2 , F3 , REORG , Rest End Otherwise Do Parse Value Esatz With SCHEMA , NAME , CARD , OV , NP , FP , ACTBLK , TSIZE , F1 , F2 , F3 , REORG , Rest End End /* End Select */ If POS('*', Reorg) > 0 THEN DO TableList.TBI.ReorgTable = "Y" end End When Work = "IX" & Words(Esatz) = 2 Then Do Do TBI = 1 to TableList.0 If TableList.TBI.TableName = Word(ESatz, 2) Then Leave End TableList.TBI.IndexName.0 = 0 End When Work = "IX" & Words(Esatz) > 10 Then Do Select When DBVersion < 8 Then Do Parse Value Esatz With SCHEMA , NAME , CARD , LEAF , LVLS , ISIZE , KEYS , F4 , F5 , F6 , REORG , Rest End Otherwise Do Parse Value Esatz With SCHEMA , NAME , CARD , LEAF , ELEAF , LVLS , ISIZE , NDEL , KEYS , F4 , F5 , F6 , F7 , F8 , REORG , Rest End End /* End Select */ If POS('*', Reorg) > 0 THEN DO TableList.TBI.ReorgIndex = "Y" TableList.TBI.IndexName.0 = TableList.TBI.IndexName.0 + 1 IXI = TableList.TBI.IndexName.0 TableList.TBI.IndexName.IXI = Space(Schema "." Name, 0) end End Otherwise Iterate End end Drop ReorgList. ReorgList.0 = 0 Drop TBName Drop TSName Drop IXName CALL SQLEXEC "CONNECT TO" DATENBANK "USER "uid" using "pwd Do i = 1 To TableList.0 DOSome = "N" If TableList.I.ReorgTable = "Y" Then DOSome = "Y" /*Reorg Table */ If DBVersion > 7 & TableList.I.ReorgIndex = "Y" Then DOSome = "Y" /* Reorg Index Ab V8 */ If DBVersion < 8 & TableList.I.ReorgIndex = "Y" Then do /*Reorg Table when Index Reorg Until V7 */ DOSome = "Y" TableList.I.ReorgTable = "Y" End If DOSome = "N" Then Iterate TableList.I.TableName = Translate(TableList.I.TableName, ' ', '0D'x) TBName = Strip(TableList.I.TableName) Parse Value TableList.I.TableName With TB_CREATOR "." TB_TABELLE If TableList.I.ReorgTable = "Y" Then Do ReorgList.0 = ReorgList.0 + 1 RI = ReorgList.0 IXName = "" TSNAME = "" Call Index_Lesen Call TabeSpace_Lesen Stmt = BuildReorgTB() ReorgList.RI = BuildReorgTB() End If TableList.I.ReorgIndex = "Y" Then Do Stmt = BuildReorgIX() If Stmt > "" Then Do ReorgList.0 = ReorgList.0 + 1 RI = ReorgList.0 ReorgList.RI = Stmt ReorgList.RI = Space(ReorgList.RI, 1) End End ReorgList.0 = ReorgList.0 + 1 RI = ReorgList.0 ReorgList.RI = BuildRSTB() ReorgList.RI = Space(ReorgList.RI, 1) End CALL SQLEXEC "CONNECT RESET" CALL SQLDB2 "CONNECT TO" DATENBANK "USER "uid" using "pwd Do I = 1 To ReorgList.0 If Test <> "TEST" Then Call SQLDB2 ReorgList.i If Test <> "TEST" Then Do Say ReorgList.i " SQLCODE="Sqlca.Sqlcode"/"Sqlca.Sqlstate End Else Do Say "DB2 "ReorgList.i End If Sqlca.Sqlcode <> 0 Then Do Say SQLMSG End End CALL SQLDB2 "CONNECT RESET" Exit BuildReorgTB: Select When DBVersion < 8 Then Do If IXName = "" Then Do ReorgTB = " REORG TABLE " TBName " USE " TSTBSPACE End Else Do ReorgTB = " REORG TABLE " TBName , " Index "IXName" USE " TSTBSPACE End End Otherwise Do If IXName = "" Then Do ReorgTB = " REORG TABLE " TBName " USE " TSTBSPACE End Else Do ReorgTB = " REORG TABLE " TBName , " Index "IXName" ALLOW READ ACCESS USE " TSTBSPACE End End End /* End Select */ REORGTB = space(REORGTB, 1) Return REORGTB BuildReorgIX: Select When DBVersion < 8 Then Do REORGIX = "" End Otherwise Do REORGIX = " REORG INDEXES ALL FOR TABLE ", TBName , " ALLOW WRITE ACCESS" , "" End End /* End Select */ REORGIX = space(REORGIX, 1) Return REORGIX BuildRSTB: Select When DBVersion < 8 Then Do RUNSTATSTB = "RUNSTATS ON TABLE " TBName, "WITH DISTRIBUTION " , "AND DETAILED INDEXES ALL " , " SHRLEVEL CHANGE " , "" End Otherwise Do RUNSTATSTB = "RUNSTATS ON TABLE " TBName, "ON ALL COLUMNS " , "WITH DISTRIBUTION ON ALL COLUMNS " , "AND DETAILED INDEX ALL " , " SHRLEVEL CHANGE " , "" End End /* End Select */ RUNSTATSTB = Space(RUNSTATSTB, 1) Return RUNSTATSTB /******************************************************/ /* */ /* Find out the Temp-Tablespace for the Table */ /* */ /******************************************************/ TabeSpace_Lesen: call SQLEXEC 'CLOSE c2' STMT2 = "SELECT TS.PAGESIZE, TS.TBSPACE, TB1.CARD " , "FROM SYSIBM.SYSTABLESPACES TS " , " , SYSIBM.SYSTABLES TB1 " , "Where TB1.Creator = '"TB_CREATOR"' " , "And TB1.Name = '"TB_TABELLE"' " , "AND TS.Datatype = 'T' " , "AND ts.pagesize = (Select pagesize " , " From SYSIBM.SYSTABLESPACES TS2 " , " , SYSIBM.SYSTABLES TB2 " , " Where TB2.Creator = TB1.Creator " , " And TB2.Name = TB1.NAME " , " And TB2.TBSpace = TS2.TBSPACE " , " ) " , "FOR READ ONLY " , "WITH UR " , "" STMT = space(STMT2, 1) anw = " " call SQLEXEC 'PREPARE s2 FROM :STMT' if SQLCA.SQLCODE \= 0 then DO anw = 'PREPARE 3 ' Call ErrorSQL RETURN END call SQLEXEC 'DECLARE c2 CURSOR for s2' if SQLCA.SQLCODE \= 0 then DO anw = 'DECLARE 3 ' Call ErrorSQL RETURN END call SQLEXEC 'OPEN c2' if SQLCA.SQLCODE \= 0 then DO anw = 'Open Cursor 3 ' Call ErrorSQL RETURN END call SQLEXEC 'FETCH c2 into :TSPAGESIZE, :TSTBSPACE, :TBCARD ' anw = 'Fetch 3 ' /* Say TBSPACE TSPAGESIZE TSTBSPACE */ if SQLCA.SQLCODE = 100 then do TBS_TABELLE.Uind.TEMPSPACE = TBSPACE TBS_TABELLE.Uind.CARD = 0 end Else do TBS_TABELLE.Uind.TEMPSPACE = TSTBSPACE TBS_TABELLE.Uind.CARD = TBCARD end call SQLEXEC 'CLOSE c2' Return /******************************************************/ /* */ /* Find out a good index for the Clustering */ /* */ /******************************************************/ Index_Lesen: call SQLEXEC 'CLOSE c1' PREP1 = "SELECT CREATOR, NAME " , ", CASE WHEN INDEXTYPE = 'CLUS' THEN 1 " , " WHEN UNIQUERULE = 'P' THEN 2 " , " WHEN UNIQUERULE = 'U' THEN 3 " , " WHEN UNIQUERULE = 'D' THEN 4 " , "ELSE 4 END AS INDEXSORT " , "FROM SYSIBM.SYSINDEXES " , "Where TBCREATOR = '"TB_CREATOR"' " , "AND TBNAME = '"TB_TABELLE"' " , "And UNIQUERULE in ('P', 'U', 'D') " , "ORDER BY 3, 2, 1 " , "FOR READ ONLY " , "WITH UR " , "" STMTBUF = Space(PREP1, 1) anw = ' ' TBS_INDEX.TBSI.Index.0 = 0 call SQLEXEC 'PREPARE s1 FROM :stmtbuf' if SQLCA.SQLCODE \= 0 then DO anw = 'PREPARE 2 ' Call ErrorSQL RETURN END call SQLEXEC 'DECLARE c1 CURSOR for s1' if SQLCA.SQLCODE \= 0 then DO anw = 'DECLARE 2 ' Call ErrorSQL RETURN END call SQLEXEC 'OPEN c1' if SQLCA.SQLCODE \= 0 then DO anw = 'Open Cursor 2' Call ErrorSQL RETURN END call SQLEXEC 'FETCH c1 into :CREATOR, :NAME, :TEMP ' anw = 'Fetch ' if SQLCA.SQLCODE = 100 then do call SQLEXEC 'CLOSE c1' TBS_INDEX.TBSI.Index.0 = 0 RETURN end if SQLCA.SQLCODE \= 0 then signal ErrorSql IXName = Space(Creator "." Name, 0) call SQLEXEC 'CLOSE c1' Return ReConnect: CALL SQLDB2 'CONNECT RESET' CALL SQLDB2 'CONNECT TO' DATENBANK 'USER 'uid' using 'pwd Return /******************************************************/ /* */ /* Show the Sqlerror */ /* */ /******************************************************/ ErrorSQL: /* call beep 220,1000 */ say say '**** SQL Error code from Statement :' say ' SQLCODE = ' SQLCA.SQLCODE say ' Nachricht = ' SQLMSG say 'anweisung ' anw say EXIT /******************************************************/ /* */ /* Init the variables */ /* */ /******************************************************/ INITPROG: parse source SourceInfo parse Value SourceInfo With Art Aufruf Programm INIDAT = GetIniDat.Rex() /* Pruefen ob definitionen fr Datenbank bestehen */ /* Definitionen der Datenbank stehen in der Datei */ /* C:\DBSERVER.INI */ /* Aus Alias wird der Datenbankname ermitteld */ SAVEDB = Datenbank Datenbank = '' Datenbank = SysIni(inidat, SAVEDB, 'STDDB') IF datenbank = '' | , Datenbank = 'ERROR:' THEN DO Say "Datenbank ist Unbekannt, " say "bitte Uli Braeuer benachrichtigen " EXIT End uid = SysIni(inidat, Datenbank, 'UID') pwd = SysIni(inidat, Datenbank, 'pwd') CHECK = SysIni(inidat, 'ALLGEMEIN', 'REORGAUS') CHECK = strip(check'\'Datenbank'.RPT') parse Value FILESPEC("name",Programm) with name '.' extention CALL SQLDB2 "CONNECT TO "DATENBANK" USER "uid "USING "pwd parse value sqlca.sqlerrp with ERRPDBProduct , 4 ERRPDBVersion , 6 ERRPDBRelease , 8 ERRPDBModifikation DBVersion = ERRPDBVersion * 1 Call Drop_Chck_Table Call Create_Chck_Table CALL SQLDB2 "CONNECT RESET" RETURN ReorgCheck: rc = SysFileDelete(CHECK) Say "INfile="CHECK CALL SQLDB2 "CONNECT TO "DATENBANK" USER "uid "USING "pwd CALL SQLDB2 "-R "CHECK" REORGCHK CURRENT STATISTICS ON TABLE ALL" CALL SQLDB2 "CONNECT RESET" Return Drop_Chck_Table: CALL SQLDB2 "CONNECT TO "DATENBANK" USER "uid "USING "pwd CTB = "DROP TABLE REORGFROMCHECKV8 " CALL SQLDB2 CTB CALL SQLDB2 "CONNECT RESET" Return Create_Chck_Table: CALL SQLDB2 "CONNECT TO "DATENBANK" USER "uid "USING "pwd CTB = "CREATE TABLE REORGFROMCHECKV8 ", "(TYPE CHARACTER(20), ", "SCHEMA CHARACTER(20), ", "NAME CHARACTER(40), ", "CARD INTEGER, ", "OV INTEGER, ", "NP INTEGER, ", "FP INTEGER, ", "ACTBLK INTEGER, ", "TSIZE DOUBLE, ", "F1 INTEGER, ", "F2 INTEGER, ", "F3 INTEGER, ", "LEAF INTEGER, ", "ELEAF INTEGER, ", "LVLS INTEGER, ", "ISIZE INTEGER, ", "NDEL INTEGER, ", "KEYS INTEGER, ", "F4 INTEGER, ", "F5 INTEGER, ", "F6 INTEGER, ", "F7 INTEGER, ", "F8 INTEGER, ", "REORG CHARACTER(4) ", ") " CALL SQLDB2 CTB CALL SQLDB2 "CONNECT RESET" Return /******************************************************/ /* */ /* Help for the programm */ /* */ /******************************************************/ HELP: Say "ReorgFromCheckV8 DB " Say " " Say "Reorg Tables from a ReorgCheck " Say "DB IS ) The Database name " Exit