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.
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.
|
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:
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:
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. |
Keyword | Description |
---|---|
Connection String: CMT ODBC.INI: CommitMode |
Specifies the default transaction isolation
level. Possible values:
Default: 2 |
Connection String: CONNTYPE ODBC.INI: ConnectionType |
Specifies the level of database access for the
connection. Possible values:
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 |
Specifies the IBM i libraries to add to
the server job's library list. The libraries are delimited by commas
or spaces, and *USRLIBL may be used as a place holder for the server
job's current library list. The library list is used for resolving
unqualified stored procedure calls and finding libraries in catalog
API calls. If *USRLIBL is not specified, the specified libraries will
replace the server job's current library list. The number of libraries supported is 75. Libraries that exceed the limit are ignored. Note: The
first library listed in this property will also be the default schema
(or library ), which is used to resolve unqualified names in SQL statements.
To specify no default schema, a comma should be entered before any
libraries.
Default: QGPL |
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:
Default: 0 |
Keyword | Description |
---|---|
Connection String: DFT ODBC.INI: DateFormat |
Specifies the date format used in date literals
within SQL statements. Possible values:
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:
Default: 1 |
Connection String: DEC ODBC.INI: Decimal |
Specifies the decimal separator used in numeric
literals within SQL statements. Possible values:
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:
Default: 0 |
Connection String: DECFLOATROUNDMODE ODBC.INI: DecFloatRoundMode |
Specifies the rounding mode, when rounding is
allowed for a result. Possible values:
Default: 0 |
Connection String: MAPDECIMALFLOATDESCRIBE ODBC.INI: MapDecimalFloatDescribe |
Specify the format for the results of a DECFLOAT
operation. Possible values:
Default: 1 |
Connection String: TFT ODBC.INI: TimeFormat |
Specifies the time format used in time literals
within SQL statements. Possible values:
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:
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:
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:
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:
Default: 7 |
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: Values
for x option:
Values for y option:
Values for z option:
Default: |
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:
Default: 1 |
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:
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:
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:
Possible values:
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:
|
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:
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:
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:
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:
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:
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:
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:
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:
Default: 1 |
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:
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:
Default: 0 |
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:
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:
Default: 0 |
Connection String: REMARKS ODBC.INI: ODBCRemarks |
Specifies the source of the text for REMARKS
columns in catalog API result sets. Possible values:
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:
Default: 1 |
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:
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:
Possible values:
Default: 0 |
Connection String: HEXPARSEROPT ODBC.INI: HexParserOpt |
Specifies how SQL hexadecimal constants will
be interpreted in SQL statements. Possible values:
Default: 0 |
Connection String: TRANSLATE ODBC.INI: ForceTranslation |
Specifies whether or not to convert binary data
(CCSID 65535) to text. Possible values:
Default: 0 |
Connection String: UNICODESQL ODBC.INI: UnicodeSQL |
Specifies whether or not to send Unicode SQL
statements to the server. Possible values:
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 |
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:
Default: 0 |
Keyword | Description |
---|---|
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:
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:
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:
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:
Default: 0 |
ODBC.INI: DBCSNoTruncError | Specifies whether or not to report a DBCS string
conversion overflow error as an ODBC truncation error. Possible
values:
Default: 0 |
Connection String: DEBUG ODBC.INI: Debug |
Specifies one or more debug options. This is a bit-flag value. Any of the posible values can be added together to calculate a combined value. Possible values:
Default: 0 |
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:
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:
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:
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:
Default: 0 |
IBM i Access ODBC Driver
driver
name. To migrate existing DSNs to the new driver name, the cwbodbcreg
tool
can be used.