|
Gary examines two coding methods-the Compound SQL statement and stored procedures-to reduce and reuse embedded SQL code to increase performance and productivity.
November 01, 1996
URL:http://www.drdobbs.com/embedded-systems/optimizing-clientserver-database-applica/184409993
Gary is a DB2 course developer at IBM's Toronto Lab. He can be contacted at [email protected].
As we move from large, centralized systems to client/server systems that use a mixed bag of LAN-connected hardware, we need to optimize our programs for the new environment. In databases where the application will be on the client and the data will be on the server, "optimizing" means reducing blocks of executable SQL statements (to reduce costly calls to the server) and reusing blocks that perform similar functions (to minimize the size of the code). To developers, this combination means less traffic on the LAN between clients and servers and less SQL code to maintain. To users, the combination means better performance and smaller programs. In this article, I'll look at two coding methods that reduce and reuse embedded SQL code-the Compound SQL statement and the stored procedure. The example code I'll present was written for IBM's DB2 for common servers Version 2. DB2 for common servers is available on many platforms. I worked with a typical client/server configuration for DB2: OS/2 client to AIX server.
When creating a client/server application, the different stages of development require different refinements. In the design and implementation stages, you should separate the application from the data by placing that data with the database at the server. In the optimization stage, you should reduce calls between the client (which contains the application) and the server. Fewer calls mean less activity on the LAN, and that means better performance for the application. When calls are made to the server, the transactions with the database should take place mostly between the database manager and the database at the server. Ideally, you make one call from the client to the server and get fully processed data returned from the database; see Figure 1.
The Compound SQL statement is a way of rolling a number of separately executable SQL statements into one. To understand the importance of executing these statements together, remember that embedded SQL requires that the application be preprocessed.
For instance, after Example 1(a) is preprocessed, the SQL code will be replaced by three call statements to the generated SQL module that corresponds to the embedded program. Each call might include a connection to a database and a commitment of transactions to the database. Those connections and commitments will come at a performance cost, though, so you should reduce the number of calls. Assume that you analyzed the code and realized you could eliminate some C statements and group the embedded SQL statements to produce Example 1(b). This is an improvement over Example 1(a), as one connection can now be made to a database for all the statements. However, Example 1(c), consisting of one executable block of SQL statements, is even better. After preprocessing, the SQL code will be replaced by a single call statement.
To illustrate how you can combine SQL statements, I'll use a typical set of operations that could be performed more effectively by one Compound SQL-a large number of inserts to the database.
Let's take the case of Fuzzy Applications Corporation, which is in the midst of an expansion: Fuzzy Apps is hiring so many developers that the human-resource director constantly updates the database with new personnel information. The HR director wrote a simple application to process the names of the all new hires in one executable block. Listing One is the complete application. Example 2 shows the Compound SQL statement in isolation. The statement is preceded by the EXEC SQL keywords to indicate to a SQL preprocessor that SQL (not C) statements follow.
A query on the PERSONNEL table after entering nine names results in Figure 2. Since the application did not enter any salaries for the new hires, the SALARY column for them is filled with null values.
Compound SQL should be used during the optimization stage-when you review the code and eliminate redundant lines and excessive calls to the server for data. Look for separate SQL statements in the same application module that can be rolled together under Compound SQL. Also look for repetitive operations, such as mass inserts or deletions, that can be performed in one batch operation. Used throughout many modules in a large application, Compound SQL will lead to a significant improvement in performance.
Because Compound SQL has a STOP AFTER clause, it can be used effectively in an application where the input values are not known until run time. In Listing One, for example, neither the new hires' names nor the number of names are known until run time.
There are two forms of the statement: ATOMIC and NOT ATOMIC. Listing One uses ATOMIC. Should there be an error in one of the substatements during execution, ATOMIC will undo all changes to the database caused by Compound SQL. NOT ATOMIC continues regardless of an error generated from a substatement. Use ATOMIC when it is critical that all substatements execute cleanly. Use NOT ATOMIC with distributed database applications where some architectures may not support a Compound SQL statement that undoes changes.
If Compound SQL is so wonderful, why not use it every time SQL statements can be grouped together? There are two reasons. First, not all SQL statements are supported. Second, only a static (as opposed to dynamic) version of Compound SQL exists. This means that you cannot use a host variable to contain intermediate results from a set of substatements. For example, suppose a host variable has a value of 7 prior to the execution of the Compound SQL statement. In a substatement, 2 is added to the host variable to create a value of 9. If a later substatement uses this host variable, it will still contain a value of 7.
Breaking functions into modules makes a lot of sense when designing an application. It reduces code and simplifies maintenance. SQL code can be grouped into modules and called from a main program. These modules are called "stored procedures." In Example 3(a), you can see a repeating pattern of SQL code. Example 3(b) shows these SQL statements in a separate, callable stored procedure. Stored procedure 1 would be written as Example 3(c).
If all of these SQL statements were designed for one database at one server, it would be even more efficient to preprocess and bind them at that server-in fact, this is what a stored procedure is designed for. When called, it performs an operation on the data at that server and returns the result to the main program, to avoid tying up either client or LAN resources. A stored procedure behaves functionally like any other module called by an application; however, it must be preprocessed and bound to the server where the database resides.
There are other reasons to use a stored procedure, including performance (the procedure is stored in an executable format), productivity (the procedure is like an object in the object-oriented programming model), and security (the procedure cannot be manipulated by users).
Fuzzy Apps is changing daily. As Figure 2 shows, some developers have taken an educational leave (Brooks), others have left (Fonzerello), and still others have been let go (Fudd). Some developers have retired (Fermat), or ceased to be (Marley), and one new hire has applied for maternity leave (Brown).
The HR director realizes the need to expand the program to better organize this data. A second application can insert the salaries of new hires and update those of employees who are retiring or on leave. She would like to have active employees in the PERSONNEL table, on-leave employees in an INACTIVE table, and those no longer with the firm in a HISTORY table. Retirees and employees on educational leave would have their salaries reduced by half; the salaries of the others would be changed to zero.
Of course, sending these instructions back and forth across the LAN between the client (a PC with OS/2) and server (a RISC/6000 with AIX) will cost network resources. But since all the data is at one server (fuzzysrv), this is a perfect opportunity to use a stored procedure. Only one call needs to be made to the server and only one block of data (the numbers of managers, employees, and people on leave) would be returned to the client.
Listing Two presents the application as a stored procedure named "tableorg." Note that the SQL statements are rolled into one stored procedure, then into one Compound SQL statement to further increase efficiency. A CALL statement in the application invokes the stored procedure, which reorganizes personnel tables at the fuzzysrv server and returns the numbers needed by the application on the client. The code to call the stored procedure from the application is EXEC SQL CALL :procname USING DESCRIPTOR :*inout_sqlda;.
The CALL statement indicates the stored procedure to call (procname). The keywords USING DESCRIPTOR followed by a pointer to inout_sqlda highlight IBM's implementation of stored procedures. DB2 uses a structure called the SQL Descriptor Area (SQLDA) to pass information between the main program and the stored procedure. Listing Two uses SQLDA as inout_sqlda, a mnemonic that describes the behavior of the SQLDA: that is, to pass information in and out of the main program and the stored procedure. The SQLDA contains information about each host variable. Host variables derive their names from the fact that they can be used within the host application program, as well as in the SQL sections of the program. Declared in the BEGIN DECLARE section, host variables are the basic units used to pass data between application code (in this case, written in C) and SQL code. The SQLDA contains four pieces of information about each host variable: its name, data type, length, and indicator variable (which contains associated information, such as whether the value is null).
IBM's implementation of stored procedures using a SQLDA, together with application and SQL code, differs from other database tools. Most other database products use a stored-procedure language. Which is better? It depends. IBM's implementation gives the flexibility to code in a familiar language (C, Cobol, Fortran, and so on). However, the use of pointers from the SQLDA to the addresses of the host variables requires a small learning curve.
There are some interesting pointer data types in the main program when the SQLDA is initialized. Specifically, character pointers are used to point to the addresses of the host variables for the number of managers, employees, and those on leave-even though these host variables were declared as short integers; see Example 4(a). Then, in the stored procedure at the bottom of the file, the pointer types are changed to the expected short integer; see Example 4(b). Also at the bottom of the file is the position of the SQLDA pointers in the stored procedure. When values are returned from the stored procedure to the main program, the pointers to the local host variables in the stored procedure must be defined at the end of the file (after any processing has occurred).
Once a stored procedure is executed, the data in the tables at the server are updated as shown in Figure 3. The application at the client then outputs the counts of the managers, active employees, and people on leave based on the data passed back through the host variables; see Figure 4.
Since the stored procedure consists largely of a Compound SQL statement, could you have just added those substatements to the Compound SQL statement at the client and not written a stored procedure? No and yes. The Compound SQL statement at the client made use of the STOP AFTER FIRST clause: If only six new hires were added, then the Compound SQL statement would have stopped after six inserts. You could have, however, created a second Compound SQL statement with all the substatements in the stored procedure. The advantage of using a stored procedure is that information can be shared, but sensitive data (such as salary) is hidden from end users.
Candidates for modules in an application usually become self-evident in the design stage. This is also the best time to consider creating stored procedures (SQL modules to complement the application program modules). Look at repeating patterns of SQL statements in the application code and group them into one stored procedure. Then take a second pass at the stored procedure to see if Compound SQL can be used to further improve performance by reducing the number of executable statements.
Another time to consider writing stored procedures is during application maintenance. If the growth in program code over time results in repetitive patterns, then new stored procedures should be created.
Fuzzy Apps has a configuration similar to many corporations today: a number of PCs (with OS/2) on a LAN connected to one large and fast server, in this case, a RISC/6000 machine (with AIX). Given this configuration, the following points should be kept in mind when passing data between the client and the server with a stored procedure:
We tend to think of optimizing client/ server applications in terms of optimizing our application code-without considering the SQL statements as part of that process. Yet there are methods that can help you both reduce calls to the server from the client and reduce the size of the SQL code itself. By introducing Compound SQL and stored procedures into your client/server applications, you will see increased performance and gains in productivity.
I'd like to thank Robert Begg, Eugene Chun, and Doug Doole, who provided technical advice concerning the SQL Descriptor Area (SQLDA). Peter Shum, Cheryl Greene, Richard Hedges, and Pat Acton were also helpful in reviewing this article. In addition, Dennis Bockus provided editorial assistance.
(a) C statement
C statement
embedded SQL statement
C statement
embedded SQL statement
C statement
C statement
embedded SQL statement
(b) C statement
C statement
embedded SQL statement
embedded SQL statement
embedded SQL statement
C statement
(c) C statement
C statement
embedded SQL statement
containing
sub-SQL statement
sub-SQL statement
sub-SQL statement
C statement
EXEC SQL BEGIN COMPOUND ATOMIC STATIC STOP AFTER FIRST
:insert_number STATEMENTS
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION )
VALUES ( :name_1, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION )
VALUES ( :name_2, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION )
VALUES ( :name_3, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION )
VALUES ( :name_4, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION )
VALUES ( :name_5, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION )
VALUES ( :name_6, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION )
VALUES ( :name_7, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION )
VALUES ( :name_8, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION )
VALUES ( :name_9, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION )
VALUES ( :name_10, 'New_Hire' );
COMMIT;
END COMPOUND;
(a) C statement C statement embedded SQL statement A embedded SQL statement B embedded SQL statement C C statement C statement C statement embedded SQL statement A embedded SQL statement B embedded SQL statement C C statement (b) C statement C statement call stored procedure 1 C statement C statement C statement call stored procedure 1 C statement (c) embedded SQL statement A embedded SQL statement B embedded SQL statement C
(a) inout_sqlda->sqlvar[0].sqldata = (char *) &nbr_of_managers; ... inout_sqlda->sqlvar[1].sqldata = (char *) &nbr_of_employees; ... inout_sqlda->sqlvar[2].sqldata = (char *) &nbr_on_leave; (b) * (short *) inout_sqlda->sqlvar[0].sqldata = loc_mgr_nbrs; * (short *) inout_sqlda->sqlvar[1].sqldata = loc_emp_nbrs; * (short *) inout_sqlda->sqlvar[2].sqldata = loc_leav_nbrs;
Figure 1: Minimizing client-to-server calls and maximizing transactions at the server.
Name Classification Salary Fermat Retired 70000.00 Watson Manager 90000.00 Brown Maternity_Leave 70000.00 Babbage Designer 80000.00 Brooks Education_Leave 60000.00 Von_Neumann Developer 70000.00 Fonzerello Quit 65000.00 Lovelace Tester 65000.00 Marley Deceased 60000.00 Presley Tech_Writer 60000.00 Fudd Terminated 70000.00 Ricardo New_Hire - Hogan New_Hire - Klink New_Hire - Schultz New_Hire - Kooky New_Hire - Arnold New_Hire - Morris New_Hire - Friday New_Hire - Adams New_Hire -
PESONNEL TABLE Name Classication Salary Watson Manager 90000.00 Babbage Designer 80000.00 Von_Neumann Developer 70000.00 Lovelace Tester 65000.00 Presley Tech_Writer 60000.00 Ricardo New_Hire 60000.00 Hogan New_Hire 60000.00 Klink New_Hire 60000.00 Schultz New_Hire 60000.00 Kooky New_Hire 60000.00 Arnold New_Hire 60000.00 Morris New_Hire 60000.00 Friday New_Hire 60000.00 Adams New_Hire 60000.00 INACTIVE TABLE Name Classication Salary Brown Maternity_Leave 70000.00 Brooks Education_Leave 30000.00 HISTORY TABLE Name Classication Salary Fermat Retired 35000.00 Marley Deceased 0.00 Fonzerello Quit 0.00 Fudd Terminated 0.00
Number of managers: 1 Number of employees: 13 Number on leave: 2
Listing One
Terms of Service | Privacy Statement | Copyright © 2024 UBM Tech, All rights reserved.
/* A program to add names of new hires into a database */
/* using one executable statement, Compound SQL; then call */
/* a stored procedure that reorganizes the personnel data at */
/* the server and returns a count of managers, employees */
/* and those on leave. */
#include <sqlca.h>
#include <sqlda.h>
#include <sqlenv.h>
#include <sql.h>
#include <sqlutil.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
short getname ( char list [][11] );
int err (struct sqlca * );
int main ( void ) {
/* Declare host variables for names of new hires, number of inserted */
/* rows required for new hires' names, and variables to contain a count */
/* of the number of managers, employees, and those on leave. */
/* Also, declare a stored procedure. */
EXEC SQL BEGIN DECLARE SECTION;
/* Names of new hires */
char name_1[20];
char name_2[20];
char name_3[20];
char name_4[20];
char name_5[20];
char name_6[20];
char name_7[20];
char name_8[20];
char name_9[20];
char name_10[20];
/* Number of inserted rows */
short insert_number = 0;
/* Count of managers, employees, those on leave */
short nbr_of_managers = 0;
short nbr_of_employees = 0;
short nbr_on_leave = 0;
/* Indicator variables for counts, to provide attributes of count variables */
short nbr_mgr_ind = 0;
short nbr_emp_ind = 0;
short nbr_on_leave_ind = 0;
/* Stored procedure */
char procname[254];
EXEC SQL END DECLARE SECTION;
/* Declare an array to hold names of new hires */
char name[10][11];
/* Declare the output SQL Descriptor Area (SQLDA) to pass information */
/* between the main program and the stored procedure */
struct sqlda *inout_sqlda = (struct sqlda * ) malloc (SQLDASIZE(3));
/* Declare the SQL Communication Area (SQLCA) for information */
/* on executing SQL statements */
struct sqlca sqlca;
/* Declare an expected return code after processing */
int retcode = 0;
/*--------------------------------------------------*/
/* Set up */
/*--------------------------------------------------*/
/* Get up to 10 names from the user (limit of 20 character length for a name); */
/* return the names and the number of names for insertion */
insert_number = getname ( name );
/* Assign the name of the stored procedure to the stored procedure variable */
strcpy ( procname, "tableorg" );
/* Error handling */
EXEC SQL WHENEVER SQLERROR GO TO ext;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
/* Connect to the database */
EXEC SQL CONNECT TO fuzzysrv IN SHARE MODE;
/* Assign the new hires' names to the host variables */
strcpy ( name_1, name[0] );
strcpy ( name_2, name[1] );
strcpy ( name_3, name[2] );
strcpy ( name_4, name[3] );
strcpy ( name_5, name[4] );
strcpy ( name_6, name[5] );
strcpy ( name_7, name[6] );
strcpy ( name_8, name[7] );
strcpy ( name_9, name[8] );
strcpy ( name_10, name[9] );
/* Initialize the output SQL Descriptor Area (SQLDA) to pass information */
/* between the stored procedure, tableorg, and the main program. */
/* Specifically, the number of managers, active employees, and those */
/* on leave. */
inout_sqlda->sqln = 3;
inout_sqlda->sqld = 3;
inout_sqlda->sqlvar[0].sqltype = SQL_TYP_SMALL;
inout_sqlda->sqlvar[0].sqldata = ( char *) &nbr_of_managers;
inout_sqlda->sqlvar[0].sqllen = sizeof ( short );
inout_sqlda->sqlvar[0].sqlind = (short *) &nbr_mgr_ind;
inout_sqlda->sqlvar[1].sqltype = SQL_TYP_SMALL;
inout_sqlda->sqlvar[1].sqldata = ( char * ) &nbr_of_employees;
inout_sqlda->sqlvar[1].sqllen = sizeof ( short );
inout_sqlda->sqlvar[1].sqlind = (short *) &nbr_emp_ind;
inout_sqlda->sqlvar[2].sqltype = SQL_TYP_SMALL;
inout_sqlda->sqlvar[2].sqldata = ( char * ) &nbr_on_leave;
inout_sqlda->sqlvar[2].sqllen = sizeof ( short );
inout_sqlda->sqlvar[2].sqlind = (short *) &nbr_on_leave_ind;
/*--------------------------------------------------*/
/* Use Compound SQL to insert names */
/*--------------------------------------------------*/
/* Insert the names with a New Hire classification into the PERSONNEL table */
EXEC SQL BEGIN COMPOUND ATOMIC STATIC STOP AFTER FIRST :insert_number STATEMENTS
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION ) VALUES ( :name_1, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION ) VALUES ( :name_2, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION ) VALUES ( :name_3, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION ) VALUES ( :name_4, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION ) VALUES ( :name_5, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION ) VALUES ( :name_6, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION ) VALUES ( :name_7, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION ) VALUES ( :name_8, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION ) VALUES ( :name_9, 'New_Hire' );
INSERT INTO PERSONNEL ( NAME, CLASSIFICATION ) VALUES ( :name_10, 'New_Hire' );
COMMIT;
END COMPOUND;
/*---------------------------------------------------------------------*/
/* Use a stored procedure to update tables and return critical numbers */
/*---------------------------------------------------------------------*/
/* Call the "tableorg" stored procedure that will: update the salaries in the */
/* PERSONNEL table; move on leave employees into the INACTIVE table and */
/* retired, deceased, quit, or terminated employees to the HISTORY table; */
/* and return the number of managers, active employees, and people on leave. */
EXEC SQL CALL :procname USING DESCRIPTOR :*inout_sqlda;
/* Check for errors */
retcode = err ( &sqlca );
/* Print the number of managers, active employees, and people on leave */
/* returned from the stored procedure */
printf ( "\n\nNumber of managers: %d", nbr_of_managers );
printf ( "\nNumber of employees: %d", nbr_of_employees );
printf ( "\nNumber on leave: %d", nbr_on_leave );
/*--------------------------------------------------*/
/* Disconnect from database and check for errors */
/*--------------------------------------------------*/
/* Free allocated memory */
free ( inout_sqlda );
/* Disconnect from database */
EXEC SQL CONNECT RESET;
ext:
retcode = err ( &sqlca );
return retcode;
} /* end main */
/* Check SQLCA for error messages */
int err ( struct sqlca *ca ) {
char buf[512] = "";
int rc = 0;
if ( ca ) {
if ( ca->sqlcode ) { /* Get and print error message */
rc = sqlaintp ( buf, 512, 78, ca );
printf ( "\n%ld %s\n", ca->sqlcode, buf );
}
}
return (rc);
} /* end err */
/* Function to get up to 10 names and put them in an array */
short getname ( char list[][11] ) {
/* Declare counter for the number of names */
short i = 0;
printf ( "Enter up to 10 names. Type 'end' to quit: \n" );
do {
scanf ( "%20s", list[i] );
} while (( strcmp ( list[i++], "end" ) != 0 ) && ( i < 10 ));
strcpy ( list[i], "end" );
i--;
return ( i );
} /* end getname */
Listing Two
/* A stored procedure to update salaries; move on leave employees */
/* to an INACTIVE tables and retired, deceased, quit, or terminated */
/* employees to a HISTORY table; and return to the calling program */
/* the number of managers, active employees, and people on leave */
#include <memory.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include <sqlda.h>
#include <string.h>
SQL_API_RC SQL_API_FN tableorg ( void *reserved1,
void *reserved2,
struct sqlda *inout_sqlda,
struct sqlca *ca )
{
/* Declare a local SQL Communication Area (SQLCA) to capture local */
/* information on executing SQL statements */
EXEC SQL INCLUDE SQLCA;
/* Declare local host variables */
EXEC SQL BEGIN DECLARE SECTION;
short loc_mgr_nbrs = 0;
short loc_emp_nbrs = 0;
short loc_leav_nbrs = 0;
EXEC SQL END DECLARE SECTION;
/* Error handling */
EXEC SQL WHENEVER SQLERROR GO TO error_exit;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
/* Update the salaries in the PERSONNEL table, reducing the salaries */
/* of those retired and on education leave by a half. Set to $0.00 */
/* those who have died, quit, or been terminated. Set to $60000.00 */
/* all those who are classified as new hires. */
/* Copy those on leave into the INACTIVE table, and copy those who */
/* have retired, died, quit or been terminated into the HISTORY table. */
/* Then delete all of the above from the PERSONNEL table, leaving only */
/* active employees and managers. */
/* Count the number of managers, active employees, and those on leave */
EXEC SQL BEGIN COMPOUND ATOMIC STATIC
UPDATE PERSONNEL SET SALARY = SALARY/2
WHERE CLASSIFICATION = 'Retired';
UPDATE PERSONNEL SET SALARY = SALARY/2
WHERE CLASSIFICATION = 'Education_Leave';
UPDATE PERSONNEL SET SALARY = 0.00
WHERE CLASSIFICATION = 'Deceased';
UPDATE PERSONNEL SET SALARY = 0.00
WHERE CLASSIFICATION = 'Quit';
UPDATE PERSONNEL SET SALARY = 0.00
WHERE CLASSIFICATION = 'Terminated';
UPDATE PERSONNEL SET SALARY = 60000.00
WHERE CLASSIFICATION = 'New_Hire';
INSERT INTO INACTIVE SELECT * FROM PERSONNEL
WHERE CLASSIFICATION = 'Maternity_Leave';
INSERT INTO INACTIVE SELECT * FROM PERSONNEL
WHERE CLASSIFICATION = 'Education_Leave';
INSERT INTO HISTORY SELECT * FROM PERSONNEL
WHERE CLASSIFICATION = 'Retired';
INSERT INTO HISTORY SELECT * FROM PERSONNEL
WHERE CLASSIFICATION = 'Deceased';
INSERT INTO HISTORY SELECT * FROM PERSONNEL
WHERE CLASSIFICATION = 'Quit';
INSERT INTO HISTORY SELECT * FROM PERSONNEL
WHERE CLASSIFICATION = 'Terminated';
DELETE FROM PERSONNEL WHERE CLASSIFICATION = 'Retired';
DELETE FROM PERSONNEL WHERE CLASSIFICATION = 'Maternity_Leave';
DELETE FROM PERSONNEL WHERE CLASSIFICATION = 'Education_Leave';
DELETE FROM PERSONNEL WHERE CLASSIFICATION = 'Quit';
DELETE FROM PERSONNEL WHERE CLASSIFICATION = 'Deceased';
DELETE FROM PERSONNEL WHERE CLASSIFICATION = 'Terminated';
SELECT COUNT(*) INTO :loc_mgr_nbrs FROM PERSONNEL
WHERE CLASSIFICATION = 'Manager';
SELECT COUNT(*) INTO :loc_emp_nbrs FROM PERSONNEL
WHERE CLASSIFICATION <> 'Manager';
SELECT COUNT(*) INTO :loc_leav_nbrs FROM INACTIVE;
COMMIT;
END COMPOUND;
/* Assign values from the stored procedure's local host variables */
/* to the output SQLDA structure in order to pass back the count of */
/* the managers, employees, and those on leave to the main program. */
* ( short * ) inout_sqlda->sqlvar[0].sqldata = loc_mgr_nbrs;
* ( short * ) inout_sqlda->sqlvar[1].sqldata = loc_emp_nbrs;
* ( short * ) inout_sqlda->sqlvar[2].sqldata = loc_leav_nbrs;
/*------------------------------------------------*/
/* Return to the main program */
/*------------------------------------------------*/
/* Return the SQLCA to the main program and disconnect */
memcpy ( ca, &sqlca, sizeof ( struct sqlca ) );
return ( SQLZ_DISCONNECT_PROC);
/* If an error has occurred, roll back any changes and return */
/* to the main program */
error_exit:
memcpy ( ca, &sqlca, sizeof ( struct sqlca ) );
EXEC SQL ROLLBACK;
return (SQLZ_DISCONNECT_PROC);
} /* end tableorg stored procedure */