Database Types
- 25 Mar 2024
- 11 Minutes to read
- Contributors
- Print
- DarkLight
Database Types
- Updated on 25 Mar 2024
- 11 Minutes to read
- Contributors
- Print
- DarkLight
Article Summary
Share feedback
Thanks for sharing your feedback!
This topic relates to the following sections:
About Database Types
Database Types stores the configuration of each database type in terms of the Connection String Format, processes it supports, and fields required, optional, and display. As part of this configuration, the translation from one Database language to another Database language for use during the migration of data is also stored within this section. Within each Database type there are three tabs of information - Data Type Translations, Database Commands, and Data Type XML.
The Data Type Translations tab displays mappings to commonly used target database types and the formatting for fields during the translation from source to target database.
The Database Commands tab contains a list of the relevant database commands with the associated command text to build out that command in the database language.
The Data Type XML tab provides a grid of rows for the Database Type and the ETL Tool with associated translations for all known Data Types.
This configuration assists in the building of XML files. Within the tab Data Type Translations, the Counts for each Database Type will default to '0' unless users have added manual translations within the Rules From or Rules To. Generally, the users should not need to alter these settings. Changes would be for unique and undefined circumstances where there is no translation from one system to another.
View the following page by navigating to Administer > Advanced > Database Types from the Syniti Migrate Home page.
The image above can be used as reference to set up the standard Database Type Translations and later modified, if necessary, as per client requirements.
Database Type Details
The page setup for Database Types is a left and right set of panels with the right side panel containing two tabs of page data. The user may create or maintain the mapping for each Database Type by performing these tasks:
The Search 
icon on the left and right panes allows the user to search for a specific Database Type or Data Type Translation respectively. To search for a record, click the Search icon, and then enter the value into the cell. The list filters down to the match the characters entered. If the entry does not exist, there is no list of records. To close the search bar, click again on the search icon.
The left pane displays existing Database Types (source and target). The user may view the Database Type parameters in the left panel by clicking the Edit 
icon.
If the Database Type has been added to this application, then the user has access to modify or delete the item. However, all defaulted Database Types are set to Configurable = '3' - Values are Locked in the system to prevent users from modifying these mappings.
Add a Database Type
To add a Database Type, within the left panel, click on the Add 
icon. Fill in the fields as identified below.
Field | Description |
|---|---|
Database Type Key* | This is the Name of the database type. (Required) |
Description* | The description of this database type. (Required) |
Connection String Format | This is the format to build the connection string. |
Terminator | The coding that is the terminator for this database type. |
Supports metadata | Toggle to indicate that there should be DB Commands for (Get Tables, Get Columns , etc).
|
Supports Workingdb | Toggle to indicate that there should be DB Commands for Create View, Create Table, Drop Table, etc. |
Supports Update | Toggle for database supports SQL updates, Hive does not, thus the XML generation builds DS jobs that do the entire process as one big insert (which differs from normal Syniti methodology) . |
| ***Fields in this section are selected for their usage*** |
Server Address* | Specify use from drop-down list. (Optional, Required, Required on Complete, Hidden) (Required) |
Port* | Specify use from drop-down list. (Required) |
Database* | Specify use from drop-down list. (Required) |
Schema* | Specify use from drop-down list. (Required) |
Service Name* | Specify use from drop-down list. (Required) |
URL* | Specify use from drop-down list.(Required) |
DSN* | Specify use from drop-down list. (Required) |
Filepath* | Specify use from drop-down list. (Required) |
Rowsize* | Specify use from drop-down list. (Required) |
Db Filename* | Specify use from drop-down list. (Required) |
SAP Fields* | Specify use from drop-down list. (Required) |
Case* | Select from drop-down case as either UPPER, lower, or Mixed. (Required) |
Username* | Specify use from drop-down list. (Required) |
Password* | Specify use from drop-down list. (Required) |
Configurable | Indicates how this record may be maintained. Ex: Display only, can make active or inactive, modify values, etc.
|
Left Escape | Enter value for left escape. |
Right Escape | Enter value for right escape. |
The values to select for each field are provided below:
Field | Description |
|---|---|
0 | Hidden |
1 | Required |
2 | Optional |
3 | System Value Locked |
The values allowed for the field Configurable are provided below:
Field | Description |
|---|---|
0 | Fully Customizable |
1 | Values are defaulted but can be changed |
2 | Allowed to mark Active or Inactive only |
3 | Values are Locked |
When finished with edits, click the Save 
icon to complete.
Database Types Translations
The right side panel for Database Types contains three tabs to maintain the Data Types Translations, Database Commands, or Datatype XML. Each of the tabs assist in building the XML Scripting from one Database type to another. The known translations have been identified and listed in these grids, but additional translations may be added by the Users to fill in any gaps.
Data Type Translations tab
The tab for Data Type Translations allows the user to maintain the data type conversions from Source to Target for the highlighted Database Type in the left side panel. Manually added Translation Rules displays as a Count in the buttons for Translation Rules From and Translation Rules To.
From the Database Types page, click on the Count 
button for either Rule From or Rules To Translation Rules.
Translation Rules From / Translation Rules To Details
From the Database Types page, click Count button to display the grid of details for either Translation Rules From or Translation Rules To. There are four tabs setup on the details for Translation Rules - Rules, Indirect Rules, Results, and Test. Each tab assists in preparing a list of Translation Rules for the selected Translation. These rules are not the standard, but the additions to the standard rules (the standard translations are stored within the button old Mappings on the previous page).
Rules Tab
The Rules tab provides a grid of Translation Rules from the source Database Type to the target Database Type. The tab is empty unless manually added Rules are saved as shown below.
The Rules tab grid displays the following column headers:
Heading | Description |
|---|---|
Datatype From | Provides the From data type name. |
Datatype To | Provides the To data type name. |
Has Context | This column stores the Context Filters. (Ex: Application, Datasource, Table, or Column to filter to the exact situation where this translation is used) |
Index | Sequence of the Translation Rules. Value increases by factor of 10 for each new entry. |
Active | Toggle to mark the Translation Rule as Active/Inactive for use.
|
Validation Status | Validation that the rule meets the criteria and will work based upon each database type. |
Edit an Existing Translation Rule
Click the Edit 
icon to maintain existing rules. The details for the Translation Rule displays in the details window.
Add a Translation Rule
If the standard list of translations does not cover requirements, the user may manually add a new translation rule as a direct rule in this tab. Click the Add icon to open the translation rules details window.
See below for field definitions for Translation Rule:
Section | Field | Definition |
|---|---|---|
From | Database Type From* | Defaults value for the FROM Database in the Translation. (Required) |
From | Datatype From* | Enter the data type of the FROM database. (EX: CHAR, INT, BIT) (Required) |
From | Datatype From Min Field Length | The minimum value allowed for length of this data type. |
From | Datatype From Max Field Length | The maximum value allowed for length of this data type. |
From | Datatype From Min Decimal Value | The minimum value allowed for decimal place of this data type. |
From | Datatype From Max Decimal Value | The minimum value allowed for decimal place of this data type. |
Context Filters | Application Filter | Select from the drop-down list the Application to which this translation specifically applies. |
Context Filters | Datasource Filter | Select from the drop-down list the Datasource (from selected Application) to which this translation specifically applies. |
Context Filters | Table Filter | Select from the drop-down list the Table (from selected Datasource) to which this translation specifically applies. |
Context Filters | Column Filter | Select from the drop-down list the Column (from selected Table) to which this translation specifically applies. |
Output | Active | Toggle to mark the Translation Rule as Active/Inactive for use.
|
Output | Index | Sequence of the Translation Rules. Value increases by factor of 10 for each new entry. |
Output | Database Type To* | Defaults value for the TO Database in the Translation. (Required) |
Output | Datatype To* | Enter the data type of the TO database. (EX: CHAR, INT, BIT) (Required) |
Output | Datatype To Field Length | Enter the Length of this field. (allows for special characters like #LENGTH#) |
Output | Datatype To Decimal Value | Enter the Decimal Value of this field. (allows for special characters like #DEC#) |
Audit | Various | These fields are all display only and are updated by actions. |
When finished with edits, click the Save icon to complete.
Upon saving, the Syniti Migrate system validates the entries. Should there be an issue, a warning message displays with details of the situation. Click on Continue Save to post the entry or click on Cancel Save to cancel this warning and return to the page to make changes before saving again. The new rule is stored in the count on the main Database Types page as shown below
Indirect Rules tab
If the translation engine is not able to find a 'direct' rule that matches the input datatype, then it checks if it can instead first translate the input to an ANSI type, and from that ANSI type to the target type. If the column is part of an SAP application, it also looks to see if we have translations for it under the SAP database type.
This tab stores Indirect Rules. The rules that do not find a matching for the input data type. This tab contains no rows unless a user adds an Indirect Rule manually.
Results tab
This is a display only page that provides a list of all of the translations stored within Syniti Migrate for the specific translation type. There may be a few in the list that are showing as Output Valid - Error. These are not properly or completely setup.
Click on the Search icon to find a specific result. Click on the Refresh 
icon to regenerate the list for changes and updates. Click on the Examine icon for a row to open the Test tab for that specific row of data.
Note
The delivered set of Database Types within the application may not be removed.
Test tab
The Test tab provides opportunity to compare the Database Type From and Database Type To translation to see if it validates as acceptable. The user may first select a row within the Results tab and click on the Examine icon to open this tab, or they can open this tab directly and manually enter the field values for the test. This tab is only used should a new manually entered Rule be required for translation.
Delete a Data Type Translation
Only manually added Rules may be deleted from the system. The Rules stored within Results as system delivered are display only.
While viewing the grid for Translation Rules on the Rules tab, click on the Delete 
icon for a row to remove it from the list.
The confirm message displays. Click OK button to proceed and remove the row from the grid, or click Cancel button to exit back to the page.
Database Command Details
The tab for Database Commands provides for storing the DB Commands to build out the scripting based upon the database selected. This section allows the team on-site to add custom DDL Scripting templates. The system provided defaults are set as not changeable.
Note
The Command Name may be set as Sentence or all small or all caps without any affect to the programming that uses these commands.
The user may view the Command line within each of these Commands.
.png)
Add a Database Command
To Add a Database Command line, click the Add 
icon. Fill in the fields as identified below.
Section | Field | Description |
|---|---|---|
Details | Database Type* | This value will default from the Data Type selected. (Required) |
Details | Application* | Select the specific Application from the drop down list. (Required)
|
Details | Command Name* | Name of the Database Command. (Required) |
Details | Command | Scripting for the command. |
Command Information | Available Parameters | This field provides the list of possible fields for use. The parameters feed from the selected Command Name. (Display Only) |
Command Information | Result Fields | This field provides the list of field values as a result of this. (Display Only) |
Audit | Various | These fields are all display only and are updated by actions. |
Note
The system delivered set of Database Types are not allowed for removal.
Edit a Database Command
Click the Edit icon to allow for field maintenance for manually entered Database Commands only. The Command Name is not editable once saved as it is part of the key.
When finished with edits, click the Save icon to complete.
Delete a Database Command
To Delete a manually entered Database Command, click on the Delete icon. A warning message displays. Click OK to proceed and remove the database command. The system delivered defaults may not be removed.
Validation of Database Commands
The scripting of Command for each Database Command goes through a validation when saved. In the image below, the command code is failing validation for a non-standard command name. The user may select to Continue Save to save the updates as they are or they may Cancel Save to correct the Command scripting.
Data Type XML Details
The tab Data Type XML provides the translations for the ETL from Database Type format to XML format. This page should not require maintenance as the current list of values should be complete. However, if a new Database Type is added, then all XML translation details will need to be updated in this page manually.
Was this article helpful?
