HP 3000 Manuals HP 3000 Manuals
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;
or
: 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.