home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 8 Other
/
08-Other.zip
/
db22fr.zip
/
FRDEMO.CMD
next >
Wrap
OS/2 REXX Batch file
|
1993-05-12
|
75KB
|
1,955 lines
/*
┌──────────────────────────────────────────────────────────────────┐
│ Name : FRDEMO.cmd │
│ Purpose : Instigate various Forward Recovery scenarios │
│ Platform : IBM DB2/2, OS/2 2.1 │
│ Author : Jeff Fisher │
│ Copyright IBM Corporation 1993 │
│ IBM Toronto Development Lab │
│ Written : 08/30/91 or thereabouts │
│ │
│ Notes : You'll need to note the various drives and │
│ directory names hard coded in this program. │
│ Change them for your own system if needed. │
│ │
│ Ideas : - enter a timestamp │
│ - display options being used: │
│ drive for database creation │
│ database name user configurable │
│ object qualifier user configurable │
│ - log file values for circular logging?? │
│ demonstrate primary and secondary by filling │
│ up all primary without committing │
│ and allow for alternate log file! │
│ - help and about │
│ - when inserting data, if the tables don't exist │
│ then exit the entire create path │
│ - option on menu to show the database flags: │
│ rollforward and backup pending. │
│ - RESUME and STOP should be allowed │
│ - multiple recovery paths and DB backup versions │
│ │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
signal on syntax
signal on error
call ProgramInitialize
call MainMenu
call EndProg
MainMenu:
/*
┌──────────────────────────────────────────────────────────────────┐
│ MainMenu │
│ │
│ Main menu and control point for the entire program │
│ Note use of REXXUTIL functions! │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
call SysCls
say c.itcyan '******************************************'
say c.yellow ' FRDEMO Forward Recovery Instigator '
say c.yellow ' '
say c.yellow ' _ 0 Menu: Program Options & Defaults '
say c.yellow ' '
say c.yellow ' 1 Menu: Database Objects '
say c.yellow ' 2 Menu: Adjust Tuning Parms '
say c.yellow ' 3 Menu: Do a Roll Forward sequence '
say c.yellow ' 4 Menu: Manipulate data in the DB '
say c.yellow ' 5 Menu: Utilities '
say c.yellow ' 6 Menu: Tools '
say c.yellow ' '
say c.yellow ' x Exit '
say c.yellow ' '
say c.itcyan '******************************************'
say c.itblue ' Copyright, IBM Corporation 1993 ' c.white
row = 3
col = 2
pos = SysCurPos(row,col)
selection = SysGetKey('NOECHO')
call SysCls
SELECT
WHEN selection = '0' then call ProgramOptions
WHEN selection = '1' then call ObjectMenu
WHEN selection = '2' then call ParmTuneMenu
WHEN selection = '3' then call RollForwardSequenceMenu
WHEN selection = '4' then call TableDataMenu
WHEN selection = '5' then call UtilityMenu
WHEN selection = '6' then call ToolsMenu
WHEN selection = 'x' then signal EndProg
OTHERWISE NOP
END
signal MainMenu
ProgramOptions:
/*
┌──────────────────────────────────────────────────────────────────┐
│ ProgramOptions │
│ │
│ Menu for selecting global program options & defaults │
│ │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
call SysCls
say c.itcyan '******************************************'
say c.yellow ' Program Options & Defaults '
say c.yellow ' '
say c.yellow ' _ 1 Show Timings '
say c.yellow ' 2 Change DBdrive '
say c.yellow ' 3 Change DBname '
say c.yellow ' 4 Change DBprefix '
say c.yellow ' '
say c.yellow ' x Exit '
say c.itcyan '******************************************' c.white
row = 3
col = 2
pos = SysCurPos(row,col)
selection = SysGetKey('NOECHO')
call SysCls
SELECT
WHEN selection = '1' then OptionTiming = 'Y'
WHEN selection = '2' then say 'not implemented yet'
WHEN selection = '3' then say 'not implemented yet'
WHEN selection = '4' then say 'not implemented yet'
OTHERWISE NOP
END
return
ObjectMenu:
/*
┌──────────────────────────────────────────────────────────────────┐
│ ObjectMenu │
│ │
│ Menu for setting up all relational objects used: │
│ │
│ Database │
│ Tables │
│ Indexes │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
call SysCls
say c.itcyan '******************************************'
say c.yellow ' Object Menu '
say c.yellow ' '
say c.yellow ' _ 1 Create database FRDEMO '
say c.yellow ' 2 Create application tables '
say c.yellow ' 3 Create application indexes '
say c.yellow ' '
say c.yellow ' 4 Drop database FRDEMO '
say c.yellow ' 5 Drop application tables '
say c.yellow ' 6 Drop application indexes '
say c.yellow ' '
say c.yellow ' x Exit '
say c.itcyan '******************************************' c.white
row = 3
col = 2
pos = SysCurPos(row,col)
selection = SysGetKey('NOECHO')
call SysCls
SELECT
WHEN selection = '1' then call CreateFRDEMO
WHEN selection = '2' then call CreateTables
WHEN selection = '3' then call CreateIndexes
WHEN selection = '4' then call DropFRDEMO
WHEN selection = '5' then call DropTables
WHEN selection = '6' then call DropIndexes
WHEN selection = 'x' then return
OTHERWISE NOP
END
return
ParmTuneMenu:
/*
┌──────────────────────────────────────────────────────────────────┐
│ ParmTuneMenu │
│ │
│ Menu for tuning the DBM and the test database: │
│ │
│ Tune for Forward Recovery + high-perf parms │
│ Tune for no Forward Recovery + high-perf-parms │
│ Tune for no Forward Recovery + low-perf parms │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
call SysCls
say c.itcyan '******************************************'
say c.yellow ' DB and DBM Parameter Tuning Menu '
say c.yellow ' '
say c.yellow ' _ 1 Performance, FR & SQLUEXIT/yes '
say c.yellow ' 2 Performance, FR & SQLUEXIT/no '
say c.yellow ' 3 Base default lo-po parms '
say c.yellow ' '
say c.yellow ' x Exit '
say c.itcyan '******************************************' c.white
row = 3
col = 2
pos = SysCurPos(row,col)
selection = SysGetKey('NOECHO')
call SysCls
SELECT
WHEN selection = '1' then call MegaTuningParms
WHEN selection = '2' then call PerfTuningParms
WHEN selection = '3' then call BaseTuningParms
WHEN selection = 'x' then return
OTHERWISE NOP
END
return
TableDataMenu:
/*
┌──────────────────────────────────────────────────────────────────┐
│ TableDataMenu │
│ │
│ Menu for putting data in the tables: │
│ │
│ From IMPORT (or EXPORT data out) │
│ - helps you save your "favorite" data │
│ From INSERTs │
│ - builds data and commits at intervals │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
call SysCls
say c.itcyan '******************************************'
say c.yellow ' Table Data Menu '
say c.yellow ' '
say c.yellow ' _ 1 EXPORT data from employee '
say c.yellow ' 2 IMPORT data to employee '
say c.yellow ' 3 Create data for all tables '
say c.yellow ' '
say c.yellow ' x Exit '
say c.itcyan '******************************************' c.white
row = 3
col = 2
pos = SysCurPos(row,col)
selection = SysGetKey('NOECHO')
call SysCls
SELECT
WHEN selection = '1' then call EXPORTemployee
WHEN selection = '2' then call IMPORTemployee
WHEN selection = '3' then call PopulateMenu
WHEN selection = 'x' then return
OTHERWISE NOP
END
return
PopulateMenu:
/*
┌──────────────────────────────────────────────────────────────────┐
│ PopulateMenu │
│ │
│ Each table can be done in turn, or all can be done │
│ together in the intended sequence. Each gets it's own CONNECT │
│ and CONNECT RESET, thus creating separate logs for each table. │
│ COMMIT or ROLLBACK is an option, allowing you to see that │
│ either option does in fact end up in the logs. │
│ The tables are populated via a lot of comcatenated strings. │
│ This is not a very good method, as concatenation of these │
│ strings in rexx seems to take a lot of CPU time. Therefore, the │
│ insert rate is limited ot about 12 per second. It could │
│ probably be much faster with a better method. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
if EmployeeRowCount > 99999 then EmployeeRandomCount = 99999
else EmployeeRandomCount = EmployeeRowCount
call SysCls
say c.itcyan '******************************************'
say c.yellow ' Populate Tables menu '
say c.yellow ' '
say c.yellow ' _ 0 Populate all tables in sequence '
say c.yellow ' '
say c.yellow ' 1 Table: DIRECTORATE '
say c.yellow ' 2 Table: DIVISION '
say c.yellow ' 3 Table: DEPARTMENT '
say c.yellow ' 4 Table: EMPLOYEE '
say c.yellow ' '
say c.yellow ' x Exit '
say c.yellow ' '
say c.itcyan '******************************************' c.white
row = 3
col = 2
pos = SysCurPos(row,col)
selection = SysGetKey('NOECHO')
call SysCls
SELECT
WHEN selection = '0' then
do
call PopulateDirectorate
call PopulateDivision
call PopulateDepartment
call PopulateEmployee
end
WHEN selection = '1' then call PopulateDirectorate
WHEN selection = '2' then call PopulateDivision
WHEN selection = '3' then call PopulateDepartment
WHEN selection = '4' then call PopulateEmployee
WHEN selection = 'x' then return
OTHERWISE NOP
END
return
RollForwardSequenceMenu:
/*
┌──────────────────────────────────────────────────────────────────┐
│ RollForwardSequenceMenu │
│ │
│ Running utilities against the database: │
│ │
│ Restore before Forward Recovery │
│ Roll Forward during Forward Recovery │
│ │
│ what happens after? │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
call SysCls
say c.itcyan '******************************************'
say c.yellow ' Roll Forward Sequence Menu '
say c.yellow ' '
say c.yellow ' _ 1 Restore before Forward Recovery '
say c.yellow ' 2 Roll Forward the database '
say c.yellow ' 3 both steps 1 and 2 '
say c.yellow ' '
say c.yellow ' x Exit '
say c.yellow ' '
say c.itcyan '******************************************' c.white
row = 3
col = 2
pos = SysCurPos(row,col)
selection = SysGetKey('NOECHO')
call SysCls
SELECT
WHEN selection = '1' then call Roll1
WHEN selection = '2' then call Roll2
WHEN selection = '3' then
do
call Roll1
call Roll2
end
WHEN selection = 'x' then return
OTHERWISE NOP
END
return
ToolsMenu:
/*
┌──────────────────────────────────────────────────────────────────┐
│ ToolsMenu │
│ │
│ LOG - starts Monitor of LOGS │
│ DB - starts Monitor of Database │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
call SysCls
say c.itcyan '******************************************'
say c.yellow ' Tools Menu '
say c.yellow ' '
say c.yellow ' _ 1 Start LOG Monitor '
say c.yellow ' 2 Start DB Monitor '
say c.yellow ' '
say c.yellow ' x Exit '
say c.yellow ' '
say c.itcyan '******************************************' c.white
row = 3
col = 2
pos = SysCurPos(row,col)
selection = SysGetKey('NOECHO')
call SysCls
SELECT
WHEN selection = '1' then call StartMonLOG
WHEN selection = '2' then call StartMonDB
WHEN selection = 'x' then return
OTHERWISE NOP
END
return
UtilityMenu:
/*
┌──────────────────────────────────────────────────────────────────┐
│ UtilityMenu │
│ │
│ Runstats & Reorg - demonstrate the effect on the logs │
│ from these utilities │
│ │
│ Backup - does the necessary backup after turning │
│ on Forward Recovery │
│ │
│ Notes: Explicit qualifiers are used on all object names │
│ so that these utilities don't have to find out │
│ what (implicit) qualifier is being used. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
call SysCls
say c.itcyan '******************************************'
say c.yellow ' Utility Menu '
say c.yellow ' '
say c.yellow ' _ 1 Reorg all tables '
say c.yellow ' 2 Runstats the tables '
say c.yellow ' 3 Backup the entire database '
say c.yellow ' '
say c.yellow ' x Exit '
say c.yellow ' '
say c.itcyan '******************************************' c.white
row = 3
col = 2
pos = SysCurPos(row,col)
selection = SysGetKey('NOECHO')
call SysCls
SELECT
WHEN selection = '1' then call ReorgTables
WHEN selection = '2' then call RunstatTables
WHEN selection = '3' then call BackupDB
WHEN selection = 'x' then return
OTHERWISE NOP
END
return
ReorgTables:
/*
┌──────────────────────────────────────────────────────────────────┐
│ ReorgTables │
│ │
│ Does reorgs on all tables with appropriate qualifiers │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> REORG all tables'
call ReorgUtility 'FRDEMO.EMPLOYEE' 'FRDEMO.EMPLOYEE_ID_I'
call ReorgUtility 'FRDEMO.DIVISION' 'FRDEMO.DIVISION_I'
call ReorgUtility 'FRDEMO.DIRECTORATE' 'FRDEMO.DIRECTORATE_I'
call ReorgUtility 'FRDEMO.DEPARTMENT' 'FRDEMO.DEPARTMENT_DEPT_I'
return
RunstatTables:
/*
┌──────────────────────────────────────────────────────────────────┐
│ RunstatTables │
│ │
│ Does runstats on all tables │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> RUNSTATS all tables'
call RunstatUtility 'FRDEMO.EMPLOYEE' 'FRDEMO.EMPLOYEE_ID_I'
call RunstatUtility 'FRDEMO.DIVISION' 'FRDEMO.DIVISION_I'
call RunstatUtility 'FRDEMO.DIRECTORATE' 'FRDEMO.DIRECTORATE_I'
call RunstatUtility 'FRDEMO.DEPARTMENT' 'FRDEMO.DEPARTMENT_DEPT_I'
return
BackupDB:
/*
┌──────────────────────────────────────────────────────────────────┐
│ BackupDB │
│ │
│ Backs up the database (using the user exit). The user exit │
│ must be found in a PATH. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> Backup to drive 0'
step = 'Backup DB to drive 0 (user exit invokation)'
CALL SQLDBS 'BACKUP DATABASE' DBname 'ALL TO 0'
if SQLCA.SQLCODE = -2029 then
do
say
say c.itred ' **** USER EXIT FATAL ERROR ****'
say c.red ' Program will continue '
say c.red ' after display of SQLCA '
call SoftErrorSQL
return
end
else
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
return
StartMonDB:
/*
┌──────────────────────────────────────────────────────────────────┐
│ StartMonDB │
│ │
│ Start Monitor for Database │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen ' ---> Start DB Monitor'
step = 'start monitor'
address cmd 'start mon_db.cmd'
if RC \= 0 then signal ErrorRC
return
StartMonLOG:
/*
┌──────────────────────────────────────────────────────────────────┐
│ StartMonLOG │
│ │
│ Start Monitor for LOGS │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen ' ---> Start LOG Monitor'
step = 'start monitor'
address cmd 'start mon_logs.cmd'
if RC \= 0 then signal ErrorRC
return
ReorgUtility:
/*
┌──────────────────────────────────────────────────────────────────┐
│ ReorgUtility │
│ │
│ Performs a REORG │
│ │
│ Individual Connect/ConnectResets are done for each so that │
│ the user can see when the log file is done with during the │
│ utility process │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
arg ReorgTable ReorgIndex
step = 'REORG'
call Connect
say c.itblue ' REORG' ReorgTable
say c.itblue ' in' DBname
say c.itblue ' w/ index' ReorgIndex
call TimingsBegin
call SQLDBS 'REORG TABLE' ReorgTable,
' IN' DBname,
' INDEX' ReorgIndex
if SQLCA.SQLCODE = -2205 then
do
say
say c.itred 'Specified Index does not exist yet'
say c.itred 'press <enter> to continue'
nimnull = SysGetKey('NOECHO')
return
end
else
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call TimingsEnd
call ConnectReset
return
RunstatUtility:
/*
┌──────────────────────────────────────────────────────────────────┐
│ RunstatUtility │
│ │
│ Performs a RUNSTAT │
│ │
│ Individual Connect/ConnectResets are done for each so that │
│ the user can see when the log file is done with during the │
│ utility process │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
arg RunstatTable RunstatIndex
step = 'RUNSTATS'
RunIndex.0 = 1
RunIndex.1 = RunstatIndex
call Connect
say c.itblue ' RUNSTAT' RunstatTable
say c.itblue ' for index' RunstatIndex
call TimingsBegin
call SQLDBS 'RUNSTATS ON TABLE' RunstatTable,
' AND INDEXES USING :RunIndex'
if SQLCA.SQLCODE = -2306 then
do
say
say c.itred 'Specified Index does not exist yet'
say c.itred 'press <enter> to continue'
nimnull = SysGetKey('NOECHO')
return
end
else
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call ConnectReset
call TimingsEnd
return
Roll1:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Roll1 │
│ │
│ Restore the database, calling the USER EXIT. │
│ Check to see if the database exists, if it does, the DBM │
│ sends back an SQLCODE "for your protection". │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> Restore from drive 0'
step = 'restore database'
call TimingsBegin
CALL SQLDBS 'RESTORE DATABASE' DBname,
'from 0 to' DBdrive
/* 'without rolling forward' <- makes it impossible to
do forward recovery - you get an sqlcode back that says
the database doesn't need rolling forward (sqlcode -1261) */
if SQLCA.SQLCODE = -2029 then
do
say
say c.itred ' **** USER EXIT FATAL ERROR ****'
say c.red ' Program will continue '
say c.red ' after display of SQLCA '
call SoftErrorSQL
return
end
else
if SQLCA.SQLCODE = +2505 then
do
call beep 100,50
say c.itred 'SQLCODE +2505'
say c.itred 'Warning: You about to write over '
say c.itred ' the existing database: '
say c.itred ' press <y> to continue '
say c.itred ' <n> to terminate '
ContRF = SysGetKey('NOECHO')
if ContRF = 'Y' | ContRF = 'y' then
do
call TimingsEnd
signal Roll1a
end
else
ContRF = 'N'
return
end
else
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call TimingsEnd
return
Roll1a:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Roll1a │
│ │
│ Ok, continue the restore. Make sure nobody is connected. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> Continue restore'
step = 'continue restore'
call TimingsBegin
call sqldbs 'CONTINUE RESTORE'
if SQLCA.SQLCODE = +2520 then
do
call beep 100,50
say c.itred 'SQLCODE +2520'
say c.itred 'Info: the database has been restored: '
say c.itred ' the backup version of the config '
say c.itred ' file was used '
say c.itred ' press <enter> to continue '
nimnull = SysGetKey('NOECHO')
end
else
if SQLCA.SQLCODE = -2010 then
do
call beep 100,50
say c.itred 'SQLCODE -2010'
say c.itred 'Another application is connected to '
say c.itred ' the DB - please disconnect them '
say c.itred ' so that this can continue '
say c.itred ' press <enter> to continue '
nimnull = SysGetKey('NOECHO')
signal Roll1
/* -2010 is fatal - start all over again */
end
else
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call TimingsEnd
return
Roll2:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Roll2 │
│ │
│ Roll forward to the end of the logs. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
if ContRF = 'N' then return
say c.itgreen '---> Roll Forward'
step = 'roll forward database'
call TimingsBegin
CALL SQLDBS 'rollforward database' DBname,
'to end of logs ',
'and stop'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call TimingsEnd
return
EXPORTemployee:
/*
┌──────────────────────────────────────────────────────────────────┐
│ EXPORTemployee │
│ │
│ Export the entire EMPLOYEE table. This is here because this │
│ is the largest table. You can edit the EXPORTed table to │
│ add more employees or special case rows. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> Export Employee'
step = 'Export employee'
exp = 'select * from employee order by id'
call Connect
CALL SQLDBS 'EXPORT :exp FROM' DBname,
'TO f:\FRDEMO\emp OF del ',
'MESSAGES msgexp.txt'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call ConnectReset
return
IMPORTemployee:
/*
┌──────────────────────────────────────────────────────────────────┐
│ IMPORTemployee │
│ │
│ Import the employee table. Table must be named │
│ "employee.del" and exist in the same directory as this. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> Import Employee'
step = 'Import employee'
call Connect
call TimingsBegin
CALL SQLDBS 'IMPORT to FRDEMO from ',
'employee.del of del ',
'insert into userid.employee ',
'MESSAGES msginp.txt '
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call ConnectReset
call TimingsEnd
return
MegaTuningParms:
/*
┌──────────────────────────────────────────────────────────────────┐
│ MegaTuningParms │
│ │
│ Set all values high: │
│ DBM sqlenseg to the max │
│ DB buffpool and related parms very high │
│ DB log retain and user exit ON │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> Set the DBM parms to MEGA values'
step = 'default DBM parms'
dbmval.0 = 2
dbmval.1 = 5 /* sqlenseg */
dbmval.2 = 1200
CALL SQLDBS 'UPDATE DATABASE MANAGER CONFIGURATION',
'USING :dbmval'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call StopDBM
call StartDBM
say c.itgreen '---> Set the DB parms to MEGA values'
step = 'Mega DB parms'
call Connect
/* ARRAY PARM VALUE DESCRIPTION */
dbval.0 = 16
dbval.1 = 16
dbval.2 = 3 /* log primary */
dbval.3 = 17
dbval.4 = 5 /* log secondary */
dbval.5 = 18
dbval.6 = 500 /* log file size */
dbval.7 = 2
dbval.8 = 800 /* buffpage */
dbval.9 = 8
dbval.10 = 4 /* db heap */
dbval.11 = 11
dbval.12 = 10 /* sort heap */
dbval.13 = 19
dbval.14 = 128 /* stmt heap */
dbval.15 = 21
dbval.16 = 15 /* attributes */
CALL SQLDBS 'UPDATE DATABASE CONFIGURATION',
'FOR' DBname,
'USING :dbval'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call ConnectReset
say
say c.yellow ' -> Tuning is at MEGA values'
say c.yellow ' -> Database Manager:'
say c.itgreen ' sqlenseg =' dbmval.2
say c.yellow ' -> Database:'
say c.itgreen ' logprimary =' dbval.2
say c.itgreen ' logsecondary =' dbval.4
say c.itgreen ' logfilsiz =' dbval.6
say c.itgreen ' buffpage =' dbval.8
say c.itgreen ' dbheap =' dbval.10
say c.itgreen ' sortheap =' dbval.12
say c.itgreen ' stmtheap =' dbval.14
say c.itgreen ' attribute =' dbval.16
pause
return
PerfTuningParms:
/*
┌──────────────────────────────────────────────────────────────────┐
│ PerfTuningParms │
│ │
│ Set all values high: │
│ DBM sqlenseg to the max │
│ DB buffpool and related parms very high │
│ DB log retain and user exit OFF │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> Set the DBM parms to perf values'
step = 'Perf DBM parms'
dbmval.0 = 2
dbmval.1 = 5 /* sqlenseg */
dbmval.2 = 802
CALL SQLDBS 'UPDATE DATABASE MANAGER CONFIGURATION',
'USING :dbmval'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call StopDBM
call StartDBM
say c.itgreen '---> Set the DB parms to perf values'
step = 'default DB parms'
call Connect
/* parm array */
dbval.0 = 14
dbval.1 = 16 /* log primary */
dbval.2 = 3
dbval.3 = 17 /* log secondary */
dbval.4 = 4
dbval.5 = 18 /* log file size */
dbval.6 = 500
dbval.7 = 2 /* buffpage */
dbval.8 = 800
dbval.9 = 8 /* db heap */
dbval.10 = 4
dbval.11 = 11 /* sort heap */
dbval.12 = 10
dbval.13 = 19 /* stmt heap */
dbval.14 = 128
CALL SQLDBS 'UPDATE DATABASE CONFIGURATION',
'FOR' DBname,
'USING :dbval'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call ConnectReset
say
say c.yellow ' -> Tuning is at PERF values'
say c.yellow ' -> Database Manager:'
say c.itgreen ' sqlenseg =' dbmval.2
say c.yellow ' -> Database:'
say c.itgreen ' logprimary =' dbval.2
say c.itgreen ' logsecondary =' dbval.4
say c.itgreen ' logfilsiz =' dbval.6
say c.itgreen ' buffpage =' dbval.8
say c.itgreen ' dbheap =' dbval.10
say c.itgreen ' sortheap =' dbval.12
say c.itgreen ' stmtheap =' dbval.14
pause
return
BaseTuningParms:
/*
┌──────────────────────────────────────────────────────────────────┐
│ BaseTuningParms │
│ │
│ Set all values high: │
│ DBM sqlenseg to the max │
│ DB buffpool and related parms low │
│ DB log retain and user exit OFF │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> Set the DBM parms to base values'
step = 'base DBM parms'
dbmval.0 = 2
dbmval.1 = 5 /* sqlenseg */
dbmval.2 = 802
CALL SQLDBS 'UPDATE DATABASE MANAGER CONFIGURATION',
'USING :dbmval'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call StopDBM
call StartDBM
say c.itgreen '---> Set the DB parms to base values'
step = 'default DB parms'
call Connect
/* parm array */
dbval.0 = 14
dbval.1 = 16 /* log primary */
dbval.2 = 2
dbval.3 = 17 /* log secondary */
dbval.4 = 2
dbval.5 = 18 /* log file size */
dbval.6 = 500
dbval.7 = 2 /* buffpage */
dbval.8 = 500
dbval.9 = 8 /* db heap */
dbval.10 = 4
dbval.11 = 11 /* sort heap */
dbval.12 = 10
dbval.13 = 19 /* stmt heap */
dbval.14 = 128
CALL SQLDBS 'UPDATE DATABASE CONFIGURATION',
'FOR' DBname,
'USING :dbval'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call ConnectReset
say
say c.yellow ' -> Tuning is at BASE'
say c.yellow ' -> Database Manager:'
say c.itgreen ' sqlenseg =' dbmval.2
say c.yellow ' -> Database:'
say c.itgreen ' logprimary =' dbval.2
say c.itgreen ' logsecondary =' dbval.4
say c.itgreen ' logfilsiz =' dbval.6
say c.itgreen ' buffpage =' dbval.8
say c.itgreen ' dbheap =' dbval.10
say c.itgreen ' sortheap =' dbval.12
say c.itgreen ' stmtheap =' dbval.14
pause
return
CreateFRDEMO:
/*
┌──────────────────────────────────────────────────────────────────┐
│ CreateFRDEMO │
│ │
│ Create the database. Currently doesn't allow the user │
│ to specify which drive to use. This would be a problem │
│ when this code is released. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> CREATE database' DBname 'on drive' DBdrive
step = 'Create database' DBname
call TimingsBegin
call SQLDBS 'CREATE DATABASE' DBname 'ON' DBdrive
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
Call TimingsEnd
return
CreateTables:
/*
┌──────────────────────────────────────────────────────────────────┐
│ CreateTables │
│ │
│ Create all tables used int he test. These tables are │
│ roughly "referentially" related to one another. That isn't │
│ the point of this test, so RI is not actually used. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> CREATE application TABLEs'
table1 = 'CREATE TABLE FRDEMO.EMPLOYEE',
'(ID integer NOT NULL,',
' NAME CHAR(75) NOT NULL,',
' address1 CHAR(75) NOT NULL,',
' address2 CHAR(75) NOT NULL,',
' address3 CHAR(75) NOT NULL,',
' address4 CHAR(75) NOT NULL,',
' state CHAR(10) NOT NULL,',
' zip char(10) NOT NULL,',
' DEPT INTEGER NOT NULL,',
' JOB CHAR(75) NOT NULL,',
' YEARS decimal(4,2) NOT NULL,',
' SALARY DECIMAL(7,2) NOT NULL,',
' COMM DECIMAL(7,2) NOT NULL)'
table2 = 'CREATE TABLE FRDEMO.DEPARTMENT',
'(DEPT INTEGER NOT NULL,',
' DEPTNAME CHAR(75) NOT NULL,',
' address1 CHAR(75) NOT NULL,',
' address2 CHAR(75) NOT NULL,',
' address3 CHAR(75) NOT NULL,',
' address4 CHAR(75) NOT NULL,',
' state CHAR(10) NOT NULL,',
' zip char(10) NOT NULL,',
' MANAGER integer NOT NULL,',
' DIVISION INTEGER NOT NULL,',
' LOCATION CHAR(75) NOT NULL)'
table3 = 'CREATE TABLE FRDEMO.DIVISION',
'(DIVISION INTEGER NOT NULL,',
' MANAGER integer NOT NULL,',
' DIRECTORATE INTEGER NOT NULL,',
' DIVNAME CHAR(75) NOT NULL,',
' address1 CHAR(75) NOT NULL,',
' address2 CHAR(75) NOT NULL,',
' address3 CHAR(75) NOT NULL,',
' address4 CHAR(75) NOT NULL,',
' state CHAR(10) NOT NULL,',
' zip char(10) NOT NULL)'
table4 = 'CREATE TABLE FRDEMO.DIRECTORATE',
'(DIRECTORATE INTEGER NOT NULL,',
' DIRECTOR integer NOT NULL,',
' DIRNAME CHAR(75) NOT NULL,',
' address1 CHAR(75) NOT NULL,',
' address2 CHAR(75) NOT NULL,',
' address3 CHAR(75) NOT NULL,',
' address4 CHAR(75) NOT NULL,',
' state CHAR(10) NOT NULL,',
' zip char(10) NOT NULL)'
call Connect
call TimingsBegin
say c.itblue ' CREATE TABLE EMPLOYEE'
step = 'CREATE TABLE EMPLOYEE'
call SQLEXEC 'EXECUTE IMMEDIATE :table1'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call TimingsEnd
call TimingsBegin
say c.itblue ' CREATE TABLE DEPARTMENT'
step = 'CREATE TABLE DEPARTMENT'
call SQLEXEC 'EXECUTE IMMEDIATE :table2'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call TimingsEnd
call TimingsBegin
say c.itblue ' CREATE TABLE DIVISION'
step = 'CREATE TABLE DIVISION'
call SQLEXEC 'EXECUTE IMMEDIATE :table3'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call TimingsEnd
call TimingsBegin
say c.itblue ' CREATE TABLE DIRECTORATE'
step = 'CREATE TABLE DIRECTORATE'
call SQLEXEC 'EXECUTE IMMEDIATE :table4'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call TimingsEnd
call ConnectReset
return
CreateIndexes:
/*
┌──────────────────────────────────────────────────────────────────┐
│ CreateIndexes │
│ │
│ Indexes don't have any direct bearing on this test, however, │
│ they are added for realism and for testing the effect of │
│ indexes and index changes to the log files. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen '---> CREATE application table INDEXs'
indexa = 'CREATE INDEX FRDEMO.DEPARTMENT_DEPT_I',
' ON FRDEMO.DEPARTMENT',
' (DEPT ASC)'
indexb = 'CREATE INDEX FRDEMO.EMPLOYEE_SALARY_I',
' ON FRDEMO.EMPLOYEE',
' (SALARY ASC)'
indexc = 'CREATE INDEX FRDEMO.EMPLOYEE_ID_I',
' ON FRDEMO.EMPLOYEE',
' (ID ASC)'
indexd = 'CREATE INDEX FRDEMO.DIVISION_I',
' ON FRDEMO.DIVISION',
' (DIVISION ASC)'
indexe = 'CREATE INDEX FRDEMO.DIRECTORATE_I',
' ON FRDEMO.DIRECTORATE',
' (DIRECTORATE ASC)'
call Connect
say c.itblue ' CREATE INDEX DEPARTMENT_DEPT_I'
step = 'CREATE INDEX DEPARTMENT_DEPT_I'
call SQLEXEC 'EXECUTE IMMEDIATE :indexa'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
say c.itblue ' CREATE INDEX EMPLOYEE_SALARY_I'
step = 'CREATE INDEX EMPLOYEE_SALARY_I'
call SQLEXEC 'EXECUTE IMMEDIATE :indexb'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
say c.itblue ' CREATE INDEX EMPLOYEE_ID_I'
step = 'CREATE INDEX EMPLOYEE_ID_I'
call SQLEXEC 'EXECUTE IMMEDIATE :indexc'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
say c.itblue ' CREATE INDEX DIVISION_I'
step = 'CREATE INDEX DIVISION_I'
call SQLEXEC 'EXECUTE IMMEDIATE :indexd'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
say c.itblue ' CREATE INDEX DIRECTORATE_I'
step = 'CREATE INDEX DIRECTORATE_I'
call SQLEXEC 'EXECUTE IMMEDIATE :indexe'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call ConnectReset
return
DropFRDEMO:
/*
┌──────────────────────────────────────────────────────────────────┐
│ DropFRDEMO │
│ │
│ Drop the database │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen ' ---> Drop database' DBname
step = 'drop the database'
call SQLDBS 'DROP DATABASE' DBname
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
return
DropTables:
/*
┌──────────────────────────────────────────────────────────────────┐
│ DropTables │
│ │
│ Drop all tables │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen ' ---> Drop all tables'
say c.itblue ' DROP table EMPLOYEE'
call Connect
step = 'DROP TABLE EMPLOYEE'
stmtbuf = 'DROP TABLE FRDEMO.EMPLOYEE'
call SQLEXEC 'EXECUTE IMMEDIATE :stmtbuf'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
say c.itblue ' DROP table DEPARTMENT'
step = 'DROP TABLE DEPARTMENT'
stmtbuf = 'DROP TABLE FRDEMO.DEPARTMENT'
call SQLEXEC 'EXECUTE IMMEDIATE :stmtbuf'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
say c.itblue ' DROP table DIVISION'
step = 'DROP TABLE DIVISION'
stmtbuf = 'DROP TABLE FRDEMO.DIVISION'
call SQLEXEC 'EXECUTE IMMEDIATE :stmtbuf'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
say c.itblue ' DROP table DIRECTORATE'
step = 'DROP TABLE DIRECTORATE'
stmtbuf = 'DROP TABLE FRDEMO.DIRECTORATE'
call SQLEXEC 'EXECUTE IMMEDIATE :stmtbuf'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call ConnectReset
return
DropIndexes:
/*
┌──────────────────────────────────────────────────────────────────┐
│ DropIndexes │
│ │
│ Drop all indexes │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itgreen ' ---> Drop all indexes'
call Connect
step = 'DROP INDEX DEPARTMENT_DEPT_I'
stmtbuf = 'DROP INDEX FRDEMO.DEPARTMENT_DEPT_I'
call SQLEXEC 'EXECUTE IMMEDIATE :stmtbuf'
if SQLCA.SQLCODE = -204 then say c.itblue ' (index was undefined)'
else
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
step = 'DROP INDEX EMPLOYEE_SALARY_I'
stmtbuf = 'DROP INDEX FRDEMO.EMPLOYEE_SALARY_I'
call SQLEXEC 'EXECUTE IMMEDIATE :stmtbuf'
if SQLCA.SQLCODE = -204 then say c.itblue ' (index was undefined)'
else
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
step = 'DROP INDEX EMPLOYEE_ID_I'
stmtbuf = 'DROP INDEX FRDEMO.EMPLOYEE_ID_I'
call SQLEXEC 'EXECUTE IMMEDIATE :stmtbuf'
if SQLCA.SQLCODE = -204 then say c.itblue ' (index was undefined)'
else
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
step = 'DROP INDEX DIVISION_I'
stmtbuf = 'DROP INDEX FRDEMO.DIVISION_I'
call SQLEXEC 'EXECUTE IMMEDIATE :stmtbuf'
if SQLCA.SQLCODE = -204 then say c.itblue ' (index was undefined)'
else
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
step = 'DROP INDEX DIRECTORATE_I'
stmtbuf = 'DROP INDEX FRDEMO.DIRECTORATE_I'
call SQLEXEC 'EXECUTE IMMEDIATE :stmtbuf'
if SQLCA.SQLCODE = -204 then say c.itblue ' (index was undefined)'
else
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
call ConnectReset
return
PopulateDirectorate:
step = 'Populate Directorate'
PopulateTable = 'FRDEMO.DIRECTORATE'
call PopulateCounts
PopulateCounter = 0
call PrePopulateInserts
do i = 1 to PopulateRowCount
directorate = i
directorate = directorate + 100000000
director = RANDOM(1,100000)
director = director + 100000000
interpret 'row = directorate||comma||director||comma||dirname||comma||address'
interpret 'stmtbuf = stmt||rparen||row||lparen'
call PopulateInsert
If FatalInsert = 'Y' then return
end
call PostPopulateInserts
return
PopulateDepartment:
step = 'Populate Department'
PopulateTable = 'FRDEMO.DEPARTMENT'
call PopulateCounts
PopulateCounter = 0
call PrePopulateInserts
do i = 1 to PopulateRowCount
department = i
department = department + 100000000
manager = RANDOM(1,100000)
division = RANDOM(1,100000)
manager = manager + 100000000
division = division + 100000000
interpret 'row = department||comma||deptname||comma||address||comma||manager||comma||division||comma||location'
interpret 'stmtbuf = stmt||rparen||row||lparen'
call PopulateInsert
If FatalInsert = 'Y' then return
end
call PostPopulateInserts
return
PopulateDivision:
step = 'Populate Division'
PopulateTable = 'FRDEMO.DIVISION'
call PopulateCounts
PopulateCounter = 0
call PrePopulateInserts
do i = 1 to PopulateRowCount
division = i
division = division + 100000000
manager = RANDOM(1,100000)
directorate = RANDOM(1,100000)
manager = manager + 100000000
directorate = directorate + 100000000
interpret 'row = division||comma||manager||comma||directorate||comma||divname||comma||address'
interpret 'stmtbuf = stmt||rparen||row||lparen'
call PopulateInsert
If FatalInsert = 'Y' then return
end
call PostPopulateInserts
return
PopulateEmployee:
step = 'Populate Employee'
PopulateTable = 'FRDEMO.EMPLOYEE'
call PopulateCounts
PopulateCounter = 0
call PrePopulateInserts
do i = 1 to PopulateRowCount
id = i
id = id + 100000000
if id < 100025000 then
job = "'MSR'"
else
job = "'AMSR'"
years = RANDOM(1,25)
salary = RANDOM(10000,99000)
comm = RANDOM(0,5000)
department = RANDOM(0,100000)
department = department + 100000000
interpret 'row = id||comma||detail||comma||department||comma||job||comma||years||comma||plus||salary||comma||plus||comm'
interpret 'stmtbuf = stmt||rparen||row||lparen'
call PopulateInsert
if FatalInsert = 'Y' then return
end
call PostPopulateInserts
return
PopulateInsert:
/*
┌──────────────────────────────────────────────────────────────────┐
│ PopulateInsert │
│ │
│ Execute the INSERT statement; check the commit count │
│ to see if it's time to COMMIT or ROLLBACK. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
step = stmtbuf
call SQLEXEC 'EXECUTE IMMEDIATE :stmtbuf'
if SQLCA.SQLCODE = -204 then
do
say
say c.itred 'Tables do not exist yet!'
FatalInsert = 'Y'
say c.itred 'press <enter> to continue'
nimnull = SysGetKey('NOECHO')
return
end
else
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
PopulateCounter = PopulateCounter + 1
if PopulateCounter = PopulateCommitCount then
do
tod = time()
say c.itblue ' 'PopulateOption c.itgreen tod 'after row 'i
step = 'commit/rollback an insert'
call SQLEXEC PopulateOption
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
PopulateCounter = 0
end
return
PrePopulateInserts:
/*
┌──────────────────────────────────────────────────────────────────┐
│ PrePopulateInserts: │
│ │
│ Display banner, setup the host variable "stmt". │
│ │
│ Get and save the beginning times. │
│ │
│ Start Using the database. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
stmt = 'INSERT INTO' PopulateTable 'VALUES '
say
say c.itmagenta '---> INSERT' PopulateRowCount 'rows'
say c.itmagenta ' 'PopulateOption' every' PopulateCommitCount 'rows'
FatalInsert = 'N'
call Connect
call TimingsBegin
return
PostPopulateInserts:
/*
┌──────────────────────────────────────────────────────────────────┐
│ PostPopulateInserts: │
│ │
│ Calculate ending time for the inserts. │
│ │
│ Display stats. While these don't have anything to do with │
│ Forward Recovery, they might be useful to demonstrate the │
│ typical time that INSERTs takes (keeping in mind that a lot │
│ time is spent in this program building the INSERT string). │
│ │
│ CONNECT RESET the database │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
call ConnectReset
call TimingsEnd
return
PopulateCounts:
/*
┌──────────────────────────────────────────────────────────────────┐
│ PopulateCounts │
│ │
│ Get the number of rows to INSERT, call PopulateOptionEdit │
│ to decide whether to COMMIT or ROLLBACK, and enter the count │
│ for COMMITting or ROLLBACKing. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say
say c.yellow '---> Populate' PopulateTable
say
say c.itgreen ' Enter number of rows to INSERT:'
pull PopulateRowCount
call PopulateOptionEdit
say c.itgreen ' Enter # of rows to 'PopulateOption' at:'
pull PopulateCommitCount
return
PopulateOptionEdit:
/*
┌──────────────────────────────────────────────────────────────────┐
│ PopulateOptionEdit │
│ │
│ Get and edit the PopulateOption. Valid values are either │
│ COMMIT or ROLLBACK. This gives the user the option of running │
│ the INSERTs either way, generating log files in any case, │
│ and saving some disk space if the choice is ROLLBACK. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
PopulateOption = 'COMMIT'
say c.itgreen ' Transact <C>ommit or <R>ollback ?'
PopulateOpt = SysGetKey('NOECHO')
if PopulateOpt = 'C' | PopulateOpt = 'c' then
PopulateOption = 'COMMIT'
else
if PopulateOpt = 'R' | PopulateOpt = 'r' then
PopulateOption = 'ROLLBACK'
else
do
say c.itred '---> Invalid Option: reenter'
signal PopulateOptionEdit
end
return
TimingsBegin:
/*
┌──────────────────────────────────────────────────────────────────┐
│ TimingsBegin │
│ │
│ Get time and date for begin of a timed perf test │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
if OptionTiming = 'Y' then
do
start_tod = time()
start_day = date('w')
start_date = date()
end
return
TimingsEnd:
/*
┌──────────────────────────────────────────────────────────────────┐
│ TimingsEnd │
│ │
│ Get time and date for the end of a timed perf test │
│ Display stats │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
if OptionTiming = 'Y' then
do
end_tod = time()
end_day = date('w')
end_date = date()
say c.itmagenta '---> Timings:'
say c.itmagenta ' Began' start_tod 'on' start_day start_date
say c.itmagenta ' Ended' end_tod 'on' end_day end_date
pause
end
return
ProgramInitialize:
/*
┌──────────────────────────────────────────────────────────────────┐
│ ProgramInitialize │
│ │
│ Register all the RexxUtil calls │
│ │
│ Setup all of the variables used in the program, also calls │
│ the SetColor routine to setup colors. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
call RxFuncAdd 'SysLoadFuncs','RexxUtil','SysLoadFuncs'
call RxFuncAdd 'SQLDBS','SQLAR','SQLDBS'
call RxFuncAdd 'SQLEXEC','SQLAR','SQLEXEC'
call SysLoadFuncs
address cmd '@ECHO OFF'
call SetColor
ConnectFlag = 'N'
OptionTiming = 'N'
COMMA = ','
RPAREN = '('
LPAREN = ')'
QUOTE = "'"
PLUS = '+'
ROW = ' '
DBdrive = 'D'
DBname = 'FRDEMO'
PopulateCommitCount = 0
id = 0
years = 0
salary = 0
comm = 0
manager = 0
director = 0
department = 0
division = 0
directorate = 0
name = "'Name'"
address1 = "'IBM at Southlake'"
address2 = "'40-A2-04'"
address3 = "'1 East Kirkwood Blvd'"
address4 = "'Roanoke'"
state = "'Texas'"
zip = "'76299-0015'"
interpret 'address = address1||comma||address2||comma||address3||comma||address4||comma||state||comma||zip'
interpret 'detail = name||comma||address'
job = "'Position'"
deptname = "'Department'"
location = "'IBM, Dallas System Center'"
divname = "'Personal System Support Center'"
dirname = "'Marketing and Technical Support'"
return
StopDBM:
/*
┌──────────────────────────────────────────────────────────────────┐
│ StopDBM │
│ │
│ Stops the Database Manager. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itblue ' STOP DB2/2'
step = 'stop database manager'
CALL SQLDBS 'STOP DATABASE MANAGER'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
return
StartDBM:
/*
┌──────────────────────────────────────────────────────────────────┐
│ StartDBM │
│ │
│ Starts the Database Manager. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
say c.itblue ' START DB2/2'
step = 'start database manager'
CALL SQLDBS 'START DATABASE MANAGER'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
return
Connect:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Connect │
│ │
│ Does a CONNECT to the database in question │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
step = 'CONNECT'
say c.itblue ' CONNECT'
call SQLEXEC 'CONNECT TO' DBname 'IN SHARED MODE'
if SQLCA.SQLCODE = 0 then
do
ConnectFlag = 'Y'
return
end
else signal ErrorSQL
return
ConnectReset:
/*
┌──────────────────────────────────────────────────────────────────┐
│ ConnectReset │
│ │
│ Does a CONNECT RESET │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
step = 'CONNECT RESET'
ConnectFlag = 'N'
say c.itblue ' CONNECT RESET'
say c.itblue
call SQLEXEC 'CONNECT RESET'
if SQLCA.SQLCODE \= 0 then signal ErrorSQL
return
EndProg:
/*
┌──────────────────────────────────────────────────────────────────┐
│ EndProg │
│ │
│ Check to see if a START USING had been done - if so then │
│ do a STOP USING so that the program can be immediately rerun │
│ from the same window. Also reset the colors. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
if ConnectFlag = 'Y' then
do
call ConnectReset
end
say c.reset
'exit'
ErrorSQL:
/*
┌──────────────────────────────────────────────────────────────────┐
│ ErrorSQL │
│ │
│ SQL Error routine - handles fatal SQL errors. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
tod = time()
call beep 100,200
say c.itred
say 'SQLCODE' SQLCA.SQLCODE 'at step:' step
say ' SQLERRP = ' SQLCA.SQLERRP
SAY ' SQLERRD 1 2 = ' SQLCA.SQLERRD.1 ' ' SQLCA.SQLERRD.2
SAY ' 3 4 = ' SQLCA.SQLERRD.3 ' ' SQLCA.SQLERRD.4
SAY ' 5 6 = ' SQLCA.SQLERRD.5 ' ' SQLCA.SQLERRD.6
SAY ' SQLWARN 0 1 = ' SQLCA.SQLWARN.0 ' ' SQLCA.SQLWARN.1
SAY ' 2 3 = ' SQLCA.SQLWARN.2 ' ' SQLCA.SQLWARN.3
SAY ' 4 5 = ' SQLCA.SQLWARN.4 ' ' SQLCA.SQLWARN.5
SAY ' 6 7 = ' SQLCA.SQLWARN.6 ' ' SQLCA.SQLWARN.7
SAY ' 8 9 = ' SQLCA.SQLWARN.8 ' ' SQLCA.SQLWARN.9
SAY ' 10 = ' SQLCA.SQLWARN.10
say ' SQLSTATE = ' SQLCA.SQLSTATE
say ' text message = ' SQLMSG
say
pause
signal EndProg
SoftErrorSQL:
/*
┌──────────────────────────────────────────────────────────────────┐
│ SoftErrorSQL │
│ │
│ SQL Error routine - handles soft (handleable) SQL errors │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
tod = time()
call beep 100,200
say c.red
say 'SQLCODE' SQLCA.SQLCODE 'at step:' step
say ' SQLERRP = ' SQLCA.SQLERRP
SAY ' SQLERRD 1 2 = ' SQLCA.SQLERRD.1 ' ' SQLCA.SQLERRD.2
SAY ' 3 4 = ' SQLCA.SQLERRD.3 ' ' SQLCA.SQLERRD.4
SAY ' 5 6 = ' SQLCA.SQLERRD.5 ' ' SQLCA.SQLERRD.6
SAY ' SQLWARN 0 1 = ' SQLCA.SQLWARN.0 ' ' SQLCA.SQLWARN.1
SAY ' 2 3 = ' SQLCA.SQLWARN.2 ' ' SQLCA.SQLWARN.3
SAY ' 4 5 = ' SQLCA.SQLWARN.4 ' ' SQLCA.SQLWARN.5
SAY ' 6 7 = ' SQLCA.SQLWARN.6 ' ' SQLCA.SQLWARN.7
SAY ' 8 9 = ' SQLCA.SQLWARN.8 ' ' SQLCA.SQLWARN.9
SAY ' 10 = ' SQLCA.SQLWARN.10
say ' SQLSTATE = ' SQLCA.SQLSTATE
say ' text message = ' SQLMSG
say
say 'press <enter> to continue'
nullinput = SysGetKey('NOECHO')
say c.normal
return
ErrorRC:
/*
┌──────────────────────────────────────────────────────────────────┐
│ ErrorRC │
│ │
│ RC Error routine - handles CMD.EXE return code │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
tod = time()
call beep 100,200
say c.itred
say ' >>> OS/2 has returned a fatal condition code'
say ' abending step = ' step
say ' source line = ' sourceline(sigl)
say ' time = ' tod
say ' RC = ' RC
say ' RESULT = ' RESULT
say
pause
signal EndProg
Error:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Error │
│ │
│ Rexx Error handling routine │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
tod = time()
call beep 220,1000
say c.itred
say ' >>> Rexx has returned a signal on error'
say ' abending step = ' step
say ' source line = ' sourceline(sigl)
say ' RC = ' RC
say ' time = ' tod
say
pause
signal EndProg
Syntax:
/*
┌──────────────────────────────────────────────────────────────────┐
│ Syntax │
│ │
│ Rexx Error handling routine │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
call beep 220,1000
say c.itred
say ' >>> Rexx has returned a signal on syntax'
say ' abending step = ' step
say ' source line = ' sourceline(sigl)
say
pause
signal EndProg
SetColor:
/*
┌──────────────────────────────────────────────────────────────────┐
│ SetColor │
│ │
│ This paragragh sets the colors used in the program. │
│ │
└──────────────────────────────────────────────────────────────────┘
*/
ansii.esc = '1B'x
c.normal = ansii.esc || '[0m'
c.highlite = ansii.esc || '[1m'
c.blackback = ansii.esc || '[40m'
c.green = c.normal || ansii.esc || '[32m'
c.grey = c.normal || ansii.esc || '[37m'
c.red = c.normal || ansii.esc || '[31m'
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