DB2 Version 10.1 for Linux, UNIX, and Windows

DUAL table

The DB2® data server resolves any unqualified reference to the DUAL table as a built-in view that returns one row and one column that is named DUMMY, whose value is 'X'.

Enablement

You enable DUAL table support by setting of the DB2_COMPATIBILITY_VECTOR registry variable to hexadecimal value 0x02 (bit position 2), and then stop and restart the instance to have the new setting take effect.

db2set DB2_COMPATIBILITY_VECTOR=02
db2stop
db2start

To take full advantage of the DB2 compatibility features for Oracle applications, the recommended setting for the DB2_COMPATIBILITY_VECTOR is ORA, which sets all of the compatibility bits.

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 only if the reference is explicitly qualified.

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;