Cloudscape System Tables
Page 5 of 17

SYSCONGLOMERATES

Describes the conglomerates within the current database. A conglomerate is a unit of storage and is either a table or an index.

Column Name

Type

Length

Nullable

Contents

SCHEMAID

CHAR

36

false

schema id for the conglomerate

TABLEID

CHAR

36

false

identifier for table (join with SYSTABLES.TABLEID)

CONGLOMERATENUMBER

LONGINT

8

false

conglomerate id for the conglomerate (heap or index)

CONGLOMERATENAME

VARCHAR

128

true

index name, if conglomerate is an index, otherwise the table ID

ISINDEX

BOOLEAN

1

false

whether or not conglomerate is an index

DESCRIPTOR

SERIALIZE
(COM.cloudscape.
types.
IndexDescriptor
)


true

system type describing the index

ISCONSTRAINT

BOOLEAN

1

false

whether or not conglomerate is a -system-generated index enforcing a constraint

CONGLOMERATEID

CHAR

36

false

unique identifier for the conglomerate

Indexes

  • SYSCONGLOMERATES_INDEX1 unique BTREE index on (CONGLOMERATEID)
  • SYSCONGLOMERATES_INDEX2 unique BTREE index on (CONGLOMERATENAME, SCHEMAID)
  • SYSCONGLOMERATES_INDEX3 BTREE index on (TABLEID)

Example Queries

The following query shows the key columns, their ordinal positions, and whether they are ascending or descending for an index named new_index in the table Flights, in the schema APP, in order:

SELECT CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER)
    AS ORDINAL_POSITION,
COLS.COLUMNNAME AS COLUMN_NAME,
(CONGLOMS.DESCRIPTOR.isAscending(
    CONGLOMS.DESCRIPTOR.getKeyColumnPosition(
    COLS.COLUMNNUMBER)) ? 'A' : 'D') AS ASC_OR_DESC
FROM SYS.SYSTABLES AS T,
SYS.SYSCONGLOMERATES AS CONGLOMS,
SYS.SYSCOLUMNS AS COLS,
SYS.SYSSCHEMAS AS S
WHERE T.TABLEID = CONGLOMS.TABLEID
AND T.TABLEID = COLS.REFERENCEID
AND CONGLOMS.CONGLOMERATENAME = 'NEW_INDEX'
AND CONGLOMS.ISINDEX
AND S.SCHEMAID = T.SCHEMAID
AND S.SCHEMANAME = 'APP'
AND (CONGLOMS.DESCRIPTOR IS NOT NULL ?
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER)
:    0) <> 0
    ORDER BY ORDINAL_POSITION