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
 

 

Retain header and trailer in Select statement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
vramya

New User


Joined: 07 Nov 2008
Posts: 13
Location: US

PostPosted: Wed Jul 14, 2010 5:03 pm    Post subject: Retain header and trailer in Select statement
Reply with quote

I have a variable length record and I have to select the rows based on particular set of values and the output file should not contain redundant records. (avoid duplicates and have only the first record found)

I have used the following with ICETOOL and it worked fine.

SELECT FROM(SORTIN1) TO(SORTOUT) ON(VLEN) -
ON(1,2,PD) ON (3,4,CH) -
FIRST VSAMTYPE(V)

In case, if I have to retain the header and trailer, do we have any way to
add in the above statement.
(I have one header and one trailer record in the file).

(or) do we have to use only datasort to achieve this ?

Thanks!
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 Jul 14, 2010 11:27 pm    Post subject:
Reply with quote

What does your header record look like? What does your trailer record look like? In particular, is there something in the header and trailer record that identify them as such (e.g. '0' in position 5 for header and '9' in position 5 for trailer)?
Back to top
View user's profile Send private message
vramya

New User


Joined: 07 Nov 2008
Posts: 13
Location: US

PostPosted: Thu Jul 15, 2010 3:30 pm    Post subject: Reply to: Retain header and trailer in Select statement
Reply with quote

The header record has '00' in the first two position and the trailer has '99' in the first two position.
Back to top
View user's profile Send private message
vramya

New User


Joined: 07 Nov 2008
Posts: 13
Location: US

PostPosted: Thu Jul 15, 2010 7:43 pm    Post subject: Reply to: Retain header and trailer in Select statement
Reply with quote

Requirement is:

Input record:
00123LAC0000000
01123LAC1234520
....
99999LAC0000000

Its a variable length record.

a) The output should not have redundant records
(Order of the sorting should be:
(3,3,CH,A) (6,3,CH,A) (9,7,CH,A), (1,2,CH,A) )

b) Header and Trailer should be retained

I had a problem when I used VLEN, as (1,2,BI) was also getting executed which affected the sort order. When I removed it, everything was in place except for Header & Trailer.Just thinking if we have some logic to incorporate in the same 'select for' command'

Thanks!
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 Jul 15, 2010 10:15 pm    Post subject:
Reply with quote

You don't seem to understand VB records. VB records have an RDW in positions 1-4 followed by the data starting in position 5. So all of your starting positions are wrong.

Also, you only show the header and trailer and one data record, so I can't tell if the data records all start with '01' or not. Do they? If all of the data records start with '01', this is much easier.

Please show a more complete example of input records and expected output records.

Also, please show the //DFSMSG messages you received for your run so I can verify that you really have VB records.
Back to top
View user's profile Send private message
vramya

New User


Joined: 07 Nov 2008
Posts: 13
Location: US

PostPosted: Fri Jul 16, 2010 11:24 am    Post subject: Reply to: Retain header and trailer in Select statement
Reply with quote

I am sorry that I posted a wrong card.

Output File looks like this:

0036DII000000000000000000002010-06-06DIRLIN
9936DII000000000000000000002010-06-06DIRLIN
0136DII00020139 PMR CLIFOR000201
0336DII00020139 CLICU100020139
0436DII00020139 6000432464
0136DII00022394 PMR CLIFOR000223
0336DII00022394 CLICU100022394
0436DII00022394 6000357646

I have used the following in my job:

SELECT FROM(SORTIN1) TO(SORTOUT) -
ON(7,2,PD) ON(9,3,CH) ON(12,20,CH) ON(5,2,CH) -
FIRST VSAMTYPE(V)

All I want is: can I exclude the header and trailer from sorting.
I want the record starting with '00' and '99' to be in the same position as like the input file.

DFSMSG:

DEBUG NOABEND,ESTAE
OPTION MSGDDN=DFSMSG,LIST,MSGPRT=ALL,RESINV=0,SORTIN=SORTIN1,SORTOUT=S
RTOUT,DYNALLOC,SZERO,EQUALS,NOVLSHRT,LOCALE=NONE,NOCHEC
SORT FIELDS=(7,2,PD,A,9,3,CH,A,12,20,CH,A,5,2,CH,A)
MODS E35=(ICE35DU,12288)
RECORD TYPE=V

Many thanks!
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Jul 16, 2010 10:47 pm    Post subject:
Reply with quote

vramya,

It would have helped if you had posted the entire DFSMSG. Either way use the following control cards.


Code:

//TOOLIN   DD *                                         
   SELECT FROM(SORTIN1) TO(SORTOUT)                -   
   ON(5,1,CH) ON(8,2,PD) ON(10,3,CH) ON(13,20,CH)  -   
   FIRST VSAMTYPE(V) USING(CTL1)                       
//CTL1CNTL DD *                                         
   INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,C'1',5)),         
   IFTHEN=(WHEN=(6,2,CH,EQ,C'00'),OVERLAY=(5:C'0')),   
   IFTHEN=(WHEN=(6,2,CH,EQ,C'99'),OVERLAY=(5:C'9'))     
   OUTFIL FNAMES=SORTOUT,BUILD=(1,4,6)                 
//*
Back to top
View user's profile Send private message
vramya

New User


Joined: 07 Nov 2008
Posts: 13
Location: US

PostPosted: Mon Jul 19, 2010 6:43 pm    Post subject: Reply to: Retain header and trailer in Select statement
Reply with quote

I am able to complete it. Thanks Everyone!!
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Validating file with multiple header/... mohitsaini DFSORT/ICETOOL 6 Thu Apr 13, 2017 1:53 am
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm


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