Need a 1 min run down of how to log executed SQL statements please

[colin.mcnulty]

Hi,

I'm a SQL Server DBA, but I guess that won't buy me any friends round
here huh? ;-) I've been asked to look at the SQL that's being
executed on a DB2 database from a web app, specifically when the web
site does XYZ, what SQL does it run on the DB2 database? Unfortunately
everyone who knew about how it works has left and I've never even seen
a DB2 database before today!

So, I appear to be looking at an IBM DB2 Universal Database version 7
as from the Help | About in the Control Center. I also have the
Command Center up, though it took me a while to realise that's where
you can execute SQL from, why didn't they call it "SQL Command
Center"?!? So my quesiton is simple: where do I start?

Or more specifically, how do I enable the Query Analyser equivalent?
In fact, just what its called would be a great help! How do I know if
I've got it installed, and where do I access the logs it generates? I
guess the end result is this, I want to be able to mark a point in the
log, go to the web app and execute a sql query, go back to the log and
see the SQL the web app generated.

If someone could give a few pointers, I would appreciate it. If you
feel like giving a step by step walkthrough, that would be great. Just
please assume I understand databases generically, but that I know
nothing about DB2, which I don't!

Many thanks,

Colin

[Serge Rielau]

co***********@yourcommunications.co.uk wrote:

I'm a SQL Server DBA, but I guess that won't buy me any friends round
here huh? ;-) We'll just have to accept you as you are, eh?
I've been asked to look at the SQL that's being
executed on a DB2 database from a web app, specifically when the web
site does XYZ, what SQL does it run on the DB2 database? Unfortunately
everyone who knew about how it works has left and I've never even seen
a DB2 database before today!

So, I appear to be looking at an IBM DB2 Universal Database version 7
as from the Help | About in the Control Center. I also have the
Command Center up, though it took me a while to realise that's where
you can execute SQL from, why didn't they call it "SQL Command
Center"?!? So my quesiton is simple: where do I start? ... because it executes more than just SQL commands.
E.g. LOAD, UPDATE DATABASE CONFIURATION, etc, etc....
Or more specifically, how do I enable the Query Analyser equivalent? Keep in mind that what applies for you with DB2 applies for many here
with SQL Server. Please explain what Query Analyzer does.
For now I assume it gives you the optimizer plan for a given query.
At the top of your Command Center GUI there is an "explain" button. Any
query typed into command center can be explained (I.e. it's access graph
analyzed) and displayed. The tool itself is called "visual explain".
There are other tools like db2exfmt which are command line based (but
more powerful...)
In fact, just what its called would be a great help! How do I know if
I've got it installed, and where do I access the logs it generates? I
guess the end result is this, I want to be able to mark a point in the
log, go to the web app and execute a sql query, go back to the log and
see the SQL the web app generated.

DB2 does not do logical logging. That is by looking at the log you
cannot reverse engineer the SQL statement that caused the log entry.
There are tools that analyze the log to do log replay or to back out
committed transactions, such as the "Recovery Expert" (extra $$), but I
take it that's not what you are looking for.
I suspect you are looking for the "Health monitor". You can get there
from the control center. The health monitor lets you chose a variety of
reports. One of them providing a list of the SQL that has recently run
(i.e. for which DB2 has the "plans" - I'm using the term loosely -
cached) along with some basic metrics. AFAIK - I'm not a GUI person -
you can explain a statement shown in the report directly from there.

Hope that helps.. a bit at least
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[colin.mcnulty]

Thanks for the reply Serge. I guess it's the health monitor I want
then, I'll look at that.

To answer your question though, SQL Server Query Analyzer, combined
with SQL Server Profiler, allows you to monitor SQL and event
communications from any app to the database. Apart from the simple
stuff like showing execution plans and the like, it can recommend
indexes for example, and even create indexes for you at the touch of a
button. Once it's turned on, you can also do nice things like view the
top 10 longest running queries in the last week. Very useful.

[colin.mcnulty]

> I suspect you are looking for the "Health monitor".

Ok Serge, I'm clearly being thick as I can't find anything called a
"Health Monitor" nor does it appear in the Help Index page!

The Control Center | Tools menu has these options:
- Satellite Administration Center
- Data Warehouse Center
- Command Center
- Script Center
- Alert Center
- Journal (This is the closest thing I've found to what I want as it
shows Job History, but these aren't SQL statements)
- License Center
- Tools Settings

The Control Center | Selected menu does list a Performance Monitoring
sub-menu, from which I can select Show Monitoring Activity. This
launches a new window, from which I can select these Monitor names:
- Locking
- Cache
- Deadlocks
- All_performance_variables
- Sort
- Disk_performance
- Global_memory
- Long_running-query
- Capacity
- Default_for_database_level
- Default_for_all_levels

This Perfomance Monitor looks like the best bet, but I can't quite find
the "list of last 100 SQL statements" monitor! ;-)

There is another menu option that looks promising: Query Patroller, but
selectiong the "Start Tracker" sub menu doesn't appear to do anything.
Some looking in the help suggests that I should have it installed and
that there should be a Windows Service in Services, but there's nothing
there that looks obvious so I'm thinking I don't have it installed
anyway.

Any thoughts? Ta.

[Serge Rielau]

co***********@yourcommunications.co.uk wrote:

I suspect you are looking for the "Health monitor".

Ok Serge, I'm clearly being thick as I can't find anything called a
"Health Monitor" nor does it appear in the Help Index page!

The Control Center | Tools menu has these options:
- Satellite Administration Center
- Data Warehouse Center
- Command Center
- Script Center
- Alert Center
- Journal (This is the closest thing I've found to what I want as it
shows Job History, but these aren't SQL statements)
- License Center
- Tools Settings

The Control Center | Selected menu does list a Performance Monitoring
sub-menu, from which I can select Show Monitoring Activity. This
launches a new window, from which I can select these Monitor names:
- Locking
- Cache
- Deadlocks
- All_performance_variables
- Sort
- Disk_performance
- Global_memory
- Long_running-query
- Capacity
- Default_for_database_level
- Default_for_all_levels

This Perfomance Monitor looks like the best bet, but I can't quite find
the "list of last 100 SQL statements" monitor! ;-)

There is another menu option that looks promising: Query Patroller, but
selectiong the "Start Tracker" sub menu doesn't appear to do anything.
Some looking in the help suggests that I should have it installed and
that there should be a Windows Service in Services, but there's nothing
there that looks obvious so I'm thinking I don't have it installed
anyway.

Any thoughts? Ta.

Yeah, I'm thinking you're not where I think you are (DB2 wise).
Please tell us the platform and level of your DB2.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[colin.mcnulty]

> Please tell us the platform and level of your DB2.

DB2 is running on Windows 2000 Server, not sure what you mean by
"level".

However, I have a workaround. I've managed to put the web app into
debug mode and am getting a rather large log file which contains the
raw SQL that it's sending to DB2, along with a million other things!
Not quite what I wanted, but will do.

Thanks very much for your time and effort Serge.

[Serge Rielau]

co***********@yourcommunications.co.uk wrote:

Please tell us the platform and level of your DB2.

DB2 is running on Windows 2000 Server, not sure what you mean by
"level".

However, I have a workaround. I've managed to put the web app into
debug mode and am getting a rather large log file which contains the
raw SQL that it's sending to DB2, along with a million other things!
Not quite what I wanted, but will do.

For future reference, this is what I'm looking for:
C:\>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09000" with
level identifier "01010107".
Informational tokens are "DB2 v9.0.0.257", "s060328", "NT32", and Fix
Pack "0".
Product is installed at "D:\SQLLIB" with DB2 Copy Name "DB2".

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Ian]

co***********@yourcommunications.co.uk wrote:

Or more specifically, how do I enable the Query Analyser equivalent?
In fact, just what its called would be a great help! How do I know if
I've got it installed, and where do I access the logs it generates? I
guess the end result is this, I want to be able to mark a point in the
log, go to the web app and execute a sql query, go back to the log and
see the SQL the web app generated.

I believe you are looking for an event monitor. You can create an
event monitor (for statements) that will capture every SQL statement
that was executed. Look for the "CREATE EVENT MONITOR" statement in
the SQL Reference. Also see the "FLUSH EVENT MONITOR" command (since
event monitors are typically buffered.

[Pierre Saint-Jacques]

If one reads the original post, it mentions DB2 V7 which does NOT have the
Health/Activity/Design Advisor monitors.
It does have Visual Exlain but to get the snapshot of the package cache
which has the current executing or executed SQL is not easy as I remember
(if possible even!).
You'll have to use:
updating the monitor switches for statements
get snapshot command, pipe the output to a file and extract the statements.
cut and paste the statements and then use visual explain to analyze the
access plan.
You can use the same file against the V7 index advisor to get index
evalution and/or recommendation.

Basically, you can most of what you ask by using a series of steps with
different tools in V7. It is not seamlless in the sense that from one
tool/monitor you can pass on the part of data you wan tto analyze to the
othe tool. It is a series of steps that pass info thru output files that
become input to the other.

Of course V8 gives you all of this interactively with the monitors, but
that's not where you are.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de news:
4f*************@individual.net...

co***********@yourcommunications.co.uk wrote:

I suspect you are looking for the "Health monitor".

Ok Serge, I'm clearly being thick as I can't find anything called a
"Health Monitor" nor does it appear in the Help Index page!

The Control Center | Tools menu has these options:
- Satellite Administration Center
- Data Warehouse Center
- Command Center
- Script Center
- Alert Center
- Journal (This is the closest thing I've found to what I want as it
shows Job History, but these aren't SQL statements)
- License Center
- Tools Settings

The Control Center | Selected menu does list a Performance Monitoring
sub-menu, from which I can select Show Monitoring Activity. This
launches a new window, from which I can select these Monitor names:
- Locking
- Cache
- Deadlocks
- All_performance_variables
- Sort
- Disk_performance
- Global_memory
- Long_running-query
- Capacity
- Default_for_database_level
- Default_for_all_levels

This Perfomance Monitor looks like the best bet, but I can't quite find
the "list of last 100 SQL statements" monitor! ;-)

There is another menu option that looks promising: Query Patroller, but
selectiong the "Start Tracker" sub menu doesn't appear to do anything.
Some looking in the help suggests that I should have it installed and
that there should be a Windows Service in Services, but there's nothing
there that looks obvious so I'm thinking I don't have it installed
anyway.

Any thoughts? Ta.

Yeah, I'm thinking you're not where I think you are (DB2 wise).
Please tell us the platform and level of your DB2.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[gimme_this_gimme_that]

For WebLogic 8.1+ setting the SQL log is a console thing and a
database driver thing.

e.g. For DB2 if you're using the type-4 driver and turn on logging,
EVERY SINGLE SQL THING (the executed sql and follow fetches using the
get* methods) get written to a log file.

It's an all or nothing thing.

I recall that the DB2 type-2 driver only writes the SQL statements but
I could be wrong.