skip to main content
Designing and coding the IP : Query execution analysis : SQL Engine Plan example
 

SQL Engine Plan example

The SQL Engine Plan option provides details about the query. The lines with the asterisk characters (*) in the following example are provided to section the example according to the information provided.
Query string
******************* Query ****************************
Query:<SELECT empno, ename FROM emp>
Parsed Query – Query Parse tree – the column and table numbers are reported as DAM_NOT_SET(-1) since query is not yet validated.
 
********************* Parsed Query **********************
SELECT ( -1.empno), ( -1.ename),
FROM emp (-1),
Validated Query – Query parse tree where each table and column reference is identified by table number. Tables are numbered in the order they appear in query starting at 0. Columns are numbered based on the order they get reported by the table schema. Column names are qualified with the table number they belong to.
********************* Validated Query **********************
SELECT ( 0.empno), ( 0.ename),
FROM SCHEMA.OAUSER.emp (0)( 0.empno, 0.ename, ::),
******************* End Validated Query ********************
Execution Plan and Query Index condition
********** Execution Plan for SIMPLE STMT *************
SELECT ( 0.empno), ( 0.ename), ( 0.job), ( 0.hiredate), ( 0.sal), ( 0.comm), ( 0.deptno),
FROM SCHEMA.OAUSER.emp (0)( 0.empno, 0.ename, 0.job, 0.hiredate, 0.sal, 0.comm, 0.deptno, ::0.empno, ),
WHERE ( ( 0.empno) = ( 2) )
************** Sub Query:1 **************
Search List:
( 0.empno) = ( 2)
***************** End of Plan ******************
********** IP is being called to execute SubQuery on Table:<emp> with Search Column:<empno>. hstmt:<0x6be880>
Query Result Specification – column name, data type, precision, scale
********************* Query Result Specification **********************
Result Column:0 – Name:empno, XoType=4, Nullable=0, Precision = 10, Scale=0
Result Column:1 – Name:ename, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:2 – Name:job, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:3 – Name:hiredate, XoType=11, Nullable=1, Precision = 19, Scale=0
Result Column:4 – Name:sal, XoType=8, Nullable=1, Precision = 15, Scale=0
Result Column:5 – Name:comm, XoType=7, Nullable=1, Precision = 7, Scale=0
Result Column:6 – Name:deptno, XoType=4, Nullable=1, Precision = 10, Scale=0