DDLOPT

Use the DDLOPT clause to add a specific option to the Db2® CREATE TABLE command generated by the DDLFILE clause.

The following options of the DDLOPT clause modify the Db2 CREATE TABLE command generated by the DDLFILE clause.

The WITH LIKE CLAUSE option inserts a LIKE keyword in the CREATE TABLE command of the DDL file. The Db2 LIKE keyword allows to use the definition of an existing table to create a new table.

The WITH LIKE CLAUSE option must be applied to a 'SELECT*' SQL query.

The IN option inserts a IN option in the CREATE TABLE command of the DDL file. This option specifies in which table space the table will be created.

The WITH LIKE CLAUSE and IN options cannot be specified more than once in a DDLOPT clause. Otherwise, an error message is sent.

DDLOPT

Syntax: DDLOPT ( WITH LIKE CLAUSE|IN ('tablespace_name','tablespace_name',...))
Variable: tablespace_name; A valid table space name.
Default: None.

Example

The following example illustrates the use of the DDLOPT clause with a WITH LIKE CLAUSE option in a control file:

GLOBAL CONNECT TO SAMPLE;
 
UNLOAD TABLESPACE

DB2 NO
LOCK NO
FLUSH BUFFERPOOLS NO

SELECT * FROM EMPLOYEE;
DDLFILE("ddlfile_out")
DDLOPT(WITH LIKE CLAUSE)
FORMAT DEL INTO I1050.EMPLOYEE_NEW;

When Optim™ High Performance Unload runs with this control file, the following execution report is generated:

[i1010@lat179(:) ~]$ db2hpu -i i1010 -f sysin_ddl -o out
INZM031I Optim High Performance Unload for Db2 06.01.00.001(130410) 64 bits 04/10/13 
         (Linux lat179 x86_64)
INZI473I Memory limitations: 'unlimited' for virtual memory and 'unlimited' for data segment
       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----10---+
000001 GLOBAL CONNECT TO SAMPLE;
000002 
000003 UNLOAD TABLESPACE
000004 
000005 DB2 NO
000006 LOCK NO
000007 FLUSH BUFFERPOOLS NO
000008 
000009 SELECT * FROM EMPLOYEE;
000010 DDLFILE("ddlfile_out")
000011 DDLOPT(WITH LIKE CLAUSE)
000012 FORMAT DEL INTO I1050.EMPLOYEE_NEW;

INZU462I HPU control step start: 09:43:29.472.
INZU463I HPU control step end  : 09:43:29.835.
INZU464I HPU run step start    : 09:43:30.023.
INZU410I HPU utility has unloaded 42 rows on lat179 host for I1010.EMPLOYEE in out.
INZU622I HPU utility has generated the DDL file ddlfile_out.
INZU465I HPU run step end      : 09:43:30.027.
INZI441I HPU successfully ended: Real time -> 0m0.555160s
User time -> 0m0.024996s : Parent -> 0m0.024996s, Children -> 0m0.000000s
Syst time -> 0m0.013997s : Parent -> 0m0.013997s, Children -> 0m0.000000s

and the following DDL file is generated:

[i1010@lat179(:) ~]$ cat ddlfile_out 
CREATE TABLE "I1050"."EMPLOYEE_NEW" LIKE "I1050"."EMPLOYEE";