When a MERGE statement is used (QBLOCK_TYPE ='MERGE')

You can determine whether a MERGE statement was used and how it was processed by analyzing the QBLOCK_TYPE and PARENT_QBLOCKNO columns.

Begin program-specific programming interface information. If the QBLOCK_TYPE column contains MERGE, a MERGE statement was used. In most cases, a MERGE is processed in multiple query blocks with QBLOCK_TYPEs MERGE, UPDATE, and INSERT.

Example

Consider the following MERGE statement:

MERGE INTO ARCHIVE AR
  USING VALUES (:hv_activity, :hv_description) FOR 
    :hv_nrows ROWS as AC (ACTIVITY, DESCRIPTION)
    ON (AR.ACTIVITY = AC.ACTIVITY)
  WHEN MATCHED THEN UPDATE SET DESCRIPTION = AC.DESCRIPTION
  WHEN NOT MATCHED THEN INSERT (ACTIVITY, DESCRIPTION)
   VALUES (AC.ACTIVITY, AC.DESCRIPTION)
   NOT ATOMIC CONTINUE ON SQL EXCEPTION

The following table shows the corresponding plan table for the MERGE statement.

Table 1. Plan table for MERGE statement
QBLOCK NO	PARENT_ QBLOCKNO	QBLOCK_ TYPE	PLANNO	TNAME	TABLE_ TYPE	JOIN_ TYPE	METHOD
1	0	MERGE	1	ACTIVITIES	B
1	0	MERGE	2	ARCHIVE	T	L	1
2	1	UPDATE	1	ARCHIVE	T
3	1	INSERT	1	ARCHIVE	T

End program-specific programming interface information.