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
 

 

Compare flat with vsam file using DFSORT
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
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 158
Location: Chennai

PostPosted: Tue Nov 05, 2013 11:01 pm    Post subject: Compare flat with vsam file using DFSORT
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: 1738
Location: Bloomington, IL

PostPosted: Tue Nov 05, 2013 11:48 pm    Post subject:
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: 158
Location: Chennai

PostPosted: Wed Nov 06, 2013 12:55 am    Post subject: Reply to: Compare flat with vsam file using DFSORT
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: 158
Location: Chennai

PostPosted: Wed Nov 06, 2013 1:06 am    Post subject: Reply to: Compare flat with vsam file using DFSORT
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    Post subject:
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

http://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: 158
Location: Chennai

PostPosted: Wed Nov 06, 2013 1:41 am    Post subject: Reply to: Compare flat with vsam file using DFSORT
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

Site Director


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

PostPosted: Wed Nov 06, 2013 1:45 am    Post subject:
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: 1738
Location: Bloomington, IL

PostPosted: Wed Nov 06, 2013 1:51 am    Post subject: Re: Reply to: Compare flat with vsam file using DFSORT
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: 158
Location: Chennai

PostPosted: Wed Nov 06, 2013 1:58 am    Post subject: Reply to: Compare flat with vsam file using DFSORT
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: 158
Location: Chennai

PostPosted: Wed Nov 06, 2013 2:02 am    Post subject: Reply to: Compare flat with vsam file using DFSORT
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: 1738
Location: Bloomington, IL

PostPosted: Wed Nov 06, 2013 2:02 am    Post subject:
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: 158
Location: Chennai

PostPosted: Wed Nov 06, 2013 2:09 am    Post subject: Reply to: Compare flat with vsam file using DFSORT
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

Site Director


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

PostPosted: Wed Nov 06, 2013 2:27 am    Post subject:
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: 158
Location: Chennai

PostPosted: Wed Nov 06, 2013 2:35 am    Post subject: Reply to: Compare flat with vsam file using DFSORT
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: 1738
Location: Bloomington, IL

PostPosted: Wed Nov 06, 2013 2:47 am    Post subject:
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: 158
Location: Chennai

PostPosted: Wed Nov 06, 2013 3:04 am    Post subject: Reply to: Compare flat with vsam file using DFSORT
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: 1738
Location: Bloomington, IL

PostPosted: Wed Nov 06, 2013 3:25 am    Post subject:
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

Site Director


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

PostPosted: Wed Nov 06, 2013 3:34 am    Post subject:
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    Post subject:
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: 158
Location: Chennai

PostPosted: Wed Nov 06, 2013 11:10 am    Post subject: Reply to: Compare flat with vsam file using DFSORT
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
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 Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 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 CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


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