D3 SQL catalog

The D3 SQL Catalog is the Tables file located in the SQL account.

Using the D3 SQL Catalog

The table below lists and describes the attribute-defining items for the Tables file. Direct modification of the Catalog is not supported. The structure of the Tables file is proprietary and its exact structure cannot be guaranteed on future releases.

To display the complete table descriptor for an SQL table, log to the SQL account and execute this AQL statement:

SELECT TABLES WITH table_name AND WITH account_name

A select list is created, which can be viewed using this statement:

list tables

SQL-DISPLAY-TABLE provides a partial listing of the table descriptor for a SQL table.

The table below lists and describes the attribute-defining items for the Tables file. Direct modification of the Catalog is not supported. The structure of the Tables file is proprietary and its exact structure cannot be guaranteed on future releases.

Attr # Attribute Name Description
0 table_id Unique number for referencing the table. The item-id of the table in the Tables file.
1 table_name SQL table name, which is often, but not always, the same as the D3 file name.
2 account Account where the D3 file exists.
3 pick_file Name of the D3 file corresponding to the table. For example, orders or orders,department1.
4 table_type Integer indicating the table type. This integer value translates to the Base_types file. Translated values are: ALIAS (not used), DELETED (not used), TABLE, SYSTEM_TABLE, SYNONYM, VIEW (not implemented).
5 macro List of columns referenced by the *, as in SELECT * FROM table_name.
6 column_name List of column names.
7 retrieve_type Integer indicating the retrieval type of the column. The values can be translated using the retrieve_types file to the tags below:
  • IID (0) – a column mapped to the D3 item ID,

  • CONTROL (1) – a standard column which is used as the controlling element in a nested set. This corresponds to the controlling element in a D3 controlling-dependent set,

  • DIRECT (2) – a standard column,

  • SYNONYM (3) – a reference to another column.

8 ordinal_position Attribute number of each column in standard D3 files.
9 reserved Reserved.
10 column_default Default value for the column.
11 is_nullable
  • 1 = null strings display as NULL.

  • 0 = null strings display as empty strings.

12 data_type Integer translating to the data_types file, which indicated the SQL data type of the column:


13 character_maximum_

length or

Precision of the numeric column or the length of the string column.
14 numeric_scale Scale of a numeric column.
15 pick_scale Multiplier used to store decimal numbers as integers in the actual database file. This is used for the mrXX conversions when they are detected by the SQL-CREATE-TABLE utility.
16 reserved Reserved.
17 correlative D3 correlative code. This is a translation code that is automatically applied at retrieval time only. The correlative is useful for parsing non-relational data into a format that can be utilized by SQL. Translates and/or BASIC calls may be performed using these functions. Note, however, that the ACCESS() functions in BASIC are not functional when those programs are called from SQL correlatives.
18 reserved Reserved.
19 column_remarks User information about the column.
20 reserved Reserved.
  constraint_type Integer translating to the constraint_types file which indicates the type of constraint:


  constraint_column_name Columns which apply to the current constraint.
  referenced_table NESTED and FOREIGN (key) constraints.
  referenced_columns NESTED and FOREIGN (key) constraints. A comma-separated list of referenced columns.
  reserved Reserved.
  reserved Reserved.
  reserved Reserved.
  Dict_id Dictionary item names.
  table_remarks User remarks about the file.
  status_byte Reserved for system tables.
  Column_deleted List of column numbers of columns that were deleted.
  index_name Name identifying an index. Index names are either program generated or assigned by the user through the SQL Create Index statement.
33 index_type Integer indicating the index type. This integer translates to the Index_types file. Translated values are HASHED (for indexes placed on primary key columns) and OTHER (for all other columns).
34 non_unique 1 = index is not unique. The index can have duplicates.

0 = index is unique. The index values must be unique. Only indexes that are program generated to support primary and unique keys are unique.

35 indexed_columns List of the indexed columns delimited by sub-value marks.
36 seq_in_index Column sequence numbers in the index. Indexed columns are numbered starting at one.
37 constraint_name Name identifying a constraint. All constraint names are program generated. Each constraint has an index associated with it that is identified by the same name except for not null constraints (an index is not created for the not null constraint). Refer to attribute 32 (Index_name) for information on the format of this attribute.
38 index_acorr A-correlative used to create the index.
39 full path Path to the D3 file in the form of account, file, datalevel. If the Q-pointer is used in SQL-CREATE-TABLE, the location of the D3 file is determined.