REGISTER TABLE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The REGISTER TABLE statement maps the structure of a file to the structure of a table.
It maps the fields in a file to columns in a virtual table. After registering the file as a table, use SQL to manipulate the contents of the file. The registered table can be referred to in database procedures. To delete a registration, use the REMOVE TABLE statement.
The following statements can be performed against registered tables:
• CREATE VIEW
• CREATE SYNONYM
• COMMENT
• SELECT
• INSERT, UPDATE, AND DELETE (if they are from an updatable Enterprise Access product)
• DROP
• SAVE
The following statements cannot be performed against registered tables:
• MODIFY
• CREATE INDEX
Syntax
The REGISTER TABLE statement has the following format:
[EXEC SQL] REGISTER TABLE [schema.]table_name
(column_name column_type [IS 'external_name']
{, column_name column_type [IS 'external_name']})
AS IMPORT FROM 'security_log_file_name' | 'CURRENT'
WITH DBMS=SXA [, ROWS = integer_value];
table_name
Assigns a name to the table.
column_name column_type [IS 'external_name']
Specifies the name and data type of each column of the virtual table.
The IS 'external_name' clause maps the columns in the virtual table to the fields in the file (external_name). For example, the following statement maps the table column, db_name, to the security log field, database:
db_name CHAR(32) IS 'database'
If the IS clause is omitted, the column names must correspond to the field names listed in the file. At least one column must be specified. Columns can be specified in any order.
AS IMPORT FROM
Specifies the file whose contents are to be imported. Valid values are:
'CURRENT'
Dynamically registers the current log file that is in use. If 'CURRENT' is specified, SQL operations on the virtual log table always see the log file in use, even if the physical log file changes.
'security_log_file_name'
Specifies the name of the security log file. The name must be specified as a quoted string, and must be a valid operating system file specification.
WITH
Specifies additional information about the table being registered.
DBMS=
Specifies the origin of the table being registered.
To register a security log, specify SXA.
By default, the security log shows security events for the entire installation. If the database field is omitted, the security log contains records only for the database in which the log is registered.
ROWS=integer_value
Specifies the number of records the log is expected to contain; the default is 1000. This value is displayed by the HELP TABLE statement as Rows: and is used by the DBMS query optimizer to produce query plans for queries that see the registered table.
Security Log File Format
The security log is created and written when security logging is enabled (using the ENABLE SECURITY_AUDIT statement). The security log file has the following format:
Note: When registered, a security log is read-only.
Permissions
The session must have MAINTAIN_AUDIT privilege.
To query the audit log, AUDITOR privilege is required.
Related Statements
Remove Table
REGISTER TABLE Example
Register a security audit log with various attributes:
REGISTER TABLE aud1 (
audittime DATE NOT NULL,
user_name CHAR(32) NOT NULL,
real_name CHAR(32) NOT NULL,
userprivileges CHAR(32) NOT NULL,
objprivileges CHAR(32) NOT NULL,
database CHAR(32) NOT NULL,
auditstatus CHAR(1) NOT NULL,
auditevent CHAR(24) NOT NULL,
objecttype CHAR(24) NOT NULL,
objectname CHAR(32) NOT NULL,
objectowner CHAR(32) NOT NULL,
description CHAR(80) NOT NULL,
detailinfo VARCHAR(256) NOT NULL,
detailnum INTEGER4 NOT NULL,
sessionid CHAR(16) NOT NULL,
querytext_sequence INTEGER4 NOT NULL
) AS IMPORT FROM 'myfile'
WITH DBMS=SXA,
ROWS=2000;