home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 18 REXX
/
18-REXX.zip
/
rexxgdb2.zip
/
Run-Exec.CMD
< prev
next >
Wrap
OS/2 REXX Batch file
|
1997-08-10
|
9KB
|
310 lines
/* rexx */
arg numrows .
/*
------------------------------------------------------------------------
Run-EXEC
DB2/2 Benchmark with REXX/Dynamic SQL
To insert 'numrows' rows to the STAFF table of the DB2/2 SAMPLE
Database & to read those back into REXX program
Utilizing IBM DB2/2 API (SQLEXEC)
Copyright (C) 1995 Global Automation Co.
All Rights Reserved
Written By Simon Husin
March - December, 1995
------------------------------------------------------------------------
To use from an OS/2 Full Screen Command Prompt Screen:
Type Run-EXEC 'numrows' and press Enter
where 'numrows' is number of rows to insert w/ ID > 1000
if not entered or invalid (<1 or > 9999), it is set to 100
------------------------------------------------------------------------
*/
'@echo off'
line = copies('-', 79)
call ShowTitle
/*
Validate 'numrows'
*/
if numrows = '' then numrows = 100
if datatype(numrows) \= 'NUM' then numrows = 100
if numrows < 1 | numrows > 9999 then numrows = 100
numrows = numrows % 1
/*
Initialize all SQL statements to be prepared later in the program
Also the SQLEXEC to execute those statements
*/
/* SQL Select data from table Staff */
sqlquery = 'SELECT ID,NAME,DEPT,JOB,YEARS,SALARY,COMM' ||,
' FROM STAFF' ||,
' WHERE ID > ?'
/* SQL Fetch C1 */
sqlfetch = 'FETCH C1' ||,
' INTO :ID,:NAME,:DEPT,:JOB,:YEARS:YEARSI,:SALARY,'||,
' :COMM:COMMI'
/* SQL Insert w/ placeholders */
sqlinsert = ' INSERT' ||,
' INTO STAFF(ID,NAME,DEPT,JOB,YEARS,SALARY,COMM)'||,
' VALUES( ?, ?, ?, ?, ?, ?, ?)'
/* SQL Execute S2 */
sqlexins = 'EXECUTE S2 USING' ||,
' :COUNT,:NAME,:DEPT,:JOB,:YEARS:YEARSI,:SALARY,'||,
' :COMM:COMMI'
/* 2 SQL Select COUNTs in a UNION */
sqlcount = 'SELECT COUNT(*)' ||,
' FROM STAFF' ||,
' UNION ' ||,
'SELECT COUNT(*)' ||,
' FROM STAFF' ||,
' WHERE ID > 1000'
/* SQL Delete created rows */
sqldelete = 'DELETE FROM STAFF WHERE ID > 1000'
/*
Attach REXX functions SQLDBS and SQLEXEC to access the Database 2 for
OS/2 (DB2/2)
*/
if rxfuncquery('SQLDBS') \= 0 then do
say 'Attaching REXX DB2/2 Database Manager APIs...'
call rxfuncadd 'SQLDBS', 'DB2AR', 'SQLDBS'
end
if rxfuncquery('SQLEXEC') \= 0 then do
say 'Attaching REXX DB2/2 SQL APIs...'
call rxfuncadd 'SQLEXEC', 'DB2AR', 'SQLEXEC'
end
/* Activate DB2/2 via API*/
say 'Starting Database Manager...'
call SQLDBS 'START DATABASE MANAGER'
if sqlca.sqlcode \= 0 &,
sqlca.sqlcode \= -1026 &, /* DB2/2 is already active */
sqlca.sqlcode \= -1063 then /* STARTDBM was successful */
call sqlerror 100
/*
------------------------------------------------------------------------
*/
call SQLEXEC 'CONNECT RESET' /* disconnecting prev. connect.*/
/*
------------------------------------------------------------------------
*/
/* Open the database */
say 'Connecting to Sample Database...'
call time 'R'
call SQLEXEC 'CONNECT TO SAMPLE IN SHARE MODE'
elapse.connect = time('E')
/* Change Current Query Optimization to 0 */
say 'Set Current Query Optimization to 0...'
call time 'R'
sql = 'SET CURRENT QUERY OPTIMIZATION 0'
call SQLEXEC 'EXECUTE IMMEDIATE :sql'
elapse.queryopt= time('E')
select
/*unit of work not done*/
when sqlca.sqlcode = -752 then call sqlerror -752
/*database not found*/
when sqlca.sqlcode = -1013 then call sqlerror -1013
/*db dir. not found*/
when sqlca.sqlcode = -1031 then call sqlerror -1031
when sqlca.sqlcode \= 0 then
say 'SQLCA.SQLCODE='sqlca.sqlcode
otherwise nop
end
/*
------------------------------------------------------------------------
*/
/* Prepare the query */
CALL SQLEXEC 'PREPARE S1 FROM :SQLQUERY'
/* Declare cursor */
CALL SQLEXEC 'DECLARE C1 CURSOR FOR S1'
/* Open cursor */
startid = 0
CALL SQLEXEC 'OPEN C1 USING :STARTID'
/* Fetch one row from the STAFF table */
CALL SQLEXEC sqlfetch
if sqlca.sqlcode \= 0 then
call sqlerror 101
/* Make corrections based on the NULL indicators */
if yearsi = -1 then years = 0
if commi = -1 then comm = 0
/* Display the first (fetched) STAFF rows */
say 'ID.......' id
say 'NAME.....' name
say 'DEPT.....' dept
say 'JOB......' job
say 'YEARS....' left(format(years, 2), 20) ||,
'(NULL indicator='yearsi')'
say 'SALARY...' format(salary, 6, 2)
say 'COMM.....' left(format(comm, 6, 2), 20) ||,
'(NULL indicator='commi')'
/* Close the cursor */
CALL SQLEXEC 'CLOSE C1'
/*
------------------------------------------------------------------------
*/
/* Inserting 'NUMROWS' rows with a commit for every 100 inserts */
maxid = numrows + 1000
say 'Inserting first Staff row' numrows' times w/ a commit @100 rows...'
call Time 'R'
CALL SQLEXEC 'PREPARE S2 FROM :SQLINSERT'
/* Do 'numrows' inserts w/ commits every 100 times */
do count = 1001 to maxid while sqlca.sqlcode = 0
CALL SQLEXEC sqlexins
if right(count, 2) \= '00' then iterate
if count = maxid then leave
CALL SQLEXEC 'COMMIT'
CALL SQLEXEC 'PREPARE S2 FROM :SQLINSERT'
end
if sqlca.sqlcode \= 0 then
call sqlerror 201
CALL SQLEXEC 'COMMIT'
elapse.insert = time('E')
/*
------------------------------------------------------------------------
*/
/* Reading the 'NUMROWS' rows just inserted */
say 'Reading all rows with ID > 1000 (into memory only)...'
call Time 'R'
CALL SQLEXEC 'PREPARE S1 FROM :SQLQUERY'
startid = 1000
CALL SQLEXEC 'OPEN C1 USING :STARTID'
do count = 1 until sqlca.sqlcode \= 0
CALL SQLEXEC sqlfetch
end
CALL SQLEXEC 'CLOSE C1'
elapse.readnew = time('E')
say 'Number of rows read..:' count-1
/*
------------------------------------------------------------------------
*/
/* Reading rows from staff table using DB2/2 COUNT(*) and UNION */
say 'Obtaining #rows w/ ID > 0 & 1000 w/ SQL COUNT & UNION...'
call Time 'R'
CALL SQLEXEC 'PREPARE S2 FROM :SQLCOUNT'
CALL SQLEXEC 'DECLARE C2 CURSOR FOR S2'
CALL SQLEXEC 'OPEN C2'
CALL SQLEXEC 'FETCH C2 INTO :COUNT'
do forever while sqlca.sqlcode = 0
say '- Found' count 'rows...'
CALL SQLEXEC 'FETCH C2 INTO :COUNT'
end
CALL SQLEXEC 'CLOSE C2'
elapse.count = time('E')
/*
------------------------------------------------------------------------
*/
/* Deleting all (new) rows */
say 'Deleting all rows with ID > 1000...'
call Time 'R'
CALL SQLEXEC 'EXECUTE IMMEDIATE :SQLDELETE'
elapse.delete = time('E')
/*
------------------------------------------------------------------------
*/
/* Committing the unit of works */
say 'Committing the unit of works...'
call Time 'R'
CALL SQLEXEC 'COMMIT'
elapse.commit = time('E')
/*
------------------------------------------------------------------------
*/
/* Close the database */
say 'Disconnecting from Sample Database...'
call time 'R'
call SQLEXEC 'CONNECT RESET'
elapse.disconnect = time('E')
/*
------------------------------------------------------------------------
*/
call ShowTitle
tottime = 0
tottime = tottime + elapse.connect
tottime = tottime + elapse.queryopt
tottime = tottime + elapse.insert
tottime = tottime + elapse.readnew
tottime = tottime + elapse.count
tottime = tottime + elapse.delete
tottime = tottime + elapse.commit
tottime = tottime + elapse.disconnect
tottimefactor = tottime / 100
say line
say left('Results', 20) right('Seconds', 12) right('Total %', 12)
say line
say left('Connect', 20) format(elapse.connect, 9, 2) format(elapse.connect / tottimefactor, 9, 2)
say left('Query Opt.', 20) format(elapse.queryopt, 9, 2) format(elapse.queryopt / tottimefactor, 9, 2)
say left('Insert', 20) format(elapse.insert, 9, 2) format(elapse.insert / tottimefactor, 9, 2)
say left('Read New', 20) format(elapse.readnew, 9, 2) format(elapse.readnew / tottimefactor, 9, 2)
say left('Count', 20) format(elapse.count, 9, 2) format(elapse.count / tottimefactor, 9, 2)
say left('Delete', 20) format(elapse.delete, 9, 2) format(elapse.delete / tottimefactor, 9, 2)
say left('Commit', 20) format(elapse.commit, 9, 2) format(elapse.commit / tottimefactor, 9, 2)
say left('Disconnect', 20) format(elapse.disconnect, 9, 2) format(elapse.disconnect / tottimefactor, 9, 2)
say line
say left('Total', 20) format(tottime, 9, 2) format(tottime / tottimefactor, 9, 2)
say line
return 0
ShowTitle:
'cls'
say
say 'RUN-EXEC --- Benchmarking REXX - DB2/2 Interface'
say ' Using IBM DB2/2 REXX API (only)'
say 'Copyright (C) 1995 Global Automation Co.'
say 'All Rights Reserved'
say
return
sqlerror:
arg errorcode
say
say 'SQL/DBS Related errors detected.'
say 'ERRORCODE =' errorcode
say 'RESULT =' result
say 'SQLCODE =' sqlca.sqlcode
say 'SQLSTATE =' sqlca.sqlstate
CALL SQLDBS 'GET MESSAGE INTO :MSG'
say 'SQLDBS MSG ='msg
say 'Program terminated!'
exit 0