Adapter for Microsoft SQL Server ODBC Settings

The technique used to load the data into the DBMS. When set to ON, enables Extended Bulk Load functionality. When set to OFF, enables Standard SQL Insert Statement. The default value is OFF. To improve the performance of large bulk load operations, use network packet sizes that are larger than the default size.

The row interval to commit or write transactions to the database. The default value is either 1000000 or DBMS specific.

The maximum number of load sessions when logged on to the database. The default value is 10.

The maximum number of load session restart attempts after a recoverable load session error. The default value is 10.

Delimits characters between fields in the intermediate flat file that is loaded into the database. The delimiter can consists of a maximum of four characters depending on the DBMS.

The options are:

• TAB. Indicates a tab character.
• a. Indicates a character string, for example, ~.
• 0xNN. Indicates a hex code, for example, a hex code for a comma is 0x44. The hex code uses ASCII for Windows or UNIX operating systems and EBCDIC for IBM Mainframes.

The default value is TAB.

Delimits characters between records in the intermediate flat file that is loaded into the database. The row delimiter can be specified in the same manner as the field delimiter except that the comma character (,) is not permitted.

The options are:

• TAB. Indicates a tab character.
• a. Indicates a character string, for example, ~.
• 0xNN. Indicates a hex code, for example, a hex code for a comma is 0x44. The hex code uses ASCII for Windows or UNIX operating systems and EBCDIC for IBM Mainframes.

There is no default value for this setting.

Controls the techniques used to load data into the DBMS. When set to ON, loads data directly through the DBMS Utility. When set to OFF, extracts source data into the intermediate file. The default value is adapter specific.

Allows you to save intermediate Bulk Load Data files until the process completes. For example, for the Adapter for Salesforce, it allows you to save REST API source data files to a folder in APPDIR/TargetMFD_JOBID_nnnnnnnnn, where nnnnnnnnn is the Job ID assigned to the request. When set to YES, implicitly sets BLK_SAVE_REPONSE_LOG to YES. The default value is NO.

Controls permission to use a Permanent Database Table as an Intermediate Table during the Bulk Load operation. The default value is ON.

Controls the Integer Precision columns. The default value is 11.

Controls mapping of the Float Precision columns. The default value is 20.

Controls mapping of the Float Scale columns. The default value is 2.

Controls mapping of the Decimal Precision columns. There is no default value for this data type.

Controls mapping of store Decimal Scale columns. There is no default value for this data type.

This setting supersedes deprecated SET CONVERSION PRECISION p s commands.

Controls mapping of the LONGCHAR columns. The Longchar mapping options are ALPHA, TEXT, BLOB, or STRING. The default value is ALPHA.

This setting is available only when the value of the CONV_LONGCHAR setting is set to ALPHA. The default value is 256.

Controls mapping of the DATETIME columns. This is a compatibility setting. When set to ON, the exact precision of date-time is preserved in the synonym field format. For example, TIMESTAMP(5) is mapped to HYYMD5 instead of HYYMDs, thus preserves all five fractional digits. The default value is OFF.

When set to ON, displays the message (FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND. The default value is ON. To stop displaying this message, set the value of the PASSRECS setting to OFF.

Controls the action on the log file switch. The options are STOP and PROCEED. The default value is STOP.

Controls MERGE statement statistics split based on the actions performed on the rows such as INSERT, UPDATE, and DELETE. The default value is OFF.

Allows you to set the case of the ALIASes for a new target.

The options are:

• Not enforced. Does not change the default case of ALIAS of a new target.
• Enforce lower case. Changes the case of the ALIAS of a new target to lowercase.
• Enforce upper case. Changes the case of the ALIAS of a new target to uppercase.

The default value is Not enforced.

Adds table options parameters to the CREATE TABLE statement generated by the CREATE FILE command. There is no default value for this setting.

Controls expansion of Master File Field formats for Unicode RDBMS columns in non-Unicode Server configurations by using byte semantics. This setting is not required in the Unicode Server configuration.

The options are:

• Single byte characters. One byte per character. For example, NCHAR(10) -> A10.
• Double byte characters. Two bytes per character. For example, NCHAR(10) -> A20.
• Triple byte characters. Three bytes per character. For example, NCHAR(10) -> A30.

The default value is installation specific.

Controls SQL Translator join strategy in FOCUS generation when automatic passthru is not possible or disabled (SQL SET APT=OFF).

The options are:

• SORTMERGE. Retrieves data from each table and then sorts, merges, and aggregates them at the end. When set to SORTMERGE, FOCUS generation always uses a sort/merge strategy to retrieve data.
• NESTEDLOOP. Selects all rows from the outer table that matches the screening conditions, then uses those rows to obtain qualified data from the inner tables. FOCUS generation uses this strategy only when there is an equijoin on a joinable column in the SQL statement. If no such equijoin is available, FOCUS generation reverts to a sort/merge strategy.

There is no default value for this setting.

Controls the number of rows to be retrieved from the DBMS in a single operation. The default value is 100.

Controls the number of rows to be passed to the DBMS in a single operation. The default value is 1.

Controls when a connection to the DBMS is disconnected.

The options are:

• ON-FIN. Causes disconnect from all connected DBMS servers after finishing the session.
• ON COMMAND. Causes disconnect from the connected DBMS server or servers at the end of the outermost command bracket.
• ON COMMIT (and ROLLBACK). Causes a disconnect in the Adapter at COMMIT or ROLLBACK time.

The default value is ON-FIN.

Specifies the amount of time that an adapter must allow to complete a login to a data source before returning an error. This time is specified in seconds. The default value is adapter specific.

Specifies command timeout before returning an error. The value 0 specifies an indefinite period of time. The value of this setting is blank by default which indicates the setting is not in effect.

Sets the transactions mode. When set to ON, the adapter implicitly starts a local transaction on each of the connections where any work is performed. At the time of COMMIT or ROLLBACK, or at the end of the server session, the adapter commits or aborts the work on each connection consecutively. When set to OFF, if successful, each individual operation with the DBMS is immediately committed or in case of errors, rolled back by the DBMS. This is recommended for read-only applications for performance considerations. It is not recommended for read-write applications because in this mode it is impossible to roll back a logical unit of work that consists of several operations. The default value is ON.

Allows specification of a transaction isolation level to improve data consistency.

The options are:

• Installation Default. Default transaction isolation level specific to DBMS installation.
• Read Committed. Database information that is either read or changed is locked. Modified objects are locked until the transaction is complete; whereas, locks obtained for reading database information are released immediately after reading.
• Read Uncommitted. Changes to the database are locked and held until the transaction is complete. No locking is done when simply reading from the database. When data is locked, other users are prevented from accessing this data.
• Repeatable Read. Database information that is either read or changed is locked. Modified objects and objects that are read are both locked until the transaction is complete. Locks on access structures, such as indexes that are not modified are released immediately after reading.
• Transactions Serializable. Information that is either read or changed is locked until the transaction is completed. All access structures that are modified or queried are also locked until the transaction is completed.
• Snapshot. Changes to the database made by other transactions are not reflected in the data that is read within a snapshot transaction. No locking is done when simply reading from the database. Therefore, other users can write data to the database during this transaction. It does not prevent other users from updating the database.

The default value is Installation Default.

For more information about transaction isolation levels, see the Microsoft documentation.

Controls optimization of a request.

The options are:

• Optimize (Unless multiplicative effect detected). JOINs and complex WHERE predicates are optimized and aggregation is passed to the DBMS unless some constructs cannot be converted to SQL or a multiplicative effect is detected.
• Optimize (Ignore multiplicative effect). JOINs and complex WHERE predicates are optimized and aggregation is passed to the DBMS unless some constructs cannot be converted to SQL. In this case, the multiplicative effect does not cause suppression of JOINs and/or aggregation.
• Suppress optimization of JOIN and complex predicates. Suppresses the optimization of JOINs and complex WHERE predicates.
• Suppress optimization of OUTER JOIN. Suppresses the optimization of OUTER JOINs only.
• Delegate calculation of PRINT objects to DBMS. Instructs the adapter to delegate calculation of defined fields used in PRINT to the DBMS.
• Suppress AGGREGATION. Suppresses aggregation but does not suppress optimization of JOINs and complex WHERE predicates.

The default value is Optimize (Unless multiplicative effect detected).