A rogue update hard to find.

I have been chasing a problem in an old long standing 4GL system. SPL has been added over the years. Weblogic and others (perl and the like) SQL have been given acces the system over the years also. One the main tables takes a char as the main way into the system (telephone number) No records are deleted they are just terminated with an end date; Over the years one telephone number comes to have many entries in the telephone table. The problem is that something is updating this end date of some of these rows with ( I think ) "current". I do not have informix rights to the system and Informix admin caught me a few updates from `onstat`. All those caught are legitimate updates. At the moment there is one primary key for an old telephone record that is hit (updated) about 20 time during the day and none in the wee hours. I run a `ps` over several days and find that all the binary programs that do not run during the night are not the problem, (Hours of code searching). There is little (nothing to be frank) to stop one of these parasitic scripts/accesses from doing what is wants.

Hi. You said there's an update triggers. I guess that triggers writes some data about update operation into another table. You should be sure that log table includes data as date and hour when update happens, and user (both of them can be get from informix functions), so you can not only identify those events, but moments of time and users also. Then you'll be able to match this info with other logs as ps and so on. There's a function for audit informix in order to record detailed logs about access and activity on database,but I don't know exactly how to make it work, but people on DBA department may know better. In order to avoid this events, you may consider identify all user who really should do this update, and then ask for revoke permission to the other ones. In this way, you should worry for user with dba permission (ideally, only "informix"). Another approach consists on making a store procedure owned by dba user which only do this operation, calling it instead of direct update on table in app code, and revoke all permission, so nobody but dba user will do this for other means different of sp, but this imply a lot of developer work. Another idea which you may consider, ask your DBA for identify every store procedure, trigger and constraint related with that table, maybe there's a piece of code on sp's or triggers which do the undesirable task. Hope this helps Omar Muñoz --- On Fri, 8/14/09, ian <ipellew@yahoo.com> wrote: > From: ian <ipellew@yahoo.com> > Subject: A rogue update hard to find. > To: informix-list@iiug.org > Date: Friday, August 14, 2009, 9:47 AM > I have been chasing a problem in an > old long standing 4GL system. > SPL has been added over the years. > Weblogic and others (perl and the like) SQL have been given > acces the > system over the years also. > > One the main tables takes a char as the main way into the > system > (telephone number) > No records are deleted they are just terminated with an end > date; > Over the years one telephone number comes to have many > entries in the > telephone table. > > The problem is that something is updating this end date of > some of > these rows with ( I think ) "current". > I do not have informix rights to the system and Informix > admin caught > me a few updates from `onstat`. > All those caught are legitimate updates. > At the moment there is one primary key for an old telephone > record > that is hit (updated) about 20 time during the day and none > in the wee > hours. > I run a `ps` over several days and find that all the binary > programs > that do not run during the night are not the problem, > (Hours of code > searching). > > There is little (nothing to be frank) to stop one of these > parasitic > scripts/accesses from doing what is wants. > >From the customers point of veiw its my problem (app > support). > > This is a long time, constantly (typical I suppose) updated > production > system where none of the testing systems fail. Well, to be > fair, none > of these system come anywhere near the real live system. > > All I can come up with now is to try and talk our Infx > Admin to put > more effort into helping with the problem. > > There is a upd/ins trigger on this table, and is how I can > run > statistics on these rogue updates. Mind that took weeks to > get > archived and restarted as it was huge and un-usable (no > indexes). > > Now the question. > What can I ask these (one) guys to do for me! > Or any other suggestions ? > > . > . > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On Aug 14, 3:47 pm, ian <ipel...@yahoo.com> wrote: > I have been chasing a problem in an old long standing 4GL system. > SPL has been added over the years. > Weblogic and others (perl and the like) SQL have been given acces the > system over the years also. > > One the main tables takes a char as the main way into the system > (telephone number) > No records are deleted they are just terminated with an end date; > Over the years one telephone number comes to have many entries in the > telephone table. > > The problem is that something is updating this end date of some of > these rows with ( I think ) "current". > I do not have informix rights to the system and Informix admin caught > me a few updates from `onstat`. > All those caught are legitimate updates. > At the moment there is one primary key for an old telephone record > that is hit (updated) about 20 time during the day and none in the wee > hours. > I run a `ps` over several days and find that all the binary programs > that do not run during the night are not the problem, (Hours of code > searching). > > There is little (nothing to be frank) to stop one of these parasitic > scripts/accesses from doing what is wants. > From the customers point of veiw its my problem (app support). > > This is a long time, constantly (typical I suppose) updated production > system where none of the testing systems fail. Well, to be fair, none > of these system come anywhere near the real live system. > > All I can come up with now is to try and talk our Infx Admin to put > more effort into helping with the problem. > > There is a upd/ins trigger on this table, and is how I can run > statistics on these rogue updates. Mind that took weeks to get > archived and restarted as it was huge and un-usable (no indexes). > > Now the question. > What can I ask these (one) guys to do for me! > Or any other suggestions ? > > . > . If they are adimant its "your problem" then change the name of the table and any reference to it in your application then wait and see who starts to complain

On 14 Aug, 15:47, ian <ipel...@yahoo.com> wrote: > I have been chasing a problem in an old long standing 4GL system. > SPL has been added over the years. > Weblogic and others (perl and the like) SQL have been given acces the > system over the years also. > > One the main tables takes a char as the main way into the system > (telephone number) > No records are deleted they are just terminated with an end date; > Over the years one telephone number comes to have many entries in the > telephone table. > > The problem is that something is updating this end date of some of > these rows with ( I think ) "current". > I do not have informix rights to the system and Informix admin caught > me a few updates from `onstat`. > All those caught are legitimate updates. > At the moment there is one primary key for an old telephone record > that is hit (updated) about 20 time during the day and none in the wee > hours. > I run a `ps` over several days and find that all the binary programs > that do not run during the night are not the problem, (Hours of code > searching). > > There is little (nothing to be frank) to stop one of these parasitic > scripts/accesses from doing what is wants. > From the customers point of veiw its my problem (app support). > > This is a long time, constantly (typical I suppose) updated production > system where none of the testing systems fail. Well, to be fair, none > of these system come anywhere near the real live system. > > All I can come up with now is to try and talk our Infx Admin to put > more effort into helping with the problem. > > There is a upd/ins trigger on this table, and is how I can run > statistics on these rogue updates. Mind that took weeks to get > archived and restarted as it was huge and un-usable (no indexes). > > Now the question. > What can I ask these (one) guys to do for me! > Or any other suggestions ? > > . > . See www.lintel.co.uk/infotrace. If this is a hammer to crack a nut then if you contact me directly we may be able to do some specific log scraping for you. David Linthwaite Lintel Software Consultancy Ltd.