DUAL table

Any unqualified reference to the table with the name DUAL is resolved as a built-in view that returns one row and one column. The name of the column is DUMMY and its value is 'X'. The DUAL table is similar to the SYSIBM.SYSDUMMY1 table.

Enablement

To enable DUAL table support, set the DB2_COMPATIBILITY_VECTOR registry variable to hexadecimal 0x02 (bit position 2), then stop and restart the instance:

db2set DB2_COMPATIBILITY_VECTOR=02
db2stop
db2start

To activate all compatibility features for Oracle applications, set the DB2_COMPATIBILITY_VECTOR registry variable to ORA, then stop and restart the instance:

db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start

Unqualified table references to the DUAL table are resolved as SYSIBM.DUAL.

If a user-defined table named DUAL exists, the Db2® server resolves a table reference to the user-defined table.

Example 1

Generate a random number by selecting from DUAL.

   SELECT RAND() AS RANDOM_NUMBER FROM DUAL

Example 2

Retrieve the value of the CURRENT SCHEMA special register.

   SET SCHEMA = MYSCHEMA;
   SELECT CURRENT SCHEMA AS CURRENT_SCHEMA FROM DUAL;