home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 8 Other
/
08-Other.zip
/
db22fr.zip
/
SQLUEXIT.CMD
< prev
Wrap
OS/2 REXX Batch file
|
1993-05-12
|
25KB
|
751 lines
/*
┌──────────────────────────────────────────────────────────────────┐
│ │
│ Name : SQLUEXIT.cmd │
│ Purpose : A customized DBM SQLUEXIT program │
│ that is purposely simplified for demos, │
│ simple testing, and learning. │
│ Platform : DB2/2 and OS/2 2.1 │
│ Author : Jeff Fisher │
│ Copyright, IBM Corporation 1993 │
│ Toronto OS/2 Planning │
│ Written : 08/31/91 │
│ │
│ Ideas : - better audit report │
│ - timings for perf testing │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
signal on syntax
MAIN:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: MAIN │
│ Validate and branch by parms received │
│ Archive/Retreive and Backup/Restore receive different parms │
│ and are thus handled differently │
│ for the displayed messages and audit log │
└──────────────────────────────────────────────────────────────────┘
*/
call SetUp
parse upper arg ParmOpt Parm1 Parm2 Parm3 Parm4 Parm5
call EditParms
SELECT
WHEN ParmOpt = 'ARCHIVE' | ParmOpt = 'RETRIEVE' then
do
call HandleParms1
if ParmOpt = 'ARCHIVE' then signal LogArchive
if ParmOpt = 'RETRIEVE' then signal LogRetrieve
end
WHEN ParmOpt = 'BACKUP' | ParmOpt = 'RESTORE' then
do
call HandleParms2
if ParmOpt = 'BACKUP' then signal DBBackup
if ParmOpt = 'RESTORE' then signal DBRestore
end
OTHERWISE
do
say c.itred 'Invalid option passed from the DBM'
sqluexitrc = UEXIT.PARM;
signal Exit_Control
End
END
EditParms:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: edit all five parms │
│ (conditional on parm type too) │
└──────────────────────────────────────────────────────────────────┘
*/
Parm1 = strip(Parm1)
if Parm1 = '' then do
say c.itred 'Parm1 is null'
sqluexitrc = UEXIT.PARM
signal Exit_Control
end
Parm2 = strip(Parm2)
if Parm2 = '' then do
say c.itred 'Parm2 is null'
sqluexitrc = UEXIT.PARM
signal Exit_Control
end
Parm3 = strip(Parm3)
if Parm3 = '' then do
say c.itred 'Parm3 is null'
sqluexitrc = UEXIT.PARM
signal Exit_Control
end
Parm4 = strip(Parm4)
if Parm4 = '' then do
say c.itred 'Parm4 is null'
sqluexitrc = UEXIT.PARM
signal Exit_Control
end
If ParmOpt = 'BACKUP' | ParmOpt = 'RESTORE' then
do
Parm5 = strip(Parm5)
if Parm5 = '' then do
say c.itred 'Parm5 is null'
sqluexitrc = UEXIT.PARM
signal Exit_Control
end
end
Else
FullPath = Parm3||Parm4;
return
HandleParms1:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: handle parms for Archive/Retrieve │
│ │
│ Display a message and write parms to audit file │
└──────────────────────────────────────────────────────────────────┘
*/
/*
┌───────────────────────────────────────────────────────┐
│ display and log all parms │
└───────────────────────────────────────────────────────┘
*/
say
say c.itgreen '******************************************'
say c.itmagenta ' SQLUEXIT:' c.itcyan ParmOpt
say c.yellow ' Date:' RunDate ' Time:' RunTime
say c.yellow ' '
say c.yellow ' Database Name :'c.itcyan Parm2
say c.yellow ' Drive :'c.itcyan Parm1
say c.yellow ' Log Path :'c.itcyan Parm3
say c.yellow ' Name :'c.itcyan Parm4
say c.yellow ' '
say c.yellow ' Message Log :'c.itcyan MessageLog
say c.itgreen '******************************************'
PT0 = '---------------------------------------------------------'
PT1 = '--> SQLUEXIT on 'RunDate' at 'RunTime
PT2 = ' Parameters received:'
PT3 = ' Action :' ParmOpt
PT4 = ' Database Name :' Parm2
PT5 = ' Drive :' Parm1
PT6 = ' Log Path :' Parm3
PT7 = ' Name :' Parm4
CALL LINEOUT MessageLog,PT0
CALL LINEOUT MessageLog,PT1
CALL LINEOUT MessageLog,Space
CALL LINEOUT MessageLog,PT2
CALL LINEOUT MessageLog,PT3
CALL LINEOUT MessageLog,PT4
CALL LINEOUT MessageLog,PT5
CALL LINEOUT MessageLog,PT6
CALL LINEOUT MessageLog,PT7
return
HandleParms2:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: handle parms for Backup │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
DB_DRIVE = strip(PARM1)
DB_NAME = strip(PARM2)
RESP_FILE = strip(PARM3)
LABEL = strip(PARM4)
REST_DIR = strip(PARM4)
INSTANCE = strip(PARM5)
rsp_line = linein( RESP_FILE ) /* Get response file line */
call stream RESP_FILE, C, 'close' /* Close response file */
say
say c.itgreen '******************************************'
say c.itmagenta ' SQLUEXIT:' c.itcyan ParmOpt
say c.yellow ' Date:' RunDate ' Time:' RunTime
say c.yellow ' '
say c.yellow ' Instance :'c.itcyan Parm5
say c.yellow ' Database Name :'c.itcyan Parm2
say c.yellow ' Location :'c.itcyan Parm1
say c.yellow ' Response File :'c.itcyan Parm3
say c.yellow ' Response :'c.itcyan rsp_line
if ParmOpt = 'BACKUP' then
do
say c.yellow ' Media Label :'c.itcyan Parm4
PT9 = ' Media Label :' Parm4
end
else
do
say c.yellow ' Restore Dir :'c.itcyan Parm4
PT9 = ' Restore Dir :' Parm4
end
say c.yellow ' '
say c.yellow ' Message Log :'c.itcyan MessageLog
say c.itgreen '******************************************'
PT0 = '---------------------------------------------------------'
PT1 = '--> SQLUEXIT on 'RunDate' at 'RunTime
PT2 = ' Parameters received:'
PT3 = ' Action :' ParmOpt
PT4 = ' Instance :' Parm5
PT5 = ' Database Name :' Parm2
PT6 = ' Drive :' Parm1
PT7 = ' Response File :' Parm3
PT8 = ' Response :' rsp_line
CALL LINEOUT MessageLog,PT0
CALL LINEOUT MessageLog,PT1
CALL LINEOUT MessageLog,Space
CALL LINEOUT MessageLog,PT2
CALL LINEOUT MessageLog,PT3
CALL LINEOUT MessageLog,PT4
CALL LINEOUT MessageLog,PT5
CALL LINEOUT MessageLog,PT6
CALL LINEOUT MessageLog,PT7
CALL LINEOUT MessageLog,PT8
CALL LINEOUT MessageLog,PT9
return
LogArchive:
/*
┌───────────────────────────────────────────────────────────────┐
│ Procedure: Log Archive │
└───────────────────────────────────────────────────────────────┘
*/
DB_DRIVE = left(strip(PARM1), 1) /* Throw away the colon */
DB_NAME = strip(PARM2)
LOG_PATH = strip(PARM3)
LOG_FILE = strip(PARM4)
LOG_PATH = LOG_PATH||LOG_FILE /* Modify LOG_PATH to contain */
/* the full log file name */
if stream(LOG_PATH, c, 'query exists') = '' then /* Log file exists? */
do
ErrMsg = 'The file 'LOG_PATH' was assumed to be previously archived'
sqluexitrc = UEXIT.OK
signal Exit_Control
end
ARCH_PATH = db_logs_bkp_dir
say c.brown
address cmd 'XCOPY 'LOG_PATH' 'ARCH_PATH
call checkrc
signal Exit_Control
LogRetrieve:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: Log Retrieve │
└──────────────────────────────────────────────────────────────────┘
*/
DB_DRIVE = left(strip(PARM1), 1) /* Throw away the colon */
DB_NAME = strip(PARM2)
LOG_PATH = strip(PARM3)
LOG_FILE = strip(PARM4)
ARCH_PATH = db_logs_bkp_dir
say c.brown
address cmd 'XCOPY 'ARCH_PATH'\'LOG_FILE' 'LOG_PATH
call checkrc
signal Exit_Control
DBBackup:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: DBBackup │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
/*
┌───────────────────────────────────────────────────────┐
│ On the first instance, erase the previous backup │
│ (check for read-only files) │
└───────────────────────────────────────────────────────┘
*/
if INSTANCE = '1' then
do
say
address cmd 'ATTRIB 'db_files_bkp_dir'\*.* -r >NUL 2>NUL'
address cmd 'DEL 'db_files_bkp_dir' /N >NUL 2>NUL'
end
/*
┌───────────────────────────────────────────────────────┐
│ display an informative message │
└───────────────────────────────────────────────────────┘
*/
if INSTANCE = '1' then
say c.itgreen '--> Backing up UIF file'
else
say c.itgreen '--> Backing up database files'
/*
┌───────────────────────────────────────────────────────┐
│ xcopy files │
│ db_files_bkp_dir was set in the configuration section │
│ of the SetUp procedure │
└───────────────────────────────────────────────────────┘
*/
say c.brown
address cmd 'XCOPY 'rsp_line' 'db_files_bkp_dir
call checkrc
/*
┌───────────────────────────────────────────────────────┐
│ go to the exit control procedure │
└───────────────────────────────────────────────────────┘
*/
signal Exit_Control
DBRestore:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: DBRestore │
└──────────────────────────────────────────────────────────────────┘
*/
/*
┌───────────────────────────────────────────────────────┐
│ display an informative message │
└───────────────────────────────────────────────────────┘
*/
if INSTANCE = '1' then
say c.itgreen 'Restoring --> UIF file'
else
say c.itgreen 'Restoring --> database files'
/*
┌───────────────────────────────────────────────────────┐
│ setup filelist │
└───────────────────────────────────────────────────────┘
*/
parse value rsp_line with . '\' . '\' filelist
/*
┌───────────────────────────────────────────────────────┐
│ xcopy files │
│ restore dest came from ? │
│ of the SetUp procedure │
└───────────────────────────────────────────────────────┘
*/
say c.brown
address cmd 'XCOPY 'db_files_bkp_dir'\'filelist' 'DB_DRIVE||REST_DIR
call checkrc
/*
┌───────────────────────────────────────────────────────┐
│ go to the exit control procedure │
└───────────────────────────────────────────────────────┘
*/
signal Exit_Control
Verify_Path:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: verify path │
│ verifies paths │
└──────────────────────────────────────────────────────────────────┘
*/
arg CHK_PATH
curdir = directory() /* Save current directory */
/***************************************************************/
/* Separate drive from path and save the remainder of the path */
/***************************************************************/
parse value CHK_PATH with drive '\' path_remain
/*****************************************/
/* Break down path by subdirectory names */
/*****************************************/
i = 1
do while path_remain <> ''
parse value path_remain with path.i '\' path_remain
i = i + 1 /* Subdirectory name index */
end
/**********************************************************************/
/* Rebuild the path one subdirectory at a time checking to see if the */
/* path exist and, if not, create it. */
/**********************************************************************/
build_path = drive /* Build path back up starting at drive letter */
j = 1
do while j < i
build_path = build_path'\'path.j /* Add a subdirectory to path */
newdir = directory( build_path ) /* See if the path already exist */
if( newdir <> build_path ) then do
md_var = 'MKDIR 'build_path
address CMD md_var /* Let OS/2 make the directory */
if (rc <> 0) then do
ErrMsg = md_var' *** FAILED, <'rc'>'
sqluexitrc = UEXIT.UNKNOWN
signal Exit_Control
end
end
j = j + 1 /* Bump subdirectory name index */
end
call directory curdir /* Return to the original directory */
return 0
checkrc:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: check OS/2 return codes │
│ called from XCOPY and other OS/2 commands │
│ and set sqluexitrc │
└──────────────────────────────────────────────────────────────────┘
*/
os2_rc = rc
SELECT
WHEN os2_rc = 0 then
do
sqluexitrc = UEXIT.OK;
return;
end
WHEN os2_rc = 1 then
do
ErrMsg= 'OS/2 XCOPY ERROR <1>: No files found to Backup or Restore'
sqluexitrc = UEXIT.NOTFOUND;
return;
end
WHEN os2_rc = 2 then
do
ErrMsg= 'OS/2 XCOPY ERROR <2>: Busy files not Backed up or Restored'
sqluexitrc = UEXIT.UNKNOWN;
return;
end
WHEN os2_rc = 3 then
do
ErrMsg= 'OS/2 XCOPY ERROR <3>: Command Terminated by user'
sqluexitrc = UEXIT.OPCAN;
return;
end
WHEN os2_rc = 4 then
do
ErrMsg= 'OS/2 XCOPY ERROR <4>: Command Terminated due to error'
sqluexitrc = UEXIT.UNKNOWN;
return;
end
OTHERWISE /* Unknown error */
do
ErrMsg= 'OS/2 XCOPY command returned ERROR <'os2_rc'>'
sqluexitrc = UEXIT.UNKNOWN;
return;
end
END
return
SetUp:
/*
┌──────────────────────────────────────────────────────────────────┐
│ SetUp │
│ sets up variables and other etc. │
└──────────────────────────────────────────────────────────────────┘
*/
address cmd '@ECHO OFF'
call SetColor
/*
┌───────────────────────────────────────────────────────┐
│ sound beep signifying beginning │
└───────────────────────────────────────────────────────┘
*/
note.3 = 524
note.2 = 494
note.1 = 440
do i=1 to 3
call beep note.i,50
end
Space = ' '
quote = '"'
RunDate = DATE()
RunTime = TIME()
DT = '*** 'RunDate', 'RunTime
/*
┌──────────────────────────────────────────────────────┐
│ This user exit can only handle 1 database │
│ and does not handle archiving old backups │
│ │
│ db_name the name of the database │
│ │
└──────────────────────────────────────────────────────┘
*/
db_name = 'frdemo'
/*
┌──────────────────────────────────────────────────────┐
│ Location of the backed-up DBs and LOGs │
│ │
│ db_files_bkp_dir where to put the backup │
│ db_logs_bkp_dir temp hold location for logs │
│ │
│ call VERIFY_DIR to make these if they don't │
│ already exist │
└──────────────────────────────────────────────────────┘
*/
db_files_bkp_dir = 'D:\frdemo.bkp\database'
db_logs_bkp_dir = 'D:\frdemo.bkp\logs'
call Verify_Path(db_files_bkp_dir)
VERIFY_DIR = 'TRUE'
call Verify_Path(db_logs_bkp_dir)
VERIFY_DIR = 'TRUE'
ARCH_PATH = ''
/*
┌───────────────────────────────────────────────────────┐
│ Make sure the directory exists │
│ │
│ EXITDIR the prefix of many following files │
└───────────────────────────────────────────────────────┘
*/
EXITDIR = 'D:\SQLUEXIT'
call Verify_Path(EXITDIR) /* Path exists?/If not, create it */
VERIFY_DIR = 'TRUE'
/*
┌───────────────────────────────────────────────────────┐
│ Log handling │
│ │
│ MessageLog actual name of the audit file │
│ ErrMsg field containing a message string │
│ AUDIT user configurable parm, whether │
│ or not to create an audit trail │
└───────────────────────────────────────────────────────┘
*/
MessageLog = 'D:\frdemo.bkp\audit.log'
AUDIT = 'Y'
ErrMsg = ''
/*
┌────────────────────────────────────┐
│ Successful │
└────────────────────────────────────┘
*/
UEXIT.OK = 0;
/*
┌────────────────────────────────────┐
│ Unsuccessful - retry in 5 minutes │
└────────────────────────────────────┘
*/
UEXIT.RES = 4; /* temporary resource error */
UEXIT.OPATTN = 8; /* operator intervention required */
/*
┌────────────────────────────────────┐
│ Unsuccessful - can't retry │
└────────────────────────────────────┘
*/
UEXIT.HARDWARE = 12; /* hardware error */
UEXIT.DEFECT = 16; /* error in uexit/function called */
UEXIT.PARM = 20; /* error in passed parameter */
UEXIT.NOTFOUND = 24; /* restore file couldn't be found */
UEXIT.UNKNOWN = 28; /* i/o or opsys failure */
UEXIT.OPCAN = 32; /* uexit terminated by the user */
sqluexitrc = UEXIT.OK; /* USED TO PASS BACK RETURN CODE */
return 0
Exit_Control:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: branch by sqluexitrc │
└──────────────────────────────────────────────────────────────────┘
*/
if sqluexitrc = UEXIT.OK then
signal Exit_Ok
else
signal Exit_Error
Exit_Error:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: Exit_Error │
│ fatal exit │
└──────────────────────────────────────────────────────────────────┘
*/
call beep 200,500
err1 = '--> SQLUEXIT: error taken on 'RunDate' @ 'RunTime;
err2 = ' Error code being returned to DBM:' sqluexitrc
err3 = ' Parms received:'
err4 = ' Error Message:'
say
say c.itred err1
say c.itred err2
say c.itred err3
say c.itred parmopt
say c.itred parm1
say c.itred parm2
say c.itred parm3
say c.itred parm4
say c.itred parm5
say c.itred err4
say c.itred ErrMsg
pause
CALL LINEOUT MessageLog,space
CALL LINEOUT MessageLog,err2
CALL LINEOUT MessageLog,err1
CALL LINEOUT MessageLog,err3
CALL LINEOUT MessageLog,parmopt
CALL LINEOUT MessageLog,parm1
CALL LINEOUT MessageLog,parm2
CALL LINEOUT MessageLog,parm3
CALL LINEOUT MessageLog,parm4
CALL LINEOUT MessageLog,parm5
CALL LINEOUT MessageLog,err4
CALL LINEOUT MessageLog,ErrMsg
say c.normal
exit sqluexitrc;
Exit_Ok:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: Exit_Ok │
│ good exit │
└──────────────────────────────────────────────────────────────────┘
*/
note.1 = 524
note.2 = 494
note.3 = 440
do i=1 to 3
call beep note.i,50
end
ok1 = '--> Exit returning, successful'
CALL LINEOUT MessageLog,space
CALL LINEOUT MessageLog,ok1
say c.itmagenta ok1
say c.normal
exit UEXIT.OK
SetColor:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: SetColor │
│ set up colors for screen display │
└──────────────────────────────────────────────────────────────────┘
*/
ansii.esc = '1B'x
c.normal = ansii.esc || '[0m'
c.highlite = ansii.esc || '[1m'
c.blink = ansii.esc || '[5m'
c.blackback = ansii.esc || '[40m'
c.itredback = ansii.esc || '[41m'
c.greenback = ansii.esc || '[42m'
c.brownback = ansii.esc || '[43m'
c.blueback = ansii.esc || '[44m'
c.magentaback = ansii.esc || '[45m'
c.cyanback = ansii.esc || '[46m'
c.greyback = ansii.esc || '[47m'
c.black = c.normal || ansii.esc || '[30m'
c.red = c.normal || ansii.esc || '[31m'
c.green = c.normal || ansii.esc || '[32m'
c.brown = c.normal || ansii.esc || '[33m'
c.blue = c.normal || ansii.esc || '[34m'
c.magenta = c.normal || ansii.esc || '[35m'
c.cyan = c.normal || ansii.esc || '[36m'
c.grey = c.normal || ansii.esc || '[37m'
c.itblack = c.highlite || c.black
c.itred = c.highlite || ansii.esc || '[31m'
c.itgreen = c.highlite || ansii.esc || '[32m'
c.yellow = c.highlite || ansii.esc || '[33m'
c.itblue = c.highlite || ansii.esc || '[34m'
c.itmagenta = c.highlite || ansii.esc || '[35m'
c.itcyan = c.highlite || ansii.esc || '[36m'
c.white = c.highlite || ansii.esc || '[37m'
c.std = c.normal || c.itcyan || c.blackback
c.reset = c.normal || c.grey || c.blackback
return 0
syntax:
Syn1 = 'syntax error in the rexx program'
CALL LINEOUT MessageLog,Syn1
CALL LINEOUT MessageLog,sigl
say c.itred syn1
say c.itred sigl
pause
say 'rexx error' rc 'in line' sigl':' errortext(rc)
pause
say sourcall(sigl)
pause
pause
return
HALT:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Procedure: Halt │
│ called when a ctrl-c is received │
└──────────────────────────────────────────────────────────────────┘
*/
ErrMsg = 'user terminated with a ctrl-c'
sqluexitrc=UEXIT.OPCAN
pause
signal Exit_Control