Maintaining ALLBASE/SQL Applications

• Tuning performance.

• Managing the source code.

• Updating a program in production use.

• Changing runtime authorizations as program users change.

• Obsoleting application programs.

Tuning Performance

When an application enters production, you can estimate the best allocation of system resources, but only after it has run for a while can you fine-tune the performance. Refer to the ALLBASE/SQL Performance Guidelines for additional information about "Controlling Performance."

The best performance also depends on good transaction management. Refer to the ALLBASE/SQL Reference Manual for additional discussion of transaction management.

Managing Source Code

You should carefully maintain the source code for each application program in case it should be necessary to re-preprocess and re-compile at a later time. In addition to C source, it is recommended that you keep copies of the ISQL command files used to create your DBEnvironments. This information is extremely useful when modifying existing code.

Use SQLGEN to create schema files for storage once you have configured the DBEnvironment on the production side. Refer to the ALLBASE/SQL Database Administration Guide for information about using SQLGEN.

Updating Application Programs

Minor modifications to programs in the production environment can be made while the program is not currently in use. Major program modifications, because they are more time-consuming, are usually made on a development machine in a development DBEnvironment.

In either case, the OWNER of the program's module (or a DBA) preprocesses the revised program and replaces the old module with a new one. Existing RUN authorities can be either preserved or revoked. Dropping old modules and preserving or revoking RUN authorities can be done by using the DROP MODULE command in ISQL or can be specified when you invoke the preprocessor.

The PRESERVE option of the DROP MODULE command retains any existing RUN authorities for the module when it is deleted from the system catalog:

   isql=> DROP MODULE myprog PRESERVE;

To delete a module and any existing RUN authorities for that module, simply omit the PRESERVE option.

You can also drop a module and revoke any existing run authorities for it at preprocessing time:

   : RUN PSQLC.PUB.SYS; INFO="SOMEDBE (MODULE (MODULENAME) DROP REVOKE)"

The DROP option tells the preprocessor to drop any existing module named MODULENAME; if you omit the DROP option, a new module is created only if a module named MODULENAME does not exist. This invocation line drops any existing module named MODULENAME, and revokes any related RUN authorities. To revoke the RUN authorities by specifying the REVOKE option in the INFO string, you must also drop the module by specifying the DROP option.

The DROP MODULE command is also useful for revised programs whose modules must be installed in a DBEnvironment different from that on which preprocessing occurred. Before using the INSTALL command to store the new module, drop the existing module using the DROP MODULE command, preserving or dropping related RUN authorization as required.

Changing Program-Related Authorization

Once a program is in production use, you may need to grant and revoke RUN and CONNECT authority as program users change. Revoking CONNECT authority requires DBA authorization:

   isql=> REVOKE CONNECT FROM OLDUSER@ACCOUNT;

Revoking RUN authority requires either module OWNER or DBA authority:

   isql=> REVOKE RUN ON PGMR1@ACCOUNT.SOMEPGM FROM OLDUSER@ACCOUNT;

Dropping Obsolete Modules

When an application program becomes obsolete, you use the DROP MODULE command to both remove the module from any DBEnvironment where it is stored and revoke any related RUN authorities:

   isql=> DROP MODULE myprog;

Related RUN authorities are automatically revoked when you do not use the PRESERVE option of this command.