Maintaining Applications

The DBA is involved in maintaining applications as follows:

Monitoring changes that invalidate sections and determining when to re-preprocess or update applications based on those changes.
Monitoring system catalog space for modules.
Maintaining module related authorities.
Sharing modules between DBEnvironments.
Dropping modules.

Invalidation and Revalidation of Sections

Before changes are made to a DBEnvironment, the impact of those changes on preprocessed statements should be weighed. The following information will help you be aware of which changes affect preprocessed statements.

In full preprocessing mode, the preprocessor stores a section for each embedded statement except:

   BEGIN DECLARE SECTION       BEGIN WORK            CLOSE CURSOR

   COMMIT WORK                 CONNECT               CREATE SCHEMA

   END DECLARE SECTION         EXECUTE               EXECUTE IMMEDIATE

   INCLUDE                     OPEN CURSOR           PREPARE

   RELEASE                     ROLLBACK WORK         SAVEPOINT                   

   START DBE                   STOP DBE              TERMINATE USER

The statements listed above either require no authorization to execute or are executed based on information contained in the compilable preprocessor output files.

In interactive mode, ALLBASE/SQL stores a section for the following SQL statements:

   PREPARE

   CREATE VIEW

When a section is stored, ALLBASE/SQL actually stores what are known as an input tree and a run tree. The input tree consists of the uncompiled statement. The run tree is the compiled, optimized, executable form of the statement.

If a section is valid at run time, ALLBASE/SQL executes the appropriate run tree when the SQL statement is encountered in the application program or procedure. If a section is invalid, ALLBASE/SQL determines whether the objects referenced in the sections exist and whether current authorization criteria are satisfied. If an invalid section can be validated, ALLBASE/SQL dynamically recompiles the input tree to create an executable run tree and executes the statement. If a section cannot be validated, the statement is not executed, and an error condition is returned to the program.

Information in the System Catalog on Validity of Sections

The SYSTEM.SECTION view contains information about stored sections. The TYPE column defines the type of SQL statement in the section:

A section for executing the SELECT statement associated with a DECLARE CURSOR statement is identified by a 1 in the TYPE column.
A section for executing the SELECT statement associated with a CREATE VIEW statement is identified by a 2 in the TYPE column.
Sections for all other statements for which the preprocessor stores a section are identified by a 0 in the TYPE column.

The STYPE column defines the section type:

A section that is part of a module is identified by a 0 in the STYPE column.
A section that is part of a procedure is identified by a 1 in the STYPE column.

The VALID column tells whether the section is valid or invalid. If a section is marked invalid, it is identified by a 0 in the VALID column. If a section is valid, it is identified by a 1. Refer to the "System Catalog" chapter for a description of all the columns in SYSTEM.SECTION.

The example below illustrates the kind of information in the SYSTEM.SECTION view:

   SELECT Name,Owner,Section,Type,Stype,Valid FROM System.Section;





   ------------------------------------------------------------------

   NAME              |OWNER           |SECTION  |TYPE  |STYPE  |VALID

   ------------------------------------------------------------------

   TABLE             |SYSTEM          |        0|     2|      0|    1 

   COLUMN            |SYSTEM          |        0|     2|      0|    1

   INDEX             |SYSTEM          |        0|     2|      0|    1

   SECTION           |SYSTEM          |        0|     2|      0|    1

   DBEFILESET        |SYSTEM          |        0|     2|      0|    1

   DBEFILE           |SYSTEM          |        0|     2|      0|    1

   SPECAUTH          |SYSTEM          |        0|     2|      0|    1

   TABAUTH           |SYSTEM          |        0|     2|      0|    1

   COLAUTH           |SYSTEM          |        0|     2|      0|    1

   MODAUTH           |SYSTEM          |        0|     0|      0|    1

   GROUP             |SYSTEM          |        0|     2|      0|    1

   PARTINFO          |PURCHDB         |        0|     2|      0|    0

   VENDORSTATISTICS  |PURCHDB         |        0|     2|      0|    1

   CEXP11            |KAREN@RIZZO     |        1|     1|      0|    1

   CEXP11            |KAREN@RIZZO     |        2|     0|      0|    1

   FAILURELIST       |MANUFDB         |        1|     0|      1|    1

   ------------------------------------------------------------------

   Number of rows selected is 16.

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>

The first eleven rows in this query result describe some of the sections stored for the system views. The next two rows describe two views in the sample database: PurchDB.PartInfo and PurchDB.VendorStatistics. Views are always stored as invalid sections, because the run tree is always generated at run time when the view is queried.

The remaining rows describe sections associated with preprocessed programs and procedures. Module CEXP11 contains two sections, one for executing the SELECT statement associated with a DECLARE CURSOR statement and one for executing a FETCH statement. Procedure ManufDB.FailureList contains one section, for an INSERT statement. For more information on preprocessing and cursors, refer to the appropriate ALLBASE/SQL Application Programming Guide and the ALLBASE/SQL Advanced Application Programming Guide. For more on procedures, see the ALLBASE/SQL Reference Manual chapter "Constraints, Procedures, and Rules."

Monitoring File Space for Modules and Sections

In order to monitor file space used for program modules and other stored sections, you need to perform the following tasks:

determine the available file space periodically
calculate the number of pages needed to store new modules

Note that through the ALTER TABLE, CREATE TABLE, CREATE PROCEDURE, CREATE RULE, CREATE VIEW, DECLARE CURSOR, and PREPARE statements you can specify a DBEFileSet for storing sections, table or long column data. If a DBEFileSet is not specified, the default DBEFileSet is used instead. Refer to the ALLBASE/SQL Reference Manual syntax for these statements and for the SET DEFAULT DBEFILESET, GRANT and REVOKE statements for complete information.

Causes for Invalidation of Sections

Sections are stored in modules or procedures. ALLBASE/SQL generates a program module when an embedded SQL program is preprocessed. Any changes to an object accessed by a section will cause that section to be invalidated.

For example, if you drop a table, all sections that assume the existence of that table will be invalidated. To enable ALLBASE/SQL to revalidate the section at run time, the table must be recreated before the section is executed. Likewise, all sections are marked invalid during migration of the DBEnvironment. ALLBASE/SQL automatically attempts to revalidate the sections at run time.

The following statements, if they operate on an object accessed by a given section, will cause that section to be invalidated:

   ADD DBEFILE                 ALTER TABLE        CREATE INDEX

   CREATE RULE                 DROP DBEFILE       DROP GROUP    

   DROP INDEX                  DROP MODULE        DROP PROCEDURE

   DROP RULE                   DROP TABLE         DROP VIEW

   REMOVE FROM GROUP           REVOKE             TRANSFER OWNERSHIP   

   UPDATE STATISTICS

At run time, ALLBASE/SQL will automatically revalidate most of the sections invalidated by any of the statements listed above. If the sections cannot be revalidated by ALLBASE/SQL, the source code must be modified to reflect the changes in the DBEnvironment.

Avoiding the Need for Re-Preprocessing

ALLBASE/SQL will not automatically re-preprocess a program that has undergone source code modification. The program must be fully preprocessed with the C, COBOL, FORTRAN, or Pascal preprocessor. The following statements may require source code changes; each statement is followed by a suggestion on how to avoid changing the code and re-preprocessing the program:

ALTER TABLE: Using this statement invalidates stored sections that access the table. ALLBASE/SQL will revalidate the section if the statements that accessed the table are still valid following the alteration.
DROP (anything accessed by the section): ALLBASE/SQL will revalidate the section if the object is recreated before the program is executed. (This does not apply to indexes, which are not accessed directly by a section.)
REVOKE (those authorities granted to the module owner): ALLBASE/SQL will revalidate the section if the required authorities are granted to the module owner before the program is executed. This will not require source code changes, but may require that the program be re-preprocessed with a different module owner.
TRANSFER OWNERSHIP (for all objects in the program except modules): ALLBASE/SQL will revalidate the section if ownership is transferred back to the original owner, or if the current owner is granted all the required authorities.

The first time an invalidated section is executed (when you run a program or execute a procedure), there may be a decrease in performance while ALLBASE/SQL revalidates the section. If you want to validate sections before executing them, you can use the VALIDATE statement.

Determining Available Space for Sections

To determine the space available in a DBEFileSet for stored sections, run SQLMON and go to Static Size screen. Examine the value of the TABLE PAGES field for every table whose owner is STOREDSECT.

Determining Number of Sections in the DBEnvironment

You can also determine how many sections are currently stored in the DBEnvironment by querying the SYSTEM.TABLE view. First, update statistics on the SYSTEM.SECTION view so that the NROWS column in the SYSTEM.TABLE is updated to show the current number of rows in the SECTION table. The NROWS column for SYSTEM.SECTION shows the number of sections in the DBEnvironment since the last UPDATE STATISTICS statement.

The query in the example below shows 44 sections stored in the DBEnvironment with a total of 2 pages occupied by SYSTEM.SECTION:

   isql=> SELECT Name, NRows, NPages FROM System.Table WHERE  Name='SECTION';





   SELECT Name, NRows, NPages FROM System.Table WHERE Name='SECTION';

   --------------------------------------------

   NAME                |NROWS      |NPAGES

   --------------------------------------------

   SECTION             |         44|          2

   ------------------------------------------------------------------------

   Number of rows selected is 1

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>

Module Related Authorities

Authorities govern who can preprocess, execute, and maintain an application that accesses a DBEnvironment.

To preprocess an application for the first time, you need CONNECT authority for the DBEnvironment in which the module is to be stored. To preprocess an existing application, you need OWNER authority for that module and CONNECT authority for its DBEnvironment.

At run time, the OWNER of the program must have authority to execute all SQL statements in the application if the program is to successfully execute. Dynamic statements are an exception. The individual running the program must have the authority to execute a statement that is dynamically preprocessed.

An individual lacking OWNER or DBA authority must have RUN authority for the module and CONNECT authority for the DBEnvironment to be able to run a program that accesses the DBEnvironment. The DBA or module owner can grant RUN authority to users. Only users with DBA authority can grant CONNECT authority.

To maintain a program (modifying code or updating RUN authority) you need OWNER authority for the module or DBA authority. Ownership cannot be transferred, but users with DBA authority can modify or preprocess the program or grant related authorities.

Sharing Modules Between DBEnvironments

Program development usually entails quite a bit of preprocessing and bug fixing before a program is ready for production. Therefore, developing an application in a production DBEnvironment is not a good idea for the following reasons:

Preprocessing a program may reduce concurrency in a multiuser DBEnvironment by holding locks on the system catalog until preprocessing is finished.
ALLBASE/SQL treats a preprocessing session as a single transaction which can fill buffers and log files quickly.
Preprocessing programs not fully debugged can cause problems for other users in the DBEnvironment.

The use of a separate DBEnvironment for development is recommended. When a program is ready to be moved to a production DBEnvironment, you can either re-preprocess the source in the new DBEnvironment or install the module using the ISQL INSTALL command.

When a C, COBOL, FORTRAN, or Pascal program is preprocessed, the preprocessor creates a file in your current working directory called SQLMOD, which contains a copy of the module that can be installed in another DBEnvironment.

The INSTALL command installs a module in another DBEnvironment. To use the INSTALL command, you need to have CONNECT or DBA authority for the DBEnvironment that will contain the new module. The following example illustrates the use of the INSTALL command:

   isql=> CONNECT TO 'PartsDBE';

   isql=> INSTALL SQLMOD.COBOL.ALLBASE;



   Name of module in this file: PGMR1@ALLBASE.SOMEPROG

   Number of sections installed: 6

   COMMIT WORK to save to DBEnvironment.

   isql=> COMMIT WORK;

ISQL copies the installable module from the file named SQLMOD.COBOL.ALLBASE. During installation, ALLBASE/SQL marks each section in the module valid or invalid, depending on the current objects and authorities in PartsDBE.

If your DBEnvironments have different names, you can avoid re-preprocessing a separate program with a different CONNECT statement in each by back referencing the DBEnvironment in the program. Then you can use the INSTALL command to install the module in the desired DBEnvironments. A COBOL program back referencing a DBEnvironment will contain the following CONNECT command:

   EXEC SQL CONNECT TO '*DBE' END-EXEC.

The appropriate DBEnvironment is identified before run time with a file equation. The following file equation identifies SomeDBE.SomeGrp.SomeAcct as the DBEnvironment the program accesses at run time:

   :FILE DBE=SomeDBE.SomeGrp.SomeAcct

Dropping Modules

Before a program can be preprocessed a second time, the previously stored module must be dropped. To do this, you can use the DROP MODULE statement in ISQL, or you can use the DROP option of the preprocessor command:

  isql=> DROP MODULE CEXP01D;

   : RUN PSQLPAS.PUB.SYS;INFO="PartsDBE (MODULE(CEXP01D)    DROP)"

The DROP MODULE statement assumes that all related RUN authorities are to be dropped along with the module. Therefore, when using the DROP MODULE statement, use the PRESERVE option to preserve all related RUN authorities for the new version of the module:

   isql=> DROP MODULE CEXP01D PRESERVE;

The preprocessor, on the other hand, assumes that all related RUN authorities are to be PRESERVED unless revoked with the REVOKE option. Thus you do not need to specify PRESERVE with the preprocessor:

   : RUN PSQLPAS.PUB.SYS;INFO="PartsDBE (MODULE(CEXP01D) DROP)"

The DROP MODULE statement is also useful in conjunction with 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, you drop the existing module using the DROP MODULE statement, preserving or dropping related RUN authorities as required.