ALLBASE/SQL FORTRAN Programs

• Start or terminate a DBEnvironment session, either in single-user mode or multiuser mode.

• Start or terminate a transaction.

• Retrieve rows from or change data in tables in a database.

• Create or drop objects, such as indexes or views.

• Identify FORTRAN variables referenced in SQL commands, known as host variables.

• Set up a common block, known as the SQL Communications Area (SQLCA), for communicating the status of executed SQL commands to your program.

• Optionally automate program flow based on SQLCA information.

• Identify cursor declarations.

Program Structure

The following example skeleton program illustrates the relationship between FORTRAN statements and embedded SQL commands in an application program. SQL commands may appear in a program at locations indicated by shading. The SQLCA Common Block Declaration may appear either before or after the FORTRAN type declaration section. The SQLCA Common Block Declaration must appear, however, before the host variable declaration section which must be the last of the type declarations in the program unit. Refer to the chapter "Embedding SQL Commands" for further clarification of program structure.

   PROGRAM ProgramName
   FORTRAN Statements
   .
   .
   .
   SQLCA Declaration
   FORTRAN Type Declarations
   Host Variable Declarations
   .
   .
   .
   FORTRAN Statements, some containing SQL Commands
   .
   .
   .
   END
   SUBROUTINE SubroutineName
   FORTRAN Statements
   .
   .
   .
   SQLCA Declaration
   FORTRAN Type Declarations
   Host Variable Declarations
   .
   .
   .
   FORTRAN Statements, some containing SQL Commands
   .
   .
   RETURN
   END

To delimit SQL commands for the preprocessor, each SQL command is prefixed by EXEC SQL:

   EXEC SQL BEGIN WORK

SQL commands may appear in the main program or any subprogram unit where you establish DBEnvironment access and manipulate data in a database.

DBEnvironment Access

During preprocessing, the preprocessor needs to access the same DBEnvironment your source code will access at runtime. Therefore when you invoke the preprocessor, the DBEnvironment your program accesses must exist and you must identify the DBEnvironment in the preprocessor command:

    $ psqlfor DBEnvironment -i SourceFileName.sql -p ModifiedSourceFileName.f

In addition, you define a DBEnvironment session for the same DBEnvironment in your source code:

   SUBPROGRAM Unit
   .
   .
   .
   EXEC SQL CONNECT TO 'DBEnvironment'
   .
   .
   .
   EXEC SQL RELEASE
   RETURN
   END

At runtime, the program starts a DBE session in DBEnvironment, where a module for the program has been stored.

If in your program you use either a dynamic command or a host variable to connect to a DBEnvironment, no DBEnvironment is defined in your source code and any module that is stored in the DBEnvironment identified in the preprocessor command is marked invalid. Refer to the chapter, "Using Dynamic Operations," for more information on dynamically connected to a database and refer to the chapter, "Host Variables," for more information on using a host variable to connect to a database.

To access more than one ALLBASE/SQL DBEnvironment from the same program, you must separate the program into separate compilable sections. Each section must access only one DBEnvironment. In each section you start and terminate a DBE session for the DBEnvironment that section accesses. You then preprocess and compile each section separately. When you invoke the preprocessor, you identify the DBEnvironment accessed by the section being preprocessed. After a section is preprocessed, it must be compiled using the -c option so that no linking is performed before the next section is preprocessed. After each program section is preprocessed and compiled, you link all of the compiled modules together to create the executable program.

   $ psqlfor DBEnvironment1 -i SourceFileName1.sql -p ModifiedSourceFile1.f
   $ fc -c ModifiedSourceFileName1.f
   .
   .
   .
   $ psqlfor DBEnvironment2 -i SourceFileName2.sql -p ModifiedSourceFile2.f
   $ fc -c ModifiedSourceFileName2.f
   $ fc ModifiedSourceFileName1.o ModifiedSourceFileName2.o -lsql -lcl -lportnls -o ExecutableFileName

	300/400: The link command line differs for Series 300 and 400 systems. $ fc ModifiedSourceFileName1.o ModifiedSourceFileName2.o -lsql -lheap2 -lportnls -lpc -o ExecutableFileName

In somes cases an ALLBASE/SQL program is used with a DBEnvironment name different from the DBEnvironment accessed at preprocessing time. In these cases, the program can accept a DBEnvironment name from the program user and dynamically preprocess the SQL command that starts a DBEnvironment session. Dynamic preprocessing is covered in the chapter, "Using Dynamic Operations."

To preprocess or use an already preprocessed ALLBASE/SQL application program, you must satisfy the authorization requirements for each DBEnvironment accessed.

Authorization

ALLBASE/SQL authorization governs who can preprocess, execute, and maintain a program that accesses an ALLBASE/SQL DBEnvironment.

To preprocess a program for the first time, you need CONNECT or DBA authority in the DBEnvironment your program accesses. When you preprocess a program file, ALLBASE/SQL stores a module for that program file in the DBEnvironment's system catalog and identifies your login as the owner of that module. Subsequently, if you have OWNER or DBA authority, you can re-preprocess the program file.

To run a program accessing an ALLBASE/SQL DBEnvironment, you need the following authorities:

At runtime, any SQL command in the program, except for the command used to start the DBE session, is executed only if the OWNER of the module has the authorization to execute the command at runtime. However, any dynamic command is executed only if the individual running the program has the authority to execute the command at run time. A dynamic command is an SQL command entered by the user at runtime.

Maintaining an ALLBASE/SQL program includes such activities as modifying a program in production use and keeping runtime authorization current as program users change. For these activities, you need OWNER authority for the module or DBA authority. More on this topic appears later in this chapter under "Maintaining ALLBASE/SQL Programs."

File Referencing

When you create a DBEnvironment, a Database Environment Configuration (DBECon) file is created. The absolute pathname of this DBECon file is stored in the DBECon file itself. In all subsequent references to files, you may use either an absolute or a relative pathname. By default, all pathnames of files and directories are relative to the pathname stored in the DBECon file. Any file reference which does not begin with a slash (/) assumes a pathname relative to that of the DBECon file.

For example, if a DBEnvironment is created with the following command:

   START DBE 'PartsDBE' NEW

and the user is currently in the directory /users/dbsupport/sql, the pathname /users/dbsupport/sql would be stored in the DBECon file. If the user subsequently creates a DBEFile with the command:

   CREATE DBEFILE Orders WITH PAGES=50, NAME='OrdersFS'

the actual pathname of the file OrdersFS would be relative to the pathname stored in the DBECon file and would be /users/dbsupport/sql/OrdersFS. Relative pathnames are restricted to a maximum length of 128 characters. If however, the user were to create a DBEFile with the command:

   CREATE DBEFILE Orders WITH PAGES=50, NAME='/users/shipping/OrdersFS'

the pathname stored in the DBECon file is ignored while creating this file. The user then needs to fully qualify this pathname each time the file is referenced. Absolute pathnames are restricted to a maximum length of 128 characters. Fully qualified file names, enclosed in quotes, are restricted to a maximum length of 44 bytes.

In addition, if the DBEnvironment you want the preprocessor to access resides in a directory other than your current working directory, you will have to either absolutely or relatively qualify the pathname of that DBEnvironment.

For example, if the DBEnvironment you want the preprocessor to access resides in a subdirectory of yours, you would invoke the preprocessor as follows:

      $ psqlfor sampledb/SomeDBE -i SourceFileName.sql -d

Use of Shared Memory

ALLBASE/SQL uses two types of shared memory, DBCore shared memory and inter-process communication (IPC) shared memory.

When you start a DBEnvironment with the START DBE command, a block of DBCore shared memory is obtained for the current DBEnvironment session. Until the session ends, all users and programs accessing the DBEnvironment share this allocated memory, which includes:

The use of DBCore shared memory is illustrated in Figure 1-2 “ Use of DBCore Shared Memory in a DBEnvironment”.

Figure 1-2  Use of DBCore Shared Memory in a DBEnvironment

Since these are shared resources, your program cannot manipulate their size directly. However, a DBA can alter them with the SQLUtil ALTDBE command. Refer to "Estimating Shared Memory Requirements" in the "Physical Design" chapter of the ALLBASE/SQL Database Administration Guide .

An IPC shared memory segment is allocated for each local access application accessing the DBEnvironment. IPC shared memory is mapped to the user's process.

Series 300 Shared Memory

	300/400: The following shared memory considerations apply only to Series 300 and 400 systems. Systems using SQL must be configured with a minimum shmmaxaddr of 13MB. The default shmmazaddr is 16MB and is recommended. (See the HP-UX System Administrator Manual for information on shmmaxaddr, an operating system parameter.) ALLBASE/SQL application programmers using their own shared memory must consider the following: Do not map your shared memory between the 12MB to 16MB address space used by ALLBASE/SQL. It is recommended that you avoid mapping shared memory within 1MB of the top of the data segment (brk). CONNECT to your DBEnvironment prior to mapping in your own shared memory. This is to satisfy ALLBASE/SQL requirements for dynamic data storage.

Choosing an Interprocess Communication Option

ALLBASE/SQL offers two interprocess communication options. Fast ipc is the default and is available for the E.1 release. The previously existing interprocess communication method, signal ipc, remains a viable alternative depending on your application requirements.

Any applications developed with ALLBASE/SQL release E.1 will by default use fast ipc. Any existing applications developed on a prior release will by default use signal ipc unless they are relinked. Once an existing application has been relinked, fast ipc is in effect. You have the option of setting an environment variable to indicate which form of interprocess communication you wish to use.

The following sections describe each option, how to set the environment variable, and debugging considerations:

Using Fast IPC

Fast ipc offers the following features:

• Performance is improved for applications with many, simple ALLBASE/SQL statements because overhead is reduced in communicating a request between an application and ALLBASE/SQL. It is the complexity of the statement that determines whether or not improved performance results. The size of a transaction or the size of a query result do not affect performance in regard to fast ipc.

• Only signal 18 (SIGCLD) is used by ALLBASE/SQL when fast ipc is enabled. Therefore, if your application uses signals, you should avoid using signal 18.

	NOTE: If you want to use fast ipc and trap signals, note that once a signal has been trapped and processed, your code must return from the signal handling routine to the trap point before further processing continues.

Setting the HPSQLfast_ipc Environment Variable

The following example illustrates how to set the HPSQLfast_ipc environment variable.

Suppose you already have applications that must use signals and which were written prior to this release. Signal ipc is the default unless you relink your applications. In this case, you reset the signal ipc option by setting the environment variable, HPSQLfast_ipc to n or N.

For the Bourne shell or Korn shell:

HPSQLfast_ipc=n; export HPSQLfast_ipc

For the C shell:

setenv HPSQLfast_ipc n

Perhaps your existing applications do not use signals, and you decide to take advantage of the new option. To reset to the fast ipc option you must relink your applications.

	NOTE: The appropriate HPSQLfast_ipc environment variable must be in effect at run time.

Setting Signal Handling for Debugging

Be aware that when you enter a debugging session, you must set the signal handling table for the xdb debugger, for example:

z 18 rs

	300/400: Series 300 and 400 systems use the cdb debugger with a different format, as follows: 18 z rs