Archiving user records

Hi, we use Informix 7.x OL on a 4-processor Solaris-x86 in a system that accepts user requests, processes them and (among others) stores a record of each request in a database table. After some time this table fills up and we have to move the data out of the database and archive it e.g. on a tape, where it can be found if needed. Another reason for moving this data is that it is read-only, rarely accessed and it is by far the largest portion of the database, so we don't want to archive it in each archive. My original idea was to have a cron job that checks from time to time how large the table is and when it gets large enough, with a comfortable safety margin, it invokes a stored procedure which - locks the table - renames it - creates a new empty table - releases the lock In this way, the time of inavailability of the table is minimized (is it?), which is necessary as there may be a lot of incoming parallel user requests. Is this the best way to achieve the task? I realized that after I rename the table and before I create the new one, there is a short period when other processes get the error -206 when they try to insert a new tuple. This can be handled in ON EXCEPTION, but it seems awkward (how do I wait say 0.1 seconds before I retry?). The second question is, what is the fastest way to get a table out of Informix - UNLOAD, dbexport, onunload, ...? Cheers, --Micha

Micha Meier wrote in message <36A702E9.F9858C68@ecrc.de>... >Hi, >we use Informix 7.x OL on a 4-processor Solaris-x86 >in a system that accepts user requests, >processes them and (among others) stores a record of each request in a database table. After some >time this table fills How does the table fill ? Does the dbspace become full ?? >up and we have to move the data out of the database and archive >it e.g. on a tape, where it can be found if needed. Another reason for moving this data is that it >is read-only, rarely accessed and it is by far the largest portion of the database, so we don't want >to archive it in each archive. Have you looked into doing alternate level 0,1 and 2 archives ?? >My original idea was to have a cron job that checks from time to time how large the table is and >when it gets large enough, with >a comfortable safety margin, it invokes a stored procedure which >- locks the table >- renames it How would you rename the table ?? >- creates a new empty table >- releases the lock >In this way, the time of inavailability of the table is minimized (is it?), which is necessary as >there may be a lot of >incoming parallel user requests. >Is this the best way to achieve the task? I realized that >after I rename the table and before I create the new one, >there is a short period when other processes get the error -206 >when they try to insert a new tuple. This can be handled >in ON EXCEPTION, but it seems awkward (how do I wait say 0.1 seconds before I retry?). Have you tried SET LOCK MODE TO WAIT ?? > >The second question is, what is the fastest way to get a table >out of Informix - UNLOAD, dbexport, onunload, ...? ONUNLOAD is faster, by far, as it unloads the data page by page rather than record by record like UNLOAD. As its only a single table you want, I wouldn't use dbexport. > >Cheers, > >--Micha

Micha Meier wrote: > > Hi, > we use Informix 7.x OL on a 4-processor Solaris-x86 > in a system that accepts user requests, > processes them and (among others) stores a record of each request in a database table. After some > time this table fills > up and we have to move the data out of the database and archive > it e.g. on a tape, where it can be found if needed. Another reason for [SNIP] Instead of rename/recreate/reload to make the new unburdened table try: ALTER FRAGMENT ON requests INITBY EXPRESSION loaddate < "1998-06-01" IN otherdbspace REMAINDER IN originaldbspace; ALTER FRAGMENT ON requests DETACH otherdbspace old_requests; Since the second part does not have to copy any data it should be quick. > when they try to insert a new tuple. This can be handled > in ON EXCEPTION, but it seems awkward (how do I wait say 0.1 seconds before I retry?). usleep(0,10000) (which is expensive in system call time but for the occassional call it's OK) -or- Use select() with a timeout and no files to watch for it will block until the timer, with microsecond precision, goes off. This is cheap at runtime but a coding hastle. I have a function like usleep that I can give you that actually uses select. Let me know. > The second question is, what is the fastest way to get a table > out of Informix - UNLOAD, dbexport, onunload, ...? onunload is fastest. Art S. Kagel

Art S. Kagel wrote: ... > Instead of rename/recreate/reload to make the new unburdened table try: > > ALTER FRAGMENT ON requests INIT> BY EXPRESSION > loaddate < "1998-06-01" IN otherdbspace > REMAINDER IN originaldbspace; > > ALTER FRAGMENT ON requests DETACH otherdbspace old_requests;> > Since the second part does not have to copy any data it should be quick. I first though this is a good idea, but there is a catch: when FRAGMENT INIT is specified, all rows of the table are reread to put them into the right fragments, even if the fragment expression puts all of them trivially into one dbspace (comparing dates is way too complex, any condition that fails is ok). As the table is quite big, this approach will lock it for a long time. It seems that renaming the old table and creating a fresh new one is still better, isn't it? --Micha

Sean Kelsey wrote: > Micha Meier wrote in message <36A702E9.F9858C68@ecrc.de>... <snip> > >up and we have to move the data out of the database and archive > >it e.g. on a tape, where it can be found if needed. Another reason for > moving this data is that it > >is read-only, rarely accessed and it is by far the largest portion of the > database, so we don't want > >to archive it in each archive. <snip> > >The second question is, what is the fastest way to get a table > >out of Informix - UNLOAD, dbexport, onunload, ...? > > ONUNLOAD is faster, by far, as it unloads the data page by page rather than > record by record like UNLOAD. As its only a single table you want, I > wouldn't use dbexport. Yes, onunload is fast, but one thing to keep in mind is that onload can only restore files from the same version. That is, if you have a whole bunch of onunload'ed tables from version 7.22, and you upgrade your server to 7.30, you won't be able to onload them. (Well, you might, but you might not.) You would have to create a new instance with the old 7.22 (assuming you still have it installed, or have the installation tape, and that you marked your onunload tape with the version that you used) in order to re-load that table. I'd unload to Ascii, myself. June (not a huge fan of onunload/onload, but writing arcunload will do that to you) -- june_t@hotmail.com Grounded in Palo Alto, living on M&M's (plain)