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