Bookshelf Home | Contents | Index | Search | PDF |
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
orsysexec
.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
orsysexec
, 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 theSYSTSIN 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.
Bookshelf Home | Contents | Index | Search | PDF |
Implementing Siebel eBusiness Applications on DB2 UDB for OS/390 and z/OS Published: 18 April 2003 |