Database tools

When you select the database scheme or DataConnect scheme, you can test the database connection, generate the test data, or generate the SQL for the database table. To export binary large object fields, you must use a dynamic outbound query.

Database connection testing

You select the Test DB Connection action to verify that the server can communicate with the database. The table name is used to run a select 1+1 from [table name] query to the database. The color of the Database section header changes to red if an error occurred. To view the errors, review the server logs in the IBM® TRIRIGA® administrator console. For information about the administrator console, see the IBM TRIRIGA Application Platform: Administrator Console User Guide.

Required table access for the DataConnect scheme

The DataConnect scheme is separate from the IBM TRIRIGA internal APIs and uses the IBM TRIRIGA Connector for Business Applications to communicate with TRIRIGA. The DataConnect scheme allows access to the database to inspect, read, and write to the tables.

The DataConnect scheme requires access to the following tables. Except for the DC_JOB table and S_ table, the following tables are used to determine the columns that are available for the DataConnect objects. The same tables must have Read capabilities for the user that is selected in the data source.

IBS_SPEC_TYPE_STAGE: Read access.
IBS_SPEC_TYPE: Read access.
SYS.COLUMNS (Microsoft SQL Server): Read access.
SYS.TABLES (Microsoft SQL Server): Read access.
IBS_MODULE: Read access.
ALL_TAB_COLUMNS (Oracle Database): Read access.
DC_JOB: Read and write access.
S_: Read and write access.

Generic SQL creation

You select the Generate SQL for Table action to create the generic SQL to define your database table. Before you select this action, enter the name of the database table and complete the data map. Each table that is used for inbound integration must have the following columns:

IMD_ID: The transaction ID that is unique to each entry in the table. This ID is not the same as the record ID.
IMD_STATUS: Identifies the status of each entry in the table. The default status of Ready identifies records to be processed. Other states include Processing, Completed, and Failed.
IMD_MESSAGE: Identifies any errors in importing the row. The same error messages are also displayed in the Integration Object form.
TRIRIGA_RECORD_ID: The internal record ID that IBM TRIRIGA uses to identify the record that is being updated or created.

The following sample code was generated for a people-record inbound integration with the previous fields.

-- Auto generated script for MSSQL.
CREATE TABLE example_people_in (
	IMD_ID NUMERIC (18, 0) IDENTITY(1,1) NOT NULL,
	IMD_STATUS VARCHAR(1000) COLLATE SQL_Latin1_General_CP1_CS_AS null DEFAULT 'Ready',
	IMD_MESSAGE VARCHAR(1000) COLLATE SQL_Latin1_General_CP1_CS_AS null,
	TRIRIGA_RECORD_ID VARCHAR(1000) COLLATE SQL_Latin1_General_CP1_CS_AS null,
	TRI_ID VARCHAR(1000) COLLATE SQL_Latin1_General_CP1_CS_AS null,
	FIRST_NAME VARCHAR(1000) COLLATE SQL_Latin1_General_CP1_CS_AS null,
	LAST_NAME VARCHAR(1000) COLLATE SQL_Latin1_General_CP1_CS_AS null,
	LANGUAGE VARCHAR(1000) COLLATE SQL_Latin1_General_CP1_CS_AS null
)

Test data creation

You select the Generate Test Data action to load your database with test data. This action is only available in the inbound direction.

After you create the database table and can connect to it, you can load your database with test data. You use the test data to run simple functional testing to verify that the mappings are correct and that the integration process works correctly. Enter the number of rows to be inserted into the database table in the Test Rows field. The data is generated as alphanumeric values. If you specify a default value in your data map, that value is inserted for all rows. This default value is useful when the field might be a locator field, a number field, a date field, or a list that requires a specific value.

If an external system is used to populate the staging database, the external system needs to populate the non-metadata fields only. The IMD_* columns are auto-populated. After the record is updated or created, the TRIRIGA_RECORD_ID column contains the record ID that identifies this record in IBM TRIRIGA internally.

Binary large object (BLOB) support

BLOBs are supported for both inbound and outbound database schemes. Supported BLOB types include binary fields, note fields, and document manager content. Image fields are not supported. To export BLOB fields, you must use a data map as a dynamic outbound query.

When you import binary fields and note fields in the data map, they are imported like other field types. The External attribute value for the BLOB field must match the database column name.

When you import or export document manager objects, the integration object must be configured to interact with the document business object of the document module. For instance, the data map for importing documents must be configured to map to the document business object.

No actual value exists for the binary content of the document on the object. Instead, you select any base object column, such as a text field in the General section, to use as a placeholder for your content. The external name can be whatever you want, but the value in the Default column must be CONTENT. This value is the trigger that connects the document manager object to the content.