How to exclude SQL in DSNTEP4 SYSPRINT



IBM's flagship relational database management system

How to exclude SQL in DSNTEP4 SYSPRINT

Postby t1pers » Fri Nov 07, 2014 3:17 pm

Hi everyone,

I have a basic DSNTEP4 job which is querying different tables to extract data. I need to exclude the SQL in SYSPRINT and have only the comments and the extracted data. I checked the PARMS that can be supplied to DSNTEP4 but I didn't find anything that can do this for me. Does anyone have an idea or suggestion how can I manipulate the output of the utility?

Thanks
t1pers
 
Posts: 8
Joined: Mon Feb 11, 2013 1:02 am
Has thanked: 1 time
Been thanked: 0 time

Re: How to exclude SQL in DSNTEP4 SYSPRINT

Postby pranav283 » Sun Nov 09, 2014 3:32 am

Hi t1pers,

I need to exclude the SQL in SYSPRINT and have only the comments and the extracted data


What you mean by this statement is that you do not want the SQL query given in our SYSIN (either instream or catalogued) to be shown in your job log for SYSPRINT, Am I right ?

Also, if you can please mention the job step, users may be able to respond with a definite answer !!

Thanks,
Pranav
pranav283
 
Posts: 47
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 2 times
Been thanked: 0 time

Re: How to exclude SQL in DSNTEP4 SYSPRINT

Postby t1pers » Mon Nov 10, 2014 1:12 pm

Thanks for your attention. You are right, I want to remove the SQL queries given in our SYSIN from showing in SYSPRINT. So the final result should be only the statements that will come from the database. Here you can see the JCL of the job:
//STEPA EXEC PGM=IKJEFT01,DYNAMNBR=20                                   
//SYSTSPRT DD  SYSOUT=*                                                 
//SYSTSIN  DD  *                                                       
  DSN SYSTEM(DB9A)                                                     
  RUN  PROGRAM(DSNTEP4) PLAN(DSNTP491) -                               
          PARMS('ALIGN(LHS)') -                                           
          LIB('DB9A.RUNLIB.LOAD')                                         
//SYSPRINT DD  SYSOUT=*                                                 
//SYSOUT   DD  SYSOUT=*                                                                                               
//SYSIN    DD  DSN=USER.SYSIN.IRRDBU00,DISP=SHR       


Thanks
t1pers
 
Posts: 8
Joined: Mon Feb 11, 2013 1:02 am
Has thanked: 1 time
Been thanked: 0 time

Re: How to exclude SQL in DSNTEP4 SYSPRINT

Postby pranav283 » Mon Nov 10, 2014 4:14 pm

Hi,

I am currently not sure how to achieve this through JCL.

By observing your DSNTEP4 parameter - PARMS(LHS), I guess you want it to be left-justified. So does that mean you want the o/p in SYSPRINT for reporting and/or documenting purposes ?

If so, then you have to code a dataset in your SYSPRINT as:
//SYSPRINT DD DSN=DOC.SYSPRINT.OUTPUT and then add a new IDCAMS step to exclude the query from your dataset :

//STEP020 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//INDD DD DSN=DOC.SYSPRINT.OUTPUT, DISP=SHR
//OUTDD DD DSN=DOC.SYSPRINT.OUTPUT.REPORT,DISP=SHR
//SYSIN DD *
REPRO INFILE(INDD) OUTFILE(OUTDD) SKIP(N)
/*

*where N = no. of statements in your SYSIN (USER.SYSIN.IRRDBU00)

Thanks,
Pranav
pranav283
 
Posts: 47
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 2 times
Been thanked: 0 time

Re: How to exclude SQL in DSNTEP4 SYSPRINT

Postby t1pers » Mon Nov 10, 2014 5:14 pm

Thanks, I will try and will share what was the result.
t1pers
 
Posts: 8
Joined: Mon Feb 11, 2013 1:02 am
Has thanked: 1 time
Been thanked: 0 time

Re: How to exclude SQL in DSNTEP4 SYSPRINT

Postby t1pers » Mon Nov 10, 2014 6:14 pm

Hi,

Unfortunately this is not working. The reason is that the output in SYSPRINT is mixed(it is not first all select queries from SYSIN and then all results from DB2):
-select query
-result from DB2
-select query
-result from DB2
.
.
.

If I use SKIP and COUNT parms of IDCAMS I can only restrict to start from the record X and copy the records till line Y.
Any other suggestion?

Thanks
t1pers
 
Posts: 8
Joined: Mon Feb 11, 2013 1:02 am
Has thanked: 1 time
Been thanked: 0 time

Re: How to exclude SQL in DSNTEP4 SYSPRINT

Postby enrico-sorichetti » Sun Nov 16, 2014 4:27 am

the DSNTEPx snippets are general use query tools
( usually used for prototyping and debugging )

they are written in PL/1 and the source is available in SDSDSAMP
and You are free to modify them according to your needs

if You do not have the PL/1 compiler here is a link that You might find useful

http://www.idug.org/p/cm/ld/fid=89
which points to
CSNTEP2 ( cobol clone of dsntep2)
RUNSQL - advanced diagnostics and tracing for DSNREXX

since CSNTEP2 is written in COBOL Your organisation should have the skills to modify it
to satisfy Your requirements ( specific formatting in Your case )
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort

These users thanked the author enrico-sorichetti for the post (total 2):
t1pers (Mon Nov 17, 2014 12:48 pm) • pranav283 (Sun Nov 16, 2014 5:06 am)
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post