System catalog entries for external tables
You can query system catalog tables to determine the status of external tables.
HCL OneDB™ updates the sysexternal and sysextdfiles system catalog tables each time an external table is created. The sysextcols system catalog table is updated when the external format type (fmttype) FIXED is specified.
| Table name | Description |
|---|---|
| sysexternal | Stores name and attributes of each external table file |
| sysextdfiles | Stores file-path and directory of each external table file |
| sysextcols | Stores attributes of each column in FIXED-type external tables |
See the HCL OneDB Guide to SQL: Reference for more information.
A row is inserted into the systables system catalog when an external table is created; however, the nrows (number of rows) and the npused (number of data pages used) columns might not accurately reflect the number of rows and the number of data pages used by the external table unless the NUMROWS clause was specified when the external table was created.
When an external table is created without specifying a value for the NUMROWS clause, HCL OneDB is unable to determine the number of rows in the external table because the data exists outside the database in data files. HCL OneDB updates the nrows column in the systables system catalog by inserting a large value (MAXINT -1), and computes the number of data pages used based on the nrows value. The values stored in npused and nrows are later used by the optimizer to determine the most efficient execution plan. While the NUMROWS clause is not required to be specified precisely, the more accurately it is specified, the more accurate the values for nrows and npused are.