USe system rundate and find last months' end dates



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

USe system rundate and find last months' end dates

Postby jasand » Thu Jul 22, 2010 3:28 pm

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.
jasand
 
Posts: 11
Joined: Fri Mar 12, 2010 5:41 pm
Has thanked: 1 time
Been thanked: 0 time

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

Postby jasand » Thu Jul 22, 2010 3:30 pm

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)
jasand
 
Posts: 11
Joined: Fri Mar 12, 2010 5:41 pm
Has thanked: 1 time
Been thanked: 0 time

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

Postby skolusu » Thu Jul 22, 2010 10:27 pm

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
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

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

Postby jasand » Mon Jul 26, 2010 11:38 am

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
jasand
 
Posts: 11
Joined: Fri Mar 12, 2010 5:41 pm
Has thanked: 1 time
Been thanked: 0 time

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

Postby jasand » Mon Jul 26, 2010 12:03 pm

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.
jasand
 
Posts: 11
Joined: Fri Mar 12, 2010 5:41 pm
Has thanked: 1 time
Been thanked: 0 time

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

Postby arcvns » Thu Jul 29, 2010 12:13 am

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
Arun
User avatar
arcvns
 
Posts: 55
Joined: Sat Feb 28, 2009 12:36 am
Location: India
Has thanked: 0 time
Been thanked: 0 time

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

Postby sandipkarde » Mon Oct 18, 2010 8:21 am

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
sandipkarde
 
Posts: 1
Joined: Mon Oct 18, 2010 8:04 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby dick scherrer » Mon Oct 18, 2010 8:50 am

Hello,

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

As far as i know, the sort products do not support date arithmetic.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby Alissa Margulies » Mon Oct 18, 2010 8:27 pm

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,
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 3 times


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post