DB disconnects during table alterations

[Former Member]

SQL Anywhere version 11

**Existing table**

CREATE TABLE "DBA"."SYscans" (
    "conum" "DCONUM" NOT NULL,
    "module" SMALLINT NOT NULL,
    "recseq" "DINTEGER4" NOT NULL,
    "scandate" "datetime" NOT NULL DEFAULT CURRENT TIMESTAMP,
    "scandesc" "DCHAR35" NULL,
    "image" LONG BINARY NULL,
    "fileext" CHAR(5) NULL,
    PRIMARY KEY ( "conum" ASC, "module" ASC, "recseq" ASC, "scandate" ASC )
) IN "system";
COMMENT ON COLUMN "DBA"."SYscans"."module" IS '10-SY,20-GL,etc.';

**Script**

alter table syscans drop primary key;
alter table syscans add ID1 dkeytype not null default '          ';
alter table syscans add ID2 dkeytype not null default '          ';
alter table syscans add primary key(conum,module,ID1,ID2,recseq,scandate)

Question

when running the above script using dbisqlc.exe, the second statement executes for about a minute and then the database service stops. They're 21,700 rows in the table. This problem exists in several installations so I doubt that it's environment related.

Any suggestions on troubleshooting the problem would be greatly appreciated.

Thanks, Bill

[VolkerBarth]

Are there other connections using the table when running the ALTER TABLE statements? If so, they may lead to (temporary) blocks. Adding columns with NON-NULL values (as here with the defaults) may lead to split rows. But all that explain that ALTER TABLE may take some time but would not explain a database stop...

What does the engine's -o console.log tell?

---

For logging dbisqlc's output, see here...

[Former Member]

Which 11 are you using?

If you are using a version of 11.0.0 or an early build of 11.0.1 your crash may be due to something that was fixed by CR#738617 which was fixed in 11.0.1 builds 2979+.