SQL Output Object

The Integrator SQL output object defines data to be written to an ODBC accessible database table. The table must already exist and not be locked by another process.

SQL Attributes

Attribute Type Description
output_type
(required)		 String Identifies the object as an SQL output object. The value of the string is "sql".
input
(required)		 String Defines the object from which the data flow is arriving.
connect_string String Specifies the ODBC connection string for connecting to an ODBC data source. To write to an ODBC source, either connect_string or sql_source must be specified. The appropriate ODBC driver must be installed when accessing a database using SQL objects in a Windows environment.
  • When using the 64-bit Integrator, make sure a 64-bit ODBC driver is installed.
  • When using the 32-bit Integrator, make sure a 32-bit ODBC driver is installed.
connect_file String

Defines an obfuscated file containing the connect string. The file can be created by using the '-save_connect_file' option to the Integrator command-line. This method of specifying the connect string is used to hide the SQL username and password from casual access. The connect_file attribute conflicts with the connect_string attribute.

See Options for ODBC Login Credentials.
sql_library String Defines the OS/400 library for the DB2 database. If this attribute exists, it overrides the sql_owner attribute. (OS/400 only)
sql_source String Specifies the name of the ODBC source or the remote database to use. To write to an ODBC source, either connect_string or sql_source must be specified.
Example (Windows):
sql_source = "MS Access 97 Database"
On the OS/400, sql_source is used to specify a remote DB2 database to use. The sql_source should refer to the database name as it appears in the WRKRDBDIRE command. If no data source is given, Integrator will connect to the local DB2 database Integrator makes a single DB2/400 connection for all output objects in a script.
sql_table
(required)		 String Specifies the name of the SQL table or view to use.
sql_owner String Defines the SQL owner for the sql_table.
sql_type String Selects the SQL type (system or SQL). If this attribute equals "SYSTEM" or "*SYS", then system naming is used. (OS/400 only)
sql_qualifier String Defines the SQL qualifier for connecting to the ODBC source. This attribute is optional. On Windows the format is "drive:\\directory\\file.mdb".
Example: sql_qualifier = "C:\\models\\sales\\sales.mdb"
sql_commit String Controls the commit behavior as rows are added to the output table. (OS/400 only)
The sql_commit attribute can have the following values:
  • none (default)
  • read_uncommitted
  • read_committed
  • repeatable_read
  • serializable

These correspond to the commit types that can be found in the IBM SQL Call-Level Interface Book. The following values are accepted by DB2 CLI, but each OS/400 server may only support some of these isolation levels:

  • SQL_TXN_NO_COMMIT—Commitment control is not used.

  • SQL_TXN_READ_UNCOMMITTED—Dirty reads, non-repeatable reads, and phantoms are possible.

  • SQL_TXN_READ_COMMITTED—Dirty reads are not possible. Non-repeatable reads and phantoms are possible.

  • SQL_TXN_REPEATABLE_READ—Dirty reads and non repeatable-reads are not possible. Phantoms are possible.

  • SQL_TXN_SERIALIZABLE—Transactions are serializable. Dirty reads, non-repeatable reads, and phantoms are not possible.

In IBM terminology:

  • SQL_TXN_READ_UNCOMMITTED is Uncommitted Read

  • SQL_TXN_READ_COMMITTED is Cursor Stability

  • SQL_TXN_REPEATABLE_READ is Read Stability

  • SQL_TXN_SERIALIZABLE is Repeatable Read

For a detailed explanation of Isolation Levels, refer to the IBM SQL Reference.
allow_missing_columns Boolean Controls whether or not all table columns must appear in the input flow. If "true", table columns that are missing from the input flow will be given a default value (as controlled by the default_value attribute). Otherwise, an error will occur.
clear_table Boolean Controls whether the table should be cleared before saving the data to it. If "true", all rows are deleted from the table before Integrator writes its data to the table. The default is "false".
bulk_insert Boolean Allows the end-user, when set to "false", to send data to an SQL database as multiple insert statements. The default is "true". Use of this attribute can be helpful if error messages such as "ODBC driver requesting duplicate data for column <n>" or "No data at execution values pending" are appearing. Results will be slower when bulk_insert is "false".
default_value String Controls what value should be given for columns missing from the input flow. Should either be "null" or "blank". Defaults to "null". The SQL column must allow null values in order for the "null" option to work.
null_value String Defines a string that indicates a null value for columns that allow nulls to be set. If a value matches this exact string, then a null will be stored instead of this string value. If this attribute is not set, blank strings will be used to indicate nulls.
encoding String Defines the encoding for data written to the SQL database. This attribute is optional. Values include:
  • auto—The SQL output object will set the encoding based on the Unicode state of other objects in the same task. (default)
  • ascii—The database will be updated using ANSI ODBC APIs.

  • latin1—The database will be updated using ANSI ODBC APIs.

  • utf-8—The database will be treated as storing Unicode characters.

  • unicode—The database will be treated as storing Unicode characters.

Keep the following points in mind:
  • If the encoding attribute is auto and no signature is found, the encoding is assumed to be latin1 if no other object in the task handles Unicode data and the Integrator file is not encoded as utf-8 (using the charset 1208 directive). Otherwise, the encoding is assumed to be utf-8. See Integrator Unicode Data Support.
  • On Windows platforms, databases are updated using the Wide character ODBC APIs.
  • On UNIX platforms, Integrator relies on the UTF-8 support available in UNIX databases; data is stored using the single character ODBC APIs, but interpreted as UTF-8.
  • The ODBC driver settings may need to be set to enable UTF-8 support.
trace_before Sub-object Traces data flows entering the specified object. This is equivalent to adding the Trace process object immediately before the current output object. See Embedded Trace Object.

Keep the following points in mind when working with SQL objects:

  • Integrator uses the DI date format of YYYY/MM/DD, but will convert these dates to ISO format (YYYY-MM-DD) when writing to an SQL table.
  • Integrator tries to use a multiple row INSERT statement to efficiently save data to the output table. On the OS/400, this statement is not compatible with VARCHAR data. If the output table contains VARCHAR data, Integrator will revert to the single row INSERT statement and insert one row at a time. This will result in slower processing. If you are designing tables to be written by Integrator, using CHAR columns will be more efficient.
  • MS Access tables can be read using Microsoft’s default ODBC drivers, but they cannot be updated.

  • Temporary tables are only available inside a single transaction. Each SQL Proc object is a single transaction, and the transaction ends before the next Integrator task.