Version 23.4.8839

• Overview
• Connector Configuration
  • Settings Tab
    • Database Connection
    • Mappings
  • Automation Tab
    • Automation Settings
  • Advanced Tab
    • Performance
    • Other Settings
    • Miscellaneous
• Establishing a Connection
• Input Mappings
  • Creating an Input Mapping
    • UPSERT
    • INSERT
    • LOOKUP
  • Input Mappings as XML
  • INSERTing into Child Tables
    • REF
    • LOOKUP
    • Child Table XML
• Output Mappings
  • Creating an Output Mapping
    • Output Filters
  • Output Mappings as XML
  • Batching Output
    • Overwriting the SELECT Query
  • Only Process New or Changed Records
    • Using a Last Modified Column
    • Using a Custom Column
  • SELECTing From Multiple Tables
    • Parent-Child Foreign Key Relationships
    • Parent-Child Output Mapping XML
• Input/Output Mappings
  • Creating an Input/Output Mapping
    • Input Parameters
  • Input/Output Mappings as XML
    • Input XML
    • Output XML

The DB2 Connector supports storing and retrieving data from DB2 databases.

Overview

After establishing a connection to the target database, the connector reads the list of tables/views present in the database. Create an Input Mapping or Output Mapping by selecting an available table and choosing columns from within this table. The connector generates an XML schema structure using the selected table and columns.

After an Input Mapping has been generated, the connector will automatically insert data into the database when it receives XML files that match the XML structure of the Input Mapping. Please see the Input Mappings section for more details.

After an Output Mapping has been generated, the connector will pull data from the database and use the pulled values to populate XML files that have the same XML structure as the output mapping. Please see the Output Mappings section for more details.

Input/Output Mappings are a special kind of mapping used for Stored Procedures that both require input (parameters) and generate output. Please see the Input/Output Mappings section for more details.

Connector Configuration

This section contains all of the configurable connector properties.

Settings Tab

Database Connection

Settings related to establishing the database connection.

• Connector Id The static, unique identifier for the connector.
• Connector Type Displays the connector name and a description of what it does.
• Connector Description An optional field to provide a free-form description of the connector and its role in the flow.
• Settings Format Whether to specify the connection settings as a list of properties or a connection string
• Connection String The database credentials in connection string format. Only applicable when Settings Format is ‘Connection String’, and is used in place of the other connection fields.
• Connection String Type Whether to authenticate with DB2 using Windows Authentication or DB2 Authentication. If set to Windows Authentication, the credentials for the Windows user running CData Arc are used. Only applicable when Settings Format is ‘Property List’.
• Server The host name or IP address of the server hosting the database.
• Database The name of the database to connect to.
• User The user credential that has permission to access the database. Only applicable when Connection String Type is ‘DB2 Authentication’.
• Password The password credential associated with the specified User. Only applicable when Connection String Type is ‘DB2 Authentication’.

Mappings

Establish mapping relationships between database tables and XML document structures. Once these mappings are created, XML data can be inserted into the database and data retrieved from the database can be pushed out as XML.

• Input Mappings Input mappings create a relationship between an XML document structure and a database insert (or update). When XML files that match the structure of the input mapping reach the DB2 Connector, the connector makes an insert/update call to the database with the values from the input file. For more information on generating Input mappings, see the Input Mappings section.
• Output Mappings Output mappings create a relationship between an XML document structure and a database select statement. When the connector receives data from the database, it populates an XML file with the same XML structure as the Output mapping. This XML file is then pushed on to the next connector in the Arc flow. For more information on generating Output mappings, see the Output Mappings section.

Automation Tab

Automation Settings

Settings related to the automatic processing of files by the connector.

• Send Whether XML files that match an Input mapping will automatically be inserted into the database.
• Retry Interval The amount of time before a failed insert/update is retried.
• Max Attempts The maximum number of times a failed insert/update will be retried before an error is thrown by the connector.
• Receive Whether data should automatically be pulled from the target database and used to generate XML files that match the configured Output mappings.
• Receive Execution Interval The amount of time between automatic attempts to retrieve data from the database.
• Minutes Past the Hour The minutes offset for an hourly schedule. Only applicable when the interval setting above is set to Hourly. For example, if this value is set to 5, the automation service downloads at 1:05, 2:05, 3:05, etc.
• Time The time of day that the attempt should occur. Only applicable when the interval setting above is set to Daily, Weekly, or Monthly.
• Day The day on which the attempt should occur. Only applicable when the interval setting above is set to Weekly or Monthly.
• Minutes The number of minutes to wait before attempting the download. Only applicable when the interval setting above is set to Minute.
• Cron Expression A five-position string representing a cron expression that determines when the attempt should occur. Only applicable when the interval setting above is set to Advanced.

Advanced Tab

Performance

Settings related to the allocation of resources to the connector.

• Max Workers The maximum number of worker threads consumed from the threadpool to process files on this connector. If set, this overrides the default setting on the Settings > Automation page.
• Max Files The maximum number of files sent by each thread assigned to the connector. If set, this overrides the default setting on the Settings > Automation page.

Other Settings

Settings not included in the previous categories.

• Command Timeout The command execution timeout duration in seconds.
• Flat Mapping Scheme Governs which Input Mapping will be used to process incoming flat files based on the filename of the document. Glob matching is supported, and multiple mappings may be defined by separating each mapping with a semicolon. For example, setting this field to ‘INVOICE=Invoice;ORDER=Order;’ would cause the connector to use the Invoice Input Mapping to process any files with ‘INVOICE’ in the filename, and any other files with ‘ORDER’ in the name would be processed using the Order Input Mapping. Comparisons are case-insensitive.
• Local File Scheme A scheme for assigning filenames to messages that are output by the connector. You can use macros in your filenames dynamically to include information such as identifiers and timestamps. For more information, see Macros.
• Log Messages Whether logs from processed files will include a copy of the file itself.
• Save to Sent Folder Whether files processed by the connector should be copied to the Sent folder for the connector.
• Output File Format Whether records pulled from the database should be formatted as XML or CSV.

Miscellaneous

Miscellaneous settings are for specific use cases.

• Other Settings Enables you to configure hidden connector settings in a semicolon-separated list (for example, setting1=value1;setting2=value2). Normal connector use cases and functionality should not require the use of these settings.

Establishing a Connection

Establishing a connection using DB2 Authentication requires the following connection properties:

• Server
• Database
• User
• Password

These can be configured as individual properties or a database connection string.

Windows Authentication can be used in place of DB2 Authentication, in which case the Windows user account running Arc is used to authenticate to DB2.

Input Mappings

Input Mappings represent an INSERT/UPDATE to one or more tables in the destination database. They are created through the visual Mapping Editor panel, and then modeled internally as XML. Files processed by the connector that match the XML structure of an Input Mapping will automatically be converted into INSERT or UPDATE queries.

The process of converting XML to database queries is detailed in the Input Mappings as XML section; conceptually, the connector is simply reading values from XML elements and using them when making INSERT/UPDATE statements.

Creating an Input Mapping

Click the ‘Add mapping’ button (+) to create a new mapping. Select the target table/view from the list of available tables in the database to bring up the Mapping Editor panel. In the Mapping Editor, select the table columns that should be included in the INSERT/UPDATE. For simple INSERTs, this is all that is required to generate a mapping.

After an Input Mapping is created, its XML representation can be viewed by clicking the ‘Code’ button (</>) button next to the mapping name.

UPSERT

UPSERT settings govern how the connector will decide whether to INSERT or UPDATE. The most common case is to set UPSERT by: to the primary key of the table. With this configuration, the connector will read the primary key value from the input XML, query the database to see if this key already exists in the table, update the record if it does exist, and insert a new record if it does not. UPSERT by: can be set to columns other than the primary key, but this may result in multiple records being returned from the database. In this case, the connector updates the first record that is returned.

If Perform this query to select the UPSERT key: is set to a SELECT query, the connector will compare the results of this query to the input XML to determine whether to INSERT or UDPATE. For example, if this field is set to the following query:

SELECT CustomerName WHERE CustomerAddress = 101 Main Street

Then the connector will first run this query against the database, then look for an CustomerName element in the input XML, and finally compare the XML value with the value returned by the database. If the values match then the connector will UPDATE that record, otherwise it will insert a new record.

INSERT

If UPSERT is disabled in the Input Mapping, the connector will automatically set the query action to INSERT. The connector will attempt to INSERT new records for each input XML file without querying the database to see if the record already exists.

LOOKUP

Some columns have a LOOKUP option available. The connector has detected that these columns have a foreign key relationship with another table in the database. If LOOKUP is enabled, the connector will query this external table prior to INSERTing, and use the value that is returned in the INSERT. The LOOKUP by: field determines what column will be used to query the external table.

As an example, imagine INSERTing a Purchase Order (PO) into a database where one of the fields in the ‘PO’ table is an internal CustomerID. Imagine that the input XML will have a CustomerName element, but not the customer’s internal ID. However, another table in the database, ‘Customers’ has both the name and ID, so it can be used to lookup the ID value based on the name. The connector will recognize that the CustomerID column of the ‘PO’ table is a foreign key, and enable LOOKUP for that column. Setting LOOKUP by: to CustomerName will instruct the connector to query the ‘Customers’ table with a statement like this:

SELECT CustomerID FROM Customers WHERE CustomerName = @CustomerName

In this query, @CustomerName represents the value in the CustomerName element from the input XML.

With this configuration, the connector will first retrieve the CustomerID from ‘Customers’, then use that value when INSERTing into the’PO’ table.

Input Mappings as XML

Input Mappings are represented in XML, with the following structure: a parent element identifies the table to insert into, and each child element corresponds to a column in the target table. The Input Mapping establishes the connection between XML elements and database columns, so that the connector can read values from incoming XML documents and use them in INSERT or UPDATE queries.

The following XML is an example Input Mapping for a ‘Students’ table:

<Items>
    <Students action="insert">
        <StudentID key="true" />
        <Name/>
        <Grade type="int"/>
        <GPA/>
    </Students>
</Items>

In the above example, StudentID, Name, Grade, and GPA are each columns of the ‘Students’ table. When the connector processes an XML file that matches this structure, it will automatically insert the values from that XML into the ‘Students’ table. The following is an example XML input file for the above mapping:

<Items>
    <Students>
        <StudentID>12554</StudentID>
        <Name>Ferris Bueller</Name>
        <Year>11</Year>
        <GPA>1.8</GPA>
    </Students>
    <Students>
        <StudentID>12921</StudentID>
        <Name>Hermione Granger</Name>
        <Year>9</Year>
        <GPA>4.0</GPA>
    </Students>
</Items>

When the connector processes the above input file, it will INSERT or UPDATE two records in the ‘Students’ table, one for each Students element. Note that the primary key of the table does not need to be included in the input XML if the database can automatically generate the primary key via AUTOINCREMENT.

INSERTing into Child Tables

Input Mappings support INSERTing into multiple tables with a single mapping. When creating an Input Mapping with multiple tables, the tables should have a foreign key relationship to each other such that one table is the child of another table. Otherwise, use multiple Input Mappings to insert into tables that are not related through a foreign key.

To INSERT into child tables, first create an Input Mapping that targets the parent table. Then add child tables from within the Input Mapping Editor using the +Add button in the upper-left. Select the appropriate table from the list of tables and the designer will automatically populate with the hierarchical structure of tables. Select the columns of the child table that should be included in the Input Mapping.

REF

Some columns within child tables will be marked with a blue ‘REF’ tag. The connector has detected that these columns correspond to the primary key in the parent table. After making the insert into the parent table, and before making the insert into the child table, the connector will query the parent table for the primary key of the record it just inserted. The connector will then use this value when inserting into the child table.

This is useful when the primary key of the parent table is generated by the database, such as an AUTOINCREMENT key. In this case, this column is not passed in as input XML, so the connector does not know the value of the column until after it has INSERTed the record into the parent table.

In the Input Mapping XML, the ‘ref’ attribute indicates that a column can be referenced later or that the column should columns that are generated by the database (e.g. AUTOINCREMENT primary keys) include a ‘ref’ attribute with the reserved value @@LAST_INSERT_ID:

<film action="upsert">
    <film_id key="true" upsert="film_id" ref="@@LAST_INSERT_ID" />

This indicates that the film_id value will be retrieved after the insert and can be referenced later in the mapping (usually in a child table). To reference this value later, set the ‘ref’ element to the name of the AUTOINCREMENT column:

<film_id key="true" ref="@film_id" />

In the above example, film_id is the name of the primary key in the parent table, and also the name of a column in the child table. The ‘ref’ syntax instructs the connector to retrieve the film_id from the parent table after inserting, and using that retrieved value as the film_id column in the child table.

LOOKUP

LOOKUP logic for child tables is the same as parent tables. Please see the LOOKUP section in Creating an Input Mapping for more details.

Child Table XML

The Input Mapping XML structure will be multi-layered when inserting into parent and child tables. The following XML is an example Input Mapping for two tables related by a foreign key:

<film action="upsert">
    <film_id key="true" upsert="film_id" ref="@@LAST_INSERT_ID" />
    <description />
    <language_id />
    <rating />
    <release_year type="date" />
    <title />
    <film_actor>
        <actor_id key="true" />
        <film_id key="true" ref="@film_id" />
        <last_update type="datetime" />
    </film_actor>
</film>

In the above example, ‘film’ is the parent table and ‘film_actor’ is the child table. The film_id column is the primary key to the ‘film’ table, and a foreign key in the ‘film_actor’ table. When the DB2 Connector processes an input XML file that matches this structure, it will update both the ‘film’ and ‘film_actor’ table and ensure that the keys linking the two records are the same.

Output Mappings

Output Mappings represent a SELECT query from one or more tables in the destination database. They are created through the visual Mapping Editor panel, and then modeled internally as XML. The results returned from the database are used to populate XML documents that match the structure of the Output Mapping. The process of converting database responses to XML is detailed in the Output Mappings as XML section.

Creating an Output Mapping

Click the ‘Add mapping’ button (+) to create a new mapping. Select the target table/view from the list of available tables in the database to bring up the Mapping Editor panel. In the Mapping Editor, select the table columns that should be returned in the SELECT statement. For simple SELECTs, this is all that is required to generate a mapping.

After an Input Mapping is created, the query it represents can be executed by clicking the ‘Execute’ button (a black play button) next to the mapping name. Additionally, the mapping’s XML representation can be viewed by clicking the ‘Code’ button (</>) button next to the mapping name.

Output Filters

The Mapping Editor includes a ‘Filters’ panel to define rules that restrict the records that are returned by the database. Each rule corresponds to a WHERE statement in the resulting SELECT query, and compares a specified column against a specified value; only records with a matching column value will be pulled.

Rules are combined with AND and OR logical operators, and rules can be grouped together to preserve order-of-operations when many rules are applied.

Output Mappings as XML

Output Mappings are represented in XML, with the following structure: a parent element identifies the table to SELECT from, and each child element corresponds to a column in the target table. The Output Mapping establishes the connection between database columns and XML elements, so that the connector can read values from database results and use them to populate XML documents.

The following XML is an example Output Mapping for a ‘Students’ table:

<Items>
    <Students selectQuery="SELECT * FROM `Students` WHERE `GPA` = 4.0">
        <StudentID key="true" />
        <Name/>
        <Grade type="int"/>
        <GPA/>
    </Students>
</Items>

In the above example, StudentID, Name, Grade, and GPA are each columns of the ‘Students’ table. When the Output Mapping is executed, the connector will run the specified selectQuery and generate an XML document for each record that is returned. The following is an example XML Output file for the above mapping:

<Items>
    <Students>
        <StudentID>12921</StudentID>
        <Name>Hermione Granger</Name>
        <Year>9</Year>
        <GPA>4.0</GPA>
    </Students>
</Items>

Batching Output

When querying for output, you can configure the connector to support batching records together into a single document. The Max Records setting governs how many records should be included in a single message, and the Batch Size setting indicates how many Batch Messages should be included in a single Batch Group.

Overwriting the SELECT Query

The Output Mapping XML displays the SELECT query that will be run against the target database. The connector builds this query based on the settings in the Output Mapping, but the query can be manually overwritten if fine-grain control over the query is required.

Simply edit the ‘selectQuery’ attribute of the element that shares a name with the database table and set it to any arbitrary SELECT query.

Only Process New or Changed Records

Output Mappings can be configured to only retrieve records that have been updated or added recently. The connector can use two approaches for detecting which records are new or modified.

The first approach requires a DateTime column that represents the last-modified time for records, and the second requires a column that explicitly stores a value representing whether a record should be processed or not.

Using a Last Modified Column

In the Mapping Editor, expand the Advanced settings at the bottom of the panel. If a DateTime column is detected, the option will be available to Use column for processing new or changed records. Set this to the appropriate DateTime column that represents the last-modified time for records.

Using a Custom Column

Output Mappings can be configured to update a column in the table for records that are successfully pulled from the database. This can be combined with a Filter rule that only SELECTs records with a column value indicating that it has not yet been pulled (or should be pulled again).

To enable column updating after processing, expand the Advanced settings at the bottom of the Mapping Editor. Enable Update column with value when rows are processed successfully, then specify which column should be updated and the value that should be set in this column.

For example, an Output Mapping might be configured to update the Processed column to ‘1’ after pulling a record. Then, a Filter rule could be applied that restricts records to only those where Processed not equal 1.

In this example, when the connector processes the output template, it creates a SELECT statement based on the template. The template would execute “SELECT Id, AccountId, Amount FROM INVOICE”, create an XML file for every record in the result set based on the template, and place those files in the Output folder for this connector.

SELECTing From Multiple Tables

Output Mappings support querying multiple tables with a single mapping. When creating an Output Mapping with multiple tables, the tables should have a foreign key relationship to each other such that one table is the child of another table. Otherwise, use multiple Output Mappings to SELECT from tables that are not related through a foreign key.

To query child tables, first create an Output Mapping that targets the parent table. Then add child tables from within the Output Mapping Editor using the +Add button in the upper-left corner. Select the appropriate table from the list of tables and the designer will automatically populate with the hierarchical structure of tables. Select the columns of the child table that should be included in the Output Mapping.

Next, a WHERE filter should be applied to the child table mapping to reflect the foreign key relationship with the parent table.

Parent-Child Foreign Key Relationships

Typically, When SELECTing from a parent table and child table, records should only be pulled from the child table if a foreign key matches the primary key from the parent table.

As an example, imagine an Output Mapping that pulls purchase order data from a parent table, ‘PurchaseOrders’, and a child table, ‘PurchaseOrderLineItems’. Each record (order) pulled from ‘PurchaseOrders’ should also pull the line items associated with that order. The two tables are related by a shared column, for example a PONumber column that is the primary key of the ‘PurchaseOrders’ table and a foreign key in the ‘PurchaseOrdersLineItems’ table. Line item records should only be pulled from the child table if the line item’s PONumber matches the PONumber of the order being pulled from the parent table.

To establish this relationship in an Arc Output Mapping, add a new Filter to the child table mapping and configure it as follows:

• In the first (left-hand) dropdown, select the column of the child table that is the foreign key to the parent table. In the above example, this would be the PONumber column of the ‘PurchaseOrderLineItems’ table.
• In the middle (operator) dropdown, select ‘equal’
• In the last (right-hand) input box, first use the dropdown arrow to the right to select ‘REF’. Afterwards, the input box should have the $ prefix, indicating that it is referencing the parent table
• Use the last (right-hand) dropdown to select the parent table column that should match the child table’s foreign key. In the above example, this would be the PONumber column of the ‘PurchaseOrders’ table.

Parent-Child Output Mapping XML

The XML representation of an Output Mapping that includes a parent and a child table has a hierarchical structure to reflect the parent-child relationship. Both the query to SELECT from the parent table and the query to SELECT from the child table will be included in the XML:

<PuchaseOrders selectQuery="SELECT * FROM `PurchaseOrders` ">
  <PONumber key="true"/>
  <AccountId/>
  <Amount/>
  <AppliedAmount/>
  <Balance/>
  <BillingCity/>
  <BillingCountry/>
  <BillingLine1/>
  <BillingPostalCode/>
  <BillingState/>
  <InvoiceLineItem selectQuery="SELECT * FROM `PurchaseOrderLineItems` WHERE `PONumber` = ${PONumber}">
    <Id key="true"/>
    <PONumber/>
    <ItemAmount/>
    <ItemName/>
    <ItemQuantity/>
  </InvoiceLineItem>
</PurchaseOrders>

The ‘${PONumber}’ syntax in the child table’s query indicates that the value is referencing a column (the PONumber column) from the parent table.

If multiple records are pulled from the ‘PurchaseOrderLineItems’ table, the InvoiceLineItem element (and all of its children) will appear multiple times in the output XML.

Input/Output Mappings

Some database operations require input and produce output. Input/Output Mappings provide a way to set an XML schema for both input files and output files for the same database operation.

Creating an Input/Output Mapping

Input/Output Mappings are created using the same approach as Input Mappings and Output Mappings. Each mapping targets a specific Stored Procedure, and the Mapping Editor wizard lets you view the expected parameters for the Stored Procedure.

Input Parameters

The input parameters for a Stored Procedure are defined by the database system, so no Mapping Editor configuration is required to select the relevant input parameters. The parameters advertised by the system are displayed in the Mapping Editor, and these cannot be de-selected or otherwise configured.

Input/Output Mappings as XML

Once an Input/Output Mapping has been created, an XML template is generated to match the required input parameters, and an XML template is generated to handle the output from the Stored Procedure.

Input XML

The XML template for input files follows the same principles as XML templates for Input Mappings; files that match the template XML structure are interpreted as input to the database operation. See Input Mappings as XML for more details.

Output XML

Output from a Stored Procedure is not defined in advance by the database system. As a result, it might be necessary to provide a test set of input parameters so the connector can invoke the procedure and detect the structure of the output.

To provide a set of sample inputs, use the Show Sample Data button in the top right of the Mapping Editor. After you provide a test set of inputs in the resulting modal, the connector displays the XML structure of the output as it received it. Copy this XML structure or use the Save Sample Data button to use this XML structure in other areas of the application, such as an XML Map Connector template.