Define a database model

This page describes how to reverse-engineer a database, define database metadata, and work with database metadata in Semarchy xDI Designer.

Create and reverse-engineer database models

The reverse engineering process has a wizard with three or four steps:

  1. Create a data server.

  2. Create a schema.

  3. Reverse-engineer the datastores.

  4. (Optional) Selectively reverse-engineer the datastores.

Step 1: create a data server

To create a data server:

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

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

  3. Click Next.

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

  5. Enter a File Name, and click Next.

  6. Select a Module, and click Finish.

    Refer to Install and manage modules for information about managing modules.

  7. The Server wizard opens.

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

    • Name: the name of your data server.

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

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

    • User: the database username, if any.

    • Password: the user password, if any.

    • (Optional) AutoCommit: When you select this option, xDI Designer connects to your server using auto-commit connections. This setting affects only connections from the Designer, and not connections opened by runtimes.

  9. Click Connect to test the connection information.

Click Next to continue to the Schema Properties page.

Step 2: create a schema

To create a schema, set the following details on the Schema Properties page:

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

  • Catalog Name (if applicable): click on Refresh Values to retrieve the list of catalogs, and 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 error tables, the load rejects. 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 to store the temporary load and integration tables for this data server. This schema is also referred to as the staging area. Click the …​ button to create a schema definition and set it as the work schema. If you do not select a schema here, the metadata uses the one defined in Schema Name instead.

  • Reject Schema: select a schema to store the rejects for this data server. Click the …​ button to create a schema and set it as the reject schema. If you do not select a schema here, the metadata uses the one defined in Schema Name instead.

It is a good idea to configure separate temporary and error schemas for each database server, and set them as Work Schema and Reject Schema for all your data schemas. By doing so, you avoid mixing your application data and xDI tables. You can use a naming scheme such as SEMARCHY_TEMP and SEMARCHY_REJECTS.

Click Next to continue to the Reverse Datastore page.

Step 3: reverse-engineer the datastores

  1. On 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, Designer retrieves all the objects from the server.

    • If you clear All Datastore, Designer activates checkboxes that correspond to the different object types available for this technology (such as VIEW, TABLE, and more). Select the types you want to retrieve.

  3. Click Refresh to refresh the list of datastores.

  4. Select the datastores you want to reverse-engineer from the list.

  5. Set the following reverse engineering parameters:

    • Reverse FK: when selected, a reverse engineering process includes foreign keys.

    • Delete No Longer Existing Column: when selected, a reverse engineering process that runs on existing metadata objects deletes columns that are no longer present.

  6. Click one of the following two options:

    • Click Next to select the columns that you want to reverse-engineer. The Selective Reverse page opens. Refer to the next step for more information.
      or

    • Click Finish to retrieve the entire structure of the selected datastore. Press Ctrl+S to save your changes.

Step 4: selectively reverse-engineer the datastores

  1. Select or clear columns to choose which columns you want to reverse-engineer.

    By default:

    • All columns are selected when reverse-engineering a metadata model for the first time.

    • No columns are selected when reverse-engineering an existing metadata model.

    When reversing columns related to other columns, such as foreign keys, 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 existing metadata:

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

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

  3. Follow the same steps as for creating a 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 same steps as for creating a schema.

To define whether to keep or delete columns that cannot be found anymore, remember to set the Delete No Longer Existing Column property.

Create a table name mask

A table name mask is a string that corresponds to a table name. It defines name patterns for the temporary objects created at run time.

You can define a table name mask using the following variables:

  • [number]: automatically generated increment for the load tables, starting at 1.

  • [targetName]: name of a target table in a mapping.

  • ${variable}$ or %{variable}%: a session variable set at run time.

For a mapping that loads a CUSTOMER table, for example, the L_[targetName]_[number] mask results in load tables named L_CUSTOMER_1, L_CUSTOMER_2, and so forth.

The resulting string must reference a valid table name.

Create and use metadata queries

You can reverse-engineer an SQL query, and use it in metadata objects that represents a database. The reversed query works in mappings as a source, like any other datastore. However, it is not intended as a target datastore.

To create a query:

  1. Create a folder to store your 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 your 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.

It is also possible to parameterize the query using XPath syntax.

Square brackets ('[' and ']') are a special character in the Expression Editor. This can cause issues if a query needs them, such as when you have column names surrounded by brackets.

To use square brackets in your SQL query, double them ('[[' or ']]') in the Expression Editor. Semarchy xDI interprets them as single brackets in the actual query.

Define connection initialization SQL scripts

A Connection Initialization SQL Script is a user-defined SQL script that runs each time you connect to a database. These scripts are useful when you need to run the same SQL commands for each connection, such as when changing global settings.

You can use any SQL statement supported by the target database and JDBC driver.

To add a Connection Initialization SQL Script, do the following steps:

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

  2. Click the SQL Initialization Script property, and enter your SQL script in the field.

Make sure you use an SQL syntax supported by your database.

When you add multiple Connection Initialization SQL Script nodes to the same metadata, Semarchy xDI concatenates all the scripts using semicolons, and then runs the result as a single script.

Not all databases and JDBC drivers support SQL Queries separated by semicolons. You should configure your metadata and script nodes to better suit your environment.