11 Multithreaded Applications

EXEC SQL ENABLE THREADS

This executable SQL statement initializes a process that supports multiple threads. This must be the first executable SQL statement in your multithreaded application.

EXEC SQL CONTEXT ALLOCATE

This executable SQL statement allocates and initializes memory for the specified runtime context; the runtime-context variable must be declared of type sql_context.

EXEC SQL CONTEXT USE

This directive instructs the precompiler to use the specified runtime context for subsequent executable SQL statements. The runtime context specified must be previously allocated using an EXEC SQL CONTEXT ALLOCATE statement.

The EXEC SQL CONTEXT USE directive works similarly to the EXEC SQL WHENEVER directive in that it affects all executable SQL statements which positionally follow it in a given source file without regard to standard C scope rules. In the following example, the UPDATE statement in function2() uses the global runtime context, ctx1:

sql_context ctx1;            /* declare global context ctx1     */

function1()
{
   sql_context :ctx1;         /* declare local context ctx1      */
   EXEC SQL CONTEXT ALLOCATE :ctx1;
   EXEC SQL CONTEXT USE :ctx1; 
   EXEC SQL INSERT INTO ...  /* local ctx1 used for this stmt   */
   ...
}

function2() 
{
   EXEC SQL UPDATE ...       /* global ctx1 used for this stmt */
}

To use the global context after using a local context, add this code to function1():

function1()
{
   sql_context :ctx1;         /* declare local context ctx1      */
   EXEC SQL CONTEXT ALLOCATE :ctx1;
   EXEC SQL CONTEXT USE :ctx1; 
   EXEC SQL INSERT INTO ...  /* local ctx1 used for this stmt   */
   EXEC SQL CONTEXT USE DEFAULT;
   EXEC SQL INSERT INTO ... /* global ctx1 used for this stmt   */
   ...
}

In the next example, there is no global runtime context. The precompiler refers to the ctx1 runtime context in the generated code for the UPDATE statement. However, there is no context variable in scope for function2(), so errors are generated at compile time.

function1() 
{
   sql_context ctx1;         /* local context variable declared */
   EXEC SQL CONTEXT ALLOCATE :ctx1;
   EXEC SQL CONTEXT USE :ctx1; 
   EXEC SQL INSERT INTO ...     /* ctx1 used for this statement */
   ...
} 
function2() 
{
   EXEC SQL UPDATE ...   /* Error! No context variable in scope */
}

EXEC SQL CONTEXT FREE

This executable SQL statement frees the memory associated with the specified runtime context and places a null pointer in the host program variable.

How to Enable Connection Pooling

To enable connection pooling while precompiling an application, the user must set the command line option CPOOL=YES. Based on CPOOL=YES/NO, the connection pool feature is enabled or disabled.

Command Line Options for Connection Pooling

Table 11-1 lists the command line options for connection pooling:

A typical multithreaded application creates a pool of 'n' physical connections. The 'n' value needs to be specified by providing the CMIN value during precompilation. A minimum number of physical connections (CMIN) to the database are created on the first connect call. For new incoming requests, the mapping from a virtual connection (named connection) to a physical connection is carried out as described in the following section:

Case 1: If a physical connection is available (among the already opened connections), a new request will be served by this connection.

Case 2: If all physical connections are in use then,

Case 2a: If the number of opened connections has not reached the maximum limit (CMAX), new CINCR connections are created, and one of these connections is used to serve the request.

Case 2b: If the number of opened connections has reached the maximum limit (CMAX) without the CNOWAIT being set, the request waits until it acquires a connection. Otherwise, the application displays an error message ORA 24401: cannot open further connections.

Example

Refer to Figure 11-4 for an illustration of the following example.

Let
CMIN be 1,
CMAX be 2, and
CINCR be 1.

Consider the following scenario. When the first request TC1 comes in, SQLLIB creates the connection pool with one physical connection C1. When another request TC2 comes in, the application checks if C1 is free. As C1 is used to serve the first request (Case 1), a new physical connection C2 is created to serve the request (Case 2a). If another request TC3 comes in, and if both C1 and C2 are busy, then TC3 either waits for a specified time or returns with an error message (Case 2b).

Performance Tuning

Users can set the connection pooling parameters to get better performance, based on the application. The Performance Graph in Figure 11-5 illustrates performance gain by changing the CMIN value for the Pro*C/C++ Demo Program:1. Demo Program:2 illustrates performance gain by changing the CMAX parameter.

Example

/* 
*  cpdemo1.pc 
* 
* Description: 
*            The program creates as many sessions as there are threads. 
*      Each thread connects to the default database, calls COMMIT and
*      executes a simple SELECT statement. Each thread have its own 
*      runtime contexts. 
* 
*/ 

#include <stdio.h> 
#include <stdlib.h> 
#include <string.h> 
#include <sqlca.h> 

#define      _EXC_OS_        _EXC__UNIX 
#define      _CMA_OS_        _CMA__UNIX 
#define      MAX_ROWS        256
#define      BUFF_LEN        21

#ifdef DCE_THREADS 
#include <pthread.h> 
#else 
#include <thread.h> 
#endif 

/* Function prototypes */ 
void   err_report(); 
#ifdef DCE_THREADS 
void   do_transaction(); 
#else 
void   *do_transaction(); 
#endif 
void   get_transaction(); 
void   logon(); 
void   logoff(); 

#define CONNINFO "hr/hr" 
#define THREADS  40 

struct parameters 
  { 
   sql_context * ctx; 
   int thread_id; 
  }; 
typedef struct parameters parameters; 

struct timeval tp1;
struct timeval tp2;

/*************************************** 
*  Main 
***************************************/ 

main() 
{ 
  sql_context ctx[THREADS]; 
#ifdef DCE_THREADS 
  pthread_t thread_id[THREADS]; 
  pthread_addr_t status; 
#else 
  thread_t thread_id[THREADS]; 
  int status; 
#endif 
  parameters params[THREADS]; 
  int i; 

  EXEC SQL ENABLE THREADS; 
  EXEC SQL WHENEVER SQLERROR DO err_report(sqlca); 

  if(gettimeofday(&tp1) == -1)
  {
    perror("First: ");
    exit(0);
  }

  /* Create THREADS sessions by connecting THREADS times */ 
  for(i=0;i<THREADS;i++) 
  { 
    printf("Start Session %d....",i); 
    EXEC SQL CONTEXT ALLOCATE :ctx[i]; 
    logon(ctx[i],CONNINFO); 
  } 

  /*Spawn threads*/ 
  for(i=0;i<THREADS;i++) 
  { 
    params[i].ctx=ctx[i]; 
    params[i].thread_id=i; 

#ifdef DCE_THREADS 
    if (pthread_create(&thread_id[i],pthread_attr_default, 
      (pthread_startroutine_t)do_transaction, 
      (pthread_addr_t) &params[i])) 
#else 
    if (status = thr_create 
      (NULL, 0, do_transaction, &params[i], 0, &thread_id[i])) 
#endif 
      printf("Cant create thread %d\n",i); 
    else 
      printf("Created Thread %d\n", i); 
  } 

  /* Logoff sessions....*/ 
  for(i=0;i<THREADS;i++) 
  { 
    /*wait for thread to end */ 
#ifdef DCE_THREADS 
    if (pthread_join(thread_id[i],&status)) 
      printf("Error when waiting for thread % to terminate\n", i); 
    else 
      printf("stopped\n"); 

    printf("Detach thread..."); 
    if (pthread_detach(&thread_id[i])) 
      printf("Error detaching thread! \n"); 
    else 
      printf("Detached!\n"); 
#else 
    if (thr_join(thread_id[i], NULL, NULL)) 
      printf("Error waiting for thread to terminate\n"); 
#endif 
      logoff(ctx[i]); 
      EXEC SQL CONTEXT FREE :ctx[i]; 
  } 

  if(gettimeofday(&tp2) == -1)
  {
    perror("Second: ");
    exit(0);
  }

    printf(" \n\nTHE TOTAL TIME TAKEN FOR THE PROGRAM EXECUTION = %f \n\n",
    (float)(tp2.tv_sec - tp1.tv_sec) + ((float)(tp2.tv_usec -
    tp1.tv_usec)/1000000.0));


} 

/*********************************************************************** 
* Function: do_transaction 
* Description:  This function calls CMMIT and execute a simple SELECT
*  statement. 
***********************************************************************/ 
#ifdef DCE_THREADS 
void do_transaction(params) 
#else 
void *do_transaction(params) 
#endif 
parameters *params; 
{ 
  struct sqlca sqlca; 
  char empName[MAX_ROWS][BUFF_LEN];
  int src_count; 
  sql_context ctx=params->ctx; 

  EXEC SQL WHENEVER SQLERROR DO err_report(sqlca); 
  EXEC SQL CONTEXT USE :ctx; 
  printf("Thread %d executing transaction\n",params->thread_id); 
  EXEC SQL COMMIT; 
  EXEC SQL SELECT FIRST_NAME into empName from EMPLOYEES where JOB_ID=
        (select JOB_ID from EMPLOYEES where EMPLOYEE_ID=205);
} 

/************************************************************** 
* Function: err_report 
* Description: This routine prints out the most recent error 
**************************************************************/ 
void      err_report(sqlca) 
struct sqlca sqlca; 
{ 
  if (sqlca.sqlcode < 0) 
    printf("\n%.*s\n\n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc); 
  exit(1); 
} 

/************************************************************ 
* Function: logon 
* Description: Logs on to the database as USERNAME/PASSWORD 
************************************************************/ 
void      logon(ctx,connect_info) 
sql_context ctx; 
char * connect_info; 
{ 
  EXEC SQL WHENEVER SQLERROR DO err_report(sqlca); 
  EXEC SQL CONTEXT USE :ctx; 
  EXEC SQL CONNECT :connect_info; 
  printf("Connected!\n"); 
} 

/*************************************************** 
* Function: logoff 
* Description: This routine logs off the database 
***************************************************/ 
void      logoff(ctx) 
sql_context ctx; 
{ 
  EXEC SQL WHENEVER SQLERROR DO err_report(sqlca); 
  EXEC SQL CONTEXT USE :ctx; 
  EXEC SQL COMMIT WORK RELEASE; 
}

Case 1: By varying CMIN

Figure 11-6 Performance Graph Case 1

Description of the illustration performgraph_case1.gif

The application takes around 7.5 seconds for execution with CPOOL=NO. With CPOOL=YES, and CMIN=8 and CMAX=14, the execution time reduces to 4.5 seconds. So, the performance improvement is about 1.7 times. The difference in performance is because of different database operations (SELECT vs UPDATE) which is purely a server side activity and beyond the scope of connection pool feature which is a client side feature.

Case 2: By varying CMAX

Figure 11-7 Performance Graph Case 2

Description of the illustration performgraph_case2.gif

For the preceding graph the demo program was run with CMIN=5 and CINCR=3. The best performance was observed with CMAX=14. Execution takes around 7.4 seconds with CPOOL=NO. With CPOOL=YES, when CMAX=14 the execution time reduces to around 3.1 seconds, resulting in a 2.3 fold performance gain.

The performance improvement varies with CMAX.Therefore to obtain the best performance for a given application, the user should vary CMIN and CMAX until the optimal performance is achieved.

Example

/*
  * Program to show the performance improvement when cpool option is used
  * Run this program with cpool=no. Record the time taken for the program to
  * execute
  *
  * Compare the execution time
  *
  * This program also demonstrates the impact of properly tuned CMAX 
  * parameter on the performance
  *
  * Run the program with the following parameter values for best performance
  * 
  * CMIN=5
  * CINCR=2
  * CMAX=14
  *
  */


#include <stdio.h>
#include <sqlca.h>

#ifdef DCE_THREADS 
#include <pthread.h> 
#else 
#include <thread.h> 
#endif 


#define CONNINFO "hr/hr"
#define THREADS 40
#define      MAX_ROWS        256
#define      BUFF_LEN        21

/***** prototypes ************** */
#ifdef DCE_THREADS 
void selectFunction();
void updateFunction();
#else 
void *selectFunction();
void *updateFunction();
#endif 

void err_report(struct sqlca sqlca);
/* ************************* */

/***** parameter to the function selectFunction, updateFunction */
struct parameters 
{
  sql_context ctx;
  char connName[20];
  char dbName[20];
  int thread_id;
};
typedef struct parameters parameters;
/*******************************************/

parameters params[THREADS];

struct timeval tp1;
struct timeval tp2;

int main()
{
  int i, status;
  thread_t thread_id[THREADS];

  int thrNos[THREADS];

  for(i=0; i<THREADS; i++)
    thrNos[i] = i;

  EXEC SQL ENABLE THREADS;

  /* Time before executing the program */
  if(gettimeofday(&tp1) == -1){
    perror("First: ");
    exit(0);
  }

  EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);
  /* connect THREADS times to the data base */
  for(i=0; i<THREADS; i++)
  {
    strcpy(params[i].dbName, "inst1");
    sprintf(params[i].connName,"conn%d", i);
    params[i].thread_id = i;

    /* logon to the data base */
    EXEC SQL CONTEXT ALLOCATE :params[i].ctx;
    EXEC SQL CONTEXT USE :params[i].ctx;
    EXEC SQL CONNECT :CONNINFO
      AT :params[i].connName USING :params[i].dbName;
  }


  /* create THREADS number of threads */
  for(i=0;i<THREADS;i++)
  {
      printf("Creating thread %d \n", i);
      if(i%2)
      {
        /* do a select operation if the thread id is odd */
#ifdef DCE_THREADS 
        if(pthread_create(&thread_id[i],pthread_attr_default, 
            (pthread_startroutine_t)selectFunction, 
            (pthread_addr_t) &params[i])) 
#else 
        if(thr_create(NULL, 0, selectFunction,
              &params[i], 0, &thread_id[i]))
#endif 
          printf("Cant create thread %d \n", i);
      }
      else
      {
        /* othewise do an update operation */
#ifdef DCE_THREADS 
        if(pthread_create(&thread_id[i],pthread_attr_default, 
            (pthread_startroutine_t)updateFunction, 
            (pthread_addr_t) &params[i])) 
#else 
        if(thr_create(NULL, 0, updateFunction,
              &params[i], 0, &thread_id[i]))
#endif 
          printf("Cant create thread %d \n", i);
      }

  }

  for(i=0; i<THREADS; i++)
  {
#ifdef DCE_THREADS 
    if(pthread_join(thread_id[i],&status)) 
      printf("Error when waiting for thread % to terminate\n", i); 

    if(pthread_detach(&thread_id[i])) 
      printf("Error detaching thread! \n"); 
#else 
    if(thr_join(thread_id[i], NULL, NULL)) 
      printf("Error waiting for thread i(%d) to terminate\n", i);
#endif 
  }

  if(gettimeofday(&tp2) == -1){
  perror("Second: ");
        exit(0);
  }

  printf(" \n\nTHE TOTAL TIME TAKEN FOR THE PROGRAM EXECUTION = %f \n\n",
      (float)(tp2.tv_sec - tp1.tv_sec) + ((float)(tp2.tv_usec -
      tp1.tv_usec)/1000000.0));

  /* free the context */
  for(i=0; i<THREADS; i++)
  {
    EXEC SQL CONTEXT USE :params[i].ctx;
    EXEC SQL AT :params[i].connName COMMIT WORK RELEASE;

    EXEC SQL CONTEXT FREE :params[i].ctx;
  }

  return 0;
}

#ifdef DCE_THREADS 
void selectFunction(parameters *params)
#else 
void *selectFunction(parameters *params)
#endif 
{
  char empName[MAX_ROWS][BUFF_LEN];
  printf("Thread %d selecting .... \n", params->thread_id);

  EXEC SQL CONTEXT USE :params->ctx;
  EXEC SQL AT : params->connName
    SELECT FIRST_NAME into empName from EMPLOYEES;
  printf("Thread %d selected ....\n", params->thread_id);
  return 0;
}

#ifdef DCE_THREADS 
void updateFunction(parameters *params)
#else 
void *updateFunction(parameters *params)
#endif 
{
  printf(" Thread %d Updating ... \n", params->thread_id);

  EXEC SQL CONTEXT USE :params->ctx;
  EXEC SQL AT :params->connName update EMPLOYEES
    set SALARY = 4000 where DEPARTMENT_ID = 10;

  /* commit the changes */
  EXEC SQL AT :params->connName COMMIT;

  printf(" Thread %d Updated ... \n", params->thread_id);
  return 0;
}


/*********** Oracle error ***********/
void err_report(struct sqlca sqlca)
{
  if (sqlca.sqlcode < 0)
   printf("\n%.*s\n\n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);

  exit(0);
}