Accessing SYSXPLAIN tables via java methods?

[Nirmal Fernando <ni...@gmail.com>]

Hi All,

In the ij tool, if we need to query SYSXPLAIN tables, we should call
some methods before and after.

eg: [0]

To cause Derby to record statistics about statement execution in the
SYSXPLAIN_* database tables in the schema named 'MY_STATS':

        call syscs_util.syscs_set_runtimestatistics(1);
        call syscs_util.syscs_set_xplain_schema('MY_STATS');

        select country from countries;

        call syscs_util.syscs_set_runtimestatistics(0);
        call syscs_util.syscs_set_xplain_schema('');


My question is can we call these methods in the code of a new tool
(like ij) as java methods?


References:

[0] http://db.apache.org/derby/docs/dev/ref/rref_syscs_set_xplain_schema.html#rref_syscs_set_xplain_schema


Thanks.

-- 
Best Regards,
Nirmal

C.S.Nirmal J. Fernando
Department of Computer Science & Engineering,
Faculty of Engineering,
University of Moratuwa,
Sri Lanka.

Re: Accessing SYSXPLAIN tables via java methods?

[Rick Hillegas <ri...@oracle.com>]

Hi Nirmal,

I'm not sure that I understand your question. Bear in mind that you will 
want the tool to be able to operate across the network (as ij does). 
When operating across the network, you can't directly call engine entry 
points because the engine isn't running in the same VM as the client. 
You may find it helpful to think about the tool as a piece of remote, 
client code which runs in New York and scrapes query plans out of an 
engine which runs in London.

Hope this is useful,
-Rick

Nirmal Fernando wrote:
> Hi All,
>
> In the ij tool, if we need to query SYSXPLAIN tables, we should call
> some methods before and after.
>
> eg: [0]
>
> To cause Derby to record statistics about statement execution in the
> SYSXPLAIN_* database tables in the schema named 'MY_STATS':
>
>         call syscs_util.syscs_set_runtimestatistics(1);
>         call syscs_util.syscs_set_xplain_schema('MY_STATS');
>
>         select country from countries;
>
>         call syscs_util.syscs_set_runtimestatistics(0);
>         call syscs_util.syscs_set_xplain_schema('');
>
>
> My question is can we call these methods in the code of a new tool
> (like ij) as java methods?
>
>
> References:
>
> [0] http://db.apache.org/derby/docs/dev/ref/rref_syscs_set_xplain_schema.html#rref_syscs_set_xplain_schema
>
>
> Thanks.
>
>   

Re: Accessing SYSXPLAIN tables via java methods?

[Nirmal Fernando <ni...@gmail.com>]

Thanks Rick for your response, I apologize if my question was not
clearly fired.

Bryan, thanks for the suggestion, I was in a doubt how exactly the
tool would start. While reading your reply, sudden thought came to my
mind whether the entries in SYSXPLAIN tables remain even after a
disconnection of a tool, so I just tried to access XPLAIN tables via a
new connection and it was still available. So I think this way would
be perfectly workable.

Thanks again.


-- 
Best Regards,
Nirmal

C.S.Nirmal J. Fernando
Department of Computer Science & Engineering,
Faculty of Engineering,
University of Moratuwa,
Sri Lanka.

Re: Accessing SYSXPLAIN tables via java methods?

[Bryan Pendleton <bp...@gmail.com>]

Hi Nirmal,

I think that the tool which formats the SYSXPLAIN data as XML should
assume that the XPLAIN tables are already fully populated for the
query of interest. I think the tool should take as its arguments:

  - the database connection information
  - the name of the schema which holds the XPLAIN tables of interest
  - the statement ID for which we want to format the query plan as XML

So, perhaps an invocation of the tool would look something like:

   java org.apache.derby.tools.exportQueryPlanAsXML \
        -c jdbc:derby:myDB \
        -u bpendleton \
        -p my-password \
        -schema my_stats \
        -statement my-long-and-ugly-UUID \
   >myQueryPlan.xml

Before using this tool, the user would therefore have to first separately
capture the query plan data into the SYSXPLAIN tables, using the procedures
that you referenced in your message.

Does that sound like a workable idea?

thanks,

bryan