How do I create a Schema Cache file?

How to create a Schema Cache file?
What is a Schema Cache file?
Why use a Schema Cache file?

What problem does a Schema Cache File solve?

When Progress starts a client application, it loads the Schema for each database that the application requires into memory. Ordinarily, Progress reads the Schema from the database, which can be on a remote server. If the network is slow, or the server itself is overloaded, this can increase startup time significantly.

Client startup time can be shortened by building and saving a Schema Cache file on a disk local to the client. A Schema Cache file is a machine-portable binary file containing a complete database schema or sub-schema for a single Progress database. When a client application is started with local Schema Cache files, Progress reads the required Schema for each database almost instantaneously from the local disk rather than having to wait for network or server traffic to provide the necessary client schema.

Building a Schema Cache File

To build a Schema Cache file, the SAVE CACHE statement is used together with a connected database.

First decide if the entire database Schema or only the Application Schema for selected tables of a database are needed. If the application accesses all the tables in the database, the complete cache will need to be built. Otherwise, a schema cache file can be built for only the tables that are accessed by the Application.

In general, a Schema Cache file is built off-line. This can be achieved in the Procedure Editor directly, or a small maintenance procedure can be written to generate the Schema Cache file.

The Schema Cache file must be rebuilt and updated on clients after:

• Making Schema changes to the database, for example Data Dictionary, Loading .df, Changing SQL widths with DBTOOL
• Changing Schema permissions 
• A dump and load operation which involves a .df file for the entire or specific database objects

Code elements require the client to cache the database schema include:

• Dynamic buffers
• SAVE CACHE statements (which obviously should not use the -cache startup parameter)
• COMPILE statements
• RUN statements that run uncompiled source code
• Which are further discussed in the following Article, Changes that require building a new Schema Cache file   

Syntax of the SAVE CACHE statement:
 

SAVE CACHE  
    { CURRENT | COMPLETE } 
    { database-name | VALUE ( char-expr ) } 
  TO 
    { pathname |  VALUE ( char-expr )} 
[ NO-ERROR ]

 

• The database-name can be the literal logical name of any Progress database or the VALUE(expression) option, where expression is a character expression that evaluates to the database name. For a DataServer, Progress saves the Schema Cache for the entire Schema Holder Database. The schema cache for a non-Progress database cannot be saved for specific tables. For further information on Schema Cache files for DataServers, refer to the Progress DataServer Documentation.
• The pathname can be the literal pathname of an Operating System file or the VALUE(expression) option, where expression is a character expression that evaluates to the pathname.
• It's not recommended to use the -trig parameter when saving the cache file:  The -trig parameter fails in combination with -cache at runtime   
• If the database contains mandatory fields excess shared memory may need increasing: Save Cache option causes 6495 error   

Example: Creating a complete Schema Cache file

1. To save the entire schema cache, connect to the database without the -trig parameter, pre 11.7.6
2. Execute the SAVE CACHE statement using the COMPLETE option.
3. Copy the resulting .csh file locally to Application clients
4. Add -cache <filename> to the Progress Client startup parameters.

/* create the current database schema cache then exit */
SAVE CACHE COMPLETE sports2000 to sports2000.csh.

For an example procedure that saves the entire schema cache for one or more databases, refer to the code example in Article:

• How to keep the schema cache on the client up to date with the db?   

Using a schema cache file for compiling application code  

When compiling application code against a remote database, using a full schema cache file will provide a small performance improvement than without it. 

1. Once schema changes have been applied to the (remote) deployment database
2. Dump the full .df from the remote database (prodict/dump_df.p)
3. Build a local database (prostrct create dbname)
4. Load this .df (prodict/load_df.p)
5. Compile against the local database
6. Delete the local database (prodel dbname)

An enhancement request has been submitted as an Idea on the Progress Community to improve the use of a schema cache file for the ABL compiler. Weigh-in with your suggestions and promote the Idea to assist with prioritisation during review by selecting this link:

• https://openedge.ideas.aha.io/ideas/OPENEDGE-I-642   

Idea submissions are monitored by our Product Management team. Enhancement requests are reviewed during the planning phase of each new product release and a list of the enhancements chosen for implementation can be found in the Release Notes documents that accompany each release.
 

References to Other Documentation:

OpenEdge Programming Interfaces, Database Access - Creating schema cache files
https://docs.progress.com/bundle/openedge-programmimg-interfaces/page/Creating-schema-cache-files.html