Icon View Incident Report

Serious Serious
Reported By: David Loving
Reported On: 9/3/2008
For: Version 2.01 Build 4
# 2771 Adding a Constraint or Index with Large Page Size Requirement Can Cause Error

The following is a sql script file created by reverse engineering my database. There is an error when trying to run this script to create a new database from scratch. It halts on the zonefielddef table creating the zidnamesourcekey constraint. It is not recognizing the INDEX PAGE SIZE 8288.

I have to remove the constraint, run the scrip to create my tables and then manually add the constraint after the INDEX PAGE SIZE is changed from the default of 8192 to 8288.

SCRIPT
BEGIN

   EXECUTE IMMEDIATE 'CREATE TABLE "zonedef"
(
"zid" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"name" VARCHAR(512) COLLATE "UNI" NOT NULL,
"desc" CLOB COLLATE "UNI",
"kind" INTEGER,
"umode" CLOB COLLATE "UNI",
"lmode" CLOB COLLATE "UNI",
"utopid" INTEGER,
"ltopid" INTEGER,
"udepth" FLOAT,
"ldepth" FLOAT,
"uoffset" FLOAT,
"loffset" FLOAT,
"remarks" CLOB COLLATE "UNI",
"adddate" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"chgdate" TIMESTAMP,
CONSTRAINT "primary" PRIMARY KEY ("zid"),
CONSTRAINT "namekey" UNIQUE ("name")
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768';
   
   EXECUTE IMMEDIATE 'CREATE TRIGGER "zonedef_update" BEFORE UPDATE ON "zonedef"
BEGIN
    set NEWROW.chgdate = CURRENT_TIMESTAMP;
END
';
   
   EXECUTE IMMEDIATE 'CREATE TABLE "zonefielddef"
(
"fid" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"zid" INTEGER NOT NULL,
"name" VARCHAR(512) COLLATE "UNI" NOT NULL,
"source" VARCHAR(512) COLLATE "UNI" NOT NULL,
"desc" CLOB COLLATE "UNI",
"units" CLOB COLLATE "UNI",
"kind" CLOB COLLATE "UNI",
"ndec" INTEGER,
"flags" INTEGER,
"unitstype" INTEGER,
"remarks" CLOB COLLATE "UNI",
"adddate" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"chgdate" TIMESTAMP,
CONSTRAINT "primary" PRIMARY KEY ("fid"),
CONSTRAINT "zidkey" FOREIGN KEY ("zid") REFERENCES "zonedef" ("zid"),
CONSTRAINT "zidfidkey" UNIQUE ("zid", "fid"),
CONSTRAINT "zidnamesourcekey" UNIQUE ("zid", "name", "source")
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 8288
BLOB BLOCK SIZE 1024
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768';
   
   EXECUTE IMMEDIATE 'CREATE TRIGGER "zonedeffield_update" BEFORE UPDATE ON "zonefielddef"
BEGIN
    set NEWROW.chgdate = CURRENT_TIMESTAMP;
END
';
END



Comments Comments and Workarounds
The workaround is as indicated by the reporter.


Resolution Resolution
Fixed Problem on 9/5/2008 in version 2.01 build 5


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image