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
 

 

Merge the 2 files File1 & File 2 and create File3
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
kingofmainframes
Warnings : 2

New User


Joined: 09 Apr 2006
Posts: 20
Location: Chennai

PostPosted: Sun Feb 04, 2007 8:02 pm    Post subject: Merge the 2 files File1 & File 2 and create File3
Reply with quote

Hi Guys,

I need your sugession and help for the below. Thanks in advance.

File struture
1. 1st 4 bytes is key field
2. 5th byte is indicator field. possible values are X, N & Y

Requirement
I have to merge the 2 files File1 & File 2 and create File3. File3 should have all records from both File1 and File2 except the first record with key value '0000'. A new record with key value '0000' has to be inserted into File3 that should have the total no of record in the File3. Also this record should have the no records with indicator X, N & Y in the file. Please note File1 & File2 may contain 400000 records. The part where I need assistance is to build the first record in File3. :-)

File 1
0000
0001X
0002X
0004N
0005Y
0006Y
0007Y

File 2
0000
0008X
0009Y

File 3
0000 TOTAL=8 X=3 N=1 Y=4
0001X
0002X
0004N
0005Y
0006Y
0007Y
0008X
0009Y
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Sun Feb 04, 2007 8:15 pm    Post subject:
Reply with quote

I think you can create a trailer record with the totals, but you would need to either re-sort or make another pass to get it back to the front. The Smart DFSORT Tricks has an example titled "Display the number of input or output records" which could give you some pointers.
Back to top
View user's profile Send private message
kingofmainframes
Warnings : 2

New User


Joined: 09 Apr 2006
Posts: 20
Location: Chennai

PostPosted: Sun Feb 04, 2007 8:44 pm    Post subject:
Reply with quote

Hi William,
Thanks. It is ok to have either header or trailer record. Please suggest how to get the record in the below format. eg:- Count of records with indicator X.
File3
9999 TOTAL=8 X=3 N=1 Y=4
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Sun Feb 04, 2007 8:56 pm    Post subject:
Reply with quote

Did you look at the example in Smart DFSORT Tricks ?
Back to top
View user's profile Send private message
kingofmainframes
Warnings : 2

New User


Joined: 09 Apr 2006
Posts: 20
Location: Chennai

PostPosted: Sun Feb 04, 2007 9:27 pm    Post subject:
Reply with quote

Yes William I did. So I can get the total no of records in the file using COUNT. But how do I get the count of the records with indicator X , Y, N.

I don't understand the second example in the DFSORT tricks.

//SYSIN DD
OPTION COPY
OMIT COND=(1,4,EQ,C'0000')
OUTFIL NODETAIL,REMOVECC,
TRAILER1=('9999 TOTAL = ',COUNT=(M11,LENGTH=8)
/*
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Sun Feb 04, 2007 9:41 pm    Post subject:
Reply with quote

I'd guess that like the IFTHEN for the 'SUI', for each type of record you want to sum, you OVERLAY a unique counter with the 001. With 3 unique keys, that would be 3 counters and 3 TOTs.

Is that better?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sun Feb 04, 2007 10:22 pm    Post subject:
Reply with quote

kom,

Here's a DFSORT job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
0000
0001X
0002X
0004N
0005Y
0006Y
0007Y
/*
//    DD *
0000
0008X
0009Y
/*
//SORTOUT DD SYSOUT=*
//SYSIN    DD    *
  OPTION COPY
  OMIT COND=(1,4,CH,EQ,C'0000')
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:C'000',84:C'000',87:C'000')),
    IFTHEN=(WHEN=(5,1,CH,EQ,C'X'),OVERLAY=(81:C'001')),
    IFTHEN=(WHEN=(5,1,CH,EQ,C'N'),OVERLAY=(84:C'001')),
    IFTHEN=(WHEN=(5,1,CH,EQ,C'Y'),OVERLAY=(87:C'001'))
  OUTFIL REMOVECC,
    BUILD=(1,80),
    TRAILER1=('0000 TOTAL=',COUNT=(M11,LENGTH=3),
     ' X=',TOT=(81,3,ZD,M11,LENGTH=3),
     ' N=',TOT=(84,3,ZD,M11,LENGTH=3),
     ' Y=',TOT=(87,3,ZD,M11,LENGTH=3))
/*


SORTOUT would have:

Code:

0001X                                             
0002X                                             
0004N                                             
0005Y                                             
0006Y                                             
0007Y                                             
0008X                                             
0009Y                                             
0000 TOTAL=008 X=003 N=001 Y=004                 


You can adjust the layout of the trailer record as needed.
Back to top
View user's profile Send private message
kingofmainframes
Warnings : 2

New User


Joined: 09 Apr 2006
Posts: 20
Location: Chennai

PostPosted: Sun Feb 04, 2007 10:43 pm    Post subject:
Reply with quote

Hi Yager, Thanks a lot for your effort and code. I think I can go on and retrofit this for my needs.

Hi William, Thanks for you too.
Back to top
View user's profile Send private message
kingofmainframes
Warnings : 2

New User


Joined: 09 Apr 2006
Posts: 20
Location: Chennai

PostPosted: Tue Feb 06, 2007 10:41 pm    Post subject:
Reply with quote

Hi Guys,

The input files File1 & File2 are VSAM KSDS files. When I concatenate these two datasets in SORTIN I am getting VSAM open error (168). Please suggest how to solve this error. File3 is also VSAM KSDS file.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Feb 06, 2007 11:57 pm    Post subject:
Reply with quote

Gee, that's kind of an important fact that you left out. icon_rolleyes.gif

The system does not allow concatenation of VSAM files. KSDSs have a key of their own and can be treated as either fixed length or variable length depending on what the data looks like. Not knowing anything about your KSDSs, I can't really tell you how to change the job to do what you want to do. I'd need to know if the records were fixed length or variable length, how long they are, where the key is, etc.
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 Feb 07, 2007 12:18 am    Post subject:
Reply with quote

Hello,

If the files are not huge, you could repro them into qsam files and use the DFSORT job provided by Frank.

You could repro them also if they WERE huge, but the storage management people may come looking for you icon_smile.gif
Back to top
View user's profile Send private message
kingofmainframes
Warnings : 2

New User


Joined: 09 Apr 2006
Posts: 20
Location: Chennai

PostPosted: Wed Feb 07, 2007 9:28 pm    Post subject:
Reply with quote

Hi Frank,

Sorry for missing that vital info. Detailed info follows. Please let me know anything is missing.

There are actually 10 files (file0 - file9) each with 40,000 records. All files are VSAM KSDS, Key - 10 bytes ZD field, Record are fixed length, Record length = 3000 Bytes.

11th byte is indicator field. possible values are I, N & Y

Key range for file0 is 0000000001 - 0000040000
Key range for file1 is 0000040001 - 0000080000
Key range for file2 is 0000080001 - 0000120000
Key range for file3 is 0000120001 - 0000160000
Key range for file4 is 0000160001 - 0000200000
Key range for file5 is 0000200001 - 0000240000
Key range for file6 is 0000240001 - 0000280000
Key range for file7 is 0000280001 - 0000320000
Key range for file8 is 0000320001 - 0000360000
Key range for file9 is 0000360001 - 0000400000

Unlike the previous expamle I gave the key value for the header record in each file ( File0 - File9) is lowvalues.

The header record in files ( file0 - file 9) should not appear in the merged file.
The trailer record should have a key value of high values.

Trailer record structure is
" KEY IS HIGHVALUES" TOTAL=0000400000 I=0000200000 Y=0000100000 N=0000100000

Hi DS,

Files are huge I guess icon_biggrin.gif
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 Feb 07, 2007 9:54 pm    Post subject:
Reply with quote

Hi K-O-M,

I guess huge is in the eye of the beholder. My medium size files are 1-3 million records that are 19,470 bytes long. A couple of our "bigger" files are a bit shorter in length but contain over 100 million records. We do have to do some juggling to get around space issues. When the smoke clears, this will be a 10-12 tera-byte warehouse. . . . icon_smile.gif

My guess is that your system could handle the temporary space as you'd give it back as soon as the process was completed.

Hopefully, you will able to work with the VSAM directly icon_smile.gif
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Feb 07, 2007 10:01 pm    Post subject:
Reply with quote

I think this DFSORT job may do what you asked for, but I didn't actually test it with VSAM KSDSs. I assumed that by low values you meant binary zeros and by high values you meant binary ones.

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN01 DD DSN=...  VSAM input file1
//SORTIN02 DD DSN=...  VSAM input file2
...
//SORTIN10 DD DSN=...  VSAM input file10
//SORTOUT DD DSN=...  VSAM output file
//SYSIN    DD    *
  RECORD TYPE=F
  OMIT COND=(1,10,BI,EQ,X'00000000000000000000')
  INREC IFTHEN=(WHEN=INIT,
     OVERLAY=(3001:+0,M11,LENGTH=10,
              3011:+0,M11,LENGTH=10,
              3021:+0,M11,LENGTH=10)),
    IFTHEN=(WHEN=(11,1,CH,EQ,C'I'),
      OVERLAY=(3001:+1,M11,LENGTH=10)),
    IFTHEN=(WHEN=(11,1,CH,EQ,C'N'),
      OVERLAY=(3011:+1,M11,LENGTH=10)),
    IFTHEN=(WHEN=(11,1,CH,EQ,C'Y'),
      OVERLAY=(3021:+1,M11,LENGTH=10))
  MERGE FIELDS=(1,10,BI,A)
  OUTFIL REMOVECC,
    BUILD=(1,3000),
    TRAILER1=(X'FFFFFFFFFFFFFFFFFFFF',' TOTAL=',
     COUNT=(M11,LENGTH=10),
     ' I=',TOT=(3001,10,ZD,M11,LENGTH=10),
     ' N=',TOT=(3011,10,ZD,M11,LENGTH=10),
     ' Y=',TOT=(3021,10,ZD,M11,LENGTH=10))
/*
Back to top
View user's profile Send private message
kingofmainframes
Warnings : 2

New User


Joined: 09 Apr 2006
Posts: 20
Location: Chennai

PostPosted: Wed Feb 07, 2007 10:14 pm    Post subject:
Reply with quote

Hi DS,

Its very interesting to read your reply. Do you process these files with record length 19470 in CICS?
We do have larger volume files like yours but we don't normally process them in CICS.
I work in a environment where we communicate with all types third party system using a lot of different communication protocols. For my system response time is very critical and so my general objective is to keep the file size as small as possible.

In this design I trying my best to reduce the down time as all these files has to made available to CICS. Preferance is not to scan these files multiple times. icon_biggrin.gif
Back to top
View user's profile Send private message
kingofmainframes
Warnings : 2

New User


Joined: 09 Apr 2006
Posts: 20
Location: Chennai

PostPosted: Wed Feb 07, 2007 10:21 pm    Post subject:
Reply with quote

Hi Frank,
Thanks a lot for your response. I will test this out tomorrow. If I remember correctly, TRAILER command did not work for VSAM files when I tried. Anyway I will check that one also. Thanks again. icon_biggrin.gif
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Feb 07, 2007 10:45 pm    Post subject:
Reply with quote

Quote:
If I remember correctly, TRAILER command did not work for VSAM files when I tried. Anyway I will check that one also.


I don't know of any reason why TRAILER1 wouldn't work for VSAM files unless you didn't generate the "key" in the trailer file that the KSDS expected. In this case, we are generating a high value key for the trailer record so that shouldn't be a problem, given that the key is actually the first 10 bytes of the record. I don't know how you defined the KSDS so I'm assuming when you say the key is in the first 10 bytes, you know that's where it actually is. If not, then you need to figure out where the key really is and change the job appropriately.
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 Feb 07, 2007 11:04 pm    Post subject:
Reply with quote

Hi K-o-M,

We don't use the "long" records in CICS. They were built many years ago by de-normalizing the online definitions so ad-hoc reporting could be easily done against them in batch. These became the history files and a new one is created each month. The online has only relatively current data. A lot of the work going on right now is to re-normalize the history files into a datawarehouse. The normalized version of the history data will be in the 10-12 tera-byte range. In addition to the "rolled-up" cubes, we are providing views so end-users will not have to navigate the underlying structures for their ad-hoc queries.
Back to top
View user's profile Send private message
kingofmainframes
Warnings : 2

New User


Joined: 09 Apr 2006
Posts: 20
Location: Chennai

PostPosted: Thu Feb 08, 2007 8:53 pm    Post subject: Re: Merge the 2 files File1 & File 2 and create File3
Reply with quote

Hi Frank,

I am getting the below error when I run this job. Kindly advice.

ICE224A 0 SORTOUT CANNOT BE USED FOR A REPORT - RECFM WITHOUT 'A' SPECIFIED, OR VSAM DATA SET
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Feb 08, 2007 9:52 pm    Post subject:
Reply with quote

Oh, it appears you're right that TRAILER1 can't be used with a VSAM output file (even if REMOVECC is specified).

You just need to send the output to a temporary file and then copy the temporary file to the VSAM file like this:

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN01 DD DSN=...  VSAM input file1
//SORTIN02 DD DSN=...  VSAM input file2
...
//SORTIN10 DD DSN=...  VSAM input file10
//SORTOUT DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//SYSIN    DD    *
  RECORD TYPE=F
  OMIT COND=(1,10,BI,EQ,X'00000000000000000000')
  INREC IFTHEN=(WHEN=INIT,
     OVERLAY=(3001:+0,M11,LENGTH=10,
              3011:+0,M11,LENGTH=10,
              3021:+0,M11,LENGTH=10)),
    IFTHEN=(WHEN=(11,1,CH,EQ,C'I'),
      OVERLAY=(3001:+1,M11,LENGTH=10)),
    IFTHEN=(WHEN=(11,1,CH,EQ,C'N'),
      OVERLAY=(3011:+1,M11,LENGTH=10)),
    IFTHEN=(WHEN=(11,1,CH,EQ,C'Y'),
      OVERLAY=(3021:+1,M11,LENGTH=10))
  MERGE FIELDS=(1,10,BI,A)
  OUTFIL REMOVECC,
    BUILD=(1,3000),
    TRAILER1=(X'FFFFFFFFFFFFFFFFFFFF',' TOTAL=',
     COUNT=(M11,LENGTH=10),
     ' I=',TOT=(3001,10,ZD,M11,LENGTH=10),
     ' N=',TOT=(3011,10,ZD,M11,LENGTH=10),
     ' Y=',TOT=(3021,10,ZD,M11,LENGTH=10))
/*
//S2    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=&&T1,DISP=(OLD,PASS)
//SORTOUT DD DSN=...  VSAM output file
//SYSIN    DD    *
  OPTION COPY
/*
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
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


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