UDB 8.1 and multiple connections

[Chris Breemer]

Hi all,

I believe I'm seeing a bug in DB2 UDB 8.1, or in any case
a behaviour different from DB2 UDB 7.1.

I have a small C program, containing a mixture of ESQLC and CLI
calls, that opens two separate connections to the same database.
On connection 1, I insert a row in table ENT1.
On connection 2, I insert a row in table ENT2.
On connection 1, I do a commit.

===> At this point, connection 2 has also seen a commit,
===> because the row in ENT2 can be selected from another process.

As I'm using default isolation level, this should not happen, right ?

However, when I compile and run the same program against a DB2
7.1 client (that connects to the same 8.1 database), it works as
expected !!! That is, when connection 1 has been committed, table ENT2
is
locked. Only when a rollback has been sent to connection2, table
ENT2 can be accessed from another process, and it does not contain the
inserted row.

Additionally, with UDB 8.1 I get a crash when trying to disconnect
from
connection 2 ( ret = SQLDisconnect(h Dbc2); ). With UDB 7.1 this does
not produce a crash or error.

Any ideas would be most appreciated. The C code and precompile command
is attached.

TIA
Chris
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <sqlcli1.h>

char* D1 = "worktodo";
char* U1 = "db2admin";
char* P1 = "db2admin";

char* D2 = "worktodo";
char* U2 = "db2admin";
char* P2 = "db2admin";

EXEC SQL INCLUDE SQLCA;

main ()
{
SQLHANDLE hEnv1 = 0;
SQLHANDLE hEnv2 = 0;
SQLHANDLE hDbc1 = 0;
SQLHANDLE hDbc2 = 0;
int ret;

/*
Environment/connection 1
*/
ret = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE , &hEnv1);
ret = SQLSetEnvAttr(h Env1, SQL_CONNECTTYPE ,
(SQLPOINTER)SQL _COORDINATED_TR ANS, 0);
ret = SQLSetEnvAttr(h Env1, SQL_ATTR_SYNC_P OINT,
(SQLPOINTER)SQL _TWOPHASE, 0);
ret = SQLSetEnvAttr(h Env1, SQL_ATTR_MAXCON N, (SQLPOINTER) 0, 0);
ret = SQLAllocHandle( SQL_HANDLE_DBC, hEnv1, &hDbc1);
ret = SQLConnect (hDbc1, D1, (short)strlen(D 1), U1,
(short)strlen(U 1), P1, (short)strlen(P 1));
/*
Environment/connection 2
*/

ret = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE , &hEnv2);
ret = SQLSetEnvAttr(h Env2, SQL_CONNECTTYPE ,
(SQLPOINTER)SQL _COORDINATED_TR ANS, 0);
ret = SQLSetEnvAttr(h Env2, SQL_ATTR_SYNC_P OINT,
(SQLPOINTER)SQL _TWOPHASE, 0);
ret = SQLSetEnvAttr(h Env2, SQL_ATTR_MAXCON N, (SQLPOINTER) 0, 0);
ret = SQLAllocHandle( SQL_HANDLE_DBC, hEnv2, &hDbc2);
ret = SQLConnect (hDbc2, D2, (short)strlen(D 2), U2,
(short)strlen(U 2), P2, (short)strlen(P 2));
/*
Switch to environment 1 and insert a row in table ENT1
*/
ret = SQLSetConnectio n(hDbc1);
EXEC SQL INSERT INTO ENT1 VALUES('1', 'CONN1');

/*
Switch to environment 2 and insert a row in table ENT2
*/
ret = SQLSetConnectio n(hDbc2);
EXEC SQL INSERT INTO ENT2 VALUES('2', 'CONN2');

/*
Switch to environment 1 and issue COMMIT
*/
ret = SQLSetConnectio n(hDbc1);
ret = SQLTransact(hEn v1, hDbc1, SQL_COMMIT);
/*************** *************** *************** *************** ***************
At this point, the row inserted via path 2 has also been committed !
Is that a bug, or what ?
*************** *************** *************** *************** ***************/

/*
Switch to environment 2 and issue ROLLBACK
*/
ret = SQLSetConnectio n(hDbc2);
ret = SQLTransact(hEn v2, hDbc2, SQL_ROLLBACK);

/* Clean up */

ret = SQLDisconnect(h Dbc1);
ret = SQLDisconnect(h Dbc2); /* This crashes with UDB 8.1 but not
with UDB 7.1 */
ret = SQLFreeConnect( hDbc1);
ret = SQLFreeConnect( hDbc2);
ret = SQLFreeEnv(hEnv 1);
ret = SQLFreeEnv(hEnv 2);
}
Precompile command:

set DB2_HOME=d:\dat abase\db2
SET PATH=%DB2_HOME% \bin;%PATH%
set BAT=db2tmp.bat

echo set DB2_HOME=d:\dat abase\db2 > %BAT%
echo SET PATH=%DB2_HOME% \bin;$%PATH% >> %BAT%
echo @echo on >> %BAT%
echo db2 connect to worktodo user db2admin using db2admin >> %BAT%
echo db2 prep db2paths.sqc OUTPUT db2paths.c BINDFILE USING
db2paths.bnd PACKAGE USING db2paths CONNECT 2 SYNCPOINT TWOPHASE
CALL_RESOLUTION DEFERRED ISOLATION CS BLOCKING ALL >> %BAT%
echo db2 bind db2paths.bnd ISOLATION CS GRANT PUBLIC >> %BAT%

%DB2_HOME%\bin\ db2cmd -c -w -i %BAT%