Bookshelf v7.5: Applying DDL on the DB2 Host

Implementing Siebel eBusiness Applications on DB2 UDB for OS/390 and z/OS > 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.

Step 1: Transferring Files to the DB2 Host

Follow either one of the procedures below to preallocate space on the mainframe for the schema.sql and ddlview.sql files. This is a crucial step due to the size of the schema.sql file.

Method 1: Preallocating Space on DB2 Host using FTP Commands

The following FTP commands can be used to preallocate space. These commands are issued in the instftp.txt file transfer protocol (FTP) script that was installed in the output directory that you designated in Step 14 (for Windows) or Step 17 (for UNIX).

To preallocate space on DB2 Host using FTP commands

Enter the following FTP commands:

quote site cylinders primary=25 secondary=2

quote site recfm=fb lrecl=80 blksize=0

put schema.sql

Method 2: Preallocating Space Directly on DB2 Host

If you do not use the FTP script (instftp.txt) to transfer the files to the mainframe, you can use the following procedure to preallocate space on the DB2 Host.

To preallocate space directly on DB2 Host

Access the Data Set Allocation panel, using ISPF option 3.2.

From the Data Set Allocation panel, allocate CYLINDERS as the space unit.

Allocate a quantity corresponding to the size of the schema.sql file (estimated at 25 cylinders).

For Record Format, select FB.

For record length, select 80.

NOTE: If you will use SPUFI to execute the entire schema.sql DDL file (without splitting it into smaller files using the DDLSPLIT rexx exec), you must verify that the SPUFI output data set is allocated approximately three times more space than the data set allocated for schema.sql.

Step 2: Splitting the DDL

To split the DDL into multiple members in a PDS, you will use REXX DDLSPLIT (filename ddlsplit.txt). The input files for REXX DDLSPLIT are the DDL files generated by the installation process. The output from ddlsplit are multiple members in a PDS. DDLSPLIT creates the following members:

DB - Create database statements.

TS - Create table-space statements.

TB - Create table and index statements.

GRANT - All grant statements.

The DDLSPLIT rexx exec must be in a data set allocated to sysproc or sysexec.

When you are ready to execute DDLSPLIT, execute the following command on the TSO Command Processor panel (option 6 on most site installations):

TSO DDLSPLIT

If the REXX is not in a dataset allocated to sysproc or sysexec, then the entire dataset name must be specified; for example:

TSO EXEC 'SIEBEL.EXEC(DDLSPLIT)'

DDLSPLIT will prompt for the input dataset and the commit frequency. It will remove the existing commit statement, and it will commit at the specified frequency. If no frequency is specified, it will default to one (1). The DDLSPLIT rexx exec process dynamically allocates the output partitioned dataset.

After you create the storage control file, you are ready to run the database installation to create the DDL.

Step 3: Executing the SQL to Create the Siebel Schema

The DBA must now execute schema.sql and ddlview.sql on the DB2 host. You can use any DB2 tool (such as SPUFI or DSNTEP2) that you prefer to do this. It is recommended that you run in batch so that you do not restrict your TSO session. After the transfer, the DBA can edit the files, as desired, and then execute them, using any customary method.

You can use DDLSPLIT and REXX exec to split the schema.sql DDL file into manageable pieces for execution on the host. In order to use the DDLSPLIT utility, you need to copy the file named ddlsplit.txt from the dbsrvr/DB2390/DBOUTPUT/INSTALL directory to the mainframe.

Step 4: 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 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 30.

Implementing Siebel eBusiness Applications on DB2 UDB for OS/390 and z/OS
Published: 18 April 2003