Getting Ingres Qep LockTrace Using JDBC

From Ingres Community Wiki

Contents 1 I. Introduction 2 II. The Example Program 3 III. Database Schema: 4 IV. Program Output 5 V. QEP/LOCK_TRACE Output

I. Introduction

This simple example illustrates how to get Ingres Query execution plan, lock_trace etc using Ingres SET statements from a Java application. This is a new feature that is implemented in Ingres 2006R2 (9.1) release of Ingres JDBC driver and DAS server. Those clients who are still on older releases of Ingres can still use this feature by having their Java applications point to the DAS server from 2006R2 installation, and using the iijdbc.jar from 2006r2. In this case it is required to define a vnode entry pointing to older release of Ingres. The Ingres JDBC driver ( iijdbc.jar ) from Ingres2006r2 installation is used for testing. It is assumed that the reader is familiar with Java/JDBC programming.

II. The Example Program

The program can also be downloaded from the URL Media:IngQep.java.

---

import java.sql.DriverManager;

import java.sql.Connection;

import java.sql.Statement;

import java.sql.PreparedStatement;

//

// Name: IngQep.java

//

// Description:

// This example demonstrates how to get QEP/Lock_Trace for an 'insert' query.

//

// Modify the JDBC URL/(user,pwd) info, compile it.

//

// To get the QEP/Locks info, the program needs to be invoked as follows:

// "java -Dingres.jdbc.dbms.trace.log=dbms.log IngQep".

//

// History:

// 18-Dec-2007 ( Usha Rajsekar )

// Created.

//

class IngQep

{

public static void main ( String [] args )

{

Connection conn = null;

PreparedStatement pst = null;

Statement stmt = null;

String sql = "insert into dt_test values(?, ?, ?)";

String url = "jdbc:ingres://rajus01:IJ7/rajus01_26bd06::testdb;";

try

{

clsName = Class.forName("com.ingres.jdbc.IngresDriver");

System.out.println (clsName);

conn = DriverManager.getConnection(url, "ingres", "ingres");

if( conn != null )

{

// Execute Ingres set statements

stmt = conn.createStatement();

stmt.executeUpdate("set Lock_Trace");

stmt.executeUpdate("set printqry");

stmt.executeUpdate("set trace point qe90");

stmt.executeUpdate("set printrules");

// execute insert statements

pst = conn.prepareStatement(sql);

java.util.Date dt = new java.util.Date();

long time = dt.getTime();

pst.setTimestamp(1, new java.sql.Timestamp( time ));

pst.setInt(2, 1);

pst.setInt(3, 2);

int res = pst.executeUpdate();

System.out.println("Inserted " + res + " record" );

}

stmt.close();

pst.close();

conn.close();

System.out.println ("Connection closed");

}

catch ( Exception ex )

{

ex.printStackTrace();

}

}

}

III. Database Schema:

C:\>sql usha

INGRES TERMINAL MONITOR Copyright 2007 Ingres Corporation

Ingres 2006 Release 2 Microsoft Windows Version II 9.1.0 (int.w32/123) login

Tue Dec 18 12:29:26 2007

continue

• help\g

Executing . . .

Name Owner Type

dt_test rajus01 table

jdbc_test rajus01 table

(2 rows)

continue

• help dt_test\g

Executing . . .

Name: dt_test

Owner: rajus01

Created: 19-nov-2007 07:38:38

Type: user table

Version: II9.0

Column Information:

Key

Column Name Type Length Nulls Defaults Seq

d ingresdate yes null

c2 integer 4 yes null

c3 integer 4 yes null

continue

• \q

Ingres 2006 Release 2 Version II 9.1.0 (int.w32/123) logout

Tue Dec 18 12:30:27 2007

IV. Program Output

C:\java -Dingres.jdbc.dbms.trace.log=dbms.log IngQep

class com.ingres.jdbc.IngresDriver

Inserted 1 record

Connection closed

V. QEP/LOCK_TRACE Output

UNLOCK: ALL Tran-id: <000046AA477BF037>

QUERY BUFFER:

set trace point qe90

QUERY PARAMETERS:

UNLOCK: ALL Tran-id: <000046AA477BF038>

QUERY BUFFER:

set printrules

QUERY PARAMETERS:

UNLOCK: ALL Tran-id: <000046AA477BF039>

QUERY BUFFER:

prepare JDBC_STMT_0_0 into sqlda from insert into dt_test values(?, ?, ?)

QUERY PARAMETERS:

LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (usha,iirel_idx,0.30)

UNLOCK: PAGE Key: (usha,iirel_idx,0.30)

LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (usha,iirelation,0.22)

UNLOCK: PAGE Key: (usha,iirelation,0.22)

LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (usha,iirelation,0.22)

LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (usha,iiattribute,0.44)

UNLOCK: PAGE Key: (usha,iiattribute,0.44)

LOCK: PAGE PHYS Mode: S Timeout: 0 Key: (usha,iiattribute,0.171)

UNLOCK: PAGE Key: (usha,iiattribute,0.171)

UNLOCK: PAGE Key: (usha,iirelation,0.22)

UNLOCK: ALL Tran-id: <000046AA477BF03A>

QUERY BUFFER:

execute JDBC_STMT_0_0 using ~V , ~V , ~V

QUERY PARAMETERS:

Parameter : 0

dn_year = 2008 dn_month = 1 dn_day = 3

dn_time = 48429 dn_nsecond = 91000000

dn_tzhour dn_tzminute = -8

Parameter : 1

1

Parameter : 2

2

UNLOCK: ALL Tran-id: <000046AA477BF03D>

LOCK: TABLE PHYS Mode: IX Timeout: 0 Key: (usha,dt_test)

LOCK: PAGE PHYS Mode: X Timeout: 0 Key: (usha,dt_test,0.1)

UNLOCK: PAGE Key: (usha,dt_test,0.1)

LOCK: PAGE PHYS Mode: SIX Timeout: 0 Key: (usha,dt_test,0.2)

UNLOCK: PAGE Key: (usha,dt_test,0.2)

LOCK: PAGE PHYS Mode: X Timeout: 0 Key: (usha,dt_test,0.1)

LOCK: PAGE NOWT Mode: X Timeout: 0 Key: (usha,dt_test,0.0)

UNLOCK: PAGE Key: (usha,dt_test,0.1)

UNLOCK: ALL Tran-id: <000046AA477BF03E>