Adapter properties and commands

This section lists the properties supported by the adapter.

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]

The corresponding adapter command is -URL connection_string.

Username

Specifies the username for the connection. This is optional property. For many drivers, you can specify the username as part of the connection string. Some drivers might use authentication other than username and password, and some drivers might not require authentication at all. The corresponding adapter command is -USER user_name.

Password

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

Driver

Specifies 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 is -DRIVER driver_class.

Autocommit

Instructs the adapter how to configure the driver in respect to automatic commit of SQL statements performed by the driver. Default is off. Use the default setting specific to the driver and database.

When set to on, the adapter explicitly requests the driver to enable and perform auto-commit.

When set to off the adapter explicitly requests the driver to disable and not perform auto-commit.

The corresponding adapter command is - AUTOCOMMIT.

Catalog

Specifies the name of the database catalog. It is a case-sensitive. The adapter does not include quotation marks around the catalog name. This is an optional property.

The corresponding adapter command is -CATALOG catalog_name.

Schema

Specifies the name of the database schema. It is a case-sensitive. The adapter does not include quotation marks around the schema name. This is an optional property.

The corresponding adapter command is -SCHEMA schema_name.

Procedure

Specifies the name of the stored procedure to execute. It is a case-sensitive and does 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 is -PROCEDURE procedure_name.

Table

Specifies the name of the table or view to access. It is a case-sensitive and does not include quotation marks around the table name. The corresponding adapter command is -TABLE table_name.

Write Mode

Specifies the mode of operation to use in PUT map function and output card when the table property is specified. The default value is Insert. The meaning of the values is as follows:

  • Insert: Each input row is inserted to the target table. The corresponding adapter command value is insert.
  • Update: Each input row is used to update the corresponding row in the target table. The corresponding adapter command value is update.
  • Insert First: Each input row is inserted to 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. The corresponding adapter command value is insert_first.
  • Update First: Each input row is used 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. The corresponding adapter command value is update_first.

The corresponding adapter command is -WRITEMODE.

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 is -TRC (or -TRUNCATE).

Here is an example of the adapter command line that includes the Truncate command:

-URL jdbc_connection_url -USR username -PWD password -WM insert -TRC -MVM null_value -CAT catalog_name -SCH schema_name -TBL table_name -BS 1
Delete

This property is applicable only on target action and can only be used with target as Table. Delete command is available under Write Mode property. When the user chooses Delete as a result after running the map, all the rows from the table for which the keys match will be deleted.

Each input row is used to delete the corresponding row in the target table. The corresponding adapter command value is delete.

Here is an example of the adapter command line that specifies the Delete write mode:

-URL jdbc_connection_url -USR username -PWD password -WM delete -MVM null_value -CAT catalog_name -SCH schema_name -TBL table_name
Query

Specifies the text of the query statement to execute when fetching the data. When GET map function is used with the adapter, the query statement text may contain query bind parameters (as '?' characters), and the values for the parameters are provided as the third argument of the GET function, with '|' character used as the value separator. The corresponding adapter command is -QUERY statement_text.

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. Generally, the command format is:

-URL jdbc_connection_url -USR username -PWD password -QUERYFILE file_path 

The corresponding adapter command is -QRYF (-QUERYFILE).

DML

Specifies the custom Data Manipulation Language (DML)statement to use for writing rows to the database. When specified, the schema, table and write mode properties are ignored. The number of bind parameters in the statement must match the number of fields in the records pushed to the adapter. The corresponding adapter command is -DML statement_text.

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. Generally, the command format is:

-URL jdbc_connection_url -USR username -PWD password -DMLFILE file_path 

The corresponding adapter command is -DMLF (-DMLFILE).

Key

Specifies 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.

The corresponding adapter command is -KEY name.

Bind

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.

The corresponding adapter command is -BIND index.

Missing Value Mode

The -QTY adapter command is used when fetching data from the database and it specifies the maximum number of rows to fetch from the database. The default value is 1, and the values S means all available rows. The -QTY command must always be accompanied by the -LSN 0 command. The -QTY and -LSN commands are managed automatically when the adapter is utilized in input card in the Design Server web UI. But when the adapter command line is specified directly, the -QTY and -LSN commands must be included in it manually.

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:
    • -QTY 1
    • -LSN 0
    • -ARRAYSIZE 0
  • When Fetch As is set to Burst mode, set the Fetch Unit to 1 and specify the following commands so that adapter reads and returns one row per burst:
    • -QTY S
    • -LSN 0
Exclude Read-Only Columns

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 is -ERO (or -EXCLUDEREADONLY).

Here is an example of the adapter command line that includes the Exclude Read-Only Columns command:

-URL jdbc_connection_url -USR username -PWD password -ERO -CAT catalog_name -SCH schema_name -TBL table_name
Pre SQL

Specifies one or more SQL statements to execute after connecting to the database and prior to fetching any rows from the database or writing any rows to the database. The statements are committed by the adapter after the last one is executed, or automatically if Autocommit is specified or the statements are non-transactional (e.g. DDL statements). Bind parameters are not supported – the statements must be static statements. The adapter assumes that individual statements are separated by a semicolon. Each semicolon and backslash character that is integral part of a statement needs to be escaped by a backslash character.

The corresponding adapter command is -PRESQL.

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 is -PRESQLF (-PRESQLFILE).

Here is an example of the adapter command line that includes the Pre SQL File command:

 -URL jdbc_connection_url -USR username -PWD password -WM insert -MVM null_value -CAT catalog_name -SCH schema_name -TBL table_name -BS 1 -PRESQLF file_path
Post SQL

Specifies one or more SQL statement to execute after fetching or writing all rows and prior to disconnecting from the database. The statements are committed by the adapter after the last one is executed, or automatically if Autocommit is specified or the statements are non-transactional (e.g. DDL statements). Bind parameters are not supported – the statements must be static statements. The adapter assumes that individual statements are separated by a semicolon. Each semicolon and backslash character that is integral part of a statement needs to be escaped by a backslash character. The corresponding adapter command is -POSTSQL.

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 is -POSTSQLF (-POSTSQLFILE).

The corresponding adapter command is -POSTSQLF (-POSTSQLFILE):

-URL jdbc_connection_url -USR username -PWD password -WM insert -MVM null_value -CAT catalog_name -SCH schema_name -TBL table_name -BS 1 -PRESQLF file_path
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 is an optional property. If you omit it or specify a fetch size of 0, the JDBC driver determines the optimal fetch size.

The corresponding adapter command is -FETCHSIZE fetch_size.

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 is -BATCHSIZE batch_size.

Error Catalog

Specifies the name of the error-table database catalog. It is case-sensitive and does not include quotation marks around the name. The corresponding adapter command is -ERRORCATALOG catalog_name.

Error Schema

Specifies the name of the error-table database schema. It is a case-sensitive. and does not include quotation marks around the name. The corresponding adapter command is -ERRORSCHEMA schema_name.

Error Table

Specifies the name of the error table where the JDBC adapter stores invalid input records. This is an optional property. 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. The corresponding adapter command is -ERRORTABLE table_name.

Optional Fields 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.

Charset

Specifies 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 is -CHARSET charset_name.

Logging

This property specifies the level of logging to use for the log (trace) file produced by the adapter. The default is Off. The value Information means log informational, the value Errors Only means log error messages only, and the value Verbose means log debug and trace level messages along with the informational and error messages.

The corresponding adapter command is:

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

-T -> Log adapter informational messages.

-TE -> Log only adapter errors.

-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 argument 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

Log file path

Specifies the location of the log file to which to write log messages. If not specified, the default log file name m4jdbc.mtr is used, and the file is stored to the directory in which the executed compiled map resides.

Failure Action

Specifies the action to perform on the transaction when the transaction fails. The default value is Rollback.

The following actions are supported:
  • Commit: This option will commit the transaction in case of transaction failure.
  • Rollback: This option will rollback the transaction in case of transaction failure.
-ARRAYSIZE array_size (or -AS 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. The -ARRAYSIZE adapter command is managed automatically when the adapter is used in input card in the Design Server web UI.