Oracle Database Discussions

We're currently using READ_ONLY_AUTOCOMMIT as setting for HS_TRANSACTION_MODEL in attempt to minimize potential for locking in remote database. Nevertheless, read locks are created with DG4ODBC use by default and persist within remote database for duration of long-running queries which makes use of READ_ONLY_AUTOCOMMIT prohibitive in our environment. Is there a way to totally disable use of transaction model for DG4ODBC to achieve "pure read only" access. In other words, is it possible to change interaction between DG4ODBC agent and ODBC driver in a way that prevents creation of read locks within remote UniVerse 10.1 database?

It would be ideal for interaction between DG4ODBC agent and ODBC driver to emulate MS Excel/ MS Query interaction that occurs using same ODBC driver as is used for DG4ODBC configuration. When accessing remote data source using MS Excel / MS Query, there is no locking on remote server whatsoever. I've performed a couple different types of ODBC traces and some differences that seem relevant/noteworthy between Excel and DG4ODBC are listed below:


Excerpts from Excel / MS Query ODBC trace:
===========================

SQLSetConnectOption
0x01010000
SQL_LOGIN_TIMEOUT
0x2D000000
SQL_SUCCESS
SQLDriverConnect
0x01010000
0xF8070200
[16]DSN=dsn.ODBC;
SQL_NTS
[16]DSN=dsn.ODBC;
1024
16
SQL_DRIVER_COMPLETE
SQL_SUCCESS


SQLGetInfo
0x01010000
SQL_DATA_SOURCE_READ_ONLY
[1]N
2048
1
SQL_SUCCESS


Reference to "SQL_DATA_SOURCE_READ_ONLY above seems indicative of behavior I would like DG4ODBC to emulate. There is no such reference within ODBC trace output for DG4ODBC test:


Excerpts from DG4ODBC trace:
===================

SQLSetConnectOption
0x01010000
SQL_AUTOCOMMIT
SQL_AUTOCOMMIT_ON
SQL_SUCCESS
SQLDriverConnect
0x01010000
0x00000000
[36]DSN=dsn.ODBC;UID=userid;PWD=password;
SQL_NTS
[36]DSN=dsn.ODBC;UID=userid;PWD=password;
1024
36
SQL_DRIVER_NOPROMPT
SQL_SUCCESS


References above to "SQL_AUTOCOMMIT" and "SQL_AUTOCOMMIT_ON" in the case of DG4ODBC seem in line with current READ_ONLY_AUTOCOMMIT setting for HS_TRANSACTION_MODEL and default DG4ODBC behavior where a transaction is set even for "read only" access to FDS (foreign data source).

In one other ODBC trace I performed the following error is reported which seems related to DG4ODBC default behavior / HS_TRANSACTION_MODEL:

UCI SQLExecute() returned -1
SQLSTATE : S1000 Native Error : 950151 [U2][SQL Client][UNIVERSE]UniVerse/SQL: Isolation levels are not supported for file types 1, 19 and 25
Facility: DBCAPERR Severity: ERROR Error ID: 46 Extern error: 950151 Message: UCI Error. Func: SQLExecute(); State: S1000; uniVerse code: 950151; Msg: [U2][SQL Client][UNIVERSE]UniVerse/SQL: Isolation levels are not supported for file types 1, 19 and 25.

So while nature and effect of locking that occurs in FDS may be largely dependent on architecture and conventions associated with foreign data source, it certainly seems possible to achieve "pure read only" access to remote data source with ODBC driver we're using by altering behavior/calls to driver issued by client/agent. MS Excel / MS Query as client/agent exhibits desired behavior. Is it possible to alter behavior of DG4ODBC agent in some way to achieve same end result?

Overall, results of DG4ODBC tests so far have been very promising but this issue of locking is turning out to be a real problem for large files and, hence, long running queries for our environment in the way of making this a viable/practical option for production use.

Any help with achieving SQL_DATA_SOURCE_READ_ONLY with DG4ODBC would be greatly appreciated!!

Thread containing additional relevant background info: https://forums.oracle.com/forums/thread.jspa?threadID=2313253

Regards, Glenn

Edited by: WileyCoyote on Dec 19, 2011 10:46 AM

Edited by: WileyCoyote on Mar 8, 2012 11:00 AM