Access Client Solutions OLE DB Custom Connection Properties

The following list contains all of the custom properties that are available for the OLE DB providers shipped with IBM Access Client Solutions - Windows Application Package (IBMDA400, IBMDARLA, and IBMDASQL). The release at which each property was added is indicated. For more information about any of these properties, consult the IBM i Access for Windows OLE DB Technical Reference which can be found in the Programmer's Toolkit included with IBM Access Client Solutions - Windows Application Package.

Add Statements to SQL Package (V5R3)

The add statements property is a Boolean value used to indicate how SQL packages should be used when package support is enabled. If it is set to True, new statements will be added to the SQL package. This is the default value. If it is set to False, the provider will use the existing statements in the package, but will not add any new statements to the package.

Application Name (V6R1)

Specifies the application name for the connection.
Sets or returns a string value. Setting this value updates the DB2 for i5/OS SQL CLIENT_APPLNAME special register value. The default value is the name of the EXE running the current application. Note: This property only applies to database host server jobs (QZDASOINIT or QZDASSINIT).

Block Fetch (V5R3)

The block fetch property is a Boolean value that determines the provider fetch behavior when the ADO cache size is set to 1. If it is set to True, the provider will fetch a block of records from the database when an application is scrolling through a result set. If it is set to False, then only one row will be fetched at a time. Note that this property influences blocking only on V5R3 when the ADO cache size is 1. Previous versions always fetched the data in blocks.

Block Size (V5R4)

The block size property specifies the amount of data to fetch from the IBM i server in KB. It has a range from 1 to 8192 with a default value of 32. For example, if BlockSize is set to 100, the provider would fetch 100KB at a time from the host. If the application sets BlockSize, the value of the Cache Size property is ignored, and Block Size will be used. If the application does not set Block Size, the application reverts to existing behavior which is to use a combination of BlockFetch and Cache Size. For example, place the following value in the Provider String field when creating a new Linked Server: BlockSize=1000

Catalog Library List (V5R1)

The catalog library list property determines which libraries are searched during an unqualified catalog or schema request. This prevents the request from searching all libraries on the system which can cause long delays and performance problems with the PC application. This property should be a comma separated list of libraries, and may contain the following special values:

*USRLIBL - The library list of the user is added to the value of this property.
*ALL - All libraries on the system are searched. This is not a recommended setting.

Client Accounting (V6R1)

Specifies the client accounting for the connection.
Sets or returns a string value. Setting this value updates the DB2 for i5/OS SQL CLIENT_ACCTNG special register. The default value is an empty string.

Client Program ID (V6R1)

Specifies the client program ID for the connection.
Sets or returns a string value. Setting this value updates the DB2 for i5/OS SQL CLIENT_PROGRAMID special register value. The default value is an empty string.

Client User ID (V6R1)

Specifies the client user ID that is used for accounting purposes for the connection. This client user ID is not used for any authentication purposes.
Sets or returns a string value. Setting this value updates the DB2 for i5/OS SQL CLIENT_USERID special register value. The default value is an empty string.

Client Workstation Name (V6R1)

Specifies the name of the client workstation for the connection.
Sets or returns a string value. Setting this value updates the DB2 for i5/OS SQL CLIENT_WRKSTNNAME special register value. The default value is an empty string.

Convert Date Time to Char (V5R1)

The convert date time to char property allows the programmer to choose how date, time, and timestamp values are represented. It this property is set to TRUE (the default), these data types are converted to string representations. However, if set to FALSE, these values are converted to PC data types representing date, time, and timestamps.

Cursor Sensitivity (V5R3)

The cursor sensitivity property is used to influence how changes made to database tables are reflected in an open cursor in the application. It is used to indicate if underlying database changes should be seen or not seen in the current result set. The property might contain one of the following values:

0	Asensitive. This is the default behavior, and it indicates that changes might or might not be seen in the current result set. If a static cursor is used, this property is ignored because static cursors are always insensitive. Asensitive is the default value for this property.
1	Insensitive. This value indicates that the application does not want to see changes that are made to the underlying database. This value is ignored if a dynamic cursor is requested.
2	Sensitive. This value indicates that the application does want to see changes made to the underlying database. This value is ignored for forward-only and static cursors.

Data Compression (V5R3)

The data compression property is used to indicate if SQL data should be compressed in communication flows to and from an IBM i system. It is a Boolean value, and defaults to True.

Default Collection (V5R1)

The default collection property can be set prior to opening a connection to the server. This property is used to define what library should be used for unqualified SQL statements. Any SQL statement that does not qualify table names is run against the default collection, including INSERT, CREATE, UPDATE, and SELECT statements. This property applies only to SQL-type connections. Other functions of the IBMDA400 (Record Level Access, Data queues, Program, and Command calls) do not implement this property.

Prior to V5R1, the default collection was a read only property, and was set to match the user profile that opened the connection. For most applications, this forced the library name to be qualified in all SQL statements.

Starting with V5R4, the default collection may be set to an empty string. When used with system naming, this allows an unqualified SQL statement to search the job library list.

Decfloat Rounding Mode (V6R1)

Specifies the rounding that is used on the results from an arithmetic operation:

Value	Behavior	Description
0 (default)	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. This is the same as the rounding that is specified in Query for i5/OS when defining *QRYDFN objects.
2	ROUND_DOWN	Round to nearest lower digit. This is the same as truncation or rounding towards zero.
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 or round away from zero.

Note: DB2 for i5/OS supports DECFLOAT with two maximum precisions:
1. DECFLOAT (16) supports up to 16 decimal digits and an exponent range of -383 to 384.
2. DECFLOAT (34) supports up to 34 decimal digits and an exponent range of -6143 to 6144.

Force Translate (V5R1)

The force translate property can be used to force CHAR data stored in CCSID 65535 to be translated. This is a special CCSID that indicates to the provider that the data is to be considered as raw binary or hexadecimal data, and should not be translated. This property will override this default behavior and cause the IBMDA400 provider to perform character conversion. Prior to connecting to the server, this property is set to a numeric value between 0 and 65535. This value indicates which CCSID the data should be converted from when the translation is made. The default value is 65535 and prevents translation. A value of 0 will cause translation to occur using the retrieved job CCSID of the IBM i signon server job during connection authentication. The provider will assume that the data is encoded in the CCSID specified for this property.

Handle Numeric Data Errors (V6R1)

An integer value which specifies host behavior when handling errors that identify invalid numeric data.

Sets or returns one of the following integer values. The default is 1:

Value	Behavior	Description
0	Ignore errors identifying invalid decimal data.	Return invalid decimal data to the application without converting to zeros.
1 (default)	Do not ignore errors identifying invalid decimal data.	Return invalid decimal data to the application as zeros.
2	Ignore errors identifying invalid decimal data and set divide by zero results.	Return invalid decimal data to the application without converting to zeros, and return NULL when dividing by zero. Note: This value is valid only for V6R1M0 and later hosts.
3	Do not ignore errors identifying invalid decimal data and set divide by zero results.	Return invalid decimal data to the application as zeros and return NULL when dividing by zero.

Note: The Handle Numeric Data Errors property only applies to applications using SQL statements.

Hex Parser Option (V5R3)

The hex parser option determines how hexadecimal literals (for example, x'F0') in SQL statements are interpreted. Hexadecimal literals can be used for insertion into either CHAR FOR BIT DATA fields, or BINARY fields. They cannot be used for both in the same connection, so this integer property determines how the database should use hexadecimal literals. The following values are allowed:

0	This is the default value, and it indicates that hexadecimal literals are used for insertion into CHAR FOR BIT DATA fields.
1	This value indicates that hexadecimal literals are used for insertion into BINARY fields.

Initial Catalog (V5R2)

The initial catalog option is used to indicate which relational database should be used on the target server. Because multiple databases can exist using independent auxiliary storage pools (IASP), this string property determines which will be used. The special value of *SYSBAS can be used to connect to the *LOCAL database.

Job Name (V5R3)

Job name is a read only property that is set by the provider after a connection to the database is established. It can be used in problem diagnosis to determine which job the provider is using.

Keep Trailing Blanks (V6R1)

A Boolean value that specifies whether or not trailing blanks are removed from character data before returning the data to the application.

Sets or returns one of the following values. The default is FALSE.

Value	Behavior
FALSE (default)	Truncate trailing blanks from data read from the host.
TRUE	Do not truncate trailing blanks from data read from the host.

Notes:
1. This property does not apply to LOB data since the System i OLE DB provider never truncates blanks from LOB data.
2. Also see Reading and Writing values for related information.

Library List (V5R4)

Library list is a comma delimited list of libraries that should make up the user portion of the host server job library list. This value should be specified before making the connection, as it is read only after the connection has been established. The special value of *USRLIBL may be inserted into the list as a placeholder for the current library list of the job. Note that this property only affects the database host server connections (QZDASOINIT).

Maximum Decimal Precision (V5R3)

The maximum decimal precision property is used to indicate the maximum precision that can be used by the host server when performing arithmetic operations on numeric data. Valid values for this integer property are 31 (default) and 63.

Maximum Decimal Scale (V5R3)

The maximum decimal scale property is used to specify the maximum scale that can be used in arithmetic operations by the host server using numeric data. Valid values range from 0 to the value of Maximum Decimal Precision. The default value is 31.

Minimum Divide Scale (V5R3)

The minimum divide scale property determines the minimum scale that will be used by the host server when performing numeric division operations. Valid values are 0 (default) to 9.

Naming Convention (V5R4)

The naming convention property is used to specify what syntax should be used for SQL statements sent to the database host server. This will also affect search order for unqualified SQL references. The following values are allowed:
 

0	This is the default value, and specifies that SQL naming should be used. The schema qualifier is a period (LIBRARY.TABLE).
1	This value indicates that system naming should be used. The schema qualifier is a forward slash (LIBRARY/TABLE).

Query Optimize Goal (V5R4)

Query optimize goal is used to determine how the database host server will optimize SQL queries. Note that this option requires that the target iSeries system also be at V5R4. The following values are allowed:
 

0	This is the default value, and allows the host server to determine optimization. If SQL packages are used, queries are optimized for returning all rows. When packages are not used, queries are optimized for the first block of data.
1	This value indicates that the host server should optimize for the first block of data.
2	This value indicates that the host server should optimize for all records.

Query Options File Library (V5R3)

The query options file library is a string property that tells the provider which database library should be used for the QAQQINI database options file. If unspecified, the default library of QUSRSYS is searched.

QUERY Storage Limit (V6R1)

Specifies the maximum storage that is used by the host system when performing SQL queries.

Notes:
1. The Query Storage Limit property only applies to applications using SQL statements.
2. The Query Storage Limit property is valid only for V6R1M0 and later hosts.

Read Only Connection (V7R1 - SE55429)

A Boolean value that specifies whether or not the connection will generate updateable cursors by default, or restrict the connection to read-only cursors. The flexible nature of OLE DB programming, combined with implementation of the IBM i cursor support, means that a SELECT statement may be initially prepared
or described using an updateable cursor type; but later when the cursor is actually opened, a read-only cursor is requested. In this situation, fetching rows from the cursor may result in a sub-optimal number of rows returned.

When the application knows in advance that it will not need updateable cursors (for example, when using OLE DB through the Microsoft ADO.NET interfaces, cursors are always read-only), setting this property to TRUE may generate increased performance when fetching data from the server.

Settings and Return Values
--------------------------
Sets or Returns one of the following values. The default is FALSE.

Value=TRUE
----------
Force all cursors opened on this connection to be read-only, overriding the cursor settings requested by the OLE DB application.

Value=FALSE (default)
---------------------
Use the cursor type requested by the OLE DB application for all cursors opened on this connection.

Secure Sockets Layer (SSL) (V5R1)

The SSL property determines if Secure Sockets Layer is used when establishing a connection to the database. It is a string (rather than Boolean) value that can contain the following values:

"TRUE" - Secure sockets will be used.
"FALSE" - Secure sockets will not be used.
"DEFAULT" - The default connection value for SSL will be used.

Sort Language ID (V5R3)

The sort language ID property is a string value that indicates which language ID is used when sorting records. It should be set to a three character language identifier. This is only applicable if the Sort Sequence property has been set to a shared or unique weight table (1 or 2).

Sort Sequence (V5R3)

Sort sequence determines how result data will be sorted. This is an integer property with the following possible values:
 

0	*HEX. Data is sorted based on hexadecimal values.
1	*LANGIDSHR. Data is sorted based on a language table, with uppercase and lowercase characters sorted as same character.
2	*LANDIDUNQ. Data is sorted based on a language table, but uppercase and lowercase characters are unique.
3	User specified table. Data is sorted using a table specified by user in the "Sort Table" property.

Sort Table (V5R3)

The sort table property is used to point to a user-defined sort table for ordering result set records. This property is applicable only if the "Sort Sequence" property has been set to the value of 3.

SQL Package Library Name (V5R3)

The package library name property is used to indicate which library should be used when creating SQL packages for extended dynamic support. This property is used only if the Use SQL Packages property has been set to True. The default value of this property is QGPL.

SQL Package Name (V5R3)

The package name property is used to determine the name of the SQL package created when the Use SQL Packages property is set to True. Only the first 7 characters of this name are used, with three additional characters generated by the provider. If this property is not specified, a name is generated by the provider.

Trace (V5R3)

The trace property is used to enable diagnostic traces when troubleshooting errors. It is an integer property, and several numeric constants are defined for various trace options. To determine the value this property should contain, select the desired trace options and add the constant values. The resulting number is the value that should be specified. The constants are defined as:
 

0	No trace options. This is the default.
1	Database monitor. The provider runs the STRDBMON command at the beginning of the connection.
2	Start debug. The provider runs the STRDGB command at the beginning of the connection.
4	Print joblog. The provider runs the DSPJOBLOG OUTPUT(*PRINT) command before disconnecting.
8	Job trace. The provider uses the STRTRC command to enable job tracing at the beginning of the connection.

Unusable SQL Package Action (V5R3)

The unusable package action property is used to specify what behavior should be used when an error occurs with an SQL package. The integer options for this property are defined as:
 

0	Return an error when a package error occurs. If a package is unusable, an error is returned to the application.
1	Ignore package errors. If a unusable package error occurs, it is ignored by the provider. This is the default value.

Use SQL Packages (V5R3)

The use SQL packages option is used to specify if extended dynamic SQL support should be enabled. This is a Boolean property and, when enabled, specifies that SQL packages will be used for SQL processing.



Example usages:

A common place of usage is when configuring a Micosoft SQL Server - Linked Server. You can refer to

Configuring an OLEDB provider for SQL Server Linked Server

Specifically you would specify one or more of the above custom connection properties in the 'Provider
string' of the Linked Server, Step 7 of the above technote.

Note: You should not put extra spaces in the Provider string between a property, the equal sign and the equal sign and the value. That can cause the property to be parsed improperly and that can cause the linked server to fail to initialize or the property to be ignored. For example you want to have a Property
string like,

Force Translate=0;Catalog Library List=AMFLIB,QGPL,PRODLIB;Block Fetch=true;

Default Collection=QIWS;Library List=MYLIB,YOURLIB;

Read Only Connection=true; Force Translate=0;Default Collection=QIWS;

if you put extra spaces in the Provider string like,

Force Translate = 0;

It will not work and will liklely cause a msg 7303 in SQL Server.


A second common place would be in an actual program coded directly to the provider.

Sample Visual Basic code:

Dim cnAS400 as ADODB.Connection
Dim strJobName as String

Set cnAS400 = New ADODB.Connection

'Set the provider to Client Access
cnAS400.Provider = "IBMDA400"

'Set custom properties.
cnAS400.Properties("Block Fetch") = True
cnAS400.Properties("Catalog Library List") = "LIBRARY1, LIBRARY2"
cnAS400.Properties("Convert Date Time To Char") = "FALSE"
cnAS400.Properties("Default Collection") = "MYLIB"
cnAS400.Properties("Force Translate") = 0
cnAS400.Properties("Cursor Sensitivity") = 0
cnAS400.Properties("Data Compression") = True
cnAS400.Properties("Hex Parser Option") = 0
cnAS400.Properties("Initial Catalog") = "*SYSBAS"
cnAS400.Properties("Maximum Decimal Precision") = 31
cnAS400.Properties("Maximum Decimal Scale") = 31
cnAS400.Properties("Minimum Divide Scale") = 0
cnAS400.Properties("Naming Convention") = 1
cnAS400.Properties("Query Options File Library") = "QUSRSYS"
cnAS400.Properties("Query Storage Limit") = 1024
cnAS400.Properties("SSL") = "FALSE"
cnAS400.Properties("Trace") = 0

'Related properties grouped together (sort sequence)
cnAS400.Properties("Sort Sequence") = 1
cnAS400.Properties("Sort Language ID") = "ENU"
cnAS400.Properties("Sort Table") = "MYLIB/MYSORTFILE"

'Related properties grouped together (SQL packages)
cnAS400.Properties("Use SQL Packages") = True
cnAS400.Properties("SQL Package Name") = "ANYNAME"
cnAS400.Properties("SQL Package Library Name") = "PACKAGELIB"
cnAS400.Properties("Add statements to SQL Package") = True
cnAS400.Properties("Unusable SQL Package Action") = 0


'Open the connection
cnAS400.Open "Data Source=MySystem;", "USERID", "PWD"
strJobName = cnAS400.Properties("Job Name")