Re: PUT cursors and Locking w/o Logging

Art I am not sure if this is a joke, but here goes anyway. Dont know if the PUT CURSOR will hold locks until the cursor is closed, but you may want to consider locking the source and target tables in exclusive mode by the data copying process. That way it will use only 1 lock per table per database for the duration of the copy. I just created a unlogged database and tried LOCKING a table in EXCLUSIVE MODE and it worked, so I guess it will work on your system too. I am running IDS 7.30.UC8. Dont know whether it was possible to lock a table in exclusive mode in 7.24, but you may want to try that out on your source database. HTH Sujit "Art S. Kagel" <kagel@bloomberg.net> on 07/16/99 09:46:17 AM Please respond to kagel@bloomberg.net To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: PUT cursors and Locking w/o Logging Question for the community: If I have a database without logging (I know! It's one of the last few of the databases created by my predecessor and I am migrating it to 7.31 with logging next week, honest) and I am inserting using a PUT CURSOR. Does the PUT CURSOR hold locks on all of the rows inserted until the cursor is closed? If the database had logging I would open the CURSOR...WITH HOLD and COMMIT every N rows to release the locks so I have assumed that without logging that no locks would be held, except briefly while the 200 or so row that fit in the PUT buffer are being inserted on the engine side, and I would have no problem. But suddenly I am nervous. I have to copy 2 - 20,000,000 row tables from one server to another and I fear running out of lock (80,000 configured). This is a critical job that I will not be able to monitor constantly for the 15 hours or so that it will run, so I just want to increase my level of comfort. Anyone have any thoughts, definite answers, "How can you be so dumb?" comments? All graciously and gladly accepted. Thanks Art S. Kagel

Art, Sujit, Pardon me for busting in with what may be an unrelated question but you may have a solution for my problem, in brief: I need to duplicate a records in a few tables where columns have unique constraints. I'm presently retrieving to a cursor, fetching to a %rowtype variable and modifying the values in the variable. The problem is INSERTing the variable - is this possible ? Can I open a cursor to INSERT ? I'd like to write a procedure to do this where I can send it the Table Name, the column name, the value for the retrieval and the value it is to be replaced with in the copy. Thanks for your help, Anil Asher In article <7mon76$s9p$1@news.xmission.com>, Sujit.Pal@bankofamerica.com wrote: > > > Art > > I am not sure if this is a joke, but here goes anyway. > > Dont know if the PUT CURSOR will hold locks until the cursor is closed, but > you may want to consider locking the source and target tables in exclusive > mode by the data copying process. That way it will use only 1 lock per > table per database for the duration of the copy. I just created a unlogged > database and tried LOCKING a table in EXCLUSIVE MODE and it worked, so I > guess it will work on your system too. I am running IDS 7.30.UC8. Dont know > whether it was possible to lock a table in exclusive mode in 7.24, but you > may want to try that out on your source database. > > HTH > Sujit > > "Art S. Kagel" <kagel@bloomberg.net> on 07/16/99 09:46:17 AM > > Please respond to kagel@bloomberg.net > > To: informix-list@iiug.org > cc: (bcc: Sujit Pal) > Subject: PUT cursors and Locking w/o Logging > > Question for the community: > > If I have a database without logging (I know! It's one of the last few > of the databases created by my predecessor and I am migrating it to > 7.31 with logging next week, honest) and I am inserting using a PUT > CURSOR. Does the PUT CURSOR hold locks on all of the rows inserted > until the cursor is closed? If the database had logging I would open > the CURSOR...WITH HOLD and COMMIT every N rows to release the locks so > I have assumed that without logging that no locks would be held, except > briefly while the 200 or so row that fit in the PUT buffer are being > inserted on the engine side, and I would have no problem. But suddenly > I am nervous. I have to copy 2 - 20,000,000 row tables from one server > to another and I fear running out of lock (80,000 configured). This is > a critical job that I will not be able to monitor constantly for the > 15 hours or so that it will run, so I just want to increase my level of > comfort. Anyone have any thoughts, definite answers, "How can you be > so dumb?" comments? All graciously and gladly accepted. > > Thanks > Art S. Kagel > > Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

com2k@my-deja.com wrote: > > Art, Sujit, > > Pardon me for busting in with what may be an unrelated question but you > may have a solution for my problem, in brief: > > I need to duplicate a records in a few tables where columns have unique > constraints. > I'm presently retrieving to a cursor, fetching to a %rowtype variable > and modifying the values in the variable. > The problem is INSERTing the variable - is this possible ? Sure why not. I assume you are changing the key values to clone and existing row's non-key columns into a new row. Should be trivail. > Can I open a cursor to INSERT ? Yes it is called a PUT cursor and is declared EXACTLY like a FETCH cursor: EXEC SQL DECLARE putter CURSOR FOR INSERT INTO tablename (col0, col1, col2,...coln) values (?,?,?,...?); You then open it, PUT to it repeatedly and flush it before closing or periodically as you like (it will flush automatically when the PUT buffer fills but you are responsible to flush the last partial buffer either by and explicit "FLUSH putter;" or by implicitely flushing with "CLOSE putter;". So: EXEC SQL OPEN putter; while(condition) { EXEC SQL PUT putter USING var1, var2, var3, ...varn; count++; if ((count % 1000) == 0) EXEC SQL FLUSH putter; } EXEC SQL FLUSH putter; EXEC SQL CLOSE putter; > I'd like to write a procedure to do this where I can send it the Table > Name, the column name, the value for the retrieval and the value it is > to be replaced with in the copy. No problem in ESQL/C or 4GL. Cannot do in a stored procedure. Art S. Kagel > In article <7mon76$s9p$1@news.xmission.com>, > Sujit.Pal@bankofamerica.com wrote: > > > > > > Art > > > > I am not sure if this is a joke, but here goes anyway. > > > > Dont know if the PUT CURSOR will hold locks until the cursor is > closed, but > > you may want to consider locking the source and target tables in > exclusive > > mode by the data copying process. That way it will use only 1 lock per > > table per database for the duration of the copy. I just created a > unlogged > > database and tried LOCKING a table in EXCLUSIVE MODE and it worked, so > I > > guess it will work on your system too. I am running IDS 7.30.UC8. Dont > know > > whether it was possible to lock a table in exclusive mode in 7.24, but > you > > may want to try that out on your source database. > > > > HTH > > Sujit > > > > "Art S. Kagel" <kagel@bloomberg.net> on 07/16/99 09:46:17 AM > > > > Please respond to kagel@bloomberg.net > > > > To: informix-list@iiug.org > > cc: (bcc: Sujit Pal) > > Subject: PUT cursors and Locking w/o Logging > > > > Question for the community: > > > > If I have a database without logging (I know! It's one of the last > few > > of the databases created by my predecessor and I am migrating it to > > 7.31 with logging next week, honest) and I am inserting using a PUT > > CURSOR. Does the PUT CURSOR hold locks on all of the rows inserted > > until the cursor is closed? If the database had logging I would open > > the CURSOR...WITH HOLD and COMMIT every N rows to release the locks so > > I have assumed that without logging that no locks would be held, > except > > briefly while the 200 or so row that fit in the PUT buffer are being > > inserted on the engine side, and I would have no problem. But > suddenly > > I am nervous. I have to copy 2 - 20,000,000 row tables from one > server > > to another and I fear running out of lock (80,000 configured). This > is > > a critical job that I will not be able to monitor constantly for the > > 15 hours or so that it will run, so I just want to increase my level > of > > comfort. Anyone have any thoughts, definite answers, "How can you be > > so dumb?" comments? All graciously and gladly accepted. > > > > Thanks > > Art S. Kagel > > > > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.