I have a program with a "DELETE *" at the beginning cleaning the table. Afterwards the program is inserting the new data. I have to COMMIT after the DELETE and after a specific number of INSERTs.

If for some reason the program fails at the first insert, the table is obviously empty. In that case I want to prevent other programs to access the table until my program ends w/o an error condition.

So I need something like a LOCK TABLE that is not released when my program has an abend.

Is there anything like that in DB2? (I doubt it...)

Alex

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.

Alex

1. Version 7.1

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.