powered by Jive Software

DDL Errors on DB2 on PRIMARY KEY mucAffiliation mucMember

When I execute the script messenger_db2.sql to create the jive database, the ddl defines a primary key greater than 1024 bytes - this is an error in DB2. Obviously, the subsequent CREATE INDEX statements fail as well, since the tables are not created.

I am using DB2 V8.1.2 on RH Linux. Jive Messenger V2.1.0 installed from jive_messenger_2_1_0.rpm.

Any workarounds or fixes for this?

This is an excerpt from the DB2 doc concerning PRIMARY KEY:

PRIMARY KEY (column-name,…)

Defines a primary key composed of the identified columns. The clause must not be specified more than once, and the identified columns must be defined as NOT NULL. Each column-name must identify a column of the table, and the same column must not be identified more than once.

The number of identified columns must not exceed 16, and the sum of their stored lengths must not exceed 1024 (refer to Byte Counts for the stored lengths).

These are the failing CREATEs:

CREATE TABLE mucAffiliation (

roomID INTEGER NOT NULL,

jid VARCHAR(1024) NOT NULL,

affiliation INTEGER NOT NULL,

CONSTRAINT mucAffiliation_pk PRIMARY KEY (roomID, jid)

);

CREATE TABLE mucMember (

roomID INTEGER NOT NULL,

jid VARCHAR(1024) NOT NULL,

nickname VARCHAR(255) NULL,

firstName VARCHAR(100) NULL,

lastName VARCHAR(100) NULL,

url VARCHAR(100) NULL,

email VARCHAR(100) NULL,

faqentry VARCHAR(100) NULL,

CONSTRAINT mucMember_pk PRIMARY KEY (roomID, jid)

);

Bob,

Hmm… a JID is supposed to be allowed to be up to 1024 characters long. If DB2 doesn’‘t suppor this, we’‘ll just need to make the JID field shorter. Would 1000 work? The only ramification is that the max JID length would be a bit shorter than the XMPP spec on DB2. It’‘s very unlikely that this would be an issue in production environments, as most JID’'s are under 50 characters.

Regards,

Matt

Matt

Yes, 1000 would work for the JID length, giving a total primary keylength of 1000+4=1004.

Should I simply change the JID to 1000 bytes wherever it is defined as a column and then just proceed?

-Bob

Should I simply change the JID to 1000 bytes wherever

it is defined as a column and then just proceed?

Yes. If you could let us know how that works, it would be appreciated. We could then change the schema in the distribution.

Regards,

Matt