Re: Deleting Using Rowid

We just migrated two of our databases to IBM Informix Dynamic Server Version 11.70.FC6 on HP-UX B.11.31 U ia64. We were in certification/test mode for a few months and did not see this issue. One of our stored procdure deletes duplicates. It failed today, as it ran for the first time in production, with SQL error 240, ISAM Error 126. The stored proc uses a cursor; uses PDQPRIORITY and within a sub-query uses an Index Hint. The tables are fragmented. The table in the sub-query is a little over 800 million rows. The other table is less than 300 million records. One of the Delete statements is pasted below note that the same stored proc runs fine in the test environment. Can you please help explain what the issue may be. I know I have not provided much detail.. please let me know what other details you would like to know to help shed some light as to what maybe going on with informix. One key point, based on IBM's advise, we ran the stored proc a few times (repeatedly) and crashed informix twice in less than 5 minutes! Yes, we were in production... ouch! If you need details from the assert failures, I can provide that too. We have provided the assert files to IBM and they are looking into it too. Any help would be appreciated! excerpt from the stored proc.... IF l_scan_type_id = 60 THEN DELETE FROM scan_undeliverable WHERE scan_event_id IN ( SELECT --+ INDEX (pin_scan_event i1_pin_scan_event) scan_event_id FROM pin_scan_event WHERE pin = l_pin AND scan_date = l_scan_date AND scan_time = l_scan_time AND scan_term_id = l_scan_term_id AND scan_route_no = l_scan_route_no AND scan_type_id = l_scan_type_id AND num_pcs = l_num_pcs AND purge_frag_id IN (l_f_purge_frag_id, l_purge_frag_id, l_t_purge_frag_id) AND scan_event_id != l_min_scan_id) AND purge_frag_id IN (l_f_purge_frag_id, l_purge_frag_id, l_t_purge_frag_id); END IF;

Is it possible that more than one thread or session was running tge procedure? BTW my dbdelete utility will be faster than this procedure. FWIW. Art On May 26, 2013 4:35 PM, "SHEHLA ARSHAD" <shehla.arshad@innovapost.com> wrote: > We just migrated two of our databases to IBM Informix Dynamic Server > Version > 11.70.FC6 on HP-UX B.11.31 U ia64. We were in certification/test mode for a > few months and did not see this issue. > > One of our stored procdure deletes duplicates. It failed today, as it ran > for > the first time in production, with SQL error 240, ISAM Error 126. The > stored > proc uses a cursor; uses PDQPRIORITY and within a sub-query uses an Index > Hint. The tables are fragmented. The table in the sub-query is a little > over > 800 million rows. The other table is less than 300 million records. One of > the > Delete statements is pasted below > > note that the same stored proc runs fine in the test environment. > > Can you please help explain what the issue may be. I know I have not > provided > much detail.. please let me know what other details you would like to know > to > help shed some light as to what maybe going on with informix. > > One key point, based on IBM's advise, we ran the stored proc a few times > (repeatedly) and crashed informix twice in less than 5 minutes! Yes, we > were > in production... ouch! If you need details from the assert failures, I can > provide that too. We have provided the assert files to IBM and they are > looking into it too. > > Any help would be appreciated! > > excerpt from the stored proc.... > > IF l_scan_type_id = 60 THEN > > DELETE FROM scan_undeliverable> > WHERE scan_event_id IN ( > > SELECT --+ INDEX (pin_scan_event i1_pin_scan_event) > > scan_event_id FROM pin_scan_event > > WHERE pin = l_pin > > AND scan_date = l_scan_date > > AND scan_time = l_scan_time > > AND scan_term_id = l_scan_term_id > > AND scan_route_no = l_scan_route_no > > AND scan_type_id = l_scan_type_id > > AND num_pcs = l_num_pcs > > AND purge_frag_id IN (l_f_purge_frag_id, l_purge_frag_id, > l_t_purge_frag_id) > > AND scan_event_id != l_min_scan_id) > > AND purge_frag_id IN (l_f_purge_frag_id, l_purge_frag_id, > l_t_purge_frag_id); > > END IF; > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f83aa3714c12904dda60414