System catalog entries for external tables

You can query system catalog tables to determine the status of external tables.

HCL Informix® 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 1. System catalog tables that describe external table files
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 IBM® Informix 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, Informix is unable to determine the number of rows in the external table because the data exists outside the database in data files. Informix 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.

Copyright© 2018 HCL Technologies Limited