3 Replies Latest reply on May 8, 2013 5:31 AM by Kgronau-Oracle

    Lock sql server table through DG4ODBC?

    825199
      Hi
      I'm using DG4ODBC to connect to SQL server 2008, querying data and insert to Oracle. Is it possible to lock the remote table in sql server, prohibit it from inserting any new record?
      My Oracle version is 11.1.0.7.0, OS version : Red Hat Enterprise Linux Server release 5.3.

      Thanks

      Edited by: user1152556 on 21:20 06-05-2013
        • 1. Re: Lock sql server table through DG4ODBC?
          Kgronau-Oracle
          Do you mean prohibiting DG4ODBC to manipulate SQL Server data? If yes, you could set a gateway parameter HS_TRANSACTION_MODEL=READ_ONLY_AUTOCOMMIT.

          - Klaus
          • 2. Re: Lock sql server table through DG4ODBC?
            825199
            No, I mean I want only DG4ODBC's session could insert/update on the remote table until I release lock. Something like "lock table table_name@remotedatabase in exclusive mode nowait"
            • 3. Re: Lock sql server table through DG4ODBC?
              Kgronau-Oracle
              by default the gateway starts its own transaction on the SQL Server side and as soon as you even execute a select it locks the data until you execute a commit (even when only selecting data). Unfortunately the amount of data being locked now depends on the SQL Server version and on the lock mode chosen by the SQl Server (row/page/table).

              Another option would be to explicit set a transaction isolation level for your gateway session using the passthrough command:


              DECLARE
              ret integer;
              c integer;

              BEGIN

              c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@MSODBCSQLSERVER_DG4ODBC_EMGTW_1123_DB;
              DBMS_HS_PASSTHROUGH.PARSE@MSODBCSQLSERVER_DG4ODBC_EMGTW_1123_DB(c, 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
              ret := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@MSODBCSQLSERVER_DG4ODBC_EMGTW_1123_DB(c);
              dbms_output.put_line(ret ||' passthrough output');
              DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@MSODBCSQLSERVER_DG4ODBC_EMGTW_1123_DB(c);
              END;
              /

              Please check out your SQL Server documentation which isolation level will fit for your needs.


              - Klaus