Conference orarep::nomahs::odbc_rdb_driver

1295.0. "PC intermittant hang on ODBC SELECT statement" by ORAREP::AWECIM::HOWARD () Thu Jan 30 1997 12:57

Problem:  PC intermittant hangs on ODBC SELECT statement.

My VB 4.0 application opens a local .MDB database that contains a few local
tables and a few tables that are linked via ODBC to RDB.  We are using 
SQL Services classes that are attached to the Rdb database.

The nature of the application requires that multiple PC Clients poll one of 
the tables in the RDB database every 60 seconds.  Intermittently the PC hangs
on the select statement, utilizing 100% of the PC's CPU.  The PC users have 
read-only access to the remote table in the Rdb database.

The application runs for long periods without a hitch.  But the problem shows 
up every 48 hours or so and is not predictable.  It may be related to periods 
of heavy network usage, but I'm not sure. 

When the problem occurs there was no blocking going on, and no activity 
observed on the SQL Services processes.  The TCPIP socket on the SQL Services 
side seemed to still exist.  Seems likely that the connection was lost between 
PC and remote socket.

I am concerned that a network connection is maintained and failing periodically
after the query is made but before the response comes back.

At a minimum, can anyone suggest a way to have the SELECT statement timeout
so that I can continue by application?  The SELECT statement seems to block
the rest of my application from executing.  I tried enabling a VB timer before 
the SELECT statement but the timer routine never got called, as if the timer 
was ignored during the select statement.

I set the LockTimeOut=20, and NetworkBufferSize=5000 in the RDBODBC.INI file.
The RDBODBC.LOG file reveals that the database access is read (DBA=R).  The
locktimeout works when I force a lock on the database.

Cross posted in VISUAL_BASIC notes.

Software Versions
-----------------
   PC:
	Windows NT 3.51 (with Service Pack 4)
	VB V4.0 32-bit
	ODBC RDB V2.10.11 32-bit with TCP/IP

   OpenVMS:
	DEC SQL V6.1-0
	DEC Rdb V6.1-0
	Rdb/Dispatch V6.1-0 (OpenVMS AXP)


ODBCRDB.LOG
-----------
	.
	.
	.
....0X0016B758: Rows Fetched =      0X00000007
....0X00194488: SELECT EVENTINDEX.EVENT_COUNTER FROM EVENTINDEX 
....0X00194488: Rows Fetched =      0X00000001
....0X00194488: SELECT EVENT_COUNTER  FROM EVENTINDEX  WHERE EVENT_COUNTER
.... = ?
....0X001BEC10: SELECT EVENTID ,REGISTRATIONID ,DATETIME ,SERVERDATETIME ,
....HOST ,APPLICATION ,COMPONENT ,VERSION ,INSTANCE ,PID ,CALLEDBY ,SEVERI
....TY ,EVENTCODE ,MESSAGE ,LONG_MESSAGE_FLAG  FROM EVENT WHERE (EVENTID >
.... 8588 ) 
....0X001BEC10: Rows Fetched =      0X00000007
....0X00194488: SELECT EVENTINDEX.EVENT_COUNTER FROM EVENTINDEX 
....0X00194488: Rows Fetched =      0X00000001
....0X00194488: SELECT EVENT_COUNTER  FROM EVENTINDEX  WHERE EVENT_COUNTER
.... = ?
....0X00192A78: SELECT EVENTID ,REGISTRATIONID ,DATETIME ,SERVERDATETIME ,
....HOST ,APPLICATION ,COMPONENT ,VERSION ,INSTANCE ,PID ,CALLEDBY ,SEVERI
....TY ,EVENTCODE ,MESSAGE ,LONG_MESSAGE_FLAG  FROM EVENT WHERE (EVENTID >
.... 8595 ) 
    ^
    |
    Application hangs here during SELECT.  Note that the previous SELECT 
    was successful.