Re: Safe Updates of a bazillion rows

> If I want to update one column (all rows) in a table that has, say, > a million rows, with two indices, and I dont want to blow out my > lock table, or log files, is there a safe way, short of turning > off logging and doing an exclusive lock and begin/commit work? > > We are beginning to use informix for projects that have huge databases, > and am getting worried. I'd hate to write esql/c or 4gl code that counts > transactions for every little diddly issue like this. There must be > a better way. > > I could use your ideas quickly, as I plan on being on site very shortly. > > Naomi Walker (aka N7FSA) Anasazi Inc. Phoenix, Arizona > naomi%anasaz.UUCP@asuvax.eas.asu.edu Naomi, This is a general problem with mass transactions that unfortunately requires programming to fix. Taking off logging and locking the table does work of course but, as you pointed out, is a hassle and forces the database to go single user while the update is operational. What I have done in the past is write some generalised routines along the lines of the dbload routine. Basically you write a routine that can be passed a select statement that will select the records to be updated/deleted, a transform statement of some form plus possibly a file containing the changes to be made in the order in which the rows will be retrieved, the database to which it is to be applied and the number of rows to update before doing a commit. Then within the function you parse the select statement into a cursor for update with hold, open the cursor. Then begin work and fetch and update/delete (depending on the parameters passed and if using a file of changes a matching key) keeping a count of rows updated. Once the count reaches the commit level you do a commit and begin work and continue. The hold cursor keeps your place in the selected set. You can either write a number of these functions for slightly different purposes or try and write a very general one. These functions solve the limit on locks and overrunning logical logs problems though you will probably need to set the database to continuous archiving. It is also necessary to consider how you are going to deal with concurrency problems and recovery in the event of database crash. For concurrency I normally use wait for locked row and keep an eye on how long the job is taking. For recovery you need to consider how to either recover the original table or restart at the last commit point as the automatic rollback will only return the table to its condition at the last commit which may be halfway through the full update. Hope this helps. Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------