JDBC adapter commands

-URL connection_string

Defines the JDBC driver-specific connection to a database. The corresponding adapter command is -URL. 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. The corresponding adapter command are -USR and -USER.

-PASSWORD password

The password for the connection. This command is optional for the reasons described in the -USER command. The corresponding adapter command are -PWD and -PASSWORD.

-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. The corresponding adapter command are -DRV and -DRIVER.

-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. The corresponding adapter command are -AC and -AUTOCOMMIT.

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. The corresponding adapter command are -CAT and -CATALOG.

-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. The corresponding adapter command are -SCH and -SCHEMA.

-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. The corresponding adapter command are -PROC and -PROCEDURE.

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

See also the -WRITEMODE command.

-COLUMNS column [,column [, …]]

Specifies the name of the columns to access. It is a case-sensitive and does not include quotation marks around the column name. The corresponding adapter command is -COLUMNS column [, column [, …]] where column is a comma separated list of columns. The corresponding adapter command are -COL and -COLUMNS.

-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. The corresponding adapter command are -WM and -WRITEMODE.

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. The corresponding adapter command are -QRY and -QUERY.

-QUERYFILE query_file

This property is used as an alternative to provide query statements through a file. The SELECT statement for fetching rows from the database is provided through file. The corresponding adapter command are -QRYF and -QUERYFILE.

-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. The corresponding adapter command is -DML.

-DMLFILE dml_file

This property is used as an alternative to provide DML statements through a file. The custom DML statement for writing rows to the database is provided through file. The corresponding adapter command are -DMLF and -DMLFILE.

-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. The corresponding adapter command is -KEY.

-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. The corresponding adapter command is -BIND.

-ROWLIMIT row_limit

Maximum number of records to return from the database. The value 0 means all available records. The corresponding adapter command are -RL and -ROWLIMIT.

-NAMEDPARAMS

If set parameters in the statement can be named instead of using ? placeholder. Named parameters should be specified as {name}. This is applicable with QUERY and DML. The corresponding adapter command are -NP and -NAMEDPARAMS.

-EXCLUDEREADONLY

Decides whether to exclude or include read-only columns, when to generate the schema or when to perform the table operations. It is enabled when source or target object is table. When specified, the read-only columns would be excluded while generating table queries or statements. The corresponding adapter command are -ERO and -EXCLUDEREADONLY.

-SETCOLUMNS column[,column[, …]]

Comma-separated list of column names to be included in SET clause. Whitespace characters should not be included unless they are part of the actual column names. The corresponding adapter command are -SETCOL and -SETCOLUMNS.

-USECOLUMNLABELS

Column labels are typically available when importing schemas for SELECT statements which include AS keyword with the columns in the list. This setting is applicable only when generating schemas. The corresponding adapter command are -UCL and -USECOLUMNLABELS.

-TRUNCATE

This property is applicable only on target action and can only be used with target as Table and Write Mode as Insert and Insert First. When the value is set to true, the adapter truncates the specified table prior to inserting rows to it. The corresponding adapter command are -TRC and -TRUNCATE.

-MISSINGVALUEMODE{null_value|empty_string}

Determines whether to set missing (empty) string values to Null Value or Empty String. The corresponding adapter command are -MVM and -MISSINGVALUEMODE.

-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. The corresponding adapter command is -PRESQL.

-PRESQLFILE pre_sql_file

This property is used as an alternative to provide Pre-SQL statements through a file. 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, through file. The corresponding adapter command are -PRESQLF and -PRESQLFILE.

-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. The corresponding adapter command is -POSTSQL.

-POSTSQLFILE post_sql_file

This property is used as an alternative to provide Post-SQL statements through a file. Specifies one or more SQL statement to execute after fetching or writing all rows and before disconnecting from the database, through file. The corresponding adapter command are -POSTSQLF and -POSTSQLFILE.

-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. The corresponding adapter command are -FS and -FETCHSIZE.

-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.
The corresponding adapter command are -BS and -BATCHSIZE.
-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:
-ERRORROWHANDLING {none | error_table | error_file}
Select the Error Row handling to set the mode on how the error data would be handled. Three modes supported are:
  • none - Error records will be ignored and will not be recorded.
  • error_table – Enables the parameters to record the errors in an error table.
  • error_file - Enables the parameters to record the errors in an error file.
The corresponding adapter command are -EROWH and -ERRORROWHANDLING.
-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.
The corresponding adapter command are -ECAT and -ERRORCATALOG.
-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.
The corresponding adapter command are -ESCH and -ERRORSCHEMA.
-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.
The corresponding adapter command are -ETBAL and -ERRORTABLE.
-ERRORFILENAME error_file_name

Name of the Error File in which error records will be captured. The corresponding adapter command are -EFILEN and -ERRORFILENAME.

-ERRORROWDATA

Include Error Row Data in the error file. The corresponding adapter command are -EROWD and -ERRORROWDATA.x

-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. The corresponding adapter command are -CS and -CHARSET.

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

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

-T

Log adapter informational messages. The corresponding adapter command is -T.

-TE

Log only adapter errors during map execution. The corresponding adapter command is -E.

-TV

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

+

Appends the trace information to the existing log file. Omit this keyword to create a new log file. The corresponding adapter command is +.

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

-Optional Field Mode

Specifies the cardinality of field components of the row group in the imported schema. The default value is Always, resulting in fields being always marked as optional (0:1). When set to Mandatory, the fields are always marked as mandatory (1:1). When set to Nullable Columns, the fields are marked optional only if they are found to be nullable. This property applies only to schema generation operation in the Design Server UI. It is not applicable to read and write operations performed during a map run.