I need to get this down on paper so I do not forget.
Once again, large (2TB+) AS/400 migration to Oracle (ODA).
Doing some batch performance compares – the easy way.
Quick question though, how many SQL statement per second do you think the ODA can run for a batch job – complete SQL statements?
We are seeing up to 3600 per second – and it’s still slower than the 10 year old 400!!! WTF?
That AS/400 is capable of some amazing feats.
Anyways, back to the blog.
Here is a way you can replicate a batch load, firstly get a snapshot of performance on the as/400 with something like this:
SELECT JCPID as INNERPID, JCVERS as INNERVERS, trim(simd) || '-' || trim(vrjd),
count(1) as INNERCOUNT,
Avg(86400*(JCETDTIM-JCSTDTIM)) as INNERAVERAGE,
min(86400*(JCETDTIM-JCSTDTIM)) AS INNERMIN,
max(86400*(JCETDTIM-JCSTDTIM)) AS INNERMAX, avg(jcpwprcd) as "ROWS PROCESSED"
from svm900.f986114@purgatorydb t1,ol900.f9860@purgatorydb, copd900.f983051@purgatorydb, svm900.f986110@purgatorydb t2
where trim(t1.jcpid) = trim(siobnm)
and trim(t1.jcvers) = trim (vrvers)
and trim(t1.jcpid) = trim (vrpid)
and t1.jcjobnbr=t2.jcjobnbr
and t1.jcexehost = 'PURGATORY'
group by 'runube JDE JDE PY900 *ALL ' || JCPID || ' ' || JCVERS ||
CASE WHEN jcjobque IS NULL THEN N'QBATCH' WHEN jcjobque = ' ' THEN N'QBATCH' ELSE jcjobque END || ' Batch Hold Save', t1.jcpid, JCVERS, trim(simd) || '-' || trim(vrjd)
having Avg(86400*(JCETDTIM-JCSTDTIM)) > 15 and Avg(86400*(JCETDTIM-JCSTDTIM)) < 15000;
This is going to give you the batch jobs on the AS/400 that on average tool more than 15 seconds and less than 150000 seconds.
My results give me about 700 jobs.
I can then use the list and save it off in excel as a baseline of performance – because this is going to be handy when comparing with oracle.
I can then feed this into a file in Linux and rip it apart to generate a script:
cat *.list | awk '{printf("./runube.ksh %s %s >> %s_%s.out\n",$1,$2,$1,$2)}' > runAWR_new.ksh
which creates something like:
./runube.ksh R03B413A S1501 >> R03B413A_S1501.out
./runube.ksh R03B50 S0001 >> R03B50_S0001.out
./runube.ksh R03B500X F5001 >> R03B500X_F5001.out
./runube.ksh R03B50D F8101 >> R03B50D_F8101.out
./runube.ksh R56202 S1501 >> R56202_S1501.out
./runube.ksh R03B571 F5004 >> R03B571_F5004.out
./runube.ksh R03B571 F5000 >> R03B571_F5000.out
./runube.ksh R42800 S5917 >> R42800_S5917.out
./runube.ksh R42800 S1713 >> R42800_S1713.out
./runube.ksh R42800 S1729 >> R42800_S1729.out
./runube.ksh R42800 S1727 >> R42800_S1727.out
./runube.ksh R047001A S0001 >> R047001A_S0001.out
./runube.ksh R09801E ZJDE0001 >> R09801E_ZJDE0001.out
./runube.ksh R11414A S0028 >> R11414A_S0028.out
./runube.ksh R11414A S0002 >> R11414A_S0002.out
./runube.ksh R30601 S0001 >> R30601_S0001.out
./runube.ksh R30601 S0001 >> R30601_S0001.out
./runube.ksh R42995 S8301 >> R42995_S8301.out
I then have my runube.ksh script that does the following:
$ vi runube.ksh
#!/bin/ksh
if [ $# -lt 2 ]
then
echo "USAGE: $0 REPORT VERSION"
exit
fi
echo "set feedback off" > sql$$.sql
echo "EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;" >> sql$$.sql
echo "select 'START##' || max(snap_id) from dba_hist_snapshot ;" >> sql$$.sql
echo "quit;" >> sql$$.sql
STARTSNAP=`sqlplus JDE/PASS@JDEPROD @sql$$.sql |grep \#\# |grep -v \'|grep START|awk -F\# '{print $3}'`
echo $1_$2 RUNNING##
echo "<h1> UBE $1 VERSION $2 </h1>">$1_$2_AWR.html
echo "<h2>">>$1_$2_AWR.html
(time runube JDE PASS PD900 *ALL $1 $2 QBATCH Interactive Hold Save) 2>&1 | grep m |egrep '^user|^sys|^real' >> $1_$2_AWR.html
echo "</h2>">>$1_$2_AWR.html
ENDSNAP=`sqlplus JDE/PASS@JDEPROD @sql$$.sql |grep \#\# |grep -v \'|grep START|awk -F\# '{print $3}'`
#AWR time <-- This is a comment – how RAD!
echo "set linesize 8000" >sql$$.sql
echo "set feedback off;" >>sql$$.sql
echo "set heading off;" >>sql$$.sql
echo "set verify off;" >>sql$$.sql
echo "SELECT output FROM TABLE(dbms_workload_repository.awr_report_html ((select dbid from v\$database),(select instance_number from v\$instance),$STARTSNAP,$ENDSNAP,8 ));" >> sql$$.sql
echo "quit;" >> sql$$.sql
cat sql$$.sql
sqlplus JDE/PASS@JDEPROD @sql$$.sql >> $1_$2_AWR.html
rm -f ./sql$$.sq
#aws s3 cp $1_$2_AWR.html s3://mybucketofawr/$1_$2_AWR.html
RAD hey!!
This has done a lot. Firstly it’s got a baseline of performance stats from the 400
Created a script that will run all of these jobs (one after the other) there is not parallel to make sure that the AWR’s are good
It generates AWR’s for you for each job
It then will put them into an S3 bucket if you are nerdy like me
Then, when you run them all on your new kit, you can have a spreadsheet like the one below which can compare the performance of the AS/400 and the new kit:
The spreadsheet can calculate the URL to get to the AWR in S3, so you can distribute these results and ppl can see the reports and see why things are performing the way that they were.
Note that if you do not want the AWR’s or anything like that, you can have the SQL generate a simple and native runube command:
SELECT 'runube JDE PASSPY900 *ALL ' || JCPID || ' ' || JCVERS ||
CASE WHEN jcjobque IS NULL THEN N'QBATCH' WHEN jcjobque = ' ' THEN N'QBATCH' ELSE jcjobque END || ' Batch Hold Save',
t1.JCPID as INNERPID, t1.JCVERS as INNERVERS, simd || vrjd,
count(1) as INNERCOUNT,
Avg(86400*(JCETDTIM-JCSTDTIM)) as INNERAVERAGE,
min(86400*(JCETDTIM-JCSTDTIM)) AS INNERMIN,
max(86400*(JCETDTIM-JCSTDTIM)) AS INNERMAX
from svm900.f986114@purgatorydb t1,ol900.f9860@purgatorydb, copd900.f983051@purgatorydb, svm900.f986110@purgatorydb t2
where trim(t1.jcpid) = trim(siobnm)
and trim(t1.jcvers) = trim (vrvers)
and trim(t1.jcpid) = trim (vrpid)
and t1.jcjobnbr=t2.jcjobnbr
and t1.jcexehost = 'PURGATORY'
group by 'runube JDE JDE PY900 *ALL ' || JCPID || ' ' || JCVERS ||
CASE WHEN jcjobque IS NULL THEN N'QBATCH' WHEN jcjobque = ' ' THEN N'QBATCH' ELSE jcjobque END || ' Batch Hold Save', t1.jcpid, JCVERS, simd || vrjd
having Avg(86400*(JCETDTIM-JCSTDTIM)) > 15 and Avg(86400*(JCETDTIM-JCSTDTIM)) < 15000;
