JDBC adapter commands

-URL connection_string

Defines the JDBC driver-specific connection to a database. See the JDBC driver documentation for details. Generally, the command format is:

-URL jdbc:driver://host:port/database[?custom_settings]
-USER user_name

The user name for the connection. This command is optional. For many drivers, you can specify the user name as part of the connection string. Some drivers might use authentication other than user name and password, and some drivers might not require authentication at all.

-PASSWORD password

The password for the connection. This command is optional for the reasons described in the -USER command.

-DRIVER driver_class

The fully-qualified Java™ class name for the java.sql.Driver interface implementation in the JDBC driver, typically provided in the driver documentation. This command is optional. If you omit the -DRIVER command, the adapter searches all available drivers to locate the driver specified in the URL connection string.

-AUTOCOMMIT {default | on | off}

Controls how SQL statements that the JDBC driver performs are committed. The adapter trace log includes the -AUTOCOMMIT setting when logging is set to verbose.

default
Use the default setting specific to the driver and database.
on
Automatically commit the SQL statements that the JDBC adapter performs.
off
Do not automatically commit the SQL statements that the JDBC adapter performs.
-CATALOG catalog_name
The first component of a fully qualified procedure name (catalog.schema.procedure) or table name (catalog.schema.table).
  • The catalog_name is case-sensitive.
  • Do not include quotation marks (" ") around the catalog name.

This command is optional. The adapter ignores it if the JDBC driver does not support it.

-SCHEMA schema_name
The second component of a fully qualified procedure name (catalog.schema.procedure) or table name (catalog.schema.table).
  • The schema_name is case-sensitive.
  • Do not include quotation marks (" ") around the schema name.

This command is optional. The adapter ignores it if the JDBC driver does not support it.

-PROCEDURE procedure_name
The third component of a fully qualified procedure name (catalog.schema.procedure). This command overrides the -TABLE command.
  • The procedure_name is case-sensitive.
  • Do not include quotation marks (" ") around the procedure name.

When used in an input card, do not specify any input arguments. The procedure can return a result set or a single set of values through output arguments and a return value.

When used in an output card and the procedure returns data, the card is automatically configured for a request and response exchange using schema types that represent input and output data.

When used in a PUT function, any specified output arguments or return values are ignored, because there is no mechanism to provide those values back to the map.

-TABLE table_name
The third component of a fully qualified table name (catalog.schema.table) .
  • The table_name is case-sensitive.
  • Do not include quotation marks (" ") around the table name.

See also the -WRITEMODE command.

-WRITEMODE {INSERT | UPDATE | INSERT_FIRST| UPDATE_FIRST}

The -WRITEMODE command specifies the mode of operation to use in a PUT function and output card when the -TABLE command is specified. The default operation is INSERT.

INSERT

Inserts each row into the target table.

UPDATE

Each input row updates the corresponding row in the target table.

INSERT_FIRST

Inserts each input row into the target table. If the operation fails due to an integrity constraint error (such as a primary key violation), use the input row data to update the matching row in the target table.

UPDATE_FIRST

Use each input row to update the matching row in the target table. If the table does not contain a matching row, insert the input row into the target table.

-QUERY statement_text

The SELECT statement for fetching rows from the database. When the SELECT statement is specified, the-CATALOG, -SCHEMA, -PROCEDURE, and -TABLE commands are ignored.

When used in an input card, do not specify any input arguments. There are no values available for the adapter to bind to the parameters.

When used in a GET function, provide the query parameter values as the second argument of the GET function call.

When used in output card and the query accepts query parameters (a lookup query), the card is automatically configured for a request and response exchange using schema types that represent input and output data.

-DML statement_text

The custom Data Manipulation Language (DML) statement for writing rows to the database. When the -DML command is specified, -CATALOG, -SCHEMA, -PROCEDURE, and -TABLE commands are ignored. The number of bind parameters in the DML statement must match the number of fields in the records that are pushed to the adapter.

-KEY name[,name[, …]]
The list of column names to use in the WHERE clause of SELECT and UPDATE statements that the JDBC adapter generates automatically in the following scenarios:
  • By a GET function when the -TABLE command is specified. The adapter assumes that the fields in the records match the number and order of columns in the generated WHERE clause.
  • By a PUT function or output card when the -TABLE command is specified and the -WRITEMODE command specifies INSERT_FIRST, UPDATE, or UPDATE_FIRST. The adapter assumes that the fields in the records match the number and order of columns in the target table.

The -KEY command is ignored in all other scenarios. If not specified, the JDBC adapter inspects the table in the database (specified by the combination of catalog, schema and table properties), determines the columns that comprise the primary key of the table, and lists those columns in the WHERE clause.

If a column name in the list contains the exclamation point (!) or comma (,), use the exclamation point (!) as the release character.

See the JDBC adapter -KEY command examples.

-BIND index[type][,index[ type][, …]]
A list of comma-separated index [type] entries that specify how to bind input-record field values to parameters in the user-defined statement that the adapter performs on the database. The index value specifies the position of the respective parameter in the statement, starting with position 1. The type value specifies the data type to use for binding input data values to the statement parameter indicated by the index position, and is one of the following: TEXT, BINARY, INT8, INT16, INT32, INT64, DECIMAL, FLOAT32, FLOAT64, DATE, TIME, TIMESTAMP. The default value is TEXT. The -BIND command applies only to the following scenarios:
  • A GET function or output card that specifies the -QUERY command.
  • A PUT function or output card that specifies the -DML command.

See the JDBC adapter -BIND command examples.

-LSN {seconds | 0 | S}
Specifies the amount of time, in seconds, that the adapter is to wait to retrieve a database row.
0
The adapter does not wait. If no rows are available to retrieve (for example, if the database table is empty), the adapter returns a warning message. This is currently the only supported value and should always be set when fetching data from the database.
S
The adapter waits for an infinite period.

See the -QTY command for considerations related to the Fetch As setting of an input card.

-PRESQL statement_text

Specifies one or more SQL statements to execute after connecting to the database and before fetching any rows from the database or writing any rows to the database.

Bind parameters are not supported. The statements must be static statements. Separate individual statements by a semicolon (;). Use a backslash character (\) to escape each semicolon and backslash character that is an integral part of a the statement.

The adapter commits the statements immediately when autocommit is specified or when the SQL statements are non-transactional (such as Data Definition Language (DDL) statements); otherwise, the adapter commits the statements after the last statement executes.

-POSTSQL statement_text

Specifies one or more SQL statement to execute after fetching or writing all rows and before disconnecting from the database.

Bind parameters are not supported. The statements must be static statements. Separate individual statements by a semicolon. Use a backslash character to escape each semicolon and backslash character that is an integral part of a the statement.

The adapter commits the statements immediately when autocommit is specified or when the SQL statements are non-transactional (such as Data Definition Language (DDL) statements); otherwise, the adapter commits the statements after the last statement executes.

-FETCHSIZE fetch_size

Specifies the number of rows per fetch that the JDBC driver retrieves from the database. The fetch size must be a non-negative number. This command is optional. If you omit it or specify a fetch size of 0, the JDBC driver determines the optimal fetch size.

-BATCHSIZE batch_size

Specifies the number of rows of data the adapter writes to the database in a single batch.

If the -WRITEMODE command specifies INSERT_FIRST or UPDATE_FIRST, the adapter ignores the specified batch size and uses a batch size of 1 row.

The batch size must be a non-negative integer. When the batch size is 0, the adapter collects all records from the transformation engine and sends them to the database in a single batch.

This command is optional.
  • When batch size is not specified on an output card, the adapter uses a default value of 1 row.
  • When batch size is not specified for a PUT function, the adapter uses a default value of 0 and sends all records from the transformation engine to the database.
-QTY {quantity | S}

Specifies the number of rows to retrieve from a database table. This command is optional. If you omit it, the default is one row. A value of S retrieves all rows.

When using an API to create or overwrite an input JDBC card:
  • When Fetch As is set to Integral mode, set the Fetch Unit to 1 and specify the following commands so that adapter reads and returns all available rows at one time:
  • When Fetch As is set to Burst mode, specify the following commands so that adapter reads and returns one row per burst:
-ERRORCATALOG catalog_name
The first component of a fully qualified error-table name (error_catalog.error_schema.error_table) where the JDBC adapter stores invalid input records.
  • The catalog_name is case-sensitive.
  • Do not include quotation marks (" ") around the catalog name.
-ERRORSCHEMA schema_name
The second component of a fully qualified error-table name (catalog.schema.table) where the JDBC adapter stores invalid input records.
  • The schema_name is case-sensitive.
  • Do not include quotation marks (" ") around the schema name.
-ERRORTABLE table_name

The name of the error table where the JDBC adapter stores invalid input records.

This command is optional.
  • When the error table is specified, the adapter stores the invalid input record as part of the current transaction and continues to process subsequent input records.
  • When the adapter cannot insert an invalid record into the error table, the adapter reports the error and the map stops executing.
  • When the error table is not specified, the adapter reports the first invalid record and the map stops executing.
Define the error table columns to accommodate the following data:
Error table column Size Content
1 Variable The raw bytes of the entire invalid input record, including delimiters.
2 16 characters Either:
  • The text error code reported by the driver
  • Null, if the driver did not report an error code, or if the adapter found the record to be in error
3 1024 characters The text of the error message reported by either the JDBC driver or the JDBC adapter.
-CHARSET charset_name

The character-set encoding to convert character data between native (core engine) and Java™ (adapter) components. By default, the adapter uses the default system locale encoding. In most cases, you do not need to change the character-set encoding. Custom encoding is applicable only in advanced scenarios. For example, when you exchange binary data with the adapter directly, the data is interpreted as text data and is encoded in a non-default encoding.

-T [E | V] [+] [file_path]

The adapter trace level and full path to the adapter trace log.

-T

Log adapter informational messages.

-TE

Log only adapter errors during map execution.

-TV

Use verbose (debug) logging. The log file records all activity that occurs while the adapter is producing or consuming messages.

+

Appends the trace information to the existing log file. Omit this keyword to create a new log file.

file_path

The full path to the adapter trace log. If you omit this keyword, the adapter creates the m4jdbc.mtr log file in the map directory.

-READMODE {SELECT_ALL | LOOKUP}

The -READMODE command specifies how rows should be fetched from the table when GET map function is used, -TABLE command is specified and -KEY command is not specified. In SELECT_ALL mode, all rows from the table are fetched. In LOOKUP mode, the adapter automatically determines the primary key columns in the table and uses them as the lookup key. The default value is SELECT_ALL, unless -TABLE and -KEY commands are both specified in which case the default value is LOOKUP.

-ARRAYSIZE array_size

The -ARRAYSIZE command specifies the number of table rows the adapter concatenates and returns as a single logical record. When concatenating the rows, the adapter uses linefeed character as the row terminator and the pipe character as the field delimiter. The special value 0 means that all available rows should be concatenated and returned as a single logical record