The Precision Bridge Archiving Wizard

Scope: 

• Target Server must be Database (Oracle, SQL Server, Postgres, MySQL, HanaDB, Snowflake or Databricks) OR Service Now
• Source Server must be Database, Service Now, Salesforce, Ivanti, Cherwell, RSA Archer or Remedy
• Service Now -> Service Now is NOT supported.

The Precision Bridge Archiving Wizard can be used to auto-create the required tables on your selected database or Service Now instance to store your migrated data.  The tables created will match the structure of the tables that you select from your source application.

If you already have tables that you want to use then you can ignore this option and simply create the required table mappings for your project.  

The Precision Bridge Archiving Wizard can also be used to auto-generate the corresponding table and field mappings in your project. To start the Wizard, select Generate>Generate Archive Targets from the Projects menu.

There are 4 steps in creating tables and/or mappings using the wizard:

• Specify the options relating to how tables and mappings will be created/updated
• Select the source forms that you wish to create tables/mappings for
• Optionally, edit the names of the target tables that will be created/updated
• Run the generation process to create or update  tables and/or mappings, or optionally for database targets, a DDL script that can be run on the database to create or update tables.

1. Selecting Auto-Generate Options

Then from the next screen select the Auto-Generate options as follows:

This screen is divided into two sections, the top section for the creation or update of target tables, the bottom section for the creation and update of project mappings.

1a - Creating Tables on the Target Server

If you want to auto-create/update target tables, select the Create/Update Target Tables option.  The default is to add target columns/fields for ALL source fields on the selected source tables.  However, if you have already defined a Form Mapping in your project you can select the option to only add columns for ‘missing’ target fields, i.e. where the mapped target column doesn't exist.

Index Creation Options (Service Now source and Database target only)

These options are currently only supported for Service Now source servers and Database Target servers. You can also optionally create indexes on the tables created on target database, based on the indexes that exist on the source tables in Service Now. There are 2 options:

• Create an index for the Record Identifier (sys_id). This will create an index for the table created in the database. It is recommended that this index be created, since in the OTB form mappings generated by Precision Bridge, it will be used during the evaluation of key mappings.
• Create database indexes to mirror other indexes defined for the Service Now table. Note that for extended tables, only base table indexes will be created on the target table.

Note: Index creation will only be performed when the table is first created, not when it is updated (with additional fields added)

Note: Index option is not available for Snowflake as target server.

Setting the 'Generate Fields For' Option (Service Now Source only)

For source servers that support the retrieval of both the 'back-end' source value and the UI displayed display value for certain fields, you have the option to choose which you would like to support when the database table is created. Currently this is only available for Service Now source projects where the Support Source Display Value Mappings Option was selected when the project was opened. See section Enabling Retrieval of Display Values (Service Now Source Only) for full information.

In Service Now, Reference, List, Boolean and Journal fields have a display value that is different to the source value. In addition any field that has choices defined for it supports retrieval of both display and/or source values.

There are 3 options, and the selection will influence which columns are added to the database table and thus which fields can be mapped:

Option	Description
Source Values Only	Columns will be added to the table to match the back end value stored by service now. For reference fields, this will be the sys_id of the referenced record. For fields with defined choices, it will be the value of the choice (which may be an integer value), rather than the displayed value. All columns will be named the same as the fields on the source table and have a data type appropriate to the source field.
Display Values Only	Table Generation: Where a field has a display value associated (e.g. reference fields and fields with defined choices), columns will be added to the table to for the display value. These columns will be varchar(255) type and will be suffixed with _disp. If the field does not have an associated display value, it will be named the same as the fields on the source table with a data type appropriate to the source field.
Both Source and Display Values	Table Generation: This will generate a table that has columns for all the source values (see Source Values Only above). Additionally, for fields that have display values, columns suffixed _disp will be added. This means that fields with display values will have two columns to hold their data, one for the source value and one for the display value.

Generate a Script rather than Create/Update the Tables Directly

By default, the tables will be created/updated during processing. However there is an option to generate a script that, when run, will perform the same actions. This gives the user the ability to make changes to the script to fine-tune the generation of the objects and then run the script manually against the database. If this function is required, select the 'Generate Script' option and then select the directory and file that the script should be written to. If an existing file is selected, it will be overwritten.

Note that use of this option will disable the option for creating the mappings after the target tables have been created. If mappings need to be created, this option will need to be used separately after the generated script has been run and the project re-opened, and the target cache flushed.

Additional Server Specific Options

Depending on the Target Server type, additional options may be available.

Use CLOB Option

For Oracle Target Servers ONLY, there is an option to change the maximum length (in characters) where a NVARCHAR2 will be used. Fields with lengths greater than this will use a CLOB datatype instead of NVARCHAR2. The default value of 2000 assumes that the Oracle Database defines MAX_STRING_SIZE=STANDARD and that a 2 byte character set is used. This value can be extended up to a maximum of 32767 characters if MAX_STRING_SIZE=EXTENDED and the database uses a 1 byte character set. Contact your DBA to identify a suitable value for this option.

Target Application Name

For Service Now Target Servers ONLY, there is an option to add the forms to an existing application scope rather than the global scope. The name of an a Service Now application that already exists can be entered in this field. If the field is left blank, all tables will be added to the global scope.

Add Role to Tables

For Service Now Target Servers ONLY, there is an option to add full access to the generated tables to a given role. If this option is left empty, no role will be granted access to these tables.

Note that if the named role does not exist, Precicion Bridge will create one with this name.

1b - Creating Mappings on the Target Server

If you also want to auto-create the corresponding Form Mapping in your current project select the Create/Update Project Mappings option.  The default here is to create a new Form Mapping for every form selected.  However, if you have already defined form mappings for your current project, select the option to update existing Form Mapping for the same source/target.  Then if a mapping is found with the same source and target table, it will be updated. If there is more than one mapping with the same source/target, the first one will be updated.

Note that this option cannot be used in conjunction with generation of a script to create/update tables. If a script is used, generate and execute the script, reopen precision bridge and then use the create/update project mappings option to generate the mappings.

Note on Currency Fields: Where a currency field is mapped, the numeric field will display only the numeric value of the currency. An additional field (prefixed _CC) will store the the 3 character currency code and will be mapped with an assignment mapping using the GET_CURRENCY_CODE function. This will extract the currency code from the ServiceNow currency value. 

Note on Journal and Journal List Fields: These fields do not hold any values in the Service Now table and should not be mapped. However the display values for these fields will return a formatted string containing all the journal entries added to the record. These can be mapped to a string target.

Display Field Mapping (for Target Tables containing Display Value Columns)

For display values columns to be mapped, the Project option 'Support Source Display Value Mappings' must be checked when the project is opened. This option can only be set when the project is opened. It will be checked by default for all new ServiceNow->Database projects created.

This option causes additional source fields to be created to allow retrieval of either the source or display values.

If the above option is not checked, the Generate Mappings option will not be able to map the display value columns in the database table. If the option is checked, AND the target table contains columns for display values (or the table will be created with display values), these will be mapped to the appropriate source display field.

After completing your selections, click on Next to navigate to the next screen.

Note on Source Filtering

When using Auto-Generate to create form mappings for Service Now -> Database migrations, the source filter for each mapping is set to:

[sys_updated_on] > #LAST_EXECUTION_TIMESTAMP#

This is used to ensure that only those records that have been modified since the last execution are included in the migration.  The first time that you execute the project you must provide a value for LAST_EXECUTION_TIMESTAMP.  To include all records set this value to an old date and time such as 1970-01-01 00:00:00

For more information on setting variables for Execution see this article.

2. Selecting the Source Forms to Generate Objects for

Select the tables from your vendor platform for which you want to auto-generate target tables and/or mappings for your current project.  To search for a specific table or range of table use the Filter option.  Note that you can search using either the table label or the underlying table name. Tables with names/labels containing the filter string will be included in the results.

Use the Add/Remove buttons to add/remove selected tables to/from the right window for processing.

Alternatively, use the ‘Add All for Project’ option to only add tables for which you have already defined mappings in the current project. The target table names (see step 3 below) will be set based on the target table names in your project.

Use the Add All Missing option to only add tables where a mapping already exists in the current project and the target table doesn't exist.

Click 'Select Target Table Names >>' to move to the next part of the wizard.

3. Specifying the Target Table Names (Optional)

By default, the tables created on the target server will have the same name as the source form they were defined from (unless you used 'Add All for Project' see above). However you can change this for the list of selected tables. This might be useful to conform to a database naming policy, for example or to avoid conflicts with existing name tables. 

The set of tables selected in the previous step is listed. The Target Table Name values can changed in the following ways:

• They can be edited directly in the table cell
• A prefix can be applied to multiple tables. Select the rows that you want to add a prefix to, select the Apply Prefix option, enter the prefix and click 'Apply to Selected Tables'
• A string in the name of multiple tables can be replaced with something else. Select the rows to change, select the Replace option, enter the string to search for and the replacement string and click 'Apply to Selected Tables' (see example below)
• The names can be re-set to default (i.e. the same as the source form name). Select the rows to change, select the Revert to Default Name option and click 'Apply to Selected Tables'.

When you have completed your selection, click on the Generate Objects button to start the process running.

4. Generating the Objects

A confirmation screen is presented after which you can select OK to start the generation or Cancel to abort.

After selecting OK, a new window is then presented to show the progress of the table/mapping generation.  If any errors are encountered they are displayed in the message column.

On completion a summary screen is presented like this:

If you are generating a script rather than creating the tables directly, you will be given the option to view the script in the default text viewer.

Saving and Loading Object Generation Definitions

Once you have completed steps 1-3 of the Wizard, you can save the options and tables you have selected in a file. This file can then be re-loaded at a later time, possibly in a different project. This allows you define the set of tables and mappings you want to create and re-run the generation process multiple times, with a different source/target server.

Use the Save button on step 3 or 4 to save the definition. You will be prompted for the file name and location. The definitions will be stored as xml.

Use the Load button at any stage to load an existing definition file. You will be prompted for the file name and location. The settings in the file will be read in and will overwrite any existing selections made.

Example: Automating Generation of Database Tables and Mappings, with Script Option

The process for creating a mapping for a new table where a script is to be produced then executed outside Precision Bridge will be as follows:

1. Check the Create/Update Target Tables option ONLY, also checking the index generation options as required
2. Select the Value Migration Support option required (see below for more information)
3. Select the Generate Script option and specify the file to write the script to
4. Select tables(s) to include in the script
5. Optionally, rename the target tables that should be created
6. Click Generate Script to write the script commands to the file
7. Open the script in an editing tool and fine tune the commands as required
8. Execute the script using the appropriate database application to generate the objects
9. Re-open the project in Precision Bridge, making sure the option to Force Cache Refresh for the Target Server is selected in the Options tab of the Open Project dialog
10. Open the Generate Archive Targets option again
11. Select the Create/Update Project Mappings option ONLY, and select the same Value Migration Support option used in (2) above
12. Select the same tables that were created in steps 1-6, renaming the target tables if required to ensure they match those that were created
13. Click Generate Objects to create the mappings in the project.

 

Further Considerations

Which Schema will the tables be Created In?

For Oracle: The tables will be created in the schema defined by the username in the database connection being used.

For SQL Server: The tables will be created in the schema specified in the project's SQL Server connection or if not defined, the Database Owner (dbo) schema.

For Posgres : The tables will be created in the schema specified in the project's postgres connection, or if not defined, the public schema.

For Hana DB, MySQL and Snowflake.

Oracle Compatibility

For Oracle Target migrations, the Precision Bridge Archiving Wizard is only supported by Oracle Version 12.2 or later due to restrictions in identifier lengths in earlier versions. 

Service Now Table and Field Naming

Service Now enforces certain naming conventions to be followed when creating tables and fields:

• Custom tables in the global scope must be prefixed u_
• Tables in an application scope must be prefixed with the scope prefix
• Custom fields in the global scope must be prefixed u_
• Only lower case alphanumeric characters and underscores can be used (double underscore not allowed).

Precision Bridge will automatically adjust the names of the target tables and fields created using the Archiving Wizard to follow these conventions, therefore the name of the table and fields on the target may not be an exact match of those on the source. 

Related Articles

Data Types & Transformation - Service Now Archiving Projects