SQL Service (com.ibi.agents.XDSQLAgent)

SQL DML *

The SQL Data Manipulation Language (DML) statement, which can also evaluate an iFL expression to build an SQL statement dynamically. For example, inserting SREG(value) into the text of your query.

Evaluate SQL

If set to true, an attempt is made to treat the SQL as an expression. This may not be appropriate to all SQL. For example, quotes in evaluated strings must be escaped by doubling.

Output Format *

The format of the result set for a select operation:

• Field. A format designed for efficient XPath.
• Column. A format that is effective for transformations.
• Simple. A format that contains minimal metadata, and produces simple JSON documents when converted.
• Row. Deprecated. Returns the entire tuple as a single string. This format is not recommended for current JDBC operations. New configurations do not permit Row to be specified.

Examples are shown following this table.

Use JNDI *

If set to true, a connection from a data source accessed through JNDI is used. Otherwise, connect directly using JDBC driver manager. When set to true, the Pool Connections configuration parameter should not be used, and pooling is handled in the JNDI data source manager.

JNDI Name

JNDI name for the requested data source. To use an iWay JDBC provider, specify as jdbc/provider. This is required if you are using JNDI.

JNDI Factory

JNDI initial context factory class. Leave blank for default.

Transaction Isolation Level

Transaction isolation level to be set if possible.

These are the standard transaction Isolation levels defined in the ANSI/ISO SQL standard. For more information on these settings, see the DBMS, JDBC, or ODBC documentation. Based on standard terminology, the following transaction isolation levels can be selected:

• As Is. Does not change the transaction isolation level. The default is accepted, as it is found on the connection.
• Read Uncommitted. Allows reading of a record that may be rolled back later. Dirty reads, nonrepeatable reads, and phantom reads are possible.
• Read Committed. Dirty reads are not possible, but nonrepeatable reads and phantom reads are possible.
• Repeatable Read. Dirty reads and nonrepeatable reads are not possible, but phantom reads are possible.
• Serializable. This is the highest level of isolation. Phantom reads are not possible. Other users are prevented from updating or inserting rows into the data set until the transaction is completed.

For more information on the terminology related to transaction isolation, see Transaction Isolation Terminology.

Pool Connections

If set to true, connections are pooled. The default value is false.

Applies to non-local transactions only. This pools connections within the flow itself. Connections can be reused across messages, depending on the commit strategy and the specifics of the database being used. If used with JNDI data sources, this local service pooling may inhibit optimum use of any JNDI data source pooling and connection management. In most cases, the Pool Connections parameter for this service should not be set when using JNDI data sources.

Pooled connections are identified by connection URL and user ID. The pooled connection is shared by all instances of this service either within the channel as a whole or within a single execution (worker) thread. To pool at the worker level, set the Need Commit parameter to true.

In local transactions, connections are always shared within the transaction.

Attempt Read Only

For select set JDBC read only flag. Some drivers cannot handle this optimization and can give security failures.

Connection Properties

Properties to apply to a connection.

Need Commit

Determines how commits should be performed on completion. Select one of the following options from the drop-down list:

• Immediate. Issue a commit immediately upon completion of the Data Manipulation Language (DML) execution, regardless of whether or not the process is part of the local transaction. The connection is not pooled for commit purposes.
• Transactional. If the process is running as a local transaction, the service will join that transaction. In this case a commit or rollback will be performed on the connection depending on the completion status of the process flow. If the process is not running in a local transaction, this defaults to the Immediate option.
• Never. No commit is ever issued by the service for this statement. This is the default for a select statement. For a DML that requires a commit, the process flow is responsible for committing the connection by use of DML in another service. This gives the transaction complete control and responsibility for the commit or rollback. A commit or rollback is not executed by the use of the Commit service or by the transactional status of the process flow.
• Force. Always issue a commit before closing the connection. Use only in non-transactional mode. Setting the Force option is required only for certain JDBC drivers that require a commit in order to close all resources. For example, some DB2 drivers will generate an error if a connection is closed without a commit. For other drivers, issuing a commit with no outstanding work can generate an error. Users are cautioned to only use the Force option if it is required by your driver and application design.

Note: Prior to iSM version 6.1.4, the Boolean values true (Immediate) and false (Transactional) were provided as options for this parameter. These internal settings continue to perform as in previous releases.

Users are cautioned that commits are issued on connections, rather than statements. Selecting the Never option delegates application responsibility for the connection to the application rather than the transaction manager of the process flow. Failure to execute an SQL service with never having a COMMIT WORK or ROLLBACK WORK (as appropriate for the specific database), will leave the connection uncommitted.

For DML-based statements with the Never option selected, you will require pooling enabled to insure that the TCL (Transaction Control Language) affects the proper connection. Pooling is automatic when using JNDI-based connections.

Timeout

Number of seconds this service will wait for an operation to complete. A value of 0 indicates no timeout check.

Max Rows

Maximum rows if query DML (0 is all).

Make Siblings

If set to true, sibling documents are created for each Max Rows group.

Sibling documents are full documents that are chained to the emitted document from the service. They can be accessed by use of an iterator, and will be emitted automatically if they exist for the final document that is output from the process flow.

Max Siblings

Maximum siblings of maximum rows (if siblings selected).

Emit No Empty

If set to true, empty documents are not emitted.

Issuance Strategy

Issuance strategy: through SQL construction or through SQL bind.

Reply Node Name

If entered, response is put at this fully qualified named node.

Base 64 if Needed

If set to true, all fields are checked for base64.

xLOB Handling

Determines how blobs and clobs should be handled. Select one of the following options from the drop-down list:

• none (default)
• external
• inline

Want Generated Keys

If set to true, generated keys on a non-bound INSERT statement are returned if available from the database in the 'iway.getkey' register. The default value is false.

Call at EOS?

In a streaming environment, EOS (End of Stream) is the short message that is sent after the last document, which signifies the EOS. This parameter determines whether this service should be called for the EOS message. The default value is false.

JDBC Connection Properties

Data Source URL *

The URL to reach the data source.

JDBC Driver *

The JDBC driver to use.

User ID

Default user ID for the connection.

Password

Default password for the connection.

success

00000

The operation was successful. For SELECT operations, success can be considered as the join of found and notfound edges.

found

00000

Rows were returned. Always issued in conjunction with the success edge. You may wire found or success edges depending on your application requirements.

notfound

00000

No rows were returned. You may wire notfound or success edges depending on your application requirements.

Note: The notfound edge indicates a successful operation, as distinct from the fail_notfound edge, which indicates that some components, such as a table or column, is not found in the database.

duplicate

3C000, 42S01, 42S11, 42S21

Some item is duplicated, for example, insert to an existing unique key.

fail_notfound

42S02, 42S12, 42S22

A component required in the DML, such as a table or column, was not found.

fail_operation

The SQL operation was not successful.

cancelled

A cancellation through a flow timeout.

fail_timeout

HYT00, HYT01, 40001

The operation times out on the channel.

fail_parse

Parameters cannot be parsed in iFL.

fail_syntax

4200, 22007

The SQL had syntax errors as reported by the DBMS.

fail_other

The SQL operation has failed for a reason that is not included in any other edge (for example, fail_notfound). You can use the sqlcode special register for further analysis.

<xopen code>

all

Specific XOPEN code. To use this edge, you must define your own custom edge in iIT Designer.

sqlcount

Number of rows returned for a SELECT operation, or the number of rows affected by an UPDATE or DELETE operation.

iway.genkey

Generated key associated with an INSERT operation, if appropriate to the database.

sqlcode

The XOPEN code for any unsuccessful operation.