|
|
|
The following features are available to the DBA and other users to assist in manipulating table data and referencing tables:
|
|
|
The DBA or any user is allowed to create synonyms for tables, views, and indexes. These alternate names, or aliases, can be used to define shorthand names in place of long, fully qualified names.
After a synonym is created, it can be referenced the same way as the object for which it was created. For example, if you create a synonym for a table or view, issue select statements using the synonym name, just as you use the table or view name.
|
|
|
You can perform the following basic operations on synonyms:
In VDBA, use the Synonyms branch for a particular database in the Database Object Manager window.
You can also accomplish these tasks using the SQL statements create synonym and drop synonym. For more information, see the SQL Reference Guide.
|
|
|
Temporary tables are useful in applications that need to manipulate intermediate results and minimize the processing overhead associated with creating tables.
Temporary tables reduce overhead in the following ways:
Because no logging is performed, temporary tables can be created, deleted, and modified during an online checkpoint.
Temporary tables are:
The declare global temporary table statement is used to create temporary (session-scope) tables. In VDBA, use the Create Table dialog.
All temporary tables are automatically deleted at the end of the session. To delete a temporary table before the session ends, issue a drop table statement.
|
|
|
The DBMS Server supports two syntaxes for declaring and referencing global temporary tables:
If the DECLARE GLOBAL TEMPORARY TABLE statement defines the table with the SESSION schema qualifier, then subsequent SQL statements that reference the table must use the SESSION qualifier.
When using this syntax, the creation of permanent and temporary tables with the same name is allowed.
If the DECLARE GLOBAL TEMPORARY TABLE statement defines the table without the SESSION schema qualifier, then subsequent SQL statements that reference the table can optionally omit the SESSION qualifier. This feature is useful when writing portable SQL.
When using this syntax, the creation of permanent and temporary tables with the same name is not allowed.
Note: In both modes, a session table is local to the session, which means that two sessions can declare a global temporary table of the same name and they do not conflict with each other.
Note: Syntaxes cannot be mixed in a single session. For example, if the table is declared with SESSION the first time, all declarations must use SESSION.
|
|
|
To create two temporary tables, names and employees, for the current session, issue the following statements:
declare global temporary table session.names
(name varchar(20), empno varchar(5))
on commit preserve rows
with norecovery
declare global temporary table session.employees as
select name, empno from employees
on commit preserve rows
with norecovery
Note: The "session." qualifier in the example is optional. If omitted, the name of the temporary table cannot be the same as any permanent table names.
The names of temporary tables must be unique only in a session.
For more information on working with temporary tables, see the descriptions for declare global temporary table and drop statements in the SQL Reference Guide.
|
|
|
When using VDBA, tables and views are self-documenting. For example, you can see the definition of a view at a glance, as well as its rows and the grants that have been defined. For a table, you can view its rows and columns, as well as properties, statistics, and other pertinent information.
When working with tables and views in applications, however, it is helpful to include commentary about the structure and uses of tables and views.
Tables and views can be commented with:
|
|
|
The comment on statement allows you to add commentary to SQL programs. Using this statement, you can create a comment for the table as a whole and for individual columns in the table.
For example, to add a remark on the name column and on the status column of the employee table:
comment on column employee.name is
'name in the format: lastname, firstname';
comment on column employee.status is
'valid codes are:
01, exempt; 02, non-exempt; 03, temp';
To delete a comment, specify an empty string. For example, the comment on the status column can be deleted by the statement:
comment on column employee.status is '';
For complete details, see the comment on statement in the SQL Reference Guide.
|
|
|
The declare table statement can be used to describe the structure of a table in ESQL programs. With this statement, you can document the columns and data types associated with a table.
For example, the employee table can be described with a declare table statement as follows:
exec sql declare userjoe.employee table
(
emp_number integer2 not null not default,
last_name varchar(20) not null,
first_name varchar(20),
birth_date date not null not default
);
For complete details, see the entry for the declare table statement in the SQL Reference Guide. For information on ESQL programs, see the chapter "Embedded SQL" in the SQL Reference Guide.