Use Static Tables

Turn tables with a small number of records that are often queried into Client static tables. Turn tables that rarely change into RDBMS static tables.

Background

RDBMS Static Tables

There are many tables that rarely change. These tables may be parents of tables that do change regularly, resulting in many lock attempts. These tables that rarely change can be specified to be RDBMS static tables, which helps to avoid potential locking conflicts.

Client Static Tables

For each session for which Client static tables have been specified, the data contents of the tables are stored in their entirety in the memory of the client computer. When the user first queries a static table, all table data are fetched and stored on the client computer until the user disconnects. If the Use Static Tables application property is set to Yes (the default), then in later fetches data is queried from local memory and not from the database.

At commit time, records of static tables are not locked for foreign key checks or constraint tests.

When to use

You can only safely turn a table into a Client static table if you know the data of the static table will not be updated while on-line processing is going on. Typical candidates are code tables such as the DISCOUNT table in the Travel Agency application. This feature leads to optimization if the data volume is relatively small and the table is likely to be queried often. When turning a table into a static table, always perform a real-time test with BenchMark that measures the query time before adopting it.

More specifically, a SELECT statement can query a static table locally if data from only this table is selected, and the statement does not contain subqueries, GROUP BY, HAVING, group functions or database-specific functions, or clauses starting with CONNECT BY, START WITH, or DISTINCT.

For a component table that has been turned into a Client Static table, the QUERY and FETCH methods are not called the second time the table is queried. This improves performance for component tables with not too many records.

You should turn a table into an RDBMS static table if the table does not change during normal use of the application. Choosing this option helps to avoid locking conflicts.

If a parent table is an RDBMS static table, the Lock Foreign Key on Check setting for a relationship has no effect. If a supertype table is an RDBMS static table, its subtype tables will also NOT be locked.

Guidelines for specifying static tables:

•If the table is changed during normal use of the application, define it as Not static.

•If a table does not change during normal use of the application define it as an RDBMS static table.

•Use Client static tables only if:

oThe table is small (< 100 rows, < 10 columns).

oThe table is likely to be queried often.

oYour network is slow.

oYou have measured a performance increase using client static tables.

To turn a table into a static table: