MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Sort Input file based on certain criteria !!
Goto page 1, 2, 3  Next
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
amit4u79
Beginner


Joined: 24 Oct 2005
Posts: 109
Topics: 36
Location: India

PostPosted: Mon Feb 20, 2006 6:04 am    Post subject: Sort Input file based on certain criteria !! Reply with quote

Hi, I have 2 queries related to DFSORT:

1) Suppose my input file has records like the one below:

Code:

Jobname  TimeTaken ----->  Just for understanding, no header record

JOB1        00:30:00
JOB2        00:34:00
JOB1        00:31:00
JOB3        01:00:00


So, my requirement is to write in output file only 1 Jobname and remove the duplicate entries by averaging out the value in the "Time Taken" column above.

So the output in above case may be:

Code:

JOB1       00:30:30
JOB2       00:34:00
JOB3       01:00:00


Actually, i am asking for DFSORT as REXX manipulation would consume a lot of CPU, since the input file contains like 50000 or more records. Kindly let me know if this manipulation of time in the second column can be done.

2) My input file is the SYSLOG which looks like:

Code:

sssssssssssssssssssss  * dsfdsfsfsdfsdfsdfsdf
                                    adsasdasa
qqqqqqqqqqqqqqqqqq     weqweqweqw
                                    sdasdasdasdasd
eeeeeeeeeeeeeeeeee  * tyrtyrtyrtyrtyrtyrt


As can be seen the first 2 lines form the entire 1st record, in the syslog. Also, record in the syslog at position 58 there is a '*'.
A record ends when the 1st column does not contain a blank, so the first record above is "sssssssssssssssssssss * dsfdsfsfsdfsdfsdfsdf adsasdasa".

Now my requirement is to write all the records in the output file which contains the asterisk at position 58, so in case above the output file would be like:

Code:

sssssssssssssssssssss  * dsfdsfsfsdfsdfsdfsdfadsasdasa


I would assign the output data set to be of a Lrecl large enuf to concatenate the 2 lines of record. But kindly let me know if this is possible.

Thanks,
- Amit.
_________________
I did not fail; I have found 10,000 ways that would not work - Albert Einstein.
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Mon Feb 20, 2006 6:20 am    Post subject: Reply with quote

amit4u79,

Both of the queries can be solved.

1. Simply convert the time into secs and get the avg and then convert them back to time format.

2. Check this topic .

http://www.mvsforums.com/helpboards/viewtopic.php?t=4356&highlight=

Use the logic in the above and change step0200 to get all dups on the seqnum padded. so you get the two conscuetive records.

If you still need assistance then please provide the LRECL and RECFM of input and output datasets.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose

PostPosted: Mon Feb 20, 2006 11:23 am    Post subject: Reply with quote

Quote:
1. Simply convert the time into secs and get the avg and then convert them back to time format.


Hmmm ... not really all that "simple", unless I'm missing something. Here's a DFSORT/ICETOOL job to do it. I assumed that the input file has RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes. Note that it uses some of the Dec, 2004 DFSORT PTF functions.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG   DD  SYSOUT=*
//IN DD *
JOB1        00:30:00
JOB2        00:34:00
JOB1        00:31:00
JOB3        01:00:00
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD SYSOUT=*
//TOOLIN   DD    *
SORT FROM(IN) USING(CTL1)
COPY FROM(T1) TO(OUT) USING(CTL2)
//CTL1CNTL DD *
  SORT FIELDS=(1,4,CH,A)
* Get t=hh*3600+mm*60+ss
  OUTREC OVERLAY=(81:(13,2,ZD,MUL,+3600),ADD,
      (16,2,ZD,MUL,+60),ADD,19,2,ZD,TO=ZD,LENGTH=8)
* Get average of t for each job number
  OUTFIL FNAMES=T1,REMOVECC,NODETAIL,
   SECTIONS=(1,4,
     TRAILER3=(1,80,AVG=(81,8,ZD,TO=ZD,LENGTH=8)))
//CTL2CNTL DD *
* Get hh=t/3600, x1=t//3600, mm=x1/60, ss=x1//60
  INREC OVERLAY=(89:81,8,ZD,DIV,+3600,TO=ZD,LENGTH=2,
    95:81,8,ZD,MOD,+3600,TO=ZD,LENGTH=8,
    91:95,8,ZD,DIV,+60,TO=ZD,LENGTH=2,
    93:95,8,ZD,MOD,+60,TO=ZD,LENGTH=2,
* Edit hhmmss as hh:mm:ss
    13:89,6,ZD,EDIT=(TT:TT:TT))
* Remove temp. fields.
  OUTREC BUILD=(1,80)
/*

_________________
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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose

PostPosted: Mon Feb 20, 2006 11:27 am    Post subject: Reply with quote

Amit,

For 2), it's not too clear what the data actually looks like for input or output. Could you show an example with more than one record to be included, and use the ubb code tags to show what the data actually looks like. Also, what is the RECFM and LRECL of the input data set.
_________________
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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Mon Feb 20, 2006 1:03 pm    Post subject: Reply with quote

Frank,

I was thinking of calculating the avg myself within sort instead of using the reporting features. here are the untested control cards.

Code:

  SORT FIELDS=(1,4,CH,A)                     
  INREC OVERLAY=(81:(13,2,ZD,MUL,+3600),ADD,
                 (16,2,ZD,MUL,+60),ADD,19,2,ZD,TO=ZD,LENGTH=8,
                  C'00000001')
  SUM FIELDS=(81,8,ZD,
              89,8,ZD)

  OUTREC OVERLAY=(81:81,8,ZD,DIV,89,8,ZD,ZD,LENGTH=8,
                  89:81,8,ZD,DIV,+3600,TO=ZD,LENGTH=2, 
                  95:81,8,ZD,MOD,+3600,TO=ZD,LENGTH=8,
                  91:95,8,ZD,DIV,+60,TO=ZD,LENGTH=2,
                  93:95,8,ZD,MOD,+60,TO=ZD,LENGTH=2,
                  13:89,6,ZD,EDIT=(TT:TT:TT)) 
  OUTFIL BUILD=(1,80)
/*


Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose

PostPosted: Mon Feb 20, 2006 2:07 pm    Post subject: Reply with quote

Oh, ok. I changed the second OUTREC to an OUTFIL for you, and it works and only takes one pass. But I still wouldn't call that "simple" given all of the arithmetic. Wink
_________________
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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
amit4u79
Beginner


Joined: 24 Oct 2005
Posts: 109
Topics: 36
Location: India

PostPosted: Mon Feb 20, 2006 8:46 pm    Post subject: Reply with quote

Hey Frank and Kolusu...you guys are really great !! Thanks a lot for all your help...actually for 2nd query was, my dataset is the syslog, so probably tht will give you an idea of what the records look like. We are working on automation to list out all the highlighted messages for the previous day by analysing our syslog data set, and in syslog data set column 58 contains a '*', but the record would extend till next line. The end of record is marked by column 20-26 not being blank, as for the next record, these columns would contain the timestamp for the next event in the syslog. I hope you understand the requirement. I had just given an example in the above case....the records can be like...

aaaa bbbbbb ccccc ee2005041 dddd * eeeeeeee dfsfdsdfs sdfsfdsdf
sdasdasd

wwww bbbbbb ccccc e2005041 dddd eeeeeeee werewrw erwerwerwr
sdasdasd

In the input record above, you may see columns 20-26 contains the date, and the record is extended till next line, so the above file contains only 2 records. But we would have in the output file the entire first record, as there is an asterisk at a particular position in the record. So basically some sort card which would give me an output as a concatenated first record, something like the one below:

aaaa bbbbbb ccccc ee2005041 dddd * eeeeeeee dfsfdsdfs sdfsfdsdf sdasdasd

Anyways, thanks a lot folks again. I am so thankful to all of you.

Regards,
- Amit.
_________________
I did not fail; I have found 10,000 ways that would not work - Albert Einstein.
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose

PostPosted: Tue Feb 21, 2006 11:31 am    Post subject: Reply with quote

Amit,

I don't know what a syslog looks like (I'm a developer - I don't use syslogs).

(Kolusu probably knows what a syslog looks like so maybe he can help you.)

If you want me to help you, you're going to have to answer the questions I asked so I can understand what you're trying to do:

For 2), it's not too clear what the data actually looks like for input or output. Could you show an example with more than one record to be included, and use the ubb code tags to show what the data actually looks like. Also, what is the RECFM and LRECL of the input data set.
_________________
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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Tue Feb 21, 2006 12:08 pm    Post subject: Reply with quote

Quote:

I don't know what a syslog looks like (I'm a developer - I don't use syslogs).

Frank,

A syslog dataset has the following attributes

Code:

RECFM  = FBA
LRECL  = 133


and I understood the reuirements as follows.

one of the line in the sysout is split into 2 lines. The way to identify these records is an asterick at pos 58 on the first line of the split line. so OP wants to pick all these split lines and then read them as one single record.

for ex:

Code:

SSSSSSSSSSSSSSSSSSSSS                  * DSFDSFSFSD         
ADSASDASA                                                   
QQQQQQQQQQQQQQQQQQ WEQWEQWEQW                               
SDASDASDASDASD                                             
EEEEEEEEEEEEEEEEEE                     * TYRTYRTYR         
TYRTYRTYRTYRTYRTYRT                                         


The intention is to pick 1,2 & 5,6th record.

once you pick these
Code:

SSSSSSSSSSSSSSSSSSSSS                  * DSFDSFSFSD         
ADSASDASA       
EEEEEEEEEEEEEEEEEE                     * TYRTYRTYR         
TYRTYRTYRTYRTYRTYRT                                         


he wants to join the split records as a single record

Code:

SSSSSSSSSSSSSSSSSSSSS    * DSFDSFSFSD  ADSASDASA       
EEEEEEEEEEEEEEEEEE       * TYRTYRTYR   TYRTYRTYRTYRTYRTYRT


Kolusu

Code:

//STEP0100 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD *                                             
----+----1----+----2----+----3----+----4----+----5----+----6-
SSSSSSSSSSSSSSSSSSSSS                  * DSFDSFSFSD         
ADSASDASA                                                   
QQQQQQQQQQQQQQQQQQ WEQWEQWEQW                               
SDASDASDASDASD                                               
EEEEEEEEEEEEEEEEEE                     * TYRTYRTYR           
TYRTYRTYRTYRTYRTYRT                                         
//SORTOUT  DD DSN=&T1,DISP=(NEW,CATLG,DELETE),DATACLAS=FBSML
//SYSIN    DD *                                             
 SORT FIELDS=COPY                                           
 OUTREC FIELDS=(01,133,                                       
               134:40,1,CHANGE=(1,C'*',C'1'),                 
                        NOMATCH=(C'0'),                       
               SEQNUM,8,ZD)                                 
 OUTFIL OUTREC=(1,133,                                       
                134,1,ZD,ADD,135,8,ZD,EDIT=(TTTTTTTT))         
/*                                                           
//STEP0200 EXEC PGM=ICETOOL                                 
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//IN       DD DSN=&T1,DISP=OLD                               
//OUT      DD SYSOUT=*                                       
//TOOLIN   DD *                                             
 SELECT FROM(IN) TO(OUT) ON(134,8,CH) ALLDUPS                 
/*                             


This will give
Code:

SSSSSSSSSSSSSSSSSSSSS                  * DSFDSFSFSD 
ADSASDASA                                           
EEEEEEEEEEEEEEEEEE                     * TYRTYRTYR   
TYRTYRTYRTYRTYRTYRT                                 


Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
amit4u79
Beginner


Joined: 24 Oct 2005
Posts: 109
Topics: 36
Location: India

PostPosted: Tue Feb 21, 2006 8:30 pm    Post subject: Reply with quote

Thanks a lot Kolusu and Frank...yes Kolusu the output you have obtained is fine with me.
Thanks a lot for understanding my requirement and providing the needed solution.

This forum rocks.

Regards,
- Amit.
_________________
I did not fail; I have found 10,000 ways that would not work - Albert Einstein.
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
amit4u79
Beginner


Joined: 24 Oct 2005
Posts: 109
Topics: 36
Location: India

PostPosted: Tue Feb 21, 2006 9:30 pm    Post subject: Reply with quote

Hi Frank, I used your JCL which used ICETOOL to get the average of JOBs, but it gave me a max cc of 16 with the following errors:

Code:

ICE000I 0 - CONTROL STATEMENTS FOR 5740-SM1, DFSORT REL 14.0 - 19:19 ON TUE FEB
            SORT FIELDS=(1,4,CH,A)                                             
            OUTREC OVERLAY=(81:(13,2,ZD,MUL,+3600),ADD,                       
                   $                                                           
ICE104A 0 INVALID INREC OR OUTREC STATEMENT OPERAND                           
                (16,2,ZD,MUL,+60),ADD,19,2,ZD,TO=ZD,LENGTH=8)                 
                $                                                             
ICE007A 1 SYNTAX ERROR                                                         
            OUTFIL FNAMES=T1,REMOVECC,NODETAIL,                               
             SECTIONS=(1,4,                                                   
               TRAILER3=(1,80,AVG=(81,8,ZD,TO=ZD,LENGTH=8)))                   
                                           $                                   
ICE006A 0 OPERAND DEFINER ERROR                                               
ICE146I 0 END OF STATEMENTS FROM CTL1CNTL - PARAMETER LIST STATEMENTS FOLLOW
          DEBUG NOABEND,ESTAE
          OPTION LIST,MSGPRT=ALL,MSGDDN=DFSMSG,RESINV=0,SORTDD=CTL1,SORTIN=IN,D
                         ALLOC                                                 
ICE012A 3 MISSING FIELDS OPERAND DEFINER                                       
ICE052I 3 END OF DFSORT


I had removed the comments you had placed in the CTL1 and CTL2 DD statement, but even after that it gives me error above. Kindly assist or advise on how should I rectify it.I had just cut pasted your code and run the JCL.

Regards,
- Amit.
_________________
I did not fail; I have found 10,000 ways that would not work - Albert Einstein.
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
amit4u79
Beginner


Joined: 24 Oct 2005
Posts: 109
Topics: 36
Location: India

PostPosted: Wed Feb 22, 2006 2:44 am    Post subject: Reply with quote

Hi Frank/Kolusu, just to inform you my shop has DFSORT release 14 presently. Pls let me know why am I getting the error above, its not only an issue with continuation of card statements to next line but also it seems it does not take certain parameters along with INREC and OUTREC.

Kindly assist.

Regards,
- Amit.
_________________
I did not fail; I have found 10,000 ways that would not work - Albert Einstein.
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose

PostPosted: Wed Feb 22, 2006 12:59 pm    Post subject: Reply with quote

The error message for OVERLAY indicates that you do not have DFSORT R14 PTF UQ95213 (Dec, 2004) installed. You need this PTF in order to use features such as IFTHEN, OVERLAY, UFF, SFF, etc as described in:

www.ibm.com/servers/storage/support/software/sort/mvs/pdug/


Ask your System Programmer to install DFSORT R14 PTF UQ95213 so you can use these features (it's free).
_________________
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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
amit4u79
Beginner


Joined: 24 Oct 2005
Posts: 109
Topics: 36
Location: India

PostPosted: Wed Feb 22, 2006 8:44 pm    Post subject: Reply with quote

Hi Frank, Kolusu, thanks a lot for all your help. I know asking this question is sort of not good, but I had a discussion with the MVS team here and they told me the PTFs are applied by them at predefined intervals and so this would not be applied till end of July.

But we have to move the SORT JCL to find average time of Jobs in production by next week. Its a catch situation I am in. So Frank can you kindly suggest is there any other alternative solution then the one you have send using OVERLAY, as I am not able to use either yours or Kolusu's solution for the first part of my initial query, where I am supposed to find out average time of jobs and list out only distinct jobs.

I hope you understand. Kindly help.

Regards,
- Amit.
_________________
I did not fail; I have found 10,000 ways that would not work - Albert Einstein.
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
amit4u79
Beginner


Joined: 24 Oct 2005
Posts: 109
Topics: 36
Location: India

PostPosted: Thu Feb 23, 2006 5:51 am    Post subject: Reply with quote

Hi Kolusu thanks a lot for your solution of writing selective data from SYSLOG to an output data set where the selection criteria is a '*' at a certain position. But my syslog data set is a VBA 137 file and to copy it to a temporary &T1, I dont know what space parameter to specify. I will then in next step using ICETOOL copy it to the output data set which also would be a VBA 137 data set.

Can you tell me what modification would I need to make in that JCL ??

Regards,
- Amit.
_________________
I did not fail; I have found 10,000 ways that would not work - Albert Einstein.
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Goto page 1, 2, 3  Next
Page 1 of 3

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group