dbconfig

This command configures a connection to an external database. Using this connection, a workflow can execute a stored procedure using the DBExecute system function in Process Designer.

CAUTION  You must configure identical connections on all servers in a farmed Process Engine system. Alternatively, after configuring a connection on one server, you can copy its configuration file to the corresponding directory on each of the remaining servers in the farm. The configuration file is named .<alias>.bin, located in FNSW_LOC/sd/ where Process Engine is installed.

TIP You can view the configured connections using the listdbconfig command.

Overview

  1. Define a stored procedure in a database.
  2. Use the dbconfig command to define a connection to the database that holds the stored procedure.

    NOTE  Process Engine must have access to the appropriate database libraries so that stored procedures can be called. You must take additional steps to configure the Process Engine server in order to call stored procedures from system steps. See Additional configuration steps below.

  3. In Process Designer, when defining a DBExecute function as part of a system step, reference the connection defined in vwtool, specify the name of the stored procedure you want to call, and specify the parameters and their types to be passed to the stored procedure.

NOTE  If Process Engine is deployed on a Windows-based server, you can define the stored procedure in a SQL Server database, an Oracle database, or a DB2 database. If Process Engine is running on a Unix-based system, you can define the stored procedure in an Oracle database or a DB2 database. A Unix-based Process Engine cannot call a stored procedure in a SQL Server database.

TIP The stored procedure can be located in a database other that the workflow database.

Syntax

The syntax varies depending on the type of database.

Database Syntax

SQL Server

dbconfig <alias> s <groupname> <dbname> <authentication> [<dbuser> <dbpassword>]

Oracle

dbconfig <alias> o <dbname> <dbuser> <dbpassword>

DB2 dbconfig <alias> d <dbname> <dbuser> <dbpassword>

Parameters

alias A name, unique within the Process Engine system, to identify the Process Engine Database Connection. This is the database name to be entered in the DBExecute function in Process Designer.
groupname (SQL Server only) The SQL Server Group name.

dbname

For SQL Server, the database name; for Oracle, the Global Database Name; for DB2, the Database Alias Name.

authentication

(SQL Server only) Enter Y to use Windows authentication, or N to specify a dbuser and dbpassword.

When using Windows Authentication, the connection to the remote database is performed using the Windows logon credentials of the user logged onto Process Engine.

If you don't use Windows Authentication, you must enter a user name and password to be used to log onto the SQL Server. This information is sent across the network in clear text from the Process Engine server to the server hosting the remote SQL Server database.

dbuser

The user that Process Engine will use to log on to the specified database.

dbpassword The password associated with the dbuser.

Additional configuration steps

To use the system step's database instruction to call stored procedures, the appropriate database API libraries must be installed and must be visible to Process Engine.

Windows-based Process Engine servers

During the installation of Process Engine on a Windows server, DB2, Oracle, or SQL Server was designated as Process Engine's underlying database manager. The appropriate client API libraries for the chosen DBMS should already be present on the Process Engine server. This enables Process Engine to call stored procedures in the chosen DBMS.

If you need to call stored procedures in either of the other database systems, you must install the appropriate database client API software for those database systems on the Process Engine server.

For example, if the original Process Engine installation specified SQL Server as the underlying DBMS, SQL Server stored procedures would be usable “out of the box.” If you need to call DB2 stored procedures from this Process Engine server, you must install the DB2 client software on the Process Engine server. Then, DB2 stored procedures could be called. If you also installed the Oracle client software on the Process Engine server, system steps could call Oracle stored procedures as well.

Unix-based Process Engine servers

During the installation of Process Engine on a Unix-based server, either Oracle or DB2 was designated as Process Engine's underlying database manager. The appropriate client API libraries for the chosen DBMS should already be present on the Process Engine server.

If you need to call stored procedures in the other database system (the database system that is not used as Process Engine’s underlying database), you must install the database client API software for that database system on the Process Engine server.

In all cases, you must verify that the appropriate links to the database client API libraries are present. If the links are not present, you must create them as described below.

In all cases, the following commands must be issued while logged on with appropriate privileges and with the appropriate environment variables set. The libraries themselves must be executable from the fnsw logon.

To call DB2 stored procedures (AIX)

  1. Verify that the DB2 software is installed.
    1. At the command prompt, enter:

      ls -alF $DB2_HOME/lib/libdb2.a

    2. If the libdb2.a file does not exist, the DB2 client software isn't properly installed.
  2. Verify that the required DB2 library is extracted.
    1. At the command prompt, enter:

      ls -alF $DB2_home/lib/libdb2.o

    2. If the libdb2.o file does not exist, create it as follows:

      cd $DB2_HOME/lib
      ar -x libdb2.a
      mv shr.o libdb2.o

  3. Verify that Process Engine is configured to find the DB2 libraries.
    1. At the command prompt, enter:

      ls -alF /fnsw/lib/shobj/libdb2.so.

    2. If the link does not exist or the link points something other than the libdb2.o library, correct the configuration as follows:

      ln -s -f $DB2_HOME/lib/libdb2.o /fnsw/lib/shobj/libdb2.so

To call DB2 stored procedures (Solaris)

  1. Verify that the DB2 software is installed.
    1. At the command prompt, enter:

      ls -alF $DB2_HOME/lib/libdb2.so

    2. If the libdb2.so file does not exist, the DB2 client software isn't properly installed.
  2. Verify that Process Engine is configured to find the DB2 libraries.
    1. At the command prompt, enter:

      ls -alF /fnsw/lib/shobj/libdb2.so

    2. If the link does not exist or the link points something other than the libdb2.so library, correct the configuration as follows:

      ln -s -f $DB2_HOME/lib/libdb2.so /fnsw/lib/shobj/libdb2.so

To call DB2 stored procedures (HP-UX)

  1. Verify that the DB2 software is installed.
    1. At the command prompt, enter:

      ls -alF $DB2_HOME/lib/libdb2.sl

    2. If the libdb2.sl file does not exist, the DB2 client software isn't properly installed.
  2. Verify that Process Engine is configured to find the DB2 libraries.
    1. At the command prompt, enter:

      ls -alF /fnsw/lib/shobj/libdb2.sl

    2. If the link does not exist or the link points something other than the libdb2.sl library, correct the configuration as follows:

      ln -s -f $DB2_HOME/lib/libdb2.sl /fnsw/lib/shobj/libdb2.sl

To call Oracle stored procedures (AIX)

  1. Verify that the Oracle software is installed.
    1. At the command prompt, enter:

      ls -alF $ORACLE_HOME/lib32/clntsh.o.

    2. If the clntsh.o file doesn't exist, the Oracle client software isn't properly installed.
  2. Verify that Process Engine is configured to find the Oracle libraries.
    1. At the command prompt, enter:

      ls -alF /fnsw/lib/shobj/liboracle.so

    2. If the link does not exist or the link points something other than the clntsh.o library, correct the configuration as follows:

      ln -s -f $ORACLE_HOME/lib32/clntsh.o /fnsw/lib/shobj/liboracle.so

To call Oracle stored procedures (Solaris)

  1. Verify that the Oracle software is installed.
    1. At the command prompt, enter:

      ls -alF $ORACLE_HOME/lib32/libclntsh.o

    2. If the libclntsh.o file doesn't exist, the Oracle client software isn't properly installed.
  2. Verify that Process Engine is configured to find the Oracle libraries.
    1. At the command prompt, enter:

      ls -alF /fnsw/lib/shobj/liboracle.so

    2. If the link does not exist or the link points something other than the libclntsh.o library, correct the configuration as follows:

      ln -s -f $ORACLE_HOME/lib32/libclntsh.o /fnsw/lib/shobj/liboracle.so

To call Oracle stored procedures (HP-UX)

  1. Verify that the Oracle software is installed.
    1. At the command prompt, enter:

      ls -alF $ORACLE_HOME/lib32/libclntsh.sl

    2. If the libclntsh.sl file doesn't exist, the Oracle client software isn't properly installed.
  2. Verify that Process Engine is configured to find the Oracle libraries.
    1. At the command prompt, enter:

      ls -alF /fnsw/lib/shobj/liboracle.sl

    2. If the link does not exist or the link points something other than the libclntsh.sl library, correct the configuration as follows:

      ln -s -f $ORACLE_HOME/lib32/libclntsh.sl /fnsw/lib/shobj/liboracle.sl