Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

How to log changes of a table - the smart way

  • 1.  How to log changes of a table - the smart way

    Posted Wed March 11, 2020 09:20 AM
    Hello everyone,

    we have a query on how to log changes to a table using Informix. The background is, we want to know how long it takes a certain process, which involves various changes to a table, to complete. I don't mean a SQL statement or query to be run through, but process steps of the application that uses the table.
    What would be the smartest way to solve this? Informix 11.70 Workgroup Edition Linux

    Thank you

    ------------------------------
    Marc Demhartner
    ------------------------------

    #Informix


  • 2.  RE: How to log changes of a table - the smart way

    Posted Wed March 11, 2020 10:12 AM
    Edited by System Admin Fri January 20, 2023 04:23 PM
    Hello Marc,
    I'm not sure I understood the question correctly, but ...
    If you want to know the execution time of your Informix application code, how about setting breakpoints at the desired points and measuring the execution time?
    Is it difficult to modify your code?

    The parts of the database that can be monitored are information about the usage of resources such as instances, tables, and indexes.
    For example, isreads, iswrites, bufreads, bufwrites, etc. in the sysmaster:sysptprof or sysprofile tables.
    The execution time of the query is not a question, so the SQLTRACE function won't help.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    Original Message


  • 3.  RE: How to log changes of a table - the smart way

    Posted Wed March 11, 2020 10:31 AM
    Edited by System Admin Fri January 20, 2023 04:15 PM
    Thanks for your help - sorry if I misspoke. I don't mean the runtime of a SQL statement or anything like that. I mean e.g. a table that is changed several times by the user in our application. The user himself goes through several processing steps. These lead to changes in the table. We would now like to use Informix to record how quickly the changes from A to Z (i.e. all application steps) have been processed.


    ------------------------------
    Marc Demhartner
    ------------------------------

    Original Message


  • 4.  RE: How to log changes of a table - the smart way

    Posted Wed March 11, 2020 10:35 AM
    It sounds like one option could be trigger based writes to an audit type table you could create to log this information, if you want a database server only option without code changes.

    Hal Maner
    M Systems International, Inc.



    Original Message


  • 5.  RE: How to log changes of a table - the smart way

    Posted Wed March 11, 2020 10:40 AM
    Yes, exactly, that was also my idea, but is it avalible in 11.70 WE?

    ------------------------------
    Marc Demhartner
    ------------------------------

    Original Message


  • 6.  RE: How to log changes of a table - the smart way

    Posted Wed March 11, 2020 10:46 AM
    Sure, triggers and stored procedures go waaay back to the earliest versions of Informix much older than 11.70 and the editions do not make a difference on this.  Triggers on inserts, updates, deletes.

    Hal



    Original Message


  • 7.  RE: How to log changes of a table - the smart way

    Posted Wed March 11, 2020 10:50 AM
    Here are some examples of auditing using triggers.

    https://www.ibm.com/developerworks/data/library/techarticle/dm-0410roy/index.html

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    Original Message


  • 8.  RE: How to log changes of a table - the smart way

    Posted Wed March 11, 2020 10:54 AM
    Thank you both for your help. It will give it a try :)

    ------------------------------
    Marc Demhartner
    ------------------------------

    Original Message


  • 9.  RE: How to log changes of a table - the smart way

    Posted Wed March 11, 2020 11:02 AM
    Marc:

    Yes, triggers are available in all versions and editions of Informix since v4.01.

    There is one rub, however, CURRENT will always return the same time within a transaction - the time the transaction began. That is an ANSI SQL requirement. Your trigger could query the shm_curtime column in sysmaster:sysshmvals, but that is full second resolution. If you need subsecond resolution to your audit triggers, then you will have to create a User Defined Function written in C or Java to return the actual system time with subsecond resolution.

    I have the code for such a beasty. Email me directly if you want it.

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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.






    Original Message


  • 10.  RE: How to log changes of a table - the smart way

    Posted Wed March 11, 2020 12:10 PM
    Isn't that per invokation of an SPL UDR, rather than per transaction?

    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 11.  RE: How to log changes of a table - the smart way

    Posted Wed March 11, 2020 12:14 PM

    Andreas:

    You are correct. Duh, my bad. Not enough coffee today.

    Art



    ------------------------------
    Art Kagel
    ------------------------------

    Original Message


Related Content