Originally posted by Marcus_A
1. I doubt that you read the thread called "Read Me: Must Read before posting" otherwise you would have stated the DB2 version and OS you are working with.
2. I doubt that you issued a "DELETE * ..." becasue there is no such SQL statement.
3. I doubt you looked at the DB2 SQL Reference manual, becasue if you had, you would have seen the LOCK TABLE statement. But I believe that locks are released when a commit is issued.
1. That's right: I did not read this. It's z/OS, the DB2-Version is 7.0 I believe.
2. That's why I put it in quotes meaning that it is an unqualified delete.
3. Sure I looked at the manual, and I also think a LOCK TABLE won't work. I asked for something "LIKE" a LOCK TABLE statement so that other programs will get a sqlcode other that +100 until my program has ended without error.
2. The syntax is DELETE FROM table-name (without a WHERE clause) to delete all rows in the table. There is no * in a delete statement because the rows are deleted, not the columns.
3. You are in luck, at least if you use static SQL.
Releasing locks: If LOCK TABLE is a static SQL statement, the RELEASE option of bind determines when DB2 releases a lock. For RELEASE (COMMIT), DB2 releases the lock at the next commit point. For RELEASE(DEALLOCATE), DB2 releases the lock when the plan is deallocated (the application ends).
If LOCK TABLE is a dynamic SQL statement, DB2 uses RELEASE(COMMIT) and releases the lock at the next commit point, unless the table or table space is referenced by cached dynamic statements. Caching allows DB2 to keep prepared statements in memory past commit points. In this case, DB2 holds the lock until deallocation or until the commit after the prepared statements are freed from memory. Under some conditions, if a lock is held past a commit point, DB2 demotes the lock state of a segmented table or a nonsegmented table space to an intent lock at the commit point.
The other programs will never get a +100 if you lock in exclusive mode. The will probably get a -911 (or something similar) that indicates a timeout after waiting for the lock to be released and then finally giving up.
The DB2 Application Programming and SQL Guide says that an application program will get a SQLCODE -911 or -913 (SQLSTATEs '40001' or '57033') when it times out waiting for an exclusive table lock (such as held by a utility). Doesn't say under which circumstances the program will get a -911 vs. -913.