All Forums Database
pirrejo 11 posts Joined 09/12
03 Oct 2013
what happens when you drop a table that is used in a running insert query?

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?

ulrich 816 posts Joined 09/09
03 Oct 2013

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

pirrejo 11 posts Joined 09/12
03 Oct 2013

Just FYI...the query utlimately failed after a considerable runtime...error message 2640:  Specified table does not exist
 
Thanks!

ToddAWalter 316 posts Joined 10/11
03 Oct 2013

The insert select statement was probably sitting in a TASM throttle queue and had not yet started to execute.

You must sign in to leave a comment.