Connecting to DB2 Data Sources

You can configure DB2 ColdFusion data sources for Windows NT and UNIX, using both ODBC and native drivers.

Configuring DB2 Options (Windows)

If ColdFusion Server Enterprise edition is installed on a Windows NT server, you can configure DB2 ColdFusion data sources using a native driver.

Note Native Driver: DB2 Universal Database 5.2/6.1 Options (Windows)

The following table describes ColdFusion options for the DB2 Universal Database 5.2/6.1 native driver. You set these options when you configure a ColdFusion data source. See "Adding Data Sources for ColdFusion" for more information about adding data sources to ColdFusion.

Configuring DB2 Options (UNIX)

If ColdFusion Server Enterprise edition is installed on a Solaris or Linux server, you can configure DB2 ColdFusion data sources using a native driver. On a Solaris server, you can also use an ODBC driver.

Note Native Driver: DB2 Universal Database 5.2/6.1 Options (Solaris, Linux)

ColdFusion native drivers are the same for both Windows NT and UNIX. To see ColdFusion options for the DB2 Universal Database 5.2/6.1 native driver, see the table in "Native Driver: DB2 Universal Database 5.2/6.1 Options (Windows)".

Note ODBC: DB2/6000 Options (Solaris)

The following table describes ColdFusion options for the DB2/6000 ODBC driver. You set these options when you configure a ColdFusion data source. See "Adding Data Sources for ColdFusion" for more information about adding data sources to ColdFusion.

MERANT IBM DB2/6000 ODBC Options
Option Description
Data Source Name A name for your ODBC data source.
Description Descriptive information about the data source.
Database Name The name of the DB2/6000 database.
Cursors Preserve cursors at the end of each transaction --Enable this option if you want cursors to be held at the current position when the transaction ends. Doing so may impact the performance of your database operations.

Configuring System and Services Files (UNIX)

Add the following settings to the /etc/system file. These settings are necessary for the Client Enabler software libraries to work.

set msgsys:msqginfo_msgmax = 65535
set msgsys:msqginfo_msgmnb = 65535
set msgsys:msqginfo_msgseg = 8192
set msgsys:msqginfo_msgssz = 16

You must reboot the server for these settings to take effect.

Add the following settings to the /etc/services file:

dbserver1 50000/tcp # DB2 connection service port

The port number used on the client must match the port number used on the server. If you are planning on supporting a UNIX client that is using Network Information Services (NIS), you must update the services file located on your NIS master server.

Installing and Configuring DB2 Client Enabler (UNIX)

Before you can create a ColdFusion data source with the DB2 native driver, you must install the DB2 version 5.2 Client Enabler Software and create an instance. The client software can be found on the DB2 version 5.2 Software Development Kit CDROM.

Refer to the documentation that comes with the software for details. You should be familiar with DB2 to successfully complete this process. Gather the following information before you begin:

Set environment variables

After you install the Client Enabler, there will be a directory with some scripts in it that you need to run to set up your environment. Environment variables need to be set to run the command line tool db2. Look in the <installdir>/sqllib directory for the db2profile and db2cshrc scripts.

For sh or ksh run:

.<installdir>/sqllib/db2profile

For csh run:

source <installdir>/sqllib/db2cshrc

Catalog a TCP/IP node

You must add an entry to the client's node directory to describe the remote node.

This entry specifies the chosen alias (node_name), the hostname (or ip_address), and the servicename (or port_number) that the client will use to access the remote server.

  1. Run the db2 command line utility db2
  2. At the db2 prompt enter the following:
    db2 => catalog tcpip node dbserver1node remote db2unixhost server 
    db2server1
    db2 =>terminate
    

Catalog the database

Before a client application can access a remote database, the database must be cataloged on the server node and on any client nodes that will connect to it. When you create a database, it is automatically cataloged on the server with the database alias (database_alias) the same as the database name (database_name). The information in the database directory, along with the information in the node directory, is used on the client to establish a connection to the remote database.

You must add an entry to the client's node directory to describe the remote node.

  1. Run the db2 command line utility db2.
  2. At the db2 prompt enter the following:
    db2 => catalog database sample as sample1 at node dbserver1node
    db2 =>terminate
    

Test the connection

You are now ready to test the connection with a known table. The following procedure uses a table that is installed with DB2.

  1. Run the DB2 command line utility db2
  2. At the db2 prompt enter the following:
    db2 => connect to sample1 user username using password
    db2 => select * from employee
    db2 => terminate
    

Data source and start script settings for DB2 (UNIX)

This section describes changes you must make to the ColdFusion start script and

Here are the environment variables that need to be set in the <installdir>/coldfusion/bin/start script file.

# DB2 environment variables
DB2INSTANCE=db2inst1
INSTHOME=/export/home/db2inst1
# Set library search path
#
# NOTE: Add your database client library directory to the FRONT of this 
list
#
# Example: 
#
LD_LIBRARY_PATH=/usr/dt/lib:/lib:/usr/openwin/lib:$INSTHOME/sqllib/
lib:$CFHOME/lib
#
# This is the list of variables that ColdFusion will see
# Add any special Database environment variables here
#
VAR_LIST="LD_LIBRARY_PATH DB2INSTANCE INSTHOME CFHOME SYBASE ORACLE_HOME 
INFORMIXDIR INFORMIXSERVER II_SYSTEM"

Data source settings for the ColdFusion DB2 native driver

The data source setting for the native driver need to point to the database name, and include a valid DB2 login name and password. The catalog procedures described above make the connection through the DB2 Client Enabler software.

DB2 Binding and Privileges for ODBC (UNIX)

Access to DB2 requires that you bind and grant privileges to the MERANT bind files. To locate the bind files, enter the DB2 command line processor by typing db2 from a shell prompt. The bind files are located in the <installdir>/coldfusion/odbc/db2 directory. Before you proceed with the steps in this section, set up your environment by running the db2profile or db2csh script as described in "Set environment variables".

Note To connect to your DB2 database

From the DB2 command line processor, connect your DB2 database using the following syntax:

db2=> CONNECT TO <database_name> USER <userid> USING <password>
Note To bind the MERANT SQL files to the DB2 database

The next step is to bind the MERANT SQL files to the database. You can use special options on the BIND command, based on your installation. Consult the DB2 Command Reference for a detailed list of BIND options. To bind the files, enter the commands listed in the following sections. To exit the DB2 command processor, enter the verb quit.

db2=> BIND iscsso.bnd blocking all grant public
db2=> BIND isrrso.bnd blocking all grant public
db2=> BIND isurso.bnd blocking all grant public
db2=> BIND iscswhso.bnd blocking all grant public
db2=> BIND isrrwhso.bnd blocking all grant public
db2=> BIND isurwhso.bnd blocking all grant public

Executing a DB2 Stored Procedure (Windows, UNIX)

Follow these steps to execute a DB2 stored procedure through ColdFusion.

Note To execute a DB2 stored procedure:
  1. Precompile the source file. Use the PREP command for this. For example: PREP C:\TEMP\OUTSRV.SQC.

    When this command is executed (barring any errors) you should be left with a C source file, for example, OUTSRV.C

  2. Compile and link the .c file generated in step 1. At this point the .c file needs to be precompiled and this process will explode the source into the appropriate C code. Compile the C file and link to get .dll
  3. Place the library file (.dll) generated in step 2 in the appropriate directory on the server. For example, put the file on a server called DB2SERVER in the C:\sqllib\function\ folder. It could also be put in the C:\sqllib\function\unfenced\ folder.
  4. Run a CREATE PROCEDURE statement to register your stored procedure.
  5. Grant users who need to run the stored procedure permission to execute it:
    GRANT EXECUTE ON PACKAGE server1 TO PUBLIC;
    
Note Example

The following example demonstrates a CFSTOREDPROC tag that calls the stored procedure named outsrv. Note that the actual stored procedure name is case sensitive, as is the password parameter.

<CFSTOREDPROC PROCEDURE="outsrv" 
    DATASOURCE="DB2SERVER" 
    USERNAME="DB2" 
    PASSWORD="DB2"> 

    <CFPROCPARAM TYPE="OUT" 
        CFSQLTYPE="CF_SQL_DOUBLE" 
        VARIABLE="FOO" NULL="NO"> 

    <CFPROCPARAM TYPE="IN" 
        CFSQLTYPE="CF_SQL_INTEGER" 
        VALUE="0" 
        NULL="NO">

</CFSTOREDPROC> 

<CFOUTPUT>#FOO#</CFOUTPUT>