The Employee Photo Resume Table: DSN8810.EMP_PHOTO_RESUME

The Employee Photo & Resume Table: DSN8810.EMP_PHOTO_RESUME

DSN8810.EMP_PHOTO_RESUME contains photos and resume text for employees in the DSN8810.EMP table, previously described. The table contains a LOB column for the resume and two LOB columns for photos, one in PSEG format and one in BMP format.

The table is a parent table of DSN8810.PROJ with a foreign key on column RESPEMP . There are four indexes associated with the tables required to store photos and resumes: one on the base table and one each on the auxiliary tables. DSN8810.XEMP_PHOTO_RESUME is a primary unique index on the base table; and DSN8810.XAUX_BMP_PHOTO , DSN8810.XAUX_PSEG_PHOTO , DSN8810.XAUX_EMP_RESUME are each unique indexes on the respective auxiliary tables.

DSN8810.EMP_PHOTO_RESUME Table and Auxiliary Table DDL

 CREATE TABLE DSN8810.EMP_PHOTO_RESUME  (EMPNO       CHAR(06) NOT NULL,    EMP_ROWID   ROWID NOT NULL GENERATED ALWAYS,   PSEG_PHOTO  BLOB(100K),   BMP_PHOTO   BLOB(100K),   RESUME      CLOB(5K))   PRIMARY KEY EMPNO IN DSN8D81L.DSN8S81B CCSID EBCDIC; 

An auxiliary table is required for each LOB column in the table. The following DDL creates the auxiliary tables required for the three LOB columns in DSN8810.EMP_PHOTO_RESUME :

 CREATE AUX TABLE DSN8810.AUX_BMP_PHOTO    IN DSN8D61L.DSN8S61M    STORES DSN8810.EMP_PHOTO_RESUME    COLUMN BMP_PHOTO;  CREATE AUX TABLE DSN8810.AUX_PSEG_PHOTO    IN DSN8D61L.DSN8S61L    STORES DSN8810.EMP_PHOTO_RESUME    COLUMN PSEG_PHOTO;  CREATE AUX TABLE DSN8810.AUX_EMP_RESUME    IN DSN8D61L.DSN8S61N    STORES DSN8810.EMP_PHOTO_RESUME    COLUMN RESUME; 

Each auxiliary table must have a unique index defined on it. DSN8810.AUX_BMP_PHOTO has a required unique index named DSN8810.XAUX_BMP_PHOTO ; DSN8810.AUX_PSEG_PHOTO has a required unique index named DSN8810.XAUX_PSEG_PHOTO ; and DSN8810.AUX_EMP_RESUME has a required unique index named DSN8810.XAUX_EMP_RESUME .