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
 

 

can UNLOAD utility use the WHERE clause??

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

New User


Joined: 17 Nov 2006
Posts: 6

PostPosted: Wed Jun 06, 2007 11:20 pm    Post subject: can UNLOAD utility use the WHERE clause??
Reply with quote

Hi,

I have a table which has to be unloaded, only those rows which are not older than 180 days from current date.

Ex:
select * from abcd.xyx_pqr where sct_evt_dt + 180 days
> current_date

the difficulty here is that i am not supposed to use IKJEFT1A utility but have to use DSNUTILB pgm to download the data.

So wanted to know if there is any possibility of
unloading selected rows which match the query above.

Though i have tried the WHEN clause , it doesn't allow any arithmetic operators.

Regards,
Sana.

P.S: No SQL select statement has to be used.
Back to top
View user's profile Send private message

PeD

Active User


Joined: 26 Nov 2005
Posts: 456
Location: Belgium

PostPosted: Thu Jun 07, 2007 12:02 am    Post subject:
Reply with quote

maybe something like
Code:
UNLOAD TABLESPACE abcd.xyx_pqr
 FROM TABLE abcd.xyx_pqr
 HEADER NONE
 WHEN ( sct_evt_dt + 180 days )
 SHRLEVEL CHANGE   


Not tested for arithmetic operation. Have a try.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Thu Jun 07, 2007 2:26 am    Post subject: Re: can UNLOAD utility use the WHERE clause??
Reply with quote

According to the manual, it is possible. If it doesn't work, you may post your complete UNLOAD statement and somebody will suggest something here.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jun 07, 2007 8:24 am    Post subject:
Reply with quote

See unload is nothing but bulk select.Normally when you run a spufi you have some limits to that you cant fetch all the ROWS for your query.That is why we opt for running a spufi in batch.Just like your normal unload give the datasets sysrec and syspunch.In SYSIN you can give your statement
like

Code:


select empid,emdno,salary from employee where salary>6000;



This will work as the same unload.
Hope this helps.

Thanks,
Prem
Back to top
View user's profile Send private message
peeterparker

New User


Joined: 17 Nov 2006
Posts: 6

PostPosted: Thu Jun 07, 2007 12:21 pm    Post subject: let me post the UNLOAD statement and the error.
Reply with quote

UNLOAD DATA PUNCHDDN SYSPUNCH UNLDNN SYSRECCOO
FROM TABLE ABC.PR_XYZ
WHEN ( STRT_DT + 2 DAYS > CURRENT_DATE )

The error is

INVALID KEYWORD - + in the WHEN line.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jun 07, 2007 1:57 pm    Post subject:
Reply with quote

can you please copy paste your code becasue I find your code to contain
some errors.
Please use the coding conventions used in this forum and then paste your code.


Thanks,
Prem
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 962
Location: Richmond, Virginia

PostPosted: Thu Jun 07, 2007 6:17 pm    Post subject:
Reply with quote

The UNLOAD WHEN clause allows "between" as well as >, >=, <, <=.

Can you use several compares with AND or OR?

The compare can be with a labeled-duration-expression, which includes the various date and timestamp components. See UNLOAD syntax documentation for the details.

Does this help?
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Fri Jun 08, 2007 6:59 am    Post subject: Re: can UNLOAD utility use the WHERE clause??
Reply with quote

This should work,

Code:
UNLOAD DATA PUNCHDDN SYSPUNCH UNLDNN SYSRECCOO
FROM TABLE ABC.PR_XYZ
WHEN ( STRT_DT > (CURRENT_DATE - 2 DAYS) )
Back to top
View user's profile Send private message
peeterparker

New User


Joined: 17 Nov 2006
Posts: 6

PostPosted: Sun Jun 10, 2007 8:37 pm    Post subject: t worked
Reply with quote

Code:
UNLOAD DATA PUNCHDDN SYSPUNCH UNLDNN SYSRECCOO
FROM TABLE ABC.PR_XYZ
WHEN ( STRT_DT > (CURRENT_DATE - 2 DAYS) )



thnax a zillion!!!
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Mon Jun 11, 2007 12:55 pm    Post subject: Re: can UNLOAD utility use the WHERE clause??
Reply with quote

You are welcome! icon_smile.gif
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 Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts Any docs / links for isub- cobol comp... SRICOBSAS COBOL Programming 4 Fri Aug 26, 2016 2:35 pm
No new posts DB2 Unload format options Susanta DB2 2 Fri Aug 12, 2016 5:42 pm
No new posts Comparex utility to Syncsort conversion mistah kurtz SYNCSORT 11 Fri Jul 22, 2016 1:53 pm


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