 |
» |
|
|
|
SYSTEM.COLUMN contains detailed information
about the columns in the tables described in
SYSTEM.TABLE (refer to "SYSTEM.TABLE").
While SYSTEM.TABLE contains a row for every
table and view in the DBEnvironment, SYSTEM.COLUMN contains
a row for each column in each of those tables and
views. Initially, only the columns of the system views
are described. ALLBASE/SQL updates this table
when processing an ALTER TABLE, CREATE TABLE, CREATE
VIEW, DROP TABLE, DROP VIEW, TRANSFER OWNERSHIP,
or UPDATE STATISTICS statement. Note that the value for PRECISION is only used for
the decimal and float data types and SCALE
is only used for the decimal data type.
For decimal columns, PRECISION has to be a value between 1 and
15, and the value for SCALE must be between 0 and
the corresponding value for PRECISION.
For floating point columns, PRECISION has to be 53 when LENGTH is 8 and
24 when length is 4. Note that the DBEFILESET column will only contain a DBEFileSet name for LONG
columns, which can reside in a separate DBEFileSet than the table.
Table 8-7 System.Column | Column Name | Type | Length | Description |
|---|
| COLNAME | CHAR | 20 | Name of the column being described | | TABLENAME | CHAR | 20 | Name of the table or view
containing this column | | OWNER | CHAR | 20 | Owner of the
table or view | | COLNUM | INTEGER | 4 | Number of the column in the table
or view. Columns are numbered 1, 2, ... n,
and n is kept in the NUMC column of SYSTEM.TABLE | | LENGTH | INTEGER | 4 | - Either
Maximum length of the
column if TYPECODE is
3 (VARCHAR) - Or
Number of bytes in the
column for all other data
types
| | AVGLEN | INTEGER | 4 | Average column length; initially 0.
This value is needed by ALLBASE/SQL. | | TYPECODE | SMALLINT | 2 | Data type of the column:
- 0
INTEGER or SMALLINT (these two are distinguished by the LENGTH field) - 1
BINARY - 2
CHAR (ASCII only) - 3
VARCHAR (ASCII only) - 4
FLOAT or REAL (these two are distinguished by the LENGTH field) - 5
DECIMAL - 6
TID (for ALLBASE/SQL use only) - 7
reserved - 8
NATIVE CHAR - 9
NATIVE VARCHAR - 10
DATE - 11
TIME - 12
DATETIME - 13
INTERVAL - 14
VARBINARY - 15
LONG BINARY - 16
LONG VARBINARY - 19
CASE INSENSITIVE CHAR - 20
CASE INSENSITIVE VARCHAR - 21
CASE INSENSITIVE NATIVE CHAR - 22
CASE INSENSITIVE NATIVE VARCHAR
| | NULLS | SMALLINT | 2 | Null value indicator:
- 0
if the column cannot
contain null values - 1
if the column can contain
null values
| | PRECISION | SMALLINT | 2 | Number of significant
decimal or binary digits in the number
(excluding the sign and
the decimal point) | | SCALE | SMALLINT | 2 | Number of digits
after the decimal point | | LANGUAGEID | SMALLINT | 2 | Code for the language
of this column.
Run NLUTIL.PUB.SYS to display a complete list of native
languages and codes for your system.
A value of -1 means NOT APPLICABLE
(for numeric type columns or
columns in views) | | DEFAULTTYPE | SMALLINT | 2 | Default value type indicator:
- 0
no default clause specified - 1
DEFAULT NULL - 2
DEFAULT USER - 3
DEFAULT Constant - 4
DEFAULT CURRENT_DATE - 5
DEFAULT CURRENT_TIME - 6
DEFAULT CURRENT_DATETIME
| | DBEFILESET | CHAR | 20 | Name of the DBEFileSet holding
LONG column data |
Example | |
SELECT * FROM System.Column;
--------------------+--------------------+--------------------+-----------+
COLNAME |TABLENAME |OWNER |COLNUM |
--------------------+--------------------+--------------------+-----------+
PARTNUMBER |PARTS |PURCHDB | 1|
PARTNAME |PARTS |PURCHDB | 2|
SALESPRICE |PARTS |PURCHDB | 3|
PARTNUMBER |SUPPLYPRICE |PURCHDB | 1|
VENDORNUMBER |SUPPLYPRICE |PURCHDB | 2|
VENDORPARTNUMBER |SUPPLYPRICE |PURCHDB | 3|
UNITPRICE |SUPPLYPRICE |PURCHDB | 4|
DELIVERYDAYS |SUPPLYPRICE |PURCHDB | 5|
DISCOUNTQTY |SUPPLYPRICE |PURCHDB | 6|
VENDORNUMBER |VENDORSTATISTICS |PURCHDB | 1|
VENDORNAME |VENDORSTATISTICS |PURCHDB | 2|
ORDERDATE |VENDORSTATISTICS |PURCHDB | 3|
ORDERQUANTITY |VENDORSTATISTICS |PURCHDB | 4|
TOTALPRICE |VENDORSTATISTICS |PURCHDB | 5|
ORDERNUMBER |ORDERS |PURCHDB | 1|
VENDORNUMBER |ORDERS |PURCHDB | 2|
---------------------------------------------------------------------------
First 16 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+-----------+-----------+--------+------+---------+------+-----------------
|LENGTH |AVGLEN |TYPECODE|NULLS |PRECISION|SCALE |LANGUAGEID
+-----------+-----------+--------+------+---------+------------------------
| 16| 16| 2| 0| 0| 0| -1
| 30| 30| 2| 1| 0| 0| 0
| 8| 4| 4| 1| 0| 0| -1
| 16| 16| 2| 0| 0| 0| -1
| 4| 4| 0| 0| 0| 0| -1
| 16| 16| 2| 0| 0| 0| -1
| 8| 8| 4| 1| 0| 0| -1
| 2| 2| 0| 1| 0| 0| -1
| 2| 2| 2| 1| 0| 0| -1
| 4| 4| 0| 0| 0| 0| -1
| 30| 30| 2| 0| 0| 0| 0
| 8| 8| 2| 1| 0| 0| 0
| 2| 2| 0| 1| 0| 0| -1
| 8| 8| 4| 1| 0| 0| -1
| 4| 0| 0| 0| 0| 0| -1
| 4| 0| 0| 0| 0| 0| -1
---------------------------------------------------------------------------
First 16 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+-----------+---------------------
|DEFAULTTYPE|DBEFILESET
+-----------+---------------------
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
--------------------------------------------------------------------------
First 16 rows have been selected.
U[p], d[own], l[eft] , r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
|
|
