Database connection validation
- 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.
- 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 utilitiesMost 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.
- WC_installdir/instances/instance_name/logs
- WC_installdir\instances\instance_name\logs
- WCDE_installdir\logs
- 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
- 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
<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
Where######################################################################################### # # # 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=#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
-
The name of the user that is connecting to the database.
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
<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.
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.
- 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.