------------------------------------- SQL Procedures Processor README 5 Version 2.1.1 5 February 23, 2000 (See the bottom of this file for change log) ------------------------------------- SQL Procedures Processor allows any user the ability to build a SQL procedures application on an OS/390 system. This version of SQL procedures processor support is provided on an AS IS basis without warranty of any kind. It is not a formally supported IBM product. Please send any queries or comments to sqlproc@us.ibm.com For more general information on SQL Procedures, please see: http://www.ibm.com/software/db2os390/sqlproc/ Introduction ------------ This file describes the installation of the SQL procedures processor for DB2 for OS/390 V5. The SQL procedures processor is a REXX language stored procedure with an exec name of "DSNTPSMP". DSNTPSMP is used synonymously with SQL procedures processor throughout this document. It processes a SQL procedures application program to make it executable as a DB2 stored procedure. The SQL procedures processor works optimally with the Stored Procedure Builder workstation product. However, the SQL procedures processor can be invoked from any calling application. You will install: - DSNTPSMP (a REXX stored procedure) - Sample programs and JCL jobs 5 ATTENTION: The IBM C/C++ for OS/390 optional feature must be 5 installed before you can use SQL Procedures support 5 for DB2 for OS/390. **************************************************************** * ATTENTION: The DB2 for OS/390 REXX Language Support Feature * * is a prerequisite for SQL REXX Stored Procedures support. * * Do not proceed with installation of this zipfile until you * * have successfully completed installation of the REXX Language* * Support Feature. * * * * The order numbers for the REXX Language Support Feature are: * * * * 5861 - 6250 Tape * * * * 5862 - 3480 Cartridge * * * * 5275 - 4mm DAT * * * * Information on how to order software can be found at: * * * * http://www.ibm.com/software/howtobuy/ * **************************************************************** Installing the code ------------------- To install SQL procedures processor support, follow these steps: (Step 1) Verify that the DB2 for OS/390 REXX Language Support Feature is installed on your DB2 for OS/390 system. 4 (Step 2) Verify that the following PTFs have been applied to your 4 system: 4 - PTF UQ35278 (fixed APAR PQ29706: DB2 for OS390 REXX Stored Procedures 4 support) 4 - PTF UQ37833 (fixed APAR PQ32467: Add CALLLIBs to DB2 for OS390 REXX 4 Stored Procedures) 4 - PTF UQ38438 (fixed APAR PQ24199: bind changes) 4 (Step 3) Download the file sqlproc1.zip to your PC and unzip it. It contains the following files: DSNTPSMP.EXEC ..... SQL procedures processor code 2 *** Reinstall DSNTSPMP.EXEC if you installed it 2 prior to Version 2.0 of this zipfile DSNTIJSQ.JCL ..... INSERT statement that defines DSNTPSMP to DB2 and DDL statements that create the DSNTPSMP database, which consists of two tables, SYSIBM.SYSPSM and SYSIBM.SYSPSMOPTS. DSNWLMP.SAMPJCL ..... sample JCL procedure that starts the WLM managed stored procedures address space 2 *** Reinstall DSNWLMP.SAMPJCL if you installed 2 it prior to Version 2.0 of this zipfile DSN8ED4.CSRC ..... Source for a sample C program that calls DSNTPSMP and can be used to verify installation 2 *** Reinstall DSN8ED4.CSRC if you installed 2 it prior to Version 1.1 of this zipfile DSN8ES2.SQLSRC ..... Source for a sample SQL Procedure 2 *** Reinstall DSN8ED2.SQLSRC if you installed 2 it prior to Version 1.1 of this zipfile DSN8ED5.CSRC ..... Source for a sample C program that calls SQL Procedure DSN8ES1 2 *** Reinstall DSN8ED5.CSRC if you installed 2 it prior to Version 1.1 of this zipfile DSNTEJ65.SAMPJCL ..... JCL to compile and run DSN8ED4 and DSN8ED5 2 *** Reinstall DSNTEJ65.SAMPJCL if you installed 2 it prior to Version 1.1 of this zipfile sqlprocp.readme ..... SQL Procedures Processor readme file 4 (Step 4) Transfer all files except sqlprocp.readme to your OS/390 4 system. If you use FTP, transfer these files as text. 4 (Step 5) Copy the job DSNTIJSQ to the data set prefix.NEW.SDSNSAMP and customize it according to the directions in the job prologue. You must run job DSNTIJSQ to define DSNTPSMP to DB2, and to create the SQL Procedures Processor database. All steps should complete with return code 0000 or 0004. 4 (Step 6) Customize DSNWLMP according to the directions in its prologue, and place it in your PROCLIB. This is the procedure that starts the WLM managed stored procedures address space that the SQL procedures processor runs in. Note: DB2 will expect to find the DSNTPSMP REXX exec in the data set associated with the SYSEXEC DD. We recommend that you specify DSN=prefix.NEW.SDSNCLST for the SYSEXEC DD. 4 (Step 7) Copy DSNTPSMP into the dataset associated with the SYSEXEC DD statement contained in the DSNWLMP procedure that you customized in the previous step. The prologue of DSNTPSMP contains a description of its input and output parameters, and what functions it can perform. 4 (Step 8) Copy sample programs DSN8ED4, DSN8ED5, and DSN8ES2 to your prefix.SDSNSAMP data set. 4 (Step 9) Copy the job DSNTEJ65 to your prefix.NEW.SDSNSAMP data set and customize it according to the directions in the job prologue. 4 (Step 10) Verify the installation of all items by running DSNTEJ65. DSNTEJ65 prepares and runs C program DSN8ED4, a sample caller that invokes DSNTPSMP to build a SQL procedures application called DSN8ES2. DSNTEJ65 also prepares and runs C program DSN8ED5, a sample caller of DSN8ES2. After you have run DSNTEJ65, the output from the SYSPRINT DD of step PH065S07 should contain a line that looks like this: Total bonuses paid to management: $14900.00 The actual amount may vary from the above example depending on how many records are in the DSN8510.EMP sample table. A non-blank amount indicates that the SQL Procedure Processor has been successfully installed, and is ready for use by the DB2 Stored Procedure Builder tool on the workstation. A blank amount indicates errors. See the Hints and Tips section below for diagnosis. 2 Hints & Tips: 2 ------------- 2 (1) Problem: Job DSNTEJ65, step PH065S04 (program DSN8ED4) ends with code 2 0012 and this message is written to the REPORT01 DD: 2 2 ERROR: Call to DSNTPSMP failed 2 -----> Processing halted 2 DSNT408I SQLCODE = -204, ERROR: DSNTPSMP IS AN UNDEFINED NAME 2 DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE 2 DSNT415I SQLERRP = DSNX9CAT SQL PROCEDURE DETECTING ERROR 2 DSNT416I SQLERRD = -120 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION 2 DSNT416I SQLERRD = X'FFFFFF88' X'00000000' X'00000000' X'FFFFFFFF' 2 X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION 2 2 Explanation: There is no entry for DSNTPSMP in SYSIBM.SYSPROCEDURES. 2 2 Action: Please run the sample, DSNTIJSQ.JCL , to register DSNTSPMP as a 2 stored procedure in SYSIBM.SYSPROCEDURES tables. Follow the directions 2 in the job prologue to customer the JCL for your environment. 2 2 (2) Problem: Job DSNTEJ65, step PH065S04 (program DSN8ED4) ends with code 2 0012 and this message is written to the REPORT01 DD: 2 2 ERROR: Call to DSNTPSMP failed 2 -----> Processing halted 2 DSNT408I SQLCODE = -471, ERROR: SQL CALL FOR STORED PROCEDURE 2 DSNTPSMP FAILED DUE TO REASON 00E79002 2 DSNT418I SQLSTATE = 55023 SQLSTATE RETURN CODE 2 DSNT415I SQLERRP = DSNX9WCA SQL PROCEDURE DETECTING ERROR 2 DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION 2 DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' 2 X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION 2 2 Explanation: No WLM environment was available for DSNTPSMP or that 2 environment 2 2 Action: See dicussion of reason code 00E79002 in the DB2 Messages and 2 Codes manual. 2 2 (3) Problem: Job DSNTEJ65, step PH065S04 (program DSN8ED4) ends with code 2 0999 and receives no result set. The SYSTSPRT DD of the WLM proc for 2 DSNTPSMP contains the following message: 2 2 Subroutine: Validate_Auth 2 ** Error SQL statement - PREPARE S8 FROM :SQLSTMT #3 2 SQLCODE =-204 2 parse source displays >TSO SUBROUTINE DSNTPSMP SYSEXEC ? DSNTPSMP 2 TSO TSO/E ?< 2 line 1727 has not been set a value : 2 call IntErr 'SQLERRMC ='SQLERRMC 2 Label: Finis 2 Subroutine: Open_PSMOUT 2 *** Exit value is =>999<= 2 2 Explanation: You have not created the SYSIBM.SYSPSM and SYSIBM.SYSPSMOPTS 2 tables which are needed for working with SQL Procedures. 2 2 Action: Please run the sample, DSNTIJSQ.JCL , to create SYSIBM.SYSPSM 2 and SYSIBM.SYSPSMOPTS tables. Follow the directions in the job prologue 2 to customer the JCL for your environment. 2 2 (4) Problem: Job DSNTEJ65, step PH065S04 (program DSN8ED4) ends with code 2 0999 and receives no result set. The SYSTSPRT DD of the WLM proc for 2 DSNTPSMP contains one or more messages like the following: 2 2 ** Error SQL statement - 2 SQLCODE =-805 2 SQLERRMC =SQLERRMC 2 SQLERRP =DSNXEPM 2 SQLERRD =-250,0,0,-1,0,0 2 SQLWARN = , , , , , , , , , , 2 SQLSTATE=51002 2 2 Explanation: The DB2 for OS/390 REXX Language Support Feature is not 2 correctly bound. 2 2 Action: Run DSNTIJRX, the install job for the REXX Language Support 2 Feature, to bind the required packages. Follow the directions in 2 the job prologue to customer the JCL for your environment. 2 2 For further information about DSNTIJRX, see the program directory 2 for the REXX Language Support Feature. See also page 26 in the 2 "DB2 for OS/390 Version 5 REXX Language" document at: 2 http://www.ibm.com/software/data/db2/os390/ 2 2 2 (5) Problem: Job DSNTEJ65, step PH065S04 (program DSN8ED4) ends with code 2 0008 and this message is written to the REPORT01 DD: 2 2 Call to BIND/REBIND module failed, rc=-2054 2 2 Explanation: DSNTPSMP bind support is not installed. 2 4 Action: Apply PTF UQ38438 (fixed APAR PQ24199: bind changes) 2 2 (6) Problem: Job DSNTEJ65, step PH065S04 (program DSN8ED4) ends with code 2 0999: 2 2 Explanation: The Stored Procedure Processor has encountered processing 2 errors. 2 2 Action: Analyze the output in the REPORTxx DDs of step PH065S04. If 2 no output was returned to the REPORTxx DD's, or if the output is 2 inconclusive, try viewing the SYSTSPRT DD of the WLM region where 2 DSNTPSMP is running. 2 2 (7) Problem: Job DSNTEJ65, step PH065S07 (program DSN8ED4) ends with code 2 0000 but the output of written to the DD SYSPRINT shows blanks for the 2 amount of bonuses paid. 2 2 Explanation: One or both of the following errors have occurred: 2 (1) The collection id specified in the DSN8ES2 source code is different 2 from the collection id specified the bind statement for plan DSN8ED5 2 in step PH065S06 of job DSNTEJ65. 2 (2) The DSN8ES2 load module created in step PH065S04 of job DSNTEJ65 2 contains an entry point for DSNALI (DB2 call attach). You can 2 verify this by browsing the load module and searching for the 2 string 'DSNALI'. 2 2 Action: Verify both of the following are true: 2 (1) The CREATE PROCEDURE statement in DSN8ES2 specifies NO WLM 2 ENVIRONMENT and a COLLID of DSN8ES51 2 (2) The BIND PLAN statement in step PH065S06 of job DSNTEJ65 specifies 2 a collection id of DSN8ES51 for package DSN8ES2 Change Activity --------------- 10/19/99 Version 1.0 1 11/10/99 Version 1.1 1 - Adds APAR fix AQ32467.FIX, fixtest for APAR PQ32427, CALLLIB support 1 for DB2 Rexx Stored Procedure support. Please run this fixtest after 1 you have applied the PTF in UQ35278.PTF (basic DB2 Rexx Stored 1 Procedure support). 1 - AQ24199.FIX, fixtest for APAR PQ24199 (bind changes), is 1 updated with a more recent rework date, 1999312 (November 8) 1 - DSNTPSMP.EXEC now uses a backslash in place of the PL/I-style 1 'not' sign, to alleviate code page concerns 1 - DSN8ED4.CSRC is updated to correct a file reading error that caused 1 it to treat data set sequence numbers as application data. It also 1 puts a linefeed character at the end of each line of the SQL 1 Procedure source it passes to DSNTPSMP. 1 - DSN8ES2.SQLSRC now contains a new, more relevant sample SQL 1 Procedure. 1 - DSNTEJ65.SAMPJCL is updated in accordance with the new version of 1 DSN8ES2 1 - DSN8ED5.CSRC is updated in accordance with the new version of 1 DSN8ES2 1 - The readme file is updated to reflect the above changes (changebar=1). 2 11/18/99 Version 2.0 2 - AQ24199.FIX is withdrawn. 2 - REMRLI.JCL is introduced to permit customers who applied fixtest PTF 2 AQ24199.FIX to remove it. 2 - BQ24199.$HDB5510 is introduced, replacing AQ24199.FIX as the fixtest 2 for APAR PQ24199 (bind changes). 2 - The Hints and Tips section added to assist diagnosis. 2 - DSNTPSMP is updated to include backup and recovery function 2 - reinstall if you installed it prior to Version 2.0 of this zipfile 2 - DSNWLMP is updated in accordance with the changes to DSNTPSMP 2 - The SQLWORK1 and SQLWORK2 DD's are removed 2 - The SQLDUMMY DD is added 2 - reinstall if you installed it prior to Version 2.0 of this zipfile 2 - The readme file is updated to reflect the above changes (changebar=2). 3 11/23/99 Version 2.0.1 3 - DSNTEJ65 updated: Add QUALIFIER(DSN8510) to BIND statement for DSN8ES2. 4 01/20/00 Version 2.1.0 4 - Removed the following because indicated PTFs are now available from IBM 4 - PTF UQ35278 (fixed APAR PQ29706: DB2 for OS390 REXX Stored Procedures 4 support) 4 - APAR fix AQ32467 (fixtest for APAR PQ32467: DB2 for OS390 REXX 4 Stored Procedures) 4 - APAR fix BQ24199 (Fixtest for APAR PQ24199: bind changes) 4 - Removed steps that dealt with handling these parts. 5 02/23/00 Version 2.1.1 5 - Documented requirement for IBM C/C++ for OS/390 optional feature (already 5 documented in prereq zipfile sqlproc1) 5 - DSNTPSMP is updated to reflect performance improvements 5 - DSN8ED4 is updated to correct JES3 incompatibilities