Employee photo and resume table (DSN8A10.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 .DSN8S10B. The following statement creates the table:

CREATE TABLE  DSN8A10.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  DSN8D10L.DSN8S10B
        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 DSN8A10.EMP_PHOTO_RESUME:

CREATE AUX TABLE DSN8A10.AUX_BMP_PHOTO
               IN DSN8D10L.DSN8S10M
               STORES DSN8A10.EMP_PHOTO_RESUME
               COLUMN BMP_PHOTO;
 
CREATE AUX TABLE DSN8A10.AUX_PSEG_PHOTO
               IN DSN8D10L.DSN8S10L
               STORES DSN8A10.EMP_PHOTO_RESUME
               COLUMN PSEG_PHOTO;
 
CREATE AUX TABLE DSN8A10.AUX_EMP_RESUME
               IN DSN8D10L.DSN8S10N
               STORES DSN8A10.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
DSN8A10.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
DSN8A10.XAUX_BMP_PHOTO DSN8A10.AUX_BMP_PHOTO Unique
DSN8A10.XAUX_PSEG_PHOTO DSN8A10.AUX_PSEG_PHOTO Unique
DSN8A10.XAUX_EMP_RESUME DSN8A10.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.