Hi Thomas,
I repeated this test with a program that forks processes instead of
creating pthreads. I'll attach this version of the program.
I find that I can establish more than 8 connections without any ODBC
errors being thrown. I've only tried up to 10 so far. Would this also
hint at thread handling problems in Linux?
The system I'm running the database on only has 8 CPU, so I only see 8
sapdb kernel processes active at a time, but this is probably a minor
observation.
Thanks,
Mark
On Fri, 2002-09-20 at 14:16, Mark Wong wrote:
> Thomas,
>
> I believe I have run into another problem that I was hoping you could
> help with. I have an application that starts a specified number of
> threads that call an empty stored procedure in a tight loop. I started
> with 1 thread and when I attempt to start 9 threads, I get the following
> error:
>
> [1] sqlstate 08S01 : [SAP AG][LIBSQLOD SO][SAP DB]Communication link
> failure;-807 Connection down, session released.
>
> I'll attach the program I used and the stored procedure.
>
> Thanks,
> Mark
[snip]
--
Mark Wong - - [EMAIL PROTECTED]
Open Source Development Lab Inc - A non-profit corporation
15275 SW Koll Parkway - Suite H - Beaverton OR, 97006
(503)-626-2455 x 32 (office)
(503)-626-2436 (fax)
http://www.osdl.org/archive/markw/
#include <pthread.h>
#include <stdio.h>
#include <stdlib.h>
#include <WINDOWS.H>
#include <sqltypes.h>
#include <sql.h>
#include <sqlext.h>
#define LOG_ODBC_ERROR(type, handle) \
log_odbc_error(__FILE__, __LINE__, type, handle)
struct odbc_context_t
{
SQLHDBC hdbc;
SQLHSTMT hstmt;
};
pthread_mutex_t db_source_mutex = PTHREAD_MUTEX_INITIALIZER;
SQLHENV henv = SQL_NULL_HENV;
SQLCHAR servername[32];
SQLCHAR username[32] = "dbt";
SQLCHAR authentication[32] = "dbt";
int thread_count;
int con_only = 0;
int parse_arguments(int argc, char *argv[]);
void worker();
double difftimeval(struct timeval rt1, struct timeval rt0);
int log_odbc_error(char *filename, int line, SQLSMALLINT handle_type,
SQLHANDLE handle);
int odbc_connect(struct odbc_context_t *odbcc);
int odbc_disconnect(struct odbc_context_t *odbcc);
int odbc_init(char *sname, char *uname, char *auth);
void test_1(struct odbc_context_t *odbcc);
int main(int argc, char *argv[])
{
int i;
if (!parse_arguments(argc, argv))
{
printf("arguments!\n");
return 0;
}
odbc_init(servername, username, authentication);
for (i = 0; i < thread_count - 1; i++)
{
pid_t child_pid;
child_pid = fork();
if (child_pid != 0)
{
break;
}
sleep(1);
}
worker();
return 0;
}
int parse_arguments(int argc, char *argv[])
{
int i;
char *flag;
if (argc < 3)
{
return 0;
}
for (i = 1; i < argc; i += 2)
{
if (strlen(argv[i]) < 2)
{
printf("invalid flag: %s\n", argv[i]);
exit(1);
}
flag = argv[i] + 1;
if (strcmp(flag, "d") == 0)
{
strcpy(servername, argv[i + 1]);
}
else if (strcmp(flag, "t") == 0)
{
thread_count = atoi(argv[i + 1]);
}
else if (strcmp(flag, "c") == 0)
{
con_only = 1;
}
}
return 1;
}
void worker()
{
struct odbc_context_t odbcc;
struct timeval rt0, rt1;
double response_time;
FILE *log;
char filename[128];
odbc_connect(&odbcc);
if (con_only)
{
printf("disconnecting immediately\n");
odbc_disconnect(&odbcc);
return;
}
/*
sprintf(filename, "t%d.log", pthread_self());
log = fopen(filename, "w");
if (log == NULL)
{
printf("canoot open logfile\n");
exit(1);
}
*/
while (1)
{
/*
if (gettimeofday(&rt0, NULL) == -1)
{
perror("gettimeofday");
}
*/
test_1(&odbcc);
/*
if (gettimeofday(&rt1, NULL) == -1)
{
perror("gettimeofday");
}
response_time = difftimeval(rt1, rt0);
fprintf(log, "%d,%f\n");
fflush(log);
*/
}
}
double difftimeval(struct timeval rt1, struct timeval rt0)
{
return (rt1.tv_sec - rt0.tv_sec) +
(double) (rt1.tv_usec - rt0.tv_usec) / 1000000.00;
}
/* Print out all errors messages generated to the error log file. */
int log_odbc_error(char *filename, int line, SQLSMALLINT handle_type,
SQLHANDLE handle)
{
SQLCHAR sqlstate[5];
SQLCHAR message[256];
SQLSMALLINT i;
char msg[1024];
i = 1;
while (SQLGetDiagRec(handle_type, handle, i, sqlstate,
NULL, message, sizeof(message), NULL) == SQL_SUCCESS)
{
sprintf(msg, "[%d] sqlstate %s : %s", i, sqlstate, message);
printf("%s\n", msg);
++i;
}
return 1;
}
/* Open an ODBC connection to the database. */
int odbc_connect(struct odbc_context_t *odbcc)
{
SQLRETURN rc;
/* Allocate connection handles. */
pthread_mutex_lock(&db_source_mutex);
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &odbcc->hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_DBC, odbcc->hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, odbcc->hdbc);
return 0;
}
/* Open connection to the database. */
rc = SQLConnect(odbcc->hdbc, servername, SQL_NTS,
username, SQL_NTS, authentication, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_DBC, odbcc->hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, odbcc->hdbc);
return 0;
}
rc = SQLSetConnectAttr(odbcc->hdbc, SQL_ATTR_AUTOCOMMIT,
SQL_AUTOCOMMIT_OFF, NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return 0;
}
rc = SQLSetConnectAttr(odbcc->hdbc, SQL_ATTR_TXN_ISOLATION,
SQL_TXN_REPEATABLE_READ, NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return 0;
}
/* allocate statement handle */
rc = SQLAllocHandle(SQL_HANDLE_STMT, odbcc->hdbc, &odbcc->hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return 0;
}
pthread_mutex_unlock(&db_source_mutex);
return 1;
}
/*
* Disconnect from the database and free the connection handle.
* Note that we create the environment handle in odbc_connect() but
* we don't touch it here.
*/
int odbc_disconnect(struct odbc_context_t *odbcc)
{
SQLRETURN rc;
rc = SQLDisconnect(odbcc->hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_DBC, odbcc->hdbc);
return 0;
}
rc = SQLFreeHandle(SQL_HANDLE_DBC, odbcc->hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_DBC, odbcc->hdbc);
return 0;
}
rc = SQLFreeHandle(SQL_HANDLE_STMT, odbcc->hstmt);
if (rc != SQL_SUCCESS)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return 0;
}
return 1;
}
/* Initialize ODBC environment handle and the database connect string. */
int odbc_init(char *sname, char *uname, char *auth)
{
SQLRETURN rc;
/* Initialized the environment handle. */
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
printf("alloc env handle failed");
return 0;
}
/* Set the database connect string, username and password. */
strcpy(servername, sname);
strcpy(username, uname);
strcpy(authentication, auth);
return 1;
}
void test_1(struct odbc_context_t *odbcc)
{
SQLRETURN rc;
rc = SQLPrepare(odbcc->hstmt, "CALL osdl_sapdb_test1", SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return;
}
rc = SQLExecute(odbcc->hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
LOG_ODBC_ERROR(SQL_HANDLE_STMT, odbcc->hstmt);
return;
}
}
