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

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).

   See example:

   You want to write back the final output from your US project to the Oracle database. So you added a Database Write process to us_utranfrmr p10 (44), the last output entity in the project. A new process, us_Database Write (11) is added and us_utranfrmr p10 (44) becomes an input entity and a blank output entity, us_tsqda p11 (45) is created.

   

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 Trillium Repository 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.

   See example:

   If you specify the number 10, the entry in the log file would look similar to the following:

   2013-05-07 15:52:38 02532 INFO TSSDA committed recordset after 10 rows

   2013-05-07 15:52:38 02532 INFO TSSDA committed recordset after 20 rows

   2013-05-07 15:52:38 02532 INFO TSSDA committed recordset after 30 rows

   2013-05-07 15:52:38 02532 INFO TSSDA committed recordset after 40 rows

   2013-05-07 15:52:38 02532 INFO TSSDA committed recordset after 50 rows

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.

   See example:

   

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.

   See example:

   In this example, Newaddrl1 is mapped to ACCOUNT_ADDRESS, TS City Name is mapped to ACCOUNT_CITY, and TS Postal Code is mapped to ACCOUNT_POSTCODE. When you run the process, those three attributes, ACCOUNT_ADDRESS, ACCOUNT_CITY, and ACCOUNT_POSTCODE, are updated with the values from us_utranfrmr p10(34). All other attributes are blanked out.