Using DB2 As Server Database, [Apply/DDL] Button On Siebel Tools Generates DDL With 'rename' Statement. (Doc ID 2187962.1)

Last updated on AUGUST 11, 2022

Applies to:

Symptoms

When DB2 is used as the server database,  [Apply/DDL] button on table object may generate 'rename' statement unexpectedly.

The issue can be replicated with the following steps:

- Use DB2 as server database.
- Using Siebel Tools Client, add new extension column.
- Using [Apply/DDL] button then [Generate DDL] option, generate DDL that is supposed to contain ‘alter table add’ statement for the new extension column.
** Using [Apply/DDL] button then [Apply] option runs the DDL directly.

Generated DDL contains the following statements. S_ORDER table is used for this example.
1) rename SIEBEL.S_ORDER to SNORDER
** This table holds the original data.

2) create table SIEBEL.S_ORDER
** It includes the newly added column.

3) insert into SIEBEL.S_ORDER (list of columns) select (list of columns) from SIEBEL.SNORDER
** It copies data from SNORDER (the original S_ORDER) to the new one. Newly added column is excluded from the list of columns.

4) drop table SIEBEL.SNORDER
** It removes the original table and leave the new one.

5) Other operations such as grant and create index follow.

Result:
Since a new column has been added, [Generate DDL] should generate just 'alter table add' statement.

There are couple of potential issues.
- It indicates the possibility of modification of the definition of the existing column(s).
- Although the final results should be the same for the newly added column, operation with 'rename' may take much longer process time comparing to 'alter table add'. It involves data movement between the original table and the newly created table. If the number of exiting records are large, it may take very long time to process.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.