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?
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


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.

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


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

5 SergeRielau commented Permalink

I have given a live demo of the profiler here at the DB2 Nightshow:


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.


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.

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


Thanks for pointing this out. Should be fixed now.