SYMPTOM:
In Strategy 9.x, the Primary Index for metadata on Teradata databases is not optimized.
The metadata creation script, mdtera.sql is defined as follows:
Sample Code/Error
CREATE TABLE DSSMDOBJINFO
(
PROJECT_ID CHAR(32) NOT NULL,
OBJECT_ID CHAR(32) NOT NULL,
OBJECT_TYPE INTEGER NOT NULL,
SUBTYPE INTEGER NOT NULL,
OBJECT_NAME VARCHAR(250) CHARACTER SET UNICODE NOT NULL,
ABBREVIATION VARCHAR(250) CHARACTER SET UNICODE,
DESCRIPTION VARCHAR(250) CHARACTER SET UNICODE,
VERSION_ID CHAR(32) NOT NULL,
PARENT_ID CHAR(32) NOT NULL,
OWNER_ID CHAR(32) NOT NULL,
HIDDEN INTEGER NOT NULL,
CREATE_TIME TIMESTAMP(3),
MOD_TIME TIMESTAMP(3),
OBJECT_UNAME VARCHAR(250) CHARACTER SET UNICODE NOT NULL,
OBJECT_STATE INTEGER NOT NULL,
LOCALE INTEGER DEFAULT 0 NOT NULL,
EXTENDED_TYPE INTEGER DEFAULT 0 NOT NULL,
VIEW_MEDIA INTEGER DEFAULT 0 NOT NULL,
ICON_PATH VARCHAR(250) DEFAULT '' CHARACTER SET UNICODE
);
ALTER TABLE DSSMDOBJINFO ADD CONSTRAINT PK_OBJINFO PRIMARY KEY (OBJECT_ID, PROJECT_ID);
The DSSMDOBJINFO table is created with PRIMARY INDEX (PROJECT_ID) since UNIQUE PRIMARY INDEX is not specified explicitly. For this reason, the data is distributed to the AMPs only for the number of projects in the metadata database.
CAUSE:
This is a known issue with Strategy 9.x.
ACTION:
This modification has been impleneted in MicroStategy version 10.0 please upgrade to 10.0 or later to have this included.
