IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

JCL sort card - get first day and last day of month


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Minimochi

New User


Joined: 25 Nov 2023
Posts: 3
Location: India

PostPosted: Thu Dec 28, 2023 6:24 pm
Reply with quote

Hi All,

I have a jcl which unloads table data to a file and the unload card has simple query as - SELECT * from <table_name>
This table has a column name as DATE which is in format yyyy-mm-dd ex.2020-05-31

The ask from my boss was - Can we get data from 1st day of the month to the last day of the month. I gave them below where condition -

WHERE DATE BETWEEN (CURRENT_DATE - (DAY(CURRENT_DATE)-1)DAYS)
AND LAST_DAY(CURRENT_DATE)

This worked good. But this unload job is month end job and sometimes tend to run late which means the current date being 1st of month which will mess up my above WHERE condition

So, my simplest idea was to do replace CURRENT_DATE with (CURRENT_DATE -2 )

But my boss is asking if we can create new step and filter out the DATE field from the output file and create a new file with only date having a month's worth data.

I dont know how i can achieve filtering the date using sort. can you guys help icon_smile.gif
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2021
Location: USA

PostPosted: Thu Dec 28, 2023 7:52 pm
Reply with quote

Please, start doing something by yourself. Then you can ask, what and why goes wrong?

Where is any sample of the code you have tried so far?

So far is not clear: what is your final goal? Where is your DB2, where is your SORT, where is your JCL, and where is your VSAM?

P.S.
This in not "JCL & VSAM"
This is "DB2" and "DFSORT/SYNCSORT"
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2021
Location: USA

PostPosted: Thu Dec 28, 2023 8:31 pm
Reply with quote

RTFM:
Quote:
FIRST_DAY Returns a date or timestamp that represents the first day of the month of the argument.

LAST_DAY Returns a datetime value that represents the last day of the month of the argument.
Back to top
View user's profile Send private message
Minimochi

New User


Joined: 25 Nov 2023
Posts: 3
Location: India

PostPosted: Thu Dec 28, 2023 10:46 pm
Reply with quote

Please, start doing something by yourself-> I got the DB2 solution with my where clause condition which I have given in my og post and since you asked to read the f*king manual. I have already used the last day function in my WHERE clause which you shared

My question is simply to understand - is there any DB2 BETWEEN and Last_date equivalent in jcl sort card

Also, THIS IS A JCL QUESTION not "DB2". I explained the DB2 part because I knew there would be someone who would ask if you tried anything?

Also, I did tried to search JCL sort and I found DATE1,DATE4 in jcl sort to deal with date conditions but I couldn't found anything where I can get first and last date of month in jcl sort utility


Also, please do not use condescending tone. You can simply ignore my post if you want to rather than being rude
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2021
Location: USA

PostPosted: Thu Dec 28, 2023 11:34 pm
Reply with quote

Minimochi wrote:

My question is simply to understand - is there any DB2 BETWEEN and Last_date equivalent in jcl sort card


Yes, there is such equivalent.
It's described in details in manuals, and in Google search from Internet, as well as in search from this forum.

P.S.
There is no such thing as, JCL SORT CARD!!!!!
You can hardly find your solution unless you understand the difference between JCL language, and SORT utility control statements.
Back to top
View user's profile Send private message
Minimochi

New User


Joined: 25 Nov 2023
Posts: 3
Location: India

PostPosted: Thu Dec 28, 2023 11:51 pm
Reply with quote

You can hardly find your solution unless you understand the difference between JCL language, and SORT utility control statements. -> I already have a DB2 solution I was looking if we have anything similar in jcl(to learn and increase my knowledge)
English is not my first language so I wrote jcl sort card. Also I don't need validation from a boomer whose only answer to every problem is "google it" or "RTFM" why not copy past the manual link on the home page and why even have a forum to discuss!
I regret asking question should've asked in stack overflow. Atleast would'nt have encountered a karen

Sayonara!
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2021
Location: USA

PostPosted: Fri Dec 29, 2023 12:17 am
Reply with quote

Minimochi wrote:
You can hardly find your solution unless you understand the difference between JCL language, and SORT utility control statements. -> I already have a DB2 solution I was looking if we have anything similar in jcl(to learn and increase my knowledge)


I repeat already 4 or 5 times:

There is no any solution of this kind in JCL language!!!!!!! Neither a similar one, nor a different one.

You can use SORT utility, which has absolutely nothing to do with JCL language.

Quote:

OUTREC
Function Field Subparameters

ADDDAYS
ADDMONS
ADDYEARS
SUBDAYS
SUBMONS
SUBYEARS

NEXTDday
PREVDday
LASTDAYW
LASTDAYM
LASTDAYQ
LASTDAYY


Or, maybe, you unload data from DB2 by means of JCL language, too? icon_pai.gif icon_pray.gif
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2021
Location: USA

PostPosted: Fri Dec 29, 2023 2:23 am
Reply with quote

Since we are talking in different languages, and there is absolutely no chance to understand each other, here is a sort of New Year gift.

Code:
//FILTER   EXEC PGM=SORT                                             
//SYSOUT   DD  SYSOUT=*                                               
//*                                                                   
//SYMNAMES DD  *                                                     
INPUT_DATE,10,10,CH                                                   
BEFORE_YESTERDAY,21,8,CH                                             
FIRST_OF_MONTH,30,10,CH                                               
FIRST_DAY,38,2,CH                                                     
LAST_OF_MONTH,41,10,CH                                               
//*                                                                   
//*-+----1----+----2----+----3----+----4----+----5----+----6----+----7
//SORTIN   DD  *    XXXXXXXX YYYY-YY-YY ZZZZ-ZZ-ZZ                   
         2023-11-01                                                   
         2023-11-05                                                   
         2023-11-10                                                   
         2023-11-15                                                   
         2023-11-20                                                   
         2023-11-25                                                   
         2023-11-30                                                   
         2023-12-01                                                   
         2023-12-05                                                   
         2023-12-10                                                   
         2023-12-15                                                   
         2023-12-20                                                   
         2023-12-25                                                   
         2023-12-30                                                   
         2024-01-01                                                   
         2024-01-05                                                   
         2024-01-10                                                   
         2024-01-15                                                   
         2024-01-20                                                   
         2024-01-25                                                   
         2024-01-30                                                   
//*-+----1----+----2----+----3----+----4----+----5----+----6----+----7
//SELECTED DD  SYSOUT=*                                               
//REJECTED DD  SYSOUT=*                                               
//*                                                                   
//SYSIN    DD  *                                                     
 INREC OVERLAY=(BEFORE_YESTERDAY:&DATE1-2,                   
                   LAST_OF_MONTH:BEFORE_YESTERDAY,               
                                 Y4T,LASTDAYM,TOGREG=Y4T(-),     
                  FIRST_OF_MONTH:LAST_OF_MONTH,                 
                       FIRST_DAY:C'01')                             
 SORT FIELDS=COPY                                                     
 OUTFIL FNAMES=(SELECTED),                                           
        REMOVECC,                                                     
        INCLUDE=(INPUT_DATE,GE,FIRST_OF_MONTH,                       
             AND,INPUT_DATE,LE,LAST_OF_MONTH),                       
        HEADER1=('*** LIST OF ACCEPTED DATES BETWEEN ',               
                 FIRST_OF_MONTH,' AND ',LAST_OF_MONTH,       
                 ' ***',/),                                   
        BUILD=(INPUT_DATE,80:X),                             
        TRAILER1=(/,'*** TOTAL ACCEPTED DATES = ',           
                  COUNT=(M0),' ***')                         
 OUTFIL FNAMES=(REJECTED),                                   
        REMOVECC,                                             
        SAVE,                                                 
        HEADER1=('*** LIST OF REJECTED DATES OUT OF ',       
                 FIRST_OF_MONTH,' AND ',LAST_OF_MONTH,       
                 ' ***',/),                                   
        BUILD=(INPUT_DATE,80:X),                             
        TRAILER1=(/,'*** TOTAL REJECTED DATES = ',           
                  COUNT=(M0),' ***')                         
 END                                                         
//*                                                           


Code:
*** LIST OF ACCEPTED DATES BETWEEN 2023-12-01 AND 2023-12-31 ***     
                                                                     
2023-12-01                                                           
2023-12-05                                                           
2023-12-10                                                           
2023-12-15                                                           
2023-12-20                                                           
2023-12-25                                                           
2023-12-30                                                           
                                                                     
*** TOTAL ACCEPTED DATES =               7  ***                       


Code:
*** LIST OF REJECTED DATES OUT OF 2023-12-01 AND 2023-12-31 ***       
                                                                       
2023-11-01                                                             
2023-11-05                                                             
2023-11-10                                                             
2023-11-15                                                             
2023-11-20                                                             
2023-11-25                                                             
2023-11-30                                                             
2024-01-01                                                             
2024-01-05                                                             
2024-01-10                                                             
2024-01-15                                                             
2024-01-20                                                             
2024-01-25                                                             
2024-01-30                                                             
                                                                       
*** TOTAL REJECTED DATES =              14  ***                       
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA

PostPosted: Fri Dec 29, 2023 5:18 pm
Reply with quote

In such situations , you can never rely on current dates .. so always use a business date maintained by your system ..
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2021
Location: USA

PostPosted: Fri Dec 29, 2023 8:07 pm
Reply with quote

Rohit Umarjikar wrote:
In such situations , you can never rely on current dates .. so always use a business date maintained by your system ..


Yes, but (depending on business requirements) it can be also assumed that the job running from 1st to Nth of each month should generate the previous month report, while running after Nth of the month generates the current month report.

P.S.
None of the issues discussed in this thread is related to "JCL & VSAM", despite TS sticktoitiveness.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts Need to set RC4 through JCL SORT DFSORT/ICETOOL 5
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Populate last day of the Month in MMD... SYNCSORT 2
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
No new posts how to calculate SUM value for VB fil... DFSORT/ICETOOL 1
Search our Forums:

Back to Top