Conference orarep::nomahs::sql_services

2143.0. "PC intermittant hangs on ODBC SELECT statement" by ORAREP::AWECIM::HOWARD () Fri Feb 14 1997 13:38

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
my VB application from executing.  

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 and ORACLE_ODBC_RDB 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.