You can configure DB2 ColdFusion data sources for Windows NT and UNIX, using both ODBC and native drivers.
If ColdFusion Server Enterprise edition is installed on a Windows NT server, you can configure DB2 ColdFusion data sources using a native driver.
![]() |
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.
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.
![]() |
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)".
![]() |
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. |
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
dbserver1
is the Connection Service name
50000
is the port number for the Connection Port
tcp
is the communication protocol that you are using
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.
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:
/etc/services
file on client and host
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
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.
db2 => catalog tcpip node dbserver1node remote db2unixhost server db2server1 db2 =>terminate
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.
db2 => catalog database sample as sample1 at node dbserver1node db2 =>terminate
You are now ready to test the connection with a known table. The following procedure uses a table that is installed with DB2.
db2 => connect to sample1 user username using password db2 => select * from employee db2 => terminate
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"
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.
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 |
From the DB2 command line processor, connect your DB2 database using the following syntax:
db2=> CONNECT TO <database_name> USER <userid> USING <password>
![]() |
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
Follow these steps to execute a DB2 stored procedure through ColdFusion.
![]() |
To execute a DB2 stored procedure: |
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
C:\sqllib\function\
folder. It could also be put in the C:\sqllib\function\unfenced\
folder.
CREATE PROCEDURE server1 (OUT sal double, IN salind integer) EXTERNAL NAME 'outsrv!outsrv' LANGUAGE C DETERMINISTIC PARAMETER STYLE DB2DARI;
GRANT EXECUTE ON PACKAGE server1 TO PUBLIC;
![]() |
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>