If you want to evaluate system tables, note the following information:
· When system tables are evaluated, the system only outputs information for objects of which the current user is the owner, or for which the user has at least some privilege (the user therefore knows the object). This authorization concept may mean that the table definition visible for the current user differs from the actual definition.
The definition of a view table is only visible for the owner of the view table.
· When you query the system table(s), you should enter conditions that describe the required object as precisely as possible. Entering the object owner considerably speeds up the search for the relevant information.
· When you specify search commands, you should specify equivalence conditions where possible. Specifying LIKE conditions is less effective.
· For performance reasons, when you query information from system tables, you should use not only the SQL statement SELECT * but also limit the number of output columns to those columns that you actually require.
When you query statistical information from system tables, in particular, additional actions are performed to determine column values in the database system when certain output columns are requested. As a result, you should only have the system determine this column information if you really need it.
· Simple identifiers are always created in the database instance in upper-case letters, irrespective of how they were entered when the data was defined.
If you use simple identifiers in a search condition, you must enter the single quotes that are typical for specifying literals.
For performance reasons, the simple identifier should not be converted to upper-case letters by the database system when the SQL statement is executed; you should enter the simple identifier directly in upper-case letters in the search condition.
CREATE TABLE mytab (…)
SELECT … FROM … WHERE … = 'MYTAB'
· Special identifiers are always specified in double quotes in the data definition. In the database instance, these are stored as they were entered, that is, they are not converted to upper-case letters.
If you use special identifiers in a search condition, you must enter the single quotes that are typical for specifying literals.
CREATE TABLE "this is mytab" (…)
SELECT … FROM … WHERE … = 'this is mytab'