How can I backtrack alters in table using systable
The poster wanted to reconstruct a history of DDL changes (columns added/dropped) on a table from the system catalogs, noting systables.version, with Informix auditing not enabled. Replies said this isn't possible: the catalogs hold no change history. In principle the logical logs could be mined to infer approximate timings, but only if all logs since database creation are kept, and it's a lot of work. The same applies to finding when a table/view was last read or written. Recommended options were enabling auditing going forward, scripting changes into your own version tables, or a third-party change-management tool (AGS Sentinel) — none of which recover past history.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello All, I want to backtrack alters/DDL made in the table by using system catalog tables. Basically I want to see that when a column was added/dropped from a particular table. I can see a column named as "version' in the systables which contains the following information according to the documentation: systables.version INTEGER Number that changes when table is altered Now, I want help from you guys to identify that by join which tables, i will be able to built a summary of all changes/alter made in a table in past? Can someone help me in building that audit trail? Please remember that I don't have Informix auditing enabled on this database. Thanks.
You cannot. The history is not tracked in the catalog tables. Art On Feb 24, 2012 7:10 AM, "OMER KHAN" <oskhan@i2cinc.com> wrote: > Hello All, > > I want to backtrack alters/DDL made in the table by using system catalog > tables. > > Basically I want to see that when a column was added/dropped from a > particular > table. > > I can see a column named as "version' in the systables which contains the > following information according to the documentation: > > systables.version INTEGER Number that changes when table is altered > > Now, I want help from you guys to identify that by join which tables, i > will > be able to built a summary of all changes/alter made in a table in past? > > Can someone help me in building that audit trail? > > Please remember that I don't have Informix auditing enabled on this > database. > > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340b9b489d5c04b9b5e758
You can backtrack this through the logs but it would be a significant amount of work and you will not get exact timings of when things happened but you can infer accurate'ish timings. You will not get total history unless you have all the logs since the database was created :) It is soooo much easier to script all your changes and keep track of any entity changes in your own 'version' table(s) - that is trivial Cheers Paul > You cannot. The history is not tracked in the catalog tables. > > Art > On Feb 24, 2012 7:10 AM, "OMER KHAN" <oskhan@i2cinc.com> wrote: > >> Hello All, >> >> I want to backtrack alters/DDL made in the table by using system catalog >> tables. >> >> Basically I want to see that when a column was added/dropped from a >> particular >> table. >> >> I can see a column named as "version' in the systables which contains >> the >> following information according to the documentation: >> >> systables.version INTEGER Number that changes when table is altered >> >> Now, I want help from you guys to identify that by join which tables, i >> will >> be able to built a summary of all changes/alter made in a table in past? >> >> Can someone help me in building that audit trail? >> >> Please remember that I don't have Informix auditing enabled on this >> database. >> >> Thanks. >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --14dae9340b9b489d5c04b9b5e758 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid.
OK. Then the only other option is to enable the auditing, so that moving forward it can be tracked? Also please let me know that if is it possible to know that when a table or view was last accessed for read/write? Thanks.
Not possible, at least not easy. You could go through the logical logs looking for references or you could turn auditing on. But short of that, no way to do it. 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 Fri, Feb 24, 2012 at 9:36 AM, OMER KHAN <oskhan@i2cinc.com> wrote: > OK. > > Then the only other option is to enable the auditing, so that moving > forward > it can be tracked? > > Also please let me know that if is it possible to know that when a table or > view was last accessed for read/write? > > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3026697a316e2604b9b717d3
Hi Omer. See AGS Sentinel Change Management Option at www.serverstudio.com. Regards, Doug Lawry www.oninitgroup.com
Yes, Doug, but that would require managing the schema over time going into the future. IB the OP wants to track the historical changes to the schema in the past. 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 Sat, Feb 25, 2012 at 6:06 AM, DOUG LAWRY <douglawry@hotmail.com> wrote: > Hi Omer. > > See AGS Sentinel Change Management Option at www.serverstudio.com. > > Regards, > Doug Lawry > > www.oninitgroup.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b2e06e7e0e62c04b9d4c412