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
 

 

Two files to merge and to update trailer count
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Wed Jun 29, 2011 2:18 pm    Post subject: Two files to merge and to update trailer count
Reply with quote

Hi

I have a requirement like there are two files with same layout.
Each file is having header and trailer and trailer contains record count of the detail records. the requirement is i have to merge those two files and the output should contain only one header and one trailer and the trailer count should be updated(count field is 9 length is 9 bytes). pls Kindly help..

the input file layouts are as follows

Input File 1:

0000000000ROVISCU 20110306
VISRO-10000003 * TEST4900001
VISRO-10000007 * TEST5300002
9999999999000000002

Input File 2:

0000000000ROVISCU 20110306
VISRO-10017720 * TEST9600001
VISRO-10019952 * TEST8100002
VISRO-10021884 * TEST0200001
VISRO-10022244 * TEST9400057
9999999999000000004

Required output file is,

Output file:

0000000000ROVISCU 20110306
VISRO-10000003 * TEST4900001
VISRO-10000007 * TEST5300002
VISRO-10017720 * TEST9600001
VISRO-10019952 * TEST8100002
VISRO-10021884 * TEST0200001
VISRO-10022244 * TEST9400057
9999999999000000006


could u pls help..
Back to top
View user's profile Send private message

sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Wed Jun 29, 2011 2:35 pm    Post subject: Reply to: Two files to merge and to update trailer count
Reply with quote

Hi,

Sorry i missed to mention file LRECL.

The files LRECL used is 1027.

record id for header is '0000000000' (10 bytes)
record id for trailer is '9999999999' (10bytes)

trailer count field length is 9 bytes.

Please help..
Back to top
View user's profile Send private message
prasanth_thavva

New User


Joined: 28 Jul 2005
Posts: 86
Location: Chennai

PostPosted: Wed Jun 29, 2011 3:15 pm    Post subject: Reply to: Two files to merge and to update trailer count
Reply with quote

Just try below control card

//SYSIN DD *

SORT FIELDS=(1,5,CH,A)
INCLUDE COND=(1,5,CH,EQ,C'VISRO')
OUTFIL FILES=01,

HEADER1=(1:C'XXXX'),

TRAILER1=(1:C'YYYY',COUNT=(M10,LENGTH=9))
/*

XXXX , YYY in header and trailer has to be change accordingly
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Wed Jun 29, 2011 3:52 pm    Post subject:
Reply with quote

Hi,

The input files are already having header and trailer. I just need to convey it from input files to output file by SORT/MERGE with no duplicates. and the trailer count should be updated which is there in triler record...

pls help...
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Jun 29, 2011 7:52 pm    Post subject:
Reply with quote

sunil.maaraka,
Quote:
I just need to convey it from input files to output file by SORT/MERGE with no duplicates.

What is the criteria to remove duplicates? Did you mean remove duplicate "detail" records or just HEADER and TRAILER records? If detail records,please explain how details are identified as duplicates?

Thanks,
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Wed Jun 29, 2011 8:01 pm    Post subject:
Reply with quote

Hi,

Both input files are having header and trailer. if i merge those two files i will be getting 2 headers and 2 trailer records in output. so dont want 2 headers and 2 trailers to be present in output dataset. so in output only 1 header and 1 trailer is accepted. and i would like to add the first file count which is there in first file trailer record with count in second file trailer record.

pls help...
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Jun 29, 2011 8:53 pm    Post subject:
Reply with quote

sunil.maaraka,
See if below helps...TESTED ON DFSORT...If header has current date, then you can replace HEADER1 like below..
Code:
HEADER1=(C'0000000000ROVISCU ',DATENS=(4MD)),

Code:
//STEP0001  EXEC PGM=SORT                         
//SYSOUT    DD SYSOUT=*                           
//SYSPRINT  DD SYSOUT=*                           
//SYSUDUMP  DD SYSOUT=*                           
//SORTIN    DD *                                 
0000000000ROVISCU 20110306                       
VISRO-10000003 * TEST4900001                     
VISRO-10000007 * TEST5300002                     
9999999999000000002                               
0000000000ROVISCU 20110306                       
VISRO-10017720 * TEST9600001                     
VISRO-10019952 * TEST8100002                     
VISRO-10021884 * TEST0200001                     
VISRO-10022244 * TEST9400057                     
9999999999000000004                               
//SORTOUT  DD  SYSOUT=*                           
//SYSIN     DD *                                 
  OMIT COND=(01,10,CH,EQ,C'0000000000',OR,       
             01,10,CH,EQ,C'9999999999')           
  SORT FIELDS=COPY,EQUALS                         
  OUTFIL REMOVECC,BUILD=(1,1027),                 
   HEADER1=(C'0000000000ROVISCU 20110306'),       
  TRAILER1=(C'9999999999',COUNT=(M11,LENGTH=9))   
/*                                               

OUTPUT
Code:
0000000000ROVISCU 20110306 
VISRO-10000003 * TEST4900001
VISRO-10000007 * TEST5300002
VISRO-10017720 * TEST9600001
VISRO-10019952 * TEST8100002
VISRO-10021884 * TEST0200001
VISRO-10022244 * TEST9400057
9999999999000000006         

Thanks,
Back to top
View user's profile Send private message
prasanth_thavva

New User


Joined: 28 Jul 2005
Posts: 86
Location: Chennai

PostPosted: Thu Jun 30, 2011 8:11 am    Post subject: Reply to: Two files to merge and to update trailer count
Reply with quote

Sql code1 code one way of acheiving result in my control card based on data record in your infile i have included only records which starts with "VISRO" , so you will be getting count as many data records available in input file.

thanks sqlcode1 for getting us the exact control card. which will taken care of Lrecl (BUILD) and some additional parameters like REMOVECC...etc.

icon_biggrin.gif
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Thu Jun 30, 2011 11:54 am    Post subject: Reply to: Two files to merge and to update trailer count
Reply with quote

Hi,

I ran the sort step using the above sort card you suggested. its working fine.

But actually one requirement is missing. The header date is not TSO/ISPF current date, actually As per our process while generating the header record the business date is used to populate in a daily running program.

So, could you pls help instead of hard coding or populating with TSO/ISPF current date, is there any way to get date from omitted header record and populate it to output header.

Please help...
Back to top
View user's profile Send private message
prasanth_thavva

New User


Joined: 28 Jul 2005
Posts: 86
Location: Chennai

PostPosted: Thu Jun 30, 2011 12:15 pm    Post subject: Reply to: Two files to merge and to update trailer count
Reply with quote

Hi,

If you look at sqlcode1 code , you will be observing in trailer statement

"//SYSIN DD *
OMIT COND=(01,10,CH,EQ,C'0000000000',OR,
01,10,CH,EQ,C'9999999999')
SORT FIELDS=COPY,EQUALS
OUTFIL REMOVECC,BUILD=(1,1027),
HEADER1=(C'0000000000ROVISCU 20110306'),
TRAILER1=(C'9999999999',COUNT=(M11,LENGTH=9))
/* "


Like wise ("Underlined") , Can you try in header by adding date format , google it for syncsort.

&DATE4...etc
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Thu Jun 30, 2011 12:23 pm    Post subject:
Reply with quote

Hi,

you can try this
Code:
  OMIT COND=(01,10,CH,EQ,C'9999999999')                                 
  INREC BUILD=(1,1027,SEQNUM,10,ZD)                                     
  SORT FIELDS=COPY,EQUALS                                               
  OUTFIL REMOVECC,BUILD=(1,1027),OMIT=(1028,10,ZD,GT,+1,AND,           
         1,10,CH,EQ,C'0000000000'),                                     
  TRAILER1=(C'9999999999',COUNT=(M11,LENGTH=9))                         



Gerry
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2159
Location: @my desk

PostPosted: Thu Jun 30, 2011 12:42 pm    Post subject:
Reply with quote

Hello Gerry,

Since we are just COPYing, do we need an EQUALS here?
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Thu Jun 30, 2011 12:50 pm    Post subject: Reply to: Two files to merge and to update trailer count
Reply with quote

Hi Jerry,


I ran it, Its working fine, its populating date from previously omitted header.

But the count value is wrongly as the count is added for header also.

for example.

OUTPUT

0000000000ROVISCU 20110306
VISRO-10000003 * TEST4900001
VISRO-10000007 * TEST5300002
VISRO-10017720 * TEST9600001
VISRO-10019952 * TEST8100002
VISRO-10021884 * TEST0200001
VISRO-10022244 * TEST9400057
9999999999000000007


The though the detail records 6 only it is showing 7 as count in the output.

how can i less one from count in output to show.

could you help pls...
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Thu Jun 30, 2011 1:01 pm    Post subject:
Reply with quote

Hi Arun,

I just used sqlcode1's JCL and amended it, but you are right, there is no need to use EQUALS


Gerry
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Thu Jun 30, 2011 1:16 pm    Post subject: Reply to: Two files to merge and to update trailer count
Reply with quote

Hi All,

Its working as required icon_biggrin.gif. I really very happy to see this much sincere help...

Its very happy to do conversation with you all..


Thank you once again...!


have a nice day... icon_smile.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2159
Location: @my desk

PostPosted: Thu Jun 30, 2011 1:45 pm    Post subject:
Reply with quote

Good that it's working fine, Can you post the final solution here?
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Thu Jun 30, 2011 2:41 pm    Post subject: Reply to: Two files to merge and to update trailer count
Reply with quote

Hi Jerry,


Could you please give an idea about how that omit condition works in below.

OUTFIL REMOVECC,BUILD=(1,1030),OMIT=(1028,10,ZD,GT,+1,AND,
1,10,CH,EQ,C'0000000000'),


Please help...
Back to top
View user's profile Send private message
sunil.maaraka

New User


Joined: 20 Apr 2011
Posts: 12
Location: India

PostPosted: Thu Jun 30, 2011 2:42 pm    Post subject: Reply to: Two files to merge and to update trailer count
Reply with quote

sorry,

OUTFIL REMOVECC,BUILD=(1,1027),OMIT=(1028,10,ZD,GT,+1,AND, 1,10,CH,EQ,C'0000000000'),

Pls...
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Jun 30, 2011 7:15 pm    Post subject: Reply to: Two files to merge and to update trailer count
Reply with quote

here is another way of doing it ( TESTED For 80 byte records )

Code:
 000027   OMIT   COND=(01,10,CH,EQ,C'9999999999')                               
 000028   SORT   FIELDS=(81,1,CH,A,1,14,CH,A)                           
 000029   INREC  IFTHEN=(WHEN=GROUP,BEGIN(1,10,CH,EQ,C'0000000000'),           
 000030                  PUSH=(82:ID=8)),                                       
 000031          IFTHEN=(WHEN=(1,10,CH,EQ,C'0000000000'),                       
 000032                  OVERLAY=(81:C'1')),                                   
 000033          IFTHEN=(WHEN=NONE,                                             
 000034                  OVERLAY=(81:C'2'))                                     
 000035   OUTFIL REMOVECC,BUILD=(1,80),                                         
 000036          OMIT=(01,10,CH,EQ,C'0000000000',AND,82,8,ZD,GT,1),             
 000037          TRAILER1=('9999999999',COUNT-1=(M11,LENGTH=9))                 


input
Code:
 000011 0000000000ROVISCU 20110306                                             
 000012 VISRO-20000003 * TEST4900001                                           
 000013 VISRO-20000007 * TEST5300002                                           
 000014 9999999999000000002                                                     
 000015 0000000000ROVISCU 20110306                                             
 000016 VISRO-10000003 * TEST4900001                                           
 000017 VISRO-10000007 * TEST5300002                                           
 000018 9999999999000000002                                                     
 000019 0000000000ROVISCU 20110306                                             
 000020 VISRO-10017720 * TEST9600001                                           
 000021 VISRO-10019952 * TEST8100002                                           
 000022 VISRO-10021884 * TEST0200001                                           
 000023 VISRO-10022244 * TEST9400057                                           
 000024 9999999999000000004                                                     

output

Code:
0000000000ROVISCU 20110306                                                     
VISRO-10000003 * TEST4900001                                                   
VISRO-10000007 * TEST5300002                                                   
VISRO-10017720 * TEST9600001                                                   
VISRO-10019952 * TEST8100002                                                   
VISRO-10021884 * TEST0200001                                                   
VISRO-10022244 * TEST9400057                                                   
VISRO-20000003 * TEST4900001                                                   
VISRO-20000007 * TEST5300002                                                   
9999999999000000008                                                             
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Thu Jun 30, 2011 7:19 pm    Post subject:
Reply with quote

Quote:
Hello Gerry,

Since we are just COPYing, do we need an EQUALS here?

Quote:
Hi Arun,
I just used sqlcode1's JCL and amended it, but you are right, there is no need to use EQUALS

Arun,
That was left over from my testing where I was initially sorting the records. Thanks for the correction.

Thanks,
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 -> JCL & VSAM 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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm


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