Bookshelf Home | Contents | Index | PDF     

Upgrade Guide for DB2 UDB for z/OS > Upgrading a Development Environment Database from the Midtier >

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 job card and job parameter information to conform to your specific standards.

Perform the following tasks to transfer the stored procedure code to the z/OS host.

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

  • You must have upgraded your schema to the 1:1:1 schema structure model. This occurs when you execute the schema.sql output file (see Executing SQL Output Files After the Second Pause - Stage 1 of 2 for further information.)
  • You must have set up your Workload Manager (WLM) environment before running the stored procedures.

    NOTE:  Setting up your WLM environment is required to install and run the UDFs and stored procedures.

  • You cannot create stored procedures until after the second pause in the development environment upgrade process.

To transfer the stored procedure files to the z/OS host

  1. Navigate to your DDL output directory and open the dev0ftp.txt file.

    Windows: The default DDL output directory is DBSRVR_ROOT\db2390\dboutput\upgrade.

    UNIX: The default DDL output directory is DBSRVR_ROOT/db2390/dboutput/upgrade.

  2. 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 z/OS 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.

  3. After modifying the dev0ftp.txt file, save it and close the file.
  4. Double-click ftpdev0.bat on Windows (from UNIX issue the following command: Ftp -i > ftpdev0.bat).

    You are prompted to enter the password for the user name that you entered in dev0ftp.txt to replace &Username.

  5. Enter the password, and then press Enter.

    All files generated to this point of the upgrade process transfer from the midtier to the z/OS host.

To submit the stored procedure job on the DB2 host

  1. After the transfer is successful, log on to the mainframe, and navigate to the DSNHLQ.SIEBEL.DEV0 file, for example, SIEBLQ1.SIEBEL.DEV0.
  2. 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.

    1. 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.

    2. 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.

    3. Enter submit and then press Enter.
    4. After submitting the job, enter cancel on the command line or press PF3 to save changes.
    5. Verify that the job completed successfully:
      • Verify that the RC=0.
      • Verify that dataset DSNHLQ.DEV.JCL was created.
  3. 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.
  4. 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)'

  5. 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, if your isolated WLM environment is WLMxyz1, then

      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.
  6. 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.
  7. After the stored procedure creates and binds, refresh the WLM environment. To do this, run WLMREFSH.

Interpreting Error Returns

The BINDSIA job sometimes fails with an RC8 with the following error in SDSF (or other job output facility) 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