Creating a table with LOB columns and auxiliary objects

This section shows you how to create a new table with Large Object (LOB) columns and its auxiliary tables with their respective indexes and tablespaces.

Step-by-step instructions

These instructions show you how to create a new table with LOB columns based on an existing table definition and generate the necessary JCL, SQL, and DDL. The steps include:

• Displaying a Similar Table Definition
• Adding Auxiliary Objects
• Editing the Auxiliary Objects Definition 

The instructions copy a table definition from Db2’s sample database DSN8910. You may substitute another database and use your own method of object display. If you do, the data in your windows will not match those in the figures.

Displaying a similar table definition

In this section, you display a table from a database and execute the CR row command to start the table creation process.

1. Access File-AID for Db2 Object Administration. The Main Menu must be displayed.

2. Execute the fast-path command TB.CRNA.

   This command selects the Table Display Facility from the Main Menu and CRTR_NAME from the Table Display Facility. It opens a SYSTABLES WHERE clause window to select tables by creator and table name (see the following figure).

   WHERE Clause to Select Tables by Creator and Table Name. This window is maximized.

   
   

3. Edit the WHERE clause to select tables with creator IDs beginning with DSN8 and table names beginning with EMP_PHOTO.
   The statement should look like the following:

   TB.CREATOR LIKE ‘DSN8%’ AND TB.NAME LIKE ’EMP_PHOTO%’ AND
   TB.TYPE IN ( ’T’, ‘G’, ‘X’, 'M' )

4. Press <Go> to execute the WHERE clause.

   A table display pops up listing tables that have CREATOR NAME beginning with DSN8 and TB.NAME beginning with EMP_PHOTO (see the following figure). Your table display should show the IBM sample database named DSN8D91A. As table EMP_PHOTO_RESUME contains LOB columns, use it as the template for this exercise.

   Display of Tables Selected by Name. This window is maximized.

   

5. Execute the CR (create) row command next to EMP_PHOTO_RESUME.
   Two windows open with attributes acquired from the existing table (see the following figure):

• 
  
  • Edit Table Information (outer window): Specifies table attributes.
  • Edit Columns (inner window): Specifies attributes for the table’s columns.

Collectively, these windows are called the table editing windows. You can use any edit row or primary command to change attributes to create a new table definition. A few fields are protected from change, as indicated by a different color on your screen.

Inner windows cannot be closed individually. Only the outer window can be closed.

Table Editing Windows

Editing the table definition

This section shows you how easy it is to define a new table by editing an existing definition from a similar object and renaming it. Refer to Editing the Table Definition for a more detailed procedure, the example here only shows minimal steps.

The table editing windows must be displayed (Table Editing Windows) before you continue.

1. Edit the Edit Table Information window:

   1. Change the table name by typing EMP_PHOTO_RES_TEST over EMP_PHOTO_RESUME.
   2. Change the table creator to your user ID, in our example DBDBA.
   3. Change the database name to a valid name, in our example PFHKFLDB.
   4. Change the tablespace name to a valid name, in our example PHHKFLTS.

   Use Smart Link for valid database and tablespace names if you are unsure which objects to name.

2. Edit the Edit Columns Information window:
   
   1. Change the column name BMP_PHOTO to PCX_PHOTO.
   2. Change the column length for PCX_PHOTO to 200K.
   3. Change the column length for RESUME to 10K.

3. Review your table editing windows; they should look like the following figure.

   Table Editing Windows With Changes

   

4. Press <Go> to generate the SQL.
   File-AID for Db2 Object Administration opens the SQL work buffer (see the following figure). The buffer contains the SQL to create the table. All statements are based on the information in the table editing windows.

   Normally, you would create an index for the table now, following the instructions starting with Displaying and Editing the Index Definition. For this example, we continue with executing the CREATE TABLE SQL now.

   SQL to Create a Table. Automatically generated SQL is appended to the SQL work buffer.

   

5. Press <Go> to execute the CREATE TABLE SQL. After execution a window displays with a status message similar to the one shown in the following figure.

6. Clear SQL buffer.

   DDL Execution Message window

   
   

Adding auxiliary objects

Now that the table with LOB columns has been created you still need to create the auxiliary objects that correspond to the LOB columns.

Access the newly created table:

1. Execute the fast-path command MENU.TB.CRNA.

   This command selects the Table Display Facility from the Main Menu and CRTR_NAME from the Table Display Facility. It opens a SYSTABLES WHERE clause window to select tables by creator and table name (see the following figure).

   WHERE Clause to Select Newly Created Table by Creator. This window is maximized.

   

2. Edit the WHERE clause to select tables with creator IDs beginning with the creator ID you just used to create the table (DBXSAMP) and table names beginning with EMP_PHOT.

   The statement should look like the following:

   TB.CREATOR LIKE ‘DBDBA%’ AND TB.NAME LIKE ’EMP_PHOT%’ AND
   TB.TYPE IN ( ’T’, ‘G’, ‘X’, ‘M’ )

3. Press <Go> to execute the WHERE clause.

   A table display pops up listing tables that have CREATOR NAME beginning with DBDBA and TB.NAME beginning with EMP_PHOT (see the following figure). Your table display should show the newly created table.

   Display of Tables Selected by Creator ID and Name. This window is maximized.

   

4. Execute the CRXO (create auxiliary objects) row command next to EMP_PHOTO_RES_TEST.
   Two windows open with attributes acquired from the existing table (see the following figure):

   • Edit Global Change (outer window): Specifies auxiliary index and tablespace attributes.
   • Edit Detail LOB Objects (inner window): Specifies attributes for the table’s LOB objects.

   Collectively, these windows are called the auxiliary objects editing windows. You can use any edit row or primary command to change attributes to create new auxiliary objects for a table. A few fields are protected from change, as indicated by a different color on your screen.

   Inner windows cannot be closed individually. Only the outer window can be closed.

   Auxiliary Objects Editing Windows

   

Editing the auxiliary objects definition

This section shows you how easy it is to define the auxiliary objects for each of the LOB columns. Based on the table and column definitions, File-AID for Db2 Object Administration presents default values for the auxiliary indexes and tablespaces to be created. You may accept these defaults or overwrite them with your own values.

For this example, accept the defaults:

1. Press <Go> to generate the SQL.
   File-AID for Db2 Object Administration opens the SQL work buffer (see the following figure). The buffer contains the SQL to create the table. All statements are based on the information in the table editing windows.
   SQL to Create Auxiliary Objects. Automatically generated SQL is appended to the SQL work buffer.
   

2. Enter the GO command to execute the CREATE LOB TABLESPACE SQL. After execution a window displays with a status message similar to the one shown in the following figure.
   DDL Execution Message for Auxiliary Objects

   

Once you have created the new table with LOB columns and all its auxiliary objects, you can add data using various loading techniques.