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 

Syncsort join a multiple rec file with a single rec file.

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
prog_mario
Beginner


Joined: 08 Sep 2007
Posts: 86
Topics: 27
PostPosted: Thu Dec 09, 2010 11:19 am    Post subject: Syncsort join a multiple rec file with a single rec file. Reply with quote
Hi everybody. I'm working on a unload file from which I'll delete old records to load it back again.

FILE_A (unload. contains many records)

aa2010-10-10mmmmmm
bb2008-10-22000000000
.
.
.

FILE_B ( constains only one record. The date used for the comparison)

2009-01-01


I need to output 2 files comparing the dates from both files:
When FILE_A < FILE_B I need to write output 1.
When FILE_A >= FILE_B I need to write output 2.

Thanks once again.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Thu Dec 09, 2010 11:26 am    Post subject: Reply with quote
prog_mario,

I am confused as to why you need to unload and delete the records and reload it back again.

Why can't you just delete off the rows from the table with the date in the Where condition?

Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
prog_mario
Beginner


Joined: 08 Sep 2007
Posts: 86
Topics: 27
PostPosted: Thu Dec 09, 2010 11:37 am    Post subject: Reply with quote
The client I work for uses utilities a lot instead of batch programs because of performance. In this case this logic will be a PROC step that will run monthly to delete old data from the table. In fact the output file with old data wil be backed up to a gdg and the other will be load replaced.
_________________
The more I learn, the more I want to learn.
Back to top
View user's profile Send private message
prog_mario
Beginner


Joined: 08 Sep 2007
Posts: 86
Topics: 27
PostPosted: Thu Dec 09, 2010 11:41 am    Post subject: Reply with quote
The date used for comparison exists in a parameter table as 750 days. I unload that paramenter already calculating the current date minus 750 days. And that's my file with only one record.
_________________
The more I learn, the more I want to learn.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Thu Dec 09, 2010 11:50 am    Post subject: Reply with quote
prog_mario,

DSNTIAUL utility can indeed be used to unload as well as delete the rows in a single step.

ex:
Code:

//STEP0100 EXEC PGM=IKJEFT01                   
//SYSTSPRT DD  SYSOUT=*,DCB=BLKSIZE=121       
//SYSPRINT DD  SYSOUT=*                       
//SYSTSIN  DD  *                               
 DSN SYSTEM(xxxx)                             
 RUN  PROGRAM(DSNTIAUL) -                     
      PLAN(DSNTIAUL)    -                     
      PARMS('SQL')      -                     
      LIB('DB2P.RUNLIB.LOAD')                 
//SYSREC00 DD DSN=your GDG backup(+1),
//            DISP=(,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(CYL,(X,Y),RLSE)
//SYSPUNCH DD SYSOUT=*                         
//SYSIN    DD *
  SELECT * FROM TABLE WHERE DATE_COL <= current date - 750 days;
  DELETE FROM TABLE WHERE DATE_COL <= current date - 750 days;
//*


Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
prog_mario
Beginner


Joined: 08 Sep 2007
Posts: 86
Topics: 27
PostPosted: Thu Dec 09, 2010 12:02 pm    Post subject: Reply with quote
In fact when we build a new process we just follow instructions from the specs they send.
_________________
The more I learn, the more I want to learn.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Thu Dec 09, 2010 12:10 pm    Post subject: Reply with quote
prog_mario wrote:
In fact when we build a new process we just follow instructions from the specs they send.


Well, you need to find an optimal solution rather than simply following instructions.

Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
prog_mario
Beginner


Joined: 08 Sep 2007
Posts: 86
Topics: 27
PostPosted: Thu Dec 09, 2010 12:44 pm    Post subject: Reply with quote
I always try to provide better solutions but I can do nothing if the client says no.
_________________
The more I learn, the more I want to learn.
Back to top
View user's profile Send private message
prog_mario
Beginner


Joined: 08 Sep 2007
Posts: 86
Topics: 27
PostPosted: Thu Dec 09, 2010 4:10 pm    Post subject: Reply with quote
Hi everybody, I think I found the solution:

I added c'1' at the begining of both files, so I can use JOINKEYS.
Code:

//JS020   EXEC PGM=SORT,COND=(4,LT)                           
//SYSOUT   DD  SYSOUT=*                                       
//SORTJNF1 DD  DISP=SHR,DSN=XXXX.XXXXXX.XXXXXXXX.XXXXX.UPD     
//SORTJNF2 DD  DISP=SHR,DSN=XXXX.XXXXXX.XXXXXXXX.XXXXX.UNL     
//SORTOF01 DD  DSN=XXXX.XXXXXX.XXXXXXXX.XXXXX.PURGE,           
//             DISP=(,CATLG,DELETE),                       
//             UNIT=TSTDA,SPACE=(CYL,(200,200),RLSE)           
//SORTOF02 DD  DSN=XXXX.XXXXXX.XXXXXXXX.XXXXX.RETAIN           
//             DISP=(,CATLG,DELETE),                       
//             UNIT=TSTDA,SPACE=(CYL,(200,200),RLSE)           
//SYSIN    DD  *                                               
  JOINKEYS FILE=F1,FIELDS=(01,01,A)    UNLOAD UPDATED         
  JOINKEYS FILE=F2,FIELDS=(01,01,A)    PURGE PARAMETER DATE   
  REFORMAT FIELDS=(F1:02,95,                                   
                   F2:02,10)                                   
  SORT FIELDS=COPY                                     
  OUTFIL FILES=01,INCLUDE=(11,10,CH,LT,96,10,CH),OUTREC=(02,95)
  OUTFIL FILES=02,INCLUDE=(11,10,CH,GE,96,10,CH),OUTREC=(02,95)
                                       

POSITIONS 11,10 AND 96,10 ARE THE DATES I NEED TO COMPARE.


Thanks anyway everybody.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Thu Dec 09, 2010 4:28 pm    Post subject: Reply with quote
prog_mario,

It is simple copy and you complicated it. Use the following JCL to create the symbol from DB2.

http://www.mvsforums.com/helpboards/viewtopic.php?p=28104#28104

and use this step.

Code:

//STEP0100 EXEC PGM=SORT                             
//SYSOUT   DD SYSOUT=*                               
//SYMNAMES DD *                                       
CUTOFFDATE,C'2009-01-01'                             
//SORTIN   DD *                                       
AA        2010-09-01 MMMMMM                           
BB        2009-08-31 000000000                       
CC        2008-10-22 000000000                       
//SORTOF01 DD SYSOUT=*                               
//SORTOF02 DD SYSOUT=*                               
//SYSIN    DD *                                       
  SORT FIELDS=COPY                                   
  OUTFIL FILES=01,INCLUDE=(11,10,CH,LT,CUTOFFDATE)   
  OUTFIL FILES=02,SAVE                               
//*


And for the record DFSORT can handle the date arthimetic and split in a single pass without involving DB2 like shown below

Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                       
AA        2010-09-01 MMMMMM                                           
BB        2009-08-31 000000000                                       
CC        2008-10-22 000000000                                       
//SORTOF01 DD SYSOUT=*                                               
//SORTOF02 DD SYSOUT=*                                               
//SYSIN    DD *                                                       
  SORT FIELDS=COPY                                                   
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(96:DATE1)),                       
  IFTHEN=(WHEN=INIT,OVERLAY=(104:96,8,Y4T,SUBDAYS,+750,TOGREG=Y4T(-)))
                                                                     
  OUTFIL FILES=01,INCLUDE=(11,10,CH,LT,104,10,CH),BUILD=(1,95)       
  OUTFIL FILES=02,SAVE,BUILD=(1,95)                                   
//*

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


Joined: 08 Sep 2007
Posts: 86
Topics: 27
PostPosted: Fri Dec 10, 2010 8:08 pm    Post subject: Reply with quote
It works!!!!!!!!!!!
"Symbols" are really what I was looking for.
My sysin has only 3 lines now.
Thank you very much again, Kolusu.
_________________
The more I learn, the more I want to learn.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
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