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

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