Project and Portfolio Management Practitioners Forum

Anyone built a 'sql query' view?

Jim Bowen
Trusted Contributor.

Anyone built a 'sql query' view?

Hi Folks,


I know this is an odd question, but here goes...


Has anyone built a view in the PPM database that would allow someone to search ALL of the SQL queries in the system?  This would include validations, workflow step sources, portlet data sources, etc. 


My use-case is this: every so often our DBA sends me a SQL statement that is taking too long to execute, and I would like to be able to easily pinpoint the source of the statement.


If you've done it, I'd like to steal it!



Jamie Pick
Super Contributor.

Re: Anyone built a 'sql query' view?

For the purposes of identifying database objects that are being obsoleted or deprecated, we have several queries that go through Validations, Portlet Data Sources, Request Type Rules, etc. to search by table name. In terms of the queries that PPM creates during it's processing, you probably have to sort of wager your best guess as to the area of the system it's happening in by the tables present in the query. You could try to narrow in on it by setting up a trace.


If you think it's something built into the product and not a custom query, I'd open an HP case and make them aware of the performance issue you're running into.





Erik Cole
Acclaimed Contributor.

Re: Anyone built a 'sql query' view?

Jim, if a query is taking too long it's not your problem, it's his! ;-) But seriously, it will be difficult to just match up the query because the parameters are sent in realtime and the queries are stored


knta_validations will have the validation SQL, and kdsh_data_sources will have the base sql for portlets (which are likely the problem ones)