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