IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Compare flat with vsam file using DFSORT


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Tue Nov 05, 2013 11:01 pm
Reply with quote

Hi,

I have a requirement, where I have to compare a flat file with a vsam file and write the matching record of flat file to another flat file.

In that one more condition for the matching record in vsam file I need to check one more condition IF flag <> 'N' then record should be extracted

File1 - Flat file
Code:

Custno         name
01~02~03   CUSTOMer1
01~02~04   Customer2
01~03~05   customer3


File2 - VSAM
Code:

Custno    Flag
010203     Y
010204     E
010305     N


File 1 custno is delimited with ~ symbol, need to compare with File2 custno
and flag <> N record should be extracted.

Output should be
Code:

01~02~03   CUSTOMer1
01~02~04   Customer2

3rd record skipped because it is having flag = N


Tried following code, don't know how to add the condition of vsam file, pls help

Code:

//STEP01 EXEC PGM=SORT                             
//SYSOUT DD SYSOUT=*                               
//SORTJNF1 DD DSN=FILE1,DISP=SHR                   
//SORTJNF2 DD DSN=VSAM1,DISP=SHR                   
//SORTOUT DD DSN=FILE2,DISP=OLD                     
//SYSIN DD *                                       
* CONTROL STATEMENTS FOR JOINKEYS APPLICATION       
  JOINKEYS FILE=F1,FIELDS=(1,2,A,4,2,A,7,2,A)       
  JOINKEYS FILE=F2,FIELDS=(1,6,A),TYPE=V           
  JOIN UNPAIRED,F1,ONLY                             
  REFORMAT FIELDS=(F1:1,1354)                       
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)
  SORT FIELDS=COPY                                 


thanks
Magesh
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Tue Nov 05, 2013 11:48 pm
Reply with quote

Assuming that the flag is indeed in position 12, would you not put
Code:
OMIT COND=(12,1,CH,EQ,C'N')

in JNF2CNTL?
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 12:55 am
Reply with quote

I got the below error when I try

Input record File1
C~S~NN~YYYY~ZZZZZ
where I need select 4 columns
(3,1 -5,2 -8,4 -13,5)

Because in input record file 2 has 12 columns that too it is a KSDS key

SNNYYYYZZZZZ =>


Code:

ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                 
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AN
ICE000I 1 - CONTROL STATEMENTS FOR
11:12 ON TUE NOV
          * CONTROL STATEMENTS FOR JOINKEYS APPLICATION                         
            JOINKEYS FILE=F1,FIELDS=(3,1,A,5,2,A,8,4,A,13,5,A)                 
            JOINKEYS FILE=F2,FIELDS=(1,12,A),TYPE=V                             
            JOIN UNPAIRED,F1,ONLY                                               
            REFORMAT FIELDS=(F1:1,1354)                                         
          * CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)                   
            SORT FIELDS=COPY                                                   
ICE405A 0 JOINKEYS STATEMENTS HAD MISMATCH IN NUMBER, LENGTH OR ORDER OF KEYS   
ICE751I 0 C5-K51706 C6-K51706 C7-K51706 E7-K51706                               
ICE052I 3 END OF DFSORT                                                         


Code:


//SYSIN DD *                                           
* CONTROL STATEMENTS FOR JOINKEYS APPLICATION           
  JOINKEYS FILE=F1,FIELDS=(3,1,A,5,2,A,8,4,A,13,5,A)   
  JOINKEYS FILE=F2,FIELDS=(1,12,A),TYPE=V               
  JOIN UNPAIRED,F1,ONLY                                 
  REFORMAT FIELDS=(F1:1,1354)                           
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)     
  SORT FIELDS=COPY                                     
//JNF2CNTL DD *                                         
 OMIT COND=(450,1,CH,EQ,C'N')                           
/* 


File 1 FB RECL 1354
File 2 KSDS RECL 450
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 1:06 am
Reply with quote

Also please advice on performance on reading the huge vsam file, The column in vsam file which I am trying compare is a KSDS KEY of RECL 12.

In fileaid we use "key next" command to search a key record, Please advice is there anything we have in sort, so that it can be searched fast.

Regards,
Magesh
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Nov 06, 2013 1:09 am
Reply with quote

magesh23586,

Stop Sending Private messages seeking help. If you posted the question in the forums, that is more than enough.

Did you even bother to look up the explanation of ICE405A which is quite self explanatory.

Code:
ICE405A 0 JOINKEYS STATEMENTS HAD MISMATCH IN NUMBER, LENGTH OR ORDER OF KEYS   


Read this which explains in detail about the error

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ice1cm60/2.2.275?
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 1:41 am
Reply with quote

Thanks, I got the error, But Please advice how to compare a delimited record with normal record.
i.e

C~S~NN~YYYY~ZZZZZ File 1

SNNYYYYZZZZZ File 2
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Nov 06, 2013 1:45 am
Reply with quote

Hello,

Remove the delimeters and compare like items - fields or individual characters. Whichever is appropriate.

Or insert delimiters in file2 nd compare . . .
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Wed Nov 06, 2013 1:51 am
Reply with quote

magesh23586 wrote:
Thanks, I got the error, But Please advice how to compare a delimited record with normal record.
i.e

C~S~NN~YYYY~ZZZZZ File 1

SNNYYYYZZZZZ File 2

Think about it, Magesh-kun. The key fields in file 1 are (3,1,5,2,8,4,13,5), yes? So assuming that the characters correspond between files 1 and 2, the join keys (which need not actually have anything to do with the VSAM key) are (1,1,2,2,4,4,8,5), yes? Same number of keys, each with the same length.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 1:58 am
Reply with quote

Wow, Its worked out... icon_smile.gif thanks Kolusu and dick scherrer.


Code:

//SYSIN DD *                                                 
* CONTROL STATEMENTS FOR JOINKEYS APPLICATION                 
  JOINKEYS FILE=F1,FIELDS=(3,1,A,5,2,A,8,4,A,13,5,A)         
  JOINKEYS FILE=F2,FIELDS=(1,1,A,2,2,A,4,4,A,8,5,A),TYPE=V   
  JOIN UNPAIRED,F1,ONLY                                       
  REFORMAT FIELDS=(F1:1,1354)                                 
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)           
  SORT FIELDS=COPY                                           
//JNF2CNTL DD *                                               
 OMIT COND=(450,1,CH,EQ,C'N')                                 
/*                                                           



But please advice on performance, Is there any way to provide or tell DFSort , "this a vsam key which I am trying to fetch". I mean like in File aid we use "Key next" to find a key record in vsam.

Regards,
Magesh
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 2:02 am
Reply with quote

Sorry Akatsukami, I missed your post.. thanks.. for the solution, I have tried the same its worked out.

But you mean to say this code is efficient enough...

or anything we can do to improve performance ?

Please advice
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Wed Nov 06, 2013 2:02 am
Reply with quote

Please explain why you find the performance unsatisfactory. Recall that not including the SORTED keyword on the JOINKEYS statements informs DFSORT that the records are not physically ordered by join key.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 2:09 am
Reply with quote

it took TOTAL TCB CPU TIME= .39 TOTAL ELAPSED TIME= .7 .

for finding 2473 records, I feel its bit slow and not sure honestly.

Quote:

Recall that not including the SORTED keyword on the JOINKEYS statements informs DFSORT that the records are not physically ordered by join key.

Sorry I couldn't understand

Please advice..

Regards,
Magesh
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Nov 06, 2013 2:27 am
Reply with quote

Hello,

Quote:
for finding 2473 records, I feel its bit slow and not sure honestly.
You feel this is slow compared to what?
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 2:35 am
Reply with quote

Compared to random read in Cobol...
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Wed Nov 06, 2013 2:47 am
Reply with quote

When the inclusion of this job causes the elapsed time of your nightly processing to threatened to overflow the batch window, and every less efficient job has been optimized, then it will be time to look at this one. Recall that half a day ago, it was not running at all, and therefore was taking an infinite amount of time to find and process those records.

The mindless drive for "performance" (probably driven by greedy thugs of bosses wanting every rupee that passes through their hands to stick there) is one of the most puzzling and unlovely things about outsourcing icon_mad.gif
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 3:04 am
Reply with quote

The requirement was cobol... I suggested dfsort to my boss and he told to client, dfsort is more efficient...:(clients are all 20 to 25 exp in mainframes...and they are all cobol fans...they can easily measure it and may challenge cobol is efficent
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Wed Nov 06, 2013 3:25 am
Reply with quote

Well, we're at an impasse here. If we worked in the same shop, I could knock out a program in an hour or so that, whilst not up to production standards, was good enough for "happy path" performance comparisons, but I don't have your data and you -- without insult -- don't have my ability and experience as a programmer.

If you're seriously sweating over performance, the only thing I can think of would be to do a straight unload of the VSAM data set, and then use that unload in the JOINKEYS step, but with less than 2,500 records, I think the overload of an extra step will swallow any performance gain in the second step. The DFSORT performance maven -- I forgot his name, he's only posted a couple-three times on this board -- might be called in, but it again wouldn't surprise me if he said that this was too small a process with which to bother.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Nov 06, 2013 3:34 am
Reply with quote

Hello,

Quote:
Compared to random read in Cobol...

I suspect the sort process will outperform the COBOL process. If you want to verify, make sure the comparison runs are high volume rather than low volume.

2400+ is Not very many records.

If it looks like the COBOL process performs as well, there should be an identifiable reason.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Nov 06, 2013 4:18 am
Reply with quote

Akatsukami wrote:
The DFSORT performance maven -- I forgot his name, he's only posted a couple-three times on this board -- might be called in, but it again wouldn't surprise me if he said that this was too small a process with which to bother.


Dave Betten is DFSORT performance expert, however there is not much he can do in this case as it is an issue of bad programming.

The COBOL program is reading a Keyed Sequential Data Set and the keys are already in the SORTED order, however the sample Joinkyes Job OP has shown is SORTING the ENTIRE KSDS file once again on the key which is a huge waste or resource and CPU time.

People demand fully optimized solutions and they don't seem to put in minimum effort to read upon the JOINKEYS documentation and on top if it they think that their time is much more valuable than our time.

magesh23586 wrote:
The requirement was cobol... I suggested dfsort to my boss and he told to client, dfsort is more efficient...:(clients are all 20 to 25 exp in mainframes...and they are all cobol fans...they can easily measure it and may challenge cobol is efficent


I applaud your audacity to challenge your seniors when you are still a newbie in DFSORT. *Sigh* Read up the documentation of Joinkeys and pay attention to the keyword SORTED and understand what it does.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 11:10 am
Reply with quote

Thanks to all...
Special thanks to Kolusu.

Added following things into the code, which has come up with significance performance improvement.
1. FILSZ specified
2. SORTED, NOSEQCK ==> I think sorted is sufficient, if we mention sorted alone, do the dfsort will perform a sequence check ?
3. AVGLEN specified.

Here is the updated code.
Code:

* CONTROL STATEMENTS FOR JOINKEYS APPLICATION             
  JOINKEYS FILE=F1,FIELDS=(3,1,A,5,2,A,8,4,A,13,5,A)     
  JOINKEYS FILE=F2,FIELDS=(1,1,A,2,2,A,4,4,A,8,5,A),SORTED,
           TYPE=V,NOSEQCK                                 
  JOIN UNPAIRED,F1,ONLY                                   
  REFORMAT FIELDS=(F1:1,1354)                             
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)       
  SORT FIELDS=COPY                                       
//JNF2CNTL DD *                                           
 OMIT COND=(450,1,CH,EQ,C'N')                             
 OPTION DYNALLOC(,16),FILSZ=E5000000,AVGRLEN=450         
/*                                                       


Regards,
Magesh
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 11:13 am
Reply with quote

And the CPU consumption i missed to tell.

After adding Sorted,NOSEQCK and FILSZ

TOTAL TCB CPU TIME= .28 TOTAL ELAPSED TIME= .6

previously it was

TOTAL TCB CPU TIME= .38 TOTAL ELAPSED TIME= .7
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Nov 06, 2013 1:19 pm
Reply with quote

magesh23586 wrote:

2. SORTED, NOSEQCK ==> I think sorted is sufficient, if we mention sorted alone, do the dfsort will perform a sequence check ?


DFSORT is not known for having statements merely for decorative purposes.

Your concern with performance will come down to the number of records on F1. If F1 is greater than 20% of the number of records on F2 I'd expect the JOINKEYS to be faster, generally. If F1 has one record, the process will be horribly slower.

The "tipping point" will be somewhere in between, and only you can identify that by running volume tests where the data is representative of what is expected in production.

DFSORT does no "keyed read" of a KSDS. With one record on F1, your entire KSDS will be read.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 1:52 pm
Reply with quote

Please correct me, if I have misunderstood
if f1 = 100 and F2 = 50 then performance is good
if f1= 50 and F2 =100 then performance will be bad ?

I tried the code as below, no difference, took same CPU.

Code:

//SYSOUT DD SYSOUT=*                                                 
//SORTJNF2 DD DSN=FILE1,DISP=SHR
//SORTJNF1 DD DSN=VSAM1,DISP=SHR               
//SORTOUT DD DSN=JOINKEY.OUT,DISP=OLD                       
//SYSIN DD *                                                         
* CONTROL STATEMENTS FOR JOINKEYS APPLICATION                       
  JOINKEYS FILE=F2,FIELDS=(3,1,A,5,2,A,8,4,A,13,5,A)                 
  JOINKEYS FILE=F1,FIELDS=(1,1,A,2,2,A,4,4,A,8,5,A),SORTED,         
           TYPE=V,NOSEQCK                                           
  JOIN UNPAIRED,F2,ONLY                                             
  REFORMAT FIELDS=(F2:1,1354)                                       
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)                 
  SORT FIELDS=COPY                                                   
//JNF1CNTL DD *                                                     
 OMIT COND=(450,1,CH,EQ,C'N')                                       
 OPTION DYNALLOC(,16),FILSZ=E5000000,AVGRLEN=450                     
/*                                                                   
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Nov 06, 2013 2:09 pm
Reply with quote

No. Hopefully with small numbers of records you won't notice anything anyway.

My comment about the JOINKEYS is not about the JOINKEYS itself, but about comparing a two-file-match generally to a keyed READ in COBOL.

For your solution the overhead of reading the entire KSDS searially has to be less than reading the KSDS from the entire key file.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Nov 06, 2013 3:25 pm
Reply with quote

A small changes to be noted, incase if any one refer this code in future.

Variable dataset should start with offset 5, which I missed.

Omit condition need to used with "NE" condition for this requirement.
Code:

//SYSIN DD *                                                 
* CONTROL STATEMENTS FOR JOINKEYS APPLICATION                 
  JOINKEYS FILE=F1,FIELDS=(3,1,A,5,2,A,8,4,A,13,5,A)         
  JOINKEYS FILE=F2,FIELDS=(5,1,A,6,2,A,8,4,A,12,5,A),SORTED,  ==>changed
           TYPE=V,NOSEQCK                                     
  JOIN UNPAIRED,F1,ONLY                                       
  REFORMAT FIELDS=(F1:1,1354)                                 
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)           
  SORT FIELDS=COPY                                           
//JNF2CNTL DD *                                               
 OMIT COND=(454,1,CH,NE,C'N')                        ==>changed         
 OPTION DYNALLOC(,16),FILSZ=E5000000,AVGRLEN=450             
/*                                                           
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Access to non cataloged VSAM file JCL & VSAM 18
Search our Forums:

Back to Top