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

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

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

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

    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.

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

- Host name where the DB2 database server resides
- Node name
- Database name
- Database alias
- Database userid and password
- Service name from the `/etc/services` file on client and host

### 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".

| 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

## 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. 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.
   - 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 example which follows 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. 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>