12. Understanding .NET Data Provider Connectivity : Application Configuration File—Troubleshoot Applications : Capturing DBMS Server Trace Information
 
Share this page                  
Capturing DBMS Server Trace Information
A .NET application using the .NET Data Provider can capture DBMS trace information from a DBMS Server of Release 9.1 (2006r2) or later. The DBMS trace information can include a Query Execution Plan (QEP), trace points, lock traces, and more. The application directs the data provider to capture the trace messages using the .NET application configuration file with a key of Ingres.trace.dbms.
For example:
<?xml version="1.0" encoding = "utf-8" ?>
<configuration>
    <appSettings>
        <add key="Ingres.trace.log" value="C:\temp\Ingres.trace.log" />
        <add key="Ingres.trace.dbms" value="1" />
    </appSettings>
</configuration>
A sample code fragment that directs the DBMS Server to return trace information to the client may look like the following:
static void TraceQEPLock_Trace(string connstring)
{
    DbCommand cmd;
    using (IngresConnection conn = new IngresConnection(connstring))
    {
        conn.Open();
        cmd = conn.CreateCommand();
 
        // SET PRINTQRY statement displays each query and its
        // parameters as it is passed to the DBMS Server for processing.
        cmd.CommandText = "set printqry";
        cmd.ExecuteNonQuery();
 
        // SET LOCK_TRACE statement enables the display of locking
        // activity for the current session, including locks taken
        // and locks released.
        cmd.CommandText = "set Lock_Trace";
        cmd.ExecuteNonQuery();
 
        // SET QEP statement displays diagrammatic representation
        // of the query execution plan chosen for the query
        // by the optimizer.
        cmd.CommandText = "set qep";
        cmd.ExecuteNonQuery();
 
        // SET TRACE POINT QE90 statement to display
        // the estimated vs. actual cost in disk and
        // CPU time of running the query.
        cmd.CommandText = "set trace point qe90";
        cmd.ExecuteNonQuery();
 
        // SET PRINTRULES statement causes the DBMS Server to send
        // a trace message to the application each time a rule is fired.
        cmd.CommandText = "set printrules";
        cmd.ExecuteNonQuery();
 
        cmd = conn.CreateCommand();
        cmd.CommandText =
                        "SELECT * FROM mytable";
        cmd.ExecuteReader();
        } // end using (IngresConnection conn)
}
The program output of such a code fragment may look like:
QUERY BUFFER:
set Lock_Trace
QUERY PARAMETERS:
QUERY BUFFER:
set qep
QUERY PARAMETERS:
QUERY BUFFER:
set trace point qe90
QUERY PARAMETERS:
QUERY BUFFER:
set printrules
QUERY PARAMETERS:
QUERY BUFFER:
SELECT * FROM mytable
QUERY PARAMETERS:
 
QUERY PLAN 1,1, no timeout, of main query    (output from SET QEP)
             Proj-rest
            Sorted(name)
            Pages 1 Tups 1
            D1 C0
 /
mytable
B-Tree(NU)
Pages 5 Tups 1                               (output from SET LOCK_TRACE)
    LOCK:   MVCC  PHYS                     Mode: IX  Timeout: NOWAIT
            Key: (mydatabase,mytable)
    LOCK:   TABLE PHYS                     Mode: IS  Timeout: NOWAIT
            Key: (mydatabase,mytable)
    LOCK:   PAGE  PHYS,LOCL,NOIN,QUTM      Mode: S   Timeout: 0
            Key: (mydatabase,mytable,0.0)
    UNLOCK: PAGE  Key: (mydatabase,mytable,0.0)
----------
| 0|
ORIG                                         (output from SET TRACE POINT QE90)
at        1
et        1
 
ad        0
ed        1
 
ac        0
ec        1
 
et        0
----------