DB2 Version 10.1 for Linux, UNIX, and Windows

DB2Connection.ConnectionString property

Gets or sets the string that is used to open a database connection.

Namespace:: IBM.Data.DB2
Assembly:: IBM.Data.DB2 (in IBM.Data.DB2.dll)

Syntax

[Visual Basic]
Public Property ConnectionString As String  Implements _
   IDbConnection.ConnectionString
[C#]
public string ConnectionString {get; set;}
[C++]
public: __property String* get_ConnectionString();
public: __property void set_ConnectionString(String*);
[JScript]
public function get ConnectionString() : String;
public function set ConnectionString(String);

Implements:: IDbConnection.ConnectionString

Property value

The connection string that includes settings, such as the database name, that are needed to establish an initial connection. The default value is an empty string (""). The maximum length is 1024 characters.

Remarks

You can use the ConnectionString property to connect to any supported data server. You can set the ConnectionString property only when the connection is closed.

The supported keywords are as follows.

Important: Only pureQuery keywords are case sensitive.

Table 1. Common keywords
Keyword	Default	Description
Database		The database alias (for a cataloged database). The maximum size of the Database keyword is 255 bytes.
Password \| PWD		The password.
Server		The server name with an optional port number for a direct connection. Use either the IPv4 notation (`server name`\|`ip address`[:`port`]) or the IPv6 notation.
User ID \| UID		The user ID.
NewPWD		The new password for the user name.

Table 2. pureQuery keywords
Keyword	Default	Description
allowDynamicSQL		Specifies whether to allow an application to run dynamically any SQL statements that are not captured in a pureQueryXML file or that are captured but not bound.
captureMode	off	An indication of whether dynamically executed statements are captured for use with pureQuery®. The following values are acceptable: off on An error is thrown if you set both the captureMode keyword to on and the executionMode keyword to static.
capturedOnly		Specifies whether to run only the SQL statements that are in the pureQueryXML file.
enableDynamicSQLReplacement		Specifies whether to run alternative SQL statements that were added to a pureQueryXML file and both the original statements and the alternative statements were not bound.
executionMode	dynamic	An indication of whether previously captured statements are executed statically by using pureQuery technology. The following values are acceptable: dynamic static An error is thrown if you set both the captureMode keyword to on and the executionMode keyword to static.
maxNonParmSQL		Specifies the maximum number of non-parameterized SQL statements to capture. This option applies only when the value of captureMode is ON. When captureMode is OFF, pureQuery ignores this parameter.
propertiesGroupId		Specifies a runtime group ID in a repository that is created in a database.
pureQueryXML		The file name and path of the pureQuery capture file.
pureQueryXmlRepository		Specifies the repository that contains the pureQueryXML file data that is used with a .NET application enabled with pureQuery client optimization.
repositoryRequired		Specifies the pureQuery client optimization behavior if errors occur during retrieving or writing pureQuery data.
sqlLiteralSubstitution		Specifies whether to capture and consolidate SQL statements that share syntax but differ only in the literal values by having pureQuery substitute parameter markers for the literal values.
traceFile		The name of the pureQuery log file.
traceLevel		Specifies the level of detail to write to the log file.

Table 3. Additional keywords
Keyword	Default	Description
Authentication	Not specified	The type of authentication. The following values are acceptable: SERVER SERVER_ENCRYPT DATA_ENCRYPT KERBEROS GSSPLUGIN CERTIFICATE
ClientAccountingString		The client accounting string that is sent to a database.
ClientApplicationName		The client application name that is sent to a database.
ClientEncAlg		An indication of whether advanced encryption standard (AES) encryption is being used. The acceptable value is AES.
ClientUserID		The client user ID that is sent to a database.
ClientWorkStationName		The client workstation name that is sent to a database.
CodePage	0	The code page identifier.
ConcurrentAccessResolution		The string values that represent the concurrent access resolution. The following values are acceptable: CurrentlyCommitted – Use the currently committed version of the data. WaitForOutcome – Wait for the `COMMIT` or `ROLLBACK` action when encountering locked rows. SkipLockedData – Skip rows in the case of record lock conflict. The SkipLockedData is supported only with DB2® for z/OS® Version 10.1 and later servers. The database server defines the default behavior if the client does not provide a ConcurrentAccessResolution value.
ConnectNodeNumber	0 to 999	Specifies the database partition server to which a connection is to be made.
Connection Lifetime	60	The amount of time in seconds that a connection can remain idle in the connection pool.
Connection Reset	false	An indication of whether a connection is put into the connection pool when the connection is closed. The following values are acceptable: true. The connection is not put into the connection pool when the connection is closed. false. The connection is put into the connection pool when the connection is closed.
Connection Timeout \| Connect Timeout \| Timeout	0	The time in seconds to wait for a database connection to be established. A value of 0 indicates that there is no time limit for a database connection to be established. If client reroute is enabled, the maximum waiting time is approximately double that of the specified connect timeout because there is a second connection attempt to the alternate server. The connect timeout value is enforced for a connection regardless of a high availability solution being enabled. If a connection attempt fails within the time specified by the connect timeout value, control is returned to the application with an SQL30081N error. The error and control is returned whether all alternative servers are tried or not.
CurrentPackageSet		Issues the SET CURRENT PACKAGESET statement after every connection.
CurrentSchema		The schema name that is used to qualify all unqualified SQL objects that are used with the current connection.
CurrentSQLID		The SQLID that is used to qualify all unqualified SQL objects that are used with the current connection to DB2 for z/OS. On servers other than DB2 for z/OS, setting the CurrentSQLID keyword is equivalent to setting the CurrentSchema keyword.
DB2Explain	off	Determines whether Explain snapshot, Explain table, or both information will be generated by the server. The following values are acceptable: Off– Disables the Explain information for snapshots and tables. Table– Generates Explain information for a table. Snapshot – Generates Explain information for a snapshot. SnapshotAndTable– Enables the Explain information for snapshots and tables.
DBName		This option is used only when connecting to DB2 for z/OS and OS/390®, and only if (base) table catalog information is requested by the application. If many tables exist in the z/OS or OS/390 subsystem, you can specify a DBName to reduce the time it takes for the application to query table information, and reduce the amount of table information in the result set.
DelimIdent		This option is used only when connecting to the Informix® database server. The DelimIdent parameter specifies whether the connected Informix database server must support delimited SQL identifiers. Valid value for the DelimIdent parameter includes: 1 - True. Indicates that connected Informix database server must support delimited SQL identifiers. 0 - False. Indicates that connected Informix database server do not have to support delimited SQL identifiers.
DisableCursorHold	false	An indication of whether cursors that are opened on the server should be left open after committing a transaction. The following values are acceptable: true. Cursors opened on the server are closed after the transaction is committed. false. Cursors opened on the server are left open after the transaction is committed.
Enlist	true	An indication of whether enlistment in the Distributed Transaction Coordinator (DTC) is allowed. The following values are acceptable: true. Enlistment in the DTC is allowed. Enlistment occurs only if a COM+ transaction is in progress at connection time. false. Enlistment in the DTC is not allowed.
FitHighPrecisionType	ReturnException	The following values are acceptable: WithTruncate. Results in returning a .NET system type after silently truncating the column value if required. AsString. Results in converting the column to a .NET string type. ReturnException. Results in a truncation exception if the value does not fit in the .NET system type.
HostVarParameter	false	An indication of whether host variable (:param) support is enabled. The following values are acceptable: true. Host variable support is enabled. false. Host variable support is disabled.
Interrupt	1	An interrupt processing mode. The following values are acceptable: 0. Interrupt processing is disabled. 1. If the server supports an interrupt, an interrupt is sent. Otherwise, the connection is dropped. 2. An interrupt results in a dropped connection, regardless of the interrupt capabilities of the server.
Isolation Level \| IsolationLevel		The isolation level for the connection. The following values are acceptable: ReadCommitted ReadUncommitted RepeatableRead Serializable Transaction Chaos Note: The keyword `Chaos` is supported only for applications that are connected to the IBM® iSeries® servers. If you set the isolation level to Transaction, the isolation level is set to the value of Transaction.Current.IsolationLevel.
Max Pool Size	No maximum	The maximum pool size.
Min Pool Size	0	The minimum pool size.
Persist Security Info	false	An indication of whether security-sensitive information is returned. The following values are acceptable: true. Allows security-sensitive information, such as a password, to be returned as part of a connection string after the connection is opened or if the connection was ever open. false. Does not return security-sensitive information as part of a connection string. You avoid exposing security-sensitive information.
Pooling	true	An indication of whether connection pooling is disabled. The following values are acceptable: true. Connection pooling is enabled. false. Connection pooling is disabled.
ProgramId		The program ID that is sent to a database.
ProgramName		The program name that is sent to a database.
QueryTimeout		Gets or sets the value of the QueryTimeout keyword. The QueryTimeout indicates the default number of seconds to wait for an SQL statement or XQuery expression to complete executing before attempting to cancel the execution and return control to the application. This attribute overrides the default 30 second `DB2Command.CommandTimeout` value.
ResultArrayAsReturnValue	false	An indication of whether results sets from Informix user-defined routines (UDRs) are returned as parameters of type ReturnValue. If you set the ResultArrayAsReturnValue keyword to true, result sets are returned as parameters of ReturnValue. This keyword is ignored when a ReturnValue parameter is not bound in the parameter collection.
RetrieveXMLInBinaryFormat	false	The value that represents whether XML data should be returned in binary format.
RetryParameterBindingOnError	true	The value that indicates whether the provider re-executes failed statements.
SchemaList		The schema list that is used to provide a more restrictive default, and therefore improve performance, for those applications that list every table in the database.
Security		An indication of whether to use SSL as a secure transport.
SessionTimeZone		Sets the value of the SESSION TIMEZONE special register on the z/OS server.
SSLClientKeystash		Set SSLClientKeystash to the fully qualified stash file name.
SSLClientKeystoredb		Set SSLClientKeystoredb to the fully qualified key database file name.
SSLClientKeystoreDBPassword		Sets keystore database password to use with the CERTIFICATE authentication.
SSLClientLabel		Sets a unique label, which is mapped to a specific certificate to use with the CERTIFICATE authentication.
StatementConcentrator		An indication of whether statement concentration is used. The following values are acceptable: Off. The statement concentrator is disabled. Literals. The statement concentrator with literals behavior is enabled. Server-side statement concentrator restrictions apply. If you do not set this keyword, the literal replacement behavior is determined by the server configuration.
SkipSynonymProcessing	false	An indication of whether IBM Data Server Provider for .NET can send a connection string to the DbPermission.Add method without passing through the DB2ConnectionStringBuilder class. The following values are acceptable: true. IBM Data Server Provider for .NET sends the connection string through the DB2ConnectionStringBuilder class. false. IBM Data Server Provider for .NET does not send the connection string through the DB2ConnectionStringBuilder class.
TargetPrincipal		The fully qualified Kerberos principal name for the target server; that is, the fully qualified Kerberos principal of the DB2 instance owner in the form of `name/instance@REALM`. For Windows operating systems, the fully qualified Kerberos principal name is the logon account of the DB2 server service in the form of `userid@DOMAIN`, `userid@xxx.xxx.xxx.com` or `domain\userid`
TrustedContextSystemUserID \| TCSUID		The trusted context SYSTEM AUTHID that is to be used with the connection.
TrustedContextSystemPassword \| TCSPWD		The password that corresponds to the trusted context SYSTEM AUTHID to be used with the connection.

Many of the settings that you can specify in the string have corresponding read-only properties, for example, the DATABASE corresponds to the Database property. When you set the connection string, all of these read-only properties are updated, except if an error is detected. In this case, none of the properties are updated. DB2Connection properties, such as Database) return only default settings or those settings that you specified for the ConnectionString property.

Validation of the connection string occurs when you set it.

Resetting the ConnectionString property on a closed connection resets all connection string values and related properties, including the password.

Example

[Visual Basic, C#] The following examples create an DB2Connection and set some of its properties in the connection string:

[Visual Basic]
Public Sub CreateDB2Connection()
    Dim myConnString As String = _
       "DATABASE=SAMPLE;"
    Dim myConnection As New DB2Connection(myConnString)
    myConnection.Open()
    MessageBox.Show("ServerVersion: " + myConnection.ServerVersion _
       + ControlChars.Cr + "Database: " + myConnection.Database.ToString())
    myConnection.Close()
End Sub

[C#]
public void CreateDB2Connection()
{
   string myConnString = "DATABASE=SAMPLE;";
   DB2Connection myConnection = new DB2Connection(myConnString);
   myConnection.Open();
   MessageBox.Show("ServerVersion: " + myConnection.ServerVersion
      + "\nDatabase: " + myConnection.Database.ToString());
   myConnection.Close();
}