Configuring the connector

You must set the connector's standard and connector-specific configuration properties before you can run it. Use one of the following tools to set a connector's configuration properties:

Standard connector properties

Standard configuration properties provide information that all connectors use. See Appendix A, Standard configuration properties for connectors for detailed information about these properties.

Important:
Because the connector for JDBC supports both the ICS and WebSphere MQ Integrator Broker integration brokers, configuration properties for both brokers are relevant to the connector.

In addition, refer to Table 5 for configuration information specific to the IBM WebSphere Business Integration Adapter for JDBC. The information in this table supplements the information in the appendix.

Table 5. Property information specific to this connector

Property Notes
CharacterEncoding This connector does not use the CharacterEncoding property
Locale Because this connector has been internationalized, you can change the value of the Locale property.
Note:
If you are using WebSphere MQ Integrator Broker as your broker, you must use the same locale for the adapter, the broker, and any applications.

Note that you must provide a value for the ApplicationName configuration property before running the connector.

Connector-specific properties

Connector-specific configuration properties provide information needed by the connector at runtime. Connector-specific properties also provide a way of changing static information or logic within the connector without having to recode and rebuild it.

Table 6 lists the connector-specific configuration properties for the connector. See the sections that follow for explanations of the properties.

Table 6. Connector-specific configuration properties

Name Possible values Default value Required
ApplicationPassword Password for connector user account
Yes*
 
ApplicationUserName Name of connector user account
Yes*
 
ArchiveProcessed true or false
true
 
No
 
ArchiveTableName Name of archive queue table
xworlds_archive_events
 
Yes if Archive Processed is true
AutoCommit true or false
false
 
No
 
CheckforEventTableInInit true or false
true
 
No
 
ChildUpdatePhyDelete true or false
false
 
No
 
CloseDBConnection true or false
false
 
No
 
ConnectorID Unique ID for the connector
null
 
No
 
DatabaseURL Name of the database server
Yes
 
DateFormat A time pattern String
MM/dd/yyyy
 HH:mm:ss
 
No
 
DriverConnectionProperties Additional JDBC driver connection properties
No
 
EventKeyDel Delimiter character or characters for object key column of event table
semicolon (;)
 
No
 
EventOrderBy
none, ColumnName,
 ColumnName, ...]
 
No
 
EventQueryType Fixed or Dynamic
Fixed
 
No
 
EventTableName Name of event queue table
xworlds_events
 
Yes, if polling is required; null (as a string) if polling is not required
JDBCDriverClass driver classname
Yes
 
MaximumDatabaseConnections Number of simultaneous database connections
5
 
Yes
 
PingQuery
SELECT 1 FROM <tablename>
 
No
 
PollQuantity Values are 1 to 500
1
 
No
 
PreserveUIDSeq true or false
true
 
No
 
RDBMS.initsession SQL statement that initializes every database session
No
 
RDBMSVendor MSSQLServer, Oracle, Others
Yes
 
ReplaceAllStr true or false
false
 
No
 
ReplaceStrList A set composed of a single character, a character delimiter, and the character's substitution string. Also, multiple such sets with a termination delimiter between them. Q,DSQNote: In the connector configuration tool, these characters represent a single quotation mark, followed by a comma, followed by two single quotation marks.
No
 
RetryCountAndInterval Count, interval in seconds 3,20
No
 
SchemaName Schema on which the events reside
No
 
SPBeforePollCall Name of the stored procedure to be executed for each poll call
No
 
StrDelimiter The character and termination delimiters used in the ReplaceStrList property
,:
 
No
 
TimingStats 0, 1, 2
0
 
No
 
UniqueIDTableName Name of table used for generation of IDs
xworlds_uid
 
No
 
UseDefaults true or false
false
 
Yes
 
UseDefaultsForCreatingChildBOs true or false
false
 
No
 
UseDefaultsForRetrieve true or false
false
 
No
 

*ApplicationPassword and ApplicationUserName are not required if you are using trusted authentication.

ApplicationPassword

Password for the connector's user account.

There is no default value.

ApplicationUserName

Name of the connector's user account.

There is no default value.

ArchiveProcessed

Specifies whether the connector archives events for which there are no current subscriptions.

Set this property to true to cause events to be inserted into the archive table after they are deleted from the event table.

Set this property to false to cause the connector not to perform archive processing. In this case, it does not check the value of the ArchiveTableName property. If ArchiveProcessed is set to false, the connector performs the following behavior:

If this property is set to false and the poll quantity is low, the connector appears to be polling the event table, but it is simply picking up the same events repeatedly.

If this property has no value, the connector assumes the value to be true. If the ArchiveTableName property also has no value, the connector assumes the archive table's name is xworlds_archive_events.

The default value is true.

ArchiveTableName

Name of archive queue table.

If the ArchiveProcessed property is set to false, it is unnecessary to set a value for this property.

The default name is xworlds_archive_events.

AutoCommit

This property makes the AutoCommit setting configurable. When set to true, all transactions are automatically committed. Some databases (such as Sybase) require AutoCommit to be set to true. If set to false, stored procedures on Sybase to fail.

If the database connection is lost, the connector will attempt to create a new connection to restart the complete processing as long as AutoCommit is set to false. If the new connection is invalid, or if AutoCommit is set to true, the connector returns APPRESPONSETIMEOUT, which results in the termination of the connector.

The default value is false.

CheckforEventTableInInit

Setting this connector property to false prevents the connector from checking for the existence of the event and archive tables during connector initialization. It is recommended that you always set it to true unless the JDBC driver you are using does not support the JDBC class DatabaseMetaData.

When the property is set to false, although the connector does not check for the existence of EventTable and ArchiveTable, the event and archive tables should always exist because the connector uses them during the initialization process. To prevent the connector from using the event and archive tables during initialization, set the property EventTableName to null.

The default value is true.

ChildUpdatePhyDelete

During an update operation, specifies how the connector handles data represented by a child business object that is missing from the incoming business object but exists in the database.

Set this property to true to cause the connector to physically delete the data record from the database.

Set this property to false to cause the connector to logically delete the data record from the database by setting the status column to the appropriate value. The application-specific information obtains the name of the status column and its value from the StatusColumnValue (SCN) parameter specified in its business-object level application-specific information. For more information, see Application-specific information at the business-object level.

Default value is false.

CloseDBConnection

This property makes the closing of the database connection configurable. When set to true, for every service call request and poll call, the database connection is closed. Setting this property to true impairs performance and is not advisable.

The default value is false.

ConnectorID

A unique ID for the connector. This ID is useful to retrieve events for a particular instance of the connector.

Default value is null.

DatabaseURL

Name of the database server to which the connector should connect.

If you use the WebSphere business integration system branded SQLServer driver, the recommended URL is:

jdbc:ibm-crossworlds:sqlserver://MachineName:PortNumber;DatabaseName=DBname
 
Important

If AutoCommit is set to false, you must set an additional parameter, SelectMethod: jdbc:ibm-crossworlds:sqlserver://MachineName :PortNumber;DatabaseName=DBname ; SelectMethod=cursorBy default, SelectMethod is set to direct. For more information, see "AutoCommit".

You must provide a value for this property in order for the connector to process successfully.

DateFormat

Specifies the date format that the connector expects to receive and return. This property supports any format that is based on the syntax as contained in Table 7.

Table 7 Defines the time format syntax using a time pattern string. In this pattern, all ASCII letters are reserved as pattern letters.

Table 7. Time format syntax

Symbol Meaning Presentation Example
G
 
era designator
(Text)
 
AD
 
y
 
year
(Number)
 
1996
 
M
 
month in year
(Text & Number)
 
July & 07
 
d
 
day in month
(Number)
 
10
 
h
 
hour in am/pm(1-12)
(Number)
 
12
 
H
 
hour in day(0-23)
(Number)
 
0
 
m
 
minute in hour
(Number)
 
30
 
s
 
second in minute
(Number)
 
55
 
S
 
millisecond
(Number)
 
978
 
E
 
day in week
(Text)
 
Tuesday
 
D
 
day in year
(Number)
 
189
 
F
 
day of week in month
(Number)
 
2
 (2nd Wed
 in July)
 
w
 
week in year
(Number)
 
27
 
W
 
week in month
(Number)
 
2
 
a
 
am/pm marker
(Text)
 
PM
 
k
 
hour in day(1-24)
(Number)
 
24
 
K
 
hour in am/pm(0-11)
(Number)
 
0
 
z
 
time zone
(Text)
 
Pacific
 Standard
 Time
 
' 
 
escape for text (Delimiter)
''
 
single quote (Literal)
'
 

Table 8. Examples using the US locale

Format pattern Result
"yyyy.MM.dd G 'at' hh:mm:ss z"
 
1996.07.10 AD at 15:08:56 PDT
"EEE, MMM d, ''yy"
 
Wed, July 10, '96
"h:mm a"
 
12:08 PM
"hh 'o''clock' a, zzzz"
 
12 o'clock PM, Pacific Daylight Time
"K:mm a, z"
 
0:00 PM, PST
"yyyy.MMMMM.dd GGG hh:mm aaa"
 
1996.July.10 AD 12:08 PM

DriverConnectionProperties

Besides the user name and password, a JDBC driver might need additional properties or information. The DriverConnectionProperties connector property will take additional properties that a JDBC driver needs, as name-value pairs. The properties should be specified as follows:

property1=value1[;property2=value2...]
 

The properties must be given as name value pairs, separated by semi-colons. The property is separated from its value by an equals sign (with no extra spaces).

For example, assume the JDBC driver needs license information and port number. The property name it expects for license information is MyLicense and the value is ab23jk5. The property name it expects for port number is PortNumber and value is 1200. The DriverConnectionProperties should be set to the value MyLicense=ab23jk5;PortNumber=1200.

EventKeyDel

Specifies the delimiter when the object_key column of the event table contains multiple attribute values.

There are two ways to retrieve the business object that has been created, updated, or deleted in the triggering application.

Note:
The order that the key values are defined should follow the same order as the key attributes in a business object.
Important:
If you use Date attribute data, avoid using a colon (:) delimiter, because it may be included in the attribute's data.

The default value is a semicolon (;), which is based on keys, not name_value pairs.

EventOrderBy

Specifies whether to turn off the ordering of events, or specifies an order of event processing that is different from the default order.

By default, at each poll the connector pulls only the number of events specified in its PollQuantity property, and orders event processing by the values in the event_time and event_priority columns of the Event table.

To cause the connector not to order events, set the value of this property to none.

To cause the connector to order by different columns in the Event table, specify the names of those columns. Separate column names with a comma (,). Specifying a value for this property overwrites the default value.

There is no default value for this property.

EventQueryType

The EventQueryType property is used to indicate whether the connector should dynamically generate a query to retrieve events from the event table or use its built in query. For the dynamically generated query, the connector maps its event structure to the columns in the event table. The order of the data in the table columns is very important. Please refer to the "Event and archive table schema" to view the correct order.

If the value in the EventQueryType is Fixed (as a string), the default query is executed. If set to Dynamic (as a string), a new query is built by getting the column names from the table that is specified in the EventTableName property.

The event table column names can change but the order and data type of the columns must remain the same as specified in the event table creation section. EventOrderBy will be appended to either the default or the dynamically generated query.

If the EventQueryType property is not added or it contains no value, it is defaulted to Fixed.

Default value is Fixed (as a string).

EventTableName

Name of event queue table, which is used by the connector's polling mechanism.

The default name is xworlds_events.

Set this to null (as a string) when polling is turned off for the connector. This prevents validation of the existence of the event and archive tables.

For a user defined event table, ensure that the event_id maps to one of the following JDBC types: INTEGER, BIGINT, NUMERIC, VARCHAR.

JDBCDriverClass

Specifies the class name of a driver. To use a particular JDBC driver, specify the driver's class name in this configuration property. For example, to specify the Oracle thin driver, set the value of this property to: oracle.jdbc.driver.OracleDriver.

For more information, see Enabling multi-driver support and UseDefaultsForCreatingChildBOs.

No default value is provided.

MaximumDatabaseConnections

Specifies the maximum number of simultaneous database connections allowed. At runtime, the number of open database connections is the sum of this value plus 1.

If the PreserveUIDSeq property is set to false, at runtime, the number of open database connections is the sum of this value plus 2.

The default value is 5.

PingQuery

Specifies the SQL statement or stored procedure that the connector executes to check database connectivity.

The following is an example of an SQL statement used as a ping query:

SELECT 1 FROM <tablename>
 

The following is an example of a stored procedure call (sampleSP) used as a ping query with an Oracle database:

call sampleSP( )
 

Note that stored procedure calls cannot have output parameters. If an input parameter is required by the database, the input value must be specified as part of the ping query. For example:

Call checkproc(2)
 

There is no default value. For more information, see Handling lost database connections and Loss of connection to the application.

PollQuantity

Number of rows in the database table that the connector retrieves per polling interval. Allowable values are 1 to 500.

The default value is 1.

PreserveUIDSeq

Specifies whether or not the incoming unique ID sequence will be preserved in the unique identifier table.

If set to true, the unique ID is not committed until the business object is successfully processed in the destination application. All other processes attempting to access the unique identifier table must wait until the transaction is committed.

If set to false, the unique ID is committed when the business object requests it. The business object processing and the unique ID processing each have their own transaction block (internal to the connector). This is only possible if the transaction relating to the unique identifier table has its own connection.

Note:
If this property is not added to the connector configuration, the default behavior is the same as if this property were added and set to true. Also, if AutoCommit is set to true, the connector executes the same behavior as if PreserveUIDSeq is set to false.

If the property PreserveUIDSeq is set to false, at runtime, the number of open database connections is the sum of this value plus 2.

The default value is true.

RDBMS.initsession

SQL statement that initializes every session with the database. The connector takes a query and executes it at startup. There should not be a return value for this query. The property name is required, but a value is not.

There is no default value.

RDBMSVendor

Specifies which RDBMS the connector uses for special processing. Set the value of this property to Oracle for the Oracle database.

If using a non-default database, ensure that the proper driver is loaded. If this property is set to Others, the connector determines which database to use by locating the driver.

A value is required for the connector to process successfully.

No default value is provided.

ReplaceAllStr

Specifies whether the connector replaces all instances of each character identified in the ReplaceStrList property with the substitution string specified in that property. The connector evaluates ReplaceAllStr only if the ESC=[true|false] parameter of each attribute's AppSpecificInfo property does not contain a value. In other words, if the ESC parameter has been specified, its value takes precedence over the value set for the ReplaceAllStr property. To cause the connector to use the value of ReplaceAllStr, verify that the ESC parameter has not been specified.

The default value of ReplaceAllStr is false.

Note:
The ESC parameter and the ReplaceAllStr and ReplaceStrList properties provide support for database escape character functionality (for example, escaping single quotes). Because the same functionality is also available from the Prepared Statements provided by the JDBC driver, these properties will be deprecated in future releases of the connector. The connector currently supports the use of the JDBC Prepared Statements.

ReplaceStrList

Specifies one or more substitution sets, each composed of an individual character to be replaced, a character delimiter, and a substitution string. The connector performs this substitution on an attribute's value only if a value has been specified for the ESC=[true|false] parameter of the attribute's AppSpecificInfo property or for the connector's ReplaceAllStr property.

Note:
The ESC parameter and the ReplaceAllStr and ReplaceStrList properties provide support for database escape character functionality (for example, escaping single quotes). Because the same functionality is also available from the Prepared Statements provided by the JDBC driver, these properties will be deprecated in future releases of the connector. The connector currently supports the use of the JDBC Prepared Statements.

The syntax for this attribute is:

single_char1,substitution_str1[:single_char2,substitution_str2[:...]]
 

where:

single_char
A character to be replaced.
single_char
The substitution string that the connector uses to replace the character.
single_char
The character delimiter, which separates the character to be replaced from the string that replaces it. By default, the character delimiter is a comma (,). You can configure this delimiter by setting the first delimiter in the StrDelimiter property.
single_char
The termination delimiter, which separates substitution sets (each of which is composed of the character to be replaced, a character delimiter, and the substitution string). By default, the termination delimiter is a colon (:). You can configure this delimiter by setting the second delimiter in the StrDelimiter property.

For example, assume you want to replace a single percent sign (%) with two percent signs (%%), and a caret (^) with a backslash and a caret (\^). By default, StrDelimiter specifies a comma (,) as the character delimiter, and a colon (:) as the termination delimiter. If you keep the default delimiters, use the following string as the value of ReplaceStrList:

%,%%:^,\^
 
Note:
A restriction of the connector configuration tool prevents entering single quotation marks. Therefore, you must represent a single quotation with the character Q, and two single quotations with the characters DSQ. In the above example, if you also want to substitute a single quotation mark (') with two single quotation marks (''), use the following notation: Q,DSQ:%,%%:^,\^

RetryCountAndInterval

Specifies the number of attempts and the interval in seconds that the connector should use when it is unable to lock data while performing an update operation.

Before it performs an update, the connector locks rows related to the update and attempts to retrieve current data. If the connector cannot lock the rows, it tries again to get the lock for the count and interval specified in this configuration property. The connector eventually times out if the lock is not obtainable within the values specified here.

Specify the value in the format: count, interval in seconds. For example, a value of 3,20 specifies three retries with an interval of 20 seconds in between.

The default is 3,20.

SchemaName

This property limits the search for the event and archive tables within that particular schema. If this property is not added or if it is left empty, the connector will search all of the schemas that the user has access to. This SchemaName is also used when building the queries to access the event and archive tables.

The Oracle Database provides support for schema names. Refer to the JDBC driver documentation for specific information.

No default value is provided.

SPBeforePollCall

This property names the stored procedure that is executed for every poll call. If the property SPBeforePollCall has a value (the name of a stored procedure), then at the start of each poll call, the connector calls the stored procedure, passing it the values of the connector properties ConnectorID and PollQuantity. The procedure will update PollQuantity number of rows, setting the connector-id column to ConnectorID where status=0 and connector-id is null. This enables load balancing in the connector.

Note:
In the case where a poll call fails prematurely (the database is down, or the connection is lost), the connector-id remains set. This may result in records being skipped during polling. It is therefore recommended that periodically, the connector-id is reset back to null for all records in the event table with a status of 0.

StrDelimiter

Specifies the character and termination delimiters for use in the ReplaceStrList property.

You can specify your own value for either or both of these delimiters. If you do so, do not specify a space or other character between them.

Default value is a comma followed immediately by a colon (,:)

TimingStats

Allows you to time each verb operation of the connector to look for problems. Available settings are:

0
(No timing statistics)
1
(Timing displayed at entry and exit of the verb operation for an entire hierarchical business object).
2
(Timing displayed at entry and exit of each verb operation for each individual business object in a hierarchical business object).

Timing messages are log messages rather than trace messages. They can be turned on and off, independent of trace levels.

The default value is 0.

UniqueIDTableName

Specifies the table that contains the latest value used for generation of a unique ID. By default, the table has one column (id). You can customize the table to add a column for each attribute that requires generation of a UID.

The default value is xworlds_uid.

UseDefaults

If UseDefaults is set to true or is not set, the connector checks whether a valid value or a default value is provided for each required business object attribute. If a value is provided, the Create succeeds; otherwise, it fails.

If UseDefaults is set to false, the connector checks only whether a valid value is provided for each required business object attribute; the Create operation fails if a valid value is not provided.

The default value is false.

UseDefaultsForCreatingChildBOs

If UseDefaultsForCreatingChildBOs is set to true or is not set, the connector checks whether a valid value or a default value is provided for each required business object attribute. If a value is provided, the Create succeeds; otherwise, it fails.

If UseDefaultsForCreatingChildBOs is set to false, the connector checks only whether a valid value is provided for each required business object attribute; the Create operation fails if a valid value is not provided.

UseDefaultsForRetrieve

For polling

If UseDefaultsForRetrieve is not defined and set to true, the default values will be set in the BO before it is retrieved from the database and dispatched to the server.

If UseDefaultsForRetrieve is defined and set to false, the default values will not be set in the BO before it is retrieved from the database and dispatched to the server.

For request processing

If UseDefaultsForRetrieve is not defined and set to false, the default values will not be set in the BO before it is retrieved from the database and dispatched to the server.

If UseDefaultsForRetrieve is defined and set to true, the default values will be set in the BO before it is retrieved from the database and dispatched to the server.

Copyright IBM Corp. 1997, 2004