242: Could not open database table

Hello, IDS Version 11.50.FC8 (Primary) and has a secondary and RSS attached OS : SunOS I have a job which rebuilds a table every weekend. It unloads some 10 million records, drops the table, create it, load the data and creates indexes. However, I get below error while creating the indexes although nobody else is using the table 242: Could not open database table (informix.tqh_temp). 113: ISAM error: the file is locked. Error in line 11Near character position 8 242: Could not open database table (informix.tqh_temp). 113: ISAM error: the file is locked. Error in line 13Near character position 38 Is this because of replication? Is there a way to avoid the error? Thanks in advance. Nitin

Just a side question. Why are you unloading, dropping, recreating, reloading, and reindexing the table every weekend? Is it being loaded with completely new data? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 21, 2012 at 10:50 AM, NITIN MATHUR <nitin_maths@rediffmail.com>wrote: > Hello, > > IDS Version 11.50.FC8 (Primary) and has a secondary and RSS attached > OS : SunOS > > I have a job which rebuilds a table every weekend. It unloads some 10 > million > records, drops the table, create it, load the data and creates indexes. > However, I get below error while creating the indexes although nobody else > is > using the table > > 242: Could not open database table (informix.tqh_temp).> > 113: ISAM error: the file is locked. > Error in line 11> Near character position 8 > > 242: Could not open database table (informix.tqh_temp).> > 113: ISAM error: the file is locked. > Error in line 13> Near character position 38 > > Is this because of replication? Is there a way to avoid the error? > > Thanks in advance. > > Nitin > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340cad90ef1604c08d4a06

My suggestion is to set the onconfig variable LOG_INDEX_BUILDS to 1. There are two ways indexes get shipped to the secondary, the default me= thod (when LOG_INDEX_BUILDS is set to 0) is asynchronous to the application = and the index remains locked while it is being shipped. Besides being asynchronous it also only places into the logical logs a single record indicating that we are creating an index. . Because of the asynchrono= us nature of this action the second create index encounters a lock. If y= ou enable LOG_INDEX_BUILDS then the index is shipped to the secondary synchronously as it is being built by logging each index page into your= logical log files. So by enabling this parameter you will use more log= ical log space. My only other suggestion would be to enable an online index build as th= is will also trigger the index to be shipped synchronously. CREATE INDEX IF NOT EXISTS ix1 on t1(c1) ONLINE; John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 05/21/2012 07:50:08 AM: > From: "NITIN MATHUR" <nitin_maths@rediffmail.com> > To: ids@iiug.org > Date: 05/21/2012 07:52 AM > Subject: 242: Could not open database table [27179] > Sent by: ids-bounces@iiug.org > > Hello, > > IDS Version 11.50.FC8 (Primary) and has a secondary and RSS attached > OS : SunOS > > I have a job which rebuilds a table every weekend. It unloads some 10= million > records, drops the table, create it, load the data and creates indexe= s. > However, I get below error while creating the indexes although nobody= else is > using the table > > 242: Could not open database table (informix.tqh_temp).> > 113: ISAM error: the file is locked. > Error in line 11> Near character position 8 > > 242: Could not open database table (informix.tqh_temp).> > 113: ISAM error: the file is locked. > Error in line 13> Near character position 38 > > Is this because of replication? Is there a way to avoid the error? > > Thanks in advance. > > Nitin > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=

Hi Art, There are some 3-4 million records inserted in this table every day and every night, 1 days data is copied on a seperate (historic) instance and on a weekend, we have to get rid of 5 days of data and leave only 2 days of data in the table. I do this by unloading 2 days of current data then dropping and recreating the table and loading the data I unloaded. regards, Nitin

Hi John, Since we are using RSS, LOG_INDEX_BUILDS is already set to 1. Also, my script to rebuild table uses split_schema script to generate SQLs to create table, indexes (after loading data), I am not sure whether I will be able to modify it to add ONLINE bit CREATE INDEX IF NOT EXISTS ix1 on t1(c1) ONLINE regards, Nitin

Got it. That explanation opens a couple of ideas: 1. Recreate the table with seven daily partitions. Each day add a new partition for that night's data load. On the weekend detach the oldest 5 partitions (actually you could pre-add the new partitions for the following 5 days at that time). Once the partitions are detached, and data loaded into the next partition for the next day, you can unload the data from the detached partitions and reload it into the historical instance at your leisure. 2. Upgrade to 11.70 so you can use RANGE/INTERVAL partitioning to automatically extend the table with a new partition each day and then only have to worry about detaching the oldest ones. With these methods, as long as the indexes are fragmented on the same schema as the table (so without an explicit fragmentation scheme) you will not have to be rebuilding indexes every day, eliminating the original problem and and much of your nightly and weekly processing time. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 21, 2012 at 1:35 PM, NITIN MATHUR <nitin_maths@rediffmail.com>wrote: > Hi Art, > > There are some 3-4 million records inserted in this table every day and > every > night, 1 days data is copied on a seperate (historic) instance and on a > weekend, we have to get rid of 5 days of data and leave only 2 days of > data in > the table. I do this by unloading 2 days of current data then dropping and > recreating the table and loading the data I unloaded. > > regards, > Nitin > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec51a8922b41d2f04c08fa42e

Have you tried "set lock mode to wait". You might be hitting locks held by the replication threads. Have you run onstat -k while the job is running to see what the lock is and who holds it? Also, does every index build for the table fail or just some of them?

Thanks Art. If I partition the table, it will be on a datetime column (qh_timestamp datetime year to fraction(3)) and will be a expression based so it should look like qh_timestamp between "2012-05-22 00:00:00" and "2012-05-22 23:59:59" and so on for each day of the week. Is there a script anywhere on iiug which I can use to generate a alter table SQL to do as above? regards, Nitin

Nope, I haven't tried lock mode to wait. The index create job does create 2-3 indexes and then start failing. regards, Nitin

Better to do a DATE or DATETIME type column but partition on DAY(partition_date) = 0 IN ... DAY(partition_date) = 1 IN ... DAY(partition_date) = 2 IN ... Or best add a partition_num column that is SMALLINT and populated it with MOD(TODAY,7) in an insert trigger and partition on that column. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, May 22, 2012 at 10:03 AM, NITIN MATHUR <nitin_maths@rediffmail.com>wrote: > Thanks Art. > > If I partition the table, it will be on a datetime column (qh_timestamp > datetime year to fraction(3)) and will be a expression based so it should > look > like qh_timestamp between "2012-05-22 00:00:00" and "2012-05-22 23:59:59" > and > so on for each day of the week. > > Is there a script anywhere on iiug which I can use to generate a alter > table > SQL to do as above? > > regards, > > Nitin > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340a99aeefd604c0a10c2e