Subquery access

The EXPLAIN output in the PLAN_TABLE sometimes shows the position and order in which subqueries are executed.

Begin program-specific programming interface information. The subqueries are indicated by a row in the PLAN_TABLE having TNAME="DSNWFQB(nn)", where 'nn' is the query block number associated with the subquery, and TABLETYPE='S'. In PLAN_TABLE, the PARENT_PLANNO column corresponds to the plan number in the parent query block where the correlated subquery is invoked for correlated subqueries. For non-correlated subqueries it corresponds to the plan number in the parent query block that represents the work file for the subquery.

Non-correlated subqueries

The EXPLAIN output below is for the non-correlated form of the following subquery:

SELECT * FROM T1 WHERE T1.C2 IN (SELECT T2.C2 FROM T2, T3 WHERE T2.C1 = T3.C1)

Table 1. EXPLAIN output for the non-correlated subquery
QB NO	PLAN NO	METHOD	TNAME	AC TYPE	MC	AC NAME	SC_ JN	PAR_ QB	PAR_ PNO	QB TYPE	TB TYPE
1	1	0	DSNWFQB(02)	R	0		N	0	0	SELECT	W
1	2	1	T1	I	1	T1_1X_C2	Y	0	0	SELECT	T
2	1	0	T2	R	0		N	1	1	NCOSUB	T
2	2	1	T3	I	1	T3_X_C1	N	1	1	NCOSUB	T

In the example above, the row corresponding to QBNO=2 and PLANNO=1 has PARENT_PLANNO (abbreviated as PAR_PNO) = 1 and PARENT_QBNO (abbreviated as PAR_QB) = 1. This means that the row corresponding to QBNO=1 and PLANNO=1 is the parent row. The sequence of execution flows from parent to child, then back to parent after the child rows are exhausted. In the example above that means the sequence of execution is (QBNO, PLANNO): (1,1) , (2,1), (2,2), (1,2).

Correlated subqueries

The following example shows a correlated subquery and the associated EXPLAIN output:

 SELECT * FROM T1 
   WHERE EXISTS (SELECT 1 FROM T2, T3 
                   WHERE T2.C1 = T3.C1 AND T2.C2 = T1.C2)

Table 2. EXPLAIN output for the correlated subquery:
QB NO	PLAN NO	METHOD	TNAME	AC TYPE	MC	ACNAME	SC_ JN	PAR_ QB	PAR_ PNO	QBTYPE	TB TYPE
1	10	0	T1	R	0		N	0	0	SELECT	T
2	1	1	T2	I	1	T2_IX_C2	N	1	1	CORSUB	T
2	2	1	T3	I	1	T3_IX_C1	N	1	1	CORSUB	T

Subqueries transformed to joins

A plan table shows that a subquery is transformed into a join by the value in column QBLOCKNO.

If the subquery is not transformed into a join, that means it is executed in a separate operation, and its value of QBLOCKNO is greater than the value for the outer query.
If the subquery is transformed into a join, it and the outer query have the same value of QBLOCKNO. A join is also indicated by a value of 1, 2, or 4 in column METHOD.