Infrastructure Objects

Before XDM tasks can be created, certain infrastructure objects must exist. These infrastructure objects describe which usernames are used when connecting to the different databases that are involved, file system paths that XDM can use to store temporary data, and the details for the JDBC connections themselves.

Every task type requires a connection object for its source and target database. The main setting for a connection object is the JDBC URL under which a database can be accessed. Every connection needs to have associated credentials. Typically, a credential object is a combination of a username and password, but it can also be a username and a certificate.

If, for executing different types of tasks, you need to connect to the same JDBC URL with different credentials, you must create multiple connection objects that use the same JDBC URL, but refer to a different credential object. In this tutorial, we use three connections, two of which refer to the same JDBC URL but use different credentials.

In addition to a JDBC URL and a credential object, the connection object can also have an associated storage location. A storage location represents a directory on the same server where a database is running, and a file transfer mechanism (such as FTP or SFTP) that XDM can use to copy data from or to this directory. Storage locations also need to have associated credentials so that XDM can log on to the server using the selected file transfer mechanism. For any given database server, the credentials for accessing the database itself and the credentials for FTP access to the storage location’s directory on that server are not necessarily identical. Therefore, you may have to create two credential objects for the same server.

The directory of a storage location is used for bulk data movement operations, which typically operate as server-side processes that can only work with local directories. Examples for this are: The Oracle data pump, the EXPORT command in Db2 for LUW, the UNLOAD utility in Db2 for z/OS, or the COPY command in PostgreSQL. All these facilities directly read from or write to local files on the server.

In XDM, a table copy task that copies between databases of the same type (e.g., from PostgreSQL to PostgreSQL) works by invoking bulk data movement mechanisms of the source and target database. If the source and target databases are on two different servers, XDM will transfer the extracted data from the source database server’s storage location to the target database server’s storage location using the specified file transfer mechanism.

By contrast, an XDM row level processor task works by reading and writing all data through the JDBC connection (using SELECT and INSERT statements). Therefore, to run a table copy task, the source and target connections need to have associated storage locations, but row level processor tasks do not. Since we are going to run both types of tasks in this tutorial, we need to have storage locations associated with the source and target connection.

The order in which we need to create these infrastructure objects is as follows:

  1. Create credentials

  2. Create storage locations (which use credentials)

  3. Create database connections (which use storage locations, and have separate credentials)

Creating credentials

There are two user accounts in the sample PostgreSQL instance. You must create two credential objects in XDM that correspond to these two user accounts. In addition, you need to create a credential object that represents the PostgreSQL super user. This super user account is necessary because in PostgreSQL, only the super user can temporarily disable triggers, which is required to execute a table copy task.

You also need to create a credential object for accessing the sample SSH server.

  1. In the left sidebar, click the menu category Infrastructure to expand the menu. Then click the menu item Credentials.

  2. Click the button + Create. This opens a dialog window. Enter the following information into the fields:

    Name

    Production user (read-only) on sample database

    User

    prodro

    Password

    pass456prod

  3. Click the button Create. The first new credential object will be inserted in the list of credentials.

  4. Click the button + Create again. The dialog window is shown again. Enter the following information:

    Name

    QA user on sample database

    User

    qa

    Password

    pass789qa

  5. Click the button Create. The second new credential object will be inserted in the list of credentials.

  6. Click the button + Create again. The dialog window is shown again. Enter the following information:

    Name

    Super user on sample database

    User

    postgres

    Password

    pass123postgres

  7. Click the button Create. The third new credential object will be inserted in the list of credentials.

  8. Click the button + Create again. The dialog window is shown again. Enter the following information:

    Name

    SSH user on sample server

    User

    sshuser

    Password

    pass123ssh

  9. Click the button Create. The fourth new credential object will be inserted in the list of credentials.

In addition to authenticating database access using a username and password, XDM can also use a certificate. In order to use certificate-based authentication, you must create a credential object, then edit it and activate the setting Certificate based authentication. However, for this tutorial, certificate-based authentication is not used.

Creating storage locations

Some task types require a file system path where extracted data can be stored. This file system path must be local to the database server. In XDM, storage locations are associated with a database connection, i.e., when XDM extracts data from tables in any given database connection, it will store the resulting files under the storage location that is associated with that database connection. Since the sample databases production and testing are located on the same PostgreSQL server, you can use the same storage location for both.

  1. In the left sidebar, click the menu category Infrastructure to expand the menu. Then click the menu item Storage locations.

  2. Click the button + Create. This opens a dialog window. Enter the following information into the fields:

    Name

    Directory /xdmdata on PostgreSQL server

    Address

    sample-ssh

    Working directory

    /xdmdata

    Credential

    Select SSH user on sample server from the drop-down list

  3. Click the button Create and edit. This will create the storage location object in XDM and open it in edit mode. Make sure that the following fields are set as shown:

    Platform

    Aix/Unix/Linux

    File transfer protocol

    Secure File transfer (SFTP)

    Port

    2222

    FTP root directory

    (leave empty)

  4. Click the button Save Changes.

Configuring database connections

The sample PostgreSQL instance contains two databases. In XDM, a connection object represents a combination of a database location (in the form of a JDBC URL) and a credential object. We need one connection to the database production with the username prodro, one connection to the database testing with the username qa, and one connection to the database testing with the username postgres.

To create the first connection object:

  1. In the left sidebar, click the menu category Infrastructure to expand the menu. Then click the menu item Connections. Click the button Button with an arrow pointing to the right-hand direction on the left side of PostgreSQL connections to open the list of existing PostgreSQL connections.

    Connections categories

  2. Click the button + Create. This opens a dialog window. Enter the following information:

    Create PostgreSQL connection

    Name

    Sample production database

    URL

    jdbc:postgresql://sample-pg:5432/production

    Credential

    Select Production user (read-only) on sample database from the drop-down list

  3. Click the button Create and edit. This will create the connection object in XDM and open it in edit mode.

  4. In the field Storage location, select Directory /xdmdata on PostgreSQL server from the drop-down box. Then click the button Save changes. The first connection object is now fully defined.

To create the second connection object:

  1. In the left sidebar, click the menu item Connections. Click the button Button with an arrow pointing to the right-hand direction on the left side of PostgreSQL connections to open the list of existing PostgreSQL connections. You should see the first connection in this list.

  2. Click the button + Create. The dialog window is shown again. Enter the following information:

    Name

    Sample testing database

    URL

    jdbc:postgresql://sample-pg:5432/testing

    Credential

    Select QA user on sample database from the drop-down list

  3. Click the button Create and edit. This will create the connection object in XDM and open it in edit mode.

  4. In the field Storage location, select Directory /xdmdata on PostgreSQL server from the drop-down box. Then click the button Save changes. The second connection object is now fully defined.

To create the third connection object:

  1. In the left sidebar, click the menu item Connections. Click the button Button with an arrow pointing to the right-hand direction on the left side of PostgreSQL connections to open the list of existing PostgreSQL connections. You should see the first two connections in this list.

  2. Click the button + Create. The dialog window is shown again. Enter the following information:

    Name

    Sample testing database as super user

    URL

    jdbc:postgresql://sample-pg:5432/testing

    Credential

    Select Super user on sample database from the drop down list

  3. Click the button Create and edit. This will create the connection object in XDM and open it in edit mode.

  4. In the field Storage location, select Directory /xdmdata on PostgreSQL server from the drop-down box. Then click the button Save changes. The third connection object is now fully defined.

This concludes the preliminary work. You are now ready to create XDM tasks.

Previous section: Sample environment | Next section: Table copy tasks