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.
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.
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
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
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:
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.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
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.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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.
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.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
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.
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?
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
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.
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.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
If there are 50,000 new adds per day and the file keeps data for 120 days, how are there 35million records? At 50,000 a day, this would be about 1.5 million per month which would be less than 7million for 120 days would it not?
Yes, IAM is not the same as VSAM - program code will work as though it is VSAM, but the underlying mechanics are quite different.
I still don't understand why this needs to be one huge file. As requested earlier, please post how/when this history data is used.
Also when each monthly is run, how are the "old" records removed from the file. Hopefully, they are not just "marked" as deleted and are actually removed.
50,000 a day is an average (more recent average) - it might go higher than that. But we currently have the total at 35 million for 120 days. Old records are removed each day with the sort I already gave in the beginning. Mentioning again for you reference:
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Quote:
But we currently have the total at 35 million for 120 days.
How? This is almost 6 times the expected volume . . . Adctually, this would average 291666.6666666667 per day for 120 days. . .
Are you sure there is an understanding as to just what is in this file?
Does any of this relate to the newly acquired company?
I believe you are getting as good performance as you can given the process is running the way it is. Until we (at least i) understand this a bit better, it may be tough to offer other suggestions.
