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
 

 

How to select no. of year from current date in REORG utility

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 132
Location: India

PostPosted: Fri Sep 03, 2010 3:46 pm    Post subject: How to select no. of year from current date in REORG utility
Reply with quote

Hi,

I want to run REORG utility for one of the tables.

Below is the jcl that I am using:

Code:

//REORG01   EXEC PGM=DSNUTILB,PARM=(DB2F,'ABCJ904')                   
//ABNLIGNR  DD DUMMY                                                   
//DSSPRINT  DD SYSOUT=*                                               
//SYSPRINT  DD SYSOUT=*                                               
//SYSUDUMP  DD SYSOUT=Z                                               
//UTPRINT   DD SYSOUT=*                                               
//SYSOUT    DD SYSOUT=*                                               
//SORTOUT   DD SPACE=(CYL,(300,300),RLSE),VOLUME=(,,,59)               
//SYSUT1    DD SPACE=(CYL,(300,300),RLSE),VOLUME=(,,,59)               
//SYSREC    DD SPACE=(CYL,(300,300),RLSE),VOLUME=(,,,59)               
//SYSPUNCH  DD SPACE=(CYL,(2,1),RLSE)                                 
//SYSDISC   DD DSN=OPERA12.DISCARD(+1),                       
//             DISP=(NEW,CATLG,CATLG),UNIT=(VTAPE,,DEFER),             
//             VOLUME=(,,,59),                                         
//             DCB=(HBC.DSCB,BLKSIZE=32760,DEN=4)                     
//SLSCOPY   DD DSN=OPERA12.SSLS001(+1),               
//             DISP=(NEW,CATLG,CATLG),UNIT=(VTAPE,,DEFER),             
//             VOLUME=(,,,59),                                         
//             DCB=(HBC.DSCB,BLKSIZE=32760,DEN=4)     
//SYSIN     DD *
   REORG TABLESPACE DSLS001F.SSLS001         LOG NO             
      COPYDDN (SLSCOPY)                                     
      DISCARD FROM TABLE DEVF.TSLS001_HST             
      WHEN (WK_CCYY <= YEARS(CURRENT DATE - 2 YEAR)                                 
      SORTDATA                                               
       SHRLEVEL REFERENCE                                   
/*


It is throwing MAXCC=08 which is shown below:
Code:

******************************** TOP OF DATA **********************************
 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = IMZJ904                         
 DSNUGTIS - PROCESSING SYSIN AS EBCDIC                                         
 DSNUGPRW -  REORG TABLESPACE DSLS001F.SSLS001         LOG NO                 
 DSNUGPRW -       COPYDDN (SLSCOPY)                                           
 DSNUGPRW -       DISCARD FROM TABLE DEVF.TSLS001_HST                   
 DSNUGPRW -       WHEN (WK_CCYY <= YEARS(CURRENT DATE - 2 YEAR))               
 DSNUGPRW - INVALID KEYWORD - CURRENT                                         
 DSNUGPRW -       SORTDATA                                                     
 DSNUGPRW -        SHRLEVEL REFERENCE                                         
 DSNUGBAC - UTILITY EXECUTION TERMINATED, HIGHEST RETURN CODE=8               
******************************* BOTTOM OF DATA ********************************


Here WK_CCYY is a column in the table TSLS001_HST which contains the YEAR only. so i want to select the year portion of (CURRENT DATE - 2).
Can anybody please help me?

PS: JCL works fine for WK-CCYY < CURRENT DATE - 2 YEAR
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 03, 2010 7:24 pm    Post subject:
Reply with quote

Hello,

Quote:
JCL works fine for WK-CCYY < CURRENT DATE - 2 YEAR
Then why was YEARS added to the statement. . . icon_confused.gif
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Sat Sep 04, 2010 3:40 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

Quote:
JCL works fine for WK-CCYY < CURRENT DATE - 2 YEAR
Then why was YEARS added to the statement. . . icon_confused.gif

As Dick hinted, full SQL syntax is not permissible in control statements for the utility.

Check the utility manual. You will probably have to have a column with DATE format in your table, in order to use the quoted syntax.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Sep 06, 2010 12:03 pm    Post subject:
Reply with quote

1) as stated : I don't think functions are allowed in DISCARD option
2) There isn't a function called years().
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts TAPE VERIFY utility?? MrExtraordinare JCL & VSAM 3 Fri Dec 09, 2016 10:57 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts Wishing everyone a very Happy Diwali ... Rohit Umarjikar General Talk & Fun Stuff 1 Sat Oct 29, 2016 1:54 am


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