The Executable Program

• Load the executable program file onto the machine where the production DBEnvironment resides.

• Install any related module(s) in the production DBEnvironment.

• Ensure necessary module owner authorities exist.

• Grant required authorities to program users.

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.

   isql=>  CONNECT TO 'SOMEDBE.GROUP3.ACCTDB;

   isql=>  INSTALL SOMEMOD.GROUP2.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.GROUP2.ACCTDB into a DBEnvironment named SOMEDBE.GROUP3.ACCTDB. During installation, ALLBASE/SQL marks each section in the module valid or invalid, depending on the current objects and authorities in SOMEDBE.GROUP3.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. If you are replacing a module with a new one of the same name, make sure no other users are accessing the module. To avoid problems, install modules while connected to the DBEnvironment in single-user mode.

Granting Required Owner Authorization

At runtime, 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 PSQLFOR.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.GROUP3.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 'SOMEDB.GROUP3.ACCTDB';

   isql=>  GRANT RUN ON PGMR1@ACCTDB.SOMEMOD TO SOMEUSER@SOMEACCT;

   isql=>  COMMIT WORK;

Now SOMEUSER@SOMEACCT can run program SOMEPROG.GROUP1.ACCTDB which accesses module PGMR1@ACCTDB.SOMEMOD.

   :HELLO SOMEUSER.SOMEACCT

    .

    .

    .

   :RUN SOMEPROG.GROUP1.ACCTDB

Note that if a program contains more than one module (multiple application source files), you need to GRANT RUN authority to the user for each module.

Running the Program

At runtime, 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 runtime. SQLEXPLAIN obtains warning and error messages from the message catalog (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. See Chapter 2 for 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 runtime:

   EXEC SQL CONNECT TO '*DBE'

This command initiates a DBE session in the DBEnvironment identified at runtime 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.GROUP1.ACCTDB

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

At runtime, an ALLBASE/SQL program interacts with the DBEnvironment as illustrated in Figure 1-4 “Runtime Events”.

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

Dynamic commands are those not known until runtime. Such commands can be entered by the user at runtime. ALLBASE/SQL converts these commands into executable ALLBASE/SQL instructions at runtime rather than at preprocessing time. Sections and other instructions created for dynamic data manipulation commands are deleted at the end of the transaction. Dynamic commands are described in more detail in the chapter "Using Dynamic Operations."

Figure 1-4 Runtime Events