Customizing Data Conversion and Migration

Why Customize

There are several reasons for customizing conversion configurations, including:

Fine-tuning data upload performance
Handling unusual data volumes
Marking additional table(s) as eligible for conversion
Reducing creation of unnecessary input files

When to Customize

The layout of the legacy extract files should be finalized as soon as possible, to provide enough time for the extract process development.

The setup of the batch job chains is less critical at the beginning of the project. The initial suggested setup is likely to be included in the application Conversion Accelerator. Adjust the initial setup after you've performed the trial uploads of the actual data, assessed the performance and figured the optimal flows.

What to Customize

Control File

The majority of the customizations affect the contents of the generated Control File and the corresponding input data file specifications. The configurations are stored on the Conversion Task Types that represent Conversion Instructions.

Customizing the Control File's load options and parameters may improve upload performance
Fully customized Control File allows you to use alternative record parsing and other advanced SQL Loader configuration techniques.
When CLOB data is supplied as Secondary Files, the system is expecting the input data files to exist and be named following the specific naming convention.
For example, if the table has multiple CLOB fields, for every CLOB field that was not excluded from conversion, the system is expecting the secondary file's name to be suffixed with _<CLOB Field Name>. See the online help for more details.

Data Delimiters and Enclosing Characters. Examine the default Conversion Instructions (Conversion Task Type) setup. Either select another delimiter from the existing list or add new value to the Extended Lookup.

CLOB as Secondary File? The indicator is defined on Conversion Instructions (Conversion Task Type).

Applicable when CLOB is supplied as Secondary File:

CLOB Columns Included in Conversion. By default, the control file is generated as if all CLOB fields are part of the converted data. The legacy data does not necessarily

contain data for all CLOB fields, hence there is no reason to create empty files. The list of excluded CLOB columns is is defined on Conversion Instructions (Conversion Task Type). Create new Conversion Instructions (Conversion Task Type) for the Table or MO with multiple CLOB fields and specify the exclusion list.

Control File "Header" - Load Options. A text stored as Managed Content. Contains the control file's fragment with options and load parameters. You can amend the options according to SQL Loader documentation. Examine the entries delivered with the product.

Note:

The text contains several substitution parameters prefixed with %. The substitution happens at generation time or at run time. Preserve them while creating a custom Control File header.

If you wish to amend the load options and parameters only, create a new Managed Content entry. Modify default Conversion Instructions (Conversion Task Types) or create new ones and add Override Instructions to Conversion Master Configuration. Run Conversion Artifact Generator and create new customized Control File. See the online help for more details.

Custom Control File. A text stored as Managed Content and representing the entire Control File, including load options, parameters and the field list.

Note:

Preserve substitution parameters (see the note above). The input data file specifications are not generated when the Custom Control File is used. Make sure that the fields in the input data files correlates to the field's list in the custom Control File.

Additional Customization Items

Table's Conversion Eligibility. The table is considered eligible for conversion according to the indicator on the Metadata Table record. It is a system data and cannot be modified by the implementation. In order to make a non-converted Table eligible for Conversion, you should add an entry to the Override Conversion Eligibility list on the Conversion Master Configuration.

Conversion Orchestration. The suggested setup of the Batch Controls, Batch Jobs, and Chains is usually included in the application Conversion Accelerator. Adjust this setup by fine-tuning the number of threads, the chain structure(s) and other batch job parameters.

How to Customize

Configurations can be amended on several levels:

To modify the configuration globally, amend the default Conversion Instructions (Conversion Task Type) that is referenced on Conversion Data Upload Master Configuration
To modify the option globally for all tables, amend the default Conversion Instruction for Table (Conversion Task Type) that is referenced on Conversion Data Upload Master Configuration
To modify the option globally for all maintenance objects, amend the default Conversion Instruction for MO (Conversion Task Type) that is referenced on Conversion Data Upload Master Configuration
To modify the option for a specific table(s) or maintenance objects, create new Conversion Instruction (Conversion Task Type) and add the Override Instruction for Table or MO on Conversion Data Upload Master Configuration
To make a non-converted table eligible for conversion, add it to the Override Conversion Eligibility list on Conversion Data Upload Master Configuration

Note:

Regenerate Conversion Artifacts to apply the configuration changes. Download the updated input file specifications.

Tips and Important Mistakes to Avoid

Issue	Details
Run the process against the right target.	The data upload only runs if the environment is pointing to the STAGING schema. Navigate to Conversion Support ' Switch Schema. On the popup screen the current schema is displayed. Make sure the current schema is Staging.
Provide data files according to the specifications. Regenerate the artifacts after modifying the data upload configurations.	SQL Loader loads the data according to the Control File. Input Data File Specifications describe what is expected from the input data file: Names of the data files Data format for all fields Data delimiters to be used in the input data file Every time the configuration has changed the artifacts must be regenerated in order to keep the configurations and the input data specifications in sync.
Provide input data files with CLOB data IF NECESSARY.	Conversion Instruction defines whether CLOB data is provided as part of the main file or as a separate file. The system expects the data files to be provided according to this definition. Open the Input Data Specifications and read carefully. If the specification mentions that CLOB is to be provided as a secondary file, this is what Control File would inspect. If you wish to include CLOB data in the main file, verify that the Conversion Instruction is set correctly. If the configuration was modified you must regenerate the artifacts.
Avoid creating unnecessary data files for CLOB columns.	By default the system expects the data to be provided for all target table columns. If the table contains multiple CLOB columns AND the CLOB data is provided as a secondary file, it means one input data file per column. To exclude unnecessary CLOB columns for a table or maintenance object, configure Conversion Instructions using the K1-ConvArtMultClobMOTaskType or K1-ConvArtMultClobTblTaskType business object and specify the Override Conversion Instruction on the Master Configuration. Regenerate conversion artifacts and examine the input data specifications after changing the configuration.
Avoid truncating the entire staging data unintentionally.	The K1-SCLTB batch process allows you to truncate a specific table or maintenance object in the STAGING schema. The K1-CLNTB batch process allows you to truncate a specific table or maintenance object in the PRODUCTION schema. If submitted without input parameter specifying a table or maintenance object, these batches will process all tables eligible for conversion. This means that all your staging data will be wiped out at once.
Clean up duplicate PK values before the data upload.	Indexes and constraints are disabled during data upload in order to boost performance. De-duplication during the data upload is not supported out-of-the-box SQL Loader direct path upload doesn't perform duplicate check No direct database access means no possibility to modify data via direct SQL after the upload Keep track of the legacy data that has been already uploaded. If you re-upload the same data again, always clean up the target table(s).
The business configurations and admin data has to be finalized and populated in Production prior for legacy data upload. Populate the legacy data extract with valid FK references to the admin/control data.	Once uploaded, the staging data cannot be "massaged"/modified thru direct SQL (that because no database access is possible on cloud) Hence the overall conversion project steps are: Design, test and complete business configurations. During this stage, multiple trial data uploads with dummy data could be performed Populate admin data in Production Create legacy data extract with valid admin data FK References Upload data into staging tables
Key Tables are not populated implicitly.	The Key Tables in the staging schema tables are not populated automatically when the legacy data is uploaded into "main" tables. Upload the data into Key Tables separately or use the batch program provided by Cloud Service Foundation.
Override Conversion Eligibility is supported on Table level only.	The conversion eligibility is overridden for individual tables. Override the eligibility for all the tables that belong to the maintenance object if you decided to convert the entire maintenance object. Note: Overriding a table's conversion eligibility doesn't mean that the staging schema is automatically updated. It only means that the data upload processes will threat this table as a valid target table.
Loading Data Directly into the Production (CISADM) Schema	The following configuration steps are required to load data directly into the Production (CISADM) schema tables: Create a custom Control File for the target table. Generate the control file with default conversion instructions and copy the contents. Modify the INTO... clause to add 'CISADM.' in front of a target table name Create a new Managed Content entry. Copy the entire control file text and save. Create a new Conversion Task Type for the target Table Specify the new Managed Content as an Override Control File. In the Data Upload Support Master Configuration, create an Override Table Instruction entry. Specify the target table and the new Conversion Task Type. Generate Conversion Artifacts for the table.
Loading Very Large Data Volumes	Avoid SQL-based conditions in the control file when loading very large data volumes. The default values and SQL-based conditions will cause SQL Loader to switch to the conventional path load which performs row-by-row inserts. The best results are achieved with a direct path load. More threads don’t necessarily mean better performance. The optimal overall data load performance is achieved when the threads (and their corresponding SQL Loader processes) are targeting different partitions. Additional guidelines: Partitioning by month is required for best performance. Load multiple months in parallel for best performance & scalability. Start with ONE thread per month Increment number of threads per month. If performance does not increase, try a smaller increment or stay with your last best. For example: loaded 12 months data with 48 threads, 4 threads/month. Large data files are preferable. Many small files have the overhead of spinning up new SQL*Loader process for each file. Set longer SQL timeout on the data upload batch process. Disable indexes before loading. Rebuild indexes after direct path load. Reduce or stop the activities in the environment when performing the massive data upload.
Using Batch Schedulers	Avoid running adhoc jobs Use the SaaS or an external batch scheduler to orchestrate the batches. Do not use template batch controls; use custom batch controls for specific tasks.

Issue

Details

Run the process against the right target.

The data upload only runs if the environment is pointing to the STAGING schema.

Navigate to Conversion Support ' Switch Schema. On the popup screen the current schema is displayed. Make sure the current schema is Staging.

Provide data files according to the specifications.

Regenerate the artifacts after modifying the data upload configurations.

SQL Loader loads the data according to the Control File.

Input Data File Specifications describe what is expected from the input data file:

Names of the data files
Data format for all fields
Data delimiters to be used in the input data file

Every time the configuration has changed the artifacts must be regenerated in order to keep the configurations and the input data specifications in sync.

Provide input data files with CLOB data IF NECESSARY.

Conversion Instruction defines whether CLOB data is provided as part of the main file or as a separate file. The system expects the data files to be provided according to this definition.

Open the Input Data Specifications and read carefully. If the specification mentions that CLOB is to be provided as a secondary file, this is what Control File would inspect.

If you wish to include CLOB data in the main file, verify that the Conversion Instruction is set correctly.

If the configuration was modified you must regenerate the artifacts.

Avoid creating unnecessary data files for CLOB columns.

By default the system expects the data to be provided for all target table columns.

If the table contains multiple CLOB columns AND the CLOB data is provided as a secondary file, it means one input data file per column.

To exclude unnecessary CLOB columns for a table or maintenance object, configure Conversion Instructions using the K1-ConvArtMultClobMOTaskType or K1-ConvArtMultClobTblTaskType business object and specify the Override Conversion Instruction on the Master Configuration.

Regenerate conversion artifacts and examine the input data specifications after changing the configuration.

Avoid truncating the entire staging data unintentionally.

The K1-SCLTB batch process allows you to truncate a specific table or maintenance object in the STAGING schema.

The K1-CLNTB batch process allows you to truncate a specific table or maintenance object in the PRODUCTION schema.

If submitted without input parameter specifying a table or maintenance object, these batches will process all tables eligible for conversion. This means that all your staging data will be wiped out at once.

Clean up duplicate PK values before the data upload.

Indexes and constraints are disabled during data upload in order to boost performance.

De-duplication during the data upload is not supported out-of-the-box

SQL Loader direct path upload doesn't perform duplicate check
No direct database access means no possibility to modify data via direct SQL after the upload

Keep track of the legacy data that has been already uploaded.

If you re-upload the same data again, always clean up the target table(s).

The business configurations and admin data has to be finalized and populated in Production prior for legacy data upload.

Populate the legacy data extract with valid FK references to the admin/control data.

Once uploaded, the staging data cannot be "massaged"/modified thru direct SQL (that because no database access is possible on cloud)

Hence the overall conversion project steps are:

Design, test and complete business configurations. During this stage, multiple trial data uploads with dummy data could be performed
Populate admin data in Production
Create legacy data extract with valid admin data FK References
Upload data into staging tables

Key Tables are not populated implicitly.

The Key Tables in the staging schema tables are not populated automatically when the legacy data is uploaded into "main" tables.

Upload the data into Key Tables separately or use the batch program provided by Cloud Service Foundation.

Override Conversion Eligibility is supported on Table level only.

The conversion eligibility is overridden for individual tables. Override the eligibility for all the tables that belong to the maintenance object if you decided to convert the entire maintenance object.

Note: Overriding a table's conversion eligibility doesn't mean that the staging schema is automatically updated. It only means that the data upload processes will threat this table as a valid target table.

Loading Data Directly into the Production (CISADM) Schema

The following configuration steps are required to load data directly into the Production (CISADM) schema tables:

Create a custom Control File for the target table.
- Generate the control file with default conversion instructions and copy the contents.
- Modify the INTO... clause to add 'CISADM.' in front of a target table name
- Create a new Managed Content entry. Copy the entire control file text and save.
Create a new Conversion Task Type for the target Table
Specify the new Managed Content as an Override Control File.
In the Data Upload Support Master Configuration, create an Override Table Instruction entry. Specify the target table and the new Conversion Task Type.
Generate Conversion Artifacts for the table.

Loading Very Large Data Volumes

Avoid SQL-based conditions in the control file when loading very large data volumes. The default values and SQL-based conditions will cause SQL Loader to switch to the conventional path load which performs row-by-row inserts.

The best results are achieved with a direct path load.

More threads don’t necessarily mean better performance. The optimal overall data load performance is achieved when the threads (and their corresponding SQL Loader processes) are targeting different partitions.

Additional guidelines:

Partitioning by month is required for best performance.
Load multiple months in parallel for best performance & scalability.
- Start with ONE thread per month
- Increment number of threads per month. If performance does not increase, try a smaller increment or stay with your last best. For example: loaded 12 months data with 48 threads, 4 threads/month.
Large data files are preferable.
- Many small files have the overhead of spinning up new SQL*Loader process for each file.
- Set longer SQL timeout on the data upload batch process.
Disable indexes before loading.
Rebuild indexes after direct path load.
Reduce or stop the activities in the environment when performing the massive data upload.

Using Batch Schedulers

Avoid running adhoc jobs

Use the SaaS or an external batch scheduler to orchestrate the batches.

Do not use template batch controls; use custom batch controls for specific tasks.

Sample Artifacts and Data Files

To assist implementers with the conversion and data upload process, multiple sample artifacts and data files are available. The sample files are provided with your cloud service documentation. The samples illustrate various data upload scenarios for table- and MO- level upload. Within the master samples zip file, there are multiple zip archives, each of which contain the following:

Control file, generated
Input Data File Specification, generated
Sample Data File, created according to the specification

The table below provides more details on each of the sample artifacts available.

Target Object	Sample Description
Interval Data Set (INT_DATA_SET)	Regular maintenance object, CLOB field as a secondary file. Configuration: Conversion Task Type K1-CNV-MO Multiple data files (3)
MO Customer Contact (CUST_CONTACT)	Regular maintenance object, CLOB fields in the main file. Configuration: same as Conversion Task Type K1-CNV-TABLE, but the CLOB as Secondary File indicator set to false.
Table Meter Read (CI_MR)	Regular table, CLOB field as a secondary file. Configuration: Conversion Task Type K1-CNV-TABLE.
Table Adjustment (CI_ADJ)	Regular table, CLOB field in the main file. Configuration: same as Conversion Task Type K1-CNV-TABLE, but the CLOB as Secondary File indicator set to false.
Table Initial Sync Request (F1_SYNC_REQ_IN)	Table with Multiple CLOBs as secondary files. Configuration: For table with multiple CLOBs, the special Conversion Task Type was created based on theK1-ConvArtMultClobTblTaskType business object.Override Control File (Managed Content) was created and used as a custom Control File. Review the sample and note that there is a conditional input data selection. Only records with BO = W1-CompositeSyncReqGISAsset would be uploaded. A custom Control File is necessary if you have a requirement to manipulate the data during upload. Input Data File Specification: Since the Control File is fully custom, including the field list, the generated specification is describing expected file name(s) only. The data field formats, delimiters, sizes, and any other information related to the Input Data File layout should be determined based on the custom Control File.

17 Customizing Data Conversion and Migration