Getting Started › Procedures and Triggers › Overview › Thin Client/Fat Server

Thin Client/Fat Server

You can write External Procedures in LE-conforming Assembler, C, PL/I, or COBOL as standard executing programs. These programs accept a list of input variables (SQL columns), perform any number of program and/or database functions, then return a list of output variables (SQL columns). This functionality allows you to create procedures that can combine a wide variety of program functions into one SQL call.

For example: A client/server application currently processes a customer order by issuing multiple SQL statements to the database region to:

• Verify the customer number
• Obtain the customer's credit limit/availability
• Validate that the ordered item(s) is in stock
• Validate that the customer has enough credit to order the items
• Enter the order in the system

With a External Procedure in SQL, the client/server application can issue a single SQL call to the External Procedure with the appropriate information (Customer number, Item number(s), Quantity). The database server (Multi-User Facility) loads the appropriate LE-conforming program as a subtask in the Multi-User Facility address space, passes over the input variables, and waits to receive the return information. On completion, the LE-conforming program hands back the output variables to the Multi-User Facility, then the Multi-User Facility returns them to the calling program and terminates the Multi-User Facility subtask. If the program fails, the subtask in the Multi-User Facility address space goes away, and the client SQL call receives an SQL return code. If the program completes but does not provide the expected number of return variables, a different SQL code is returned.