Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Budapest / Hungary
    Posts
    2

    Unhappy Unanswered: LOCK TABLE vs SQLEndTrans (newbie)

    Hi there!

    First i'm sorry, maybe my problem doesn't belong exactly here but i've searched the net and aslo several forums, but i couldn't find anything so i decided to post here.

    I'm working on a win32 application (ms visualc) and i use odbc to reach some tables on my company's iSeries through the Client Access Express ODBC driver (soooo old version) The problem is i am no db programmer so i have very little knowledge in dbs and odbc and nothing specific in db2

    It wouldn't be a problem because some very useful odbc tutoriasl are exist on the net so the program almost fine. The only problem i have is i must lock the tables the program use (with LOCK TABLE IN EXCLUSIVE MODE) this also works fine, the problem comes when i want to release them. The ms odbc doc says that i should call SQLEndTran() rather than exectuting COMMIT (if i use SQLExecute with COMMIT i get: SQL0751 - SQL statement COMMIT not allowed in a stored procedure or trigger.). Well, SQLEndTran() would be also nice if it worked :/ I call it, it returns with SQL_SUCCESS and nothing happens, the tables are still unreachable by another processes. I've also turned auto-commit off because i've read that it ignores SQLEndTran but it's the same.

    Maybe i'm doing something very wrong, is there any other way to lock and release those tables?

    Please help, thanks!
    Regards: Bagoj
    p.s.: sorry my english is not perfect

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Why do you need to lock tables with "lock exlusive mode" command? This command is only used if you would like that no application should access the table for any operation (not even select).

    Why don't you just execute SELECT statements and UPDATE / DELETE / INSERT statements against DB2?

    Be aware that DB2 locking can be done with SELECT statement.

    Sample
    Uncomitted read locking: SELECT ... FROM ... WHERE ... WITH UR
    Cursor stability locking: SELECT ... FROM ... WHERE ... WITH CS
    Read stability locking: SELECT ... FROM ... WHERE ... WITH RS
    Repeatable read locking: SELECT ... FROM ... WHERE ... WITH RR

    Cursor stability locking is the default locking if no WITH clause is specified.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 02-16-04 at 04:38.

  3. #3
    Join Date
    Feb 2004
    Location
    Budapest / Hungary
    Posts
    2
    Well yes i'd like exactly that.

    I have something like:


    field are: DocID, PageNUM, FileID

    -Read how Many pages are there for a DocID (which is the last page)
    -Insert a new page with the next page number

    It's not the perfect example and the real one is little more compicated with 5 tables but it's like this. I don't want to an instance of my app (a document archiver) read how many pages are there for a docID while an other is preparing to insert a new one.

    I'd like to lock the table and if it fails it probably means that another process is currently inserting a page. The app tries to lock the tables util it's successful (or timeout). It's what LOCK TABLE is for, isn't it?


    Thanks!
    Regards: Bagoj

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •