DB2 Data Provider Connection String Options

The following connection option descriptions are listed alphabetically by the connection string option name. The ConnectionStringBuilder property name is listed immediately underneath the GUI name.

The connection string option name and the property name are the same; however, the spaces in the connection string option name are required.

Accounting Info

Property

AccountingInfo

Description

Specifies the CLIENT ACCTNG register on the server.

Valid Values

string

where string is the accounting information.

Default

An empty string

Category

Client Monitoring

Alternate Servers

Property

AlternateServers

Description

Specifies a list of alternate database servers to which the data provider tries to connect if the primary database server is unavailable. Specifying a value for this connection string option enables connection failover for the data provider.

The value you specify must be in the form of a string that defines connection information for each alternate server. You must specify the Host name or the IP address of each alternate server and the port number of each alternate server.

See Configuring Connection Failover for a discussion of connection failover and information about other connection string options that you can set for this feature.

Valid Values

"Host=hostvalue;Port=portvalue[, ...]"

Example

The following Alternate Servers value defines two alternate servers for connection failover:

Alternate Servers="Host=Accounting;Port=446; Host=123.456.78.90;Port=447"

Default

An empty string

Category

Failover

Application Name

Property

ApplicationName

Description

Specifies the name that the DB2 server uses to identify the client application. This option sets the CLIENT APPLNAME register on the server. For DB2 LUW V9 and higher, this option also sets the APPL_NAME column of the SYSIBMADM.APPLICATIONS table.

Valid Values

string

where string is the name of the application.

Default

An empty string

Category

Client Monitoring

Array Send Size

Property

ArraySendSize

Description

Specifies the size of arrays used when performing array binding, in particular during bulk load operations. The value is specified in kilobytes.

See Performance Considerations for information about configuring this option for optimal performance.

Valid Values

The value for this option can be an integer from 1 to 4,294,967,296 (4 GB). The value 1 is a special value that does not define the number of bytes but, instead, causes the data provider to allocate space for exactly one row of data.

Default

5120

Category

Performance

Authentication Method

Property

AuthenticationMethod

Description

Determines which authentication method the data provider uses when establishing a connection.

Valid Values

ClearText | Client | EncryptedPassword | EncryptedUIDPassword | Kerberos

If set to ClearText, the data provider sends the user ID and password in clear text to the server for authentication. If a user ID and password are not specified, the data provider throws an exception.

If set to Client, the data provider uses client authentication. Client authentication uses the user ID of the user logged onto the system on which the data provider is running to authenticate the user to the database. Because the database server does not authenticate the user, use client authentication only when you can guarantee that only trusted clients can access the database server.

If set to EncryptedPassword, the data provider sends a user ID in clear text and an encrypted password to the DB2 server for authentication. If a user ID and password are not specified, the data provider throws an exception.

If set to EncryptedUIDPassword, the data provider sends the encrypted user ID and password to the DB2 server for authentication. If either the user ID or password is not specified, the data provider throws an exception.

If set to Kerberos, the data provider uses Kerberos authentication. The data provider ignores any user ID or password specified.

Default

ClearText

Category

Security

Batch Update Behavior

Property

BatchUpdateBehavior

Description

Determines how the data provider optimizes batch processing during update operations. The BatchUpdateSize property must be set to 0 or a integer greater than 1 (refer to DB2DataAdapter in the online help for more information).

Valid Values

ArrayBindWhenPossible | ArrayBindOnlyInserts | NoOptimizations

If set to ArrayBindWhenPossible, the data provider attempts to use array binding to optimize batch updates. Use this value if your application 1) uses batch operations for inserts, updates, or deletes and 2) performs these operations on many values in the same columns. In this case, set the Batch Update Behavior connection string option.

If set to ArrayBindOnlyInserts, the data provider can use array binding to optimize batch inserts. Use this value if your application 1) uses batch operations for inserts only and 2) inserts many values in the same columns.

If set to NoOptimizations, no optimizations are used during DataAdapter update operations.

Default

ArrayBindWhenPossible

Category

Performance

Catalog Schema

Property

CatalogSchema

Description

Specifies an alternative schema to be used when the data provider needs to query the database catalog.

Valid Values

string

where string is the name of a valid DB2 schema.

Default

An empty string. The data provider uses the server’s default system catalog schema.

Category

Advanced

Certificate Store Location

Property

CertificateStoreLocation

Description

Specifies the location of the X.509 certificate store used for client authentication.

Valid Values

CurrentUser | LocalMachine

If set to CurrentUser, the application uses the X.509 certificate store specified by the current user.

If set to LocalMachine, the application uses the X.509 certificate store assigned to the local machine.

Default

CurrentUser

Category

Security

Category

Security

Charset For 65535

Property

CharsetFor65535

Description

Specifies a character set from which to convert when fetching character columns (Char, Varchar, Longvarchar, Clob, Char For Bit Data, Varchar For Bit Data, Longvarchar For Bit Data) defined with Coded Character Set Identifier (CCSID) 65535.

Valid Values

0 or any valid IANA code page value

If set to 0 or an empty string, the data provider returns these columns as binary columns and does no conversion of the data.

Default

An empty string

Category

Advanced

Client Host Name

Property

ClientHostName

Description

Specifies the name that the DB2 database server uses to identify the client workstation name.

Valid Values

string

where string is the host name of the client machine.

Default

An empty string

Category

Client Monitoring

Client User

Property

ClientUser

Description

Specifies the name that the DB2 server uses to identify the client name by setting the CLIENT USERID register on the server.

Valid Values

NULL | string

where string is a valid user ID.

Default

An empty string

Category

Client Monitoring

Common Work Arounds

Property

WorkArounds

Description

This option is reserved. Enter values only when directed by Progress DataDirect customer support.

Default

0

Category

Advanced

Connection Pool Behavior

Property

ConnectionPoolBehavior

Description

Specifies the order in which a connection is removed from the connection pool for reuse, based on how frequently or how recently the connection has been used.

Note: This option is ignored if Reauthentication Enabled is set to True.

Valid Values

LeastRecentlyUsed | MostRecentlyUsed | LeastFrequentlyUsed | MostFrequentlyUsed

If set to MostRecentlyUsed, the data provider uses a Last In First Out (LIFO) approach to return the connection that was returned to the pool most recently.

If set to LeastRecentlyUsed, the data provider uses a First In First Out (FIFO) approach to return the connection that has been in the pool for the longest time. This value ensures a balanced use of connections in the pool.

If set to MostFrequentlyUsed, the data provider returns the connection with the highest use count. This value enables applications to give preference to the most seasoned connection.

If set to LeastFrequentlyUsed, the data provider returns the connection with the lowest use count. This value ensures a balanced use of connections in the pool.

Default

LeastRecentlyUsed

Category

Connection Pooling

Connection Reset

Property

ConnectionReset

Description

Specifies whether a connection that is removed from the connection pool for reuse by an application will have its state reset to the initial configuration settings of the connection.

Valid Values

True | False

If set to True, the state of connections removed from the connection pool for reuse by an application is reset to the initial configuration of the connection. Resetting the state impacts performance because the new connection must issue additional commands to the server, for example, resetting the current database to the value specified at connect time.

If set to False, the state of connections is not reset.

Default

False

Category

Connection Pooling

Connection Retry Count

Property

ConnectionRetryCount

Description

Specifies the number of times the data provider tries to connect to the primary server, and, if specified, the alternate servers after the initial unsuccessful attempt. See Configuring Connection Failover for a discussion of load balancing.

Valid Values

0 | x

where x is a positive integer from 1 to 65535.

If set to 0, the data provider does not try to reconnect after the initial unsuccessful attempt.

If set to x, the data provider attempts to connect the specified number of times. If a connection is not established during the retry attempts, the data provider returns an error that is generated by the last server to which it attempted to connect.

Default

0

Category

Failover

Connection Retry Delay

Property

ConnectionRetryDelay

Description

Specifies the number of seconds the data provider waits after the initial unsuccessful connection attempt before retrying a connection to the primary server, and, if specified, the alternate servers. See Configuring Connection Failover for a discussion of connection pooling.

Note: This option has no effect unless the Connection Retry Count connection string option is set to an integer value greater than 0.

Valid Values

0 |x

where x is a positive integer from 1 to 65535.

If set to 0, there is no delay between retrying the connection.

If set to x, the data provider waits the specified number of seconds between connection retry attempts.

Default

3

Category

Failover

Connection Timeout

Property

ConnectionTimeout

Description

Specifies the number of seconds after which the attempted connection to the database server fails if not yet connected. If connection failover is enabled, this option applies to each connection attempt. A value of 0 means that the data provider never times out on a connection attempt.

Valid Values

0 | x

where x is a positive integer greater than 1.

Default

15

Category

Connection Pooling

Create Package

Property

CreatePackage

Description

Specifies whether to explicitly force the binding or rebinding of the packages needed by the data provider.

A rebind can be needed to change a bind-option used for the provider packages, for example, to change the Default Qualifier. When the packages are not found on the database, the data provider automatically attempts to bind them.

Valid Values

True | False

If set to False, the data provider does not attempt to bind packages unless they are missing.

If set to True, the data provider attempts to bind packages unless they are missing.

Default

False

Category

Advanced

Current Function Path

Property

CurrentFunctionPath

Description

Specifies the default path to be used when searching for functions or stored procedures. When set to an empty string, the data provider uses the default specified by the system.

Valid Values

string

where string is default path to be used when searching for functions or stored procedures.

Default

An empty string.

Category

Advanced

Current Schema

Property

CurrentSchema

Description

Specifies the default schema to be used when executing queries. If set to an empty string, the data provider uses the value for the User ID option.

Valid Values

string

where string is a valid DB2 schema.

Default

An empty string.

Category

Advanced

Cursor Description Cache

Property

CursorDescriptionCache

Description

Specifies whether to reuse cached result set description information when the data provider is requested to execute a SELECT statement that exactly matches a previously executed SELECT statement.

This connection string option can affect performance. See Performance Considerations for more information.

Notes:

• If the application does not execute the same SQL statements repeatedly, set the value to False. In this case, setting Cursor Description Cache to True uses memory unnecessarily.
• If the column definition of a table changes between two executions of the same SELECT statement, the data provider may behave unpredictably. We recommend that you set this option to False if you expect your database table definitions to change while your application is accessing them. Alternatively, you can simply restart your application if the table definitions change.

Alias

Cache Column Info

Entity Framework Notes

Ignored

Valid Values

True | False

If set to True, the cached result set description information is reused.

Default

True

Category

Performance

Cursors With Hold

Property

CursorsWithHold

Description

Specifies whether to use held cursors when executing a query. Held cursors maintain their positions in the result set after a commit has been performed, for example, when you are using a DB2DataReader and you want to perform multiple commands on each row of the DataSet, committing after each command.

This connection string option can affect performance. See Performance Considerations for more information.

Valid Values

True | False

If set to True, the application can use Held cursors.

If set to False, held cursors are not used when executing a query. The DB2DataReader’s position is lost after a commit.

Default

True

Category

Advanced

Database Name

Property

Database Name

Description

Specifies the name of the database to which you want to connect.

For DB2 for z/OS, use the name of the DB2 location that you want to access. Your system administrator can determine the name of your DB2 location using the following command:

DISPLAY DDF

Alias

Database

Valid Values

string

where string is the name of the database.

Default

An empty string

Category

Standard Connection

Default Qualifier

Property

DefaultQualifier

Description

Specifies the default qualifier to be used when binding the packages. This option applies only when packages are bound or rebound.

Valid Values

String

where String is the name of a default qualifier. When set to an empty string, the data provider uses the value for User ID.

Default

An empty string

Category

Advanced

Default Schema Restriction

Property

DefaultSchemaRestriction

Description

Specifies the default schema restriction to be used when no Schema restriction is specified in a call to GetSchema.

Valid Values

string

where string is the name of a schema restriction.

Default

An empty string (no restrictions are used)

Category

Schema Information

Defer Prepare

Property

DeferPrepare

Description

Specifies whether the DB2 data provider defers doing a Prepare on the database until the first execute of the statement.

See Performance Considerations for information about configuring this option for optimal performance.

Valid Values

True | False

If set to True, the prepare request to the server is chained to the execute request and is sent in the same packet on the first request. This connection string option can affect performance.

Setting this option to False can be useful If you want to see possible syntax or other prepare time errors when calling the Prepare() method of the DB2Command object:

• If you want to see possible syntax or other prepare time errors when calling the Prepare() method of the DB2Command object.
• If you are connected to DB2 v7.x on Linux/UNIX/Windows and you want to retrieve values from Clob columns that are less than 32 KB. Prepare cannot be deferred in this case.

Default

True

Category

Performance

Dynamic Sections

Property

DynamicSections

Description

Specifies the number of sections to be provided when binding the packages to the database. This value limits the number of concurrent prepared statements at the server for this connection and cannot be higher than the value used when the packages were created.

Valid Values

An integer 2 or greater

Default

200

Category

Advanced

Enable Manual Enlistment

Property

EnableManualEnlistment

Description

Specifies whether the data provider permits manual enlistment in distributed transactions after the connection is opened.

See Performance Considerations for information about enlisting in distributed transactions can affect performance.

Valid Values

True | False

If set to True, the connection, once opened, can be manually enlisted in distributed transactions.

If set to False, the connection, once opened, cannot be manually enlisted in distributed transactions.

Default

False

Category

Advanced

Enable Trace

Property

EnableTrace

Description

Specifies whether tracing is enabled.

Valid Values

0 | x

where x is a positive integer 1 or higher.

If set to x, tracing is enabled.

If set to 0, tracing is not enabled.

Default

0

Category

Tracing

Enable XA

Property

EnableXA

Description

Enables a workaround that provides limited support for tools such as Visual Studio LightSwitch that assume that the underlying data provider can promote a local transaction to a fully-distributed transaction on an as-needed basis. Currently, the data provider cannot promote a local transaction to a fully-distributed transaction.

Valid Values

True | False

When set to True and Enlist is set to True, distributed transactions are supported and calls to Connection.EnlistTransaction function normally. See Performance Considerations for information about using distributed transactions.

When set to False, the connection cannot be enlisted in a distributed transaction. Calls to Connection.EnlistTransaction start a local transaction.

Note: When set to False and Enlist is set to True, the data provider returns an error.

Default

True

Category

Advanced

Encryption Method

Property

EncryptionMethod

Description

Defines how the data provider encrypts data sent between the data provider and the data source. The data provider supports client authentication for SSL. Client authentication uses the user ID of the user logged onto the system on which the data provider is running to authenticate the user to the database.

Valid Values

NoEncryption | DBEncryption | SSL

If set to NoEncryption, data sent between the data provider and the DB2 server is not encrypted.

If set to DBEncryption (DB2 for Linux/UNIX/Windows and DB2 for z/OS), data is encrypted using a DB2-specific encryption algorithm.

Note: DBEncryption can only be set when the Authentication Method connection string option is set to EncryptedUID, EncryptedPassword, or ClearText.

If set to SSL, all data sent between the data provider and the server is encrypted with SSL. The data provider checks the value of the Validate Server Certification connection string option to perform the necessary server authentication if required.

When SSL is enabled, the following connection string options also apply:

• Host Name In Certificate
• Validate Server Certificate

Notes:

• An exception is thrown if the data provider attempts to connect with an invalid encryption method, for example, using SSL when connecting with a server that does not support SSL.
• Because the database server does not authenticate the user when client authentication is used, use this method of authentication if you can guarantee that only trusted clients can access the database server.

Default

NoEncryption

Category

Security

Enlist

Property

Enlist

Description

Specifies whether the data provider automatically attempts to enlist the connection in creating the thread’s current transaction context.

Valid Values

True | False

If set to True, the data provider automatically attempts to enlist the connection.

If set to False, the data provider does not automatically attempt to enlist the connection.

Note: Distributed transactions are supported only when connected to DB2 V8.1 or higher for Linux/UNIX/Windows.

See Performance Considerations for information about configuring this option for optimal performance.

Default

False

Category

Performance

Grantee

Property

Grantee

Description

Specifies the user ID or user group that must be authorized to execute the packages. This option is valid only when binding or rebinding the data provider’s packages.

Valid Values

string

where string is a valid user ID or user group.

Default

PUBLIC

Category

Advanced

Grant Execute

Property

GrantExecute

Description

Specifies whether a grant is to be executed when binding or rebinding the data provider’s packages.

Valid Values

True | False

If set to True, the grant is executed.

If set to False, the grant is not executed.

Default

True

Category

Advanced

Host

Property

Host

Description

Specifies either the name or the IP address of the DB2 server to which you want to connect. For example, if your network supports named servers, you can specify a server name such as DB2server. Or, you can specify an IP address in IPv4 or IPv6 format or a combination of both. See Using IP Addresses for information about using IP addresses.

Valid Values

string

where string is a valid host name or IP address.

Default

localhost

Category

Standard Connection

Host Name In Certificate

Property

HostNameInCertificate

Description

Specifies a host name for certificate validation when SSL encryption is enabled (Encryption Method=SSL) and validation is enabled (Validate Server Certificate=True). This connection string option provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server that the data provider is connecting to is the server that was requested.

Valid Values

empty string | host_name

If no host name is specified, the data provider compares the value specified for the Host option to the Common Name (CN) parts of the certificate’s Subject name. If the values do not match, the connection fails and the data provider throws an exception.

Note: If multiple CN parts are present, the data provider validates the host name against each CN part. If any one validation succeeds, a connection is established.

If SSL or validation is not enabled, this option is ignored.

Default

An empty string

Category

Security

Initial Command Timeout

Property

InitialCommandTimeout

Description

Specifies the default wait time (timeout in seconds) before the data provider terminates the attempt to execute the command and generates an error. This option provides the same functionality as the DB2Command object’s CommandTimeout property without the need to make changes to the application code. Subsequently, an application can use the CommandTimeout property to override the Initial Command Timeout connection string option.

Valid Values

0 or x

where x is any positive integer

If set to 0, the query never times out.

Note: Set the Initial Command Timeout option to a value that is greater than the largest default deadlock detection and timeout value on the server. This ensures that the application receives a more meaningful reply in case of a timeout.

Example

For example, in the following C# code fragment, the connection string instructs the application to wait 60 seconds before terminating the attempt to execute the command. The application then specifies a CommandTimeout of 45 seconds, which overrides the value set in the connection string:

DB2Command command = new DB2Command();
DB2Connection conn = new DB2Connection("…; Initial Command Timeout=60; …");
conn.Open();
command.Connection = connection;
 
// command.CommandTimeout returns 60;
command.CommandTimeout = 45;
// command.CommandTimeout returns 45
command = new
DB2Command
();
command.CommandTimeout = 45;
command.Connection = conn;
// command.CommandTimeout still returns 45

Default

30

Category

Advanced

Initialization String

Property

InitializationString

Description

Specifies one SQL command that is issued immediately after connecting to the database to manage session settings.

Note: If the statement fails to execute for any reason, the connection to the DB2 server fails. The data provider throws an exception that contains the error returned from the DB2 server.

Valid Values

command

where command is a SQL command.

Example

Example 1: To set the date format on every connection using an Alter Session statement:

Initialization String=ALTER SESSION SET DATEFORMAT xxx 

Example 2: To set DRDA accounting values:

Initialization String="EXTNAM=MyApp"

Default

An empty string

Category

Connection Pooling

Isolation Level

Property

IsolationLevel

Description

Specifies the default isolation level to be used when executing queries. Refer to Isolation Levels for more information about isolation levels.

Valid Values

ReadCommitted | ReadUncommitted | RepeatableRead | Serializable | None

If set to ReadCommitted, shared locks are held while the data is being read to avoid reading modified data, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data.

If set to ReadUncommitted, other processes are prevented from changing records that are read or changed by your application (including phantom records) until end of transaction (EOT). Reading from the database does not involve any locking. Shared locks are issued and no exclusive locks are honored.

If set to RepeatableRead, all rows retrieved by an application are locked within a unit of work until EOT, preventing other users from updating the data. As a result, a query issued twice within the same unit of work while a cursor is opened return the same result.

If set to Serializable, updating or inserting is prevented until the transaction is complete.

If set to None, your program can read modified records even if they have not been committed by another person. (On i5/OS, this is the only isolation level that works for collections that have No Logging enabled.) If an application attempts to start a transaction, the data provider throws an exception.

Default

ReadCommitted

Category

Advanced

License Path

Property

LicensePath

Description

Specifies the fully qualified path to the DDTek.lic license file. The license file is installed by default in the product installation directory.

Valid Values

A fully-qualified path

If set to an empty string the data provider looks for the license file in the application’s current directory. If the license file is not found, the data provider checks for keys placed in the registry during the installation process; then, the data provider looks for the license key in the installation directory. If the license key is not found, the data provider fails to connect.

Example

C:\install_dir\DDTek.lic

Default

An empty string

Category

Advanced

Load Balance Timeout

Property

LoadBalanceTimeout

Description

Specifies the minimum number of seconds to keep connections in a connection pool before destroying them. The Min Pool Size option can cause some connections to ignore this value.

Valid Values

0 | x

where x is a positive integer from 1 to 65535.

If set to 0, the lifetime is never limited by time.

See Removing Connections from a Pool for a discussion of connection lifetimes.

Alias

Connection Lifetime

Default

0

Category

Connection Pooling

Load Balancing

Property

LoadBalancing

Description

Determines whether the data provider uses client load balancing in its attempts to connect to primary and alternate database servers. The list of alternate servers is specified by the Alternate Servers connection option.

Valid Values

True | False

If set to True, the data provider attempts to connect to the database servers in random order. See Client Load Balancing for more information about load balancing.

If set to False, client load balancing is not used and the data provider connects to each server based on its sequential order (primary server first, then, alternate servers in the order they are specified).

Note: This option has no effect unless alternate servers are defined for the Alternate Servers connection string option.

The Load Balancing connection string option is an optional setting that you can use in conjunction with connection failover. See Using Connection Failover for a discussion of connection failover and for information about other connection options that you can set for this feature.

Default

False

Category

Failover

Max Pool Size

Property

MaxPoolSize

Description

Specifies the maximum number of connections within a single pool. When the maximum number is reached, no additional connections can be added to the connection pool. The Max Pool Size Behavior option can cause this option to be ignored during periods of high demand.

See Creating a Connection Pool for information about creating connection pools.

Valid Values

An integer from 1 to 65535.

Example

If set to 20, the maximum number of connections allowed in the pool is 20.

Default

100

Category

Connection Pooling

Max Pool Size Behavior

Property

MaxPoolSizeBehavior

Description

Specifies whether the data provider can exceed the number of connections specified by the Max Pool Size option when all connections in the connection pool are in use.

See Creating a Connection Pool for information about creating connection pools.

Valid Values

SoftCap | HardCap

If set to SoftCap, when all connections are in use and another connection is requested, a new connection is created, even when the connection pool exceeds the number specified by Max Pool Size. Idle connections in excess of the Max Pool Size are closed by the pool manager.

If set to HardCap, when the maximum number of connections allowed in the pool are in use and a new connection is requested, the connection request is queued. If no connection becomes available before the Connection Timeout is reached, the data provider terminates the attempt and generates an error.

Default

HardCap

Category

Connection Pooling

Max Statement Cache Size

Property

MaxStatementCacheSize

Description

Specifies the maximum number of cached statements that can be associated with the current connection.

Valid Values

0 | x

where x is a positive integer greater than 1.

If set to 0, statement caching is disabled.

This connection string option can affect performance. See Performance Considerations for more information.

Default

10

Category

Performance

Min Pool Size

Property

MinPoolSize

Description

Specifies the number of connections created when a connection pool is initialized and the minimum number of connections that will be kept in the pool. The connection pool retains this number of connections, even when some connections exceed their Load Balance Timeout value.

See Removing Connections from a Pool for a discussion of connection lifetimes.

Valid Values

0 | x

where x is a positive integer from 1 to 65535.

If set to 0, the connection pool is not required to retain any connections.

Default

0

Category

Connection Pooling

New Password

Property

NewPassword

Description

Specifies a new password for the user ID following a successful connection. If the change of password fails for any reason, the connection to the server fails.

Notes:

• When the EncryptionMechanism property specifies encrypted passwords, the New Password value is also encrypted.
• New Password is mutually exclusive with connection pooling. If the New Password connection string option is defined, the data provider ignores the setting of the Pooling option.

Valid Values

string

where string is a valid password.

Default

An empty string

Category

Security

Package Collection

Property

PackageCollection

Description

Specifies the collection or schema that the data provider uses to create the packages at bind-time. The data provider searches this collection or schema for the packages when they are needed.

Valid Values

string

where string is a valid collection or schema.

Default

NULLID

Category

Advanced

Package Owner

Property

PackageOwner

Description

Specifies the owner to be used when binding or rebinding the packages.

Valid Values

string

where string is the owner of the DB2 packages.

Default

An empty string

Category

Advanced

Parameter Mode

Property

ParameterMode

Description

Specifies the behavior of native parameter markers and binding. This allows applications to reuse provider-specific SQL code and simplifies migration to the DataDirect Connect for ADO.NET data providers.

Entity Framework Notes

Not supported. Instead, the Entity Framework data provider only binds by name.

Valid Values

ANSI | BindByOrdinal | BindByName

If set to ANSI, the ? character is processed as a parameter marker and bound as ordinal.

If set to BindByOrdinal, native parameter markers are used and are bound as ordinal for stored procedures and standard commands.

If set to BindByName, native parameter markers are used and are bound by name. Either colon or @ parameter markers are supported in a prepared statement, but not a combination of the two.

Default

For the ADO.NET data provider, the default is ANSI.

For the ADO.NET Entity Framework data provider, the only supported value is BindByName.

Category

Advanced

Password

Property

Password

Description

Specifies the case-sensitive password used to connect to your DB2 database.

Valid Values

string

where string is a valid password.

Default

An empty string

Category

Security

Persist Security Info

Property

PersistSecurityInfo

Description

Specifies whether to display security information such as the password in clear text in the connection string.

Valid Values

True | False

If set to True, the value of the Password connection string option is displayed in clear text.

If set to False, the data provider does not display the password in clear text in the connection string.

Default

False

Category

Security

Pooling

Property

Pooling

Description

Specifies whether connections are pooled.

Valid Values

True | False

If set to True, connection pooling is enabled. See Using Connection Pooling for a discussion of connection pooling.

Default

True

Category

Connection Pooling

Port

Property

Port

Description

Specifies the port of the DB2 DRDA listener.

Valid Values

port

where port is the port number.

Default

446

Category

Standard Connection

Program ID

Property

ProgramID

Description

Specifies the value of the Client Product Version/ID on the server. For DB2 LUW V9 and higher, this value is in the CLIENT_PRDID column of the SYSIBMADM.APPLICATIONS table.

Valid Values

string

where string is the value that identifies the Client Product Version/ID on the server.

Default

An empty string

Category

Client Monitoring

Reauthentication Enabled

Property

ReauthenticationEnabled

Description

Determines whether the data provider can reauthenticate a connection, that is, perform a proxy authentication, in a pooled connection environment.

Note: The user performing the switch must have SETSESSIONUSER permission (DB2 V9.1 and higher on Linux/UNIX/Windows) or SYSADM permission (DB2 V8.1.4 and higher on Linux/UNIX/Windows).

Valid Values

True | False

If set to True, the data provider checks the credentials of the OS authenticated user requesting a connection and reassigns a connection enabled for reauthentication to the user.

If set to False, the data provider does not reauthenticate users in a pooled connection environment. Each user has a different connection string and a different connection pool.

Default

False

Category

Security

Schema Collection Timeout

Property

SchemaCollectionTimeout

Description

Specifies the number of seconds after which an attempted schema collection operation fails if it is not yet completed.

Valid Values

A positive integer 1 or higher.

Default

120

Category

Schema Information

Schema Options

Property

SchemaOptions

Description

Specifies additional database metadata that can be returned. By default, the data provider prevents the return of some available database metadata to optimize performance. If your application needs this database metadata even though returning it is performance-expensive, specify the name or hexadecimal value of the metadata.

This option can affect performance (see Performance Considerations).

Valid Values

If set to ShowDescriptions or 0x01, descriptions of catalogs, procedures, tables, and views are returned.

If set to IncludeSynonyms or 0x02, synonyms for procedures, tables, and views are returned.

If set to ShowColumnDefaults or 0x04, column defaults are returned.

If set to ShowProcedureDefinitions or 0x20, procedure definitions are returned.

If set to ShowViewDefinitions or 0x40, view definitions are returned.

If set to ShowAll or 0xFFFFFFFF, all database metadata is returned.

To show more than one piece of the omitted database metadata, specify either a comma-separated list of the names, or the sum of the hexadecimal values of the metadata that you want to return.

The following table lists the names and hexadecimal values you can specify to increase the amount of database metadata returned.

DB2 Column Collections

_{1 COLUMN_HAS_DEFAULT is always reported with a value of null.}

Example

To return descriptions of tables and columns, specify Schema Options=ShowDescriptions or Schema Options=0x01.

To return descriptions of the Tables and Columns collections, and the procedure definitions (hexadecimal values 0x01 and 0x20, respectively), specify one of the following:

Schema Option=ShowDescriptions, ShowProcedureDefinitions
Schema Options=0x21

Default

0x7F or All except IgnoreUserPermissions

Category

Schema Information

Server

Property

Server

Description

Specifies the host and port of the DB2 server to which you want to connect.

The value is ignored if the Host and Port properties are already set.

Valid Values

host:port

Default

localhost:446

Category

Standard Connection

Socket Buffer Size

Property

SocketBufferSize

Description

Specifies the send and receive buffer size (in KB) for the socket.

Valid Values

A 32-bit integer

Default

32

Category

Performance

Sort Sequence Table

Property

SortSequenceTable

Description

Specifies the value to which the DB2 SRTSEQ parameter is set. The SRTSEQ parameter determines the sort sequence table that the DB2 for i database uses for sorting character data.

This connection string option has no effect unless the Create Package connection string option is set to True.

Valid Values

HEX | JOBRUN

If set to HEX, no sort sequence table is used. The hexadecimal values of the graphic characters are used for a binary sort.

If set to JOBRUN, the SRTSEQ value for the job is used.

Default

HEX

Category

Advanced

Statement Cache Mode

Property

StatementCacheMode

Description

Specifies the statement caching mode for the lifetime of the connection. See Using Statement Caching for more information.

Entity Framework Notes

ExplicitOnly is the only valid value for the Entity Framework data provider.

Valid Values

Auto | ExplicitOnly

If set to Auto, statement caching is enabled for statements marked as Implicit by the Command property StatementCacheBehavior. These commands have a lower priority than that of explicitly marked commands, that is, if the statement pool reaches its maximum number of statements, the statements marked implicit are removed from the statement pool first to make room for statements marked Cache.

If set to ExplicitOnly, only commands that are marked Cache by the StatementCacheBehavior property are cached.

Notes:

• ExplicitOnly is the only valid value for the Entity Framework data provider.
• To enable statement caching for existing applications that use the DataDirect Connect for ADO.NET 3.1 data providers without changing any application code, set Statement Cache Mode to Auto. All statements are eligible to be placed in the statement cache.

Default

ExplicitOnly

Category

Performance

Trace File

Property

TraceFile

Description

Specifies the path and name of the trace file. If the specified trace file does not exist, the data provider creates it.

Valid Values

A valid path name

Example

\DB2Trace.txt

Default

An empty string

Category

Tracing

Use Column Labels

Property

UseColumnLabels

Description

A Boolean value to specify whether to enable a workaround to handle column name problems for stored procedures on a iSeries servers.

Valid Values

True | False

If set to True, the data provider uses the column name for the stored procedure result set columns as a column label.

If set to False (the initial default), column labels are not used.

Default

False

Category

Schema Information

Use Current Schema

Property

UseCurrentSchema

Description

Specifies whether the results are restricted to tables or views in the current schema of the GetSchema method of the DbConnection object.

Valid Values

True | False

If set to True, the results returned from any GetSchema call are restricted to tables and views in the current schema.

If set to False, the results returned from a GetSchema call are not restricted to tables and views in the current schema unless the call includes Restrictions parameters.

Default

False

Category

Schema Information

User ID

Property

UserID

Description

Specifies the DB2 logon ID to use for this connection.

Valid Values

string

where string is a valid user name.

Example

db2admin

Default

An empty string

Category

Security

Validate Server Certificate

Property

ValidateServerCertificate

Description

Specifies whether the data provider must verify the server certificate as valid according to a Certificate Authority.

Valid Values

True | False

If set to True, the data provider validates the server certificate during authentication.

If set to False, no certificate verification is required during authentication.

Default

True

Category

Security

Work Arounds

Property

WorkArounds

Description

This option is reserved. Enter values only when directed by Progress Customer Support.

Default

0

Category

Advanced

Xml Describe Type

Property

XmlDescribeType

Description

Specifies that the XML returned from a column of type XML is returned in character format.

Valid Values

String | Binary

If set to String, the XML is returned in character format.

If set to Binary, the result set column is described with a column type of BLOB and the column type name is xml. Your application can use the GetBytes(), GetValue(), GetValues(), or GetXmlReader() methods to return data as binary.

Default

String

Category

Advanced

Implementation Differences for the DB2 Entity Framework Data Provider

Note: Some functionality that is provided by connection string options in the ADO.NET data provider is not supported in the Entity Framework data provider. However, the functionality is provided through the use of pseudo stored procedures. see Using Pseudo Stored Procedures for more information.

See Understanding the DB2 Wire Protocol Driver and Packages and Supported Properties and Methods for other differences.