Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?

Peter Farley, III

Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
Hi All,

First, my apologies to the IDUG Support people - I accidentally sent this email to them instead of to this list. I ask IDUG to please ignore that email.

I have a business need to run program-generated SELECT inquiries that may have multiple thousands of values for a WHERE clause (WHERE variable_name IN ('value1', 'value2', . . . . )).

I have RTFM for techniques to run the SPUFI program supplied by DB2 in a batch job. I have searched the archives of this list but I did not find anything referencing SPUFI at all.

If it matters we are DB2 V11, z/OS 2.2 here. We don't use DSNTEP2 for "batch SPUFI" because of the awful "paged" output format and no PL/1 compiler to modify the source to suit our needs.

I am aware of the existence of CSNTEP2, a COBOL version of DSNTEP2, but it has SELECT size limitations (32760 maximum size of a single VARCHAR variable).

I see in the DB2 manuals that there is a Java command line program to invoke a Java equivalent of SPUFI, "java com.ibm.db2.clp.db2", but I have some confusion about how to run this from a Rexx EXEC and get the connection to the proper DB2 database.

Our "normal" way to run a DB2 program like DSNTEP2 uses IKJEFT01 with SYSTSIN similar to this:

DSN SYSTEM(DB2X)
RUN PROGRAM(DSNTEP2) PLAN(PLANNAME) LIB('HLQ.LOADLIB') END

Our batch COBOL DB2 programs use a CAF connection to DB2 via a custom exit so the batch programs do not deal with any CONNECT process. As a result I am somewhat in the dark about how to code the CONNECT process, or whether I need to use some other way to connect the Rexx or the Java program to the correct DB2.

Any RTFM pointers you can provide to examples of exactly how to use the Java command line processor or the actual SPUFI program supplied by DB2 in a batch Rexx program would be greatly appreciated.

Peter




This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and delete the message and any attachments from your system.

Philip Sevetson

Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Farley, III)
Peter,

Why are you using "IN" syntax? Wouldn't it be easier to INSERT all of the values you want in a temporary table (or a not-completely-temporary table) and then do an Inner Join against the table which you want results from? (This also has the advantage of using multiple shorter individual statements...)

-phil (sevetson)

From: Farley, Peter x23353 [mailto:[login to unmask email]
Sent: Thursday, June 06, 2019 2:35 PM
To: [login to unmask email]
Subject: [DB2-L] - Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?

Hi All,

First, my apologies to the IDUG Support people - I accidentally sent this email to them instead of to this list. I ask IDUG to please ignore that email.

I have a business need to run program-generated SELECT inquiries that may have multiple thousands of values for a WHERE clause (WHERE variable_name IN ('value1', 'value2', . . . . )).

I have RTFM for techniques to run the SPUFI program supplied by DB2 in a batch job. I have searched the archives of this list but I did not find anything referencing SPUFI at all.

If it matters we are DB2 V11, z/OS 2.2 here. We don't use DSNTEP2 for "batch SPUFI" because of the awful "paged" output format and no PL/1 compiler to modify the source to suit our needs.

I am aware of the existence of CSNTEP2, a COBOL version of DSNTEP2, but it has SELECT size limitations (32760 maximum size of a single VARCHAR variable).

I see in the DB2 manuals that there is a Java command line program to invoke a Java equivalent of SPUFI, "java com.ibm.db2.clp.db2", but I have some confusion about how to run this from a Rexx EXEC and get the connection to the proper DB2 database.

Our "normal" way to run a DB2 program like DSNTEP2 uses IKJEFT01 with SYSTSIN similar to this:

DSN SYSTEM(DB2X)
RUN PROGRAM(DSNTEP2) PLAN(PLANNAME) LIB('HLQ.LOADLIB') END

Our batch COBOL DB2 programs use a CAF connection to DB2 via a custom exit so the batch programs do not deal with any CONNECT process. As a result I am somewhat in the dark about how to code the CONNECT process, or whether I need to use some other way to connect the Rexx or the Java program to the correct DB2.

Any RTFM pointers you can provide to examples of exactly how to use the Java command line processor or the actual SPUFI program supplied by DB2 in a batch Rexx program would be greatly appreciated.

Peter




This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and delete the message and any attachments from your system.
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Peter Farley, III

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Philip Sevetson)

Phil,

The access has to be strictly READ-ONLY as the process will be going against production DB2 tables which I may not change in any way, so creating a temporary table is not possible.  Getting a new table created by DBA's just for these inquiries is bureaucratically complicated and lengthy, and the inquiries may change rapidly over a relatively short time span, so pure SELECT  and FOR FETCH ONLY is the only path I see forward.

Peter

(Replying via the web interface as I am set up with only daily digests via email).
 
In Reply to Philip Sevetson:

Peter,

Why are you using "IN" syntax? Wouldn't it be easier to INSERT all of the values you want in a temporary table (or a not-completely-temporary table) and then do an Inner Join against the table which you want results from? (This also has the advantage of using multiple shorter individual statements...)

-phil (sevetson)

From: Farley, Peter x23353 [mailto:[login to unmask email]
Sent: Thursday, June 06, 2019 2:35 PM
To: [login to unmask email]
Subject: [DB2-L] - Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?

Hi All,

First, my apologies to the IDUG Support people - I accidentally sent this email to them instead of to this list. I ask IDUG to please ignore that email.

I have a business need to run program-generated SELECT inquiries that may have multiple thousands of values for a WHERE clause (WHERE variable_name IN ('value1', 'value2', . . . . )).

I have RTFM for techniques to run the SPUFI program supplied by DB2 in a batch job. I have searched the archives of this list but I did not find anything referencing SPUFI at all.

If it matters we are DB2 V11, z/OS 2.2 here. We don't use DSNTEP2 for "batch SPUFI" because of the awful "paged" output format and no PL/1 compiler to modify the source to suit our needs.

I am aware of the existence of CSNTEP2, a COBOL version of DSNTEP2, but it has SELECT size limitations (32760 maximum size of a single VARCHAR variable).

I see in the DB2 manuals that there is a Java command line program to invoke a Java equivalent of SPUFI, "java com.ibm.db2.clp.db2", but I have some confusion about how to run this from a Rexx EXEC and get the connection to the proper DB2 database.

Our "normal" way to run a DB2 program like DSNTEP2 uses IKJEFT01 with SYSTSIN similar to this:

DSN SYSTEM(DB2X)
RUN PROGRAM(DSNTEP2) PLAN(PLANNAME) LIB('HLQ.LOADLIB') END

Our batch COBOL DB2 programs use a CAF connection to DB2 via a custom exit so the batch programs do not deal with any CONNECT process. As a result I am somewhat in the dark about how to code the CONNECT process, or whether I need to use some other way to connect the Rexx or the Java program to the correct DB2.

Any RTFM pointers you can provide to examples of exactly how to use the Java command line processor or the actual SPUFI program supplied by DB2 in a batch Rexx program would be greatly appreciated.

Peter

Philip Sevetson

Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Farley, III)
Peter,

I would only note that a Declared Temporary Table is not in any way a cataloged, logged, or persistent object – it’s got only the duration of a unit of work, somewhat like a &&file in JCL. If they won’t let you do _that_, then it’s true, you don’t have any good choices.

-phil (sevetson)

From: Peter Farley, III [mailto:[login to unmask email]
Sent: Thursday, June 06, 2019 3:22 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?


Phil,

The access has to be strictly READ-ONLY as the process will be going against production DB2 tables which I may not change in any way, so creating a temporary table is not possible. Getting a new table created by DBA's just for these inquiries is bureaucratically complicated and lengthy, and the inquiries may change rapidly over a relatively short time span, so pure SELECT and FOR FETCH ONLY is the only path I see forward.

Peter

(Replying via the web interface as I am set up with only daily digests via email).

In Reply to Philip Sevetson:
Peter,

Why are you using "IN" syntax? Wouldn't it be easier to INSERT all of the values you want in a temporary table (or a not-completely-temporary table) and then do an Inner Join against the table which you want results from? (This also has the advantage of using multiple shorter individual statements...)

-phil (sevetson)

From: Farley, Peter x23353 [mailto:[login to unmask email]
Sent: Thursday, June 06, 2019 2:35 PM
To: [login to unmask email]
Subject: [DB2-L] - Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?

Hi All,

First, my apologies to the IDUG Support people - I accidentally sent this email to them instead of to this list. I ask IDUG to please ignore that email.

I have a business need to run program-generated SELECT inquiries that may have multiple thousands of values for a WHERE clause (WHERE variable_name IN ('value1', 'value2', . . . . )).

I have RTFM for techniques to run the SPUFI program supplied by DB2 in a batch job. I have searched the archives of this list but I did not find anything referencing SPUFI at all.

If it matters we are DB2 V11, z/OS 2.2 here. We don't use DSNTEP2 for "batch SPUFI" because of the awful "paged" output format and no PL/1 compiler to modify the source to suit our needs.

I am aware of the existence of CSNTEP2, a COBOL version of DSNTEP2, but it has SELECT size limitations (32760 maximum size of a single VARCHAR variable).

I see in the DB2 manuals that there is a Java command line program to invoke a Java equivalent of SPUFI, "java com.ibm.db2.clp.db2", but I have some confusion about how to run this from a Rexx EXEC and get the connection to the proper DB2 database.

Our "normal" way to run a DB2 program like DSNTEP2 uses IKJEFT01 with SYSTSIN similar to this:

DSN SYSTEM(DB2X)
RUN PROGRAM(DSNTEP2) PLAN(PLANNAME) LIB('HLQ.LOADLIB') END

Our batch COBOL DB2 programs use a CAF connection to DB2 via a custom exit so the batch programs do not deal with any CONNECT process. As a result I am somewhat in the dark about how to code the CONNECT process, or whether I need to use some other way to connect the Rexx or the Java program to the correct DB2.

Any RTFM pointers you can provide to examples of exactly how to use the Java command line processor or the actual SPUFI program supplied by DB2 in a batch Rexx program would be greatly appreciated.

Peter

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Peter Farley, III

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Philip Sevetson)

Phil,

 

I actually don't know if I am prevented from using one because I don't know enough SQL to know what a Declared Temporary Table looks like much less how to use it.  Could you provide a simplified example as an alternative to a SELECT like this:

 

SELECT VAR_NM FROM DB2X.TBLNAME WHERE VAR_NM IN ('A', 'B', 'C', … 'Z') FOR FETCH ONLY WITH UR;

 

Where VAR_NM is CHAR(1).  The business need here is to determine if any of the "IN" values are present in DB2X.TBLNAME and list all that are out to a sequential file from which they can be extracted via SORT or REXX script.  In the real application VAR_NM is larger than 1 character and there are millions of values of VAR_NM in DB2X.TBLNAME and multiple thousands of "IN" values generated by another process to be checked against the table.

 

Peter
 
In Reply to Philip Sevetson:

Peter,

I would only note that a Declared Temporary Table is not in any way a cataloged, logged, or persistent object – it’s got only the duration of a unit of work, somewhat like a &&file in JCL. If they won’t let you do _that_, then it’s true, you don’t have any good choices.

-phil (sevetson)

From: Peter Farley, III [mailto:[login to unmask email]
Sent: Thursday, June 06, 2019 3:22 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?


Phil,

The access has to be strictly READ-ONLY as the process will be going against production DB2 tables which I may not change in any way, so creating a temporary table is not possible. Getting a new table created by DBA's just for these inquiries is bureaucratically complicated and lengthy, and the inquiries may change rapidly over a relatively short time span, so pure SELECT and FOR FETCH ONLY is the only path I see forward.

Peter

(Replying via the web interface as I am set up with only daily digests via email).

In Reply to Philip Sevetson:
Peter,

Why are you using "IN" syntax? Wouldn't it be easier to INSERT all of the values you want in a temporary table (or a not-completely-temporary table) and then do an Inner Join against the table which you want results from? (This also has the advantage of using multiple shorter individual statements...)

-phil (sevetson)

From: Farley, Peter x23353 [mailto:[login to unmask email]
Sent: Thursday, June 06, 2019 2:35 PM
To: [login to unmask email]
Subject: [DB2-L] - Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?

Hi All,

First, my apologies to the IDUG Support people - I accidentally sent this email to them instead of to this list. I ask IDUG to please ignore that email.

I have a business need to run program-generated SELECT inquiries that may have multiple thousands of values for a WHERE clause (WHERE variable_name IN ('value1', 'value2', . . . . )).

I have RTFM for techniques to run the SPUFI program supplied by DB2 in a batch job. I have searched the archives of this list but I did not find anything referencing SPUFI at all.

If it matters we are DB2 V11, z/OS 2.2 here. We don't use DSNTEP2 for "batch SPUFI" because of the awful "paged" output format and no PL/1 compiler to modify the source to suit our needs.

I am aware of the existence of CSNTEP2, a COBOL version of DSNTEP2, but it has SELECT size limitations (32760 maximum size of a single VARCHAR variable).

I see in the DB2 manuals that there is a Java command line program to invoke a Java equivalent of SPUFI, "java com.ibm.db2.clp.db2", but I have some confusion about how to run this from a Rexx EXEC and get the connection to the proper DB2 database.

Our "normal" way to run a DB2 program like DSNTEP2 uses IKJEFT01 with SYSTSIN similar to this:

DSN SYSTEM(DB2X)
RUN PROGRAM(DSNTEP2) PLAN(PLANNAME) LIB('HLQ.LOADLIB') END

Our batch COBOL DB2 programs use a CAF connection to DB2 via a custom exit so the batch programs do not deal with any CONNECT process. As a result I am somewhat in the dark about how to code the CONNECT process, or whether I need to use some other way to connect the Rexx or the Java program to the correct DB2.

Any RTFM pointers you can provide to examples of exactly how to use the Java command line processor or the actual SPUFI program supplied by DB2 in a batch Rexx program would be greatly appreciated.

Peter

-----End Original Message-----

Horacio Villa

Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Farley, III)
What would be the length of the SQL?
You could generate a sequential file with all the IN values, then use
DSNTIAL concatenating the SELECT with the sequential file and then the
last part of the SQL, if it's length permits it.
Something like this:

//STEPxxx EXEC PGM=IKJEFT01,DYNAMNBR=nn
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB1x) PARMS('SQL')
END
/*
//SYSREC00 DD whatever file you want the output to go
//SYSIN DD *
SELECT VAR_NM FROM DB2X.TBLNAME WHERE VAR_NM IN
/*
// DD DISP=SHR,DSN=your.sequential.file
// DD *
FOR FETCH ONLY WITH UR;




Peter Farley, III

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Horacio Villa)

Horacio,

Thank you, that is an interesting idea that I didn't think of as a possible solution.  I will try it out and reply later with my results.

Peter
 
In Reply to Horacio Villa:

What would be the length of the SQL?
You could generate a sequential file with all the IN values, then use
DSNTIAL concatenating the SELECT with the sequential file and then the
last part of the SQL, if it's length permits it.
Something like this:

//STEPxxx EXEC PGM=IKJEFT01,DYNAMNBR=nn
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB1x) PARMS('SQL')
END
/*
//SYSREC00 DD whatever file you want the output to go
//SYSIN DD *
SELECT VAR_NM FROM DB2X.TBLNAME WHERE VAR_NM IN
/*
// DD DISP=SHR,DSN=your.sequential.file
// DD *
FOR FETCH ONLY WITH UR;

Peter Farley, III

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Farley, III)

Apparently there is additional authority required to use DSNTIAUL against our production table that I do not have.  I am investigating possibilities with our DBA's, but I'm not hopeful for a quick resolution.  There are understandable and legitimate security and audit concerns with granting that authority for a production table to an application programmer, even a trusted one.

 

So I am still searching for answers.  Does anyone have any other batch access examples for large SPUFI SELECT's they could share?

 

Peter

James Campbell

Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Farley, III)
DECLARE GLOBAL TEMPORARY TABLE FRED
(C1 CHAR(1));

INSERT INTO SESSION.FRED VALUES('A');
INSERT INTO SESSION.FRED VALUES('B');
...

Ideally you would create an index on SESSION.FRED.C1 , but it is possible that you won't
have authority on the STOGROUP and BUFFERPOOL required. But worth a shot

CREATE INDEX FREDIX
ON SESSION.FRED(C1)
;

then

SELECT T.VAR_NM
FROM DB2X.TBLNAME T
, SESSION.FRED F
WHERE F.C1 = T.VAR_NM
;

"SESSION" is a fixed value; T, F, FRED, FREDIX and C1 can be changed to taste.


James Campbell


On 6 Jun 2019 at 13:47, Peter Farley, III wrote:

>
> Phil,
>  
> I actually don't know if I am prevented from using one because I don't know enough SQL to know
> what a Declared Temporary Table looks like much less how to use it.  Could you provide a
> simplified example as an alternative to a SELECT like this:
>  
> SELECT VAR_NM FROM DB2X.TBLNAME WHERE VAR_NM IN ('A', 'B', 'C', ... 'Z') FOR FETCH
> ONLY WITH UR;
>  
> Where VAR_NM is CHAR(1).  The business need here is to determine if any of the "IN" values are
> present in DB2X.TBLNAME and list all that are out to a sequential file from which they can be
> extracted via SORT or REXX script.  In the real application VAR_NM is larger than 1 character and
> there are millions of values of VAR_NM in DB2X.TBLNAME and multiple thousands of "IN" values
> generated by another process to be checked against the table.
>  
> Peter
>  
> In Reply to Philip Sevetson:
> Peter,
>
> I would only note that a Declared Temporary Table is not in any way a cataloged, logged, or
> persistent object - it´s got only the duration of a unit of work, somewhat like a &&file in JCL.
> If they won´t let you do _that_, then it´s true, you don´t have any good choices.
>
> -phil (sevetson)
>
> From: Peter Farley, III [mailto:[login to unmask email]
> Sent: Thursday, June 06, 2019 3:22 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx
> EXEC?
>
>
> Phil,
>
> The access has to be strictly READ-ONLY as the process will be going against production
> DB2 tables which I may not change in any way, so creating a temporary table is not
> possible. Getting a new table created by DBA's just for these inquiries is bureaucratically
> complicated and lengthy, and the inquiries may change rapidly over a relatively short time
> span, so pure SELECT and FOR FETCH ONLY is the only path I see forward.
>
> Peter
>
> (Replying via the web interface as I am set up with only daily digests via email).
>
> In Reply to Philip Sevetson:
> Peter,
>
> Why are you using "IN" syntax? Wouldn't it be easier to INSERT all of the values you want
> in a temporary table (or a not-completely-temporary table) and then do an Inner Join against
> the table which you want results from? (This also has the advantage of using multiple
> shorter individual statements...)
>
> -phil (sevetson)
>
> From: Farley, Peter x23353 [mailto:[login to unmask email]
> Sent: Thursday, June 06, 2019 2:35 PM
> To: [login to unmask email]
> Subject: [DB2-L] - Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
>
> Hi All,
>
> First, my apologies to the IDUG Support people - I accidentally sent this email to them
> instead of to this list. I ask IDUG to please ignore that email.
>
> I have a business need to run program-generated SELECT inquiries that may have multiple
> thousands of values for a WHERE clause (WHERE variable_name IN ('value1', 'value2', . . .
> . )).
>
> I have RTFM for techniques to run the SPUFI program supplied by DB2 in a batch job. I
> have searched the archives of this list but I did not find anything referencing SPUFI at all.
>
> If it matters we are DB2 V11, z/OS 2.2 here. We don't use DSNTEP2 for "batch SPUFI"
> because of the awful "paged" output format and no PL/1 compiler to modify the source to
> suit our needs.
>
> I am aware of the existence of CSNTEP2, a COBOL version of DSNTEP2, but it has
> SELECT size limitations (32760 maximum size of a single VARCHAR variable).
>
> I see in the DB2 manuals that there is a Java command line program to invoke a Java
> equivalent of SPUFI, "java com.ibm.db2.clp.db2", but I have some confusion about how to
> run this from a Rexx EXEC and get the connection to the proper DB2 database.
>
> Our "normal" way to run a DB2 program like DSNTEP2 uses IKJEFT01 with SYSTSIN
> similar to this:
>
> DSN SYSTEM(DB2X)
> RUN PROGRAM(DSNTEP2) PLAN(PLANNAME) LIB('HLQ.LOADLIB') END
>
> Our batch COBOL DB2 programs use a CAF connection to DB2 via a custom exit so the
> batch programs do not deal with any CONNECT process. As a result I am somewhat in the
> dark about how to code the CONNECT process, or whether I need to use some other way
> to connect the Rexx or the Java program to the correct DB2.
>
> Any RTFM pointers you can provide to examples of exactly how to use the Java command
> line processor or the actual SPUFI program supplied by DB2 in a batch Rexx program
> would be greatly appreciated.
>
> Peter
>
> -----End Original Message-----
>



---
This email has been checked for viruses by AVG.
https://www.avg.com

Michael Hannan

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Farley, III)

In Reply to Peter Farley, III:

Apparently there is additional authority required to use DSNTIAUL against our production table that I do not have.  I am investigating possibilities with our DBA's, but I'm not hopeful for a quick resolution.  There are understandable and legitimate security and audit concerns with granting that authority for a production table to an application programmer, even a trusted one. 

So I am still searching for answers.  Does anyone have any other batch access examples for large SPUFI SELECT's they could share? 

There should not be authority problems to run DSNTIAUL. I use it at many sites and have no authority to Select from Prod tables in general, only Catalog and Explain tables. Yes you do need auth to run it, execute the Plan, whatever. Access to read the executable libraries. It is no more dangerous than SPUFI, TEP2, etc. Just does not have ugly filler stuff inserted into the output. 

DSNTIAUL is one of my favorites because I have full control over the output format. I can then use SQL as a report producer formatted exactly how I want.

In Db2 V8 for z/OS, the limits for SQL size increased dramatically. We were originally limited to 32K character SQLs, and I did hit the limit at times. Even got SQL too complex for RDS message sometimes. Now no problem, I think. If each IN value wastes space with multiple blanks outside the quoted string or numeric value, the multiple blanks will be internally counted as just one towards the limit. 

I have not yet hit a limit for size of an SQL in DSNTIAUL. Certainly have some well over 32K characters. I have more problems with the limit on the number of query blocks allowed in a single SQL. Did go over 256 Query Blocks (or similar) once or twice.

I have created Declared Temp Tables in Prod in order to do trial dynamic Explains of the SQLs of others or of Programs. Usually that is allowed, even if no Authority to create a normal Table or Tablespace. I also found I can create Views but only in limited  circumstances, even that alters the Catalog, e.g. Views on tables I own.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Jan Moeyersons

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Farley, III)

Why not use DSNREXX?

Cheers,

Jantje.

Philip Sevetson

Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Michael Hannan)
Michael and Peter,

The size limit for a SQL statement is 2097152 bytes

V10 : https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_limits.html
V12: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_limits.html
Search on page for “Maximum length of an SQL statement”
-phil (sevetson)

From: Michael Hannan [mailto:[login to unmask email]
Sent: Friday, June 07, 2019 2:21 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?


In Reply to Peter Farley, III:

Apparently there is additional authority required to use DSNTIAUL against our production table that I do not have. I am investigating possibilities with our DBA's, but I'm not hopeful for a quick resolution. There are understandable and legitimate security and audit concerns with granting that authority for a production table to an application programmer, even a trusted one.

So I am still searching for answers. Does anyone have any other batch access examples for large SPUFI SELECT's they could share?

There should not be authority problems to run DSNTIAUL. I use it at many sites and have no authority to Select from Prod tables in general, only Catalog and Explain tables. Yes you do need auth to run it, execute the Plan, whatever. Access to read the executable libraries. It is no more dangerous than SPUFI, TEP2, etc. Just does not have ugly filler stuff inserted into the output.

DSNTIAUL is one of my favorites because I have full control over the output format. I can then use SQL as a report producer formatted exactly how I want.

In Db2 V8 for z/OS, the limits for SQL size increased dramatically. We were originally limited to 32K character SQLs, and I did hit the limit at times. Even got SQL too complex for RDS message sometimes. Now no problem, I think. If each IN value wastes space with multiple blanks outside the quoted string or numeric value, the multiple blanks will be internally counted as just one towards the limit.

I have not yet hit a limit for size of an SQL in DSNTIAUL. Certainly have some well over 32K characters. I have more problems with the limit on the number of query blocks allowed in a single SQL. Did go over 256 Query Blocks (or similar) once or twice.

I have created Declared Temp Tables in Prod in order to do trial dynamic Explains of the SQLs of others or of Programs. Usually that is allowed, even if no Authority to create a normal Table or Tablespace. I also found I can create Views but only in limited circumstances, even that alters the Catalog, e.g. Views on tables I own.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Mohammad Khan

Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Farley, III)
I have a couple of suggestions for making CSNTEP2 usable for your situation. First you don't have to process all your input values in a single SQL statement rather use multiple statements to fit the 32K statement limit of the program. The other option would be to modify CSNTEP2 to remove this limitation. After all the source is available and it's in COBOL.
Khalid

From: Farley, Peter x23353 <[login to unmask email]>
Sent: Thursday, June 06, 2019 1:35 PM
To: [login to unmask email]
Subject: [DB2-L] - Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?

Hi All,

First, my apologies to the IDUG Support people - I accidentally sent this email to them instead of to this list. I ask IDUG to please ignore that email.

I have a business need to run program-generated SELECT inquiries that may have multiple thousands of values for a WHERE clause (WHERE variable_name IN ('value1', 'value2', . . . . )).

I have RTFM for techniques to run the SPUFI program supplied by DB2 in a batch job. I have searched the archives of this list but I did not find anything referencing SPUFI at all.

If it matters we are DB2 V11, z/OS 2.2 here. We don't use DSNTEP2 for "batch SPUFI" because of the awful "paged" output format and no PL/1 compiler to modify the source to suit our needs.

I am aware of the existence of CSNTEP2, a COBOL version of DSNTEP2, but it has SELECT size limitations (32760 maximum size of a single VARCHAR variable).

I see in the DB2 manuals that there is a Java command line program to invoke a Java equivalent of SPUFI, "java com.ibm.db2.clp.db2", but I have some confusion about how to run this from a Rexx EXEC and get the connection to the proper DB2 database.

Our "normal" way to run a DB2 program like DSNTEP2 uses IKJEFT01 with SYSTSIN similar to this:

DSN SYSTEM(DB2X)
RUN PROGRAM(DSNTEP2) PLAN(PLANNAME) LIB('HLQ.LOADLIB') END

Our batch COBOL DB2 programs use a CAF connection to DB2 via a custom exit so the batch programs do not deal with any CONNECT process. As a result I am somewhat in the dark about how to code the CONNECT process, or whether I need to use some other way to connect the Rexx or the Java program to the correct DB2.

Any RTFM pointers you can provide to examples of exactly how to use the Java command line processor or the actual SPUFI program supplied by DB2 in a batch Rexx program would be greatly appreciated.

Peter




This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and delete the message and any attachments from your system.
-----End Original Message-----
HCSC Company Disclaimer

The information contained in this communication is confidential, private,
proprietary, or otherwise privileged and is intended only for the use of
the addressee. Unauthorized use, disclosure, distribution or copying is
strictly prohibited and may be unlawful. If you have received this
communication in error, please notify the sender immediately at
(312) 653-6000 in Illinois; (800) 447-7828 in Montana;
(800) 835-8699 in New Mexico; (918) 560-3500 in Oklahoma;
or (972) 766-6900 in Texas.

Peter Farley, III

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to James Campbell)

Thank you for the example James.  I will try that here to see if I am permitted to use it.

Peter
 
In Reply to James Campbell:

DECLARE GLOBAL TEMPORARY TABLE FRED
(C1 CHAR(1));

INSERT INTO SESSION.FRED VALUES('A');
INSERT INTO SESSION.FRED VALUES('B');


Ideally you would create an index on SESSION.FRED.C1 , but it is possible that you won't
have authority on the STOGROUP and BUFFERPOOL required. But worth a shot

CREATE INDEX FREDIX
ON SESSION.FRED(C1)
;

then

SELECT T.VAR_NM
FROM DB2X.TBLNAME T
, SESSION.FRED F
WHERE F.C1 = T.VAR_NM
;

"SESSION" is a fixed value; T, F, FRED, FREDIX and C1 can be changed to taste.

James Campbell

 

Peter Farley, III

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Jan Moeyersons)

Jantje,

I would be interested in a DSNREXX solution because I have other parts of the process that need a REXX script to automate it more completely.  Could you suggest a skeleton REXX using DSNREXX to perform the simplified SELECT I posted earlier in this discussion?

Peter
 
In Reply to Jan Moeyersons:

Why not use DSNREXX?

Cheers,

Jantje.

Peter Farley, III

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Philip Sevetson)

Thanks Phil.  My DBA also suggested that I should use the High Performance Unload utility (INZUTILB) that we have in our shop as a better alternative than DSNTIAUL, and that utility works using my large SELECT without any problems.
 
In Reply to Philip Sevetson:

Michael and Peter,

The size limit for a SQL statement is 2097152 bytes

V10 : https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_limits.html
V12: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_limits.html
Search on page for “Maximum length of an SQL statement”
-phil (sevetson)

Peter Farley, III

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Mohammad Khan)

Khalid,

I can see how that could be done in my simple case (stop when the next "IN" value would exceed the space in the host variable W02-INPUT-SQL leaving enough room at the end for the required trailing syntax), but doing it in the general case would require almost a full SQL parsing capability to be able to determine where to split up the input SELECT, wouldn't it ?  That seems like quite a lot more work than I would want to take on, and probably quite painful to do in COBOL as well.

Peter


In Reply to Mohammad Khan:

I have a couple of suggestions for making CSNTEP2 usable for your situation. First you don't have to process all your input values in a single SQL statement rather use multiple statements to fit the 32K statement limit of the program. The other option would be to modify CSNTEP2 to remove this limitation. After all the source is available and it's in COBOL.
Khalid

Mohammad Khan

Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Farley, III)
My suggestion to split the sql into smaller bits was for your specific case where the items in the in-list are the only variable, it surely is not a generic solution. On the other hand increasing the statement size limit in CSNTEP2 is likely not that difficult. Since you have other pieces of your sultion in REXX, you are probably better off trying a REXX solution to execute your queries as well.
Khalid

From: Peter Farley, III <[login to unmask email]>
Sent: Friday, June 07, 2019 12:26 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?


Khalid,

I can see how that could be done in my simple case (stop when the next "IN" value would exceed the space in the host variable W02-INPUT-SQL leaving enough room at the end for the required trailing syntax), but doing it in the general case would require almost a full SQL parsing capability to be able to determine where to split up the input SELECT, wouldn't it ? That seems like quite a lot more work than I would want to take on, and probably quite painful to do in COBOL as well.

Peter

In Reply to Mohammad Khan:
I have a couple of suggestions for making CSNTEP2 usable for your situation. First you don't have to process all your input values in a single SQL statement rather use multiple statements to fit the 32K statement limit of the program. The other option would be to modify CSNTEP2 to remove this limitation. After all the source is available and it's in COBOL.
Khalid

-----End Original Message-----
HCSC Company Disclaimer

The information contained in this communication is confidential, private,
proprietary, or otherwise privileged and is intended only for the use of
the addressee. Unauthorized use, disclosure, distribution or copying is
strictly prohibited and may be unlawful. If you have received this
communication in error, please notify the sender immediately at
(312) 653-6000 in Illinois; (800) 447-7828 in Montana;
(800) 835-8699 in New Mexico; (918) 560-3500 in Oklahoma;
or (972) 766-6900 in Texas.

Peter Conlin

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Farley, III)

Hi Peter,

I didn't see much regarding the java CLP & agree it's neither documented nor intuitive for batch, but here's an example.

//BATCH    EXEC PGM=BPXBATCH,    
// PARM='SH db2'
//STDOUT DD SYSOUT=*
//STDERR DD SYSOUT=*
//STDIN DD PATHOPTS=(ORDONLY),
// PATH='/tmp/p1.stdin'

The STDIN file (attached) contains the CONNECT, SELECT & TERMINATE.

My .profile (Db2 12) is also attached.

It is probably more useful for environmental debugging than anything else.

Good Luck,

Peter

Attachments

  • p1.stdin.txt (<1k)
  • DOTprofile.txt (1.7k)

Peter Farley, III

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Conlin)

Thanks Peter.  When I manage to find some of those increasingly scarce round tuits I will try that out.  In the meantime I think I will follow my DBA's advice and use the Fast Data Unload utility for the present project.  It seems more than capable for my simple needs in this case.

 

Thanks to all who offered help.  Much appreciated, and I learned a few things along the way.

Peter

 
In Reply to Peter Conlin:

Hi Peter,

I didn't see much regarding the java CLP & agree it's neither documented nor intuitive for batch, but here's an example.

//BATCH    EXEC PGM=BPXBATCH,    
// PARM='SH db2'
//STDOUT DD SYSOUT=*
//STDERR DD SYSOUT=*
//STDIN DD PATHOPTS=(ORDONLY),
// PATH='/tmp/p1.stdin'

The STDIN file (attached) contains the CONNECT, SELECT & TERMINATE.

My .profile (Db2 12) is also attached.

It is probably more useful for environmental debugging than anything else.

Good Luck,

Peter

Patrick Bossman

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Conlin)

Hello,

Somehow I missed this thread.  We made some updates to CLP recently to support pass tickets and credentials on the call to CLP.  You can also specify them on the connect string.  Take a look here, scroll all the way to the bottom.

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/comref/src/tpc/db2z_clpstartsyntax.html

There is increased interest in serverside batch SQL execution via USS to perform SQL activities as part of CI/CD.

If additional samples/examples needed in this space, let me know.  

Best regards,

Pat Bossman

Colin Raybould

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Patrick Bossman)

Hi Pat,

That is useful.  With a small Java routine I should be able to generate a pass ticket and logon to DB2 via the CLP in batch without exposing my password.

Regards,

Collin Raybould.

In Reply to Patrick Bossman:

Hello,

Somehow I missed this thread.  We made some updates to CLP recently to support pass tickets and credentials on the call to CLP.  You can also specify them on the connect string.  Take a look here, scroll all the way to the bottom.

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/comref/src/tpc/db2z_clpstartsyntax.html

There is increased interest in serverside batch SQL execution via USS to perform SQL activities as part of CI/CD.

If additional samples/examples needed in this space, let me know.  

Best regards,

Pat Bossman

Bob Jeandron

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Jan Moeyersons)

Believe Rexx would be a solution for you.   May take a bit of trial and error but you can format the output anyway you want.   See migration program …..SDSNSAMP(DSNTIJPM).    It uses Rexx to create migration reports from the catalog.

Good Luck.

Jan Moeyersons

RE: Example JCL and code to run SPUFI (not DSNTEP2) from Rexx EXEC?
(in response to Peter Farley, III)

/****  REXX  *****/

SQLQ = "SELECT IBMREQD FROM SYSIBM.SYSDUMMY1"

subsys="DB2S"

address TSO "SUBCOM DSNREXX"

if RC then RXSUBCOM('ADD','DSNREXX','DSNREXX')

address DSNREXX CONNECT subsys

if SQLCODE ^= 0 then do

  say 'sqlcode='SQLCODE'

  call SQLCA

end

address DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"

if SQLCODE ^= 0 then CALL SQLCA

address DSNREXX "EXECSQL PREPARE S1 FROM :SQLQ"

if SQLCODE ^= 0 then CALL SQLCA

address DSNREXX "EXECSQL OPEN C1"

if SQLCODE ^= 0 then CALL SQLCA

ibmreqd = ''

do while SQLCODE = 0

  address DSNREXX "EXECSQL FETCH C1into :ibmreqd"

  if SQLCODE ^= 0 then CALL SQLCA

  say ibmreqd

end

address DSNREXX "EXECSQL CLOSE C1"

address DSNREXX "DISCONNECT"

 

SQLCA:

say 'SQLCODE :' SQLCODE

say 'SQLSTATE:' SQLSTATE

exit 99