Locking doubt in Informix 9.40 FC5

Hi all!!! I'm trying to "SELECT..INSERT" about 1 million rows from one standard table to one RAW table. Both tables have blob fields, and I've just discovered that IDS place an exclusive lock for each blob I'm trying to copy... My LOCKS parameter is not enough to assume 1 lock for each row, so: Do I have to "split" my rows in the "Select...Insert" statement, or could I try something to do it in only one step? I have read about "Byte-range locking", but I don't know if I can apply it in a single SQL Statement. Thank you very much in advance. Luis.

Get my dbcopy utility. Like dbload it will perform commits every N rows to release the locks and logical logs. Dbcopy is included in the package utils2_ak which is available for download from the IIUG Software Repository (www.iiug.org/software) or the Oninit web site @ www.oninit.com/utils Art On Mon, Jul 7, 2008 at 10:33 AM, LUIS VENTURA <luisventurap@gmail.com> wrote: > Hi all!!! > > I'm trying to "SELECT..INSERT" about 1 million rows from one standard table > to > one RAW table. Both tables have blob fields, and I've just discovered that > IDS > place an exclusive lock for each blob I'm trying to copy... My LOCKS > parameter > is not enough to assume 1 lock for each row, so: > > Do I have to "split" my rows in the "Select...Insert" statement, or could I > try something to do it in only one step? > > I have read about "Byte-range locking", but I don't know if I can apply it > in > a single SQL Statement. > > Thank you very much in advance. > Luis. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Okay ... giving away a trade secret ... so watch carefully ... :) unload to delete_em.sql DELIMITER " " select unique "DELETE", "FROM", "tab1", "WHERE", "col_in_tab1", "=", col_in_tab1, ";" from tab1 WHERE col_in_tab1 in (select col_in_tab2 from tab2); You then execute the resulting delete_em.sql file. Since they are single item deletes, and since you want them deleted once and forever, you do not need to bundle the deletes within a transaction. Since you are doing the deletes one at a time, you do not need to worry about locks. :) Just know that you may be, depending upon how many records are to be deleted, burning through logical logs like crazy and need to either have a manner to keep the logs continuously backed up or temporarily change LTAPEDEV to /dev/null (must bounce instance before change takes effect) or last option, change logging at the database level ... yucky stuff ... which requires a backup (fake or not) to turn off and on. Actually ... I learned to do this from a past posting on another question ... which I took to heart and have been using the idea ever since. Take care. Clifton M. Bean Informix DBA / AIX System Admin Currency Technics & Metrics Phone: (972) 812-1411 x244 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LUIS VENTURA Sent: Monday, July 07, 2008 9:33 AM To: ids@iiug.org Subject: Locking doubt in Informix 9.40 FC5 [12590] Hi all!!! I'm trying to "SELECT..INSERT" about 1 million rows from one standard table to one RAW table. Both tables have blob fields, and I've just discovered that IDS place an exclusive lock for each blob I'm trying to copy... My LOCKS parameter is not enough to assume 1 lock for each row, so: Do I have to "split" my rows in the "Select...Insert" statement, or could I try something to do it in only one step? I have read about "Byte-range locking", but I don't know if I can apply it in a single SQL Statement. Thank you very much in advance. Luis. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Thank you all for your responses. Finally I made a stored procedure which commit tx every N rows, and, adittionally, it controls the -9810 error (which is quite common in our databases...) Thank you very much again for your help.