• Improved code quality
• Reduced debugging time
• Clear function understanding
• Improved performance
• Improved testing.
One example of the use of Assembler macros is the development of the most complex air traffic control system in the world for the National Airspace System (NAS). Developed in the late ’60s, NAS went into production in 1971. It still controls most of the high altitude air traffic across the U.S., as was evident on 9/11.
High-quality code conversion can be generated automatically by recognizing code patterns in one language and translating them to another language. Developing repeatable processing code and using code pattern techniques provide the best quality of consistent code. In order to develop the best quality of SQL PL code, provide your best people with the best training available.
IBM developed SQL PL as a set of commands that extend the use of SQL in the IBM DB2 database system. It provides procedural programmability in addition to the querying commands of SQL.
A native SQL procedure is a procedure whose body is written entirely in SQL. The body is written in the SQL procedural language. A native SQL procedure is created by issuing a single SQL statement, CREATE PROCEDURE. Native SQL procedures don’t require any other program preparation, such as precompiling, compiling or link-editing source code. Native SQL procedures are executed as SQL statements that are bound in a DB2 package. Native SQL procedures don’t have an associated external application program. The following statements represent the type of statements that can be included in a DB2 native SQL stored procedure body:
• Most SQL statements can be coded in an SQL stored procedure. Some SQL statements are valid in a compound statement but aren’t valid if the SQL is the only statement in the procedure body.
• Assignment statements, such as SET, SELECT INTO and FETCH, can be used to assign values to output parameters or SQL variables. An SQL variable is defined and used only within the body of an SQL stored procedure.
• CASE statements can be used to select an execution path, or even a specific cursor to be opened for processing, based on the evaluation of one or more conditions.
• IF statements can be used just like the CASE statement.
• The GOTO and LEAVE statements can be used to skip over sections of SQL and transfer program control out of a loop or a block of code.
• The LOOP, FOR and WHILE statements execute a single statement or a group of statements multiple times. They each have unique features that can determine how they will perform. The RETURN statement can be used to return a status in the form of an integer value to the invoking application.
• The CALL statement can be used to call another SQL stored procedure.
• Compound statements can be coded that contain one or more of any of the other SQL procedures language statements. Additionally, compound statements can contain SQL variable declarations, condition handlers and cursor declarations. Compound statements can be nested.
When coding compound statements, you must code the component statements in the following specific order:
1. SQL variable
2. Condition declarations
3. Cursor declarations
4. Handler declarations
5. Procedure body statements (CASE, IF, LOOP, REPEAT, WHILE and other SQL statements).
Additional details can be found in Craig Mullins’ book DB2 Developer's Guide: A Solutions-Oriented Approach to Learning the Foundation and Capabilities of DB2 for z/OS (6th Edition).
The Benefits of Procedural SQL
The procedural flow control statements are among the most useful procedural extension to SQL. The flow of procedural SQL is controlled by the typical programming constructs you can add along with standard SQL statements. These typical constructs enable programmers to: