Avatar of Jim Youmans
Jim Youmans
Flag for United States of America asked on

DB2 11.5 Capture text of generated query

DB2 11.5 on Windows

We have an app that creates and runs a query based on a number of conditions.  This app is legacy and no one know much about it.  The beginning of the query is the same.

MERGE INTO schema1.table1 dgmsnc USING

Open in new window

After that it gets complicated.  I have searched the plan cache and diag log but can't find it.  Is there any way to capture a given query without capturing all queries?  This is a busy system and if I try to log every query it will bring the system to it's knees.

Any suggestions?

Thank you.


DB2Windows OS

Avatar of undefined
Last Comment
Jim Youmans

8/22/2022 - Mon
Kent Olsen

Hi Jim,

That's a tough one.  I don't know of any way to log just one query without modifying the SQL that's running it.

What are you trying to learn?

Jim Youmans

We have an issue where the database is producing FODC files ever day and I opened a case with IBM and they have identified this one query as maybe the issue but they need the full query.  This app grabs a dataset and then run this query for each item.  The problem is no one knows exactly how it works.  I would like for the developer to just write it to a log as it runs but they are scared to death to touch it because of it's age and no one current here know anything about it.
Kent Olsen

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jim Youmans

How much overhead do you think this might add?  It is a very busy database and almost every process is ran by one or two accounts.  So "someone" will be a lot of data.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Kent Olsen

It basically is writing text to the log so there shouldn't be much overhead, even if a lot is written to the log.

And you can turn it on/off any time you want.  If you know that the job in question will run between 9:15 and 9:30AM, enable the event monitor by 9:15 and disable it after 9:30.  If it runs at 2:30AM just create windows events to enable it by 2:30 and disable it a short time later.

If it runs periodically throughout the day, turn the event monitor on any time you want, let it run, and disable it later.  Then check the log.

Jim Youmans

Great minds think alike.  I was just thinking that last night.  Thank you!!!!