Rename Change Table schema

If your transformation type is Rename Change Table schema, you can do the following:

• Rename Change Table schema to (string)
• Add a prefix or suffix
• Remove a prefix or suffix
• Replace a prefix or suffix with different characters
• Convert Change Table schema name to uppercase
• Convert Change Table schema name to lowercase
• Rename Change Table schema (expression)

Rename Change Table schema to (string)

Use the Rename Change Table schema to: [string] option to change the name of all Change Table schemas that you defined in the Transformation scope step to a different name. For example, if you have a Change Table schema called EMPLOYEE and want to change all instances of this name to EMP then enter the string EMP. You can enter any string in this field.

Add a prefix or suffix

Use the Add a prefix or suffix option to add additional characters to the beginning or end of the Change Table schema name for all tables that fit the definition you created in the Transformation scope step. For example, if the Change Table schema name is EMPLOYEES, you can add a suffix, such as TAR or _TAR to the Change Table schema name for all Change Table schemas with that Change Table schema name. In this case, the resulting Change Table schema name will be EMPLOYEESTAR or EMPLOYEES_TAR.

To globally add a prefix or suffix:

1. Select Add <Prefix/Suffix> Insert Characters to matching Change Table schema names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click [string] to activate the field.
4. Type the characters you want as the prefix or suffix. If you want to include an underscore or other legal character to separate the prefix/suffix from the original name, you must add it as part of the character string.
5. Click Finish to add the rule to the Global Rules list.

Remove a prefix or suffix

Use the Remove a prefix or suffix option to remove a string of characters from the beginning or end of a Change Table schema name for all tables that fit the definition you created in the Transformation scope step.

For example, you can use this option to remove the letters _REV from the Change Table schema name for all Change Table schemas with the name EMPLOYEES. In this case the Change Table schema name in the target will be EMPLOYEES.

To globally remove a prefix or suffix:

1. Select Remove <Prefix/Suffix> <String> from matching Change Table schema names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click [string] to activate the field.
4. Type the characters you want to remove. If you want to remove an underscore or other legal character from the original name, you must add it as part of the character string.
5. Click Finish to add the rule to the Global Rules list.

Replace a prefix or suffix with different characters

Use the Replace a prefix or suffix option to replace a string of characters with a different string of characters. You determine whether to replace the characters at the beginning or end of a Change Table schema name for all tables that fit the definition you created in the Transformation scope step.

For example, you can use this option to replace the letters _ORIG with _REPL in the Change Table schema names for all Change Table schemas called EMPLOYEE_ORIG. In this case the Change Table schema name in the target will be EMPLOYEE_REPL.

To globally replace a prefix or suffix:

1. Select Replace <Prefix/Suffix> <String> by <String> for all matching schema names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click the first [string] to activate the field.
4. Type the characters from the existing (source) Change Table schema name that you want to replace. If you want to include an underscore or other legal character from the original name in the string that you want to replace, you must add it as part of the character string.
5. Click the second [sting] to activate the field.
6. Type the characters you want to use in the target. These characters replace the original (source) characters in the target.
7. Click Finish to add the rule to the Global Rules list.

Convert Change Table schema name to uppercase

Use the convert to uppercase option to convert a Change Table schema name to all upper case. For example:

• Table_cat, becomes TABLE_CAT
• table_cat, becomes TABLE_CAT
• taBLe_Cat, becomes TABLE_CAT

To globally change the Change Table schema name to all uppercase:

1. Select Convert Change Table schema name to uppercase.
2. Click Finish to add the rule to the Global Rules list.

Convert Change Table schema name to lowercase

Use the convert to lowercase option to convert a Change Table schema name to all lower case. For example:

• Table_cat, becomes table_cat
• TABLE_CAT, becomes table_cat
• taBLe_Cat, becomes table_cat

To globally change the Change Table schema name to all lowercase:

1. Select Convert Change Table schema name to lowercase.
2. Click Finish to add the rule to the Global Rules list.

Rename Change Table schema (expression)

Use the Rename Change Table schema to [expression] option to change the name of all tables that fall within the scoped you defined in the Transformation scope step. For example, if you have a Change Table schema called EMPLOYEE and want to change all instances of the Change Table schema name as defined in the previous step to EMP.

To rename the Change Table schema:

1. Select Rename Change Table schema to.

2. Click the button to the right of the Rename Change Table schema to option to open the Expression Editor. For information on how to use the Expression Editor, see Using the Expression Builder.

   or

   Click [expression] to activate the field and continue with step 3.

3. Type an SQLite expression or a string (in quotes) to rename the table. For example:

   • "New_Change_Table_Schema"

   • ’PREF_’||$AR_M_SOURCE_SCHEMA||’_SUFF’

   You can use the following metadata in the SQLite expression:

   • $AR_M_SOURCE_COLUMN_DATATYPE
   • $AR_M_SOURCE_COLUMN_NAME
   • $AR_M_SOURCE_SCHEMA
   • $AR_M_SOURCE_TABLE_NAME
4. When you're done, click Next to see a summary of your rule and replace the default name and description, or Finish to add the rule to the Global Rules list.