Connection string keywords

The IBM i Access support for the ODBC driver has many connection string keywords that are used to change the behavior of the ODBC connection.

These same keywords and their values are stored when an ODBC data source is setup. When an ODBC application makes a connection, any keywords specified in the connection string override the values specified in the ODBC data source.

Choose from the following tables for more information on the connection string keywords that are recognized by the IBM i Access support for the ODBC driver. The Connection String keyword in the Keyword column can be used on the connection strings passed to SQLBrowseConnect and SQLDriverConnect. The ODBC.INI keyword in the Keyword column can be set at the data source name (DSN) level in the ODBC.INI file. On Windows, the ODBC.INI information is stored in the registry. On Linux®, the ODBC.INI information is stored in the odbc.ini file under /etc for System DSNs and the $HOME/.odbc.ini file for User DSNs.

Table 1. IBM i Access ODBC connection string keywords for General properties
Keyword Description
Connection String: DSN Specifies the name of the ODBC data source that you want to use for the connection.
Connection String: DRIVER
Specifies the name of the ODBC driver that you want to use.
Note: This should not be used if the DSN property has been specified.
Possible values:
  • IBM® i Access ODBC Driver
  • iSeries Access ODBC DriverNote 1
  • Client Access ODBC Driver (32-bit)Note 1
Connection String: PWD

ODBC.INI: Password

 Specifies the password for the IBM i user ID for the connection.
Connection String: SIGNONNote 2

ODBC.INI: SignonNote 2

Specifies what default user ID to use if the connection cannot be completed with the current user ID and password information.

Possible values:
  • 0 = Use Windows user name
  • 1 = Use default user ID
  • 2 = None
  • 3 = Use IBM i Navigator default
  • 4 = Use Kerberos principal

Default: 3
Connection String: SSLNote 2

ODBC.INI: SSLNote 2

Specifies whether a Secure Sockets Layer (SSL) connection is used to communicate with the server.

Possible values:
  • 0 = Encrypt only the password
  • 1 = Encrypt all client/server communication

Default: 0
Connection String: SYSTEM

ODBC.INI: System

 Specifies the IBM i system name to connect. For more information, see IBM i name formats for ODBC Connection APIs.
Connection String: UID

ODBC.INI: UserID

 Specifies the user ID for the IBM i connection.
Table 2. IBM i Access ODBC connection string keywords for Server properties
Keyword Description
Connection String: CMT

ODBC.INI: CommitMode

Specifies the default transaction isolation level.

Possible values:
  • 0 = Commit immediate (*NONE)
  • 1 = Read committed (*CS)
  • 2 = Read uncommitted (*CHG)
  • 3 = Repeatable read (*ALL)
  • 4 = Serializable (*RR)

Default: 2
Connection String: CONNTYPE

ODBC.INI: ConnectionType

Specifies the level of database access for the connection.

Possible values:
  • 0 = Read/Write (all SQL statements allowed)
  • 1 = Read/Call (SELECT and CALL statements allowed)
  • 2 = Read-only (SELECT statements only)

Default: 0
Connection String: DATABASE

ODBC.INI: Database

Specifies the IBM i relational database (RDB) name to connect.

Special values for this option include specifying an empty-string or *SYSBAS. An empty-string indicates to use the user-profile's default setting for database. Specifying *SYSBAS will connect a user to the SYSBAS database (RDB name).

Default: empty-string
Connection String: DBQ

ODBC.INI: DefaultLibraries

 Start of change

Specifies the IBM i libraries to add to the server job's library list as well as the default library used to resolve unqualified names. The libraries can be delimited by commas or spaces and *USRLIBL may be used as a place holder for the server job's current library list. If *USRLIBL is not specified, the specified libraries will replace the server job's current library list. The number of libraries supported is 75; any libraries that exceed the limit are ignored.

The first library specified will be also be used to set the CURRENT SCHEMA special register. This value is used by SQL for resolving unqualified objects such as tables and views. When in System Naming mode, this will have the effect of disabling use of the library list for unqualified SQL names, although the library list may still be used for other purposes. To disable setting CURRENT SCHEMA, add a leading comma before any libraries.

Default: QGPL

Examples:

If UID=myuser;DBQ=mylib,mylib2,mylib3 is specified, then CURRENT SCHEMA special register is set to MYLIB and the library list would be MYLIB, MYLIB2, and MYLIB3.

If UID=myuser;DBQ=,mylib,mylib2,mylib3;NAM=1 is specified, then CURRENT SCHEMA special register is set to *LIBL and the library list would be MYLIB, MYLIB2, and MYLIB3.

If UID=myuser;DBQ=,mylib,mylib2,mylib3;NAM=0 is specified, then CURRENT SCHEMA special register is set to MYUSER and the library list would be MYLIB, MYLIB2, and MYLIB3.

If UID=myuser;DBQ=mylib,*USRLIBL,mylib2 is specified, then CURRENT SCHEMA special register is set to MYLIB and the library list would be MYLIB, <existing user portion of the library list>, and MYLIB2.

End of change
Connection String: MAXDECPREC

ODBC.INI: MaximumDecimalPrecision

Specifies the maximum precision of decimal data that will be returned.

Possible values: 31 or 63

Default: 31
Connection String: MAXDECSCALE

ODBC.INI: MaximumDecimalScale

Specifies the maximum scale used in arithmetic calculations involving decimal data. This value must be less than the value of MAXDECPREC.

Possible values: 0 - 63

Default: 31
Connection String: MINDIVSCALE

ODBC.INI: MinimumDivideScale

Specifies the minimum scale used in arithmetic calculations involving decimal data.

Possible values: 0 - 9

Default: 0
Connection String: NAM

ODBC.INI: Naming

Specifies the naming convention used when referring to tables. For more information, refer to Naming conventions in the DB2® for i SQL reference.

Possible values:
  • 0 = *SQL
  • 1 = *SYS

Default: 0
Table 3. IBM i Access ODBC connection string keywords for the Data types properties
Keyword Description
Connection String: DFT

ODBC.INI: DateFormat

Specifies the date format used in date literals within SQL statements.

Possible values:
  • 0 = yy/dd (*JUL)
  • 1 = mm/dd/yy (*MDY)
  • 2 = dd/mm/yy (*DMY)
  • 3 = yy/mm/dd (*YMD)
  • 4 = mm/dd/yyyy (*USA)
  • 5 = yyyy-mm-dd (*ISO)
  • 6 = dd.mm.yyyy (*EUR)
  • 7 = yyyy-mm-dd (*JIS)

Default: 5
Connection String: DSP

ODBC.INI: DateSeparator
Specifies the date separator used in date literals within SQL statements.
Note: This property has no effect unless the DateFormat property is set to 0 (*JUL), 1 (*MDY), 2 (*DMY), or 3 (*YMD).
Possible values:
  • 0 = "/" (forward slash)
  • 1 = "-" (dash)
  • 2 = "." (period)
  • 3 = "," (comma)
  • 4 = " " (blank)

Default: 1
Connection String: DEC

ODBC.INI: Decimal

Specifies the decimal separator used in numeric literals within SQL statements.

Possible values:
  • 0 = "." (period)
  • 1 = "," (comma)

Default: 0
Connection String: DECFLOATERROROPTION

ODBC.INI: DecfloatErrorOption

Specifies whether a warning or data mapping error is reported when encountering an error with the decimal floating point data type. If not provided, the server attribute value is unchanged.

Possible values:
  • 0 = Report decimal floating point error as a data mapping error
  • 1 = Report decimal floating point error as a warning

Default: 0
Connection String: DECFLOATROUNDMODE

ODBC.INI: DecFloatRoundMode

Specifies the rounding mode, when rounding is allowed for a result.

Possible values:
  • 0 = ROUND_HALF_EVEN - round to nearest digit. If equidistant, round to the nearest even digit.
  • 1 = ROUND_HALF_UP - round to nearest digit. If equidistant, round up.
  • 2 = ROUND_DOWN - round to nearest lower digit. This is the same as truncation.
  • 3 = ROUND_CEILING - round towards +infinity.
  • 4 = ROUND_FLOOR - round towards -infinity.
  • 5 = ROUND_HALF_DOWN - round to nearest digit. If equidistant, round down.
  • 6 = ROUND_UP - round to nearest higher digit.

Default: 0
Connection String: MAPDECIMALFLOATDESCRIBE

ODBC.INI: MapDecimalFloatDescribe

Specify the format for the results of a DECFLOAT operation.

Possible values:
  • 1 = SQL_ VARCHAR
  • 3 = SQL_ DOUBLE

Default: 1
Connection String: TFT

ODBC.INI: TimeFormat

Specifies the time format used in time literals within SQL statements.

Possible values:
  • 0 = hh:mm:ss (*HMS)
  • 1 = hh:mm AM/PM (*USA)
  • 2 = hh.mm.ss (*ISO)
  • 3 = hh.mm.ss (*EUR)
  • 4 = hh:mm:ss (*JIS)

Default: 0
Connection String: TSP

ODBC.INI: TimeSeparator
Specifies the time separator used in time literals within SQL statements.
Note: This property has no effect unless the TimeFormat property is set to 0 (*HMS).
Possible values:
  • 0 = ":" (colon)
  • 1 = "." (period)
  • 2 = "," (comma)
  • 3 = " " (blank)

Default: 0
Connection String: TSFT

ODBC.INI: TimestampFormat

Specifies the format of TIMESTAMP values when converted to SQL_C_CHAR by the driver. For more information, refer to String representations of datetime values in the DB2 for i SQL reference.

Possible values:
  • 0 = yyyy-mm-dd hh:mm:ss.[n...] (*ISO)
  • 1 = yyyy-mm-dd.hh.mm.ss.[n...](*IBM)

Default: 0
Connection String: XMLCURIMPPARSE

ODBC.INI: XMLCurrentImplicitParse

Specifies the XMLPARSE option to use for the connection. This attribute indicates how whitespace in serialized XML data should be handled by DB2 when the data is implicitly parsed without validation.

Possible values:
  • 0 = Strip whitespace
  • 1 = Preserve whitespace

Default: 0
Connection String: XMLDECLARATION

ODBC.INI: XMLDeclarationFormat

Specifies the XML Declaration to return with XML columns that are returned in result sets.

This is a bit-flag value. Any of the possible values can be added together to calculate a combined value.

Possible values:
  • 0 = No declarations or byte order marks (BOMs) are added to the output buffer.
  • 1 = A byte order mark (BOM) in the appropriate endianness is prepended to the output buffer if the target encoding is UTF-16.
  • 2 = A minimal XML declaration is generated, containing only the XML version.
  • 4 = An encoding attribute that identifies the target encoding is added to any generated XML declaration. Therefore, this setting only has effect when the setting of 2 is also included when computing the value of this attribute.

Default: 7
Table 4. IBM i Access ODBC connection string keywords for the Package properties
Keyword Description
Connection String: DFTPKGLIB

ODBC.INI: DefaultPkgLibrary
Specifies the library for the SQL package.
Note: This property has no effect unless the XDYNAMIC property is set to 1.

Default: QGPL
Connection String: PKG

ODBC.INI: DefaultPackage
Specifies how the extended dynamic (package) support will behave.
Note: This property has no effect unless the XDYNAMIC property is set to 1.

Possible values: A/DEFAULT(IBM),x,0,y,z,0

Values for x option:
  • 1 = Use (Use the package, but do not put any more SQL statements into the package)
  • 2 = Use/Add (Use the package and add new SQL statements into the package)
Values for y option:
  • 0 = Return an error (SQL_ERROR) to the application when an SQL package error occurs
  • 1 = Return a warning (SQL_SUCCESS_WITH_INFO) to the application when an SQL package error occurs
  • 2 = Return success (SQL_SUCCESS) to the application when an SQL package error occurs
Values for z option:
  • 0 = Do not cache SQL package in memory
  • 1 = Cache SQL package in memory, possibly reducing the amount of communication to the server

Default: A/DEFAULT(IBM),2,0,1,0,512
Connection String: XDYNAMIC

ODBC.INI: ExtendedDynamic

Specifies whether to use extended dynamic (package) support.

Extended dynamic support provides a mechanism for caching dynamic SQL statements on the server. The first time a particular SQL statement is run, it is stored in a SQL package on the server. On subsequent runs of the same SQL statement, the server can skip a significant part of the processing by using information stored in the SQL package. For more information, see Use Extended Dynamic SQL.

Possible values:
  • 0 = Disable extended dynamic support
  • 1 = Enable extended dynamic support

Default: 1
Table 5. IBM i Access ODBC connection string keywords for Performance properties
Keyword Description
Connection String: BLOCKFETCH

ODBC.INI: BlockFetch

Specifies whether or not internal blocking will be done on fetches of 1 row. When set, the driver will try to optimize the fetching of records when one record is requested by the application. Multiple records will be retrieved and stored by the driver for later retrieval by the application. When an application requests another row, the driver will not need to send another flow to the host database to get it. If not set, blocking will be used according to the application's ODBC settings for that particular statement. For more information on setting this option see the fine-tuning record blocking topic.

Possible values:
  • 0 = Use ODBC settings for blocking
  • 1 = Use blocking with a fetch of 1 row

Default: 1
Connection String: BLOCKSIZE

ODBC.INI: BlockSizeKB

Specifies the block size (in kilobytes) that is retrieved on FETCH requests and then cached on the client. This property has no effect unless the BLOCKFETCH property is 1. Larger block sizes reduce the frequency of communication to the server, and therefore may increase performance.

Possible values: 1 – 8192

Default: 256
Connection String: COMPRESSION

ODBC.INI: AllowDataCompression

Specifies whether to compress data sent to and from the server. In most cases, data compression improves performance due to less data being transmitted between the driver and the server.

Possible values:
  • 0 = Disable compression
  • 1 = Enable compression

Default: 1
Connection String: CONCURRENCY

ODBC.INI: Concurrency
Specifies whether to override the ODBC concurrency setting by opening all cursors as updateable.
Note: In the following two cases, setting this option has no effect:
  1. When building a SELECT SQL statement the FOR FETCH ONLY or FOR UPDATE clause can be added. If either of these clauses are present in a SQL statement the ODBC driver will honor the concurrency that is associated with the clause.
  2. Catalog result sets are always read-only.
Possible values:
  • 0 = Use ODBC concurrency settings
  • 1 = Open all cursors as updateable

Default: 0
Connection String: CURSORSENSITIVITY

ODBC.INI: CursorSensitivity

Specifies the cursor sensitivity to use when opening cursors. This option applies to all forward-only and dynamic cursors that are opened on the same connection. Static cursors are always insensitive.

Possible values:
  • 0 - Unspecified/Asensitive
  • 1 = Insensitive
  • 2 = Sensitive
Connection String: EXTCOLINFO

ODBC.INI: ExtendedColInfo
The extended column information affects what the SQLGetDescField and SQLColAttribute APIs return as Implementation Row Descriptor (IRD) information. The extended column information is available after the SQLPrepare API has been called. The information that is returned is:
  • SQL_DESC_AUTO_UNIQUE_VALUE
  • SQL_DESC_BASE_COLUMN_NAME
  • SQL_DESC_BASE_TABLE_NAME and SQL_DESC_TABLE_NAME
  • SQL_DESC_LABEL
  • SQL_DESC_SCHEMA_NAME
  • SQL_DESC_SEARCHABLE
  • SQL_DESC_UNNAMED
  • SQL_DESC_UPDATABLE
Note: the driver sets the SQL_DESC_AUTO_UNIQUE_VALUE flag only if a column is an identity column with the ALWAYS option over a numeric data type (such as integer). Refer to the Db2 for i SQL Reference for details on identity columns.
Possible values:
  • 0 = Do not retrieve extended column information
  • 1 = Retrieve extended column information

Default: 0
Connection String: LAZYCLOSE

ODBC.INI: LazyClose
Specifies whether to delay closing cursors until subsequent requests. This will increase overall performance by reducing the total number of requests.
Note: This option can cause problems due to the cursors still holding locks on the result set rows after the close request.
Possible values:
  • 0 = Close all cursors immediately
  • 1 = Delay closing of cursors until the next request

Default: 0
Connection String: MAXFIELDLEN

ODBC.INI: MaxFieldLength
Specifies the maximum LOB (large object) size (in kilobytes) that can be retrieved as part of a result set. LOBs that are larger than this threshold will be retrieved in pieces using extra communication to the server. Larger LOB thresholds will reduce the frequency of communication to the server, but will download more LOB data, even if it is not used. Smaller LOB thresholds may increase frequency of communication to the server, but they will only download LOB data as it is needed.
Notes:
  • Setting this property to 0 forces the driver to always retrieve the LOB values with additional communication flows.
  • Setting this property larger than 15360 KB has no effect. Anything larger than 15360 KB is retrieved in pieces from the server. Retrieving the data in pieces reduces the amount of memory needed, at any given time, on the client.
Possible values: 0 — 2097152

Default: 32
Connection String: PREFETCH

ODBC.INI: PreFetch

Specifies whether to prefetch data upon executing a SELECT statement. This increases performance when accessing the initial rows.

Possible values:
  • 0 = Do not prefetch data
  • 1 = Prefetch data

Default: 1
Connection String: QRYSTGLMT

ODBC.INI: QueryStorageLimit

Specifies storage limit for a query. If the estimated storage usage exceeds the specified storage limit in the parameter, the query is not executed.

Possible values:
  • *NOMAX = No Query Limit
  • 0 - 2147352578

Default: *NOMAX
Connection String: QUERYOPTIMIZEGOAL

ODBC.INI: QueryOptimizeGoal

Specifies the optimization goal for queries. This parameter corresponds to the QAQQINI option called OPTIMIZATION_GOAL. For more information, refer to the QAQQINI option in the Db2 for i SQL Reference.

Possible values:
  • 0 = Use the goal of *ALLIO if extended dynamic support is enabled, otherwise use the *FIRSTIO goal.
  • 1 = *FIRSTIO - Return the first block of data as fast as possible.
  • 2 = *ALLIO - Optimize as if the complete result set will be read by the application.

Default: 0
Connection String: QUERYTIMEOUT

ODBC.INI: QueryTimeout

Specifies whether the driver will disable support for the query timeout attribute, SQL_ATTR_QUERY_TIMEOUT. If disabled, SQL queries will run until they finish.

Possible values:
  • 0 = Disable support for the query timeout attribute
  • 1 = Allow the query timeout attribute to be set

Default: 1
Table 6. IBM i Access ODBC connection string keywords for the Language properties
Keyword Description
Connection String: LANGUAGEID

ODBC.INI: LanguageID

Specifies a 3-character language id to use for selection of a sort sequence. This property has no effect unless the SORTTYPE property is set to 2.

Possible values: AFR, ARA, BEL, BGR, CAT, CHS, CHT, CSY, DAN, DES, DEU, ELL, ENA, ENB, ENG, ENP, ENU, ESP, EST, FAR, FIN, FRA, FRB, FRC, FRS, GAE, HEB, HRV, HUN, ISL, ITA, ITS, JPN, KOR, LAO, LVA, LTU, MKD, NLB, NLD, NON, NOR, PLK, PTB, PTG, RMS, ROM, RUS, SKY, SLO, SQI, SRB, SRL, SVE, THA, TRK, UKR, URD, VIE

Default: ENU
Connection String: SORTTABLE

ODBC.INI: SortTable

 Specifies the library and file name of a sort sequence table stored on the system. This property has no effect unless the SORTTYPE property is set to 3.
Connection String: SORTTYPE

ODBC.INI: SortSequence

Specifies how the server sorts records before sending them to the client.

Possible values:
  • 0 or 1 = Sort based on hexadecimal values
  • 2 = Sort based on the language set in LANGUAGEID property
  • 3 = Sort based on the sort sequence table set in the SORTTABLE property

Default: 0
Connection String: SORTWEIGHT

ODBC.INI: SortWeight

Specifies how the server treats case while sorting records. This property has no effect unless the SORTTYPE property is set to 2.

Possible values:
  • 0 = Shared-Weight (uppercase and lowercase characters sort as the same character)
  • 1 = Unique-Weight (uppercase and lowercase characters sort as different characters)

Default: 0
Table 7. IBM i Access ODBC connection string keywords for the Catalog properties
Keyword Description
Connection String: CATALOGOPTIONS

ODBC.INI: CatalogOptions

Specifies one or more options to affect how catalog APIs return information. This is a bit-flag value. Any of the possible values can be added together to calculate a combined value.

Possible values:
  • 1 = Return information about aliases in the SQLColumns result set.
  • 2 = Return result set information for SQLTablePrivileges and SQLColumnPrivileges.

Default: 3
Connection String: LIBVIEW

ODBC.INI: LibraryView

Specifies the set of libraries to be searched when returning information when using wildcards with catalog APIs. In most cases, use the default library list or default library option as searching all the libraries on the server will take a long time.

Possible values:
  • 0 = Use default library list
  • 1 = All libraries on the server
  • 2 = Use default library only

Default: 0
Connection String: REMARKS

ODBC.INI: ODBCRemarks

Specifies the source of the text for REMARKS columns in catalog API result sets.

Possible values:
  • 0 = IBM i object description
  • 1 = SQL object comment

Default: 0
Connection String: SEARCHPATTERN

ODBC.INI: SearchPattern

Specifies whether the driver will interprets string search patterns and underscores in the library and table names as wildcards (search patterns). By default, % is treated as an 'any number of characters' wildcard, and _ is treated as a 'single character' wildcard.

Possible values:
  • 0 = Do not treat search patterns as wildcards
  • 1 = Treat search patterns as wildcards

Default: 1
Table 8. IBM i Access ODBC connection string keywords for Conversion properties
Keyword Description
Connection String: ALLOWUNSCHAR

ODBC.INI: AllowUnsupportedChar

Specifies whether or not to suppress error messages which occur when characters that can not be converted (because they are unsupported) are detected.

Possible values:
  • 0 = Report error messages when characters can not be converted
  • 1 = Suppress error messages when characters can not be converted

Default: 0
Connection String: CCSID

ODBC.INI: CCSID

Specifies a codepage to override the default client codepage setting with.

Possible values: Client codepage setting or 0 (use default client codepage setting)

Default: 0
Connection String: GRAPHIC

ODBC.INI: Graphic
This property affects the handling of the graphic (DBCS) data types of GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB that have a CCSID other than Unicode. This property affects two different behaviors:
  1. Whether the length of a graphic field is reported as a character count or as a byte count by the SQLDescribeCol API.
  2. Whether graphic fields are reported as a supported type in the SQLGetTypeInfo result set
Possible values:
  • 0 = Report character count, report as not supported
  • 1 = Report character count, report as supported
  • 2 = Report byte count, report as not supported
  • 3 = Report byte count, report as supported

Default: 0
Connection String: HEXPARSEROPT

ODBC.INI: HexParserOpt

Specifies how SQL hexadecimal constants will be interpreted in SQL statements.

Possible values:
  • 0 = Treat hexadecimal constants as character data
  • 1 = Treat hexadecimal constants as binary data

Default: 0
Connection String: TRANSLATE

ODBC.INI: ForceTranslation

Specifies whether or not to convert binary data (CCSID 65535) to text.

Possible values:
  • 0 = Do not convert binary data to text
  • 1 = Convert binary data to text

Default: 0
Start of changeConnection String: TRIMCHAR

ODBC.INI: TrimCharFields

End of change		 Start of change

Specifies whether or not to trim trailing spaces (padding) from fixed-length CHAR and GRAPHIC fields in EBCDIC, UTF-8, and UTF-16 columns. Variable-length fields such as VARCHAR and VARGRAPHIC are unaffected.

Possible values:
  • 0 = Do not trim trailing spaces
  • 1 = Trim trailing spaces

Default: 0

End of change
Connection String: UNICODESQL

ODBC.INI: UnicodeSQL

Specifies whether or not to send Unicode SQL statements to the server.

Possible values:
  • 0 = Send EBCDIC SQL statements to the server
  • 1 = Send UCS-2 Unicode SQL statements to the server in UCS-2

Default: 0
Connection String: XLATEDLLNote 2

ODBC.INI: TranslationDLLNote 2

Specifies the full path name of the DLL to be used by the ODBC driver to translate the data that is passed between the ODBC driver and the server. The DLL is loaded when a connection is established.

Connection String: XLATEOPTNote 2

ODBC.INI: TranslationOptionNote 2

Specifies a 32-bit integer translation option that is passed to the translation DLL. This parameter is optional. The meaning of this option depends on the translation DLL that is being used. Refer to the documentation provided with the translation DLL for more information. This option is not used unless the XLATEDLL property is set.

Default: 0
Table 9. IBM i Access ODBC connection string keywords for Diagnostic properties
Keyword Description
Connection String: QAQQINILIB

ODBC.INI: QAQQINILibrary

 Specifies a query options file library. When a query options file library is specified the driver will issue the command CHGQRYA passing the library name for the QRYOPTLIB parameter. The command is issued immediately after the connection is established. This option should only be used when debugging problems or when recommended by support as enabling it will adversely affect performance.
Connection String: SQDIAGCODE

ODBC.INI: SQDiagCode

 Specifies Db2 for i SQL diagnostic options to be set. Use only as directed by your technical support provider.
Connection String: TRACE

ODBC.INI: Trace

Specifies one or more trace options. These options should only be used when debugging problems or when recommended by support as they will adversely affect performance.

This is a bit-flag value. Any of the possible values can be added together to calculate a combined value.

Possible values:
  • 0 = No tracing
  • 2 = Enable Database Monitor
  • 4 = Enable the Start Debug (STRDBG) command
  • 8 = Print job log at disconnect
  • 16 = Enable job trace
  • 32 = Enable database host server trace

Default: 0
Table 10. IBM i Access ODBC connection string keywords for other properties
Keyword Description
Start of changeConnection String: ALWAYSCALCLEN

ODBC.INI: AlwaysCalculateResultLength

End of change		 Start of change

Specifies whether or not to allow the driver to return SQL_NO_TOTAL (-4) for result lengths from SQLGetData or whether to always calculate the actual result length. When doing non-trivial conversions from/to UTF-8, UTF-16, or mixed EBCDIC, the driver has no way to know how long the actual conversion will be without doing it. As an optimization, the ODBC specification allows the driver to return SQL_NO_TOTAL instead, but some applications and middleware do not handle this value correctly, leading to defects. Enabling this option prevents the driver from returning this value at the cost of having to do extra rounds of conversion.

Possible values:
  • 0 = Allow SQL_NO_TOTAL to be returned when the result length calculation is non-trivial
  • 1 = Require the result length to always be calculated at the cost of performance

Default: 0

End of change
Connection String: ALLOWPROCCALLS

ODBC.INI: AllowProcCalls

Specifies whether stored procedures can be called when the connection attribute, SQL_ATTR_ACCESS_MODE, is set to SQL_MODE_READ_ONLY.

Possible values:
  • 0 = Do not allow stored procedures to be called
  • 1 = Allow stored procedures to be called

Default: 0
Connection String: CONCURRENTACCESSRESOLUTION

ODBC.INI: ConcurrentAccessResolution

Contains the preference for concurrent access resolution. This property identifies how a row lock conflict should be handled when it is encountered. This property only applies to read-only queries with isolation level CS.

Possible values:
  • 0 = Use Server Setting
  • 1 = Use Currently Committed Rows
  • 2 = Wait for Outcome
  • 3 = Skip Locks

Default: 0
Connection String: DB2SQLSTATES

ODBC.INI: DB2SQLStates

Specifies whether or not to return ODBC-defined SQL States or DB2 SQL States. Refer to the Db2 for i SQL Reference for more details on the DB2 SQL States. This option should be used only if you have the ability to change the ODBC application's source code. If not, you should leave this option set to 0 as most applications are coded only to handle the ODBC-defined SQL States.

Possible values:
  • 0 = Return ODBC-defined SQLStates
  • 1 = Return DB2 SQL States

Default: 0
Connection String: DATETIMETOCHAR

ODBC.INI: ConvertDateTimeToChar

Specifies one or more options on how date, time, and timestamp data types are reported to an application. This option supports cases in which date values such as 24:00:00 are used.

This is a bit-flag value. Any of the possible values can be added together to calculate a combined value.

Possible values:
  • 0 = Map the DATE, TIME, and TIMESTAMP data types as SQL_TYPE_DATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP
  • 1 = Map DATE data type as SQL_CHAR
  • 2 = Map TIME data type as SQL_CHAR
  • 4 = Map TIMESTAMP data type as SQL_CHAR

Default: 0
ODBC.INI: DBCSNoTruncError

Specifies whether or not to report a DBCS string conversion overflow error as an ODBC truncation error.

Possible values:
  • 0 = Report DBCS string conversion overflow error as ODBC truncation error
  • 1 = Ignore truncation error

Default: 0
Connection String: DEBUG

ODBC.INI: Debug

Specifies one or more debug options. This is a bit-flag value. Any of the possible values can be added together to calculate a combined value.

Possible values:
  • 2 = Return SQL_IC_MIXED for the SQL_IDENTIFIER_CASE option of SQLGetInfo
  • 4 = Store all SELECT statements in the package
  • 8 = Return zero for the SQL_MAX_QUALIFIER_NAME_LEN option of SQLGetInfo
  • 16 = Add positioned UPDATEs / DELETEs into packages
  • 32 = Convert static cursors to dynamic cursors
  • 64 = Send the entire column size worth of data for variable length fields (VARCHAR, VARGRAPHIC, BLOB, etc.) Note, set this option with caution as this can have an adverse impact on performance.
  • 128 = Subtract one from the SQLBindParameter sourcelength if the last character in the buffer is a null-terminator character.
  • 256 = Ignore data decimal errors
  • 512 = Ignore cast warnings (SQL0402) for scrollable cursors
  • 1024 = Disable variable length compression
  • 2048 = Return no support for SQL_CVT_DATE when calling the SQLGetInfo's SQL_CONVERT_TIMESTAMP option.
  • 32768 = If the result of a query results in a column being divided by 0, return a NULL value instead of an error.

Default: 0
Start of changeConnection String: KEEPALIVE

ODBC.INI: EnableKeepAlive

End of change		 Start of change

Enables or disables TCP keep alive.

Possible values:
  • 0 = Explicitly disable
  • 1 = Explicitly enable
  • 2 = use default value by way of cwbcopwr

Default: 2

End of change
Start of changeConnection String: LOGINTIMEOUT

ODBC.INI: LoginTimeout

End of change		 Start of change
Sets the login timeout (in number of seconds). If a login takes longer than the value specified, the connection will be aborted. If not specified, the timeout value is set based on the following sources, in the order shown:
  1. SQL_ATTR_LOGIN_TIMEOUT ODBC connection attribute
  2. No timeouts are used (infinite timeout)

Possible values: 0 – 4294967295

End of change
Start of changeConnection String: TIMEOUT

ODBC.INI: Timeout

End of change		 Start of change
Sets the socket timeout (in number of seconds). If not specified, the timeout value is set based on the following sources, in the order shown:
  1. SQL_ATTR_CONNECTION_TIMEOUT ODBC connection attribute
  2. Value set by way of cwbcopwr
  3. No timeouts are used (infinite timeout)

Possible values: 0 – 4294967295

End of change
Connection String: TRUEAUTOCOMMIT

ODBC.INI: TrueAutoCommit

Specifies how to handle autocommit support. In past ODBC drivers, turning autocommit on resulted in the server running under the *NONE isolation level. Now autocommit can run under any isolation level. Applications that require strict conformance to the SQL specification should use setting 1. Note that this setting requires that all files be journaled. Setting 0 offers better performance for most applications. See the SQL reference for further information on Transaction Isolation levels.

Possible values:
  • 0 = run autocommit under the *NONE isolation level
  • 1 = run autocommit under the isolation level that is set for the connection. The connection's isolation level is set using the SQLSetConnectAttr API and the SQL_ATTR_TXN_ISOLATION option.

Default: 0
Connection String: NEWPWD

ODBC.INI: NewPassword

 Specifies a new password used to change the current user's IBM i password. This option is only honored if set by an application. When using this option, the UID and PWD keywords should also be specified.
Connection String: XALCS

ODBC.INI: XALooselyCoupledSupport

Specifies whether locks are shared between loosely coupled distributed transaction branches.

Possible values:
  • 0 = Locks are not shared
  • 1 = Locks are shared

Default: 1
Connection String: XALOCKTIMEOUT

ODBC.INI: XALockTimeout

Specifies the maximum amount of time (in seconds) that a distributed transaction waits on a lock request before timing out.

Possible values:
  • 0 = Use the default system setting
  • 0 – 999999999 = the number of seconds to wait

Default: 0
Connection String: XATXNTIMEOUT

ODBC.INI: XATransactionTimeout

Specifies the amount of time (in seconds) that a distributed transaction waits before timing out.

Possible values:
  • 0 = Wait indefinitely for the transaction to finish
  • 0 – 999999999 = the number of seconds to wait

Default: 0
Note 1: Driver name is only registered on Windows and is deprecated and will no longer be registered in a future release. Users are advised to switch to the IBM i Access ODBC Driver driver name. To migrate existing DSNs to the new driver name, the cwbodbcreg tool can be used.
Note 2: The keyword is only supported on Windows.