------------------------------------- SQL Procedures README 3 Version 2.1.1 3 February 23, 2000 (See the bottom of this file for change log) ------------------------------------- SQL Procedures lets you build and run SQL procedures on an OS/390 system using JCL. Workload Manager (WLM) is not required. This version of SQL procedures 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 support for DB2 for OS/390 V5. You will install - Two new load modules for the SQL procedures precompiler - Sample programs and JCL jobs ATTENTION: The IBM C/C++ for OS/390 optional feature must be installed before you can use SQL Procedures support for DB2 for OS/390. We also recommend that you install PTF UQ35278 (for APAR PQ29706). The APAR fix is available along with the DSNTPSMP download mentioned below. 3 If you have the C/C++ Productivity Tool for OS/390 installed on 3 your workstation and the Debug Tool installed on your OS/390 3 system, you can debug an SQL Procedure after applying PTF UQ40233 3 for APAR PQ30773. If you plan to use the Stored Procedure Builder (SPB), we recommend that you order the DB2 for OS/390 REXX Language Support Feature. The order numbers 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/ For SPB support, you must also - install DSNTPSMP (the SQL Procedure Processor written in REXX) and available from: http://www.ibm.com/software/db2os390/sqlproc/ - configure your DB2 system to run DSNTPSMP as a WLM managed stored procedure Installing the code ------------------- To install SQL procedures support, follow these steps: (Step 1) Download the file sqlproc1.zip to your PC and unzip it. It contains the following files: DSN8ED3.CSRC ..... Source for a sample C program that calls an SQL procedure DSN8ES1.SQLSRC ..... Source for a sample SQL procedure DSNHSQL.JCLPROC ..... JCL procedure to compile an SQL procedure DSNTEJ63.SAMPJCL ..... JCL to compile DSN8ES1 DSNTEJ64.SAMPJCL ..... JCL to compile and run DSN8ED3 DSNTHAL.SAMPJCL ..... Linkedit JCL for the precompiler load modules sqlproc1.readme ..... SQL Procedures readme file sqlproc_oct1999.pdf ..... SQL Procedures Preview Document xmitpsm.bin ..... SQL Procedures Precompiler load modules (Step 2) Transfer all files except sqlproc_oct1999.pdf and sqlproc1.readme to your OS/390 system. For example, using FTP: - Transfer all files except xmitpsm.bin as text files. - Transfer file xmitpsm.bin as a binary file. Check (Step 3) for details. (Step 3) You must pre-allocate a sequential data set where xmitpsm.bin will be received on your OS/390 system. The data set, YourMVSFileName, must be defined as RECFM=FB, LRECL=80, BLKSIZE=3120. Thirty tracks of a 3390 disk should be adequate. Restore the contents of the sequential data set (YourMVSFileName) into an MVS partitioned data set (PDSName). Note that you should preallocate the data set PDSName to have the same characteristics as DSN510.SDSNLOAD. Here is a model of the command to get the data set, YourMVSFileName, into an MVS PDS (PDSName). RECEIVE INDSNAME(YourMVSFileName) at prompt: DA(PDSName) SYSOUT(*) When restored to the PDS you should have two modules with the following characteristics: Size AC AM RM DSNHPSM 000CA048 00 24 24 DSNHPC2 0001AA08 00 31 ANY Copy these two modules to DSN510.SDSNLOAD or USER.SDSNLOAD. You might receive the following message: "Received file appears not to be an INTERACTIVE DATA TRANSMISSION facility file" If you receive this message, check and correct these conditions if necessary: - Your bin transfer format is incorrect. It must be fixed and lrecl of 80 if you use terminal emulator transfer - The data set, YourMVSFileName, does not have the appropriate characteristics (Step 4) Copy the job DSNTHAL to the data set DSN510.NEW.SDSNSAMP and customize DSNTHAL. You must run job DSNTHAL to create aliases required for DSNHPSM. If you do not wish to update DSN510.SDSNLOAD, then you can run DSNTHAL on your new PDS (USER.SDSNLOAD). (Step 5) Customize DSNHSQL and place it in your PROCLIB. If you did not place the new modules in DSN510.SDSNLOAD, then include the new PDS (USER.SDSNLOAD) that contains DSNHPSM, with the aliases for DSNHPSM, and DSNHPC2, in the STEPLIB concatenation for step PC in procedure DSNHSQL. If you do not have DSNHPSM with all its aliases and DSNHPC2 available for step PC, you get the following error message: DSNH20061I E DSNHSMS1 LINE 1 COL 4 UNEXPECTED ERROR RETURNED FROM LE/370: REASON CODE 2, RETURN CODE 00000024 You do not need DSNHPSM with its aliases, and DSNHPC2 in any other steps. (Step 6) Verify the installation by compiling the supplied samples. You can run the samples after applying PTF UQ35278 (for APAR PQ29706). This PTF makes it possible to run stored procedures whose language is SQL. If you do not want to apply the PTF yet, you must modify the LANGUAGE column for each SQL procedure from SQL to C with this statement: UPDATE SYSIBM.SYSPROCEDURES SET LANGUAGE = 'C' WHERE LANGUAGE = 'SQL' You can then invoke your SQL procedures as though they were written in C. Change Activity --------------- 10/19/99 Version 1.0 2 01/20/00 Version 2.1.0 - Add notice that the IBM C/C++ Optional Feature is required for DB2 for OS/390 SQL Procedures support 3 02/23/00 Version 2.1.1 - Add notice that PTF UQ40233 (fixes PQ30773) is required for using C/C++ Productivity Tools to debug a DB2 for OS/390 SQL procedure