Advanced ColdFusion Administration
Advanced Data Source Management

Connecting to DB2 Databases

On Windows and UNIX, ColdFusion lets you access DB2 databases using ODBC and native drivers.

Configuring DB2 options (Windows)

If you install ColdFusion on a Windows server, you can configure a DB2 database as a ColdFusion data source using ODBC, OLE DB, or a native driver. For information about using OLE DB with ColdFusion data sources, see "About OLE DB".

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:

Option	Description
Data Source Name	A name for your data source.
Description	Descriptive information about the data source.
Database Alias	The DB2 database name.

Configuring DB2 options (UNIX)

If you install ColdFusion Server Enterprise Edition on a Solaris or Linux server, you can configure DB2 ColdFusion data sources using a native driver. On Solaris, you can also use a MERANT ODBC driver.

Native driver: DB2 Universal Database 5.2/6.1 options (Solaris, Linux)

ColdFusion native drivers are the same for Windows NT and UNIX. For the 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)".

ODBC: DB2/6000 options (Solaris)

The following table describes ColdFusion options for the MERANT IBM DB2/6000 ODBC driver:

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. Select this option if you want cursors to be held at the current position when the transaction ends. Doing so can impact the performance of your database operations.

Configuring system and services files (UNIX)

You must add some settings that are necessary for the Client Enabler software libraries to work.

To configure system and services files:

1. Add the following settings to the /etc/system file:

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

2. You must restart the server for the settings to take effect.
3. Add the following settings to the /etc/services file:

   dbserver1 50000/tcp # DB2 connection service port
   
   

   • dbserver1 is the Connection Service name.
   • 50000 is the port number for the Connection Port. The port number used on the client must match the port number used on the server.
   • tcp is the communication protocol that you are using.

If you are planning on supporting a UNIX client that is using Network Information Service (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. You can find the client software on the DB2 version 5.2 Software Development Kit CD-ROM. Refer to the documentation that comes with the software for details.

You perform the following steps:

• Set environment variables.
• Catalog a TCP/IP node.
• Catalog the database.
• Test the connection.

You should be familiar with DB2 to successfully complete this process. Gather the following information before you begin:

• Host name where the DB2 database server resides
• Node name
• Database name
• Database alias
• Database user id and password
• Service name from the /etc/services file on client and host

Set environment variables

After you install the Client Enabler, you need to run some scripts to set up your environment. You must also set environment variables 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.

To catalog a TCP/IP node:

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 client uses the information in the database directory, along with the information in the node directory, to establish a connection to the remote database.

To add an entry to the client's database node directory:

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.

To test the connection:

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 that you must make to the ColdFusion start script.

You must set the following environment variables 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 must point to the database name and include a valid DB2 login name and password. The catalog procedures described in the previous section 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".

To connect to your DB2 database:

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

   db2=> CONNECT TO <database_name> USER <userid> USING <password>
   
   

2. Bind the MERANT SQL files to the database, using special options on the BIND command, based on your installation. For a detailed list of BIND options, see the DB2 Command Reference.

To bind the MERANT SQL files to the DB2 database:

1. Enter the following commands:

   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
   
   

2. Enter quit to exit the DB2 command processor.

Executing a DB2 stored procedure (Windows, UNIX)

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

To execute a DB2 stored procedure:

1. Use the PREP command to precompile the source file; for example: PREP C:\TEMP\OUTSRV.SQC.

   When this command executes (barring any errors), you should have a C source file; for example, OUTSRV.C.

2. Compile and link the .C file generated in step 1 to get the dll file.
3. Place the dll file generated in step 2 into the appropriate directory on the server.

   For example, put the file on a server called DB2SERVER into the C:\sqllib\function\ folder. You could also put it into the C:\sqllib\function\unfenced\ folder.

4. Run a CREATE PROCEDURE statement to register your stored procedure.
   • The CREATE PROCEDURE statement creates a row in the database catalog (syscat.procedures table), making it visible to client applications, including ColdFusion Server.
   • The stored procedure's name is what you called it in your SQC file. The following example calls the stored procedure outsrv.
   • The create procedure statement looks like this:

     CREATE PROCEDURE server1
     (OUT sal double, IN salind integer)
     EXTERNAL NAME 'outsrv!outsrv'
     LANGUAGE C
     DETERMINISTIC
     PARAMETER STYLE DB2DARI;

5. Grant users who need to run the stored procedure permission to execute it:

   GRANT EXECUTE ON PACKAGE server1 TO PUBLIC;

Example

The following example demonstrates a CFSTOREDPROC tag that calls the stored procedure named outsrv. The actual stored procedure name and the password parameter are case sensitive.

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

Copyright © 2001, Macromedia Inc. All rights reserved.

---