DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?

Kal Sub

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?

Hi,

I am trying to capture update statements run by some userids and store it in an "audit" table. The actual requirement is just to capture updated columns and values for audit purposes, but since I have quite a few tables to capture, I can't have a single target audit table matching the source tables, hence I am thinking of capturing the actual SQL statement and storing it.

One simple solution of course is to have one target audit table for each of the source tables, but that would just end up in a lot of audit tables, hence I am trying to get them all in a single table.

Any other potential solutions also welcome. (DB2 Audit trace itself has some limitations like capturing only the first SQL statement in a UOW, hence that wouldn't meet my need).

Thanks

Regards

Kals

Jørn Thyssen

RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Kal Sub)

Hi Kal,

I assume you’re on z/OS?

Do you have a vendor log analysis tool? Such tools normally have an audit feature that allows you to capture changes for a specified set of tables and load the data into a single audit table. 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

Kal Sub

RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Jørn Thyssen)

Hi Jørn,

Yes, this is for Z/OS. Sorry, I don't have a vendor log analysis tool 

Regards

Kals


In Reply to Jørn Thyssen:

Hi Kal,

I assume you’re on z/OS?

Do you have a vendor log analysis tool? Such tools normally have an audit feature that allows you to capture changes for a specified set of tables and load the data into a single audit table. 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

Rob Barbour

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Kal Sub)
Hi Kal,

You may want to look at Log Analyzers that have audit function. One such
product is our ULT product.... see
http://www.esaigroup.com/products/ultdb2log.htm for more info. ULT is a
full function, reasonably priced tool.

HTH,
Rob Barbour, ESAi

On 12/18/2018 5:49 PM, Kal Sub wrote:
>
> Hi,
>
> I am trying to capture update statements run by some userids and store
> it in an "audit" table. The actual requirement is just to capture
> updated columns and values for audit purposes, but since I have quite
> a few tables to capture, I can't have a single target audit table
> matching the source tables, hence I am thinking of capturing the
> actual SQL statement and storing it.
>
> One simple solution of course is to have one target audit table for
> each of the source tables, but that would just end up in a lot
> of audit tables, hence I am trying to get them all in a single table.
>
> Any other potential solutions also welcome. (DB2 Audit trace itself
> has some limitations like capturing only the first SQL statement in a
> UOW, hence that wouldn't meet my need).
>
> Thanks
>
> Regards
>
> Kals
>
>
> -----End Original Message-----
--
Signature Best Regards,
Rob Barbour
Enterprise Systems Associates, Inc ("ESAi")
UCF Research Park
3259 Progress Drive
Orlando,  Florida 32826    USA
Toll Free: 1-866-GO-4-ESAI (1-866-464-3724)
http://www.ESAIGroup.com/products
http://www.ESAIGroup.com/products
Visit us at the next IDUG & SHARE Conferences

twitter.com/ESAiSoftware http://www.twitter.com/ESAiSoftware


z Performance... Productivity for DB2... Services...
BCV4 -  Db2, SAP, PeopleSoft Clones/Refreshes in Minutes vs Days
BCV5/BCV6 -  On-Demand Db2 Refresh / Migrate in 1/10th Time & Effort
XDM - Test Data Mgmt & Masking for Db2,Oracle, SQL Server et.al.
ULT4DB2 - Faster, Better Value in DB2 Log Analyzers w/ PROP, & Audit
BPA4DB2 - Save $, Improve DB2 Performance with Buffer Pool Analyzer
XM4DB2 - Proactive Approach for Db2,Performance, & Dynamic SQL
SQLQC - Find, Analyze, Improve SQL Quality Control and Performance

COST Optimization - Lower Mainframe MLC Software Costs
INSPECT-CPU - Improve CICS Application Performance with ICPU





David Baldon

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Kal Sub)
Since this is for audit purposes it seems you use a log processing tool to post process the logs to capture the SQL UPDATE statements or simply generate a report showing the before and after values of the columns you’re interested in. The audit requirement regarding how long the data must be retained may influence your decision on what you use the log processing tool to produce. Also consider the volume of expected updates.

...David

From: Kal Sub [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2018 4:49 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?


Hi,

I am trying to capture update statements run by some userids and store it in an "audit" table. The actual requirement is just to capture updated columns and values for audit purposes, but since I have quite a few tables to capture, I can't have a single target audit table matching the source tables, hence I am thinking of capturing the actual SQL statement and storing it.

One simple solution of course is to have one target audit table for each of the source tables, but that would just end up in a lot of audit tables, hence I am trying to get them all in a single table.

Any other potential solutions also welcome. (DB2 Audit trace itself has some limitations like capturing only the first SQL statement in a UOW, hence that wouldn't meet my need).

Thanks

Regards

Kals

-----End Original Message-----

Kal Sub

RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to David Baldon)

Thank you to the people who have responded already. A lot of you are suggesting Log Analysis tools. I think that would be useful, but right now not feasible, especially considering I could implement a (non-elegant) solution to achieve the goals. I was hoping there would be some sort of "Get Current SQL" statement that would help me... or, some other way of collating the pre-update and post-update values and write them to a table.

Well, I will just wait for a while and see if anything else comes up. 

Regards

Kals

In Reply to David Baldon:

Since this is for audit purposes it seems you use a log processing tool to post process the logs to capture the SQL UPDATE statements or simply generate a report showing the before and after values of the columns you’re interested in. The audit requirement regarding how long the data must be retained may influence your decision on what you use the log processing tool to produce. Also consider the volume of expected updates.

...David

From: Kal Sub [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2018 4:49 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?


Hi,

I am trying to capture update statements run by some userids and store it in an "audit" table. The actual requirement is just to capture updated columns and values for audit purposes, but since I have quite a few tables to capture, I can't have a single target audit table matching the source tables, hence I am thinking of capturing the actual SQL statement and storing it.

One simple solution of course is to have one target audit table for each of the source tables, but that would just end up in a lot of audit tables, hence I am trying to get them all in a single table.

Any other potential solutions also welcome. (DB2 Audit trace itself has some limitations like capturing only the first SQL statement in a UOW, hence that wouldn't meet my need).

Thanks

Regards

Kals

-----End Original Message-----

Tony Saul

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Kal Sub)
Without a Log Analyzer or similar, I would look at using Triggers for the data values and investigate SYSAUDITPOLICIES .Also, have you looked at Temporal Tables? I must admit I haven't and they hurt my brain, but from what I understand any changes would be kept as additional non-unique rows with the date/time of the change. 
As most of the Performance Monitors can trace SQL and get data values back, and they are just invoking a DB2 Trace, there may be a DB2 Trace option that could give you the data you want, but you would have to extract and interpret the SMF data.
Regards, Tony

On Wednesday, 19 December 2018, 12:52:36 pm ACDT, Kal Sub <[login to unmask email]> wrote:


Thank you to the people who have responded already. A lot of you are suggesting Log Analysis tools. I think that would be useful, but right now not feasible, especially considering I could implement a (non-elegant) solution to achieve the goals. I was hoping there would be some sort of "Get Current SQL" statement that would help me... or, some other way of collating the pre-update and post-update values and write them to a table.

Well, I will just wait for a while and see if anything else comes up. 

Regards

Kals

In Reply to David Baldon:

Since this is for audit purposes it seems you use a log processing tool to post process the logs to capture the SQL UPDATE statements or simply generate a report showing the before and after values of the columns you’re interested in. The audit requirement regarding how long the data must be retained may influence your decision on what you use the log processing tool to produce. Also consider the volume of expected updates.

...David

From: Kal Sub [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2018 4:49 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?


Hi,

I am trying to capture update statements run by some userids and store it in an "audit" table. The actual requirement is just to capture updated columns and values for audit purposes, but since I have quite a few tables to capture, I can't have a single target audit table matching the source tables, hence I am thinking of capturing the actual SQL statement and storing it.

One simple solution of course is to have one target audit table for each of the source tables, but that would just end up in a lot of audit tables, hence I am trying to get them all in a single table.

Any other potential solutions also welcome. (DB2 Audit trace itself has some limitations like capturing only the first SQL statement in a UOW, hence that wouldn't meet my need).

Thanks

Regards

Kals

-----End Original Message-----

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
http://www.ESAIGroup.com/idug



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Tony Saul

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Kal Sub)
Without a Log Analyzer or similar, I would look at using Triggers for the data values and investigate SYSAUDITPOLICIES .Also, have you looked at Temporal Tables? I must admit I haven't and they hurt my brain, but from what I understand any changes would be kept as additional non-unique rows with the date/time of the change. 
As most of the Performance Monitors can trace SQL and get data values back, and they are just invoking a DB2 Trace, there may be a DB2 Trace option that could give you the data you want, but you would have to extract and interpret the SMF data.
Regards, Tony

On Wednesday, 19 December 2018, 12:52:36 pm ACDT, Kal Sub <[login to unmask email]> wrote:


Thank you to the people who have responded already. A lot of you are suggesting Log Analysis tools. I think that would be useful, but right now not feasible, especially considering I could implement a (non-elegant) solution to achieve the goals. I was hoping there would be some sort of "Get Current SQL" statement that would help me... or, some other way of collating the pre-update and post-update values and write them to a table.

Well, I will just wait for a while and see if anything else comes up. 

Regards

Kals

In Reply to David Baldon:

Since this is for audit purposes it seems you use a log processing tool to post process the logs to capture the SQL UPDATE statements or simply generate a report showing the before and after values of the columns you’re interested in. The audit requirement regarding how long the data must be retained may influence your decision on what you use the log processing tool to produce. Also consider the volume of expected updates.

...David

From: Kal Sub [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2018 4:49 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?


Hi,

I am trying to capture update statements run by some userids and store it in an "audit" table. The actual requirement is just to capture updated columns and values for audit purposes, but since I have quite a few tables to capture, I can't have a single target audit table matching the source tables, hence I am thinking of capturing the actual SQL statement and storing it.

One simple solution of course is to have one target audit table for each of the source tables, but that would just end up in a lot of audit tables, hence I am trying to get them all in a single table.

Any other potential solutions also welcome. (DB2 Audit trace itself has some limitations like capturing only the first SQL statement in a UOW, hence that wouldn't meet my need).

Thanks

Regards

Kals

-----End Original Message-----

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
http://www.ESAIGroup.com/idug



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

J&#248;rn Thyssen

RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Kal Sub)

Hi Kal,

In the absence of log analysis tools I think you have to turn to system temporal tables or triggers. 

As far as I recall Db2 lab measurements shows that system temporal tables adds less overhead to a transaction than the equivalent triggers. However, both add overhead to the DBA work as you have to maintain the history tables in addition to the base tables.

The system temporal history tables must match the base tables so you end up with a lot of history tables. With triggers you could have a central audit table but I would fear that that inserting into a single audit table would become a huge hotspot so you should be really careful with the design (append, member cluster, etc.). The triggers would have to contain some logic to map the columns of a specific table to the audit table so it is more difficult to automate the creation. Whereas with one audit table per table you can easily automate the creation of system temporal history table or triggers.

In Reply to Kal Sub:

Hi Jørn,

Yes, this is for Z/OS. Sorry, I don't have a vendor log analysis tool 

Regards

Kals


In Reply to Jørn Thyssen:

Hi Kal,

I assume you’re on z/OS?

Do you have a vendor log analysis tool? Such tools normally have an audit feature that allows you to capture changes for a specified set of tables and load the data into a single audit table. 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 


 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

Edited By:
Jørn Thyssen[Organization Members] @ Dec 19, 2018 - 09:22 AM (Europe/Copenhagen)

Phil Grainger

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to David Baldon)
Also, are you being asked to audit the “SQL that is making the updates” or the “changes that were made to the data”

These are two very different things – you may actually need to capture both

If all you save is the SQL, then down the line how will you know what data was affected by your SQL? This is possibly far more important that knowing exactly what SQL was issued

I agree with Dave – a log processing tool (or something you write yourself) is the way to go here. What’s in the log is incontrovertible evidence of data changes caused by SQL (or by cascaded SQL in the case of triggers)
________________________________

Phil Grainger

Principal Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[cid:[login to unmask email]

[https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]







From: Baldon, David [mailto:[login to unmask email]
Sent: 19 December 2018 00:59
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?

Since this is for audit purposes it seems you use a log processing tool to post process the logs to capture the SQL UPDATE statements or simply generate a report showing the before and after values of the columns you’re interested in. The audit requirement regarding how long the data must be retained may influence your decision on what you use the log processing tool to produce. Also consider the volume of expected updates.

...David

From: Kal Sub [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2018 4:49 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?


Hi,

I am trying to capture update statements run by some userids and store it in an "audit" table. The actual requirement is just to capture updated columns and values for audit purposes, but since I have quite a few tables to capture, I can't have a single target audit table matching the source tables, hence I am thinking of capturing the actual SQL statement and storing it.

One simple solution of course is to have one target audit table for each of the source tables, but that would just end up in a lot of audit tables, hence I am trying to get them all in a single table.

Any other potential solutions also welcome. (DB2 Audit trace itself has some limitations like capturing only the first SQL statement in a UOW, hence that wouldn't meet my need).

Thanks

Regards

Kals

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.png (8.2k)
  • image002.png (9.3k)

Kal Sub

RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Phil Grainger)

Hi Phil/Jørn,

We are interested in capturing manual updates run thru dynamic sql by specific users. Preferably just the changes to the data. These updates would be adhoc, maybe once a day. We are not interested in changes made by executing static sql in applications.

If it was just one table, I would have created a trigger to capture the changes, and write the pre and post records to a separate "audit" table.  I could still do that for the 20 or so tables I need to audit, but, I was hoping for a more elegant solution, albeit one that doesn't involve purchasing another tool, whereby I can capture all that audit data in to a single audit table.

I had almost settled on triggers as a viable option. Just trying to see whether I can manage with a single audit table, in whatever format. For example, I could just concatenate the values of all columns with a delimiter and write to a single VARCHAR column in the audit table, Does that sound a sensible or reasonable option ?

Thanks,

Regards

Kals

In Reply to Phil Grainger:

Also, are you being asked to audit the “SQL that is making the updates” or the “changes that were made to the data”

These are two very different things – you may actually need to capture both

If all you save is the SQL, then down the line how will you know what data was affected by your SQL? This is possibly far more important that knowing exactly what SQL was issued

I agree with Dave – a log processing tool (or something you write yourself) is the way to go here. What’s in the log is incontrovertible evidence of data changes caused by SQL (or by cascaded SQL in the case of triggers)
________________________________

Phil Grainger

Principal Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[cid:[login to unmask email]

[https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]







From: Baldon, David [mailto:[login to unmask email]
Sent: 19 December 2018 00:59
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?

Since this is for audit purposes it seems you use a log processing tool to post process the logs to capture the SQL UPDATE statements or simply generate a report showing the before and after values of the columns you’re interested in. The audit requirement regarding how long the data must be retained may influence your decision on what you use the log processing tool to produce. Also consider the volume of expected updates.

...David

From: Kal Sub [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2018 4:49 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?


Hi,

I am trying to capture update statements run by some userids and store it in an "audit" table. The actual requirement is just to capture updated columns and values for audit purposes, but since I have quite a few tables to capture, I can't have a single target audit table matching the source tables, hence I am thinking of capturing the actual SQL statement and storing it.

One simple solution of course is to have one target audit table for each of the source tables, but that would just end up in a lot of audit tables, hence I am trying to get them all in a single table.

Any other potential solutions also welcome. (DB2 Audit trace itself has some limitations like capturing only the first SQL statement in a UOW, hence that wouldn't meet my need).

Thanks

Regards

Kals

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

Peter Vanroose

Re: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Jørn Thyssen)

Kal,

As Jørn Thyssen already suggested, I'd also suggest you look at enabling temporal (system_time) for those tables.
Version 11 added precisely the auditing features that you might want to use: you can now specify SESSION_USER & CURRENT SQLID (and of course also current_timestamp) as default values for (possibly hidden) columns in e.g. the history table.
I know this means you'll have to monitor as many additional (history) tables as there are base tables, which is not what you wanted, but it should be relatively easy to write a simple (REXX or SQL/PL) program to regularly summarize the new content of those history tables: start by using the following query to get their names:

SELECT creator||'.'||name FROM sysibm.systables WHERE type='H'


--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Kal Sub

Re: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Peter Vanroose)

Thanks, Peter (and Jørn and Tony who also suggested temporal tables),

Temporal tables sound promising, but some of these tables undergo a lot of changes via the application itself which we are not interested in. We are interested only in the SPUFI type updates made by non-functional manual userids, which is not likely to be much. If I am going to be creating an other set of tables, it may be easier to implement my requirement by using triggers.

But, I will try them out as well when I get a chance. (we are still in the process of converting to v11 NFM, so don't have access to some features yet).

Regards

Kals

In Reply to Peter Vanroose:

 

Kal,

As Jørn Thyssen already suggested, I'd also suggest you look at enabling temporal (system_time) for those tables.
Version 11 added precisely the auditing features that you might want to use: you can now specify SESSION_USER & CURRENT SQLID (and of course also current_timestamp) as default values for (possibly hidden) columns in e.g. the history table.
I know this means you'll have to monitor as many additional (history) tables as there are base tables, which is not what you wanted, but it should be relatively easy to write a simple (REXX or SQL/PL) program to regularly summarize the new content of those history tables: start by using the following query to get their names:

SELECT creator||'.'||name FROM sysibm.systables WHERE type='H'


--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Michael Hannan

Re: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Kal Sub)

Kal,

I am not so keen on the temporal table suggestions. Seems like there are only limited situations to be captured.

Wild Idea.

Consider not allowing these users to Update via SPUFI (or any other way directly), but provide a special program they are authorised to execute which allows Updates and  logs the UPDATE SQLs. 

This is not a full solution since a complex UPDATE SQL may not make it obvious in what it did, and user could execute 100 thousand UPDATEs. Triggers might be practical if they have a way to exclude almost all of the normal production Updates, but I worry too much overhead.

I worry that any solution other than the Log analysis tool will be too expensive. The Log analysis would have have to be run regularly, to save info of interest, before the Logs disappear (when too old).

To invent your own Audit product maybe just too complex.

Try some idea on just one table to start with to see if really can be practical and not cause too much overhead. Beware of very long running User Update statements that will be made even longer before Commit occurs.

At one time I used to have the power to Update many Prod Tables to apply fixes to the data. Applications corrupted tables too frequently, due to inadequate testing. I had to split my UPDATEs up into SQLs that would only hit so many rows per COMMIT, to avoid serious Locking problems in a 24x7 system. No one could audit my fixes effectively in those days but they were needed, to prevent customers being billed incorrectly, however business got to review my intended Updates with before and after information, before the fixes were applied. This stuff was developed specifically for the situation, not general purpose. On occasion, fixes were so complex, took 3 weeks to develop them and too complex for others to review effectively. Billing rules were too complex.

So the whole thing is not easy.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Phil Grainger

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Michael Hannan)
And ANY solution that involves changing what Db2 does when a user executes SQL is likely to add cost to EVERY SQL statement that processes that table (temporal/triggers etc ALL have overhead)

The only cost to a Log Analysis solution is the cost of running the analysis itself (and all solutions that I know of allow repetitive runs of reporting where each run picks up from when the last one completed – including taking into account in-flight URs)

It sounds like you have discarded that solution on one of acquisition cost – but you need to look at the TOTAL cost of your chosen solution (and the costs of future maintenance)

Sorry if I sound biased, but I am! The solution to your conundrum IS to use the Db2 log
________________________________

Phil Grainger

Principal Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[cid:[login to unmask email]

[https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]







From: Michael Hannan [mailto:[login to unmask email]
Sent: 20 December 2018 07:10
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?


Kal,

I am not so keen on the temporal table suggestions. Seems like there are only limited situations to be captured.

Wild Idea.

Consider not allowing these users to Update via SPUFI (or any other way directly), but provide a special program they are authorised to execute which allows Updates and logs the UPDATE SQLs.

This is not a full solution since a complex UPDATE SQL may not make it obvious in what it did, and user could execute 100 thousand UPDATEs. Triggers might be practical if they have a way to exclude almost all of the normal production Updates, but I worry too much overhead.

I worry that any solution other than the Log analysis tool will be too expensive. The Log analysis would have have to be run regularly, to save info of interest, before the Logs disappear (when too old).

To invent your own Audit product maybe just too complex.

Try some idea on just one table to start with to see if really can be practical and not cause too much overhead. Beware of very long running User Update statements that will be made even longer before Commit occurs.

At one time I used to have the power to Update many Prod Tables to apply fixes to the data. Applications corrupted tables too frequently, due to inadequate testing. I had to split my UPDATEs up into SQLs that would only hit so many rows per COMMIT, to avoid serious Locking problems in a 24x7 system. No one could audit my fixes effectively in those days but they were needed, to prevent customers being billed incorrectly, however business got to review my intended Updates with before and after information, before the fixes were applied. This stuff was developed specifically for the situation, not general purpose. On occasion, fixes were so complex, took 3 weeks to develop them and too complex for others to review effectively. Billing rules were too complex.

So the whole thing is not easy.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.png (8.2k)
  • image002.png (9.3k)

Daniel Luksetich

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Peter Vanroose)
Here’s a solution I came up with. All SQL was captured in associated audit tables. One audit table for each base table. I then wrote SQL statements, one per audit table, to generate XML documents. I loaded that into one table which could then be used for reporting. The XML generated makes for quick and easy reports, and is indexable! Probably a little more work than you’d like. I had a half dozen tables to audit in this manner, and was able to code the SQL statements in about a day. Nothing fancy, but a great was to get tables with various numbers of columns into a single table.

Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Peter Vanroose <[login to unmask email]>
Sent: Wednesday, December 19, 2018 4:50 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?



Kal,

As Jørn Thyssen already suggested, I'd also suggest you look at enabling temporal (system_time) for those tables.
Version 11 added precisely the auditing features that you might want to use: you can now specify SESSION_USER & CURRENT SQLID (and of course also current_timestamp) as default values for (possibly hidden) columns in e.g. the history table.
I know this means you'll have to monitor as many additional (history) tables as there are base tables, which is not what you wanted, but it should be relatively easy to write a simple (REXX or SQL/PL) program to regularly summarize the new content of those history tables: start by using the following query to get their names:

SELECT creator||'.'||name FROM sysibm.systables WHERE type='H'


-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
https://www.abis.be/ https://www.abis.be/html/enDB2Calendar.html



-----End Original Message-----

Michael Hannan

RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Daniel Luksetich)

Dan,

" All SQL was captured in associated audit tables. One audit table for each base table." Were you a little vague on purpose? Do you mean you used Triggers or temporal Tables to capture changes rather than capture the SQL itself? 

 

Was there a good practical reason for Audit tables in between the original and the XML Docs, or just easier to test? .e.g. DB2 limitations or performance?

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Tsui Yuk Kai

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Jørn Thyssen)
hi
we dont have this kind of tools but even it have it will consume more CPU
and storage actually. If you have huge table to delete or modify and you
can imagine the result.


Jørn Thyssen <[login to unmask email]> 於 2018年12月19日 星期三寫道:

> Hi Kal,
>
> I assume you’re on z/OS?
>
> Do you have a vendor log analysis tool? Such tools normally have an audit
> feature that allows you to capture changes for a specified set of tables
> and load the data into a single audit table.
>
> Best regards,
>
> Jørn Thyssen
>
> Rocket Software
> 77 Fourth Avenue • Waltham, MA • 02451 • USA
> https://maps.google.com/?q=77+Fourth+Avenue%C2%A0%E2%80%A2+Waltham,+MA+%E2%80%A2+02451+%E2%80%A2+USA&entry=gmail&source=g
> E: [login to unmask email] • W: www.rocketsoftware.com
>
> 2018 IBM Champion.
>
> Views are personal.
>
> -----End Original Message-----
>

Peter Vanroose

Re: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Michael Hannan)

Interesting idea, indeed: "write/compile your own SPUFI".

And since SPUFI is open source, that's actually not too difficult to do!

Modify the source as to just capture & save the SPUFI input data set content into some audit table.
Don't forget to also capture the corresponding SQLCODE(s) (maybe by just extracting those from the SPUFI output data set). Also don't forget to capture the user ID, and also either hard-code or otherwise capture the value of the SSID (Db2 name, first line from the DB2I defaults panel).

In Response to Michael Hannan's suggestion:

[...] Consider not allowing these users to Update via SPUFI (or any other way directly), but provide a special program they are authorised to execute which allows Updates and  logs the UPDATE SQLs. [...]

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Daniel Luksetich

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Michael Hannan)
First of all, I meant to say all data was captured. Sorry about that. So, triggers capture the data into shadow tables. System-period temporal is better now, and I’ve been using those quite a bit. Although XML performance has been greatly improved, I can’t see putting it in triggers as some of the rates of change are extreme, and with system-period temporal in the picture now it’s not possible. So, each table gets an “audit” version of itself. The people with the audit software didn’t want to deal with a whole bunch of tables, and that’s were the XML came in to play. So, the XML is fed into the audit software, and the individual tables are clean up daily.



Cheers,



Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Michael Hannan <[login to unmask email]>
Sent: Friday, December 21, 2018 12:16 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?



Dan,

" All SQL was captured in associated audit tables. One audit table for each base table." Were you a little vague on purpose? Do you mean you used Triggers or temporal Tables to capture changes rather than capture the SQL itself?



Was there a good practical reason for Audit tables in between the original and the XML Docs, or just easier to test? .e.g. DB2 limitations or performance?

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



-----End Original Message-----

Philip Sevetson

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Tsui Yuk Kai)
Mr Tsui,

That is not necessarily true in all use cases, or even most.

If, for example, you know which tables are heavily updated (and by what process), you can omit reporting on the controlled processes (static programs, batch), and report only exceptions. Most log analysis tools do this (and all of them should). Selecting what _not_ to report is as important as the reporting.

--Phil Sevetson

From: Tommy Tsui [mailto:[login to unmask email]
Sent: Friday, December 21, 2018 1:26 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?

hi
we dont have this kind of tools but even it have it will consume more CPU and storage actually. If you have huge table to delete or modify and you can imagine the result.


Jørn Thyssen <[login to unmask email]<mailto:[login to unmask email]>> 於 2018年12月19日 星期三寫道:

Hi Kal,

I assume you’re on z/OS?

Do you have a vendor log analysis tool? Such tools normally have an audit feature that allows you to capture changes for a specified set of tables and load the data into a single audit table.

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA https://maps.google.com/?q=77+Fourth+Avenue%C2%A0%E2%80%A2+Waltham,+MA+%E2%80%A2+02451+%E2%80%A2+USA&entry=gmail&source=g
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com http://www.rocketsoftware.com

2018 IBM Champion.

Views are personal.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Phil Grainger

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Philip Sevetson)
I’m concerned also that there is more concern about the “measurable” cost of a log analysis tool and far less worry about the “invisible” costs of any other solution. As I said before, almost all the alternative suggestions will incur overhead not only on the SQL that is being audited, but potentially on ALL SQL that is modifying selected tables

This “hidden” cost will manifest itself as sub-optimal performance of the applications and increased costs – especially since some of the SQL is bound to be executed during the R4 peak
________________________________

Phil Grainger

Principal Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[cid:[login to unmask email]

[https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]







From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 21 December 2018 13:37
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?

Mr Tsui,

That is not necessarily true in all use cases, or even most.

If, for example, you know which tables are heavily updated (and by what process), you can omit reporting on the controlled processes (static programs, batch), and report only exceptions. Most log analysis tools do this (and all of them should). Selecting what _not_ to report is as important as the reporting.

--Phil Sevetson

From: Tommy Tsui [mailto:[login to unmask email]
Sent: Friday, December 21, 2018 1:26 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?

hi
we dont have this kind of tools but even it have it will consume more CPU and storage actually. If you have huge table to delete or modify and you can imagine the result.


Jørn Thyssen <[login to unmask email]<mailto:[login to unmask email]>> 於 2018年12月19日 星期三寫道:

Hi Kal,

I assume you’re on z/OS?

Do you have a vendor log analysis tool? Such tools normally have an audit feature that allows you to capture changes for a specified set of tables and load the data into a single audit table.

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA https://urldefense.proofpoint.com/v2/url?u=https-3A__maps.google.com_-3Fq-3D77-2BFourth-2BAvenue-25C2-25A0-25E2-2580-25A2-2BWaltham-2C-2BMA-2B-25E2-2580-25A2-2B02451-2B-25E2-2580-25A2-2BUSA-26entry-3Dgmail-26source-3Dg&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=BvOsvpShA3LfpZJw-CPYt03-EiBhVA8c-NS5Z-CHVYA&s=7eMhmJJwe_QRkXC8XopnbrRFSSi8WqnOwhdYDSSbPfA&e=
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rocketsoftware.com&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=BvOsvpShA3LfpZJw-CPYt03-EiBhVA8c-NS5Z-CHVYA&s=JLhh3hvXQ01zj_9InZXa9dD6KOAIUvUqmCzvfmVTQUY&e=

2018 IBM Champion.

Views are personal.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.png (8.2k)
  • image002.png (9.3k)

Michael Hannan

Re: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Peter Vanroose)

In Reply to Peter Vanroose:

Interesting idea, indeed: "write/compile your own SPUFI".

And since SPUFI is open source, that's actually not too difficult to do!

Modify the source as to just capture & save the SPUFI input data set content into some audit table.
Don't forget to also capture the corresponding SQLCODE(s) (maybe by just extracting those from the SPUFI output data set). Also don't forget to capture the user ID, and also either hard-code or otherwise capture the value of the SSID (Db2 name, first line from the DB2I defaults panel).

Yes I was not thinking of writing a SPUFI from scratch. DSNTEP2/4 and DSNTIAUL are also programs that allow updating DML.

So I was thinking of modifying one of those programs with authorisation to update the tables concerned, and having the tables not authorised for update by other means. The modified program could capture UPDATE SQLs. I have not tried it myself. Just speculating.

I agree with Phil's post that overheads for capturing Update audit info, other than by Log analysis, maybe too expensive, even if triggers would detect which Updates are the ad hoc ones to capture AUDIT info for.

I don't know the cost of Log Aanalysis tool, nor exact capabilities, as have never been a user, but I suspect it might be the most viable option.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Dec 22, 2018 - 02:52 AM (Europe/Berlin)

Michael Hannan

Re: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Michael Hannan)



In Reply to Michael Hannan:

I agree with Phil's post that overheads for capturing Update audit info, other than by Log analysis, maybe too expensive, even if triggers would detect which Updates are the ad hoc ones to capture AUDIT info for.

I don't know the cost of Log Aanalysis tool, nor exact capabilities, as have never been a user, but I suspect it might be the most viable option.

 

Too be more clear, if tables to be audited do not have a large number of rows and will be updated not much, so not at all volatile and probably activity dominated by queries, then any of the temporal or triggers solutions maybe good. 

For very large tables, may not be so practical to use other than the Log Analysis, since someone could update multiple columns in all of the rows potentially, and even the query aspect of an Update to find the rows to be updated could run very long with a poor access path. Updates that hit indexed columns cost extra naturally. Could be worth putting resource limits on dynamic Updates/Inserts/Deletes so they do not try to do too much work in a single SQL, and take too long before Commit/Rollback can be done, assuming availability of the table matters.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

James Campbell

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to Peter Vanroose)
If you want to know the SQL being executed, you can trace IFCIDs 63, 247 and 317. But
they don't trace what is the actual updates are - just the SQL.

SPUFI is not open source. Even DSNTEP2 isn't actually "open source" - although you do
have access to the source code and may change it.

The only actual open source (Db2 for z/OS) SQL processor I know of is DARWIN
http://www.gseukdb2.org.uk/db/index.php/downloads/shareware

James Campbell

On 21 Dec 2018 at 2:06, Peter Vanroose wrote:

>
> Interesting idea, indeed: "write/compile your own SPUFI".
> And since SPUFI is open source, that's actually not too difficult to do!
> Modify the source as to just capture & save the SPUFI input data set content into some audit
> table.
> Don't forget to also capture the corresponding SQLCODE(s) (maybe by just extracting those from
> the SPUFI output data set). Also don't forget to capture the user ID, and also either hard-code or
> otherwise capture the value of the SSID (Db2 name, first line from the DB2I defaults panel).
>
> In Response to Michael Hannan's suggestion:
> [...] Consider not allowing these users to Update via SPUFI (or any other way directly), but
> provide a special program they are authorised to execute which allows Updates and  logs
> the UPDATE SQLs. [...]
> --      Peter Vanroose
>         ABIS Training &Consulting,
>         Leuven, Belgium.
>         https://www.abis.be/
>

Peter Vanroose

Re: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to James Campbell)

I'm not saying its freeware, but it's open source, and was once just meant as an example for how to write (dynamic) embedded SQL with Db2.

In Reply to James Campbell:

SPUFI is not open source. Even DSNTEP2 isn't actually "open source" - although you do
have access to the source code and may change it.

The only actual open source (Db2 for z/OS) SQL processor I know of is DARWIN
http://www.gseukdb2.org.uk/db/index.php/downloads/shareware

James Campbell

On 21 Dec 2018 at 2:06, Peter Vanroose wrote:
> Interesting idea, indeed: "write/compile your own SPUFI".
> And since SPUFI is open source, that's actually not too difficult to do!
> Modify the source as to just capture & save the SPUFI input data set content into some audit
> table.
> Don't forget to also capture the corresponding SQLCODE(s) (maybe by just extracting those from
> the SPUFI output data set). Also don't forget to capture the user ID, and also either hard-code or
> otherwise capture the value of the SSID (Db2 name, first line from the DB2I defaults panel).
>
> In Response to Michael Hannan's suggestion:
> [...] Consider not allowing these users to Update via SPUFI (or any other way directly), but
> provide a special program they are authorised to execute which allows Updates and  logs
> the UPDATE SQLs. [...]

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Mohammad Khan

DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?
(in response to James Campbell)
There is also CSNTEP2 at IDUG Code Place.
Khalid

-----Original Message-----
From: James Campbell <[login to unmask email]>
Sent: Saturday, December 22, 2018 6:48 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Z/OS - Is there an easy way to capture currently executing SQL (via a trigger) ?

If you want to know the SQL being executed, you can trace IFCIDs 63, 247 and 317. But they don't trace what is the actual updates are - just the SQL.

SPUFI is not open source. Even DSNTEP2 isn't actually "open source" - although you do have access to the source code and may change it.

The only actual open source (Db2 for z/OS) SQL processor I know of is DARWIN http://www.gseukdb2.org.uk/db/index.php/downloads/shareware

James Campbell

HCSC Company Disclaimer

The information contained in this communication is confidential, private,
proprietary, or otherwise privileged and is intended only for the use of
the addressee. Unauthorized use, disclosure, distribution or copying is
strictly prohibited and may be unlawful. If you have received this
communication in error, please notify the sender immediately at
(312) 653-6000 in Illinois; (800) 447-7828 in Montana;
(800) 835-8699 in New Mexico; (918) 560-3500 in Oklahoma;
or (972) 766-6900 in Texas.