11Migrating a Siebel Database to Unicode Format

Migrating a Siebel Database to Unicode Format

This chapter describes how to migrate your Siebel database from an ASCII or EBCDIC encoding format to a Unicode encoding format.

This chapter consists of the following topics:

• About Migrating a Siebel Database to Unicode Format

• Roadmap for Migrating the Siebel Database to Unicode

• Requirements for Migrating the Siebel CRM Database to Unicode

• Generating the Unicode Migration Files

• Process of Preparing the z/OS Host Environment

• Process of Performing the Database Unicode Migration

• Viewing the Log File

About Migrating a Siebel Database to Unicode Format

You can migrate your Siebel database from either an ASCII or EBCDIC encoding system to a Unicode encoding system if the following conditions are met:

• You are using a Innovation Pack 2017 with DB2 Version for z/OS.

• You are migrating to a DB2 version for z/OS subsystem.

When you migrate your Siebel schema from an ASCII or EBCDIC encoding system to a Unicode encoding system, all of the source tables that are marked as active in the repository are migrated to new target tables which are in Unicode format. Tables that are defined as inactive in the repository are not built in the Unicode target schema.

Migrating to Unicode involves the following steps:

1. Innovation Pack 2017 supports Unicode UTF-16 encoding, that is, it uses a double-byte character encoding scheme. To migrate to Unicode, Siebel database utilities first convert the existing schema to a schema that supports UTF-16.

2. Siebel CRM database utilities then build SQL CREATE schema control cards and JCL templates, which are transferred to the z/OS host and applied to create the target database schema.

3. Finally, data is migrated from the source ASCII or EBCDIC database to the target (Unicode) database using Cross Loader SQL control cards; these are built using options available on the Siebel Unicode Migration Option Menu on the z/OS host.

Caution: Migrating to Unicode is more complex than simply importing your existing data into a Unicode database. Failure to execute the migration correctly can result in serious data corruption or unrecoverable data loss. For this reason, the participation of Oracle’s Application Expert Services is mandatory if you perform a Unicode migration. Contact your Oracle sales representative for Oracle Advanced Customer Services to request assistance from Oracle’s Application Expert Services.

About Unicode Storage Requirements

The use of the Unicode UTF-16 encoding format will increase the storage required by your Siebel database. During the Unicode migration process, data types in the Siebel ASCII or EBCDIC database are mapped from single-byte to double-byte characters, as shown in the following table.

Table Data Type Mappings During Unicode Migration

Data Types on ASCII and EBCDIC Databases on Z/OS	Data Types on Unicode Databases on Z/OS
Char	Graphic
Varchar	Vargraphic
Longvarchar	Longvargraphic
Clob	DBClob

Moving from single-byte to double-byte characters causes some Siebel table spaces to exceed their page sizes and causes some tables to exceed the 32-KB limit; these tables will require LOB columns in the Unicode database.

To help you estimate the increase in storage that can occur when you migrate from a code page to a Unicode database, The following table shows the number of table spaces and the storage required for a sample SIA EBCDIC database, and the storage and table spaces required by the same database when it has been migrated to Unicode.

Note: The figures in the following table are provided as a guideline only. The increase in storage required by your actual Unicode database might be greater or lesser than indicated for the sample database.

Table Table Spaces and Storage Required by Sample Siebel SIA Databases

Siebel SIA Database	No of 4-KB Table Spaces	No of 8-KB Table Spaces	No of 16-KB Table Spaces	No of 32-KB Table Spaces	Total Space Required
EBCDIC	4229 (2,759,008 KB)	331 (312,112 KB)	171 (62,272 KB)	105 (52,160 KB)	3,185,552 KB
Unicode	3941 (2,507,584 KB)	405 (327,776 KB)	345 (358,992 KB)	247 (119,712 KB)	3,314,064 KB

Siebel Business Applications use COMPRESS set to YES as the default for table space definitions; setting the COMPRESS parameter to YES reduces the storage requirements for table spaces.

About the NOT PADDED Clause

To minimize any increase in storage when you migrate your database to Unicode, indexes in the Siebel Schema are defined with the NOT PADDED clause by default when you convert your storage control file to Unicode using the Siebel Database Storage Configurator as described in Converting the Storage Control File to Unicode.

When the NOT PADDED clause is specified:

• Varying-length columns of an index are not padded to their maximum length; DB2 stores only the key data, which reduces the storage required by the index key.

• VARGRAPHIC columns of an existing index are stored as varying-length columns.

  If the NOT PADDED clause is not specified, VARGRAPHIC columns are stored as fixed-length columns.

  You can specify that indexes are to be NOT PADDED by default by specifying the PADIX DSNZPARM. You can also use the PADDED or NOT PADDED clauses in the CREATE INDEX or ALTER INDEX statements to specify whether an individual index is padded or not.

Differences Between ASCII, EBCDIC and Unicode Sort Order

The EBCDIC, ASCII, and Unicode encoding systems each use a different sort order for numbers, upper case alpha characters, lower case alpha characters, and special characters. As a result, if you load a partitioned table based on these characters, rows can be assigned to different partitions in the target table than they were in the original ASCII or EBCDIC source table, which might lead to unbalanced partitions in your database. You must determine if the partitioning keys need to be reevaluated when you migrate to Unicode.

Roadmap for Migrating the Siebel Database to Unicode

To migrate a Siebel CRM database from an ASCII or EBCDIC encoding system to a Unicode encoding system, complete the following tasks and processes:

1. Perform the Unicode migration prerequisite tasks described in Requirements for Migrating the Siebel CRM Database to Unicode.

2. Use the Unicode Migration option of the Database Configuration Wizard and the Upgrade Wizard to generate the schema-related files for the target (Unicode) database.

   For information on this task, see Generating the Unicode Migration Files.

3. Transfer the Unicode migration files to the z/OS host and set up the host environment as described in Process of Preparing the z/OS Host Environment.

4. Process of Performing the Database Unicode Migration.

Requirements for Migrating the Siebel CRM Database to Unicode

The tasks described in this topic are a step in the Roadmap for Migrating the Siebel Database to Unicode.

Before you can migrate your Siebel CRM ASCII or EBCDIC database to a Unicode database, you must perform the prerequisite tasks described in this topic.

1. Install Innovation Pack 2017.

2. Ensure that the target environment you are migrating to is a DB2 for z/OS subsystem. The Unicode data conversion process fails if these minimum requirements are not met.

3. Verify that the Group codes in the Siebel Repository and the DB2 Catalog are consistent.

   Review the Group names in the schema.ddl file for your existing ASCII or EBCDIC database and verify that they match the table space names in the storage control file for your existing database. If they match, proceed to Step 4. If they do not match, do the following:

   1. Run the following SQL statement to update S_TABLE with the group codes from the catalog:

           UPDATE source_tableowner_name.S_TABLE A
           SET A.GROUP_CD = (SELECT B.TSNAME
           FROM SYSIBM.SYSTABLES B
           WHERE A.NAME = B.NAME AND B.CREATOR = 'source_tableowner_name')
      

      where source_tableowner_name is your Siebel schema qualifier name.

   2. Remove the diccache.dat file from your SIEBSRVR_ROOT\bin directory (Windows) or the SIEBSRVR_ROOT/bin directory (UNIX).

4. Extract the storage control file for your existing Siebel ASCII or EBCDIC database.

   The extracted file contains definitions of the physical layout of your existing Siebel database and is used to generate the DDL to build the Unicode database schema.

   For information on extracting a storage control file using the Database Configuration Utility, see Extracting a Storage Control File from the DB2 Catalog. Ensure that you specify the following values:

   • Extract method. Select the Extract from Catalog option, not the Extract from Catalog and Merge with Template option

   • Database values. Specify database parameter values for your existing Siebel ASCII or EBCDIC database

   • Storage Control File. You can assign any name to the storage control file that is extracted, for example, storage_ascii.ctl or storage_ebcdic.ctl

5. Extract the schema.ddl file for your existing Siebel ASCII or EBCDIC database.

   Ensure that your existing ASCII or EBCDIC Siebel database and repository are synchronized, then extract the schema.ddl file for your database. The schema.ddl file contains the logical definitions for all Siebel tables and indexes for the current version of your schema and is used as an input parameter by the Database Configuration Wizard when you select the Unicode Migration option.

   1. Synchronize your Siebel database and repository using the Database Configuration Utility.

      For information on this task, see Synchronizing Siebel Repository Definitions and the Physical Siebel Schema.

      Ensure that you specify the following values:

      • Run Database Utilities option. Select the Synchronize Schema Definition option of the Database Configuration Wizard

      • Storage Control File. Specify the name of the storage control file that you extracted in Step 4, for example, storage_ascii.ctl or storage_ebcdic.ctl

      The Synchronize Schema Definition process generates the synchronized schema.ddl file and places it in the DBSRVR_ROOT\db2390 (Windows) or DBSRVR_ROOT/db2390 (UNIX) directory.

   2. Rename the schema.ddl file to one of the following, depending on the code page of your existing database:

      • ddl_ascii.ctl

      • ddl_ebcdic.ctl

6. Convert the storage control file to Unicode.

   Convert the storage control file you extracted in Step 4 to Unicode format using the Siebel Database Storage Configurator (dbconf.xls). The converted storage control file is then used as an input parameter by the Database Configuration Wizard when you select the Migrate to Unicode option. For information on completing this task, see Converting the Storage Control File to Unicode.

Converting the Storage Control File to Unicode

As part of the process of migrating your ASCII or EBCDIC database to a Unicode format, you must convert the physical layout of your source database so that it supports Unicode. This involves expanding the size of the columns in the schema tables; the extent to which a column must be expanded is determined by the column type and length.

Once you have converted the storage control file to Unicode, you must validate the converted file. This topic outlines both tasks.

To convert the source storage control file to Unicode and validate it

1. Open the Siebel Database Storage Configurator as described in About Modifying Storage Control Files.

2. Click the Import tab, navigate to the directory containing your existing Siebel schema storage control file (the file you extracted in Step 4), and double-click this file to import it.

3. When the import process is completed, a message appears stating that you have imported the storage control file successfully. Click OK.

4. When the message, Please enter default values for your system appears, accept the default values and click Set.

5. You are prompted to indicate whether you want to import row lengths. Click Yes.

6. Navigate to the directory containing the renamed schema.ddl file for your schema (for example, ddl_ascii.ctl or ddl_ebcdic.ctl) and double-click this file to import it (this is the file you extracted and renamed in Step 5).

   The Siebel Database Storage Configurator reads the ddl_codepage.ctl file to determine the type and length of each table column in the schema.

7. When the ddl_codepage.ctl file is imported successfully, click OK.

8. When the message Would you like to import row counts is displayed, select No.

9. Click the Structures tab, then the Database tab.

10. (Optional) Change the database name prefix of all the databases listed (for example, SIDB) to the database name prefix you want to use in your Unicode schema (for example, SIEB). The database name prefix must be the same for all database objects in the Siebel schema.

11. Click Functions, and then Convert to Unicode.

12. Click the Convert button.

    If a message is displayed stating that the spreadsheet has already been converted to Unicode, click the Structures tab and change entries in the CCSID column from Unicode to ASCII, then select the Convert to Unicode tab again.

13. When the storage control file has been converted, click the Home tab and then click the Export button.

14. Rename your converted storage control file, for example, storage_uni.ctl, and save it. The Siebel Database Storage Configurator validates the file.

15. Click OK and a storage control file is created for your Siebel database with an expanded Unicode schema.

16. If errors occur while the Unicode storage control file is being exported, a message is displayed and the values that need to be reviewed are highlighted.

    Make any corrections that are required before exporting the file again. For further information on this task, see Modifying a Storage Control File Using the Database Storage Configurator.

17. Validate the converted Unicode storage control file using the Database Configuration Utility Validate Storage File option.

    For information on validating a storage control file using the Database Configuration Utility, see Validating an Extracted or Modified Storage Control File.

    Ensure that you specify the following values:

    • Configure Database option. Select the Validate Storage File option

    • Batch or Foreground Mode. Choose the Batch - Generate Unload/Load option

    • Database values. Specify database parameter values for your existing Siebel ASCII or EBCDIC database

    • Schema File. Specify the ddl_codepage.ctl file you extracted in Requirements for Migrating the Siebel CRM Database to Unicode

    • Storage Control File. Specify the storage control file you have just converted to Unicode, for example, storage_uni.ctl

18. Depending on the outcome of the validation process, do one of the following:

    • If the validation process completes successfully, proceed to Generating the Unicode Migration Files.

    • If the validation process fails because of buffer pool errors, follow the procedure in Recovering from Buffer Pool Errors, then run the validation process again.

      Errors generated during the validation process are written to a log file in the SIEBSRVR_ROOT\log\dbconfig_validate_mf\output directory (Windows) or the SIEBSRVR_ROOT/log/dbconfig_validate_mf/output directory (UNIX).

Recovering from Buffer Pool Errors

If the validation of the converted Unicode storage control file fails because the buffer pool sizes specified in the storage control file are too small, you must correct the errors in the storage control file and run the validation process again. This topic describes how to correct buffer pool errors.

To recover from buffer pool errors

1. Open the Siebel Database Storage Configurator as described in About Modifying Storage Control Files.

2. Import the storage control file you previously converted to Unicode (storage_uni.ctl) as described in Converting the Storage Control File to Unicode.

   Note: When importing the Unicode storage control file, ensure you specify No when you are prompted to select whether or not you want to import row lengths.

3. Click Functions, and then Tools.

4. Click the Repair BP Validation button.

5. Navigate to the directory containing the log file listing the buffer pool errors and open this file. The default directory is SIEBSRVR_ROOT\log\dbconfig_validate_mf\output (Windows) or SIEBSRVR_ROOT/log/dbconfig_validate_mf/output (UNIX).

6. The Siebel Database Storage Configurator updates the buffer pool sizes. When this process completes successfully, click OK.

7. Export the updated file, save it, and validate it again as described in Converting the Storage Control File to Unicode.

   Note: Ensure that you save the file with the same name it had when you imported it.

Generating the Unicode Migration Files

When you have extracted the original schema ddl.ctl file and converted the storage control file to Unicode for your ASCII or EBCDIC database, run the Database Configuration Utilities to generate the new Unicode schema files at the midtier.

The Database Configuration Wizard builds the new schema by reading the current schema and making the necessary adjustments to data types to convert the schema to a Unicode format. The utility also builds the load and unload control cards required to perform the migration process on the z/OS host.

This task is a step in Roadmap for Migrating the Siebel Database to Unicode.

To generate the Unicode schema files

1. Launch the Database Configuration Wizard and follow the steps in Performing a Standard Installation until the Siebel Database Operation screen is displayed Step 6).

2. From the Siebel Database Operation screen, select Run Database Utilities, and then click Next.

3. Select the Unicode Migration option, and click Next.

4. Choose the mode you want to use for the Unicode migration process.

   • Batch - Generate Unload/Load. Allows you to generate the Unicode migration files in batch mode. Select this option.

   • Foreground - Generate Insert/Select. Allows you to generate the Unicode migration files in foreground mode.

5. If you have installed more than one language pack onto the Siebel Server, the Base Language screen is displayed. Specify which language is the primary (base) language for the Siebel database, and click Next.

6. Enter the ODBC Data Source Name to connect to the target Unicode database. Click Next.

7. Enter the database user name for the target Unicode database. Click Next.

8. Enter the password associated with the database user name for the target Unicode database. Re-enter the password to confirm, then click Next.

9. Enter the Security Group ID/Grantee name, for example, SSEROLE, then click Next.

10. Enter the schema qualifier for the target database, and click Next.

11. On the Host/LPAR name where Target database resides screen, enter the name of the host and logical partition that will contain the new Unicode database to be migrated, for example ZM01. Click Next.

12. Enter your TSO account ID, then click Next.

    This account ID must have the authorization to allocate and create data sets on the z/OS host.

13. Enter the dataset high-level qualifier you want to use for the z/OS Unicode migration data sets, then click Next. Follow your organization’s naming standards.

14. Enter the name of the directory where you want to save the migration DDL files generated. The default directory is DBSRVR_ROOT\db2390\dboutput\unicode_migration (Windows) or DBSRVR_ROOT/db2390/dboutput/unicode_migration (UNIX).

    Click Next.

15. Specify the schema.ddl file you generated in Step 5 (ddl_codepage.ctl), then click Next.

16. Enter the path and name of the storage control file you converted to Unicode (for example, storage_uni.ctl) as described in Converting the Storage Control File to Unicode.

17. Specify the name of the Log Output Directory and click Next.

    By default, the files are created in SIEBSRVR_ROOT\log\unicode_migration_mf (Windows) or SIEBSRVR_ROOT/log/unicode_migration_mf (UNIX).

18. Save the configuration information you have entered and launch the Siebel Upgrade wizard as described in the following topics:

    • Running the Database Configuration Wizard Under Windows

    • Running the Database Configuration Wizard Under UNIX

19. Click OK.

    The Upgrade Wizard creates a number of files in the output directory you specified.

20. Apply these files on your z/OS host as described in Transferring Migration Upgrade Files from the Midtier to the z/OS Host.

Process of Preparing the z/OS Host Environment

This process is a step in the Roadmap for Migrating the Siebel Database to Unicode.

After generating the files required to perform the Unicode migration on the midtier, you must move these files to the z/OS host where they are used to create the data sets required to perform the Unicode migration.

To prepare the z/OS environment for the Unicode migration, perform the following tasks.

1. Transferring Migration Upgrade Files from the Midtier to the z/OS Host

2. Creating and Allocating the Setup Data Sets

3. Defining Environment Variables for the Unicode Migration

Transferring Migration Upgrade Files from the Midtier to the z/OS Host

When you run the Unicode Migration option of the Database Configuration Wizard, files are generated in the midtier output directory that you specified. You must now transfer these files from the midtier to the z/OS host where they can be executed.

This task is a step in Process of Preparing the z/OS Host Environment.

To transfer the Unicode migration files to the DB2 host

1. Navigate to the output directory you specified in Step 14:

   Windows: The default output directory is DBSRVR_ROOT\db2390\dboutput\unicode_migration

   UNIX: The default output directory is DBSRVR_ROOT/db2390/dboutput/unicode_migration

2. Double-click Uni_Jobsync.bat (Windows) or issue the following command from UNIX:

   ftp -vn < Uni_ftpsync.txt
   

3. When prompted to do so, enter the password for the TSO user name that you specified in Generating the Unicode Migration Files, then press any key to continue.

   The files are sent to the z/OS host and the log file, Uni_ftpsync.log, is created in your log output directory.

4. Verify that all the files referenced in the Uni_ftpsync.txt file in your Unicode migration output directory have been transferred to z/OS data sets.

Creating and Allocating the Setup Data Sets

After transferring the Unicode migration files to the z/OS host, you must generate and allocate the data sets used in the migration process. The following procedure describes how to do this.

This task is a step in Process of Preparing the z/OS Host Environment.

To create and allocate the Unicode migration data sets

1. Log on to the target z/OS host where the Unicode database is to be migrated. Ensure that the Procedure parameter on the logon panel is set to DB2REL8.

2. To create the Unicode migration data sets, navigate to the DSNHLQ.SIEBEL.UNI.JOB0 data set.

3. Go to Edit mode on the data set and modify the job card, if necessary. Submit the job by typingsubmit on the command line, and pressing Enter.

4. Verify that the job ran successfully (RC is 0) and that it created the DSNHLQ.SIEBEL.UNI.EXEC data set. This data set contains the executable code for the Unicode migration.

5. To allocate the DSNHLQ.SIEBEL.UNI.EXEC REXX exec library on the ISPF Primary Option menu, select option 6 to go to the TSO command line processor panel.

6. Issue the following command to allocate the CLIST/EXEC library:

   Alloc f(sysexec) da(‘DSNHLQ.SIEBEL.UNI.EXEC’) shr reuse
   

7. Press Enter. The EXEC library is allocated.

   The CLIST/EXEC DSNHLQ.SIEBEL.UNI.EXEC library is used to execute all REXX execs. The panel and programs are located in this library.

Tip: The TSO command allocation for the EXEC library must be reissued with each TSO logon. Add this allocation to your personal logon CLIST to avoid having to reallocate the DSNHLQ .SIEBEL.UNI.EXEC library with each new TSO logon.

Defining Environment Variables for the Unicode Migration

After creating the z/OS data sets required to run the Unicode migration, you have to set up the z/OS system environment variables appropriately for the migration process. The following procedure describes how to perform this task.

This task is a step in Process of Preparing the z/OS Host Environment.

To set up the migration environment on the z/OS host

1. Display the Siebel Unicode Migration Option Menu panel (panel ID is UNIMIG8P) by navigating to the TSO command line processor panel, then entering the following command:

   UNIMIG82
   

2. To define the environment variables that will be used for the Unicode migration process, select option 1: Environment Setup and press Enter.

3. The Unicode Migration Environment Setup Menu appears. The panel ID is SBLSSETP.

4. Select option 1: Set System Environment Variables, and press Enter. The System Variable Definitions panel is displayed (panel id SBLSETVP).

5. Enter values in the following fields, or amend any of the values that are displayed, as appropriate for your environment.

   • Source DDL From: Create Target On:

     Enter information in the following fields to identify your source and target databases:

     • Host/LPAR Name. Enter the source and target MVS subsystem LPAR identifiers. For a subsystem to subsystem migration, the MVS source and target LPAR names can be the same or different, depending on where the DB2 subsystems reside. For a same subsystem migration, the MVS source and target LPAR names must be the same.

     • DB2 Subsystem. Enter the DB2 subsystem identifier (SSID) for the source and target databases. For a subsystem to subsystem migration, the source and target SSIDs must be different. For a same subsystem migration, the source and target SSIDs must be the same.

     • Table Owner. Enter the character IDs that identify the source and target Siebel Schema table owners. The source and target table owner names must be different.

   • Source DB2 Libraries: Target DB2 Libraries

     The default DB2 library names are displayed for your source and target databases. Amend these values as necessary.

6. Press PF3 and the values you entered are saved in the EXEC library.

7. On the Unicode Migration Environment Setup Menu select option 2: Unpack JOB1, and press Enter.

8. Submit the JCL in the data set DSNHLQ.SIEBEL.UNI.JOB1.

9. Verify that the job ran successfully (RC is 0) and that the following data set libraries were created:

   • DSNHLQ.UNI.INSTALL.JCL

   • DSNHLQ.UNI.JCLLIB

   • DSNHLQ.UNI.LOAD

   • DSNHLQ.UNI.PROC

10. On the Unicode Migration Environment Setup Menu select option 3: Set Jobcard Variables, and press Enter. The Jobname Prefix/Parm Definitions Jobcard Parameters panel appears; the panel ID is SBLJPXP.

11. Specify values for the following fields:

    • Accntg. Verify that you are using the correct Accounting class.

    • Notify. You can change the NOTIFY value from &SYSUID to your TSO ID or leave it as &SYSUID.

      Note: You can remove the notify parameter from the job card if you choose.

    • Scheduling. Enter either 1: Siebel Scheduled mode or 2: Vendor Scheduled mode. For information about choosing a scheduler, see Siebel Database Upgrade Guide for DB2 for z/OS.

12. Enter a three-character job name prefix for the following Unicode migration job types:

    • Cross Loader

    • Rebuild Indexes

    • RunStats

    It is recommended that the three-character prefix is unique as this makes it easier to find your jobs in the queue but this is not a requirement. The remaining five characters of the job name are defined by Oracle and are unique across all Unicode migration jobs.

13. Press Enter after entering the job name prefix and parameter definitions. The JCL template files are updated. Messages indicate when each step is completed.

14. On the Unicode Migration Environment Setup Menu, select option 4: Verify target environment, and press Enter.

    A process is run to determine whether or not any objects exist in the target environment. If objects are found, you must delete them before proceeding.

    Note: Running option 4: Verify target environment, might not locate all objects in a target environment. It is recommended that you perform additional checks to verify that your target environment is empty.

15. On the Unicode Migration Environment Setup Menu, select option 5: Build Log Table, and press Enter.

    This option creates and loads the temporary logging table for the Unicode migration jobs.

16. Submit the JCL in the data set DSNHLQ.SIEBEL.UNI.INSTALL.JCL(CREATLOG) to create the logging table.

    The DSNHLQ.SIEBEL.SBLLOG.LOADFILE file is also created; this file loads the initial set of jobs that were transferred to the z/OS host.

17. Verify that the job ran successfully (RC is 0). You can review the output in SDSF or another job output facility.

    The Environment Setup is now completed.

18. Press PF3 to return to the Siebel Unicode Migration Option Menu panel.

    You can now proceed to build the target schema. For information on this task, see Building the Target Schema.

Process of Performing the Database Unicode Migration

This process is a step in the Roadmap for Migrating the Siebel Database to Unicode.

Once you have prepared your z/OS host system, you can migrate your ASCII or EBCDIC Siebel schema to a Unicode format by performing the following tasks in the sequence in which they are listed:

1. Building the Target Schema

2. Building the Unicode Migration Jobs

3. Submitting the Unicode Migration Jobs

Building the Target Schema

Complete the following procedure to build the target (Unicode) schema. When you have completed this task, you can then migrate data from the ASCII or EBCDIC schema to the Unicode schema.

This task is a step in the Process of Performing the Database Unicode Migration.

To build the target schema

1. On the Siebel Unicode Migration Option Menu, select option 2. Pre-Migration. The Unicode Pre-Migration Menu panel is displayed. The panel ID is SBLUPREP.

2. Select option 1. Build Target Schema to create the Unicode schema on the target database.

3. Submit the JCL in the data set DSNHLQ.SIEBEL.UNI.INSTALL.JCL(CRESCHEM).

   The Build Target Schema process creates the Unicode databases, table spaces, and tables in the target DB2 subsystem.

4. On the Unicode Pre-Migration Menu, select option 2. Verify Target Schema, and press Enter.

   The Unicode migration tool reads table information from the source and target schemas, and compares the schemas to ensure that they contain the same tables.

   Differences between the schemas are logged in the DSNHLQ.SIEBEL.UNI.DIFFRENT data set. Review this file and resolve the cause of any differences noted.

   Note: Objects in the source schema that are defined as inactive in the repository are not built in the target schema.

   The build target schema process is now completed.

Building the Unicode Migration Jobs

Once you have built your Unicode schema, you must build the appropriate DB2 Cross Loader, index rebuild, and Runstats jobs that are required to migrate your source database to your Unicode database. This topic describes how to build the data migration jobs.

This task is a step in the Process of Performing the Database Unicode Migration.

To build the Unicode migration jobs

1. On the Siebel Unicode Migration Option Menu, select option 2. Pre-Migration, and press Enter.

2. On the Unicode Pre-Migration Menu, select option 3. Build Jobs, and press Enter. The Unicode Pre-Migration Build Job Menu is displayed. The panel ID is SBLBLDJP.

3. Select option 1. Build Cross Loader Jobs, and press Enter.

4. Submit the JCL in the data set DSNHLQ.SIEBEL.UNI.INSTALL.JCLLIB(SBLJCL23) to generate the data migration jobs.

5. After submitting the job, enter cancel on the command line or press PF3 to save changes.

6. Verify that the job ran successfully (RC is 0), and that a data set, DSNHLQ.SIEBEL.DDLIMP.UNLOAD.CNTL, was created.

   This data set contains the Cross Loader cards and jobs that copy the data from the source to the target database.

7. On the Unicode Pre-Migration Build Job Menu, select option 2. Build Index Rebuild Jobs, and press Enter.

   A message appears, asking you to confirm that the DSNHLQ.SIEBEL.PROC(ISPBAT) (ISPF batch procedure) is installed.

8. Enter Y to confirm that the ISPF batch proc is installed, and press Enter.

9. When the following message appears, specify the number of indexes to be included in each rebuild job, and press Enter:

   NUMBER OF INDEXES PER REBUILD JOB.
   

   The maximum number of indexes that can be included in a job is 10.

   Consider your objective before choosing a maximum number of indexes for each job. Increasing this number results in fewer jobs but requires more memory and sort work. Reducing this number results in more jobs, which reduces resource requirements but causes fewer indexes to be built in parallel.

10. Submit the JCL in the data set, DSNHLQ.SIEBEL.UNI.INSTALL.JCLLIB(SBLJCL24).

    Note: This job builds the Index Rebuild jobs for unique, non-unique, and obsolete indexes and can take an extended period of time to complete (up to an hour or more).

11. Verify that the job ran successfully (RC is 0) and that the following data sets were created:

    • DSNHLQ.SIEBEL.UNI.RBLDOIX.JCL

    • DSNHLQ.SIEBEL.UNI.RBLDOIX.SQL

    • DSNHLQ.SIEBEL.UNI.RBLDSIX.JCL

    • DSNHLQ.SIEBEL.UNI.RBLDSIX.SQL

12. On the Unicode Pre-Migration Build Job Menu, select option 3. Build Runstat Jobs, and press Enter.

    The Upgrade RUNSTATS panel is displayed (the panel ID is SBLRSP).

13. Read the information relating to the RUNSTATS jobs on the Upgrade Runstats panel, then press Enter.

14. Submit the JCL in the data setDSNHLQ.SIEBEL.UNI.INSTALL.JCLLIB(SBLRS) to start the RUNSTATS job generation process. When the process is completed, the Runstat jobs are placed in the DSNHLQ.SIEBEL.UNI.RUNST data set.

15. Press PF3 twice to return to the Unicode Pre-Migration Menu.

    Note: Before proceeding to Step 16 to load the log table, ensure that all of the Build jobs have finished running and that they completed successfully (RC is 0).

16. Select option 4. Load Target Schema to load the Siebel logging table with the Unicode migration jobs to be executed against the target schema. Press Enter.

17. Submit the job using the JCL in data set DSNHLQ.SIEBEL.UNI.INSTALL.JCL(LOADLOG2).

18. Verify that the job ran successfully, and that additional log entries for Cross Loader, index rebuild, and runstats jobs have been loaded to the log table (TMP_SBLLOG_UNI).

    The process of building the Unicode migration jobs is completed.

Submitting the Unicode Migration Jobs

When you have built your Unicode schema and generated the migration jobs, you can migrate data from the ASCII or EBCDIC Siebel schema to the new Unicode schema by submitting the appropriate DB2 Cross Loader jobs. Then you must create indexes for the new Unicode tables. This topic describes how to perform both of these tasks, and how to generate statistics for the new Unicode schema.

This task is a step in the Process of Performing the Database Unicode Migration.

Note: Ensure that all of the pre-migration jobs have completed successfully before you submit the Unicode migration jobs described in this topic.

To submit the Unicode migration jobs

1. On the Siebel Unicode Migration Option Menu, select option 3. Migration, and press Enter. The Unicode Migration Menu is displayed (the panel ID is SBLUMIGP). The options on the Unicode Migration Menu are used to submit the jobs that perform the schema Unicode migration.

   Submit the jobs in the sequence in which they are listed on the menu. The indexes are built after the Cross Loader jobs are completed for performance reasons. If the indexes are built before the Cross Loader jobs are run, the load jobs take much longer.

2. Select option 1. Submit Cross Loader Jobs, and press Enter. The Cross Loader jobs migrate the data from the source to the target Unicode database.

3. Submit the JCL in the data set DSNHLQ.SIEBEL.UNI.INSTALL.JCL(SUBXLOAD).

   If one of the Cross Loader jobs ends abnormally during the data migration process, restart the job as described in Resubmitting Cross Loader Jobs.

   When all the jobs are successfully executed, the Unicode tables are loaded with data from the source schema.

4. To create unique, nonunique, and obsolete indexes for the loaded Unicode tables, select option 2. Build Indexes, and press Enter.

5. Submit the JCL in the data set DSNHLQ.SIEBEL.UNI.INSTALL.JCL(CRESCHIX).

6. Verify that the job ran successfully (RC is 0 or RC is 4).

   Indexes are created on the tables in the Unicode schema.

7. To populate the indexes, select option 3. Submit Rebuild Index Jobs, and press Enter.

8. Submit the JCL in the data set DSNHLQ.SIEBEL.UNI.INSTALL.JCL(CRESCHEM).

9. Verify that the job(s) ran successfully (RC is 0 or RC is 4).

   The indexes on the Unicode schema are rebuilt.

10. To collect statistics for the table spaces in the new Unicode schema, select option 4. Submit RunStat Jobs, and press Enter.

11. Submit the JCL in the data set DSNHLQ.SIEBEL.UNI.INSTALL.JCL(SUBRUNST).

    Verify that the job(s) ran successfully (RC is 0 or RC is 4). RUNSTATS jobs are run on all table spaces in the Unicode schema.

12. Press PF3 to save changes and return to the Siebel Unicode Migration Option Menu.

    The Unicode migration process is now completed.

Resubmitting Cross Loader Jobs

This topic describes how to resubmit Cross Loader jobs that fail during the Unicode migration process.

Each Cross Loader job consists of two steps: a main step, and a verification step.

The start of the main step, which executes the Cross Loader job, is indicated as follows:

//XLD000 

where 000 is the step number.

The main step is followed by the verification step, which has the following format:

//XLD00000    INCLUDE MEMBER=. 

where //XLD00000 is the XLOADER card member number.

If one of the Cross Loader data migration jobs ends abnormally, correct the problem that caused the job to fail and then resubmit the job. Do not, however, rerun steps in the job that have already executed successfully; doing so causes the job to fail again, creates duplicate data in the affected table, and causes the index rebuild jobs to fail.

To restart a Cross Loader job

1. Using IBM’s SDSF or another job output facility, type SJ next to the failed job.

2. Delete the Cross Loader job steps that ran successfully.

3. Submit the job again.

Recovering from Cross Loader Column Length Errors

When running the Unicode Migration Cross Loader jobs, you might receive the following error message:

SUM OF INPUT COLUMN LENGTHS FOR CURSOR C1 IS TOO LARGE

This error message is generated when the row length of the table being unloaded has exceeded the maximum allowed by Cross Loader. If you receive this message, perform the steps in the following procedure.

To recover from Cross Loader column length error messages

1. Do one of the following:

   • If the definitions for the column are the same in the source and target tables, define the LONG column as a VARCHAR column of a specified length, for example:

     CAST (LOG_DATA_LONG AS VARCHAR (16350))
     

   • If the definitions for the column in the source and target tables are different, change the definition of the LONG column in the source table to have the same format as the corresponding column in the target table, for example, the column might be defined as a CLOB data type in the target table.

2. Rerun the job that generated the error.

Viewing the Log File

During the Unicode migration process, a log file is generated that provides information on the current status of the migration jobs. You can review the log file to determine the status of a specific job using the following procedure.

To view the migration job log

1. On the Siebel Unicode Migration Option Menu, select option 4. View Log Database Joblog, and press Enter.

   The Joblog Query panel is displayed (panel ID is SBLULOGP).

2. Specify the number of jobs that you want to display on the screen at one time by entering a value in the Rows field, then select the type of jobs you want to display by entering one of the following option numbers, and pressing Enter:

   • Option 1. Lists all of the jobs that completed successfully.

   • Option 2. Lists all of the jobs that did not complete successfully.

   • Option 3. Lists the jobs that did not run.

   • Option 4. Lists all of the jobs in the job log.

The log file is initially populated with data as part of the environment setup, and is updated as jobs are run to reflect the current status of the migration jobs.