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
 

 

Query on BMC unload utility

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

New User


Joined: 26 Jun 2011
Posts: 7
Location: India

PostPosted: Fri Sep 06, 2013 10:37 am    Post subject: Query on BMC unload utility
Reply with quote

I have below unload control card:

Code:
UNLOAD
 DIRECT AUTO
 SORTDEVT SYSDS
 SORTNUM   64   
 LIMIT 0
 SHRLEVEL  CHANGE
 CONSISTENT NO
 ON FAILURE UNLOAD TERMINATE UTILITY
 CNTLCARDS DB2LOAD
    SELECT A.COL1,
           A.COL2,
           (CASE WHEN DATE(COL3) = CURRENT DATE
                 THEN COL3
                 ELSE 0 END) AS COL3,
           B.COL4
           FROM TABLE1 A, TABLE 2 B
           WHERE B.COL1 = A.COL1
Now, the requirement is instead of using current date in the above query we need to pick up the date from a mainframe PS file. Can someone please guide me if can give a PS file as an input to the BMC utility, and what changes needs to be done in the above query. If you can give me a sample JCL and control card for the above scenario, that would be helpful

Thanks
Sandeep

Code'd
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2171
Location: @my desk

PostPosted: Fri Sep 06, 2013 11:16 am    Post subject:
Reply with quote

One way is to have your sort product read this PS file having date in it and build the unload card dynamically and use it
Back to top
View user's profile Send private message
sandy mf

New User


Joined: 26 Jun 2011
Posts: 7
Location: India

PostPosted: Fri Sep 06, 2013 11:25 am    Post subject: Reply to: Query on BMC unload utility
Reply with quote

Thanks Arun.

It would be helpful for me if you can post a sample JCL and Control card performing such activity
Back to top
View user's profile Send private message
sandy mf

New User


Joined: 26 Jun 2011
Posts: 7
Location: India

PostPosted: Fri Sep 06, 2013 6:44 pm    Post subject: Reply to: Query on BMC unload utility
Reply with quote

Can't this be acheived by BMC unload utility alone
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Fri Sep 06, 2013 7:42 pm    Post subject: Re: Reply to: Query on BMC unload utility
Reply with quote

sandy mf wrote:
Can't this be acheived by BMC unload utility alone

My Unload+ manual does not show any options for reading data sets and incorporating that data into the control cards. To the left, using *Sort (or Rexx; Arun and I have different skill sets) to generate the required cards is so trivial that there can be no rational reason not to do it.
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 685
Location: Earth

PostPosted: Sat Sep 07, 2013 12:48 am    Post subject:
Reply with quote

Is it possible for your date input be placed in a control card?
For example:
Code:
           (CASE WHEN DATE(COL3) = 'yyyymmdd'

If so, you can keep upper part of query in one card, date file/control card as shown above and bottom part of your query in another control card.
Provide all three concatenated as input to your unload step.

Let us know if you got the idea.
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


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