Locating Columns Stored in Optical Families

To determine which columns in a database are stored on an optical platter, query the sysblobs table. The sysblobs table contains four columns that provide the following information:

• The space name (blobspace, dbspace, or family name) where the column is stored
• The media type (M = magnetic; O = optical)
• The table identification number
• The column number within the table

The following SELECT statement shows the locations of all TEXT or BYTE data columns in a given database:

SELECT * FROM sysblobs

The output that the preceding SELECT statement produces for the stores_demo database indicates that column number 5 in the table with tabid 109 is stored in the optical family family1.

spacename        type          tabid   colno
rootdbs            M            109      4
family1            O            109      5

With this information, you can query the syscolumns table to obtain the name of the column, as follows:

SELECT colname FROM syscolumns WHERE tabid = 109 AND colno = 5

That particular query produces the following output:

colname
cat_picture

The oncheck utility with the -pD option also tells you whether a TEXT or BYTE data column is stored on an optical platter. However, the information is displayed on a row-by-row basis. The following oncheck command displays information for rows in the catalog table of the stores_demo database:

oncheck -pD stores_demo:catalog

For more information about oncheck, refer to the IBM Informix: Administrator's Reference.