Comments (17)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 JKL commented Permalink

Cool stuff!
However - I do not get it work for me. The plsql_profiler.sql runs without error. The "db2 CALL PROFILE.GET_ACTUALS('SELECT * FROM SYSCAT.COLUMNS WHERE COLNAME = ''ROUTINENAME''', ?)" returns the db2exfmt Statement and db2exfmt generates its report. So far so good - but what I do not see are the Section Actuals. In db2exfmt report there is a warning "EXP0224W The section actuals were not collected".

 
What am I doing wrong?
 
Joachim
 
PS: DB2 ESE is on 9.7 FP3a
PPS: My company offers a 3-day Explain Workshop where we also introduce Section Actuals, but the stony way. If you don't mind I would present your solution as a very good alternative (of course on an as-is basis).

2 SergeRielau commented Permalink

JKL,

 
I have no objection to spreading this tool around.
 
The warning you describe has been reported in another case as well. I have not had time to track the reason yet.
If you ping me by email at srielauATcaDOTibmDOTcom maybe we can pin it down.
 
Cheers
Serge

3 Stanisław_Bartkowski commented Permalink

Hi Serge,

 
I like this tool.
 
But one remark:
 
Please add to the procedures header DYNAMIC RESULT SETS 1 clause. Otherwise (for some mysterious reason) cannot run them from IBM Data Studio - I'm receiving the following message
-----------
CALL PROFILE.PLSQLPACKAGE('.........', '........')
Procedure "DB2INST1.PROFILE.PLSQLPACKAGE" returned "1" query result sets, which exceeds the defined limit "0".. SQLCODE=464, SQLSTATE=0100E, DRIVER=3.61.65
---------------
 
Although seems working being launched from the command line.
 
Best regards,
 
 

4 SergeRielau commented Permalink

Stanislaw,

 
Sure I can do that. Another think I want to do is to make the profiler resilient against not having the ALL_SOURCE view.
 
Cheers
Serge

5 SergeRielau commented Permalink

I have given a live demo of the profiler here at the DB2 Nightshow:
http://www.dbisoftware.com/blog/db2nightshow.php?id=325

 
Cheers
Serge

6 YONGLEIG commented Permalink

I like one of the Teradata's monitoring tool, which can tell you the actuals and execution time for each lolepop at real time. I hope DB2 can achieve this too.

 
We have scripts to monitor the IO,read/write and time for each subsection in DPF. That's useful to deal with complicated SQL. We need one step further...

7 KiranaTama commented Permalink

finally I found the right solution for my problem. thanks for developing this.

 
By ERP

8 SergeRielau commented Permalink

Thanks Kirana,
I'm glad to hear you find the tool useful.

 
If you have improvement suggestions or bug reports, please share, so I can keep making the tool better.
 
Cheers
Serge

9 StefanMihokovic commented Permalink

Hi Serge,

 
your example to the Prozedure 'PROFILE.CREATE_EXPLAIN_TABLES' is wrong.
 
Regards/Grüße Stefan

10 SergeRielau commented Permalink

Stefan,

 
Thanks for pointing this out. Should be fixed now.
 
Cheers
Serge

11 StefanMihokovic commented Permalink

Hi Serge,

 
in your DDL script 'profiler.sql ' is also a small bug.
 
Instead of "ALTER MODULE PROFILE PUBLISH PROCEDURE TRACE()" it should read at line 1568 "'ALTER MODULE PROFILE ADD PROCEDURE TRACE()".
 
Regards/Grüße Stefan

12 SergeRielau commented Permalink

Stefan,
That's technically not a bug. DB2 doesn't actually require prototypes. So to PUBLISH the "body" is acceptable.
Nonetheless I have fixed it in my local copy and it will roll into the next refresh.

 
Cheers
Serge

13 oschoett commented Permalink

The definition of the new TRACE function produces an error in DB2 9.7.5:

 
> ALTER MODULE PROFILE PUBLISH PROCEDURE TRACE()
[...]
> DB21034E The command was processed as an SQL statement because it was not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0245N The invocation of routine "RPAD" is ambiguous. The argument in
> position "1" does not have a best fit. LINE NUMBER=6. SQLSTATE=428F5
 
How do I delete the profiler after having defined it in a new schema? Trying to drop the schema gives the error
 
> DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA" cannot
> be processed because there is an object "PROFILE.OBJECT", of type "TYPE",
> which depends on it.. SQLCODE=-478, SQLSTATE=42893, DRIVER=3.57.82
 
Trying to first delete the objects mentioned in the message leads into dependency hell.

14 SergeRielau commented Permalink

Oliver,

 
I only got the error previously reported by Stefan when running the script against DB2 9.7.5.
I have uploaded a fix for that now and hope that it fixes your problem since it's the same procedure.
 
To remove the profiler:
 
DROP MODULE PROFILE;
DROP EVENT MONITOR PROFILE;
DROP TABLESPACE MONITORTBS;
 
Hope that works for you
Serge

15 oschoett commented Permalink

The DROP MODULE statement was the key; I could delete everything afterwards, thank you.

 
However, I still cannot install the current profiler.sql in DB2/NT64 9.7.5:
 
Problem 1: I have done
> db2 create schema profiler
> db2 set schema profiler
> db2 -tf C:\Users\oschoett\home\download\IBM\db2\LUW\profiler_2012-03-29.sql
The result is:
3 error messages at the beginning:
> SQL0204N "PROFILE" is an undefined name. SQLSTATE=42704
Many tables, packages, functions and stored procedures in schema PROFILE, not PROFILER.
This appears to be related to the statement in line 26:
> SET CURRENT SCHEMA PROFILE
which does not seem to work if you want to install in another schema.
 
Problem 2: Error message at the end:
> SQL0245N The invocation of routine "RPAD" is ambiguous. The argument in
> position "1" does not have a best fit. LINE NUMBER=6. SQLSTATE=428F5

16 oschoett commented Permalink

I have run into a problem similar to the one in Comment 1. I am using the profiler without trace from December 2011. For a simple test statement, the call "systools.profile.get_actuals('...', ?); gives me a db2exfmt statement, and I get the query plan with actual numbers. For a more complex statement (359 lines), the above call gives the error message

 
> SQL20501N The explain facility failed because the specified section could not
> be found. Reason code = "4". SQLSTATE=4274L
 
Is there a system limit that needs to be increased?

17 SergeRielau commented Permalink

Oliver,

 
It can be that the statement was pushed out of the package cache.
This could be because there is a lot of other stuff happening on the system or because the statement had to be soft (runstats, create index) invalidated or hard (drop index, drop table, ...) invalidation.
 
Increasing the package would be what comes to mind.
 
Now, if you happen to be on a DB 10 beta there was an issue I saw on Windows where statements got very aggressively removed from the cache. This was fixed in beta 3.
 
Hope that helps.
 
Cheers
Serge