Using This Guide
Getting Started
Quick Start Connect
Connecting to a Database
1. Setting the Classpath
2. Registering the Drivers
3. Passing the Connection URL
URL Formats: DataDirect Connect for JDBC Drivers
URL Formats: DataDirect Connect XE for JDBC Drivers
Testing the Connection
Using the Drivers
About the Product
Drivers
DataDirect Test
DataDirect Spy
DataDirect Connection Pool Manager
J2EE Connector Architecture Resource Adapters
Using Resource Adapters with an Application Server
Using Resource Adapters from an Application
Security Permissions for Resource Adapters
Using Connection Properties
Providing Connection Information
Optimizing Performance
Connecting Using the JDBC Driver Manager
Registering the Drivers
Specifying Connection URLs
URL Format: DataDirect Connect for JDBC
URL Format: DataDirect Connect XE for JDBC
Connecting Using Data Sources
How Data Sources Are Implemented
Creating Data Sources
Calling a Data Source in an Application
Using IP Addresses
Testing Connections
Required Permissions for the Java 2 Platform
Permissions for Establishing Connections
Granting Access to Java Properties
Granting Access to Temporary Files
Granting Access to an Oracle tnsnames.ora File
Permissions for Kerberos Authentication
DB2
Microsoft SQL Server
Sybase
Apache Hive
Permissions for Bulk Load from a CSV File
Unicode Support
Error Handling
Driver Errors
Database Errors
Advanced Features
Using Failover
Connection Failover
Extended Connection Failover
Select Connection Failover
Guidelines for Primary and Alternate Servers
Using Client Load Balancing
Using Connection Retry
Using Client Information
How Databases Store Client Information
Storing Client Information
Returning Client Information
Returning MetaData About Client Information Locations
DB2 Workload Manager (WLM) Attributes
DB2 V9.5 and Higher for Linux/UNIX/Windows
DB2 for z/OS
Using Security
Authentication
Data Encryption Across the Network
SSL Encryption
SSL Server Authentication
SSL Client Authentication (DB2, MySQL, Oracle, and PostgreSQL)
Using Connection Pooling
Understanding Connection Pooling
Using Reauthentication
Using Statement Pooling
Using DataDirect Bulk Load
Using a DDBulkLoad Object
Exporting Data to a CSV File
Loading Data From a ResultSet Object
Loading Data From a CSV File
Specifying the Bulk Load Operation
Logging
Using Bulk Load for Batch Inserts
Using Bulk Load for Single Inserts/Updates/Deletes (Salesforce Driver)
Using CSV Files
Bulk Load Configuration File
Bulk Load Configuration File Schema
Verifying the Bulk Load Configuration File for Database Connections
Character Set Conversions
External Overflow Files
Discard File
DataDirect Connect Drivers
DB2 Driver
Data Source and Driver Classes
Connection URL
DB2 for Linux/UNIX/Windows
DB2 for z/OS and i
J2EE Connector Architecture Resource Adapter Class
Connection Properties
AccountingInfo
AddToCreateTable
AllowImplicitResultSetCloseForXA
AlternateID
AlternateServers
ApplicationName
AuthenticationMethod
BatchPerformanceWorkaround
BulkLoadBatchSize
CatalogIncludesSynonyms
CatalogOptions
CatalogSchema
CharsetFor65535
ClientHostName
ClientUser
CodePageOverride
ConcurrentAccessResolution
ConnectionRetryCount
ConnectionRetryDelay
ConvertNull
CreateDefaultPackage
CurrentFunctionPath
CurrentQueryOptimization
Database
DatabaseName
DynamicSections
EnableCancelTimeout
EncryptionMethod
FailoverGranularity
FailoverMode
FailoverPreconnect
Grantee
GrantExecute
HostNameInCertificate
ImportStatementPool
InitializationString
InsensitiveResultSetBufferSize
JavaDoubleToString
JDBCBehavior
KeyPassword
KeyStore
KeyStorePassword
LoadBalancing
LobStreamingProtocol
LocationName
LoginTimeout
LongDataCacheSize
MaxPooledStatements
MaxStatements
OptimizationProfile
OptimizationProfileToFlush
PackageCollection
PackageOwner
Password
PortNumber
ProgramID
QueryTimeout
ReplacePackage
ResultSetMetaDataOptions
SendStreamAsBlob
ServerName
SpyAttributes
StripNewlines
TrustStore
TrustStorePassword
UseCurrentSchema
User
ValidateServerCertificate
WithHoldCursors
XMLDescribeType
Performance Considerations
DB2 Packages
Creating DB2 Packages Using the Package Manager
Creating DB2 Packages Using Connection Properties
Example: DB2 for Linux/UNIX/Windows
Example: DB2 for z/OS and i
Creating DB2 Packages Using Package Creation List Files
Copying the DB2 Packages (z/OS and i)
Data Types
Returning and Inserting/Updating XML Data
Returning XML Data
Character Data
Binary Data
Inserting/Updating XML Data
Character Data
Binary Data
Support for DB2 pureScale
Authentication
Using the AuthenticationMethod Property
Configuring User ID/Password Authentication
Configuring Kerberos Authentication
Product Requirements
Configuring the Driver
Specifying User Credentials for Kerberos Authentication (Delegation of Credentials)
Obtaining a Kerberos Ticket Granting Ticket
Configuring Client Authentication
Data Encryption
Configuring DB2-Specific Encryption
Configuring SSL Encryption
Non-Default Schemas for Catalog Methods
Choosing a DB2 Optimization Class
Reauthentication
Client Information for Connections
Workload Manager (WLM)
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
JTA Support
Large Object (LOB) Support
Batch Inserts and Updates
Parameter Metadata Support
Insert and Update Statements
Select Statements
Stored Procedures
ResultSet Metadata Support
Rowset Support
Auto-Generated Keys Support
Configuring Failover
Specifying Primary and Alternate Servers
Specifying Connection Retry
Failover Properties
Configuring Bulk Load
Informix Driver
Data Source and Driver Classes
Connection URL
J2EE Connector Architecture Resource Adapter Class
Connection Properties
AccountingInfo
AlternateServers
ApplicationName
BulkLoadBatchSize
CatalogOptions
ClientHostName
ClientUser
CodePageOverride
ConnectionRetryCount
ConnectionRetryDelay
ConvertNull
Database
DatabaseName
DBDate
FailoverGranularity
FailoverMode
FailoverPreconnect
FetchBufferSize
ImportStatementPool
InformixServer
InitializationString
InsensitiveResultSetBufferSize
JavaDoubleToString
JDBCBehavior
LoadBalancing
LoginTimeout
MaxPooledStatements
MaxStatements
Password
PortNumber
ProgramID
QueryTimeout
ResultSetMetaDataOptions
ServerName
SpyAttributes
UseDelimitedIdentifier
User
Performance Considerations
Data Types
Client Information for Connections
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
JTA Support
Parameter Metadata Support
Insert and Update Statements
Select Statements
Stored Procedures
ResultSet MetaData Support
Rowset Support
Blob and Clob Searches
Auto-Generated Keys Support
Configuring Failover
Specifying Primary and Alternate Servers
Specifying Connection Retry
Failover Properties
Configuring Bulk Load
MySQL Driver
Data Source and Driver Classes
Connection URL
J2EE Connector Architecture Resource Adapter Class
Connection Properties
AccountingInfo
AlternateServers
ApplicationName
BulkLoadBatchSize
CatalogOptions
ClientHostName
ClientUser
CodePageOverride
ConnectionRetryCount
ConnectionRetryDelay
ConvertNull
Database
DatabaseName
EncryptionMethod
FailoverGranularity
FailoverMode
FailoverPreconnect
HostNameInCertificate
ImportStatementPool
InitializationString
InOrderColumnAccess
InsensitiveResultSetBufferSize
InteractiveClient
JavaDoubleToString
JDBCBehavior
KeyPassword
KeyStore
KeyStorePassword
LoadBalancing
LobCommandSize
LoginTimeout
MaxPooledStatements
MaxStatements
Password
PortNumber
ProgramID
QueryTimeout
ResultSetMetaDataOptions
ServerName
SpyAttributes
TreatBinaryAsChar
TrustStore
TrustStorePassword
User
ValidateServerCertificate
Performance Considerations
Data Types
Data Encryption
Client Information for Connections
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
JTA Support
Large Object (LOB) Support
Parameter Metadata Support
Insert and Update Statements
Select Statements
Stored Procedures
ResultSet MetaData Support
Rowset Support
Auto-Generated Keys Support
Configuring Failover
Specifying Primary and Alternate Servers
Specifying Connection Retry
Failover Properties
Configuring Bulk Load
Oracle Driver
Data Source and Driver Classes
Connection URL
J2EE Connector Architecture Resource Adapter Class
Connection Properties
AccountingInfo
Action
AlternateServers
ApplicationName
AuthenticationMethod
BatchPerformanceWorkaround
BulkLoadBatchSize
BulkLoadOptions
CatalogOptions
ClientHostName
ClientID
ClientUser
CodePageOverride
CommitBehavior
ConnectionRetryCount
ConnectionRetryDelay
ConvertNull
Database
DatabaseName
DataIntegrityLevel
DataIntegrityTypes
EditionName
EnableBulkLoad
EnableCancelTimeout
EnableServerResultCache
EncryptionLevel
EncryptionMethod
EncryptionTypes
FailoverGranularity
FailoverMode
FailoverPreconnect
FetchTSWTZasTimestamp
HostNameInCertificate
ImportStatementPool
InitialColumnBufferSize
InitializationString
InsensitiveResultSetBufferSize
JavaDoubleToString
JDBCBehavior
KeyPassword
KeyStore
KeyStorePassword
LoadBalancing
LoadLibraryPath
LoginTimeout
MaxPooledStatements
MaxStatements
Module
NewPassword
Password
PortNumber
ProgramID
QueryTimeout
ReportRecycleBin
ResultSetMetaDataOptions
SDUSize
SendFloatParametersAsString
ServerName
ServerType
ServiceName
SID
SpyAttributes
StringParamsMustMatchCharColumns
SupportLinks
SysLoginRole
TNSNamesFile
TNSServerName
TrustStore
TrustStorePassword
User
ValidateServerCertificate
WireProtocolMode
Performance Considerations
Support for Oracle RAC
Using tnsnames.ora Files
Connecting to the Database
Configuring the tnsnames.ora File
Connecting to Oracle Instances Running in Restricted Mode
Data Types
Using Date/Time Data Types
Date/Time Session Parameters
TIMESTAMP Data Type
TIMESTAMP WITH LOCAL TIME ZONE Data Type
TIMESTAMP WITH TIME ZONE Data Type
Using Collection Data Types
VARRAY/TABLE
OBJECT
Returning and Inserting/Updating XML Data
Returning XML Data
Inserting/Updating XML Data
REF CURSOR Data Type
Authentication
Using the AuthenticationMethod Property
Configuring User ID/Password Authentication
Configuring Kerberos Authentication
Product Requirements
Configuring the Driver
Specifying User Credentials for Kerberos Authentication (Delegation of Credentials)
Obtaining a Kerberos Ticket Granting Ticket
Configuring NTLM Authentication
Product Requirements
Configuring the Driver
Configuring Client Authentication
Data Encryption and Data Integrity
SSL
Oracle Wallet
Oracle Advanced Security
Reauthentication
Client Information for Connections
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
JTA Support
Batch Inserts and Updates
Parameter Metadata Support
Insert and Update Statements
Select Statements
Stored Procedures
ResultSet MetaData Support
Executing Insert/Update/Delete Statements with a Returning Clause
Rowset Support
Configuring Failover
Specifying Primary and Alternate Servers
Specifying Connection Retry
Failover Properties
Configuring Bulk Load
PostgreSQL Driver
Data Source and Driver Classes
Connection URL
J2EE Connector Architecture Resource Adapter Class
Connection Properties
AccountingInfo
AlternateServers
ApplicationName
BulkLoadBatchSize
CatalogOptions
ClientHostName
ClientUser
ConnectionRetryCount
ConnectionRetryDelay
ConvertNull
Database
DatabaseName
EnableCancelTimeout
EncryptionMethod
HostNameInCertificate
ImportStatementPool
InitializationString
InsensitiveResultSetBufferSize
JavaDoubleToString
KeyPassword
KeyStore
KeyStorePassword
LoadBalancing
LoginTimeout
MaxLongVarcharSize
MaxNumericPrecision
MaxNumericScale
MaxPooledStatements
MaxStatements
MaxVarcharSize
Password
PortNumber
ProgramID
QueryTimeout
ResultSetMetaDataOptions
ServerName
SpyAttributes
SupportsCatalogs
TransactionErrorBehavior
TrustStore
TrustStorePassword
User
ValidateServerCertificate
VarcharClobThreshold
Performance Considerations
Data Types
Ref Cursors
Returning and Inserting/Updating XML Data
Returning XML Data
Inserting/Updating XML Data
Character Data
Binary Data
Data Encryption
Client Information for Connections
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
JTA Support
Large Object (LOB) Support
Parameter Metadata Support
Stored Procedure Results
ResultSet MetaData Support
Rowset Support
Configuring Failover
Specifying Primary and Alternate Servers
Specifying Connection Retry
Failover Properties
Configuring Bulk Load
Progress OpenEdge Driver
Data Source and Driver Classes
Connection URL
Connection Properties
AlternateServers
CatalogOptions
ConnectionRetryCount
ConnectionRetryDelay
ConvertNull
Database
DatabaseName
EncryptionMethod
FailoverGranularity
FailoverMode
FailoverPreconnect
HostNameInCertificate
ImportStatementPool
InitializationString
InsensitiveResultSetBufferSize
JavaDoubleToString
JDBCBehavior
LoadBalancing
LoginTimeout
MaxPooledStatements
MaxStatements
Password
PortNumber
QueryTimeout
ServerName
SpyAttributes
TrustStore
TrustStorePassword
User
ValidateServerCertificate
Performance Considerations
Data Types
Data Encryption
Client Information for Connections
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
JTA Support
Parameter Metadata Support
Insert and Update Statements
Select Statements
Stored Procedures
ResultSet MetaData Support
Rowset Support
Blob and Clob Searches
Auto-Generated Keys Support
Configuring Failover
Specifying Primary and Alternate Servers
Using the AlternateServers Property
Specifying Connection Retry
Connection Failover Properties
Microsoft SQL Server Driver
Data Source and Driver Classes
Connection URL
J2EE Connector Architecture Resource Adapter Class
Connection Properties
AccountingInfo
AlternateServers
AlwaysReportTriggerResults
ApplicationIntent
ApplicationName
AuthenticationMethod
BulkLoadBatchSize
BulkLoadOptions
CatalogOptions
ClientHostName
ClientUser
CodePageOverride
ConnectionRetryCount
ConnectionRetryDelay
ConvertNull
Database
DatabaseName
DateTimeInputParameterType
DateTimeOutputParameterType
DescribeParameters
DescribeInputParameters
DescribeOutputParameters
Domain
EnableBulkLoad
EnableCancelTimeout
EncryptionMethod
FailoverGranularity
FailoverMode
FailoverPreconnect
FetchTSWTZAsTimestamp
HostNameInCertificate
HostProcess
ImportStatementPool
InitializationString
InsensitiveResultSetBufferSize
JavaDoubleToString
JDBCBehavior
LoadBalancing
LoadLibraryPath
LoginTimeout
LongDataCacheSize
MaxPooledStatements
MaxStatements
NetAddress
PacketSize
Password
PortNumber
ProgramID
ProgramName
QueryTimeout
ReceiveStringParameterType
ResultSetMetaDataOptions
SelectMethod
SendStringParametersAsUnicode
ServerName
SnapshotSerializable
SpyAttributes
StringInputParameterType
StringOutputParameterType
SuppressConnectionWarnings
TransactionMode
TruncateFractionalSeconds
TrustStore
TrustStorePassword
User
UseServerSideUpdatableCursors
ValidateServerCertificate
WSID
XATransactionGroup
XMLDescribeType
Performance Considerations
Connecting to Named Instances
Data Types
Returning and Inserting/Updating XML Data
Returning XML Data
Character Data
Binary Data
Inserting/Updating XML Data
Character Data
Binary Data
Authentication
Using the AuthenticationMethod Property
Configuring SQL Server Authentication
Configuring Kerberos Authentication
Product Requirements
Configuring the Driver
Specifying User Credentials for Kerberos Authentication (Delegation of Credentials)
Obtaining a Kerberos Ticket Granting Ticket
Configuring NTLM Authentication
Product Requirements
Configuring the Driver: Not Specifying User Credentials (Windows Only)
Configuring the Driver: Specifying User Credentials
Data Encryption
Using SSL with Microsoft SQL Server
Configuring SSL Encryption
DML with Results (Microsoft SQL Server 2005 and Higher)
Reauthentication
Client Information for Connections
SQL Escape Sequences
Isolation Levels
Using the Snapshot Isolation Level (Microsoft SQL Server 2005 and Higher)
Using Scrollable Cursors
Server-Side Updatable Cursors
JTA Support: Installing Stored Procedures
Distributed Transaction Cleanup
Transaction Timeout
Explicit Transaction Cleanup
Large Object (LOB) Support
Batch Inserts and Updates
Parameter Metadata Support
Insert, Update, and Delete Statements
Select Statements
Stored Procedures
ResultSet MetaData Support
Rowset Support
Auto-Generated Keys Support
Null Values
Configuring Failover
Specifying Primary and Alternate Servers
Specifying Connection Retry
Failover Properties
Configuring Bulk Load
Sybase Driver
Data Source and Driver Classes
Connection URL
J2EE Connector Architecture Resource Adapter Class
Connection Properties
AccountingInfo
AlternateServers
ApplicationName
AuthenticationMethod
BatchPerformanceWorkaround
BulkLoadBatchSize
CatalogOptions
ClientHostName
ClientUser
CodePageOverride
ConnectionRetryCount
ConnectionRetryDelay
ConvertNull
Database
DatabaseName
EnableBulkLoad
EnableCancelTimeout
EncryptionMethod
ErrorBehavior
FailoverGranularity
FailoverMode
FailoverPreconnect
FetchTWFSasTime
HostNameInCertificate
ImportStatementPool
InitializationString
InsensitiveResultSetBufferSize
JavaDoubleToString
JDBCBehavior
LoadBalancing
LoginTimeout
LongDataCacheSize
MaxPooledStatements
MaxStatements
PacketSize
Password
PortNumber
PrepareMethod
ProgramID
QueryTimeout
ResultSetMetaDataOptions
SelectMethod
ServerName
ServicePrincipalName
SpyAttributes
TransactionMode
TrustStore
TrustStorePassword
UseAlternateProductInfo
User
ValidateServerCertificate
Performance Considerations
Data Types
Authentication
Using the AuthenticationMethod Property
Configuring User ID/Password Authentication
Configuring Kerberos Authentication
Product Requirements
Configuring the Driver
Specifying User Credentials for Kerberos Authentication (Delegation of Credentials)
Obtaining a Kerberos Ticket Granting Ticket
Data Encryption
Client Information for Connections
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
Large Object (LOB) Support
Batch Inserts and Updates
Parameter Metadata Support
ResultSet MetaData Support
Rowset Support
Auto-Generated Keys Support
Null Values
Configuring Failover
Specifying Primary and Alternate Servers
Specifying Connection Retry
Failover Properties
Configuring Bulk Load
JTA Support
DataDirect Connect XE Drivers
The Driver for Apache Hive
Data Source and Driver Classes
Connection URL
Connection Properties
AccountingInfo
ApplicationName
AuthenticationMethod
ClientHostName
ClientUser
ConnectionRetryCount
ConnectionRetryDelay
ConvertNull
Database
DatabaseName
EnableCancelTimeout
ImportStatementPool
InitializationString
InsensitiveResultSetBufferSize
JavaDoubleToString
LoginTimeout
MaxPooledStatements
MaxStatements
Password
PortNumber
ProgramID
QueryTimeout
RemoveColumnQualifiers
ServerName
ServicePrincipalName
SpyAttributes
StringDescribeType
TransactionMode
UseCurrentSchema
User
WireProtocolVersion
Performance Considerations
Data Types
Authentication
Using the AuthenticationMethod Property
Configuring User ID/Password Authentication
Configuring Kerberos Authentication
Product Requirements
Configuring the Driver
Specifying User Credentials for Kerberos Authentication (Delegation of Credentials)
Obtaining a Kerberos Ticket Granting Ticket
Views
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
JTA Support
Large Object (LOB) Support
Parameter Metadata Support
Insert and Update Statements
Select Statements
Stored Procedures
ResultSet Metadata Support
Rowset Support
Auto-Generated Keys Support
Restrictions for Apache Hive Functionality
Greenplum Driver
Data Source and Driver Classes
Connection URL
J2EE Connector Architecture Resource Adapter Class
Connection Properties
AccountingInfo
AlternateServers
ApplicationName
BulkLoadBatchSize
CatalogOptions
ClientHostName
ClientUser
ConnectionRetryCount
ConnectionRetryDelay
ConvertNull
Database
DatabaseName
EnableCancelTimeout
EncryptionMethod
HostNameInCertificate
ImportStatementPool
InitializationString
InsensitiveResultSetBufferSize
JavaDoubleToString
KeyPassword
KeyStore
KeyStorePassword
LoadBalancing
LoginTimeout
MaxLongVarcharSize
MaxNumericPrecision
MaxNumericScale
MaxPooledStatements
MaxStatements
MaxVarcharSize
Password
PortNumber
ProgramID
QueryTimeout
ResultSetMetaDataOptions
ServerName
SpyAttributes
SupportsCatalogs
TransactionErrorBehavior
TrustStore
TrustStorePassword
User
ValidateServerCertificate
VarcharClobThreshold
Performance Considerations
Data Types
Refcursors
Client Information for Connections
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
JTA Support
Large Object (LOB) Support
Parameter Metadata Support
Stored Procedure Results
ResultSet MetaData Support
Rowset Support
Configuring Failover
Specifying Primary and Alternate Servers
Specifying Connection Retry
Failover Properties
Configuring Bulk Load
Salesforce Driver
Data Source and Driver Classes
Version String Information
Connection URL
Connection Properties
BulkLoadAsync
BulkLoadBatchSize
BulkLoadConcurrencyMode
BulkLoadPollInterval
BulkLoadThreshold
CatalogOptions
ConfigOptions
ConnectionRetryCount
ConnectionRetryDelay
ConvertNull
CreateDB
Database
DatabaseName
EnableBulkLoad
FetchSize
ImportStatementPool
InitializationString
InsensitiveResultSetBufferSize
JavaDoubleToString
LogConfigFile
LoginTimeout
MaxPooledStatements
MaxStatements
Password
ProxyHost
ProxyPassword
ProxyPort
ProxyUser
ReadOnly
RefreshDirtyCache
RefreshSchema
SecurityToken
ServerName
StmtCallLimit
StmtCallLimitBehavior
TransactionMode
User
WSCompressData
WSFetchSize
WSRetryCount
WSTimeout
Performance Considerations
Data Types
Mapping Objects to Tables
Client-Side Caches
Creating a Cache
Modifying a Cache Definition
Disabling and Enabling a Cache
Refreshing Cache Data
Dropping a Cache
Caching MetaData
Catalog Tables
SYSTEM_CACHES Catalog Table
SYSTEM_CACHE_REFERENCES Catalog Table
SYSTEM_REMOTE_SESSIONS Catalog Table
SYSTEM_SESSIONS Catalog Table
Timeouts
Views and Remote/Local Tables
Using Identifiers
Database Configuration File
<Database>
<User>
<UseSchema>
<Schema>
<ConfigOptions>
<SessionOptions>
Mapping Objects to Tables
Reports
Connecting Through a Proxy Server
Data Encryption
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
JTA Support
Large Object (LOB) Support
Parameter Metadata Support
ResultSet MetaData Support
Rowset Support
Auto-Generated Keys Support
Configuring Bulk Load
Using This Guide
Configuring Bulk Load