Defining LONG Columns with a CREATE TABLE or ALTER TABLE Command

Following is the portion of the CREATE TABLE or ALTER TABLE command syntax for specifying a LONG column column definition. A maximum of 40 such LONG columns may be defined for a single table. The syntax is as follows:

When you create or add a LONG column to a table you have the option of specifying the DBEFileSet in which it is to be stored. Because LONG column data may take up a large chunk of a given DBEFile's data pages, placing LONG column data in a separate DBEFileSet is strongly advantageous from the standpoint of storage as well as performance.

If the IN DBEFileSetName clause is not specified for a LONG column, this column's data is by default stored in the same DBEFileSet as its related table.

	NOTE: It is recommended that you do not use the SYSTEM DBEFileSet in which to store your data, as this could severely impact database performance.

In the following example, LONG column data for PartPicture will be stored in PartPictureSet while data for columns PartName and PartNumber will be stored in PartsTableSet.

CREATE TABLE PartsTable (

             PartName CHAR(10),

             PartNumber INTEGER,

             PartPicture LONG VARBINARY(1000000) IN PartPictureSet)

          IN PartsTableSet

The next command specifies that data for new LONG column, PartModule, be stored in PartPictureSet.

ALTER TABLE PartsTable

        ADD PartModule LONG VARBINARY(70000) IN PartPictureSet

See the "BINARY Data" section of the "Host Variables" chapter for more information on using BINARY and VARBINARY data types in long columns.

Now that we have defined our table, let's see how to put data into it and to specify where data goes when it is retrieved.