5.16 Select from DUAL

< Day Day Up >

Oracle provides a dummy table called DUAL, which is frequently used to retrieve system information. When converting references to DUAL, you have several options:

Change the SELECT statement to a VALUES statement.
Directly assign special registers to variables (in SQL PL).
Create a table/view called DUAL to mimic the Oracle DUAL table (DUAL would need to be created/aliased under all schemas).
Use the DB2 dummy table SYSIBM.SYSDUMMY1, which has a single row and one column, IBMREQD, with a value of Y.
Create a synonym of SYSIBM.SYSDUMMY1 called DUAL.

Table 5-6: Use of dummy table for system information
Oracle	DB2 UDB
select SYSDATE from DUAL	VALUES(CURRENT TIMESTAMP) INTO <variable> or select CURRENT TIMESTAMP from SYSIBM.SYSDUMMY1

In some circumstances it may be too costly to comb through all source code to convert references to DUAL and Oracle system variables to use DB2 syntax. As an alternative, you can preserve your existing SQL by defining a view named DUAL with the (column) value(s) you need. Example 5-39 illustrates.

Example 5-39: DB2 dummy view for system information

 create view dual (sysdate) as select CURRENT TIMESTAMP from SYSIBM.SYSDUMMY1 !

The result looks like

    db2 => select sysdate from dual    SYSDATE    --------------------------    2003-10-15-18.03.59.399071      1 record(s) selected.

< Day Day Up >