Database connection acquisition for utilities and Ant tasks

WebSphere Commerce utilities and Ant tasks are updated to reference a single class to configure the acquisition of a database connection. This unified database connection method ensures that each utility and Ant task can reference this class to acquire a database connection, regardless of the JDBC driver that a database uses. You do not need to complete any further action before these utilities and processes begin to use the AllDBConnector class to help acquire a database connection.

• CDFGenerator utility
• Configuration Manager
• Data Load utility
• Database Cleanup utility command script
• encryptChallengeAnswer utility
• Export Marketing Statistics utility
• Import Marketing Statistics utility
• Propagating the search index
• Running the instance creation command
• massload utility (Server environment)
• Migration
• MigrateEncryptedInfo utility
• migrateSearchFacet utility
• SegmentCopy
• setdbtype utility
• seourlkeywordgen utility
• stagingcopy utility
• stagingprop utility
• updatedb utility

By default, we can specify the connection parameters for a database from the command line, or the utility can construct the parameters. With this unified connection method, these options for specifying the database for use with a utility are not modified. We can still log all of the database connections that are made by a utility. To set where to create the log files, use the logoutput element as a subelement of the database-type configuration. With this logging setup, the log file records the database specifications that are input to establish a database connection for a utility. Once a set of input parameters are collected, we can review the specified parameters and potentially configure overrides to control how utilities connect to the database. For instance, we can override the database specification to ensure that utilities only use a specific JDBC URL.

(Oracle) We can configure the AllDBConnector class to use an Oracle wallet to authenticate user credentials for connecting to an Oracle database. By configuring the AllDBConnector class to authenticate users with an Oracle wallet, we can make the database connection process for WebSphere Commerce utilities more secure. For more information about this configuration, see Configure the Oracle database connection for utilities to authenticate users with Oracle Wallet.

Database connection acquisition configuration

The configuration properties for this unified database connection method are specified within following file:

• (AIX) (Linux) (IBM i) WC_installdir/xml/config/alldbconnector.xml

• (Windows) WC_installdir/xml\config\alldbconnector.xml

• (Developer) WCDE_installdir\xml\config\alldbconnector.xml

To review the XML schema to help you understand how utilities or Ant tasks establish a database connection, go to the following path and open the file for review:

• (AIX) (Linux) (IBM i) WC_installdir/xml/config/alldbconnector.xsd

• (Windows) WC_installdir/xml\config\alldbconnector.xsd

• (Developer) WCDE_installdir\xml\config\alldbconnector.xsd

The database connection configuration properties are set as name-value pair elements within the alldbconnector.xml XML configuration file. These properties are set as both global properties and as part of the specific database type configurations. When a database connection is established, the connection uses the configuration settings for the specific database input identifier. If no configuration for that identifier exists, the connection uses the global configuration properties. The following code provides an outline of the configuration XML file schema:

<alldbconnector>
  <db2 />
  <oracle />
  <derby />
  <iseries />
  <iseries_toolbox />
</alldbconnector>

To change how utilities acquire a database connection, configure the properties within the configuration XML file. For each of the 5 supported database types, the following elements can be included within each database type element:

<drivertype> (0 or 1 time) (only under the <oracle> element)
<jdbcdriver> (0 or 1 time)
<jdbcurl> (0 or 1 time)
<logoutput> (0 or 1 time)
<property> (0 or more times)
<override> (0 or more times)

Where:

(Oracle) drivertype

(Oracle) The Oracle driver-type that a utility uses to establish a database connection. A utility uses this configuration element when the input specification of a database is in the following format, server:port/service Or server:port:sid Setting this element controls whether the utility uses the thin or oci Oracle driver. Utilities use this element only when the element is specified within the <oracle> element. The drivertype element does not support the inclusion of a subelement. To configure this element, use the following element attributes:

value

Required. The type of Oracle driver class to use. Default is 'thin'. The other accepted value is 'oci'.

enabled

Optional. Enables the drivertype element. We can set one for the following values for this element:

yes

Enables the element. The default value.

no

Disables the element.

jdbcdriver

The driver class that a utility uses to establish a database connection. Use this element to specify a different driver class for a utility to use for establishing a database connection. The jdbcdriver element does not support the inclusion of a subelement. To configure this element, use the following element attributes:

value

Required. The fully qualified name of the JDBC driver. Do not include the .class suffix in this name.

enabled

Optional. Enables the jdbcdriver element. We can set one for the following values for this element:

yes

Enables the element. The default value.

no

Disables the element.

jdbcurl

The JDBC URL that a utility uses to acquire a database connection. The jdbcurl element does not support the inclusion of a subelement. Do not use this element as a direct subelement of a database type element, such as <oracle> or <db2>. If you do use this element as a subelement at this level, the element forces all database connections to connect to the same database. This behavior might not be the correct behavior for all utilities, such as the stagingprop utility. To use this element, include the element as a subelement within an override element. To configure this element, use the following element attributes:

value

Required. The JDBC URL path that a utility uses to acquire a database connection.

enabled

Optional. Enables the jdbcurl element. We can set one for the following values for this element:

yes

Enables the element. The default value.

no

Disables the element.

The following code snippet configures the jdbcurl element. This configuration ensures that all utilities on a DB2 for Linux, UNIX, and Windows database connects to the database 'mall' on the 'localhost' server.

<alldbconnector>
  <db2>
    <property name="currentSchema" value="MYSCHEMA" />
    <property name="currentQueryOptimization" value="0" enabled="no" />
    <logoutput value="standardout" />
    <jdbcurl value="jdbc:db2://localhost:50000/mall"; />
  </db2>
  <oracle />
  <derby />
  <iseries />
  <iseries_toolbox />
</alldbconnector>

logoutput

Specifies an output stream where all diagnostics logged by the class can be output into a log file. The logoutput element does not support the inclusion of a subelement. To configure this element, use the following element attributes:

value

Required. The path where a utility creates the diagnostics log. The accepted values for this element are stdout, standardout, or an absolute file path. Any non-absolute paths cause the utility to ignore the logoutput element. The following code snippets demonstrate the setting of an absolute path for the logoutput element:

<!-- for windows -->
<logoutput value="f:\some_directory\db2-connection-diagnostics.log" enabled="no" />

<!-- for non-windows -->
<logoutput value="/some_directory/db2-connection-diagnostics.log" enabled="no" />

enabled

Optional. Enables the logoutput element. We can set one for the following values for this element:

yes

Enables the element. The default value.

no

Disables the element.

The following code snippet demonstrates the configuration of the logoutput element to have a value set to standardout. The value standardout ensures that all diagnostics for any connection that is established on a DB2 for Linux, UNIX, and Windows database is printed on the standard output stream.

<alldbconnector>
  <db2>
    <property name="currentSchema" value="MYSCHEMA" />
    <property name="currentQueryOptimization" value="0" enabled="no" />
    <logoutput value="standardout" />
  </db2>
  <oracle />
  <derby />
  <iseries />
  <iseries_toolbox />
</alldbconnector>

property

Use this element to specify more database connection name-value pair configuration properties before the caller utility acquires the connection properties. If the enabled attribute is not set to yes, the caller utility ignores the property. The property element does not support the inclusion of a subelement. To configure this element, use the following element attributes:

name

Required. The name of the property.

value

Required. The corresponding value for the property.

type

Optional. The data type of the property value. Default is java.lang.String. Other acceptable values are java.lang.Integer and java.lang.Boolean.

enabled

Optional. Enables the property element. We can set either of the following values for this element:

yes

Enables the element. The default value.

no

Disables the element.

scope

Indicates whether the property is system property or connection property.

system

The property is a system property.

connection

The property is a connection property. The default value.

(Oracle) For example, to configure the AllDBConnector class to authenticate users with an Oracle wallet when we are using the thin Oracle driver-type, set a property oracle.net.tns.admin as a system property. By setting this property as a system property, the Oracle thin driver can retrieve TNS entries from the tnsnames.ora configuration file for the database client. The AllDBConnector class and the Oracle wallet needs these entries to authenticate users and establish a database connection.

The following code snippet demonstrates the configuration of two property elements. These properties ensure that any connections to a DB2 for Linux, UNIX, and Windows database that a utility requests has the value MYSCHEMA set for the property currentSchema. The property currentQueryOptimization is not enabled, so the caller utility ignores this property.

<alldbconnector>
  <db2>
    <property name="currentSchema" value="MYSCHEMA" />
    <property name="currentQueryOptimization" value="0" enabled="no" />
  </db2>
  <oracle />
  <derby />
  <iseries />
  <iseries_toolbox />
</alldbconnector>

ignoreUserPass

Indicates that the user name and password specified by a user is to be removed by the database connection process when a utility is connecting to a database. Include this element when you configure the AllDBConnector class to authenticate users with a different process.

(Oracle) For example, use this element when you configure the AllDBConnector class to authenticate users with an Oracle wallet. When you configure the class to authenticate users with an Oracle Wallet, the user name and password that are included in the command-line utility are no longer needed.

security

Indicates that AllDBConnector class needs to make a Security.addProvider(...) API call to retrieve a specified security provider to authenticate user access to establish a database connection. To configure this element, use the following element attribute:

providername

The fully qualified class name of the security provider. If the security provider classes are not included in any JAR file that the AllDBConnector class loads by default, configure the AllDBConnector class to load the security provider JAR files using the jar element.

(Oracle) For example, to configure the AllDBConnector class to authenticate users with an Oracle wallet, we must specify the appropriate Oracle security provider.

<security providername="oracle.security.pki.OraclePKIProvider" />

jar

Indicates that the database connection process is to use the AllDBConnector class to attempt to load a specified JAR file needed to successfully establish a database connection. By loading JAR files, the AllDBConnector ensures that all classes that are needed to establish a database connection are available to each other class. To configure this element, use the following element attribute:

path

The file system path of the JAR file that the AllDBConnector class is to load.

If you require a security provider that is not available in a JAR file that loads by default, configure the AllDBConnector class to load the JAR file for our security provider.

(Oracle) For example, to configure the AllDBConnector class to authenticate users with an Oracle wallet, you need to load Oracle JAR files. The following code snippet demonstrates how to configure the AllDBConnector class to load these JAR files:

<jar path="c:\oracle\product\11.2.0\dbhome_1\jlib\oraclepki.jar" />
<jar path="c:\oracle\product\11.2.0\dbhome_1\jlib\osdt_cert.jar" />
<jar path="c:\oracle\product\11.2.0\dbhome_1\jlib\osdt_core.jar" />

override

Provides the capability to override any specified input connection parameters. The override element accepts the same subelements as the parent database-type elements, except the override element cannot include a nested override subelement. To configure this element, use the following element attributes:

identifier

Required. Identifying name for the override configuration. Ensure that the identifier is unique among the override configurations within the configuration file. When a specified input parameter matches the identifier for an override configuration, the override values are used to replace the specified input parameters.

enabled

Optional. Enables the override configuration. We can set either of the following values for this element:

yes

Enables the element. The default value.

no

Disables the element.

includeusers

Controls whether the override applies to only specific users. Use a comma-separated list to include multiple users.

excludeusers

Controls whether the override configuration does apply to specific users. Use a comma-separated list to exclude multiple users.

Note: If the override element does not include the includeusers or excludeusers attributes, the override element applies to all users when the override configuration identifier value matches the database specification that a user includes in the command to run a utility.If multiple override elements are configured with the same identifier, the following rules are used to determine which configuration to use:

• If multiple override elements exist with the same attributes, the first element configuration is used.

• If multiple override elements exist with different attributes, the first element configuration that matches one of the following conditions is used. These conditions are checked in order:

  • The override configuration includes the user that is trying to establish a database connection in the includeusers attributes.

  • The override configuration does not include the includeusers or excludeusers attributes.

  • The override configuration has an excludeusers attribute that does not have the user specified.

  If an ignoreuserpass element is included in one of the overrides, the element applies after the appropriate override configuration to use is determined.

The following snippet specifies an override configuration:

<alldbconnector>
  <db2>
    <property name="currentSchema" value="MYSCHEMA" />
    <property name="currentQueryOptimization" value="0" enabled="no" />
    <logoutput value="standardout" />
    <jdbcurl value="jdbc:db2://localhost:50000/mall"; />

    <override identifier="prodmall">
      <jdbcurl value="jdbc:db2://prodserver:50000/mall" />
      <property name="currentSchema" value="PRODSCMA" />
    </override>
  </db2>
  <oracle />
  <derby />
  <iseries />
  <iseries_toolbox />
</alldbconnector>

This configuration overrides the connection properties for all connections to a DB2 for Linux, UNIX, and Windows database to use the following parameters:

• The value MYSCHEMA for the currentSchema property

• The value jdbc:db2://localhost:50000/mall for the jdbcurl property

If the input parameter prodmall is specified, the override configuration ensures that no property or jdbcurl elements of the parent database-type apply. Instead, the caller utility uses the value jdbc:db2://prodserver:50000/mall for the jdbcurl element, and the value PRODSCMA is used for the currentSchema element. The override configuration does not override the value for the logoutput element at the database-type level configuration. If you do include a logoutput element within the override configuration, the caller utility creates a diagnostic log at the paths set in each logoutput element.

Configuration property overrides

The following code snippet specifies the input parameters to connect the stagingprop utility to a database. These parameters set the values for the -sourcedb and -destdb parameters:

-sourcedb mall 
-sourcedb localhost:50000/mall 
-sourcedb jdbc:db2://localhost:50000/mall

To configure overrides for these parameters, use the parameter values 'mall', 'localhost:5000/mall', and 'jdbc:db2://localhost:50000/mall' as the value for the override configuration identifier elements. Within the override configuration, we can set custom connection parameters or a different JDBC URL. If the override does not exist, or does not contain a JDBC URL element, the utility constructs the JDBC URL from the specified input parameters. However, If a JDBC URL exists in an applicable override element, the stagingprop utility uses the configured JDBC URL instead of constructing a URL from the input parameters.

Note: Depending on performance requirements, your system might use a local 64-bit database server. If your system uses a 64-bit database server with a 32-bit Java virtual machine (JVM), certain utilities and installation processes might fail. For instance, utility processes can fail if the only database connection input parameter that the utility or process specifies when attempting to establish a database connection is the database name. In these situations, the problem can be resolved by configuring an override with the database name as the override identifier value and using a jdbc subelementFor example, certain database types, such as a DB2 for Linux, UNIX, and Windows database, the database name specification, or the type-2 JDBC URL specification forces the use of the type-2 JDBC driver. The use of a type-2 JDBC driver requires loading native libraries. An attempt to load a native library can fail when the JVM is 32-bit and the database server is 64-bit. Using libraries to look up the database server name and port number to construct a type-4 JDBC URL can also fail because this process also requires the loading of native libraries. We can resolve this problem by configuring an override with the database name as the override identifier value, or by completing one of the following tasks:

• Install a 32-bit client

• Use a 32-bit database server

• Use a 64-bit JVM

DB2 pureScale support

Beginning with WebSphere Commerce Version 7 Fix Pack 8, all WebSphere Commerce utilities support the use of a DB2 pureScale database. When creating an instance on a DB2 for Linux, UNIX, and Windows pureScale database, the instance creation process adds the following connection properties to the WebSphere Commerce data source:

clientRerouteAlternateServerName=[comma separated host list]
clientRerouteAlternatePortNumber=[comma separated port list]
enableAlternateServerListFirstConnect=true
enableSysplexWLB=true

If Content Management is also enabled, the instance creation process also adds the properties to the WebSphere Commerce Publish data source.

When creating an instance with a DB2 pureScale database, the process adds override elements within the alldbconnector.xml configuration file. The override elements contain the same elements that are added to the WebSphere Commerce data source and includes the appropriate JDBC URL information. We can review the full list of connection properties that are set to support a DB2 pureScale database within the ConnectionPropertyConfig and CMConnectionPropertyConfig targets in the following files:

• WC_installdir\config\deployment\xml\createInstance.xml

• WC_installdir\components\Workspaces\xml\configureWorkspaces.xml

Example

The following configuration XML file sets the properties that the alldbconnector class uses to configure how utilities acquire a database connection. The following configuration specifies overrides for two databases that are cataloged on a local DB2 for Linux, UNIX, and Windows database server.

On the local instance, the first database is cataloged as 'mall' and the second database as 'prodmall'. The alias 'mall' refers to a local database that is named 'mall', while the alias 'prodmall' refers to a remote database 'mall' that is on a host, which is named 'prodserver'. On the 'prodserver' host, the DB2 remote connection service operates on port '50000'. The local database server is on a host named 'stagingserver'. On the 'stagingserver' host, the database connection service is operational on port '50000'.

If any connection parameters specified for a utility match the override configurations, the properties set within the override configurations are used to acquire a database connection.

<alldbconnector>
  <db2>
    <!--
      ***
      start of overrides for local database
      ***
    -->
    
    <!-- override database name so that native library loading isn't done -->
    <override identifier="mall">
      <jdbcurl value="jdbc:db2://localhost:50000/mall"; />
      <property name="currentSchema" value="STAGING" />
    </override>
    
    <!-- override type-2 database specification to prevent native library loading -->
    <override identifier="jdbc:db2:mall">
      <jdbcurl value="jdbc:db2://localhost:50000/mall"; />
      <property name="currentSchema" value="STAGING" />
    </override>
    
    <!-- override type-4 database specification for uniformity -->
    <override identifier="stagingserver:50000/mall">
      <jdbcurl value="jdbc:db2://localhost:50000/mall"; />
      <property name="currentSchema" value="STAGING" />
    </override>
    
    <!--
      override JDBC URL for schema specification
      note that we need not have overridden that JDBC URL in this override, but let us do so anyway
    -->
    <override identifier="jdbc:db2://stagingserver:50000/mall">;
      <jdbcurl value="jdbc:db2://localhost:50000/mall"; />
      <property name="currentSchema" value="STAGING" />
    </override>
    
    <!-- we could also create overrides for localhost:50000/mall and jdbc:db2://localhost:50000/mall but perhaps that can be left as an exercise -->
    
    <!--
      ***
      end of overrides for local database
      ***
    -->
    
    <!--
      ***
      start of overrides for remote database
      ***
    -->
    
    <!-- override database name so that native library loading isn't done -->
    <override identifier="prodmall">
      <jdbcurl value="jdbc:db2://prodserver:50000/mall"; />
      <property name="currentSchema" value="PRODUCTN" />
    </override>
    
    <!-- override type-2 database specification to prevent native library loading -->
    <override identifier="jdbc:db2:prodmall">
      <jdbcurl value="jdbc:db2://prodserver:50000/mall"; />
      <property name="currentSchema" value="PRODUCTN" />
    </override>
    
    <!-- override type-4 database specification for uniformity -->
    <override identifier="prodserver:50000/mall">
      <jdbcurl value="jdbc:db2://prodserver:50000/mall"; />
      <property name="currentSchema" value="PRODUCTN" />
    </override>
    
    <!--
      override JDBC URL for schema specification
      note that we need not have overridden that JDBC URL in this override, but let us do so anyway
    -->
    <override identifier="jdbc:db2://prodserver:50000/mall">;
      <jdbcurl value="jdbc:db2://prodserver:50000/mall"; />
      <property name="currentSchema" value="PRODUCTN" />
    </override>
    
    <!--
      ***
      end of overrides for remote database
      ***
    -->
  </db2>
  <oracle />
  <derby />
  <iseries />
  <iseries_toolbox />
</alldbconnector>