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 for a WebSphere Commerce utility or process with the configured parameters.

The connection validation utility can be called by another utility or the utility can be run as a stand-alone utility. When another utility calls the connection validation utility, the connection validation utility logs the validation results within the log file of the utility that calls the connection validation utility. When you run the connection validation utility as a stand-alone utility, the utility logs the results within the log file for the validation utility.

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 certain databases and drivers. By default, this utility is integrated with many WebSphere Commerce utilities. 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 types of 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
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.7, 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.log file. If you specify your instance name when you run the utility, the instance name is included in a log file that includes the instance name. The 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, the user name, the Java version, and the 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 AlternateHosts="" AlternatePorts="" CreateDB="true"
   DBAName="db2admin" DBAPwd="RoCZGKmg0L+xSMSTsnoHUw=="
   DBHost="myhost.mycompany.com" DBMSName="DB2"
   DBNode="" DBServerPort="50000" DBUserID="wcs"
   DBUserPwd="IihACl5kbaY=" OraUserID=""
   OracleDataFile="" PureScale="false" 
   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" DBAHomeDir="" DBAName="App"
   DBAPwd="xK36ck80s6GbQL+aVIOszg==" DBHost=""
   DBMSName="DB2J" DBNode="" DBServerPort=""
   DBUserHomeDir="" DBUserID="APP"
   DBUserPwd="xK36ck80s6GbQL+aVIOszg==" OraUserID=""
   RemoteDB="false" RunDB2SG="false" ServiceName=""
   StagingEnable="false" active="true" name="WCDE_installdir\db\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)
# If set to false, then an existing database will be used.
#---------------------------------------------------------------------------------
bCreateDB=true

# Do you want this tool to create schema? (Accepted values are: true or false)
# If set to false, then an existing schema will be used, and implies that bCreateDB must also be 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). For IBM System i databases choose 'db2'.
#-----------------------------
dbType=db2

# WebSphere Application Server JDBC/Datasource JDBC type (Accepted values are: db2, oracle, db2_iseries_toolbox or db2_iseries_native)
# Specify the type of connection required to establish a connection to the client side of the database
#-----------------------------
WASJDBCDriverType=db2

# EJB jar type (Accepted values are: db2, oracle, os400, etc)
# Specify the type of connection required to establish a connection to the server side of the database
#-----------------------------
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=


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

# Remote database server hostname
# The host name (fully qualified if possible) of the machine that has the database
#---------------------------------
dbHostname= 

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

# Database node name to catalog the remote database on (only for DB2)
# Required when using remote database, otherwise leave this field blank
#-----------------------------------------------------------------
dbNodeName=

# Service name for Oracle database
# Required when using Oracle
#-----
serviceName=

# Datafile for schema (only for Oracle)
# Required when using Oracle, otherwise leave the field blank
#-----------------------------------
dataFilePath=

# Tablespace name for Commerce schema (only for Oracle)
# Required when using Oracle, otherwise leave the field blank
#---------------------------------------------------
tablespaceName= 

# Temporary tablespace name for Commerce schema (only for Oracle)
# Required when using Oracle, otherwise leave the field blank
#-------------------------------------------------------------
tempTablespaceName=temp

# Database user name (schema owner)
# ID for the database user.  Only english word characters and numbers (a-z, 0-9) can be used.
#-----------------------------------
dbUserName=WCS

# Schema name (schema owner)
# This Must be exactly the uppercase version of dbUserName that was specified above
#-----------------------------------
schemaName=WCS


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

# Database user name (schema owner)
# Required when using Oracle, otherwise leave the field blank
#-----------------------------------
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)
# Used only on IBM System i (iSeries, AS/400).
# Indicates whether the database schema exists prior to instance creation.
# set to 'false' On Windows, AIX, and Linux
#---------------------------------------------
dbExist=false

# Massloader method (suggested:sqlimport)
# Accepted values are: load | import | sqlimport| createonly | loadonly | delete
#
# 'load' method uses the native database loading functions from the database vendor
# 'import' method uses native database import or update methods
# 'sqlimport' method uses JDBC calls to insert and update data for local/remote databases
# 'loadonly' method used to load mass load data files that were created using the createonly method
# 'delete' method is used to delete data that is in the input XML document from the database
#----------------------------------
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:/WC_installdir/instances/demo/xml/loader/WCALoggerConfig.xml

# Absolute path of the error directory for Massloader
# system property com.ibm.wcm.ErrorReporterDir
#------------------------------------
errordirectory=WC_installdir/instances/instance_name/logs

# Massloader customizer
# one from WC_installdir/properties dir
#-----------------------
mlcustomizer=

# IDResolver customizer
# one from WC_installdir/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://hostname:dbServerPort/dbName

# 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, which includes the following files.

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

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. The code is split across multiple lines for readability.


<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"/>