Parsing of CE SQL to DB SQL

Question

I am supporting Filenet Applications and generally focus on performance improvement techniques. Often we face this issue related to the queries optimization. Generally we get the queries from DBA and these are DB SQL which are fired at the database level. Now from the application code we pass the CE SQL and not the DB SQL. I am aware that the CE parse the CE SQL to underlying DB SQL. I am trying to figure out if I have the DB SQL can I get the corresponding CE SQL which is being fired. A code or script which I can write in which I enter the CE SQL and the corresponding DB SQL gets generated. Appreciate if I could get any pointers on this as I am really stuck.

Answer 1

You need to enable Trace Logging for the DB subsystem. This is done through the Trace Control tab of Domain configuration in ACCE. Then you will be able to see database queries in p8_server_trace.log.

For convenience you might want to enable tracing for the SRCH subsystem as well. Then original and generated queries will go hand in hand.

Detailed info on Trace Logging is available in the FileNet P8 documentation.

Answer 2

The way to capture CE SQL queries is to turn on auditing for the object class your are interested in and select Query Event as the event. Now every time a query is performed an event object is created. This object has a property called QueryText which contains the CE query that is performed. You could use the creation time or some other information in the query to match it to your database query.

The query events can be queried using the ACCE or accessed programmatically using the API object com.filenet.api.events.QueryEvent.

Be aware that on a busy system a lot of query events can be generated!