Extract DDL Issue

Toad for IBM DB2
1

Hi,

trying to extract DDL for a table that has a MQ Table generates the DDL script in the wrong order.

Whenever you have a MQ table with IMMEDIATE REFRESH you need to have the base table and an unique INDEX created prior to the CREATE MQ TABLE script.

Toad generates the scripts in the wrong order as following:

. CREATE BASE TABLE
. GRANTs BASE TABLE
. CREATE MQ TABLE
. GRANTs MQ TABLE
. CREATE INDEX BASE TABLE
. CREATE INDEX MQ TABLE

The correct order when you have IMMEDIATE REFRESH, should be:

. CREATE BASE TABLE
. GRANTs BASE TABLE
. CREATE INDEX BASE TABLE (at least the unique index must be created prior to the MQ table)
. CREATE MQ TABLE
. GRANTs MQ TABLE
. CREATE INDEX MQ TABLE

This is an old issue, still around in the new release.

BTW, it also happens when you use the SCHEMA COMPARE feature.

Regards,
Andre

2

Hi Andre,
Can you explain the reason of that,-is it just to improve performance while creating MQT to prevent fullscan?
P.S.
You can’t create MQT with REFRESH IMMEDIATE if base table doesn’t have PK.
(At least I haven’t been able to do so)
But PK will automatically create system generated unique index.

create table vlas.basemqt2 (col1 int not null, col2 varchar(50));
ALTER TABLE VLAS.BASEMQT2 ADD PRIMARY KEY (COL1);

CREATE TABLE “VLAS”.“DDD3”
( “COL1”, “COL2” )
AS (

select *
from vlas.basemqt2
) DATA INITIALLY DEFERRED
REFRESH IMMEDIATE
ENABLE QUERY OPTIMIZATION
IN “TS_MONITORING”;

3

Hi Vlas,

I see your point, but what I’m used to do is exactly the other way around, when I create the unique index DB2 will use it as the PK.

Anyway, even if I use your approach the issue is still there.

Have you tried to export the ddl you’ve just created and used the Toad Generated DDL to re-create the table? There is when the issue happens.

Regards,
Andre

4

I have reproduced the issue and created CR 102,999
Thanks