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 regarding the DB2 utility DSNUTILB

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

New User


Joined: 18 Nov 2010
Posts: 7
Location: India

PostPosted: Mon Nov 22, 2010 4:33 pm    Post subject: Query regarding the DB2 utility DSNUTILB
Reply with quote

Hi,

I have a query regarding the DB2 utility DSNUTILB

Code:
// IF P01.RC < 5 THEN                                         
//P02     EXEC PGM=DSNUTILB,                                   
//             PARM='DBAF,Jobname.REORG'                     
//STEPLIB   DD DSN=DB2@.AAA,DISP=SHR               
//SYSPRINT  DD SYSOUT=*                                       
//UTPRINT   DD SYSOUT=*                                       
//SYSUDUMP  DD SYSOUT=*                                       
//SYSTEMPL  DD DSN=WOK.DBAF.DBAA.LCTL(SNJ400A2),DISP=SHR       
//SYSIN     DD DSN=WOK.DBAF.DBAA.LCTL(SNJ400A3),DISP=SHR       
//           DD DSN=WOK.UNLOAD.TSN105.NKY2,DISP=SHR           
//           DD *                                             
                  )                                                                                   


WOK.DBAF.DBAA.LCTL(SNJ400A3) ----> conatins the SQL query

Code:
OPTIONS TEMPLATEDD(SYSTEMPL)                   
                                               
REORG TABLESPACE SN01A1A.A100A                 
         LOG NO                               
         SORTDATA                             
         COPYDDN(TCOPYD)                       
         SHRLEVEL REFERENCE                   
         UNLOAD CONTINUE                       
         WORKDDN(TSYUTD,TSOUTD)               
         PUNCHDDN TPUNCH                       
         SORTKEYS                             
         SORTDEVT DISK                         
         SORTNUM 160                           
         STATISTICS                           
           INDEX ALL                           
         REPORT NO                             
           UPDATE ALL                         
         UNLDDN    TSRECD                     
         DISCARDDN TDISC                       
         DISCARD                               
           FROM TABLE OA1A.TABLE105 WHEN       
               ANYKEY IN ( 


WOK.UNLOAD.TSN105.NKY2 --> Contains the keys for the IN condition

With the help of the above query we can extract all the matching records i.e SOLCODE = 0

My requirement is if the SOLCODE is 100 then the records mentioned in the file (WOK.UNLOAD.TSN105.NKY2) need to be displayed.

eg : Contents of file WOK.UNLOAD.TSN105.NKY2

Code:
AAAA
BBBB
CCCC

If AAA exist in the table TABLE105 then SQLCODE = 0.----> Need not extract the record.

CCC - Record doesnot exist in TABLE105 then extract the record (SQLCODE = 100).

PS: For this Not equal to condition will not work.

Please let me know if you need any other infoarmation.

Edited: Please use BBcode when You post some code/error, that's rather readable, Thanks... Anuj
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Mon Nov 22, 2010 5:29 pm    Post subject:
Reply with quote

Any particular reason that you decided to post a DB2 question in the JCL forum ???
Back to top
View user's profile Send private message
nimesh.chakravarty

New User


Joined: 18 Nov 2010
Posts: 7
Location: India

PostPosted: Mon Nov 22, 2010 7:02 pm    Post subject:
Reply with quote

Sorry for that.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Nov 22, 2010 9:49 pm    Post subject:
Reply with quote

nimesh - the way you have described the question, I'm kinda confused to understand if you are looking for a DB2 solution or want to use some trick in JCL (using some utility or cond parameters).


Please explain this below text again
Quote:
If AAA exist in the table TABLE105 then SQLCODE = 0.----> Need not extract the record.

CCC - Record doesnot exist in TABLE105 then extract the record (SQLCODE = 100).
?

I'll make a guess here, however, and will try to use your words - If SQLCODE is 0, that means your output frile from the step executing DSNUTILB will have the data for all <records> which are found, let's call this as file1. You already have a file "WOK.UNLOAD.TSN105.NKY2 " which has all the fields on which you query the table, file2. So basically, now you need to compare these two files, is not it?
Back to top
View user's profile Send private message
nimesh.chakravarty

New User


Joined: 18 Nov 2010
Posts: 7
Location: India

PostPosted: Tue Nov 23, 2010 12:54 am    Post subject:
Reply with quote

Yes you are right, But incase of compare I need to introduce another step in my JCL. This is the last option if i dont find the solution

My requirement is

In file WOK.UNLOAD.TSN105.NKY2 we have records which will be used in the where clause of the query.

If the record is not present in the table TABLE105( SOLCODE =100) then need the record written in output file (file1)

Isnt there any method where I can check the SQLCODE within the JCL i.e SOLCODE =100 the write in file1

Please let me know if more information is required.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Nov 23, 2010 10:45 am    Post subject:
Reply with quote

Nimesh,

Hope this helps, Can SQLCODE 100 be tracked using DSNtep2 utility?



Thanks,
Sushanth
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts TAPE VERIFY utility?? MrExtraordinare JCL & VSAM 3 Fri Dec 09, 2016 10:57 pm


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