Functional Indexes for DB2/UDB for Linux, UNIX, and Windows
The following example shows how the key fields are concatenated:
CREATE TABLE PS_QE_ALTER_NEW (QE_ALTER_FLD1 VARGRAPHIC(1) NOT NULL,
QE_ALTER_FLD2 VARGRAPHIC(1) NOT NULL,
QE_ALTER_FLD3 VARGRAPHIC(1) NOT NULL,
QE_ALTER_FLD4 VARGRAPHIC(10) NOT NULL,
QE_ALTER_FLD5 VARGRAPHIC(20) NOT NULL,
QE_ALTER_FLD6 VARGRAPHIC(11) NOT NULL,
QE_ALTER_FLD7 VARGRAPHIC(1) NOT NULL,
QE_ALTER_FLD8 VARGRAPHIC(1) NOT NULL,
QEPC_AGE VARGRAPHIC(8) NOT NULL,
QEPC_FILE_NUM DECIMAL(6, 2) NOT NULL,
QE_BEGIN_DT DATE,
QE_BIRTHDATE DATE,
QE_31DIGFLD9 DECIMAL(31, 8) NOT NULL,
QE_ANNUAL_PCT DECIMAL(3, 1) NOT NULL,
QE_MS_DTTM1 TIMESTAMP,
QE_MS_DTTM2 TIMESTAMP,
QE_ALTER_TIMEA TIME,
QE_ALTER_TIMEB TIME,
DBXCONCATCOL GENERATED ALWAYS AS (QE_ALTER_FLD1 || QE_ALTER_FLD2 ||
QE_ALTER_FLD3 || QE_ALTER_FLD4 || QE_ALTER_FLD5 || QE_ALTER_FLD6 ||
QE_ALTER_FLD7 || QE_ALTER_FLD8 || QEPC_AGE ||
VARGRAPHIC(CHAR(QEPC_FILE_NUM)) ||
VALUE(VARGRAPHIC(CHAR(QE_BEGIN_DT)),'') ||
VALUE(VARGRAPHIC(CHAR(QE_BIRTHDATE)),'') ||
VARGRAPHIC(CHAR(QE_31DIGFLD9)) || VARGRAPHIC(CHAR(QE_ANNUAL_PCT)) ||
VALUE(VARGRAPHIC(CHAR(QE_MS_DTTM1)),'') ||
VALUE(VARGRAPHIC(CHAR(QE_MS_DTTM2)),'') ||
VALUE(VARGRAPHIC(CHAR(QE_ALTER_TIMEA)),''))) IN PTAPP INDEX IN
PTAPPIDX NOT LOGGED INITIALLY;
COMMIT;
CREATE UNIQUE INDEX PS_QE_ALTER_NEW ON PS_QE_ALTER_NEW (DBXCONCATCOL);
CREATE INDEX PSWQE_ALTER_NEW ON PS_QE_ALTER_NEW (QE_ALTER_FLD1,
QE_ALTER_FLD2,
QE_ALTER_FLD3,
QE_ALTER_FLD4,
QE_ALTER_FLD5,
QE_ALTER_FLD6,
QE_ALTER_FLD7,
QE_ALTER_FLD8,
QEPC_AGE,
QEPC_FILE_NUM,
QE_BEGIN_DT,
QE_BIRTHDATE,
QE_31DIGFLD9,
QE_ANNUAL_PCT,
QE_MS_DTTM1,
QE_MS_DTTM2);
COMMIT;