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.

Usage Transactions

Re-Keying D1_USAGE.USG_EXTERNAL_ID During Base Insert to Production

1. Purpose

This section describes a conversion workaround for implementations where D1_USAGE.USG_EXTERNAL_ID is used as a reference to C1_USAGE.C1_USAGE_ID and therefore needs to be re-keyed during the Base Insert to Production process.

This workaround is applicable only when the implementation uses USG_EXTERNAL_ID as a reference to C1_USAGE.

2. Problem Statement

During the Base Insert to Production process for the D1_USAGE table, the value in USG_EXTERNAL_ID is not transformed or re-keyed to the corresponding Production C1_USAGE_ID value from the C1_USAGE table.

The Base Insert to Production process relies on metadata-defined Foreign Key relationships to identify fields that require key transformation. Because no Foreign Key relationship is defined between D1_USAGE.USG_EXTERNAL_ID and C1_USAGE.C1_USAGE_ID, the process does not recognize USG_EXTERNAL_ID as a field requiring re-keying.

As a result, the source USG_EXTERNAL_ID value is carried forward without being replaced by the corresponding Production key.

3. Why the Foreign Key Is Not Defined Out of the Box

The absence of a Foreign Key relationship between D1_USAGE.USG_EXTERNAL_ID and C1_USAGE.C1_USAGE_ID is intentional.

USG_EXTERNAL_ID supports multiple implementation patterns and is not necessarily expected to reference a Usage record within the application. For example, an implementation may use this field to hold an identifier that is provided to, or maintained by, an external system.

Therefore, the product cannot assume that USG_EXTERNAL_ID always represents a reference to C1_USAGE.C1_USAGE_ID, and a Foreign Key relationship is not provided as part of the standard product configuration.

However, some implementations use USG_EXTERNAL_ID specifically as a reference to C1_USAGE.C1_USAGE_ID. For these implementations, the absence of the Foreign Key relationship prevents the Base Insert to Production process from identifying the field for Production key transformation.

4. Proposed Solution

For implementations where D1_USAGE.USG_EXTERNAL_ID represents a reference to C1_USAGE.C1_USAGE_ID, a temporary Foreign Key relationship can be defined in the application metadata as a conversion workaround.

The temporary relationship allows the Base Insert to Production process to recognize USG_EXTERNAL_ID as a key reference and transform it to the corresponding Production C1_USAGE_ID.

The constraint should be created before the conversion activity and removed after the migration has completed successfully and the results have been validated.

5. Create the Temporary Foreign Key

In the Cloud Application:

Search for and open the D1_USAGE table.
Navigate to the Constraints tab.
Select Add.
Configure the constraint using the following values:

Configuration	Value
Constraint	`ZZ_USGEXTID`
Constraint Type	`Foreign Key`
Referring Constraint	`C1_CXT368P0`
Referring Constraint Table	`Usage`
Referring Constraint Owner	`CCB`
Enable Referential Integrity	Enabled
Sequence	`1`
Field	`Usage External ID`

Save the table definition.

6. Refresh the Batch Metadata

Before running the Base Insert to Production job, execute the following batch process:

F1-FLUSH

Running F1-FLUSH ensures that the updated table and constraint metadata is refreshed and made available to the batch infrastructure.

The Base Insert to Production job should be started only after F1-FLUSH has completed successfully.

7. Run Base Insert to Production

After the temporary Foreign Key has been created and F1-FLUSH has completed successfully, run the applicable Base Insert to Production job for the D1_USAGE data.

The temporary Foreign Key relationship enables the process to identify USG_EXTERNAL_ID as a reference to the Usage key and perform the corresponding Production key transformation.

8. Validate the Migration

After the Base Insert to Production job completes, validate the migrated data before removing the temporary constraint.

Confirm that:

D1_USAGE.USG_EXTERNAL_ID has been transformed to the expected Production C1_USAGE_ID.
The migrated D1_USAGE records reference the appropriate Production Usage records.
The Base Insert to Production job completed successfully.
No unexpected migration or referential integrity errors occurred.

The temporary constraint should not be removed until the migration results have been successfully validated.

9. Post-Insert Cleanup

After the migration has completed successfully and the results have been validated:

Return to the D1_USAGE table definition.
Navigate to the Constraints tab.
Locate and remove the ZZ_USGEXTID constraint.
Save the updated table definition.
Run F1-FLUSH again.

The second execution of F1-FLUSH ensures that the batch infrastructure is refreshed after the temporary Foreign Key relationship has been removed.

10. Expected Outcome

For implementations where D1_USAGE.USG_EXTERNAL_ID represents a reference to C1_USAGE.C1_USAGE_ID, the temporary ZZ_USGEXTID Foreign Key provides the metadata required for the Base Insert to Production process to perform the appropriate Production key transformation.

The workaround is limited to the conversion activity. Once the migration is complete and validated, the temporary constraint is removed so that the D1_USAGE metadata is returned to its standard post-conversion configuration.

Process Summary

Create ZZ_USGEXTID → Run F1-FLUSH → Run Base Insert to
          Production → Validate Re-Keying → Remove ZZ_USGEXTID → Run
      F1-FLUSH

Important: This workaround should be used only when the implementation has established that D1_USAGE.USG_EXTERNAL_ID is intended to reference C1_USAGE.C1_USAGE_ID. It should not be applied where USG_EXTERNAL_ID is used for an external or otherwise unrelated identifier.

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.

18 Customizing Data Conversion and Migration