User Defined Env. Var.

Good Morning, Is there a way to set a user defined environment variable that the engine would recognize? More specifically... I have a 2 hour maintenance window. I have tables that I cannot do what needs to be done in 2 hours. I want to put some logic in a trigger that will check this variable and take a different route than normal if it is set. Something like, if set insert into different tableelse do insert into normal table That is a very simple example however I think it describes what I want to do. TIA, Dan

Hello. Instead of using OS variables, or even scripts, I suggest you to use a database field, on a table with 1 row, just to "flag" you request, understand? Then you could easily manipulate it´s value according to your process levels, right? Best regards. Em 11/01/2011 08:21, DAN MUELLER escreveu: > Good Morning, > > Is there a way to set a user defined environment variable that the engine > would recognize? More specifically... > > I have a 2 hour maintenance window. I have tables that I cannot do what needs > to be done in 2 hours. I want to put some logic in a trigger that will check > this variable and take a different route than normal if it is set. Something > like, > > if set > > insert into different table> else > > do insert into normal table > > That is a very simple example however I think it describes what I want to do. > > TIA, > Dan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11

We considered that however did not want to add the overhead of another select every time this table is inserted into.

Hmm, tough one. OK, try this, create a small stored procedure to set or clear the value in a BOOLEAN global variable. The trigger can be set up to call a stored procedure to perform one of the other of the inserts depending on the value in the global variable. Then the session just has to call the procedure to set the global or to clear its value. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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, Jan 11, 2011 at 7:21 AM, DAN MUELLER <dan.mueller@trnswrks.com>wrote: > Good Morning, > > Is there a way to set a user defined environment variable that the engine > would recognize? More specifically... > > I have a 2 hour maintenance window. I have tables that I cannot do what > needs > to be done in 2 hours. I want to put some logic in a trigger that will > check > this variable and take a different route than normal if it is set. > Something > like, > > if set > > insert into different table> else > > do insert into normal table > > That is a very simple example however I think it describes what I want to > do. > > TIA, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5a78f1107cf0499919471

DAN MUELLER wrote: ================================================================================ Good Morning, Is there a way to set a user defined environment variable that the engine would recognize? More specifically... I have a 2 hour maintenance window. I have tables that I cannot do what needs to be done in 2 hours. I want to put some logic in a trigger that will check this variable and take a different route than normal if it is set. Something like, if set insert into different tableelse do insert into normal table That is a very simple example however I think it describes what I want to do. TIA, Dan ================================================================================ Response: It looks like you want to accept new records for your table(s) even while doing maintenance that requires exclusive use. Presumably incorporating the new records into the primary table once the maintenance is complete. Renaming the table during maintenance and putting a different table in its place could be an option. Something like... rename table normal to under_maintenance rename table different to normal {maintenance steps against under_maintenance} rename table normal to different rename table under_maintenance to normal insert into normal select * from differenttruncate different You may not need to change the insert process at all. Although, you would certainly want error handling around the renames for when you hit non-exclusive access errors. Dave Griffen

DAVE GRIFFEN wrote: ================================================================================ It looks like you want to accept new records for your table(s) even while doing maintenance that requires exclusive use. Presumably incorporating the new records into the primary table once the maintenance is complete. Renaming the table during maintenance and putting a different table in its place could be an option. Something like... rename table normal to under_maintenance rename table different to normal {maintenance steps against under_maintenance} rename table normal to different rename table under_maintenance to normal insert into normal select * from differenttruncate different You may not need to change the insert process at all. Although, you would certainly want error handling around the renames for when you hit non-exclusive access errors. ================================================================================ Update: On second thought, utilizing a synonym instead of renames would be cleaner and seems to avoid some of the non-exclusive access errors... drop synonym normal create synonym normal for secondary_copy{maintenance steps against primary_copy} drop synonym normal create synonym normal for primary_copy insert into primary_copy select * from secondary_copytruncate secondary_copy Again, programs inserting records could all be pointed at "normal".

Hello, Maybe you can use a view combined with a 'instead of insert' trigger on that view. Than in the trigger, using a variable as suggested by Art, you can select the real table to fo your inserts HTH, E. Ionescu