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

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.

   

4. Click Finish to save the change.

To specify the output settings

These settings are all optional.

1. Click the Output Settings tab.
2. Click the Build expression... button ( ) or Rules Library and filter rows or apply the library business rule.
3. Specify the Debug option as needed.
4. Specify a Sample Count number. This controls the reporting increment of the number of records processed count. These counts are written to the process log starting at the specified value. This output allows you to track progress and helps troubleshoot any problems. By default this field is blank. If no value is entered, no records are written to the log file.

   See example:

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

   Thu July 14 11:27:35 2011 - <50> records processed

   Thu July 14 11:27:35 2011 - <100> records processed

   Thu July 14 11:27:35 2011 - <150> records processed

5. Click Finish to save the change.

To run the Database Write process

Before running the process, always run it in test mode first and preview the results. When you click the Preview button, the process runs only in the Control Center and does not access the database, so you can safely preview the results as if the database had been accessed.

1. Select the Option tab.
2. Make sure that the Run in test mode option is selected.
3. Click Preview to see the results from the test mode. You can also run the process or double-click the output entity (the database icon).
4. When you are satisfied with the results in the preview, clear the Run in test mode option and click Finish to save the change.
5. Run the process. The output will be written to the database.

To export a project with the Database Write process

1. From the Navigation or Project View, right-click the project and select Export. The Export Project Options window opens.
2. Specify the options as you would for a regular batch export or project definition. See Exporting a Project to a Batch Script or Exporting a Project Definition.
   • (Optional) If you want to load data from the database at the beginning of the batch processing, select the Include database source data with export option and select Include Database Read for <table_name>. See Reading Data from Database for a Batch Script for more information.
3. Click Export.