Bookshelf Home | Contents | Index | PDF     

Implementing Siebel eBusiness Applications on DB2 UDB for z/OS and OS/390 > Siebel Schema Installation on the DB2 Host > Performing a Standard Installation >

Applying DDL on the DB2 Host

When you select the option Generate DDL Into Files, the files schema.sql and ddlview.sql are generated in the DDL output directory that you specified during the installation. Before you continue with the installation, perform the steps below to apply the DDL to the DB2 host.

Transferring Files to the DB2 Host

The following instructions are for transferring certain files to the DB2 host.

To transfer files to the DB2 host

  1. Navigate to the output directory DBSRVR_ROOT\DB2390\DBOUTPUT\INSTALL (Windows) or dbsrvr_root/db2390/dboutput/install (UNIX) and open the instftp.txt file.
  2. Edit the instftp.txt file using the following information:
    1. Change &IP into the IP address or domain name of your DB2 host, for example ZM01.
    2. Change &Username into the your own user name, for example SADMIN.
    3. Change SIEBELQ1 to your own high-level qualifier (HLQ), for example, SADMIN, using a command like Replace All. Save the file.

      Following is an example of the instftp.txt file.

    open &IP
    user &Username

    quote site cylinders primary=1 secondary=1
    quote site recfm=fb lrecl=80 blksize=0

    send C:\sea7x\dbsrvr\db2390\dboutput\install/jobinstl.txt 'HLQ.SIEBEL.INST.JOBINSTL'
    send C:\sea7x\dbsrvr\db2390\dboutput\install/ddlview.sql 'HLQ.SIEBEL.INST.DDLVIEW'

    quote site cylinders primary=10 secondary=25
    send C:\sea7x\dbsrvr\db2390\dboutput\install/schema.sql 'HLQ.SIEBEL.INST.SCHEMA'

    quit

  3. Double-click instftp.bat (Windows) or issue the following command (UNIX):

    ftp -vn < instftp.txt

  4. Enter the password associated with the user name you entered in the instftp.txt file in Step 2.

    Press any key to continue.

  5. The log file instftp.log is created in your DBSRVR_ROOT\DB2390\DBOUTPUT\INSTALL (Windows) or dbsrvr_root/db2390/dboutput/install (UNIX) directory.

    The log contains information on the file transfer, for example:

    Connected to ZM01.siebel.com.

    220-FTPD1 IBM FTP CS V1R4 at LOOPBACK, 22:44:26 on 2004-02-27.
    220 Connection will close if idle for more than 5 minutes.
    331 Send password please.
    230 SADMIN is logged on. Working directory is SADMIN.
    200 SITE command was accepted
    200 SITE command was accepted
    200 Port request OK.
    125 Storing data set SADMIN.SIEBEL.INST.JOBINSTL
    250 Transfer completed successfully.
    ftp: 45038 bytes sent in 0.00Seconds 45038000.00Kbytes/sec.

    200 Port request OK.
    125 Storing data set SADMIN.SIEBEL.INST.DDLVIEW
    250 Transfer completed successfully.
    ftp: 1850 bytes sent in 0.00Seconds 1850000.00Kbytes/sec.

    200 SITE command was accepted
    200 Port request OK.
    125 Storing data set SADMIN.SIEBEL.INST.SCHEMA
    250 Transfer completed successfully.
    ftp: 8369624 bytes sent in 2.42Seconds 3452.82Kbytes/sec.

    221 Quit command received. Goodbye.

  6. After the transfer is successful, log on to the mainframe, and navigate to the HLQ.SIEBEL.INST.JOBINSTL file, for example, SIEBLQ1.SIEBEL.INST.JOBINSTL.
  7. The DSLIST screen reflects the data sets matching the user ID entered previously.
  8. Edit the HLQ.SIEBEL.INST.JOBINSTL data set by entering e to the left of this data set and press ENTER.

    The Edit Entry Panel appears.

  9. As reflected in the comments section change all occurrences of SIEBELQ1 to your HLQ using the following command:

    c SIEBELQ1 HLQ ALL

    NOTE:  Make sure that your entry is in capital letters.

  10. Enter submit on the Command line and press ENTER to submit the job.

    This job will create a new data set named HLQ.SIEBEL.INST.EXEC.

  11. On the DSLIST screen, edit the HLQ.SIEBEL.INST.EXEC data set by entering e to the left of the data set and press ENTER.
  12. Enter exec to the left of the SBLINST member to start the installation.
  13. On the Install Main Menu screen, you will make a selection from the following options:

    1. Generate Siebel DDL - Generate JCL and DDL

    2. Generate View DDL - Generate VIEW DDL

    Enter 1 on the Command line to create JCL and DDL.

  14. Enter the file name for the DDL then press ENTER to generate the schema.

    For example, enter HLQ.SIEBEL.INST.SCHEMA to specify the DDL input data set.

    An edit list will appear for an SQL data set named HLQ.SIEBEL.INST.SCHEMA.SCH.

  15. Press F3 to exit after the job completes.
  16. Enter e to the left of the HLQ.SIEBEL.INST.SCHEMA.SCH data set and press ENTER.
  17. In the HLQ.SIEBEL.INST.SCHEMA.SCH data set, you will edit the following JCL files, in this order:

    DBJCL

    TSJCL

    TBJCL

    GRJCL

    First, edit the DBJCL JCL file.

    1. Enter e to the left of the DBJCL JCL file and press ENTER.
    2. Edit job card parameters in each file, for example:

    1000001 //D2PUNL0 JOB ACCNT#,CLASS=S,MSGCLASS=X,MSGLEVEL=(1,1),
    000002 // TIME=1440,REGION=0M,NOTIFY=&SYSUID,LINES=9999
    000003 //* TYPRUN=SCAN

    1. Enter an asterisk (*) to the left of the TYPRUN parameter.
    2. Enter submit on the Command line and press ENTER to submit the job.

      Make the same edits in the remaining files.

      After each job is completed, verify that the return code is 0 (zero) to make sure that no errors have occurred and that the job successfully completed.

  18. Go to Step 11 and follow the instruction to edit the HLQ.SIEBEL.INST.EXEC file to generate the View DDL file.
  19. Select 2 on the Install Main Menu screen to generate the View DDL file.
  20. Enter the DDL file name, for example, HLQ.SIEBEL.INST.DDLVIEW to specify the DDL input data set.

    Press ENTER to generate a DDLVIEW SQL data set named HLQ.SIEBEL.INST.DDLVIEW.VIE.

  21. Enter e to the left of the HLQ.SIEBEL.INST.DDLVIEW.VIE data set and press ENTER.
  22. In the HLQ.SIEBEL.INST.DDLVIEW.VIE data set, you will edit the VIEWJCL file.
    1. Enter e to the left of the VIEWJCL JCL file and press ENTER.
    2. Edit job card parameters in each file, for example:

    1000001 //D2PUNL0 JOB ACCNT#,CLASS=S,MSGCLASS=X,MSGLEVEL=(1,1),
    000002 // TIME=1440,REGION=0M,NOTIFY=&SYSUID,LINES=9999
    000003 //* TYPRUN=SCAN

    1. Enter an asterisk (*) to the left of the TYPRUN parameter.
    2. Enter submit on the Command line and press ENTER to submit the job.

      After each job is completed, verify that the return code is 0 (zero) to make sure that no errors have occurred and that the job successfully completed.

  23. Return to the mid-tier to continue the database server installation.

    Click Yes on the following screen after all the processes are complete:

    Pause #1: Please create Siebel Schema using schema.sql and ddlview.sql located in the Output Directory. Once the objects are created, please select Yes. To stop now and continue installation later please select No. (To resume at a later time, please start the Upgrade Wizard from the command line using option /m master_install_mf.ucf).

  24. Make sure that the schemvld.sql file, generated during the installation and placed in the default output directory (DBSRVR_ROOT\DB2390\DBOUTPUT\INSTALL on Windows or dbsrvr_root/db2390/dboutput/install on UNIX) or into the output directory that you designated, is empty.

    Click Yes on the following screen to complete the manual installation:

    Pause #2: Please ensure that the schemvld.sql is empty (which means that Siebel Schema was created successfully). If so, please continue with the UDFs deployment: ftp the files to install UDF functions. (To resume, please start the Upgrade Wizard from the command line using the option /m master_install_mf.ucf).

    NOTE:  If you would like to save the schema.sql file with the complete Siebel Schema DDL, which was generated for the entire Siebel Schema, rename schema.sql after you apply schema.sql on the mainframe.

Validating Siebel Schema Creation

The second pause generated the schemvld.sql and schema.sql files. The schemvld.sql file contains validation sql, based on the newly created Siebel Schema. The schema.sql file contains SQL statements for missing Siebel schema objects, if such objects are found. Normally, schema.sql is empty.

When reviewing the schema.sql file, two possible options exist:

  • The file is empty, which means the entire schema was created successfully.
  • The file is not empty, which means the schema was not created successfully.

    This is likely a result of the DBA missing a step during the manual schema creation part on the DB2 host. The file contains the missing objects of the schema (the Delta schema). The DBA needs to apply the file with the missing object to complete a full schema creation.

NOTE:  If you are creating a Siebel Schema with partitioning, the generated schema.sql file contains drop and create triggers which are acceptable and can be ignored.

Deploying User Defined Functions

There are two user defined functions (UDFs) that are provided in the Siebel application: nextseq and exrate. The nextseq() UDF generates sequential values for EIM Export and the exrate() UDF is used for currency aggregation functions. After the schema validation, UDFs must be installed. Following are the instructions on installing the UDFs during an installation.

NOTE:  You must have Workload Manager (WLM) installed and configured to run the nextseq and exrate user-defined functions.

To deploy user defined functions

  1. Navigate to the default installation output directory and modify the udfftp.txt FTP file:
    1. Change the IP address to the IP or domain name of your mainframe.
    2. Change the user ID to a valid TSO user ID.
    3. Change the SIEBELQ1 high level DSN qualifier to a valid DSN qualifier.
  2. Double-click DBSRVR_ROOT\DB2390\DBOUTPUT\INSTALL\udfftp.bat to execute the batch FTP script to send files to the mainframe (Windows), or issue the following command (UNIX):

    ftp -vn < udfftp.txt

    The script sends the following files to the mainframe from the DBSRVR_ROOT\DB2390\STORPROC\ZOS directory (Windows) or the $DBSRVR_ROOT/DB2390/STORPROC/ZOS directory (UNIX):

    • DB2390/STORPROC/ZOS/SIEBEL.UDF.RECEIVE.JCL
    • DB2390/STORPROC/ZOS/SIEBEL.UDFLOAD.XMIT
    • DB2390/STORPROC/ZOS/SIEBEL.UDF.CNTL.XMIT
    • DB2390/STORPROC/ZOS/SIEBEL.UDFDDL.XMIT
    • DB2390/STORPROC/ZOS/SIEBEL.UDFDBRM.XMIT
  3. Modify the USER_ID.SIEBEL.UDF.RECEIVE.JCL dataset according to instructions provided in this JCL file and submit it.
  4. After the jobs complete, run them in the UDF.CNTL PDS.

    Make sure that the condition equals 0.

Setting Up the SQL Delimiter on DB2

Because the file schema.sql uses a slash as a delimiter, you must set up the slash as the delimiter in the tool (SPUFI or DSNTEP2) you are using to apply the DDL.

Siebel eBusiness Applications ships files with slashes for delimiters, because schema.sql might contain triggers for table partitioning that use semicolons. For example:

CREATE TRIGGER Q202999.PTH0393 NO CASCADE BEFORE INSERT ON

Q202999.S_ACT_EMP REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL

WHEN (N.EMP_ID IS NOT NULL) BEGIN ATOMIC SET N.PARTITION_COLUMN =

RIGHT(N.EMP_ID, 2); END

/

NOTE:  If you are not using table space partitioning, it is safe to replace slashes with semicolons.

DSNTEP2

If you are going to run the DDL in batch mode, using DSNTEP2, use the parameter SQLTERM(/) to set this. The example below illustrates how this appears in the SYSTSIN DD statement.

NOTE:  In the following example, Q202 is the subsystem name.

DSNTEP2 example:

//SYSTSIN DD *

DSN SYSTEM(Q202)

RUN PROGRAM(DSNTEP2) PLAN(DSNTEP71) PARMS('/SQLTERM(/)') -

LIB('DSN710.RUNLIB.LOAD')

END

SPUFI

If you will use SPUFI, perform the following procedure.

To set the delimiter in SPUFI

  • Set CHANGE DEFAULTS to YES.

    When the CURRENT SPUFI DEFAULTS panel appears, change the SQL TERMINATOR to a slash mark.

    NOTE:  If triggers are not being used, you can change the slashes back to semicolons. If using the TSO editor, perform the following procedure.

To replace slashes with semicolons in the TSO editor

  • Enter the following command:

    CHANGE X'61' X'5E' ALL

If your platform at midtier is Microsoft Windows, return to Step 25; if your platform is UNIX, Step 31.

    
Implementing Siebel eBusiness Applications on DB2 UDB for z/OS and OS/390