24x7 online logs using DSN1LOGP (similar to CA Log Analyzer)

SRICOBSAS · New User Joined: 07 Dec 2015 Posts: 19 Location: India

Hi DB2 Experts,

I am in a kind of difficult situation in my project. I would be really grateful if experts could help me out.

On a daily basis I need to extract all log messages from the DB2 archive logs and all daily trace data from the active SMF datasets, paste these output in a word doc, upload the word doc onto a sharepoint folder

The catch is I need to do be able to query the logs while DB2 is active (.i.e the bootstrap dataset is active and hence difficult to touch it using DSN1LOGP)

There are 3 bad news in this regard

1) My client has discontinued the use of CA Log Analyzer which they were evaluating for the past 3 months. Hence CA support is not available.
2) Our DB2 DBA has gone on a long medical emergency (probably after hearing this CA news he is planning to quit)
3) I have been pulled in for this daily log activity. I come from a Business Intelligence background and did some COBOL programming.

Since nobody was around whom I could ask I began by looking what are the available tools for this log extraction / trace extraction activity. I found:

Informatica PowerExchange
IBM Data Studio
BMC MainView
Compuware DBA-Xpert

With Informatica PowerExchange there is a way to trap the logs (from the active log dataset). But the problem is the tables need to have "DATA CAPTURE CHANGES" on. The client is not in favor of that.

I couldnt find any log display capabilities in Data Studio

BMC Mainview can show the active traces (even if tracing is not turned on using START TRACE) but the problem is it will capture only those traces / those commands which were issued by a z/OS batch job. The requirement is to extract ALL log messages (for all DB2 activity originating within z/OS or outside it. Various external applications connect to the mainframe DB2 via ODBC / JDBC and perform routine selects / inserts / updates. Hence ALL DB2 activities need to be trapped and stored)

Couldnt find any logging capability using Compuware DBA-Xpert

So I am back to DSN1LOGP and DSN1SMFP

Has anyone tried using DSN1LOGP on archive log datasets while DB2 is still active? I can get the RBAStart / RBAEnd / DBID / OBID info from Compuware's DBA-Xpert (or even from the sysibm catalog tables- SYSTABLES and SYSTABLESPACESTATS). I can also map these RBAs to the correct archive log (for e.g if 3 selects, 2 inserts and 5 row deletes happened yesterday on a table XYZ, I can get to know that today the archive log XYZ_ARCHLOG_D001 will contain all these info). The challenge is to extract the SQL DDL / DML activity from these archive logs while DB2 is up and running.

As for DSN1SMFP I googled around and thought of going for the below steps:

1) Insert AUDIT policies on tables which need to be audited in SYSIBM.SYSAUDITPOLICIES

2) Turn on tracing using START TRACE dest(SMF) at the start of the day

3) At the end of the day do a STOP TRACE

4) Use DSN1SMFP to read the relevant IFCID records and publish the SMF traces on sharepoint

I heard there is some overhead for turning on tracing but since this is the first time I am doing this activity I can convince the client for the business need in this regard

In short my client wants me to use the native IBM log / smf utilities DSN1LOGP and DSN1SMFP (or the available tools- Informatica / IBM Data Studio / Mainview / DBA-Xpert) and develop a 24x7 CA-LOg Analyzer kind online log extraction utility (I believe CA Log Analyzer can extract logs even while DB2 is online)

Apologies for the lengthy post, I also wanted to post the tool research I have done so far.

Would be grateful for any pointers / directions in this regard.

Thanks & Regards

SRICOBSAS · New User Joined: 07 Dec 2015 Posts: 19 Location: India

Hi

Config info (thought would be helpful)

Platform: z/OS v 1.13
DB2: v 10 (10.1.5)
Installation / Upgrade mode: NFM

Thanks & Regards

Rohit Umarjikar · Posted: Thu Aug 18, 2016 8:10 pm

SRICOBSAS · New User Joined: 07 Dec 2015 Posts: 19 Location: India

Hi Rohit,

Thanks for responding.

This client for whom I am required to carry out this daily log extraction / trace extraction is a small one. For billing purposes there are 2 DBAs, but in reality there is only 1 and he's out on medical emergency.

The other one is more of a mainframe / RACF administrator. He has backed out on helping me in this matter saying he is new and he doesnt know anything about DB2.

DB2 here has been running without much of issues. Thats because nobody has touched the defaults. For major issues (almost none have been observed for nearly 3 yrs now) IBM is called in. For most of the small, regular DBA activities (read: SELECT querying / column alteration / minor changes in stored procs e.t.c) JCL scripts and scripts in DataStudio already exist.

When my team approached the DBA for the DB2 logs / traces the mainframe administrator tried fiddling with DB2 MSTR logs but it wasnt helpful. The DBA requested for CA Log Analyzer but it was given to him only on evaluation basis. Because of financial restructuring here CA support is being reduced. Hence after the evaluation period CA Log Analyzer went out of the radar.

So far the DBA was using CA Log Analyzer for uploading these logs / traces onto sharepoint. But now without CA Log Analyzer I have to explore alternative options for this log / trace collection.

I would have washed off my hands of this task but I have my own interest in setting up this

I manage a set of BI / Cobol apps. On a regular basis if frontend troubleshooting is required I look into the backend DB2 logs to see if anything wrong went there first. I have little DB2 experience but in my opinion the backend DB2 maintenance needs to be revamped. But now if this log / trace upload process is broken then I am looking at lots of inconsistencies in my troubleshooting process which will affect my project deliveries.

I undertook this activity for the above reason but now I find that I have to do more than what I bargained for. I cant back out now. I have to somehow restore the log / trace uploading process.

As mentioned earlier I found

Informatica PowerExchange
IBM Data Studio
BMC MainView
Compuware DBA-Xpert

The last one- DBA-Xpert is more of a basic view type tool which gives you short insights on all tables / tablespaces / views / storage groups e.t.c. I found some RBA values for some tablespaces mapped to some archive logs.

But the issue is I cant use DSN1LOGP while DB2 is up and running. There is no scheduled DB2 restart process at my shop and I have to give a real valid business reason for bringing down DB2 (which is extremely difficult)

I cant refer to the bootstrap datasets while DB2 is online. I am trying to figure out if I can make DSN1LOGP bypass the bootstrap and refer to the archive logs directly.

As for tracing I am trying to find out the extent to which BMC Mainview can trap the traces. There are some event traces I saw in Mainview but these are originating from within batch jobs in z/OS. I need to trap all SMF audit activities- whether within z/OS or outside it.

I am trying to setup DSN1SMFP. The only concern is I have to have the audit poliicies in SYSADUITPOLICIES and do a START TRACE / STOP TRACE and then read the IFCID records collected during this interval.

Informatica Powerexchange is not helping me much because I have to turn on DATA CAPTURE CHANGES on the desired tables.

So in short I am looking for some least-cost / least-effort system compatible log / trace collection. Some mechanism whereby DB2's running is not affected.

Thanks once again. Sorry for the lengthy post.

Regards
SRICOBASAS