Page 1 of 1

USe system rundate and find last months' end dates

PostPosted: Thu Jul 22, 2010 3:28 pm
by jasand
Hi,

Please let me know how to get the below requirement.
I need to manipulate the system run date and get last two month's end dates.

For eg: If today is June 1st or 3rd,
then the output file (FB/80) should hold 'May' and 'April' month end dates.

The output file should hold a single record as below,
ABCDEFGXX0000053110043010

where
ABCDEFG - constant
XX - a parm value (alphabetic) (Can we pass the parm value in the SORT JCL ?)
0000 - constant
053110 - constant (MMDDYY - May month's end date)
043010 - constant (MMDDYY - April month's end date)

Thanks.

Re: USe system rundate and find last months' end dates

PostPosted: Thu Jul 22, 2010 3:30 pm
by jasand
a small correction in my above query..

053110 - date field (MMDDYY - May month's end date)
043010 - date field (MMDDYY - April month's end date)

Re: USe system rundate and find last months' end dates

PostPosted: Thu Jul 22, 2010 10:27 pm
by skolusu
Jasand,

You cannot directly pass parm values to DFSORT , however you can create a symnames dataset (FB lrecl=80) and pass the value. Look at the example below. This job will give the last date of CURRENT MONTH-1 AND CURRENT MONTH-2 . Today is July 22 , you would get the last dates of June and May.

//STEP0100 EXEC PGM=SORT     
//SYSOUT   DD SYSOUT=*       
//SYMNAMES DD *             
PVALUE,C'XX'                 
//SORTIN   DD *             
A                           
//SORTOUT  DD SYSOUT=*       
//SYSIN    DD *                                                         
  SORT FIELDS=COPY                                                     
  INREC IFTHEN=(WHEN=INIT,BUILD=(DATE2,C'01',X,DATE2-1,C'01')),         
  IFTHEN=(WHEN=INIT,                                                   
  OVERLAY=(20:1,8,Y4T,TOJUL=Y4T,30:10,8,Y4T,TOJUL=Y4T)),               
  IFTHEN=(WHEN=(24,3,ZD,EQ,1),                                         
  OVERLAY=(01:1,4,ZD,SUB,+1,EDIT=(TTTT),C'1231',                       
           20:1,8,Y4T,TOJUL=Y4T),HIT=NEXT),                             
  IFTHEN=(WHEN=(34,3,ZD,EQ,1),                                         
  OVERLAY=(10:10,4,ZD,SUB,+1,EDIT=(TTTT),C'1231',                       
           30:10,8,Y4T,TOJUL=Y4T),HIT=NEXT),                           
  IFTHEN=(WHEN=(28,1,CH,EQ,C' '),                                       
  OVERLAY=(24:24,3,ZD,SUB,+1,EDIT=(TTT),                               
           40:20,7,Y4T,TOGREG=Y4T),HIT=NEXT),                           
  IFTHEN=(WHEN=(38,1,CH,EQ,C' '),                                       
  OVERLAY=(34:34,3,ZD,SUB,+1,EDIT=(TTT),                               
           50:30,7,Y4T,TOGREG=Y4T),HIT=NEXT),                           
  IFTHEN=(WHEN=(40,1,CH,EQ,C' '),OVERLAY=(40:20,7,Y4T,TOGREG=Y4T)),     
  IFTHEN=(WHEN=(50,1,CH,EQ,C' '),OVERLAY=(50:30,7,Y4T,TOGREG=Y4T))     
                                                                       
  OUTREC BUILD=(C'ABCDEFG',            $ CONSTANT                       
                PVALUE,                $ PARM FROM SYMNAMES             
                C'0000',               $ CONSTANT                       
                40,8,Y4T,DTNS=(MDY),   $ LAST MONTH LAST DATE           
                50,8,Y4T,DTNS=(MDY),   $ LAST 2 MONTH LAST DATE         
                80:X)                  $ PAD SPACES UPTO 80             
//*


The output from this job is
ABCDEFGXX0000063010053110

Re: USe system rundate and find last months' end dates

PostPosted: Mon Jul 26, 2010 11:38 am
by jasand
Hi Skolusu,

Thanks for your reply.

It seems that the function Y4T is not working for me.

I get the below error message,
 DATA DICTIONARY SYMBOLS SUBSTITUTED :                                         
 SORT FIELDS=COPY                                                               
 INREC IFTHEN=(WHEN=INIT,BUILD=(DATE2,C'01',X,DATE2-1,C'01')),IFTHEN=(WHEN=INIT,
 VERLAY=(20:1,8,Y4T,TOJUL=Y4T,30:10,8,Y4T,TOJUL=Y4T)),IFTHEN=(WHEN=(24,3,ZD,EQ,1
                *                                                               
 ,OVERLAY=(01:1,4,ZD,SUB,+1,EDIT=(TTTT),C'1231',20:1,8,Y4T,TOJUL=Y4T),HIT=NEXT),
 FTHEN=(WHEN=(34,3,ZD,EQ,1),OVERLAY=(10:10,4,ZD,SUB,+1,EDIT=(TTTT),C'1231',30:10
 8,Y4T,TOJUL=Y4T),HIT=NEXT),IFTHEN=(WHEN=(28,1,CH,EQ,C' '),OVERLAY=(24:24,3,ZD,S
 B,+1,EDIT=(TTT),40:20,7,Y4T,TOGREG=Y4T),HIT=NEXT),IFTHEN=(WHEN=(38,1,CH,EQ,C' '
 ,OVERLAY=(34:34,3,ZD,SUB,+1,EDIT=(TTT),50:30,7,Y4T,TOGREG=Y4T),HIT=NEXT),IFTHEN
 (WHEN=(40,1,CH,EQ,C' '),OVERLAY=(40:20,7,Y4T,TOGREG=Y4T)),IFTHEN=(WHEN=(50,1,CH
 EQ,C' '),OVERLAY=(50:30,7,Y4T,TOGREG=Y4T))                                     
 WER268A  INREC STATEMENT   : SYNTAX ERROR                                     
 WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                                 
 WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                                 
******************************** BOTTOM OF DATA ********************************


But when trying with the initial piece of code alone, I get the month start dates, so the problem is with Y4T

//STEP0100 EXEC PGM=SORT,REGION=0M                             
//SYSOUT   DD SYSOUT=*                                         
//SYMNAMES DD *                                                 
PVALUE,C'XX'                                                   
/*                                                             
//SORTIN   DD *                                                 
A                                                               
/*                                                             
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                                 
  SORT FIELDS=COPY                                             
  INREC IFTHEN=(WHEN=INIT,BUILD=(DATE2,C'01',X,DATE2-1,C'01')),
  IFTHEN=(WHEN=INIT,                                           
  OVERLAY=(20:1,8))                                             
/*                                                             
**************************** Bottom of Data ********************


********************************* TOP OF DATA ******
20100701 20100601  20100701                         
******************************** BOTTOM OF DATA ****


Can you please advise on the above error I get ?

Thanks

Re: USe system rundate and find last months' end dates

PostPosted: Mon Jul 26, 2010 12:03 pm
by jasand
got to know from a previous topic discussed in the forum that, Syncsort does not support Y4T or TOJUL.

Can you suggest me how to achieve this then?

Thanks.

Re: USe system rundate and find last months' end dates

PostPosted: Thu Jul 29, 2010 12:13 am
by arcvns
jasand,

Syncsort has been providing DB2 query support from 1.2 version onwards. As you have the latest release now, ie. 1.3.2, you might want to try the below Syncsort job which works for your Syncsort version. You need to replace the DB2 subsystem name in the PARM parameter and the DB2 load library name in the STEPLIB DD with the respective names at your site.

//STEP1   EXEC PGM=SORT,PARM='DB2=XXXX'
//STEPLIB   DD DSN=XXXX.DSNLOAD,DISP=SHR
//SYMNAMES  DD *
PVALUE,C'XX'
//SORTDBIN  DD *
  SELECT (CURRENT DATE-DAY(CURRENT DATE) DAYS),
         (CURRENT DATE-DAY(CURRENT DATE) DAYS-1 MONTH)
  FROM SYSIBM.SYSDUMMY1
//SYSOUT    DD SYSOUT=*
//SORTOUT   DD SYSOUT=*
//SYSIN     DD *
  OPTION COPY
  INREC BUILD=(C'ABCDEFG',PVALUE,C'0000',
               6,2,9,2,3,2,16,2,19,2,13,2)
SORTOUT had
ABCDEFGXX0000063010053110

Re: USe system rundate and find last months' end dates

PostPosted: Mon Oct 18, 2010 8:21 am
by sandipkarde
arcvns wrote:jasand,

Syncsort has been providing DB2 query support from 1.2 version onwards. As you have the latest release now, ie. 1.3.2, you might want to try the below Syncsort job which works for your Syncsort version. You need to replace the DB2 subsystem name in the PARM parameter and the DB2 load library name in the STEPLIB DD with the respective names at your site.

//STEP1   EXEC PGM=SORT,PARM='DB2=XXXX'
//STEPLIB   DD DSN=XXXX.DSNLOAD,DISP=SHR
//SYMNAMES  DD *
PVALUE,C'XX'
//SORTDBIN  DD *
  SELECT (CURRENT DATE-DAY(CURRENT DATE) DAYS),
         (CURRENT DATE-DAY(CURRENT DATE) DAYS-1 MONTH)
  FROM SYSIBM.SYSDUMMY1
//SYSOUT    DD SYSOUT=*
//SORTOUT   DD SYSOUT=*
//SYSIN     DD *
  OPTION COPY
  INREC BUILD=(C'ABCDEFG',PVALUE,C'0000',
               6,2,9,2,3,2,16,2,19,2,13,2)
SORTOUT had
ABCDEFGXX0000063010053110
Hello,

Can we get number of days between to dates using SYNCSORT 1.3.2.1R?
ex:
date1: 01-12-2010 mmddyyyy
date2: 02-08-2010 mmddyyyy
result: 27 days

Re: USe system rundate and find last months' end dates

PostPosted: Mon Oct 18, 2010 8:50 am
by dick scherrer
Hello,

Suggest you consider getting the number of days using sql. . .

As far as i know, the sort products do not support date arithmetic.

Re: USe system rundate and find last months' end dates

PostPosted: Mon Oct 18, 2010 8:27 pm
by Alissa Margulies
sandipkarde wrote:Can we get number of days between to dates using SYNCSORT 1.3.2.1R?
ex:
date1: 01-12-2010 mmddyyyy
date2: 02-08-2010 mmddyyyy
result: 27 days

Please contact me offline at alissa.margulies@syncsort.com and I would be happy to further assist you.

Regards,