Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Sort Merge tune performance
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Fri Jul 20, 2012 12:24 am    Post subject: Sort Merge tune performance
Reply with quote

We are processing a huge VSAM file (around 35 million records, 1282 record length) and add around 50,000 records from another VSAM to it each day. Its like a history file and we maintain both VSAM and flat file versions of it (can't change that its business) as output. It takes around 30 mins to process as of now. We are going to add another 35 million records because we acquired a new company. Any performance tuning help is greatly appreciated.

Code:

//DCAMS002 EXEC PGM=SORT
//SYSUDUMP DD   SYSOUT=*
//SYSPRINT DD   SYSOUT=*
//SYSOUT   DD   SYSOUT=*
//SORTIN01 DD   DSN=VTEST.HISTFILE,DISP=SHR
//SORTIN02 DD   DSN=VTEST.NEWFILE,DISP=SHR
//SORTOF01 DD   DSN=VTEST.HISTFILE.TEMP,DISP=SHR
//SORTOF02 DD   DSN=TEST.HISTFILE(+1),
//          DISP=(NEW,CATLG,DELETE),
//          UNIT=SYSDA,
//          SPACE=(CYL,(4200,4200),RLSE),
//          VOL=(,,,40),
//          DCB=(RECFM=FB,LRECL=1282,BLKSIZE=0)
//SYSIN    DD *
//   OPTION DYNALLOC=(,250)
//   MERGE FIELDS=(1,52,CH,A)
//   OUTFIL FNAMES=SORTOF01,INCLUDE=(23,10,CH,GE,DATE1(-)-120)
//   OUTFIL FNAMES=SORTOF02,INCLUDE=(23,10,CH,GE,DATE1(-)-120)


The current SORT SYSOUT is given below:

Code:

MAIN STORAGE = (MAX,6291456,6291456)
MAIN STORAGE ABOVE 16MB = (6234096,6234096)
OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,SAMEMT=Y,DYNSPC=256
OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=ABEND,MSGDDN=SYSOUT
OPTIONS: VIO=N,RESDNT=ALL ,SMF=NO   ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N,ABCODE=MSG
OPTIONS: RESALL=32768,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,COBEXIT=COB2
OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0
OPTIONS: VLSHRT=N,ZDPRINT=N,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=262144,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
OPTIONS: NULLOUT=RC0
VSAM ACCESS METHOD USED FOR SORTIN01
VSAM ACCESS METHOD USED FOR SORTIN02
STORAGE USED FOR OUTFIL : BELOW 16M = 20480, ABOVE 16M = 272384
SORTOF01 : VSAM USED, LRECL = 1282, BLKSIZE = 1536, TYPE = F
SORTOF02 : EXCP USED, LRECL = 1282, BLKSIZE = 26922, TYPE = FB
DC-K51707 CB-K90014 DD-K51707 E8-K51707
INSERT 0, DELETE 0
RECORDS - IN: 36971229, OUT: 36971229
SORTOF01 : DELETED = 1, REPORT = 0, DATA = 36971228
SORTOF01 : TOTAL IN = 36971229, TOTAL OUT = 36971228
SORTOF02 : DELETED = 1, REPORT = 0, DATA = 36971228
SORTOF02 : TOTAL IN = 36971229, TOTAL OUT = 36971228
END OF DFSORT
Back to top
View user's profile Send private message

Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Jul 20, 2012 2:00 am    Post subject: Reply to: Sort Merge tune performance
Reply with quote

divya_maddi,

You can move the INCLUDE condition upfront. Try these control cards

Code:

//SYSIN    DD *                           
  OPTION DYNALLOC=(,250)                   
  INCLUDE COND=(23,10,CH,GE,DATE1(-)-120) 
  MERGE FIELDS=(1,52,CH,A)                 
  OUTFIL FILES=(01,02)                     
//*
Back to top
View user's profile Send private message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Fri Jul 20, 2012 2:43 am    Post subject:
Reply with quote

Just tried running the job with the new control card by having the INCLUDE upfront. Could not see an improvement. Please let me know if there is another way.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Jul 20, 2012 5:17 am    Post subject:
Reply with quote

divya_maddi,

Please add //SORTDIAG DD DUMMY to your JCL and re-run the job and send us the complete sysout to DFSORT hot line: dfsort@us.ibm.com
Back to top
View user's profile Send private message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Fri Jul 20, 2012 9:32 pm    Post subject: sysout with //SORTDIAG DD DUMMY
Reply with quote

I am going to send the sysout to the email ID you mentioned above, but I am pasting it here as well just in case. Any performance tuning help is greatly appreciated.

Thanks!

Code:


1ICE805I 1 JOBNAME: DMIVSAHT , STEPNAME: DCAMS002
 ICE802I 0 BLOCKSET     TECHNIQUE IN CONTROL
 ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
 ICE857I 0 C=2, LA=80092, DA=6444, LB=1912, DB=1056, MB=136, CB=36824, UB=128
 ICE858I 0 LA=78172, DA=6144, AA=72028, BA=217, CP=1, TA=266
 ICE859I 0 LB=1844, DB=1068, AB=776, BB=0, CP=0, RS=0, TB=20
 ICE860I 0 F=YY, P=2, M=N, B=256
 ICE751I 0 C5-K90025 C6-K90025 C7-K51707 C8-K90025 EE-K51707 E9-K51707 C9-BASE   E5-K51707 E7-K90025
 ICE143I 0 BLOCKSET     MERGE TECHNIQUE SELECTED
 ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
 ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 09:31 ON FRI JUL 20, 2012 -
0             OPTION DYNALLOC=(,250)
              INCLUDE COND=(23,10,CH,GE,DATE1(-)-120)
              MERGE FIELDS=(1,52,CH,A)
              OUTFIL FILES=(01,02)
 ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
 ICE857I 0 C=2, LA=80092, DA=6444, LB=1912, DB=1056, MB=136, CB=36824, UB=128
 ICE858I 0 LA=78172, DA=6144, AA=72028, BA=217, CP=1, TA=266
 ICE859I 0 LB=1844, DB=1068, AB=776, BB=0, CP=0, RS=0, TB=20
 ICE860I 0 F=YY, P=2, M=N, B=256
 ICE751I 0 C5-K90025 C6-K90025 C7-K51707 C8-K90025 EE-K51707 E9-K51707 C9-BASE   E5-K51707 E7-K90025
 ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
 ICE252I 1 PARMLIB OPTIONS WERE MERGED WITH INSTALLATION MODULE DEFAULTS
 ICE088I 0 DMIVSAHT.DCAMS002.        , INPUT LRECL = 1282, BLKSIZE = 1536, TYPE = F
 ICE093I 0 MAIN STORAGE = (MAX,6291456,6291456)
 ICE156I 0 MAIN STORAGE ABOVE 16MB = (6234096,6234096)
 ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256
 ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=ABEND,MSGDDN=SYSOUT
 ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=NO   ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N             ,ABCODE=MSG
 ICE130I 0 OPTIONS: RESALL=32768,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,COBEXIT=COB2
 ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0
 ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=N,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
 ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=262144,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
 ICE235I 0 OPTIONS: NULLOUT=RC0
 ICE084I 1 VSAM ACCESS METHOD USED FOR SORTIN01
 ICE084I 1 VSAM ACCESS METHOD USED FOR SORTIN02
 ICE889I 0 CT=MAX     , SB=3, L=0, D=0000, CCW=1MAM
 ICE901I 0 N 01PP10 02PP10
 ICE231I 0 STORAGE USED FOR OUTFIL : BELOW 16M = 20480, ABOVE 16M = 272384
 ICE855I 0 SORTOF01 : TX=N, R= , L= , B= , BL=0, BR=0, DCT=2, VS=N, RU=Y, SB=6
 ICE210I 0 SORTOF01 : VSAM USED, LRECL = 1282, BLKSIZE = 1536, TYPE = F
 ICE855I 0 SORTOF02 : TX=N, R=J, L=J, B=D, BL=0, BR=0, DCT=4, VS=N, RU=X, SB=8
 ICE210I 0 SORTOF02 : EXCP USED, LRECL = 1282, BLKSIZE = 26922, TYPE = FB
 ICE751I 1 DC-K51707 CB-K90014 DD-K51707 E8-K51707
 ICE900I 0 CON=0,MUV=1,VOL=1,ENU=0,SBK=0,SRC=0,VEM=0
 ICE055I 0 INSERT 0, DELETE 3184377
 ICE054I 0 RECORDS - IN: 37252557, OUT: 34068180
 ICE227I 0 SORTOF01 : DELETED = 0, REPORT = 0, DATA = 34068180
 ICE228I 0 SORTOF01 : TOTAL IN = 34068180, TOTAL OUT = 34068180
 ICE227I 0 SORTOF02 : DELETED = 0, REPORT = 0, DATA = 34068180
 ICE228I 0 SORTOF02 : TOTAL IN = 34068180, TOTAL OUT = 34068180
 ICE804I 5 OUTFIL   EXCP COUNT: 405587
 ICE891I 1 6284120 WMAIN, 7336 CMAIN, MAX CALLOC, N SCN, B BA, 0 AZ, 0 BZ, NN QC, 0 CZ, 0 DZ, 0 PLE
 ICE892I 1 1282 RIN  1282 BLI  0 BLO  1282 RUN  0 BUN  2097 CPU  00 CVC
 ICE052I 0 END OF DFSORT
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Jul 20, 2012 10:28 pm    Post subject:
Reply with quote

divya_maddi,

Here are some recommendations from our resident performance expert.

Your input is a KSDS with a CI size of 1536 which would mean there are 390 CIs per CA. Therefore, I would recommend to update the SORTIN01 and SORTIN02 as follows:

Code:

//SORTIN01 DD  DSN=VTEST.HISTFILE,DISP=SHR,AMP=('BUFND=391')
//SORTIN02 DD  DSN=VTEST.NEWFILE,DISP=SHR,AMP=('BUFND=391')
Back to top
View user's profile Send private message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Sat Jul 21, 2012 12:11 am    Post subject: Reply to: Sort Merge tune performance
Reply with quote

Still could not see an improvement icon_sad.gif

30.13 minutes with your recommendation
30.78 minutes with our production code
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Jul 21, 2012 12:21 am    Post subject:
Reply with quote

is that wall clock time?

what about actual cpu time and a few other run statistics?
start with the simple stuff,
like what is found in the first part of the jes display.

your insistence on wall clock time may not be valid........
on your machine, it may just take that long.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jul 21, 2012 12:32 am    Post subject:
Reply with quote

Hello,

How many other processes (online or batch) might be using these files at the same time this job is run?
Back to top
View user's profile Send private message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Sat Jul 21, 2012 12:47 am    Post subject:
Reply with quote

Yes that is wall clock time. CPU time is pretty low - under 3 mins.
No other process uses the file when we use it for this job.
Job runs at midnight and the file is closed down in the online region as well.

Before this we used REPRO for the VSAM copy and then a SORT for flat file copy. Now I have everything in one MERGE. Brought down the clock time from 2 hrs to 30 mins. But just wondering if there is more we can do.

Thanks!
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jul 21, 2012 7:49 am    Post subject:
Reply with quote

Hello,

If i understand your process, more than 100million 1.25k records are being read and written - most of the reads/writes are vsam.

30 minutes may not be so bad . . . Sounds like the process is running at or near the speed of the dasd.

Just thought.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sat Jul 21, 2012 1:33 pm    Post subject: Reply to: Sort Merge tune performance
Reply with quote

If you can show the JCL for the steps from the close of the online to your merge, there may be something. Perhaps to replace the REPRO with SORT, depending on exactly what it is doing.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Jul 22, 2012 1:42 am    Post subject:
Reply with quote

Hi Bill,

If i understand, the repro process took 2 hours.

One thought i had was to use sort to "unload" the vsam files, merge these sequential files, and thn copy the result file into a newly delete/defined vsam file.

Might be worth a timing test. . .
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sun Jul 22, 2012 4:39 am    Post subject: Reply to: Sort Merge tune performance
Reply with quote

Hi Dick,

I was thinking along those lines. Reviewing the topic, the REPRO has already gone :-)

divya_maddi,

In your first run one record was deleted, so moving the INCLUDE would not have helped, but in your second nearly 10% of the total. Will make some difference on a MERGE, but not huge.

The BUFND should be noticeable, even if not always in elapsed. You have one record per CI. Does the file get used much in batch? How (serial, random, mixture)?

How many levels of index are there?

50k vs 35m is a very small number of inserts. Because of the small CI it might be worth checking on a program to do keyed inserts.

A listcat after the online and after the sort would be interesting.
Back to top
View user's profile Send private message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Thu Jul 26, 2012 9:31 pm    Post subject:
Reply with quote

I am sorry for the delay in response.
Following is the definition of the VSAM we are using (for all of them).
We almost always use the VSAM only for inserts and never for reads.
Any help in tuning this would be appreciated.

Yes the move from REPRO to SORT Merge already happened.
We FTP the file with 50,000 records from unix and then sort it into VSAM.
We then sort merge this VSAM with 50,000 records to our history VSAM (and flat file) with 35 million records.

We are at liberty to change the definition of the VSAM if it is going to improve performance.

DEFINE CLUSTER( -
NAME(VSAM) -
FOR(0000) -
FREESPACE(0 0) -
BUFFERSPACE(24576) -
OWNER($IAM) -
INDEXED -
SPEED -
SHAREOPTIONS(2 3) -
KEYS(52 0)) -
DATA( -
NAME(VSAM.DATA) -
CYL(3000 3000) -
RECSZ(1282 1282)) -
INDEX( -
NAME(VSAM.INDEX) -
CYL(1 1))
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Thu Jul 26, 2012 9:36 pm    Post subject: Reply to: Sort Merge tune performance
Reply with quote

Quote:
BUFFERSPACE(24576) -
OWNER($IAM) -
INDEXED -


is it a VSAM ( managed by IBM VSAM )
or is it a IAM file
http://www.fdr.com/products/iam/iam01.cfm

???

tuning and friends might be quite different
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Jul 26, 2012 9:53 pm    Post subject:
Reply with quote

divya_maddi wrote:
I am sorry for the delay in response.
Following is the definition of the VSAM we are using (for all of them).
We almost always use the VSAM only for inserts and never for reads.
Any help in tuning this would be appreciated.

DEFINE CLUSTER( -
NAME(VSAM) -
FOR(0000) -
FREESPACE(0 0) -
BUFFERSPACE(24576) -
OWNER($IAM) -
INDEXED -
SPEED -
SHAREOPTIONS(2 3) -
KEYS(52 0)) -
DATA( -
NAME(VSAM.DATA) -
CYL(3000 3000) -
RECSZ(1282 1282)) -
INDEX( -
NAME(VSAM.INDEX) -
CYL(1 1))


divya_maddi,

Something doesn't add up here. In your earlier sysout you showed the vsam cluster has a CISIZE of 1582 and with the new definition the cluster will have a CISIZE of 8192. This will effect BFUND parameters. Is this input Clusters definition or output cluser defintion?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jul 26, 2012 10:07 pm    Post subject:
Reply with quote

Hello,

I'd run a timing test to see how this worked doing the merge with qsam files and then reloading the vsam.

Quote:
We almost always use the VSAM only for inserts and never for reads.
How often and in what ways is the history data used?

Does this all need to be in one huge file? Could there be separate files for each year and then when the data reached the purge criteria date (if there is one), simply delete the file for that year? IMHO, history data should not be "forever-to-date" online.
Back to top
View user's profile Send private message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Fri Jul 27, 2012 8:18 pm    Post subject:
Reply with quote

We hold history only for 120 days and it has to be in one file. It is a KSDS VSAM but out DASD Manager asked us to use IAM as the owner which helps in compression. I never worked with IAM files, so i do not know if one needs to call this an IAM file. It is compressed though - The sequential file occupies much more space than the VSAM even though both have the same data.

bufferspace is BUFFERSPACE(24576) for all our VSAM file definitions both input and output.
Back to top
View user's profile Send private message
divya_maddi

New User


Joined: 03 Nov 2005
Posts: 33

PostPosted: Fri Jul 27, 2012 8:19 pm    Post subject:
Reply with quote

I already tried QSAM followed by VSAM loading - takes longer due to the multiple writes may be?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us