Database connection acquisition for utilities and Ant tasks

By default, you 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. You 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, you can review the specified parameters and potentially configure overrides to control how utilities connect to the database. For instance, you can override the database specification to ensure that utilities only use a specific JDBC URL.

You 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, you can make the database connection process for HCL Commerce utilities more secure. For more information about this configuration, see Configuring the Oracle database connection for utilities to authenticate users with Oracle Wallet.

Database connection acquisition configuration

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.

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

<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:

drivertype

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. The default value is 'thin'. The other accepted value is 'oci'.

enabled

Optional. Enables the drivertype element. You 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. You 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. If you want 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. You 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. You 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. The default value is java.lang.String. Other acceptable values are java.lang.Integer and java.lang.Boolean.

enabled

Optional. Enables the property element. You 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.

For example, to configure the AllDBConnector class to authenticate users with an Oracle wallet when you are using the thin Oracle driver-type, you need to 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 that are 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.

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, you must configure the AllDBConnector class to load the security provider JAR files using the jar element.

For example, to configure the AllDBConnector class to authenticate users with an Oracle wallet, you 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 that is 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 your security provider.

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. You 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

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

DB2 pureScale support

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

When you create 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 HCL Commerce data source and includes the appropriate JDBC URL information.

Example

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 that is named 'stagingserver'. On the 'stagingserver' host, the database connection service is operational on port '50000'.

If any connection parameters that are 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>