Database connection validation

The database connection validation utility tests the connection to the WebSphere Commerce database and logs the connection test results. Use this information to troubleshoot database-related problems. The utility tests whether a connection can be established with the configured parameters of a WebSphere Commerce utility or process. The utility logs the validation results within either the log file of the utility that calls the connection validation utility to run, or within the log file for the validation utility when the utility is run as a stand-alone process.

The database connection validation utility, wcsDbValidation, is an ANT-based utility that you can use to help resolve common database-related problems. As a site administrator, you can use this utility to test the connection between WebSphere Commerce and only certain databases and drivers. By default, this utility is integrated with many WebSphere Commerce utilities, and you can integrate this utility to test the connection between other utilities and the WebSphere Commerce database.

The database validation connection utility supports all WebSphere Commerce configurations. You can run the utility to validate the following database connections:

Embedded. WebSphere Commerce supports only embedded connections. This utility also tests the Network Client, and ignores any class not found exception
Type 2 and Type 4 connections
Thin and Thick client connections
Native and Toolbox connections

The database connection validation utility logs and validates the system and database connections for a utility and includes the information within the log files for the utility. The utility does the validation before the utility performs the actual database operations, such as data load operations or schema changes. If the utility encounters problems during a database process, the utility provides information to help you troubleshoot the problem, such as identifying whether a parameter or connection is invalid. Environment information might not be included in a log file when an exception occurs while you run a utility. By using the information that is generated by the database connection validation utility, you can help identify any environment-related problems.

The utility tests the primary connection, which is obtained from the input JDBC URL. A primary database connection is the connection that is established with the defined parameters. If the validation utility is called during the process of another utility, the primary connection is the connection that is used by the caller utility. The utility also tests the connection through the alldbconnector utility. For more information about the alldbconnector utility, see Database connection acquisition for utilities and Ant tasks.

If the primary connection fails, the validation utility throws an exception. Then, the utility constructs secondary connections to test. If a secondary connection fails, the utility prints an error message and continues with the testing and validation process.

By default, you can use this validation utility to gather information about the establishment of a database connection to help you troubleshoot problems with the following processes:

Instance creation
When instance creation fails, the schema can be successfully created through SQL, but the data might not load into the database. Data is loaded into the database through the massload utility, which uses JDBC to establish a database connection. Instance creation can fail when the process cannot read from the correct directory. This problem can occur when the wrong WebSphere Commerce installation directory is specified or if you are logged on as the wrong user.
For example, it is possible that the schema might not create successfully after a database is created. When this error occurs, run the validation utility. The information from the validation utility generates within the createInstanceANT.log file before the base schema creates. You can locate this file within the following directory:
- WC_installdir/instances/instance_name/logs
- WC_installdir\instances\instance_name\logs
- WCDE_installdir\logs
Fix pack update
Similar to instance creation, fix pack updates can fail with the schema successfully created through SQL, but with the data not loaded into the database. This problem can occur when the wrong database user name is specified, or the database user password is expired.
The information from the validation utility generates within the updatedb-instance_name.log file, where instance_name is the name of your instance, such as demo. For instance, updatedb-demo.log. The log information generates before the fix pack schema creates or the data changes occur. You can locate this file within the following directory:
- WC_installdir/logs/update/update
- WC_installdir\logs\update\update
- WCDE_installdir\logs\update\update
Feature pack enablement
Feature pack enablement can fail due to similar causes as both instance creation and fix pack updates.
The information from the validation utility generates within the enablefeatureName_timestamp.log file, where featureName is the name of the feature that is being enabled, such as management-center. The time stamp of when the feature enablement runs is appended to the file name of the log. The log information generates before the feature pack schema creates. You can locate this file within the following directory:
- WC_installdir/instances/instance_name/logs
- WC_installdir\instances\instance_name\logs
- WCDE_installdir\logs
WebSphere Commerce utilities
Most WebSphere Commerce utilities, such as stagingprop utility, accept input parameters to establish a database connection. Some of these utilities have separate configuration files specific for each utility, but the connections for each utility are established in a similar way. The database connection validation utility can be used to provide system environment information and validate the parameters and configurations that can help you diagnose problems. Examples of these problems include:
- Whether the JDK level is 1.6, which is the required level.
- That the class paths that are specified include all of the required JAR files.
- Whether the JDBC driver is not specified or is incorrect. This information is typically retrieved from the createInstance.properties file.
- That the JDBC URL, database name, database user name, and other parameters are correct. For instance, if the database name changes, the configuration files might still specify the incorrect JDBC URL.
The log information generates within the log file for the specific utility.

If you are running the utility separately from any utility, the log information generates within the wcsDbValidation-instance_name.log. This log file generates within the following directory:

WC_installdir/instances/instance_name/logs
WC_installdir\instances\instance_name\logs
WCDE_installdir\logs

Within the log, the following information generates within the file:

System information, such as the operating system, user name, Java version, and WebSphere Commerce version
Database information, such as input parameters, JDBC drivers, JDBC URLs, and alternate drivers and URLs. The utility tests the JDBC driver with input parameters and prints the driver location that includes JAR files and class paths. The utility also tests the input JDBC URLs with the input parameters and prints connection information that includes the connection class, production information, and WebSphere Commerce schema level.

Information sources

The database connection validation utility retrieves database parameter information from the following files and in the following sequence:

instance_name.xml

This file, such as the demo.xml file can be located within the following directory:

WC_installdir/instances/instance_name/xml
WC_installdir\instances\instance_name\xml

Within this file, the database information can be included within code that resembles the following code:

<Database>
  <DB CreateDB="true" DBAName="db2admin"
  DBAPwd="xK36ck80s6GbQL+aVIOszg=="
  DBHost="schema03.torolab.ibm.com" DBMSName="DB2"
  DBNode="" DBServerPort="50000" DBUserID="wcs"
  DBUserPwd="xK36ck80s6GbQL+aVIOszg==" OraUserID=""
  OracleDataFile="" RemoteDB="false" RunDB2SG="true"
  ServiceName="" active="true" name="mall"/>
</Database>

wc-server.xml

In a WebSphere Commerce Developer environment, the wc-server.xml file is used instead of the instance_name.xml file. The wc-server.xml file can be located within the following directory:

workspace_dir\WC\xml\config

Within this file, the database information can be included within code that resembles the following code:

<Database>
  <DB CreateDB="true" DBAName="db2admin"
  DBAPwd="xK36ck80s6GbQL+aVIOszg=="
  DBHost="schema03.torolab.ibm.com" DBMSName="DB2"
  DBNode="" DBServerPort="50000" DBUserID="wcs"
  DBUserPwd="xK36ck80s6GbQL+aVIOszg==" OraUserID=""
  OracleDataFile="" RemoteDB="false" RunDB2SG="true"
  ServiceName="" active="true" name="mall"/>
</Database>

createInstances.properties

This file can be located within the following directory:

WC_installdir/instances/instance_name/properties
WC_installdir\instances\instance_name\properties
WCDE_installdir\properties

Within the file, the database information that the validation utility retrieves can be included in code that resembles the following code:

#########################################################################################
#					   									                                                       #
# Database properties  												                                       #
#					   															                                      		#
#########################################################################################

# Specifies whether you want this tool to create database (on DB2) or tablespace (on Oracle). (Accepted values are: true or false)
#---------------------------------------------------------------------------------
bCreateDB=true

# Do you want this tool to create schema? (Accepted values are: true or false)
#------------------------------------------------------
bCreateSchema=true

# Will this instance use a remote database? (Accepted values are: true or false)
#--------------------------------------------------------
isRemoteDB=false

# Database type (Accepted values are:  db2 or oracle)
#-----------------------------
dbType=db2

# WebSphere Application Server JDBC/Datasource JDBC type (Accepted values are: db2, oracle, db2_iseries_toolbox or db2_iseries_native)
#-----------------------------
WASJDBCDriverType=db2

# EJB jar type (Accepted values are: db2, oracle, os400, etc)
#-----------------------------
ejbType=db2

# DBMS specific sql file location (Accepted values are: db2, oracle or os400)
#---------------------------------------------------------------
sqldir=db2

# Database administrator ID
#---------------------------
dbaName=db2admin

# Database administrator password
#---------------------------------
dbaPassword=


# Commerce database name
#------------------------
dbName=mall

# Remote database server hostname
#---------------------------------
dbHostname=schema03.torolab.ibm.com

# Remote database server port
#-----------------------------
dbServerPort=50000

# Database node name to catalog the remote database on (only for DB2)
#-----------------------------------------------------------------
dbNodeName=

# Service name for Oracle database
#-----
serviceName=

# Datafile for schema (only for Oracle)
#-----------------------------------
dataFilePath=

# Tablespace name for Commerce schema (only for Oracle)
#---------------------------------------------------
tablespaceName=WCTBLSPC

# Temporary tablespace name for Commerce schema (only for Oracle)
#-------------------------------------------------------------
tempTablespaceName=temp

# Database user name (schema owner)
#-----------------------------------
dbUserName=WCS

# Schema name (schema owner)
# uppercase version of dbUserName
#-----------------------------------
schemaName=WCS


# Database user password
#------------------------
dbUserPassword=

# Database user name (schema owner)
#-----------------------------------
oracleUserName=

# Do you want to set this database as the active Commerce database? (Accepted values are: true or false)
#--------------------------------------------------------------------------------
dbActive=true

# Does the database already exist? (Accepted values are: true or false)
#---------------------------------------------
dbExist=true

# Massloader method (DEFAULT:sqlimport)
# Accepted values are: load | import | sqlimport| createonly | loadonly | delete
#----------------------------------
loadmethod=sqlimport

# IDResolve method (DEFAULT: mixed)
# Accepted values are: load | update | mixed
#---------------------------
idResolveMethod=mixed

# Absolute path of the logger config file for Massloader
# system property com.ibm.wca.logging.configFile
#------------------------------------
loggerconfigfile=C:/WebSphere/CommerceServer70/instances/demo/xml/loader/WCALoggerConfig.xml

# Absolute path of the error directory for Massloader
# system property com.ibm.wcm.ErrorReporterDir
#------------------------------------
errordirectory=C:/WebSphere/CommerceServer70/instances/demo/logs

# Massloader customizer
# one from <WC>/properties dir
#-----------------------
mlcustomizer=

# IDResolver customizer
# one from <WC>/properties dir
# Leave it empty for DB2
# OracleConnectionCustomizer.properties for Oracle
#-----------------------
idcustomizer=

# JDBC driver
#-------------
jdbcDriver=com.ibm.db2.jcc.DB2Driver

# JDBC URL
#----------
jdbcURL=jdbc:db2://schema03.torolab.ibm.com:50000/mall

# Trigger delimiter
# Delimiter for DB2 is #
# Delimiter for Oracle is /
#--------------------------
triggerDelimiter=#

Where

dbType

The database type. For example, DB2, Oracle, or Derby

dbaName

The name or user ID of the database administrator user.

dbaPassword

The password for the database administrative user.

dbName

The database name to be connected.

dbUserName

DB2 The name of the user that is connecting to the database.

Oracle The user ID connecting to the database.

dbUserPassword

The password for the user that is connecting to the database.

jdbcDriver

The JDBC driver to be used. The default value depends on the database type. For example, if you are using DB2, the JDBC driver might be com.ibm.db2.jcc.DB2Driver. If you are using Oracle, the JDBC driver might be oracle.jdbc.OracleDriver.

jdbcURL

The JDBC URL to be used.

Configuration XML files for WebSphere Commerce utilities

The configuration files that the validation utility retrieves information from include:

createBaseSchema.xml

This file contains instance creation information and can be found within the following directory:

WC_installdir/config/deployment/xml
WC_installdir
WCDE_installdir

configureDatabaseFixpack.xml

The file contains information for the updatedb utility and can be found within the following directory:

WC_installdir/components/Fixpack/xml
WC_installdir
WCDE_installdir

enableFeature.xml

The file contains feature pack enablement information and can be found within the following directory:

WC_installdir/components/common/xml
WC_installdir
WCDE_installdir

databaseValidation.xml

The file contains information for the database connection validation utility. This file can be found within the following directory:

WC_installdir/components/common/xml
WC_installdir
WCDE_installdir

Within these files, you can find database information with code sections that resemble the following code:

<taskdef name="WcsDbValidation" classname="com.ibm.commerce.config.ant.WcsDbValidation" />
...
..
<WcsDbValidation
validation="3"
 newinstance="false"
propList="wcUserInstallDir[;]instanceName[;]dbUserName[;]dbUserPassword[;]schemaName[;]dbType[;]jdbcDriver[;]jdbcURL[;]dbName[;]dbaName[;]dbaPassword[;]loggerconfigfile[;]errordirectory"
valueList="${WCUserInstallDir}[;]${instanceName}[;]${dbUserName}[;]${dbUserPassword}[;]${schemaName}[;]${dbType}[;]${jdbcDriver}[;]${jdbcURL}[;]${dbName}[;]${dbaName}[;]${dbaPassword}[;]${loggerconfigfile}[;]${errordirectory}" />

Example

When you are applying a fix pack update, the loading of data into the WebSphere Commerce database with the updatedb utility fails. When the failure occurs, the updatedb utility log file indicates that the load process failed, but does not provide information about the changes that are required to complete the loading process. For example, the massload process that runs as part of the updatedb utility process generates 2 log files, message.txt and trace.txt. These log files can include partial database information, such as the JDBC URL and database user name that is specified. The log files do not include information about whether the specified JDBC driver is correct, or if any JAR files are missing in the classpath.

The message.txt log file generates for the massload process that runs as part of the updatedb utility. This log file can include information that resembles the following code:

2011-04-28 12:00:00.365, <main>, RegisterInstance::generateDatabaseNode, S1
	CWXAC5648E: dbaHomeDir is missing from the properties file.

2011-04-28 12:00:00.396, <main>, RegisterInstance::generateDatabaseNode, S1
	CWXAC5648E: dbUserHomeDir is missing from the properties file.

2011-04-28 12:17:51.584, <main>, RegisterInstance::generateDatabaseNode, S1
	CWXAC5648E: dbaHomeDir is missing from the properties file.

2011-04-28 12:17:51.584, <main>, RegisterInstance::generateDatabaseNode, S1
	CWXAC5648E: dbUserHomeDir is missing from the properties file.

The trace.txt file generates for the massload process that runs as part of the updatedb utility. This file includes some database connection information, which can resemble the following code:

2011-04-28 11:51:02.146, <Thread-66>, com.ibm.wca.IdResGen.IdResolve::setUpJDBCData
	Trace: DBDriver name is : com.ibm.db2.jcc.DB2Driver.

2011-04-28 11:51:02.146, <Thread-66>, com.ibm.wca.IdResGen.IdResolve::obtainConnection
	Trace: Obtaining new database connection.

2011-04-28 11:51:02.146, <Thread-66>, com.ibm.wca.IdResGen.IdResolve::obtainConnection
	Trace: DB User name : wcs

2011-04-28 11:51:02.146, <Thread-66>, com.ibm.wca.IdResGen.IdResolve::obtainConnection
	Trace: DBURL is  : jdbc:db2://70rtbvt5:50000/MALL.

If the wcsDbValidation database connection validation utility runs, the log information that is generated by the utility includes more database connection information to help troubleshoot the updatedb utility failure. With the validation utility log information you can:

Ensure that the configuration parameters are valid and can be used to establish a database connection. If a connection is not established, you can change the parameters in the configuration files and the test whether the new parameters can establish a connection.
Compare the utility log information with the log information from other utilities to identify if different parameters and connections should be used.
Determine whether a different connection type can be successful. If different connections can be successful, you can configure the utility to use one of the different connection types.