IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 4 replies
  • Latest Post - ‏2019-07-08T11:40:06Z by robberendt
Ajay Kulkarni
Ajay Kulkarni
26 Posts

Pinned topic Program Id via SQL Trigger

‏2019-05-31T02:31:10Z |

Hello, i am implementing a trigger on a table that will capture the audit information on that table (who inserted, updated, deleted).

I am going to use SQL Create Trigger. In my audit file i need the  program name that did the actual update, job name etc. How should the SQL trigger capture the actual program that updated the base file?

Example - Base File is say F55BASE that has the trigger. Say a program called P55PGM updates a row in F55BASE, the SQL trigger should capture P55PGM as teh program name that updated the file. 

  • robberendt
    robberendt
    84 Posts

    Re: Program Id via SQL Trigger

    ‏2019-06-17T20:34:55Z  

    So what you are looking for is the same information you would get from DSPJRN, if you journaled the file?  

    I wonder if you can use special registers?  

    https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/db2/rbafzspecreg.htm

    If the layout of the file can be altered you could add an additional column which defaults to that special register.  

    See also system period temporal tables at  

    https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzahf/rzahftemporaltable.htm

  • robberendt
    robberendt
    84 Posts

    Re: Program Id via SQL Trigger

    ‏2019-06-17T20:36:47Z  

    So what you are looking for is the same information you would get from DSPJRN, if you journaled the file?  

    I wonder if you can use special registers?  

    https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/db2/rbafzspecreg.htm

    If the layout of the file can be altered you could add an additional column which defaults to that special register.  

    See also system period temporal tables at  

    https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzahf/rzahftemporaltable.htm

    You could create a lot of hidden columns which automatically default to these special registers.

    If you used system period temporal tables the history table would be very impressive.

  • Andreas Bott
    Andreas Bott
    1 Post

    Re: Program Id via SQL Trigger

    ‏2019-07-08T11:28:54Z  

    You could create a lot of hidden columns which automatically default to these special registers.

    If you used system period temporal tables the history table would be very impressive.

    Hi,

     

    if i understood right, there is an older Post about this Problem.

    https://www.ibm.com/developerworks/community/forums/html/topic?id=6cd99fee-b88c-4c60-87ee-6bd68844175d

     

    Perhaps this could help you.

     

    Greetz

  • robberendt
    robberendt
    84 Posts

    Re: Program Id via SQL Trigger

    ‏2019-07-08T11:40:06Z  

    Hi,

     

    if i understood right, there is an older Post about this Problem.

    https://www.ibm.com/developerworks/community/forums/html/topic?id=6cd99fee-b88c-4c60-87ee-6bd68844175d

     

    Perhaps this could help you.

     

    Greetz

    See also STACK_INFO at

    https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/DB2%20for%20i%20-%20Services