ALLBASE/SQL Program Execution

Installing the Program Module

When the preprocessor stores a module in a DBEnvironment, it also creates a file containing a copy of the module, which can be installed into another DBEnvironment. You use the INSTALL command in ISQL to install the module in another DBEnvironment. In this example, the module is installed in the SomeDBE environment which is in the same group and account as the PartsDBE environment:

   isql=> CONNECT TO 'SomeDBE.GROUPDB.ACCTDB';

   isql=> INSTALL SOMEMOD.GROUPC.ACCTDB;



   Name of module in this file:  Pgmr1@ACCTDB.SOMEMOD

   Number of sections installed:  6

   COMMIT WORK to save to DBEnvironment.



   isql=> COMMIT WORK;

ISQL copies the module from the installable module file named SOMEMOD.GROUPC.ACCTDB into a DBEnvironment named SomeDBE.GROUPDB.ACCTDB. During installation, ALLBASE/SQL marks each section in the module valid or invalid, depending on the current objects and authorities in SomeDBE.GROUPDB.ACCTDB. To use the INSTALL command, you need to be able to start a DBE session in the DBEnvironment that is to contain the new module.

Granting Required Owner Authorization

At run time, embedded SQL commands are executed only if the original module owner has the authority to execute them. Therefore, you need to grant required authorities to the module owner in the production DBEnvironment.

If module Pgmr1@ACCTDB.SomeMod contains a SELECT command for table PURCHDB.PARTS, the following grant would ensure valid owner authorization:

   isql=> GRANT SELECT on PURCHDB.PARTS to Pgmr1@ACCTDB;

If Pgmr1@ACCTDB had DBA authority, he could have assigned ownership of the module to another owner by using the OWNER parameter:

   :RUN PSQLCOB.PUB.SYS;INFO='SomeDBE.GROUPDB.ACCTDB &

    (MODULE(SOMEMOD) OWNER (PURCHDB))'

In this case, ownership belongs to a class, PurchDB. Only an individual with DBA authority can maintain this program, and runtime authorization would be established as follows:

   isql=> GRANT SELECT ON PURCHDB.PARTS TO PURCHDB;

Granting Program User Authorization

In order to execute an ALLBASE/SQL program you must be able to start any DBE session initiated in the program. You must also have one of the following authorities in the DBEnvironment accessed by the program:

   RUN

   module OWNER

   DBA

A DBA must grant the authority to start a DBE session. In most cases, application programs start a DBE session with the CONNECT command, so CONNECT authorization is sufficient:

   isql=> CONNECT TO 'SomeDBE.GROUPDB.ACCTDB';

   isql=> GRANT CONNECT TO SomeUser@SomeAcct;

   isql=> COMMIT WORK;

If you have module OWNER or DBA authority, you can grant RUN authority:

   isql=> CONNECT TO 'SomeDBE.GROUPDB.ACCTDB';

   isql=> GRANT RUN ON Pgmr1@ACCTDB.SomeMod TO SomeUser@SomeAcct;

   isql=> COMMIT WORK;

Now SomeUser@SomeAcct can run program SomeProg.GROUPC.ACCTDB:

   :HELLO SomeUser.SomeAcct

    .

    .

    .

   :RUN SomeProg.GROUPC.ACCTDB

Running the Program

At run time, two file equations may be required--one for the ALLBASE/SQL message catalog and one for the DBEnvironment to be accessed by the program.

If the program contains the SQLEXPLAIN command, the ALLBASE/SQL message catalog must be available at run time. SQLEXPLAIN obtains warning and error messages from SQLCTxxx.PUB.SYS. If SQLCTxxx is installed in a different group or account on your system, you must use a file equation to specify its location. Chapter 2 contains further information on the ALLBASE/SQL message catalog.

If the program contains a CONNECT or START DBE command that uses a back referenced DBEnvironmentName, submit a FILE command to identify the DBEnvironment to be accessed by the program at run time:

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



       This command initiates a DBE session in the DBEnvironment

       identified at run time as follows:



   :FILE DBE=SomeDBE.SomeGrp.SomeAcct

Once you identify the ALLBASE/SQL message catalog and appropriate DBEnvironment, you can run the program:

   :RUN SomeProg.GROUPC.ACCTDB

You must specify the name of an executable program file as SomeProg. Do not specify a module name in the RUN command.

At run time, an ALLBASE/SQL program interacts with the DBEnvironment as illustrated in Figure 1-4.

Figure 1-4 Runtime Events

All the COBOL statements inserted by the preprocessor and the stored sections automatically handle database operations, including providing the application program with status information after SQL commands are executed. SQL commands that have a stored section are executed if the section is valid at run time or can be validated by ALLBASE/SQL at run time.

Dynamic commands are those not defined until run time. Such commands can be entered by the user at run time. ALLBASE/SQL converts these commands into executable ALLBASE/SQL instructions at run time rather than at preprocessing time. Sections and other instructions created for dynamic data manipulation commands are deleted at the end of the transaction.