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, starting in DB2 12 they are disallowed by default.
Acceptable values: | YES, NO |
Default: | NO |
Online changeable: | YES |
DSNZPxxx: | DSN6SPRM DISALLOW_SEL_INTO_UNION |
Important: The DISALLOW_SEL_INTO_UNION is deprecated. That is, it remains supported,
but 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.
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:
- 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)
- Copy the SELECT statement in a BIND job. If the result is longer than 72 characters, formatting is required.
- Start and collect the IFCID 0376 trace.
- Run the bind job that you created.
- Stop the IFCID 0376 trace and analyze the output.