Just a general question... what happens when mytbl is dropped after insert into mytbl2 sel * from mytbl is submitted but not yet completed? mytbl is definately dropped (cannot run any selects on it), but the insert statement is still running. Will the insert ultimately fail or have an incomplete load? Or does the fact that it is still running indicate that all necessary information from mytbl was extracted, is stored in spool, and is being manipulated/inserted/distributed etc to the destination table?
what makes you sure that the table is droped while the insert/select is still running? Did you checked the DBQL records for the two statements? Do you still see the insert/select session in viewpoint?
a drop require a exclusive lock
and explain will show something link
1) First, we lock a distinct db."pseudo table" for exclusive
use on a RowHash to prevent global deadlock for test_db_uli.test_a.
2) Next, we lock db.tb for exclusive use.
So, as long as any SQL is touching the table the drop will not be executed.
Just FYI...the query utlimately failed after a considerable runtime...error message 2640: Specified table does not exist
The insert select statement was probably sitting in a TASM throttle queue and had not yet started to execute.