{"id":15892,"date":"2022-06-06T15:21:50","date_gmt":"2022-06-06T19:21:50","guid":{"rendered":"https:\/\/www.iri.com\/blog\/?p=15892"},"modified":"2022-06-06T17:28:34","modified_gmt":"2022-06-06T21:28:34","slug":"mapping-db-data-types","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/migration\/data-migration\/mapping-db-data-types\/","title":{"rendered":"Mapping DB Data Types"},"content":{"rendered":"

Abstract: The process of transferring data from one database (DB) to another can be complicated when those DBs do not use the same data types. A DB Data Type Mapping wizard is now implemented in <\/span><\/i>IRI Workbench<\/span><\/i><\/a> to help solve this problem for <\/span><\/i>SortCL<\/span><\/i><\/a>-driven data management jobs with different sources and targets.<\/span><\/i><\/p>\n

After completing a task like <\/span>multi-table masking<\/span><\/a> in FieldShield for example, if the target schema does not have the tables already created to place the masked data, Data Definition Language (DDL) scripts must be generated ahead of time to create the tables.\u00a0<\/span><\/p>\n

Now suppose that the source and target tables are in different vendor databases. There can be errors with the DDL because the wizard generating the scripts encounters a data type that is in one vendor but not the other.\u00a0<\/span><\/p>\n

At this point, a default data type is used \u2013 often Varchar \u2013 which isn\u2019t the best solution, which forces the user to examine the scripts and modify them. This can be a time-consuming process and if the user is not careful, it can lead to a loss in data.\u00a0<\/span><\/p>\n

With the implementation of the new Data Type Mapping wizard, IRI Workbench users can dictate which data types should be used when going from one database to another.<\/span><\/p>\n

The IRI Workbench GUI is built on Eclipse and is used to create job scripts that allow for the movement and manipulation of structured data through compatible <\/span>IRI products<\/span><\/a>. All the work specified in those scripts is performed at runtime by the back-end <\/span>SortCL<\/span><\/a> data processing program.<\/span><\/p>\n

<\/b>Overview of Data Type Maps<\/b><\/h5>\n

The Data Type Mapping preferences can be found in the IRI section in Preferences. The first view provides information on the maps created and allows for several options such as adding new maps, editing existing ones, removing, and importing\/exporting:<\/span><\/p>\n

\"\"<\/p>\n

The table has four columns, including the <\/span>Source <\/span><\/i>and <\/span>Target<\/span><\/i>, which shows the names of the DB profiles created when setting up the <\/span>JDBC connections<\/span><\/a>. These are the same names you should see in the Data Source Explorer view.<\/span><\/p>\n

The <\/span>Name<\/span><\/i> column holds unique names for data type maps which can provide context for using these maps in different Workbench job wizards. This is why names must be unique.\u00a0<\/span><\/p>\n

The <\/span>Default<\/span><\/i> column allows you to specify which map will be used by default when job wizards create DDL scripts. If the default is not selected, the user can still use a map if the source and target DB and version are the same as the source and target DB used in the wizard.<\/span><\/p>\n

Creating a Data Type Map<\/b><\/h5>\n

To create a new map, click <\/span>Add\u2026<\/span><\/i> and a new window will appear like the one below.\u00a0<\/span><\/p>\n

\"\"<\/p>\n

The default name of <\/span>NewMap <\/span><\/i>is automatically provided, but you should rename it.\u00a0 Next to the name is the default selection; check this if this new map will be the default map for the source and target DB in your jobs.<\/span><\/p>\n

As for the <\/span>Source <\/span><\/i>and <\/span>Target <\/span><\/i>sections, you can choose to map data types between databases or from\/to SortCL job scripts. If you select a database, the drop-down menu will present all previously configured connection profiles, as the image below shows:\u00a0<\/span><\/p>\n

\"\"<\/p>\n

You will not be able to create a map that has the same source in the target, however; i.e.,<\/span><\/p>\n