[Db-derby Wiki] Update of "OptimizerTracing" by MamtaSatoor

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The "OptimizerTracing" page has been changed by MamtaSatoor:
http://wiki.apache.org/db-derby/OptimizerTracing?action=diff&rev1=1&rev2=2

  You can trace the optimizer's analysis, one statement at a time. Optimizer tracing is enabled by this command:
  
-    {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', true ); }}}
+  . {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', true ); }}}
  
  After enabling tracing, issue the query you want to trace. E.g.:
  
-    {{{ select columnname from sys.systables t, sys.syscolumns c where t.tableid = c.referenceid and t.tablename = 'SYSROLES'; }}}
+  . {{{ select columnname from sys.systables t, sys.syscolumns c where t.tableid = c.referenceid and t.tablename = 'SYSROLES'; }}}
  
  Finally, turn tracing off and dump the trace. By default, the trace is printed on standard out. However, you can also force the trace to go into a file. Here's how you disable tracing and print the trace on standard out...
  
-    {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', false ); }}}
+  . {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', false ); }}}
  
  ...and here's how you disable tracing, printing the trace to the file z1.txt:
  
-    {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', false, 'z1.txt' ); }}}
+  . {{{ call syscs_util.syscs_register_tool( 'optimizerTracing', false, 'z1.txt' ); }}}
  
+ The tracing option explained above is available in 10.10 release. Prior to 10.10 release, one can use following undocumented way to enable optimizer tracing.
+ 
+ Define two stored procedures in the user application.
+ 
+  . public static void trace() {
+   . org.apache.derby.iapi.db.OptimizerTrace.setOptimizerTrace(true);
+  } public static void printTrace() {
+   . System.out.println(org.apache.derby.iapi.db.OptimizerTrace.getOptimizerTraceOutput());
+  } s.execute("create procedure trace() language java parameter style java external name '" + getClass().getName() + ".trace'"); s.execute("create procedure print_trace() language java parameter style java external name '" + getClass().getName() + ".printTrace'");
+ 
+ And then surround the sql statement that needs to be traced with call trace() before the statement to trace, and printTrace() after the statement to trace as shown below:
+ 
+  . s.execute("call trace()"); s.execute("select 1 from sys.sysschemas natural join sys.systables"); s.execute("call print_trace()");
+