Joined: 03 Jan 2003 Posts: 283 Topics: 27 Location: US
Posted: Tue Mar 01, 2005 4:29 pm Post subject: Reporting lateral file merge
I need to do a lateral merge of 2 files which have the same layout.
The structure is
For File1:
-----------
1. Date - ccyymmdd
2. Record key
3. Old amt - 9(10)v9(8)
For File2:
-----------
1. Date - same format
2. Record key
3. New amt - 9(10)v9(8)
The O/P file should be in a report format such as
REPORT HEADING DATE:----
PAGE:----
DATE FROM FILE2 SHOULD COME HERE as a heading
OLDAMT NEWAMT DIFFERENCE %VARIANCE
I'm able to do this in SORT(with splice) but I was not sure what would the SECTIONS i should be using for getting a page break. Also, i got confused with the reporting parms. So, please help with the sort job. Also, Please let me know if there is any way in EASYTRIEVE as well..
________
vaporizer volcano
Last edited by coolman on Sat Feb 05, 2011 1:39 am; edited 1 time in total
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Tue Mar 01, 2005 8:09 pm Post subject:
Quote:
I'm able to do this in SORT(with splice) but I was not sure what would the SECTIONS i should be using for getting a page break.
Well, that would depend on what you want to break on in the report. Your description of the input and output is rather sketchy. Please provide more details. Show an example of the input records with actual values and what you want the output report to look like (for more than one page). Indicate the starting position, length and format of the key, and the length of the various fields for input and output.
Show your "SORT(with splice)" job so we know what you've done so far. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Both IN1 and IN2 have the same layouts. The LRECL is 150 and RECFM is FB.
The key begins at 12th postion and is of length 11. File1 contains an old amt which is at postion 113 and is of length 18. File2 contains an new amt which is at posn 56 and is of length 18. The objective is to have report which would be like as shown under
I have written an EZT job for the same and have generated this report yesterday. But, I have one small issue there. How do we have an edit mask that accomodates a negative sign. In sort, we have EDIT=(ZZZZ),SIGNS=(+,-) which would accomodate the sign. How could this be accomplished in EZT.
As always, thanks Frank for your prompt response.
________
Ipad guide
Last edited by coolman on Sat Mar 12, 2011 8:35 pm; edited 1 time in total
Joined: 03 Jan 2003 Posts: 283 Topics: 27 Location: US
Posted: Wed Mar 02, 2005 10:15 am Post subject:
oops...missed out on the SECTIONS. I know that for a SECTIONS statement be based on the control break in the report. But, in this case, I really don't want to do anything like control summary addition. All I want is after every page, the header needs to be repeated and the detail records follow suit.
________
T500
Last edited by coolman on Sat Feb 05, 2011 1:40 am; edited 1 time in total
Since the ZD fields are 18 bytes, we need the new larger number support in z/OS DFSORT V1R5 PTF UQ95214 or DFSORT R14 PTF UQ95213 (Dec, 2004). Without the Dec, 2004 PTF, DFSORT only supported up to 15-digit ZD values for certain functions such as arithmetic expressions. With the Dec, 2004 PTF, DFSORT supports up to 31-digit ZD values for all functions. (Note that the job below won't work with Syncsort because Syncsort does NOT have the support for larger numbers that DFSORT has).
That said, here's the DFSORT/ICETOOL job to do what you want. Note that since you only want page headings, we can use HEADER2. We don't need SECTIONS since you're not using section breaks.
Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file1
//IN2 DD DSN=... input file2
//T1 DD DSN=&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),
//*** USE MOD FOR T1
// DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
* IN1->T1: Reformat for SPLICE
COPY FROM(IN1) TO(T1) USING(CTL1)
* IN2->T1: Remove dups and reformat for SPLICE
SELECT FROM(IN2) TO(T1) ON(12,11,CH) FIRST USING(CTL2)
* T1->OUT: Splice records to get:
* |date.|key|prev.|current|
* Produce the report from these values.
SPLICE FROM(T1) TO(OUT) ON(11,11,CH) -
WITH(1,8) WITH(51,18) USING(CTL3)
/*
//CTL1CNTL DD *
* |blank|key|prev.|blank..|
OUTREC FIELDS=(11:12,11,31:113,18,51:18X)
/*
//CTL2CNTL DD *
* |date.|key|blank|current|
OUTFIL FNAMES=T1,OUTREC=(1:1,8,11:12,11,51:56,18)
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,REMOVECC,
HEADER2=(/,X,/,DATE=(MD4/),28:'DISCREPANCY REPORT #1 -',
5X,1,8,/,28:36C'-',/,X,/,
'PREVIOUS BALANCE',28:'CURRENT BALANCE',
55:'DIFFERENCE',82:'%DIFF',/,
22C'-',28:22C'-',55:22C'-',82:5C'-'),
OUTREC=(31,18,ZD,EDIT=(I,III,III,IIT.TTTTTTTT),
28:51,18,ZD,EDIT=(I,III,III,IIT.TTTTTTTT),
55:31,18,ZD,SUB,51,18,ZD,
EDIT=(I,III,III,IIT.TTTTTTTT),
82:((31,18,ZD,SUB,51,18,ZD),MUL,+1000),DIV,31,18,ZD,
EDIT=(IIT.T))
/*
Wow, wouldn't we all like to have that first balance.
Note: I had to stretch my browser window as wide as it would go to to get the report to display correctly. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Last edited by Frank Yaeger on Thu Mar 03, 2005 5:37 pm; edited 1 time in total
Joined: 03 Jan 2003 Posts: 283 Topics: 27 Location: US
Posted: Thu Mar 03, 2005 4:55 pm Post subject:
Quick Q Frank - Would this job split up into multiple pages if there are 1000+ records in the i/p. The reason for my asking is that it does not have SECTIONS which is where I faced issues.
________
Toyota Corolla E140 history
Last edited by coolman on Sat Feb 05, 2011 1:40 am; edited 1 time in total
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Thu Mar 03, 2005 5:35 pm Post subject:
Yes, it would. Page breaks are controlled by LINES=n (DFSORT's default is LINES=60).
You're confusing page breaks and section breaks. LINES=n controls the number of lines per page. When you get n lines on a page, DFSORT will start a new page.
HEADER2 sets up the page headers and TRAILER2 sets up page trailers. Whenever DFSORT starts a new page (after n lines), the HEADER2 lines are printed at the top of the page. The TRAILER2 lines are printed at the bottom of the page before we start a new page.
SECTIONS sets up a section break whenever the key (p,m) changes. SKIP=P can be used to start a new page for each section. SKIP=nL can be used to insert n lines between sections on the same page. HEADER3 and TRAILER3 set up section headers and section trailers. You don't need to use SECTIONS to set up page breaks - LINES=n will set up the page breaks. But you can use SECTIONS and SKIP=P to set up section breaks and start each section on a new page if you want to. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
