|
View previous topic :: View next topic
|
| Author |
Message |
nimesh.chakravarty
New User
Joined: 18 Nov 2010
Posts: 7
Location: India
|
|
|
|
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
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 |
|
 |
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| Any particular reason that you decided to post a DB2 question in the JCL forum ??? |
|
| Back to top |
|
|
nimesh.chakravarty
New User
Joined: 18 Nov 2010
Posts: 7
Location: India
|
|
|
|
| Sorry for that. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
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 |
|
|
nimesh.chakravarty
New User
Joined: 18 Nov 2010
Posts: 7
Location: India
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
| Back to top |
|
|
|
|
