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 DB2 - TERMINATE utility vasanthz DB2 5 Mon May 15, 2017 11:49 pm
No new posts HLIST Utility In Batch Virendra Shambharkar TSO/ISPF 4 Fri Apr 07, 2017 3:38 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


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