Define a Database Model

This page describes how to define database metadata and reverse-engineer a database.

Create and Reverse-Engineer a Database Model

This process uses a wizard composed of the following three steps:

  1. Create a new data server

  2. Create a new schema

  3. Reverse-engineer the datastores

  4. (Optional) Selectively reverse-engineer the datastores

Step 1: Create A New Data Server

To create a new data server:

  1. In the Project Explorer toolbar, click the metadata New Metadata button. The New Model wizard opens.

  2. In the Choose the type of Metadata tree, select RDBMS > <DBMS Technology> where DBMS Technology> is the name of the DBMS technology you want to connect to.

  3. Click Next.

  4. Select the parent folder or project for your new resource.

  5. Enter a File Name and click Next.

  6. Select a Module and click Finish.

    Refer to Install and Manage Modules for information on managing modules.

  7. The Server wizard opens.

  8. On the Server Connection page, enter the following details:

    • Name: Name of the data server.

    • Driver: Select a JDBC Driver suitable for your data server.

    • URL: Enter the JDBC URL to connect this data server.

    • User: The database username, if any.

    • Password: The user password, if any.

    • (Optional) AutoCommit: When this option is selected, Semarchy xDI Designer connects to the data server using auto-commit connections. This setting only affects the connections from the Designer (for example, when used for reverse-engineering) and does not affect the connections used by Runtimes.

  9. Click Connect to validate this connection.

  10. Click Next. The schema properties page opens.

Step 2: Create A New Schema

To declare a new schema:

  1. On the Schema Properties page, enter the following details:

    • Name: Select the checkbox next to this field and enter a user-friendly name for this schema.

    • Catalog Name (when applicable): Click on the Refresh Values to retrieve the list of catalogs and then select one.

    • Schema Name: Click Refresh Values to retrieve the list of schemas from the database and select the expected schema.

    • Reject Mask: Set the table name mask for the table containing the load rejects (error tables). Refer to Table Name Masks for more information.

    • Load Mask: Set the table name masks for the temporary load tables.

    • Integration Mask: Set the table name masks for the temporary integration tables.

    • Work Schema: Select a schema for storing the load and integration temporary tables for this data server. This schema is also referred to as the Staging Area. Click the …​ button to create a new schema definition and set it as the work schema. If no schema is selected for Work Schema, the Schema Name schema will be used.

    • Reject Schema: Select a schema for storing the errors (rejects) tables for this data server. Click the …​ button to create a new schema and set it as the reject schema. If no schema is selected for Reject Schema, the Schema Name schema will be used.

      It is recommended to configure two separate temporary and error schemas for each database server and set them as Work Schema and Reject Schema for all the data schemas. For example, SEMARCHY_TEMP and SEMARCHY_REJECTS. This avoids mixing application data (data schemas) and xDI tables in the same schema.

  2. Click Next. The Reverse Datastore page opens.

Step 3: Reverse-Engineer the Datastores

  1. In the Reverse Datastore page, optionally type a filter for the objects you want to retrieve.

    Use the _ and % wildcards to represent one or any number of characters.

  2. Optionally filter the type of objects that you want to return:

    • When All Datastore is selected (default), all the objects from the server are retrieved.

    • If you unselect All Datastore, checkboxes that correspond to the different object types available for this technology are enabled (VIEW, TABLE, …​). Select the types you want to retrieve.

  3. Click Refresh to refresh the list of datastores.

  4. Select the datastores that you want to reverse engineer in the list.

  5. Set the following reverse-engineering parameters:

    • Reverse FK: When selected (default), foreign keys will be included in reverse-engineering.

    • Delete No Longer Existing Column: This option is selected by default. When this option is selected, the reverse-engineering process that runs on an existing metadata deletes the columns that cannot be found anymore.

  6. Either click:

    • Next to select the columns that you wish to reverse engineer. The Selective Reverse page opens. Refer to Step 4: Selectively Reverse-Engineer the Datastores for more information.

      or

    • Finish to retrieves the entire structure of the selected datastore. Finally, press Ctrl+S to save your changes.

Step 4: Selectively Reverse-Engineer the Datastores

  1. Select or unselect the columns you wish or do not wish to reverse-engineer.

    By default:

    • All the columns are selected when you are reverse-engineering a new metadata model.

    • None of the columns are selected when you are reverse-engineering an existing metadata model.

    When reversing columns related to other columns (for example, foreign key), the related column name is not displayed.

  2. Click Finish to reverse-engineer the selected columns.

  3. Press Ctrl+S to save your changes.

Reverse-Engineer a New Schema

To reverse-engineer a new schema on an existing metadata:

  1. In the metadata file editor, select the root node.

  2. Right-click and select Action > Launch DataSchema Wizard.

  3. Follow the steps described in Step 2: Create A New Schema.

Reverse-Engineer An Existing Schema

To retrieve metadata changes from an existing schema, or to retrieve new table definitions, you must perform a new reverse-engineering.

To reverse-engineer an existing schema:

  1. In the metadata file editor, select the node corresponding to the schema.

  2. Right-click and select Action > Launch DataSchema Wizard.

  3. Click Next on the first page of the wizard.

  4. On the second page follow the steps described in Step 2: Create A New Schema.

Set the Delete No Longer Existing Column property to define whether you want to keep or delete the columns that cannot be found anymore.

Table Name Masks

Table Name Masks define name patterns for the temporary objects created at run-time.

A table name mask is a string that corresponds to a table name. It can be parameterized using the following variables:

  • [number]: Automatically generated increment for the load tables, starting with 1.

  • [targetName]: Name of the target table of a mapping.

  • ${variable}$ or %{variable}%: A session variable that is set at run-time.

Note that the resulting string must be a valid table name.

For example, L_[targetName]_[number] would create Load tables named L_CUSTOMER_1, L_CUSTOMER_2, etc. for a mapping loading the CUSTOMER table.

Create and Use a Metadata Query

A SQL Query can be reverse-engineered and used in a database metadata.

To create a Query:

  1. Create a folder to store queries:

    1. Right-click the database node in the metadata editor and click New > Query Folder.

    2. Enter a Name for the folder in the Properties view.

  2. Right-click the folder in the tree view and click New > Query.

  3. Enter a Name for the query.

  4. Enter an Expression that takes the form of a SQL SELECT query.

  5. Save the metadata.

  6. Right-click the query and click Actions > Reverse.

The reversed query can be used in mappings as Source like any other datastore. However, it is not meant to be used as a target datastore.
It is possible to parameterize the query using XPath syntax.

Define Connection Initialization SQL Scripts

A Connection Initialization SQL Script is a user-defined SQL script that is run each time a connection is created with this database.

Use it when you need to run the same SQL script each time a connection opens for this database, to change global settings for this connection for example, such as the character set, and more…​

Any SQL statement supported by the target database and JDBC driver can be used.

To add a new Connection Initialization SQL Script:

  1. Right-click the server node in the tree view and click New > Connection Initialization SQL.

  2. Click the SQL Initialization Script property and define your SQL script inside.

Use the SQL Syntax supported by the database. As an example, depending on the database and JDBC driver, running semi-colon-separated SQL Queries may or may not be supported.
When multiple Connection Initialization SQL Script nodes are added on the same metadata, Semarchy xDI concatenates all the scripts using semi-colons, and then runs the resulting script. Note that depending on the database and JDBC driver, running semi-colon-separated SQL Queries may or may not be supported.