3

Hey hoping someone can assist or at least point me in the right direction.

I'm looking to add some Generate always audit fields into some of our new tables to assist in the auditing process, from what i have found only special registers can be used on this generate always fields.

From IBM site I have found most of what is needed but the one field in particular that we always use is Program Name. (IE which program made the update to the table)

For the life of me I cannot figure this out. We currently use the Program-Status Data Structure and specify that value there, but this then defeats the purpose as the other 6 audit fields will always populate but this one wont.

I have also tired the following but these are for SQL Procedures and Functions and do not pick up if an RPG program does the Update/Insert

SYSIBM.ROUTINE_SCHEMA
SYSIBM.ROUTINE_SPECIFIC_NAME
SYSIBM.ROUTINE_TYPE


CREATE TABLE TSTAUDIT.policyInfo 
  ( policy_id  CHAR(4) NOT NULL, 
    coverage   INT NOT NULL, 
    sys_start  TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN , 
    sys_end    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END , 
    Change_Type CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION),                        
    Change_User VarCHAR(128) GENERATED ALWAYS AS (SESSION_USER),                                        
    Change_JobName VARCHAR(28) GENERATED ALWAYS AS (QSYS2.JOB_NAME) , 
    Change_APPLNAME VARCHAR(255) GENERATED ALWAYS AS (CURRENT CLIENT_APPLNAME),
    Change_RSCHEMA VARCHAR(128) GENERATED ALWAYS AS (SYSIBM.ROUTINE_SCHEMA),
    Change_RName VARCHAR(128) GENERATED ALWAYS AS (SYSIBM.ROUTINE_SPECIFIC_NAME),
    Change_RType CHAR(1) GENERATED ALWAYS AS (SYSIBM.ROUTINE_TYPE));

I Have also looked into (CURRENT CLIENT_APPLNAME) (CURRENT CLIENT_PROGRAMID) (CURRENT CLIENT_USERID) (CURRENT CLIENT_WRKSTNNAME)

But these need to be set for each connection, meaning that each new program called would have to call the SYSPROC.WLM_SET_CLIENT_INFO procedure (or call respective API) adding additional overhead to the process.

Any advise would be greatly appreciated.

***** Additional information

Something I forgot to mention is that this working for incoming connections because these CURRENT CLIENT_ fields can be set as part of the connection string.

The issue i am having is for the internal running jobs (Interactive and Batch) where i would have to set these for each program that is called.

Now doing this is not such a big issue as we can make it a standard when using these files these fields have to be set, Although the issue i have found now is switching between programs. If Program A and B both write to File X. When Program A starts it sets the CLIENT_PROGRAMID Field Does some things and then calls Program B. Program B sets the CLIENT_PROGRAMID field and does what is needed. When control is returned to Program A the CLIENT_PROGRAMID is still "PROGRAM B" meaning any updates that Program A does would reflect incorrectly each time it does a Data change after program B has been called.

Really hope that's not confusing.

Michael Knight
  • 31
  • 3
  • "additional overhead to the process" -- how bad is the overhead (compared with the overall time it takes to establish a new session) that it causes concern? – mustaccio Dec 10 '21 at 13:47
  • So there is no "new connection" the audits im talking about are more the Interactive sessions and Batch processes that run on the actual box itself. Sorry i should have mentioned that. So when i user logs on they can go though a variety of options to make updates. So one connection just different processes. – Michael Knight Dec 10 '21 at 14:43
  • So you are ok with adding this info to the connection string, when you have a new connection, but not willing to call the api when there is no external connection? Seems that is the way it needs to be done, and the overhead would be the same either way. – jmarkmurphy Dec 13 '21 at 14:36
  • Its not that we are unwilling to do so, If you read the Additional information added to the question you can see there is another issue with us setting this value. There should be a way to have this field stamped on table changes, if you look at the Journals that get written the data is there so it is available at time. Just looking to see if anyone knows of a way for us to use it. – Michael Knight Dec 14 '21 at 06:08

0 Answers0