DISALLOW_SEL_INTO_UNION in macro DSN6SPRM

Specifies whether to disallow a UNION or UNION ALL as the outermost from-clause of a SELECT INTO statement, which is invalid SQL syntax. Such statements were inadvertently allowed before Db2 12. However, in Db2 12 they are disallowed by default.

Acceptable values: YES, NO
Default: Start of changeYESEnd of change
Online changeable: YES
Data sharing scope: Group
DSNZPxxx: DSN6SPRM DISALLOW_SEL_INTO_UNION
YES
SELECT INTO statements with UNION or UNION ALL as the outermost from-clause are not supported. This setting enforces valid SQL syntax.
NO
SELECT INTO statements with UNION or UNION ALL as the outermost from-clause are accepted. This setting tolerates invalid SQL syntax.
Important: Start of changeIn Db2 12, the default value of DISALLOW_SEL_INTO_UNION changes from NO to YES.

DISALLOW_SEL_INTO_UNION is also deprecated. Although it remains supported, support is likely to be removed eventually. Identify any packages that use UNION or UNION ALL in the from-clause of a SELECT INTO statement and correct them as necessary.

End of change
You can identify affected packages while DISALLOW_SEL_INTO_UNION is set to NO by binding suspected packages into a dummy collection ID with EXPLAIN(ONLY) and monitoring IFCID 0376 records. Use the following procedure:
  1. Issue the following SQL statement to generate a list of BIND commands.
    SELECT 'BIND PACKAGE(DUMMYCOL) COPY(' ||
       COLLID || '.' || NAME || ') ' ||
       CASE WHEN(VERSION <> '')
            THEN 'COPYVER(' || VERSION || ') '
            ELSE '' END ||
       'EXPLAIN(ONLY)'
FROM SYSIBM.SYSPACKSTMT
WHERE STATEMENT LIKE '%SELECT%INTO%UNION%';

    The statement generates output similar to the following result:

    BIND PACKAGE(DUMMYCOL) COPY(DSN_DEFAULT_COLLID_PLAY01.PLAY01) EXPLAIN(ONLY)
  2. Copy the SELECT statement in a BIND job. If the result is longer than 72 characters, formatting is required.
  3. Start and collect the IFCID 0376 trace.
  4. Run the bind job that you created.
  5. Stop the IFCID 0376 trace and analyze the output.