How to connect to one or more data sources

Db2 ODBC supports different connection types to remote data sources through DRDA.

If an application is CONNECT (type 1) and specifies MULTICONTEXT=0, Db2 ODBC allows the application to logically connect to multiple data sources. However, Db2 ODBC allows the application only one outstanding transaction (a transaction the application has not yet committed or rolled back) on the active connection. If the application is CONNECT (type 2), then the transaction is a distributed unit of work and all data sources participate in the disposition of the transaction (commit or rollback).

To connect concurrently to one or more data sources, call SQLAllocHandle() (with HandleType set to SQL_HANDLE_DBC) once for each connection. Use the connection handle that this statement yields in an SQLConnect() call to request a data source connection. Use the same connection handle in an SQLAllocHandle() call (with HandleType set to SQL_HANDLE_STMT) to allocate statement handles to use within that connection. An extended connect function, SQLDriverConnect(), allows you to set additional connection attributes. However, statements that execute on different connections do not coordinate.

Example: The following example illustrates an application that connects, allocates handles, frees handles, and disconnects. This application connects to multiple data sources but does not explicitly set a connection type or specify multiple-context support. The CONNECTTYPE and MULTICONTEXT keywords in the initialization file declare these settings.

/* ... */
/*******************************************************
**    - Demonstrate basic connection to two data sources.
**    - Error handling mostly ignored for simplicity
**
**  Functions used:
**
**    SQLAllocHandle   SQLDisconnect
**    SQLConnect       SQLFreeHandle
**  Local Functions:
**    DBconnect
**
********************************************************/
#include <stdio.h>
#include <stdlib.h>
#include "sqlcli1.h"
int
DBconnect(SQLHENV henv,
          SQLHDBC * hdbc,
          char    * server);
#define MAX_UID_LENGTH    18
#define MAX_PWD_LENGTH    30
#define MAX_CONNECTIONS   2
int
main( )
{
    SQLHENV         henv;
    SQLHDBC         hdbc[MAX_CONNECTIONS];
    char *          svr[MAX_CONNECTIONS] =
                    {
                      "KARACHI"   ,
                      "DAMASCUS"
                    }
    /* Allocate an environment handle   */
    SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    /* Connect to first data source */
    DBconnect(henv, &hdbc[0],
              svr[0]);
    /* Connect to second data source */
    DBconnect(henv, &hdbc[1],
              svr[1]);
    /*********   Start processing step  *************************/
    /* Allocate statement handle, execute statement, and so on  */
    /*********   End processing step  ***************************/
    /************************************************************/
    /* Commit work on connection 1.                             */
    /************************************************************/
    SQLEndTran(SQL_HANDLE_DBC, hdbc[0], SQL_COMMIT);
    /************************************************************/
    /* Commit work on connection 2. This has NO effect on the   */
    /* transaction active on connection 1.                      */
    /************************************************************/
    SQLEndTran(SQL_HANDLE_DBC, hdbc[1], SQL_COMMIT);
    printf("\nDisconnecting .....\n");
    SQLDisconnect(hdbc[0]);   /* disconnect first connection */ 
       SQLDisconnect(hdbc[1]);     /* disconnect second connection  */                                       
    SQLFreeHandle (SQL_HANDLE_DBC, hdbc[0]);    /* free first connection handle */
    SQLFreeHandle (SQL_HANDLE_DBC, hdbc[1]);    /* free second connection handle */
     SQLFreeHandle(SQL_HANDLE_ENV, henv);   /* free environment handle */
    return (SQL_SUCCESS);
}
/********************************************************************
**   Server is passed as a parameter. Note that NULL values are    **
**   passed for USERID and PASSWORD.                               **
********************************************************************/
int
DBconnect(SQLHENV henv,
          SQLHDBC * hdbc,
          char    * server)
{
    SQLRETURN       rc;
    SQLCHAR         buffer[255];
    SQLSMALLINT     outlen;
   
    SQLAllocHandle(SQL_HANDLE_DBC, henv, hdbc); /* allocate connection handle */
    rc = SQLConnect(*hdbc, server, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
    if (rc != SQL_SUCCESS) {
        printf(">--- Error while connecting to database: %s -------\n", server);
        return (SQL_ERROR);
    } else {
        printf(">Connected to %s\n", server);
        return (SQL_SUCCESS);
    }
}
/* ... */