Troubleshooting: Database connectivity for DB2 databases

You can encounter database connectivity issues when you are connecting to a DB2 database to create instances, apply fix packs, or when you run many WebSphere Commerce utilities. To resolve these connectivity issues, ensure that you validate your configured database connection settings and ensure that you are using the correct JDBC driver configuration. Review the following information about connecting to DB2 databases, including common connectivity errors, to help you validate your settings and troubleshoot problems.

Run the WebSphere Commerce database connection validation utility

If you encounter errors when a WebSphere Commerce utility or process that involves database-related operations runs, use the database connection validation utility to ensure that a database connection established. By default, this validation utility runs as part of the process for some utilities and processes. You can run this utility by itself to validate that a database connection can be properly established with the parameters that are configured for your WebSphere Commerce instance.

When the utility completes running, a detailed report is generated that indicates whether a successful database connection can be established with the specified connection parameters. The report also indicates whether any errors occurred. For more information about this utility, see Running the database connection validation utility.

When you run the validation utility from a command-line utility, specify your database connection parameters in the command syntax. When you run the utility and want to include your connection parameters in the command, use the following syntax: 
wcsDbValidation.sh -jdbcURL jdbc_url -jdbcDriver com.ibm.db2.jcc.DB2Driver -dbUserName user -dbUserPassword password
For example, the following command includes a JDBC URL and identifies the database user and password:
wcsDbValidation.sh -jdbcURL jdbc:db2://hostname:50000/mall -jdbcDriver com.ibm.db2.jcc.DB2Driver -dbUserName dbusr -dbUserPassword password

Validate the DB2 client environment access

Ensure that you can connect to your DB2 client environment from the WebSphere Commerce non-root user (wasuser) profile. You can use the DB2 client to complete administrative tasks and to provide Java JDBC driver JAR files for WebSphere Commerce utilities to access for connecting to the database.

When your profile access is configured, you can connect to your DB2 database by using your database name. If you do not include your database user name and password, DB2 authenticates your database access by using your operating system user credentials. The command to connect to the DB2 client can resemble the following command, which displays the resulting connection information for an environment that uses a Linux operating system:
[wasuser@myhostname ~]$ db2 connect to mall

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.0.5
 SQL authorization ID   = WASUSER
 Local database alias   = MALL
LinuxIf you cannot access the client environment, include the following DB2 instance owner user profile into the WebSphere Commerce non-root user profile:
. /home/db2inst1/.profile
Where db2inst1 is the non-root user who owns the DB2 installation, and home/db2inst1 is the home directory of the non-root instance owner. This profile configuration is typically completed during WebSphere Commerce installation.
To ensure that you connect to the correct client, DB2 maintains a registry of local and remote databases. This registry is used to map a database name, such as "mall", to the actual database on a remote server. DB2 uses this mapping to connect you to the DB2 client for your database. To view the registry of available local and remote databases, use the following DB2 commands:
  • LIST NODE DIRECTORY command. This command shows the lists of registered nodes. From these nodes, DB2 can find the host name and port number where your database is hosted.
  • LIST DATABASE DIRECTORY command. This command shows the list of databases and the registered nodes that the databases are within. With the node identified, you can look up the list of registered nodes to find the host name and port number information. When you run this command, your command and resulting database list can be structured similar to the following command and database directory information result:
[wasuser@myhostname bin]$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = MALL
 Database name                        = MALL
 Node name                            = myhostname
 Database release level               = 10.5.0.5
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Validate the database connection acquisition framework configuration

Most WebSphere Commerce utilities that require a database connection are updated to connect to the database through the database connection acquisition framework. This framework is a unified database connection method that ensures that each utility and Ant task can reference a single class to configure the acquisition of a database connection, regardless of the JDBC driver that a database uses.

For example, to establish a connection when you run the database cleanup (dbclean) utility and use the database connection acquisition framework, your command can resemble the following command:
./dbclean.sh -dbtype db2 -db mall -object cacheivl -type obsolete -days 1 
-instancexml WC_installdir/instances/instance_name/xml/instance_name.xml -dbuser wcs -dbpasswd password
This command requires only the database name "mall" to identify the database and the database user name "wcs" and password "password" to establish a connection. Typically when you include only the database name as a parameter, you are indicating that the utility is to use a JDBC type 2 driver to connect to the database. With this framework however, DB2 APIs are used to resolve the host name and port number connection parameters for use with a JDBC type 4 driver based on the specified database name. The utility then establishes a connection by using the type 4 driver.

To resolve connectivity issues that you encounter when your utilities use the database connection acquisition framework, ensure that the connection settings in the alldbconnector.xml configuration file are correct. When you are reviewing your database connection acquisition configuration, check for any configured overrides that can be causing your connectivity problem. By including overrides, you can configure how utility is to establish a connection, regardless of the specified input parameters. For example, when a user enters a command to connect to a database that is called "mall", an override can configure the utility to always connect to the database "mall2" instead. For more information about the settings and overrides in this file, see Database connection acquisition for utilities and Ant tasks.

Note: When your utilities use this framework, you can still use the actual JCC JDBC type 4 connection URL in the parameters when you run a utility. For example, the following command includes the URL in the database parameter for the database cleanup utility command: 
./dbclean.sh -dbtype db2 -db jdbc:db2://myhostname:50000/MALL -object cacheivl -type obsolete -days 1 
-instancexml WC_installdir/instances/instance_name/xml/instance_name.xml -dbuser wcs -dbpasswd passw0rd
By using the JDBC URL you can include more connection properties to help you troubleshoot connectivity problems. For example, you can include properties to specify failover options or to enable JCC tracing. The following parameter demonstrates how to enable the JCC trace with the JDBC URL when you run a utility. 
-db "jdbc:db2://myhostname:50000/MALL:traceFile=/tmp/jdbc.trace;traceLevel=-1;"

Validate that a database connection can be established with the JDBC type 4 and type 2 drivers

WebSphere Commerce supports the use of both the JDBC type 2 (thick) driver and the JDBC type 4 (thin) driver for connecting to a database. Ensure that you can establish a connection with both types of driver.

WebSphere Commerce uses the db2jcc4.jar DB2 JCC JDBC type 4 driver for the DB2 UDB database. This type 4 driver connection type is an upgrade from the type 2 driver and provides improved performance and a richer set of functions for creating database connections. This upgrade includes both the runtime data source connections and the utilities connections, such as staging utilities, loading utilities, dbclean, and acpload.

The type 2 connection type database name syntax is deprecated for most utility command interfaces. For example, the syntax of the following database names in the sourcedb and destdb parameters are deprecated:
stagingprop.sh –dbtype DB2 - sourcedb stagingDBName –destdb productionDBName
Instead, use the following type 4 connection type database name syntax when you run WebSphere Commerce utilities: 
stagingprop.sh –dbtype DB2 - sourcedb host:port/stagingDBName –destdb host:port/productionDBName
The type 4 (thin) driver connection is a pure Java implementation of the DB2 client and does not interact or use your locally installed DB2 client. Since this connection type does not access the database directory for your client, ensure that you specify the host name, port number, and database name parameters that are needed to locate the database.

Although you can still use the type 2 connection type database name syntax, this syntax is converted internally to type 4 syntax and a type 4 database connection is created. This naming conversion is completed by using the DB2Administrator class that is provided by the DB2 JCC driver. To avoid a potential performance impact due to the conversion, use the type 4 connection type database name syntax.

If you choose to use the type 2 connection type database name, ensure that the database is cataloged properly on the WebSphere Commerce node. The DB2Adminstrator class requires the DB2 catalog information to convert the host name and port number from the type 2 connection type name syntax.

Validate the WebSphere Application Server database connection

Ensure that the database connection parameters in WebSphere Application Server are correct.
  1. In the WebSphere Application Server administration console, expand Resources > JDBC > Data Sources. Review and verify the type 4 driver connection type settings.
    Common and required data source properties for type 4 driver
  2. Review and verify your user name and password settings that are associated with the connection type settings. The user name and password settings are listed in the JAAS - J2C authentication record that is associated with the selected data source.
Notes:
  • If you are working within an authoring environment, you can also see a data source for publishing data. This data source typically has the production database connection parameters for publishing data. Review and verify the connection settings for this data source.
  • If you encounter database connectivity issues on a clustered node, ensure that the Java WebSphere Application Server node is running under the non-root user ID profile.

Common connectivity errors

  • The JDBC driver class cannot be found.
    If you use the JCC JDBC type 4 driver, you can encounter a connectivity error if Java cannot find the db2jcc4.jar JAR file for the driver. Java needs to find the JAR file to use the com.ibm.db2.jcc.DB2Driver driver class for the type 4 driver. This error can resemble the following error message, which is generated in the log file from the WebSphere Commerce database validation utility:
    [WcsDbValidation] Connection Testing:
[WcsDbValidation]   Test with supplied jdbcDriver
[WcsDbValidation]   jdbcDriver = com.ibm.db2.jcc.DB2Driver not found.
[WcsDbValidation]     Class not found : com.ibm.db2.jcc.DB2Driver
[WcsDbValidation] java.lang.ClassNotFoundException: com.ibm.db2.jcc.DB2Driver
[WcsDbValidation]       at java.lang.Class.forName(Class.java:182)
[WcsDbValidation]       at com.ibm.commerce.config.ant.WcsDbValidation.testJdbcDriverClass(WcsDbValidation.java:665)
[WcsDbValidation]       at com.ibm.commerce.config.ant.WcsDbValidation.logDatabaseProperties(WcsDbValidation.java:457)
[WcsDbValidation]       at com.ibm.commerce.config.ant.WcsDbValidation.execute(WcsDbValidation.java:236)
    The JAR file is available with the DB2 client under the Java directory, which is expected to be the DB2_installdir/java directory. You can encounter this error when the JAR file is not in the expected directory. For example, if the DB2 client is not installed in the location that was specified during WebSphere Commerce installation. Review the WC_installdir/xml/product.xml file to identify the expected installation location for the DB2client. Ensure that the JAR file is in the expected directory.
  • The JDBC driver class can be found, but a connection cannot be established.
    You can encounter different errors when a connection cannot be established by using the JCC JDBC type 4 driver. To troubleshoot these errors, a warning or error code and an SQL state message code are typically provided in the error message when an attempt to connect to the database fails. These codes map to a particular message that explains the warning, error, or SQL state. The error message that includes the codes can typically resemble the following message, which identifies a failed connection that is caused by an invalid user ID or password: 
    BUILD FAILED
WC_installdir/components/common/xml/databaseValidation.xml:20: com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException: 
[jcc][t4][2013][11249][4.16.53] Connection authorization failure occurred.  
Reason: User ID or Password invalid. ERRORCODE=-4214, SQLSTATE=28000
    • Warning codes for JDBC driver issues are in the ranges +4200 to +4299 and from +4450 to +4499.
    • Error codes for JDBC driver issues are in the ranges -4200 to -4299 and from -4450 to -4499.
    You can also retrieve the message for a SQLSTATE code from the DB2 command-line utility, which can resemble the following command:
    [wasuser@myhostname bin]$ db2 ? 28000

SQLSTATE 28000: Authorization name is invalid.
  • A connection to the database server cannot be established
    When WebSphere Commerce cannot establish a connection to the database server, you can see an error message that is similar to the following sample message:
    BUILD FAILED
WC_installdir/components/common/xml/databaseValidation.xml:20: com.ibm.db2.jcc.am.SqlNonTransientConnectionException: 
DB2 SQL Error: SQLCODE=-1060, SQLSTATE=08004, SQLERRMC=WCS, DRIVER=4.16.53
    These error messages can include an SQLCODE message code. This message code maps to a detailed message that can help you troubleshoot the error. For example, the code in the preceding sample error message, SQLCODE=-1060, maps to the following error message: 
    SQL1060N  User "<authorization-ID>" does not have the CONNECT privilege
    Where authorizationID is the ID of the user that is attempting to connect to the database. This ID value is also the value for the SQLERRMC message code, 'WCS', in the preceding sample error message.
    You can also retrieve the message for a SQLCODE code from the DB2 command-line utility, which can provide a possible user response to resolve the error. For example, the following command provides the explanation of possible user response for an error that generates the SQLCODE=-1060 message code:
    [wasuser@hstp bin]$ db2 ? sql1060
SQL1060N  User "<authorization-ID>" does not have the CONNECT privilege.

Explanation:
The specified authorization ID does not have the CONNECT privilege to
access the database. The CONNECT privilege must be granted before the
user can connect to a database. This error is also returned when a
switch user request is made with a user ID allowed on the trusted
connection but that user ID does not hold CONNECT privilege on the
database. The connection is put in an unconnected state.
...

User response:
Contact the system administrator or database administrator for the
database and request a GRANT CONNECT for the authorization ID. Resubmit
the command.
...