Bookshelf v7.7: Deploying Stored Procedures and User-Defined Functions

Upgrade Guide for DB2 UDB for z/OS and OS/390 > Upgrading the Database >

Deploying Stored Procedures and User-Defined Functions

Upgrades: All upgrades.

Environments: Development environment only.

This topic is part of an upgrade process. See How to Perform the Upgrade.

You must transfer and install a user-defined function (UDF) and several stored procedures onto the Siebel Database Server to support the Siebel product. Any method that transfers the necessary files to the correct location on the Database Server is acceptable.

NOTE: When deploying stored procedures and user-defined functions, you need to update the jobcard and job parameter information to conform to your specific standards.

Perform the following tasks to transfer the stored procedure code to the zSeries host.

Prerequisites: The following prerequisites apply to creating and deploying stored procedures:

You must have upgraded your schema to the new schema structure introduced in Release 7.7.
You must have set up your WLM environment before running the stored procedures.
You cannot create stored procedures until after the second pause in the development environment upgrade process.
For development environment upgrades from the midtier, see Resuming the Siebel Upgrade Wizard After the Second Pause.
For development environment upgrades from the mainframe, see Resuming the Siebel Upgrade Wizard on the Midtier After the Second Pause.

To transfer the stored procedure files to the zSeries host

Navigate to your DDL output directory and open the dev0ftp.txt file.
Windows: The default DDL output directory is SIEBEL_ROOT\dbsrvr\db2390\dboutput\upgrade.

UNIX: The default DDL output directory is $SIEBEL_ROOT/dbsrvr/db2390/dboutput/upgrade.
Edit the dev0ftp.txt file using the following information:
1. Change &IP into the IP address of your DB2 host, for example ZM01.
2. Change &Username to an authorized TSO ID.
3. Change the constant SIEBELQ1 to your own dataset high-level qualifier (DSNHLQ), for example, SADMIN, using a command like Replace All.
  Use the same high-level qualifier for all zSeries upgrade datasets. The DSNHLQ must be 18 characters or less and can have multiple nodes separated by periods. A node must be one to eight characters in length. The DSNHLQ can be the TSO ID.
After modifying the dev0ftp.txt file, save it and close the file.
Double-click ftpdev0.bat on Windows (from UNIX issue the following command: Ftp -i > ftpfile.bat)
You are prompted to enter the password for the user name that you entered in dev0ftp.txt to replace &Username.
Enter the password, and then press Enter.
All files generated to this point of the upgrade process transfer from the midtier to the zSeries host.

To submit the stored procedure job on the DB2 host

After the transfer is successful, log on to the mainframe, and navigate to the DSNHLQ.SIEBEL.DEV0 file, for example, SIEBLQ1.SIEBEL.DEV0.
Edit DSNHLQ.SIEBEL.DEV0, and then submit the job.
1. In the job card set the following values:
  CLASS=S MSGCLASS=X JOBPARAM S=YOUR_HOST_NAME
  
  For example, if your Host server is QA01, the job card should look like the following:
//###DEV0D JOB ACCNT#,CLASS=S,MSGCLASS=X,MSGLEVEL=(1,1),
// TIME=1440,REGION=0M,NOTIFY=&SYSUID,LINES=9999
//*
/*JOBPARM S=QA01

Use uppercase letters only. As indicated in the Comments section, change all references of SIEBELHLQ to your TSO ID or DSNHLQ using the following command:

C SIEBELQ1 DSNHLQ ALL

Where DSNHLQ is the dataset name high-level qualifier.
1. On the command line, enter the following commands:
  C <DB2LIB1> your_DB2_load_DSN ALL
  
  For example, C <DB2LIB1> DSN710.SDSNLOAD ALL
  
  C <DB2LIB2> your_second_DB2_load_DSN ALL
  
  For example, C <DB2LIB2> DSN710.RUNLIB.LOAD ALL
  
  C &TSUBSYS your_subsystem ALL
  
  For example, C &TSUBSYS Q10K ALL
  
  C &SCHEMAQUALIFIER your_schema_qualifier ALL
  
  For example, C &SCHEMAQUALIFIER CQ10Knnn ALL
  
  Change the <DB2WLM> parameter to your isolated WLM environment name. This must include eight characters.
  
  For example, if your isolated WLMENV is WLMxyz1, then you should issue the following command:
  
  C <DB2WLM> 'WLMxyz1 ' ALL
  
  NOTE: A trailing space was added in the preceding example to make the string eight characters long.
2. Enter submit and then press Enter.
3. After submitting the job, enter cancel on the command line or press PF3 to save changes.
4. Verify that the job completed successfully:
  - Verify that the RC=0.
  - Verify that dataset DSNHLQ.DEV.JCL was created.
Edit the RECEIVE member of DSNHLQ.DEV.JCL as appropriate, then submit the job.
1. In the job card set the following values:
  CLASS=S MSGCLASS=X JOBPARAM S=YOUR_HOST_NAME
2. Comment the line that begins with //LIBS by replacing L with *.
  For example, if your Host server is QA01, the job card should look like the following:
000001 //###SPRV JOB ACCNT#,CLASS=S,MSGCLASS=X,MSGLEVEL=(1,1),
000002 // TIME=1440,REGION=0M,NOTIFY=&SYSUID,LINES=9999
000003 //*
000004 /*JOBPARM S=QA01
000005 //*
000006 //*IBS JCLLIB ORDER=(DSNHLQ.JCLLIB)
000007 //*
1. Verify that RC equals 00 to signify that the job completed successfully.
Execute a rexx exec to alter the received DDL library symbolics.
Enter the following command from the command line:

TSO EX 'DSNHLQ.DEV.JCL(DDLMODR)'

For example, if your DSNHLQ is XYZ123.AA, then enter the following command after you run the RECEIVE member:

TSO EX 'XYZ123.AA.DEV.JCL(DDLMODR)'
Edit the IEBCOPY member of DSNHLQ.DEV.JCL as appropriate, then submit the job.
1. In the job card, set the following values:
  CLASS=S MSGCLASS=X JOBPARAM S=YOUR_HOST_NAME
2. Comment the line that begins with //LIBS by replacing L with *.
3. In the line beginning //OUTPDS DD DSN=load_module_dsname_space
  Specify the WLM environment where:
  
  load_module_dsname_space corresponds to your isolated WLM environment.
  
  For example, your isolated WLM environment is WLMxyz1:
  
  DB2WLM.WLMxyz1.LOAD
4. Verify the job card looks like the following (your Host server is QA01) and submit the job:
000001 //###SPRV JOB ACCNT#,CLASS=S,MSGCLASS=X,MSGLEVEL=(1,1),
000002 // TIME=1440,REGION=0M,NOTIFY=&SYSUID,LINES=9999
000003 //*
000004 /*JOBPARM S=QA01
000005 //*
000006 //*IBS JCLLIB ORDER=(DSNHLQ.JCLLIB)
000007 //*
1. Verify that RC equals 00 to signify that the job completed successfully.
Edit the BINDSIA member of DSNHLQ.DEV.JCL.
1. In the job card, set the following values:
  CLASS=S MSGCLASS=X JOBPARAM S=<YOUR HOST NAME>
2. Comment the line that begins with //LIBS by replacing L with *.
3. Verify the job card looks as follows (your host server is QA01):
000001 //###BNDS JOB ACCNT#,CLASS=S,MSGCLASS=X,MSGLEVEL=(1,1),
000002 // TIME=1440,REGION=0M,NOTIFY=&SYSUID,LINES=9999,
000003 // USER=SBLDBA
000004 //*
000005 /*JOBPARM S=QA01
000006 //*
000007 //*IBS JCLLIB ORDER=(DSNHLQ.JCLLIB)
1. Execute the following change command: to replace &SCHEMAQUALIFIER with the target schema qualifier you are using:
C &SCHEMAQUALIFIER TABLEOWNER_NAME ALL
1. Execute the following change command, replacing ASCEBC with either ASCII or EBCDIC as appropriate:
  - For ASCII databases, type the following command: C 'ASCEBC' ASCII ALL
  - For EBCDIC databases, type the following command: C 'ASCEBC' EBCDIC ALL
2. Execute the following change command to replace &TSUBSYS with the subsystem you are using:
C &TSUBSYS SUBSYSTEM ALL
1. Submit the job. Verify that RC equals 00 to signify that the job completed successfully.
2. Press PF3 to save your changes.
After the stored procedure creates and binds, refresh the Workload Manager (WLM) environment. To do this, run WLMREFSH.

Interpreting Error Returns

The BINDSIA job sometimes fails with an RC8 with the following error in SDSF output:

DSNT408I SQLCODE = -454, ERROR: THE SIGNATURE PROVIDED IN THE CREATE FUNCTION STATEMENT FOR CQ10K006.NEXTSEQ MATCHES THE SIGNATURE OF SOME OTHER FUNCTION ALREADY EXISTING IN THE SCHEMA

DSNT418I SQLSTATE = 42723 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICRT SQL PROCEDURE DETECTING ERROR

This will cause a job status return code of 8 for CREATE and 4 for BIND. Error messages of this type are acceptable. Any other message types are unacceptable and mean the job has failed.

Upgrade Guide for DB2 UDB for z/OS and OS/390