How to exclude SQL in DSNTEP4 SYSPRINT

[t1pers]

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

[pranav283]

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

[t1pers]

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:

Code: Select all

//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

[pranav283]

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

[t1pers]

Thanks, I will try and will share what was the result.

[t1pers]

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

[enrico-sorichetti]

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 )