View previous topic :: View next topic
|
Author |
Message |
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
Hi,
I ran a test to execute an SQL query using IKJEFT01 & SYNCSORT programs. But, I am not able to determine which one is the best.
Below given are the "STEP TERMINATION REPORTING" copied from JESYSMSG.
for IKJEFT01:
Code: |
* HH.MM.SS.TH MMM.THT *
* STEPNAME PRIV AREA SIZE 31744K PAGES SWAP IN 0 STEP VECTOR TIME 00.00.00.00 *
* PROCSTEP STEPFT01 PRIV USED TOP 408K PAGES SWAP OUT 0 INIT VECTOR TIME 00.00.00.00 *
* PGM NAME IKJEFT01 PRIV USED BOT 472K REREADS 0 CPU TCB TIME 00.00.00.04 0.000 *
* COMP CODE 0004 STEP SERVICE 1,272 TAPE MOUNTS 0 CPU SRB TIME 00.00.00.00 0.000 *
* MEMLIMIT SIZE 200MB CPU TIME 0.000 *
* *
* UNIT-T-DDNAME------EXCP CNT---MB TRANSFER UNIT-T-DDNAME------EXCP CNT---MB TRANSFER UNIT-T-DDNAME------EXCP CNT---MB TRANSFER *
* 3447 STEPLIB 10 0.107 3458 STEPLIB 108 1.157 *
* *
* EXCP TOTAL 118 TOTAL DASD MB TRANSFERS 1.264 TOTAL TAPE MB TRANSFERS 0.000 *
|
for SYNCSORT:
Code: |
HH.MM.SS.TH MMM.THT *
* STEPNAME PRIV AREA SIZE 31744K PAGES SWAP IN 0 STEP VECTOR TIME 00.00.00.00 *
* PROCSTEP STEPSORT PRIV USED TOP 320K PAGES SWAP OUT 0 INIT VECTOR TIME 00.00.00.00 *
* PGM NAME SYNCSORT PRIV USED BOT 1132K REREADS 0 CPU TCB TIME 00.00.00.34 0.005 *
* COMP CODE 0000 STEP SERVICE 10,533 TAPE MOUNTS 0 CPU SRB TIME 00.00.00.00 0.000 *
* MEMLIMIT SIZE 200MB CPU TIME 0.383 *
* *
* UNIT-T-DDNAME------EXCP CNT---MB TRANSFER UNIT-T-DDNAME------EXCP CNT---MB TRANSFER UNIT-T-DDNAME------EXCP CNT---MB TRANSFER *
* 3447 STEPLIB 0 0.000 3458 STEPLIB 34 0.364 *
* *
* EXCP TOTAL 34 TOTAL DASD MB TRANSFERS 0.364 TOTAL TAPE MB TRANSFERS 0.000 * |
My plan is to change all the IKJEFT01 to SYNCSORT since the SQL querying is supported by SYNCSORT.
Please suggest if it is worth in terms of cost and other important factors.
Thanks. |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
What were the savings in both resource and hard cash ? |
|
Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
I don't know why it does not appear good though the preview was showing correct alignment !! |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
well if you change the world to use syncsort, you are now locked into a third party product.
What are the differences in JCL/Parms/cntl cards?
what is your sql? what are the differences in resource use?
are your results always going to be sorted?
what benefits do you receive from using syncsort over the old ibm IKJEF?
What does your DBA have to say? |
|
Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
Dick, I want to know the details you have asked and that is why I posted the topic......
DBA's could not help.
Thanks. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
I don't know why it does not appear good though the preview was showing correct alignment !! |
I don't know what this is telling me
Suggest that almost all of the time in an sql query will be servicing the query in db2 and whether the query is submitted one way or the other will have little to do with the performance of the query. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
it always difficult to tune only two different runs
are You sure You are comparing the same things ???
look at the service units and tcb and cpu times
I see nothing favorable to sort
the only thing that looks better in the sort sample are the steplib I/Os
from what You posted looks like it is not worth, but,
again to advice properly many many more tests must be carried on... |
|
Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
Ok. Thank you all for your inputs. |
|
Back to top |
|
|
MBabu
Active User
Joined: 03 Aug 2008 Posts: 400 Location: Mumbai
|
|
|
|
ramsri wrote: |
My plan is to change all the IKJEFT01 to SYNCSORT since the SQL querying is supported by SYNCSORT.
|
What do you mean by 'supported by'? It obviously works in both TSO and SYNCSORT.
Your results show TSO using .04 CPU sec vs .34 sec for Syncsort but as Enrico said, a single run in a complex multitasking system like z/OS is usually worthless. Also the sample runtime is extremely small so overhead of starting TSO or Syncsort may be more or less of the total work done in a real run meaning that a small run of 1 query probably doesn't represent the work you really want to do. The overhead of starting TSO is usually pretty small so unless this is to be run millions of times, making the change is probably not worth the effort, even if Syncsort does eventually prove to be a little faster. |
|
Back to top |
|
|
|