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!
p.s.: sorry my english is not perfect
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.
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.
-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?