Writing Back Data to a Database - trillium_discovery - trillium_quality - Latest

Trillium Control Center

Product type
Software
Portfolio
Verify
Product family
Trillium
Product
Trillium > Trillium Discovery
Version
Latest
Language
English
Product name
Trillium Quality and Discovery
Title
Trillium Control Center
Copyright
2024
First publish date
2008
Last updated
2024-10-18
Published on
2024-10-18T15:02:04.502478

The database write function is available as a Database Write process in TS Quality. The Database Write process allows you to link the output of the process or project to a database table, so you can directly return the results to the database without using any other ETL tools.

Warning: This functionality is designed to support interest in writing to staging environments in production processes or temp tables in test environments. The Database Write process will overwrite the database table and will not append or update the data. The attributes in the table that are not selected for mapping will be blanked out. We recommend the database write operation always be performed in a staging area and not directly in the original tables.

Use the following steps to configure and run the Database Write process:

  1. Add a Database Write process
  2. Specify the Database Write process options
  3. Map the output attributes
  4. Specify the output settings
  5. Run the Database Write process
  6. Export the project for a batch processing or project definition  

Add a Database Write process

To add a Database Write process

  1. From the Navigation or Project View, right-click the output entity which you want to write to the database and select Add process branch. The Create Process window opens.
    Note: A Database Write process can be added from the Add process branch option only; it cannot be added from the Insert process option.
  2. In Process Selection, select Database Write and click OK. The Database Write process is added to the process flow and an empty output entity is created (the database icon).

Specify the Database Write process options

To specify the Database Write process options

  1. Open the Database Write process for editing. The Database Connection Details window opens.
  2. Select the Type of the database from the drop-down list.
    Note: See the TrilliumRepository Administrator's Guide for details about database types.
  3. Enter the name of the database in the Database field.
  4. Enter the name of the table in the Table field.
  5. Enter the Username and Password for the database.
  6. (Optional) The password is always displayed in the control file (STX file). If you want the password to display encrypted, select Encrypt.
    Note: The password option will become the Reset password option after encryption. The next time you enter the username and password, check the Reset password option and re-enter your password.
  7. Optional: Enter a numeric value in Commit Frequency. It controls the frequency of the records committed to the database and allows you to track progress of the database write process. This option is useful when you want to locate process hangs. The commit information is written out in the log file. Right-click the process and select View> Log File or check the log file (tssda.log) in the metabase/logs folder in the server path.
  8. Click Validate database settings to validate the database connection.

    When the connection is established, the message "Database connection has been validated" displays. If the message "Database connection has not been validated" is displayed, check the loader connection and make sure the settings are correct. You cannot go to the next procedure (mapping outputs) until the database settings are validated.

    Note: If your Trillium Quality batch project includes a Database Read/Write process, uses an ODBC data source with an ODBC driver (database adapter) and the Vendor-Supplied ODBC Adapter, and you have the Trillium server installed on a Linux system, an error is returned when you validate the project's database settings. A project with this configuration cannot be validated because running the exported batch script in a Linux environment is not supported. To support this configuration, the Trillium server must be installed on a Windows system.

Map the output attributes

To map the output attributes

When the database settings are validated, you are ready to map the attributes from the input to your database table.

  1. Select the Schema Editor tab. The Schema Editor opens. The Trillium attributes in the input entity are listed in the left pane and the attributes in the database table are listed in the right pane.
    Note: The width displayed in the right pane is the attribute's width in the database table, but the encoding is always UTF8, which is the internal processing encoding in the Control Center. When the data is written back to the database, the encoding will be converted back to the original database encoding.
  2. Determine which attribute from the input entity should be copied to which attribute in the database table.
  3. Drag and drop each attribute from the left pane on top of the Output Attribute Name in the right pane. You must position the cursor so that there is a box around the database attribute to which you are mapping this input. You can select multiple attributes to drag.

    Note the following:

    • Ensure to map all required attributes. The attributes that are not mapped will not be generated.
    • When an attribute is mapped to an attribute smaller in width, the following message displays: "Your output attribute is shorter than the input which means that your data may be truncated. Would you like to continue?"Click Yes to continue the mapping. Click No to cancel the mapping.
    • The maximum attribute size is 32KB for database read/write. Attributes that are bigger than 32KB such as Text fields in SQL Database will be truncated.