DB2 10.5 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   Specifies the database alias. The maximum size of the value of the Database keyword is 255 bytes.
NewPWD   Specifies the new password for the user ID.
Password | PWD   Specifies the password.
Server   Specifies 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   Specifies the user ID.
Table 2. pureQuery keywords
Keyword Default Description
allowDynamicSQL True Specifies whether an application can dynamically run any SQL statements that are not captured in a pureQueryXML file or that are captured but not bound.
capturedOnly False Specifies whether to run only the SQL statements that are in the pureQueryXML file.
captureMode off Specifies whether to capture information about SQL statements and the type of information to capture. An error is thrown if you set both the captureMode keyword to on and the executionMode keyword to static.
enableDynamicSQLReplacement False Specifies whether to run alternative SQL statements that are in a pureQueryXML file when both the original statements and the alternative statements were not bound.
executionMode dynamic Indicates whether previously captured statements are executed statically by using pureQuery technology. An error is thrown if you set both the captureMode keyword to on and the executionMode keyword to static.
maxNonParmSQL -1 Specifies the maximum number of non-parameterized SQL statements to capture. This keyword applies only when the value of the captureMode keyword is ON. The maxNonParmSQL keyword value is ignored when the value of the captureMode keyword is OFF.
propertiesGroupId   Specifies a runtime group ID in a repository that is created in a database.
pureQueryXML   Specifies 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 that is enabled with pureQuery client optimization.
repositoryRequired no Specifies the pureQuery client optimization behavior if errors occur during retrieving or writing pureQuery data.
sqlLiteralSubstitution NOT_SET Specifies whether to capture and consolidate SQL statements that share syntax and differ only in the literal values by having the pureQuery client substitute parameter markers for the literal values.
traceFile Specifies the name of the pureQuery log file.
traceLevel OFF Specifies the level of detail to write to the log file.
Table 3. Additional keywords
Keyword Default Description
Authentication   Specifies the type of authentication. The following values are acceptable:
  • CERTIFICATE
  • DATA_ENCRYPT
  • GSSPLUGIN
  • KERBEROS
  • SERVER
  • SERVER_ENCRYPT
ClientAccountingString   Specifies the client accounting string that is sent to a database.
ClientApplicationName   Specifies the client application name that is sent to a database.
ClientEncAlg   Specifies whether advanced encryption standard (AES) encryption is used. The acceptable value is AES.
ClientUserID   Specifies the client user ID that is sent to a database.
ClientWorkStationName   Specifies the client workstation name that is sent to a database.
CodePage 0 Specifies the code page identifier.
ConcurrentAccessResolution   Specifies the string values that represent the concurrent access resolution. The following values are acceptable:
CurrentlyCommitted
Use the currently committed version of the data.
SkipLockedData
Skip rows in the case of record lock conflict. The SkipLockedData option is supported only with DB2® for z/OS® Version 10 and later servers.
WaitForOutcome
Wait for the commit or rollback action when encountering locked rows.

The database server defines the default behavior if the client does not provide a ConcurrentAccessResolution keyword value.

For DB2 for i servers, the ConcurrentAccessResolution property is supported only by DB2 for i V7R1 and later servers.

ConnectNodeNumber 0 - 999 Specifies the database partition server to connect to.
Connection Lifetime 60 Specifies the amount of time in seconds that a connection can remain idle in the connection pool.
Connection Reset false Specifies whether a connection is put into the connection pool when the connection is closed. The following values are acceptable:
false
The connection is put into the connection pool when the connection is closed.
true
The connection is not put into the connection pool when the connection is closed.
Connection Timeout | Connect Timeout | Timeout 0 Specifies 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 connection timeout because there is a second attempt to connect to the alternative server.

The connection timeout value is enforced for a connection regardless of a high availability solution being enabled. If a connection attempt fails within the time that is specified by the connection timeout value, control is returned to the application with an SQL30081N error. The error and control are returned whether all alternative servers are tried or not.

CurrentFunctionPath   Specifies the list of schema names that can be used to resolve function references and data type references in dynamic SQL statements.
CurrentPackageSet   Issues the SET CURRENT PACKAGESET statement after every connection.
CurrentSchema   Specifies the schema name that is used to qualify all unqualified SQL objects that are used with the current connection.
CurrentSQLID   Specifies the SQLID that is used to qualify all unqualified SQL objects that are used with the current connection to DB2 for z/OS servers. On servers other than DB2 for z/OS, setting the CurrentSQLID keyword is equivalent to setting the CurrentSchema keyword.
DB2Explain off Determines whether the server generates Explain snapshot, Explain table, or both types of information. The following values are acceptable:
Off
Does not generate Explain information for snapshots and tables.
Snapshot
Generates Explain information for a snapshot.
SnapshotAndTable
Generates Explain information for snapshots and tables.
Table
Generates Explain information for a table.
DBName Specifies the database name. This keyword is used only when connecting to DB2 for z/OS and OS/390® servers and only if the application requests base table catalog information. If many tables exist in the z/OS or OS/390 subsystem, you can specify the DBName keyword to reduce the time that it takes for the application to query table information and reduce the amount of table information in the result set.
DelimIdent Specifies whether the connected Informix® database server must support delimited SQL identifiers. The DelimIdent keyword can be set to one of the following values:
0
False. Indicates that a connected Informix database server does not have to support delimited SQL identifiers.
1
True. Indicates that a connected Informix database server must support delimited SQL identifiers.
DisableCursorHold false
Indicates whether cursors that are opened on the server are left open after a transaction is committed. The following values are acceptable:
false
Cursors that are opened on the server are left open after the transaction is committed.
true
Cursors that are opened on the server are closed after the transaction is committed.
Enlist true Indicates whether enlistment in the Distributed Transaction Coordinator (DTC) is allowed. The following values are acceptable:
false
Enlistment in the DTC is not allowed.
true
Enlistment in the DTC is allowed. Enlistment occurs only if a COM+ transaction is in progress at connection time.
FitHighPrecisionType ReturnException Specifies how IBM® Data Server Provider for .NET converts the high precision data to the .NET system types. The following values are acceptable:
AsString
Converts the column to a .NET string type.
ReturnException
Results in a truncation exception if the value does not fit in the .NET system type.
WithTruncate
Returns a .NET system type after silently truncating the column value if required.
HostVarParameter false Indicates whether host variable (:param) support is enabled. The following values are acceptable:
false
Host variable support is disabled.
true
Host variable support is enabled.
Interrupt

In Version 10.5 GA and Fix Pack 1: 1

In Version 10.5 Fix Pack 2 and later:
  • 1 for connections to all database servers other than DB2 for z/OS servers
  • 2 for connections to DB2 for z/OS servers
Specifies the 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   Specifies the isolation level for the connection. The following values are acceptable:
  • Chaos
  • ReadCommitted
  • ReadUncommitted
  • RepeatableRead
  • Serializable
  • Transaction
The Chaos option is supported only for applications that are connected to DB2 for i 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 Specifies the maximum pool size.
Min Pool Size 0 Specifies the minimum pool size.
Persist Security Info false Indicates whether security-sensitive information is returned. The following values are acceptable:
false
Does not return security-sensitive information as part of a connection string. You avoid exposing security-sensitive information.
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.
Pooling true Indicates whether connection pooling is disabled. The following values are acceptable:
false
Connection pooling is disabled.
true
Connection pooling is enabled.
ProgramId   Specifies the program ID that is sent to a database.
ProgramName   Specifies the program name that is sent to a database.
QueryTimeout Specifies the number of seconds to wait for an SQL statement or XQuery expression to complete. The QueryTimeout keyword indicates the default number of seconds to wait for execution of an SQL statement or XQuery expression to be completed before attempting to cancel the execution and return control to the application. This keyword overrides the default value of the DB2Command.CommandTimeout property, which is 30 seconds.
ResultArrayAsReturnValue false Indicates whether result sets from Informix user-defined routines (UDRs) are returned as parameters of type ReturnValue. Values are as follows:
false
Result sets are not returned as parameters of type ReturnValue.
true
Result sets are returned as parameters of type ReturnValue.
This keyword is ignored when a ReturnValue parameter is not bound in the parameter collection.
RetrieveXMLInBinaryFormat false Specifies whether XML data is returned in binary format. The following values are acceptable:
false
XML data is not returned in binary format.
true
XML data is returned in binary format.
RetryParameterBindingOnError true Indicates whether the provider re-executes failed statements. The following values are acceptable:
false
IBM Data Server Provider for .NET does not re-execute failed statements.
true
IBM Data Server Provider for .NET re-execute failed statements.
SchemaList   Specifies a string value that contains list of schemas. The schema list is used to provide a more restrictive default, and therefore help improve performance, for those applications that list every table in the database.
Security   Indicates whether to use SSL as a secure transport. The SSL is the only value that can be specified for the Security keyword.
SessionTimeZone   Sets the value of the SESSION TIMEZONE special register on the z/OS server.
SkipSynonymProcessing false
Indicates whether IBM Data Server Provider for .NET can send a connection string to the DbPermission.Add method without passing the connection string through the DB2ConnectionStringBuilder class. The following values are acceptable:
false
IBM Data Server Provider for .NET does not send the connection string through the DB2ConnectionStringBuilder class.
true
IBM Data Server Provider for .NET sends the connection string through the DB2ConnectionStringBuilder class.
SSLClientKeystash   Specifies the SSL stash file that is used for an SSL connection.
SSLClientKeystoredb   Specifies the SSL key database file that is used for an SSL connection.
SSLClientKeystoreDBPassword   Sets the keystore database password to use with CERTIFICATE authentication.
SSLClientLabel   Sets a unique label, which is mapped to a specific certificate to use with CERTIFICATE authentication.
SSLServerCertificate   Specifies the fully qualified name of a self-signed server certificate or a certificate authority (CA) certificate.
StatementConcentrator  
Indicates whether statement concentration is used. The following values are acceptable:
Literals
The statement concentrator with literals behavior is enabled. Server-side statement concentrator restrictions apply.
Off
The statement concentrator is disabled.
If you do not set this keyword, the server configuration determines the literal replacement behavior.
TargetPrincipal  

Specifies the fully qualified Kerberos principal name for the target server. The fully qualified Kerberos principal name of the DB2 instance owner in the form of name@REALM or 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.

TrustedContextSystemPassword | TCSPWD  

Specifies the password that corresponds to the trusted context SYSTEM AUTHID to use with the connection.

TrustedContextSystemUserID | TCSUID  

Specifies the trusted context SYSTEM AUTHID to use with the connection.

Many of the settings that you can specify in the string have corresponding read-only properties. For example, the DATABASE keyword corresponds to the DB2Connection.Database property. When you set the connection string, all these read-only properties are updated except if an error is detected. In this case, none of the properties are updated. The DB2Connection properties, such as DB2Connection.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 a DB2Connection object 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();
}