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
- Navigate to the output directory
DBSRVR_ROOT \DB2390\DBOUTPUT\INSTALL (Windows) or dbsrvr_root /db2390/dboutput/install (UNIX) and open the instftp.txt file.
- Edit the instftp.txt file using the following information:
- Change
&IP into the IP address or domain name of your DB2 host, for example ZM01.
- Change
&Username into the your own user name, for example SADMIN.
- 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
- Double-click instftp.bat (Windows) or issue the following command (UNIX):
ftp -vn < instftp.txt
- Enter the password associated with the user name you entered in the instftp.txt file in Step 2.
Press any key to continue.
- 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.
- 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.
- The
DSLIST screen reflects the data sets matching the user ID entered previously.
- 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.
- 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.
- 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 .
- 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.
- Enter
exec to the left of the SBLINST member to start the installation.
- 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.
- 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.
- Press F3 to exit after the job completes.
- Enter
e to the left of the HLQ .SIEBEL.INST.SCHEMA.SCH data set and press ENTER.
- 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.
- Enter e to the left of the DBJCL JCL file and press ENTER.
- 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
- Enter an asterisk (*) to the left of the TYPRUN parameter.
- 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.
- Go to Step 11 and follow the instruction to edit the
HLQ .SIEBEL.INST.EXEC file to generate the View DDL file.
- Select 2 on the Install Main Menu screen to generate the View DDL file.
- 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.
- Enter
e to the left of the HLQ .SIEBEL.INST.DDLVIEW.VIE data set and press ENTER.
- In the
HLQ .SIEBEL.INST.DDLVIEW.VIE data set, you will edit the VIEWJCL file.
- Enter e to the left of the VIEWJCL JCL file and press ENTER.
- 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
- Enter an asterisk (*) to the left of the TYPRUN parameter.
- 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.
- 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).
- 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.sq l 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:
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
- Navigate to the default installation output directory and modify the udfftp.txt FTP file:
- Change the IP address to the IP or domain name of your mainframe.
- Change the user ID to a valid TSO user ID.
- Change the
SIEBELQ1 high level DSN qualifier to a valid DSN qualifier.
- 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
- Modify the
USER_ID .SIEBEL.UDF.RECEIVE.JCL dataset according to instructions provided in this JCL file and submit it.
- 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.
|