Employee photo and resume table (DSN8D10.EMP_PHOTO_RESUME)

The sample employee photo and resume table complements the employee table.

Begin general-use programming interface information. Each row of the photo and resume table contains a photo of the employee, in two formats, and the employee's resume. The photo and resume table resides in table space DSN8D13L.DSN8S13B. The following statement creates the table:

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

Db2 requires an auxiliary table for each LOB column in a table. The following statements define the auxiliary tables for the three LOB columns in DSN8D10.EMP_PHOTO_RESUME:

CREATE AUX TABLE DSN8D10.AUX_BMP_PHOTO
               IN DSN8D13L.DSN8S13M
               STORES DSN8D10.EMP_PHOTO_RESUME
               COLUMN BMP_PHOTO;
 
CREATE AUX TABLE DSN8D10.AUX_PSEG_PHOTO
               IN DSN8D13L.DSN8S13L
               STORES DSN8D10.EMP_PHOTO_RESUME
               COLUMN PSEG_PHOTO;
 
CREATE AUX TABLE DSN8D10.AUX_EMP_RESUME
               IN DSN8D13L.DSN8S13N
               STORES DSN8D10.EMP_PHOTO_RESUME
               COLUMN RESUME;

End general-use programming interface information.

Content of the employee photo and resume table

The following table shows the content of the columns in the employee photo and resume table.

Table 1. Columns of the employee photo and resume table
Column	Column name	Description
1	EMPNO	Employee ID (the primary key).
2	EMP_ROWID	Row ID to uniquely identify each row of the table. Db2 supplies the values of this column.
3	PSEG_PHOTO	Employee photo, in PSEG format.
4	BMP_PHOTO	Employee photo, in BMP format.
5	RESUME	Employee resume.

The following table shows the indexes for the employee photo and resume table.

Table 2. Indexes of the employee photo and resume table
Name	On column	Type of index
DSN8D10.XEMP_PHOTO_RESUME	EMPNO	Primary, ascending

The following table shows the indexes for the auxiliary tables that support the employee photo and resume table.

Table 3. Indexes of the auxiliary tables for the employee photo and resume table
Name	On table	Type of index
DSN8D10.XAUX_BMP_PHOTO	DSN8D10.AUX_BMP_PHOTO	Unique
DSN8D10.XAUX_PSEG_PHOTO	DSN8D10.AUX_PSEG_PHOTO	Unique
DSN8D10.XAUX_EMP_RESUME	DSN8D10.AUX_EMP_RESUME	Unique

Relationship to other tables

The employee photo and resume table is a parent table of the project table, through a foreign key on column RESPEMP.