/*** +-------------------------REXX-----------------------------------+ ***/ /* PROGRAMMNAME : GenRestore.Rex */ /* AUSRUF : AUS Command line FUER DB2 LUW */ /* AUTHER : ULRICH BRAEUER 25.09.2001 */ /* FUNKTION : Erstellen Restore Database */ /* Rexx GenRestore.Rex */ /* Oder */ /* Rexx GenRestore.Rex <-d Timestamp> (20100510165500) */ /* */ /**************************************************************************/ call RxFuncAdd "SysLoadFuncs", "rexxutil", "SysLoadFuncs" call sysloadfuncs parse version rexxType . parse source platform . UPlatform = Translate(platform) back = "/" /* Fuer UNIX/LINUX */ If uplatform = "WINDOWSNT" Then back = "\" /* FUER WINDOWS */ 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 Parms PARSE ARG DATENBANK Parmso If Pos('-', Datenbank, 1) > 0 Then do PARSE UPPER ARG Parms PARSE ARG Parmso End Call Get_Parms If HELP = "HELP" Then do Call Help exit End Call Init Call Get_DBINFO DROP AUSLIST. AUSLIST.0 = 0 ODBNAME = STRIP(ODBNAME) Call Get_BackUp_Directory STMT = "select DBPARTITIONNUM, TYPE, PATH " , "from SYSIBMADM.DBPATHS " , "Where TYPE = 'DB_STORAGE_PATH' " , "" DB_PATH = "" If DBVersion > 8 Then Do Call Open_Cursor call SQLEXEC "FETCH c1 into :DBPARTITIONNUM " , " , :TYPE " , " , :DB_PATH " , "" If Sqlca.sqlcode = +100 Then DB_Path = "" Call Close_Cursor End STMT = "SELECT SNAPSHOT_TIMESTAMP " , " , CHAR(TABLESPACE_ID) AS TABLESPACE_ID " , " , TABLESPACE_NAME " , " , INT(CONTAINER_ID) AS CONTAINER_ID " , " , CONTAINER_NAME " , " , CASE WHEN INT(CONTAINER_TYPE) = 6 THEN 'FILE' " , " ELSE 'PATH' " , " END AS CTYPE " , " , INT(TOTAL_PAGES) AS TOTAL_PAGES " , " , INT(USABLE_PAGES) AS USABLE_PAGES " , " , INT(ACCESSIBLE) AS ACCESSIBLE " , " , INT(STRIPE_SET) AS STRIPE_SET " , " , T2.DATATYPE AS DATATYPE " , "FROM TABLE(SNAPSHOT_CONTAINER('"ODBNAME"',-1)) T " , "INNER JOIN SYSIBM.SYSTABLESPACES T2 " , "ON T2.TBSPACEID = T.TABLESPACE_ID " , "ORDER BY int(TABLESPACE_ID), CONTAINER_ID " , "" Call Get_TS_INfo /* Parse Value LastBackupPath with Drive '\' pfad '\' . */ Parse Value LastBackupPath with Drive Value(back) pfad Value(back) . If LASTBACKUPTIME = "LASTBACKUPTIME" Then Do LASTBACKUPTIME = 'YYYYMMDDHHMMSS' DRIVE = "C:" Pfad = ODBNAME End Call DBCFG_Info OK = ADDLIST("RESTORE DATABASE "ODBNAME) If Length(PASSWD) > 0 & Length(USER) > 0 Then do /* OK = ADDLIST(" USER "USER " USING " PASSWD ) */ OK = ADDLIST(" USER "USER ) End If TABLESPACE = "TABLESPACE" Then Do OK = ADDLIST(" TABLESPACE ("TSNames") ") End If ONLINE = "ONLINE" Then Do OK = ADDLIST(" ONLINE ") End If IncementalBackup = "Y" Then Do /* Incemental or Delta */ OK = ADDLIST(" INCREMENTAL AUTOMATIC") End x = Translate(LastBackupPath) Select When POS('DB2TSM', X, 1) > 0 Then Do OK = ADDLIST(" USE TSM ") End When POS('.DLL', X, 1) > 0 Then Do OK = ADDLIST(" LOAD "LastBackupPath) End When Length(FromDir) > 0 Then do OK = ADDLIST(" FROM "FromDir) End Otherwise do OK = ADDLIST(" FROM "BackDir) end End /* End Select */ OK = ADDLIST(" TAKEN AT "Substr(LASTBACKUPTIME, 1, 14)) If OnlineBackup = "Y" Then do OK = ADDLIST(" LOGTARGET "DB_LOGPATH) End If Length(DB_PATH) > 0 & REDIRECT = "REDIRECT" Then do OK = ADDLIST(" ON "DB_PATH ) OK = ADDLIST(" DBPATH ON "DB_PATH) End OK = ADDLIST("-- TO "Drive ) OK = ADDLIST("-- INTO "ODBNAME) If OnlineBackup = "Y" Then do OK = ADDLIST-- NEWLOGPATH "DB_LOGPATH) End Else Do OK = ADDLIST("-- NEWLOGPATH "DB_LOGPATH) End OK = ADDLIST(" WITH 2 BUFFERS ") OK = ADDLIST(" BUFFER 1024 ") If REDIRECT = "REDIRECT" Then do OK = ADDLIST("-- REPLACE EXISTING ") OK = ADDLIST(" REDIRECT ") OK = ADDLIST("-- GENERATE SCRIPT D:\Redirect.sql ") End Else do OK = ADDLIST(" REPLACE EXISTING ") End If OnlineBackup <> "Y" Then OK = ADDLIST(" WITHOUT ROLLING FORWARD ") OK = ADDLIST(" WITHOUT PROMPTING ") OK = ADDLIST(" ; ") If REDIRECT = "REDIRECT" Then do Do I = 1 TO TSNAMES.0 AUSGABE = "SET TABLESPACE CONTAINERS FOR "Word(TSNAMES.i, 1) , "IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (" , Word(TSNAMES.i, 2)" " Do y = 1 TO TSNAMES.I.FILE.0 AUSGABE = AUSGABE""TSNAMES.I.FILE.Y IF y = TSNAMES.I.FILE.0 THEN AUSGABE = AUSGABE ") ;" ELSE AUSGABE = AUSGABE ", " End OK = ADDLIST(AUSGABE) End OK = ADDLIST("RESTORE DATABASE "ODBNAME" CONTINUE;") End If OnlineBackup = "Y" Then do OK = ADDLIST("ROLLFORWARD DATABASE "ODBNAME" TO END OF LOGS AND STOP OVERFLOW LOG PATH ("Temp_Dir");") End If Tablespace = "TABLESPACE" Then Do TSNames = Translate(TSNames, ' ', ',') Do I = 1 to Words(TSNames) TSNAME = TSNAMES.I.TSN OK = ADDLIST("ROLLFORWARD DATABASE "ODBNAME" TO END OF LOGS AND COMPLETE TABLESPACE("TSNAME") "ONLINE";") End End call sqlexec "CONNECT RESET" Call SQLDB2 "DETACH" Return OPEN_CURSOR: call SQLEXEC "PREPARE s1 FROM :STMT" if SQLCA.SQLCODE \= 0 then DO anw = "PREPARE " Call ErrorSQL RETURN END call SQLEXEC "DECLARE c1 CURSOR for s1" if SQLCA.SQLCODE \= 0 then DO anw = "DECLARE " Call ErrorSQL RETURN END call SQLEXEC "OPEN c1" if SQLCA.SQLCODE \= 0 then DO anw = "Open Cursor " Call ErrorSQL RETURN END Return Get_TS_INfo: CALL OPEN_CURSOR DROP TSNAMES. TSNAMES.0 = 0 OID = 99999999999 Do while SQLCA.SQLCODE = 0 CALL FETCH_CURSOR If SQLCA.SQLCODE <> 0 Then Leave IF OID <> STRIP(TABLESPACE_ID) THEN DO OID = STRIP(TABLESPACE_ID) TSNAMES.0 = TSNAMES.0 + 1 OIND = TSNAMES.0 TSNAMES.OIND = STRIP(TABLESPACE_ID) OFI = 0 DROP TSNAMES.OIND.FILE. END OFI = OFI + 1 TSNAMES.OIND.FILE.0 = OFI TSNAMES.OIND.TSN = TABLESPACE_NAME TSNAMES.OIND.FILE.OFI = STRIP(CTYPE) STRIP(CONTAINER_NAME) If STRIP(CTYPE) = 'FILE' THEN DO TSNAMES.OIND.FILE.OFI = TSNAMES.OIND.FILE.OFI STRIP(TOTAL_PAGES) End End Return FETCH_CURSOR: call SQLEXEC "FETCH c1 into :SNAPSHOT_TIMESTAMP " , " , :TABLESPACE_ID " , " , :TABLESPACE_NAME " , " , :CONTAINER_ID " , " , :CONTAINER_NAME " , " , :CTYPE " , " , :TOTAL_PAGES " , " , :USABLE_PAGES " , " , :ACCESSIBLE " , " , :STRIPE_SET " , " , :DATATYPE " , "" anw = "Fetch " if SQLCA.SQLCODE = 100 then do RETURN end if SQLCA.SQLCODE \= 0 then signal ErrorSql Return CLOSE_CURSOR: call SQLEXEC "CLOSE c1" RETURN ADDLIST: AUSLIST.0 = AUSLIST.0 + 1 AIX = AUSLIST.0 AUSLIST.AIX = ARG(1) SAY AUSLIST.AIX If Length(OUT) > 0 Then Do Call LineOut OUT, AUSLIST.AIX OK = STREAM(DSN,"COMMAND","CLOSE") End RETURN 0 Init: If Length(db) > 0 Then Datenbank = DB INIDAT = GetIniDat.Rex() If Length(USER) > 0 & Length(Datenbank) > 0 Then Do if Length(PASSWD) = 0 Then PASSWD = GetPwd() pwd = PASSWD UID = USER CALL SQLEXEC 'CONNECT TO' DATENBANK 'USER 'uid' using 'pwd If sqlca.Sqlcode <> 0 Then do Say "Fehler beim Connect der Datenbank oder Datenbank ist Unbekannt " Say "SQLCODE= "sqlca.Sqlcode "/" "SQLSTATE= "sqlca.Sqlstate SQLNR = "SQL"sqlca.sqlcode If sqlca.sqlcode < 0 Then SQLNR = "SQL"sqlca.sqlcode*-1 "rxqueue /CLEAR" "db2 ? "SQLNR" | rxqueue " do queued() Pull Zeile Say zeile End EXIT End CALL SQLEXEC 'CONNECT RESET' End Else Do SAVEDB = Datenbank Datenbank = "" Datenbank = SysIni.Rex(inidat, SAVEDB, "STDDB") IF datenbank = "" | , Datenbank = "ERROR:" THEN DO Say "Datenbank ist Unbekannt, " say "bitte Uli Braeuer benachrichtigen " Call Help EXIT End uid = SysIni.Rex(inidat, Datenbank, "UID") pwd = SysIni.Rex(inidat, Datenbank, "PWD") BackDir = SysIni.Rex(inidat, Datenbank, "BACKUP_DIR") UID = strip(UID) PWD = strip(PWD) End Temp_Dir = VALUE('TMP',,'ENVIRONMENT') Datum = Date('S') Parse Value Time('L') With Stunde ':' Minute ':' Sekunde '.' Milisekunde . Timestamp = Space(Datum Stunde Minute Sekunde Milisekunde, 0) If Length(OUT) > 0 & OUTUNIQUE = "OUTUNIQUE" Then Do Out_Drive = FILESPEC(Drive,Out) Out_Path = FILESPEC(Path,Out) Out_Name = FILESPEC(Name,Out) Out_Extention = FILESPEC(Extension,Out) If Length(Out_Extention) > 0 Then Out_Extention = "." || Out_Extention Out = Out_Drive || Out_Path || Out_Name || '_' || Timestamp || Out_Extention End /* Daten aus der Inidatei ggf. mit Daten aus der Eingabe Ueberscheiben */ If Length(PASSWD) > 0 Then PWD = PASSWD If Length(USER) > 0 Then UID = USER Call Get_NodeDirectory Call SQLDB2 "Attach to "NodeName" User "UID" using "PWD call sqlexec "CONNECT TO "Datenbank "USER "UID "USING "PWD parse value sqlca.sqlerrp with ERRPDBProduct , 4 ERRPDBVersion , 6 ERRPDBRelease , 8 ERRPDBModifikation DBVersion = ERRPDBVersion * 1 DB2INST = VALUE('DB2INSTANCE',,'ENVIRONMENT') Return Get_DBINFO: call SQLDBS 'OPEN DATABASE DIRECTORY ON 0 USING :scan_db' if SQLCA.SQLCODE <> 0 then do say 'Error on Open DATABASE DIRECTORY ' return end do i=1 to scan_db.2 /* scan_db.2 contains number of databases */ call SQLDBS 'GET DATABASE DIRECTORY ENTRY :scan_db.1 USING :entry' UDB = Translate(Datenbank) UENT1 = Translate(Entry.1) If UDB <> UENT1 Then Iterate ODBNAME = Entry.2 If Strip(ENTRY.5) <> "" Then Do ONODE = ENTRY.5 RNODE = "YES" /* REMOTE */ End Else Do ONODE = ENTRY.3 RNODE = "NO" /* REMOTE */ End Leave end /* end do loop */ /* clean up - close directory scan */ call SQLDBS 'CLOSE DATABASE DIRECTORY :scan_db.1' Return Get_BackUp_Directory: If RNODE = "YES" Then Do CMD = "ATTACH TO "ONODE" USER "uid" using "PWD Call SQLDBS CMD if SQLCA.SQLCODE <> 0 then do say CMD SQLCA.SQLCODE return end End TS = '00010101000000' CMD = "OPEN BACKUP RECOVERY HISTORY FILE FOR "DATENBANK" TIMESTAMP :TS USING :SCANBACKUP" call SQLDBS CMD if SQLCA.SQLCODE <> 0 then do say CMD SQLCA.SQLCODE return end Do Forever CMD = "GET RECOVERY HISTORY FILE ENTRY :SCANBACKUP.1 USING :GET_DB" call SQLDBS CMD if SQLCA.SQLCODE <> 0 then do If SQLCA.SQLCODE = 1014 Then Leave /* END OF DIRECTORY SCAN */ say CMD SQLCA.SQLCODE Leave end If Get_db.3 = "R" Then do /* Restore database ignorieren */ Iterate End LastBackupTime = Get_DB.5 LastBackupPath = Get_DB.14 Find = Pos("OFFLINE", GET_DB.15) If Find > 0 Then OnlineBackup = "N" Else OnlineBackup = "Y" AnzTS = Get_DB.1 AnzTSUsed = Get_DB.2 OP = Get_DB.3 obj = Get_DB.4 timestamp = Get_DB.5 Type = Get_DB.6 Einheit = Get_DB.7 FirstProtok = Get_DB.8 AktProtok = Get_DB.9 BACKUP_ID = Get_DB.10 Schema = Get_DB.11 /* Load */ Tablename = Get_DB.12 /* Load */ TSNames = "" Komma = "" Do TSI = 1 To Get_DB.13.0 TSNames = TSNames || Komma Space(Get_DB.13.TSI, 0) /*Build List of Tablespaces */ Komma = "," End BackupPath = Get_DB.14 Kommentar = Get_DB.15 If Length(Search_datum) > 0 Then Do /* Restore ein bestimmtes datum */ If POS(Search_datum, Get_DB.5, 1) > 0 Then do /*Datum gefunden */ Leave /* Ja dann fertig */ End End end Select When Type = "F" Then do /* Offline Backup */ OnlineBackup = "N" IncementalBackup = "N" End When Type = "N" Then do /* Online Backup */ OnlineBackup = "Y" IncementalBackup = "N" End When Type = "I" Then do /* Incemental Offline */ OnlineBackup = "N" IncementalBackup = "Y" End When Type = "O" Then do /* Incemental Online */ OnlineBackup = "Y" IncementalBackup = "Y" End When Type = "D" Then do /* Delta Offline */ OnlineBackup = "N" IncementalBackup = "Y" End When Type = "E" Then do /* Delta Online */ OnlineBackup = "Y" IncementalBackup = "Y" End Otherwise Do OnlineBackup = "N" IncementalBackup = "N" End End /* End Select */ /* Backuptypes F - Offline N - Online I - Incremental offline O - Incremental online D - Delta offline E - Delta online */ CMD = "CLOSE RECOVERY HISTORY FILE :SCANBACKUP.1" call SQLDBS CMD if SQLCA.SQLCODE <> 0 then do say CMD SQLCA.SQLCODE return end If RNODE = "YES" Then Do CMD = "DETACH " Call SQLDBS CMD if SQLCA.SQLCODE <> 0 then do say CMD SQLCA.SQLCODE return end End Return ErrorSQL: /* call beep 220,1000 */ say say "**** SQL gab folgende Fehlerbedingung zurueck:" say " SQLCODE = " SQLCA.SQLCODE say " Nachricht = " SQLMSG say "anweisung " anw say EXIT DBCFG_Info: Values.0 = 6 Values.1 = 114 /* SQLF_DBTN_LOG_RETAIN_STATUS */ Values.2 = "" Values.3 = 105 /* SQLF_DBTN_LOGHEAD */ Values.4 = "" Values.5 = 103 /* SQLF_DBTN_LOGPATH */ Values.6 = "" DB_LOGPATH = "" DB_LOGHEAD = Drive || back ODBNAME || back DBLOGS Call SQLDBS "GET DATABASE CONFIGURATION FOR "Datenbank" USING :values " If sqlca.sqlcode = 0 Then do DB_LOGPATH = Values.6 DB_LOGHEAD = VALUES.4 End Return 0 Get_NodeDirectory: /* Find out the nodename of the database, when it's a remote DB */ NodeName = "" NodeName = Space(NodeName, 0) DBFound = "NO" call SQLDBS 'OPEN DATABASE DIRECTORY ON 0 USING :scan_db' if SQLCA.SQLCODE <> 0 then do say 'Error on Open DATABASE DIRECTORY ' NodeName = VALUE('DB2INSTANCE',,'ENVIRONMENT') return end /* loop until the Database is found and get the node if it's a remote DB */ do i=1 to scan_db.2 /* scan_db.2 contains number of databases */ call SQLDBS 'GET DATABASE DIRECTORY ENTRY :scan_db.1 USING :entry' If Datenbank <> Entry.1 Then Iterate NodeName = entry.5 /* Set Nodename for Attach */ DBFound = "YES" /* Found the database = "YES" */ Leave end /* end do loop */ NodeName = Space(NodeName, 0) If Length(NodeName) = 0 Then NodeName = VALUE('DB2INSTANCE',,'ENVIRONMENT') /* clean up - close directory scan */ call SQLDBS 'CLOSE DATABASE DIRECTORY :scan_db.1' Return Get_Parms: PASSWD = "" USER = "" Search_datum = "" FromDir = "" ValidParms.0 = 11 ValidParms.1 = "-REDIRECT" ValidParms.2 = "-ONLINE" ValidParms.3 = "-TABLESPACE" ValidParms.4 = "-U " ValidParms.5 = "-P " ValidParms.6 = "-D " ValidParms.7 = "-OUTUNIQUE" ValidParms.8 = "-OUT " ValidParms.9 = "-DB " ValidParms.10 = "-FROM " ValidParms.11 = "-HELP" /* Init Values */ Do I = 1 To ValidParms.0 ParmName = Substr(ValidParms.i, 2) Interpret ParmName "= ''" End /* Check Values */ Do I = 1 To ValidParms.0 If Pos(ValidParms.i, Parms, 1) < 1 Then Iterate ParmName = Substr(ValidParms.i, 2) Interpret ParmName "= '"ParmName"'" If ParmName = "U " Then Do USER = Get_Parms_Value(ParmName) End If ParmName = "P " Then Do PASSWD = Get_Parms_Value(ParmName) End If ParmName = "D " Then Do Search_datum = Get_Parms_Value(ParmName) End If ParmName = "OUT " Then Do OUT = Get_Parms_Value(ParmName) End If ParmName = "DB " Then Do DB = Get_Parms_Value(ParmName) End If ParmName = "FROM " Then Do FromDir = Get_Parms_Value(ParmName) End End /* Check Values wrong parameter */ Do I = 1 To Words(Parms) found = "N" Do y = 1 To ValidParms.0 If Word(Parms, i) = ValidParms.y Then Do Found = "Y" Leave End End If Found = "N" Then Do Say "Falscher Parameter " Word(Parms, i) "bitte nur korrekte Werte eingeben" Say "!!!!!! Programm wird unterbrochen !!!!!!!!!!!!!!" Call Help Exit End End Return Get_Parms_Value: SRCParm = "" SRCVAL = "-" || ARG(1) SRCVALL = Length(SRCVAL) FPOS = Pos(SRCVAL, Parms, 1) nxt = FPos + 1 NxtPos = Pos(" -", Parms, nxt) If NxtPos < 1 Then Do NxtPos = Length(Parms) + 1 End ParmLen = NxtPos - (fpos + SRCVALL) DelStart = FPos + SRCVALL SRCParm = SUBSTR(ParmsO, DelStart, ParmLen) SRCParm = Strip(SRCParm) Parms = Delstr(Parms, DelStart, ParmLen) Parmso = Delstr(Parmso, DelStart, ParmLen) Return SRCPARM Help: Say "Aufruf GenRestore.Rex -DB <-D Timestamp> <-REDIRECT> <-U USERID> <-P Password> <-TABLESPACE> <-ONLINE>" Say "" Say "Optionen : -DB Name der Datenbank " Say " -REDIRECT Erstellt restore mit Redirect " Say " -TABLESPACE Erstellt restore fuer die in den Sicherungen verwendeten Tablespaces" Say " -ONLINE Erstellt restore mit Online " Say " -D Erstellt restore fuer den Timstamp ansonsten wir der letzte TS verwendet" Say " -U Zu verwendente USERID" Say " -P Zu verwendentes Password" Say " -OUT Ausgabe in Datei " Say " -OUTUNIQUE Ausgabedatei wir mit Timestamp Versehen " Say " -FROM /backup/dir1,/backup/dir2,/backup/dirx " Say " -HELP Diese Anzeige " Say "" Say "Beispiele : Rexx Genrestore.Rex sample (Letzter Backup) " Say " Rexx Genrestore.Rex -DB sample -D 20100628 (Erster Backup von Datum) " Say " Rexx Genrestore.Rex -DB sample -D 201006281410 (Backup von 28.06.2010 14:10) " Say " Rexx Genrestore.Rex -DB sample -D 201006281410 -REDIRECT -U DB2User -P password " Say " Rexx Genrestore.Rex -DB sample -OUT /home/dbbackup/dbcfg.txt -OUTUNIQUE" Return GetPwd: Procedure pwd = "" count = 0 parse value SysCurPos() with row col do forever /* while key1\=d2c(13)*/ If count = 0 Then do ok = SysCurPos(row,col) Say "enter password : " /* row = row - 1 */ col = col + 16 ok = SysCurPos(row,col) End Else ok = SysCurPos(row,col) key1=SysGetKey("NoEcho") count = count + 1 /* say c2d(key1) */ Select When key1=d2c(13) then Do/* Enter key */ leave End When key1=d2c(27) then Do/* Escape */ pwd = "" key1 = "" leave End When (key1=d2c(8)) then Do /* Backspace */ if length(pwd) = 1 then do pwd = "" End if length(pwd) > 1 then do pwdL = length(pwd) - 1 pwd = substr(pwd, 1, pwdL) End End Otherwise do pwd = pwd || key1 End End key1 = "" end ok = SysCurPos(row,0) return pwd