MERGE

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges that are held by the privilege set that is defined below must include at least one of the following privileges:

• SYSADM authority
• Ownership of the table
• DATAACCESS authority
• If the search condition contains a reference to a column of the table or view, the SELECT privilege for the referenced table or view
• If the delete operation is specified, the DELETE privilege for the table or view
• If the insert operation is specified, the INSERT privilege for the table or view
• If the update operation is specified, at least one of the following privileges is required:
  • the UPDATE privilege for the table or view
  • the UPDATE privilege on each column that is updated
  • If the right side of the assignment clause contains a reference to a column of the table or view, the SELECT privilege for the referenced table or view

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

If the insert operation or assignment clause includes a subquery, the privileges that are held by the privilege set must also include at least one of the following privileges:

• SYSADM authority
• The SELECT privilege on every table or view that is identified in the subquery
• Ownership of the tables or views that are identified in the subquery
• DATAACCESS authority

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the statement is dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run, bind, define, or invoke) and is summarized in Table 1. (For more information on these behaviors, including a list of the DYNAMICRULES bind option values that determine them, see Authorization IDs and dynamic SQL.)

Description

INTO table-name or view-name

Identifies the target of the insert or update operations of the MERGE statement. The name must identify a table or view that exists at the current server. The name must not identify:

• A catalog table
• A directory table
• A created global temporary table
• A read-only view
• A system-maintained materialized query table
• A table that is implicitly created for an XML column
• An accelerator-only table

If a view is specified as the target of the MERGE statement, the view must not be defined with any INSTEAD OF triggers.

AS correlation-name

correlation-name provides an alternate name that can be used when referencing columns of the intermediate result table. If no correlation-name is specified, the name of the intermediate result table is the name of the target table or view of the MERGE statement. Otherwise, the name is the correlation-name.

include-columns

Specifies a set of columns that are included, along with the columns of the specified table or view, in the result table of the MERGE statement when it is nested in the FROM clause of the outer fullselect that is used in a SELECT statement, or in a SELECT INTO statement. The included columns are appended to the end of the list of columns that are identified by table-name or view-name. If a value is not specified for an included column, a null value is returned for that column.

INCLUDE

Introduces a list of columns that is to be included in the result table of the MERGE statement. The included columns are only available if the MERGE statement is nested in the FROM clause of a SELECT statement or a SELECT INTO statement. INCLUDE can only be specified when the MERGE statement is nested in the FROM clause of a SELECT statement.

column-name

Specifies the name for a column of the result table of the MERGE statement that is not the same name as another included column or a column in the table or view that is specified in table-name or view-name.

data-type

Specifies the data type of the included column. The included columns are nullable.

Columns with the following data types can not be used as INCLUDE columns:

• LONG VARCHAR,
• LONG VARGRAPHIC,
• XML
• LOBs
• distinct types that are based on any of the listed data types.

built-in-type

Specifies a built-in data type. See CREATE TABLE for a description of each built-in type.

distinct-type

Specifies a distinct type. Any length, precision, or scale attributes for the column are those of the source type of the distinct type as specified by using the CREATE TYPE statement.

USING source-values

Specifies the row or rows to be merged into the target table or view. VALUES values-single-row specifies a single row of source data. VALUES values-multiple-row specifies multiple rows of source data.

expression

Specifies an expression of the type that is described in Expressions. The expression must not include a column name. The expression must not reference a NEXT VALUE or PREVIOUS VALUE expression. If the expression is a single host variable, the host variable can identify a structure. Any host variable or structure that is specified must be described in the application program according to the rules for declaring host structures and variables.

If the expression is a host variable, or if a host variable is being explicitly cast, the host variable can include an indicator variable or an indicator array (in the case of a host structure). Either indicator variables or indicator arrays can be enabled for extended indicator variables.

To provide a null value, specify the NULL keyword on a CAST specification.

NULL

Specifies a null value.

The column of the source-table corresponding to the NULL keyword is used to determine the data type of the null value. The column of the source-table must be referenced elsewhere in the MERGE statement such that its data type can be determined from the context in which it is used, and all such references must resolve to the same data type. References to date-time data types and CHAR or VARCHAR are considered the same type.

host-variable-array

Specifies a host variable array. Each host variable array must be defined in the application program in accordance with the rules for declaring an array. A host variable array contains the data to merge into a target column. The number of rows must be less than or equal to the dimension of each of the host variable arrays. An optional indicator array can be specified for each host variable array. An indicator array should be specified if the SQLTYPE of any SQLVAR occurrence indicates that a column is nullable. The indicator array can be enabled for extended indicator variables. The dimension of the indicator array must be large enough to contain an indicator for each row of input data.

A host structure is not supported in host-variable-array.

host-variable-array is supported in C/C++, COBOL, and PL/I.

FOR host-variable or integer-constant ROWS

Specifies the number of rows to merge. For a dynamic MERGE statement, this clause can be specified on the EXECUTE statement. host-variable or integer-constant is assigned to a value k. If host-variable is specified, it must be an exact numeric type with a scale of zero and must not include an indicator variable. k must be in the range of 1 to 32767. k rows are merged into the target from the specified source data.

If a parameter marker is specified in FOR n ROWS, a value must be provided with the USING clause of the associated EXECUTE statement.

AS correlation-name

Specifies a correlation name for the source-values.

column-name

Specifies a column name to associate the input data to the SET assignment-clause for an update operation or the VALUES clause for an insert operation.

ON search-condition

Specifies join conditions between the source-values and the target table or view.

Each column-name in the search condition must name a column of the target table, view, or source-values. A subquery is not allowed in the search-condition. If a column-name exists in both the target and the source-values, the column name must be qualified.

For each row of the source-values, the search-condition is applied to each row of the target. If the search-condition is evaluated as true and the target is not empty, the specified WHEN MATCHED clause is used. Otherwise, the specified WHEN NOT MATCHED clause is used.

WHEN MATCHED or WHEN NOT MATCHED

Specifies the condition under which the modification-operation is run.

WHEN MATCHED

Specifies the operation to perform on the rows where the ON search-condition is true and the target is not empty. Only UPDATE can be specified after the THEN clause. WHEN MATCHED must not be specified more than one time.

WHEN NOT MATCHED

Specifies the operation to perform on the rows where the ON search-condition is false or unknown, or the target is empty. Only INSERT can be specified after the THEN clause. WHEN NOT MATCHED must not be specified more than one time.

THEN update-operation or THEN insert-operation

Specifies the operation to run when the matching-condition evaluates to true.

UPDATE SET

Specifies the update operation to run when the matching-condition evaluates to true.

When extended indicator variables are enabled, a column of the source table must not be referenced multiple times in a single modification-operation. Extended indicator variables are enabled when EXTENDEDINDICATOR(YES) is used, or when the WITH EXTENDED INDICATORS prepare attribute has been specified for the MERGE statement.

The rows that are updated from a source-row are subject to more updates by subsequent source-rows in the same statement. The update is cumulative.

An update-operation in a MERGE statement will not rest the AREO* status on a table.

assignment-clause

Specifies a list of column updates.

column-name

Identifies a column to update. column-name must identify a column of the specified table or view, and that column must be updatable. The column must not be a generated column, or a column of a view that is derived from a scalar function, a constant, or an expression. column-name can also identify an included column. The same column-name must not be specified more than one time. A view column that is derived from the same column as another column of the view can be updated, but both columns cannot be updated in the same MERGE statement.

Assignments to included columns are only processed when the MERGE statement is nested in the FROM clause of a SELECT statement or a SELECT INTO statement. There must be at least one assignment clause that specifies a column-name that is not an included column.

expression

Specifies the new value of the column. The expression is any expression of the type that is described in Expressions. The expression must not include an aggregate function.

An expression can contain references to columns of source-values or target table or view. A column name is first checked as a column of the target, and then checked as a column of the source table. For each row that is updated, the value of a target column in an expression is the value of the column in the row before the row is updated. expression cannot contain references to an included column.

If expression is a reference to a single column of the source-values, the source-values column value might have been specified with an extended indicator variable value. The effects of such indicator variables apply to the corresponding target columns of the assignment-clause.

When extended indicator variables are enabled, an expression must not be more complex than a reference to a single column of the source table, or a single host variable if the indicator is set to an extended indicator value of default (-5) or unassigned (-7). In addition, a CAST specification can be used if either:

• The target column is defined as nullable.
• the target column is defined as NOT NULL with a non-null default, the source of the CAST specification is a single host variable, and the data attributes (data type, length, precision, and scale) of the host variable are the same as the result of the cast specification.

DEFAULT

Specifies the default value for the column. DEFAULT can be specified only for columns that have a default value. The value that is assigned depends on how the column is defined. For more information about default values, see the description of the DEFAULT clause in CREATE TABLE.

NULL

Specifies the null value as the new value of the column. Specify NULL only for nullable columns.

insert-operation

Specifies the insert operation that is to be executed for the rows where the matching-condition evaluates to true.

The rows that are inserted from a source-row are immediately subject for update by subsequent source-row in the same statement.

INSERT

Specifies a list of column names and row value expressions to use for the insert operation.

The number of values for the row in the row-value expression must be equal to the number of names in the insert column list. The first value is inserted into the first column in the list, the second value into the second column, and so on.

column-name

Specifies the columns for which the insert values are provided. Each name must identify a column of the table or view.

If an included column is not specified in the list of column names, the value of the included column is set to null. The column list cannot contain only included columns.

The same column must not be specified more than one time. A view column that cannot accept insert values must not be specified. A view column that cannot accept insert values must not be specified.

If the object of the operation is a view that contains columns that cannot accept insert values, a list of column names must be specified and the list must not specify these columns.

Omission of the column list is an implicit specification of a list in which every column of the table (that is not defined as implicitly hidden) or view is identified in left-to-right order. This list is established when the statement is prepared and therefore does not include columns that were added to the table after the statement was prepared.

VALUES

Introduces one or more rows of values to insert.

expression

Specifies an expression of the type that does not include a column name of the target. If expression is a host variable, the host variable can include an indicator variable, or in the case of a host structure, an indicator array. When extended indicator variables are enabled, an expression must not be more complex than a reference to a single host variable if the indicator is set to an extended indicator value of default (-5) or unassigned (-7).

In addition, a CAST specification can be used if either:

• The target column is defined as nullable.
• the target column is defined as NOT NULL with a non-null default, the source of the CAST specification is a single host variable, and the data attributes (data type, length, precision, and scale) of the host variable are the same as the result of the cast specification.

DEFAULT

Specifies to assign the default value to the column. DEFAULT must only be specified for columns that have a default value. If the column is specified in the INCLUDE column list, the column value is set to null.

DEFAULT must be specified for a column that is defined as GENERATED ALWAYS. A valid value can be specified for a column that is defined as GENERATED BY DEFAULT.

NULL

Specifies the null value as the value of the column. Specify NULL only for nullable columns.

NOT ATOMIC CONTINUE ON SQLEXCEPTION

The rows of input data are processed separately. Any statement level triggers are processed for each row of source data that is processed, and the transition table includes the individual row that was processed. When errors are encountered and this option is in effect, processing continues, and some of the specified rows will not be processed. In this case, if an appropriate trigger is defined on the underlying base table, the statement level trigger will only be activated for rows that were successfully processed.

Regardless of the failure of any particular source row, the MERGE statement will not undo any changes that are made to the database by the statement. Merge will be attempted for rows that follow the failed row. However, the minimum level of atomicity is at least that of a single source row (that is, it is not possible for a partial merge to complete), including any triggers that might have been activated as a result of the MERGE statement.

QUERYNO integer

Specifies the number for this SQL statement that is used in EXPLAIN output and trace records. The number is used for the QUERYNO column of the plan table for the rows that contain information about this SQL statement. This number is also used in the QUERYNO column of the SYSIBM.SYSSTMT and SYSIBM.SYSPACKSTMT catalog tables.

If QUERYNO is not specified, the number that is associated with the SQL statement is the statement number that is assigned during precompilation. Thus, if the application program is changed and then precompiled, the statement number might change.

Notes

Trigger considerations:

A MERGE statement might cause triggers to be activated. A trigger might cause other statements to be executed or raise error conditions depending on the source data values. A before-update or before-insert trigger processes immediately before the update or insert operation.

If a source row results in an insert, any after-insert triggers are activated after the insert operation completes.

If a source row results in updates, any after-update triggers are activated after all of the update operations complete.

SQLCA and GET DIAGNOSTICS considerations:

The GET DIAGNOSTICS statement can be used immediately after the MERGE statement to check which input rows fail during the merge operation. The GET DIAGNOSTICS statement information item, NUMBER, indicates the number of conditions that are raised. The GET DIAGNOSTICS condition information item, DB2_ROW_NUMBER, indicates the input source rows that cause an error.

Indexes with VARBINARY columns:

Suppose that the identified table has an index on a VARBINARY column or a column that is a distinct type that is based on VARBINARY data type. In that case, that index column cannot specify the DESC attribute. To use the SQL data change operation on the identified table, either drop the index or alter the data type of the column to BINARY and then rebuild the index.

Considerations for a MERGE without a column list in insert-operation:

A MERGE statement without a specified column list as part of insert-operation does not include implicitly hidden columns. Therefore, such columns must have a defined default value.

Considerations for non-atomic processing of a MERGE statement:

When NOT ATOMIC is specified, the rows of source data are processed separately. Any references to special registers, sequence expressions, and functions in the MERGE statement are evaluated as each row of source data is processed. Statement level triggers are activated as each row of source data is processed.

If one or more errors occur during the operation for a row of source data, processing continues. The row that was being processed at the time of the error is not inserted or updated. Execution continues with the next row to be processed, and any other changes that are made during the execution of the multiple-row MERGE statement are not backed out. However, the processing of an individual row is an atomic action.

DRDA considerations:

DB2 Connect™ Version 9.1 and subsequent releases support the MERGE statement. The support is for CLI only, with no embedded static SQL support.

When you run a MERGE statement at a DB2® for z/OS® requester, cases might exist where the requestor does not know the number of rows in the source table. This situation includes the following cases:

• For static or dynamic MERGE statements, of the FOR n ROWS clause contains a constant value for n.
• For dynamic MERGE statements, of host variables are specified on the USING clause of an EXECUTE statement.

For both of these cases, if the number of rows in the source table is not known, the requester might send more data than is required to the server. The number of rows that are processed is correct because the server knows the correct numbers of rows to process. However, performance might be adversely affected. Consider the following example:

...long serial num [10];
struct { short len;
char data [18];
       }
name[20]...
EXEC SQL 
MERGE INTO T1
  USING (VALUES (:serial_num, :name))
  FOR 5 ROWS...

When this statement is run at the requester, the number of rows to merge (five) is not known. As a result, the requester sends 10 values for serial-name and name to the server because 10 is the size of the smallest host variable array and is, therefore, the maximum number of rows that can merge without causing an error.

Do the following to help minimize performance problems:

• Avoid using numeric constants in the FOR n ROWS clause of the MERGE statement. For static MERGE statements, avoiding numeric constants ensures that the values for n are known at the requester.
• For dynamic MERGE statements, use the USING DESCRIPTOR clause instead of the USING host-variable clause on the EXECUTE statement. If a USING DESCRIPTOR clause is used on the EXECUTE statement, the value for n must be indicated in the descriptor.
• If either of the previous methods cannot be used, perform the following actions:
  • Make your host variable arrays as small as possible, or declare that the size of your host variable arrays are the size of n in the descriptor. This action avoids sending many unused host variable array entries to the server.
  • Ensure that varying length string arrays are initialized to a length of 0 (zero). Doing so minimizes the amount of data that is sent to the server.
  • Ensure that decimal host variable arrays are initialized to valid values. Doing so causes the requester to avoid sending a negative SQLCODE if the requester encounters invalid decimal data.

Extended indicator variable usage:

When extended indicator variables are enabled, negative indicator values outside the range of -1 through -7 must not be specified. Also, the default and unassigned extended indicator values must not be used in contexts in which they are not supported.

Extended indicator variables in the assignment-clause:

Assigning the extended indicator a value of unassigned leaves the target column set to its current value, as if it had not been specified in the statement. Assigning the extended indicator a value of default assigns the default value to the column.

The assignment-clause must not assign all target columns to an extended indicator value of unassigned.

Extended indicator variables in the insert-operation:

In insert-operation, a value of unassigned sets the column to its default value.

Extended indicator variables and update triggers:

If a target column is assigned an extended indicator value of unassigned, that column is not considered to have been updated. That column is treated as if it had not been specified in the OF column-name list of any update trigger that is defined on the target table.

Extended indicator variables and insert triggers:

The activation of insert triggers is not affected by the use of extended indicator variables. Suppose that all columns in the implicit or explicit column list are assigned an extended indicator value of unassigned or default. Then, assume that an insert operation where all columns are assigned to the respective default values is attempted. If that operation is successful, the insert trigger is activated.

Table space data compression during an insert operation:

If the table space is defined with the COMPRESS YES option, and data is inserted into a table in the table space, the first rows are stored uncompressed. When a amount of data that is determined by DB2 is inserted into the table, a compression dictionary is created and stored in the table space. The rows that are inserted into the table after the dictionary is created are stored compressed by using the compression dictionary.

System-period temporal tables:

When a MERGE statement is processed for a system-period temporal table, the rows are affected in the same way as if the specific data change operation was invoked.

Tables with enforced row and column access controls:

For information about how enabled row permissions and column masks affect the update and insert operations in the MERGE statement, see the INSERT and UPDATE statement information.