General SQL Coding Guidelines

This final section on SQL guidelines contains advice for creating understandable and easily maintained SQL. When developing an application, you might be tempted to "let it be if it works." This advice is not good. You should strive for well-documented, structured code. The following miscellaneous guidelines will help you achieve that goal with your SQL statements.

Code SQL Statements in Block Style

You should code all SQL in block style. This standard should apply to all SQL code, whether embedded in a COBOL program, coded as a QMF query, or implemented using another tool. Use the following examples as standard templates for the SELECT , INSERT , UPDATE , and DELETE statements.

The following is the SELECT statement:

 EXEC SQL     SELECT   EMPNO, FIRSTNME, MIDINIT, LASTNAME              WORKDEPT, PHONENO, EDLEVEL     FROM     EMP     WHERE    BONUS = 0     OR       SALARY < 10000     OR       (BONUS < 500     AND       SALARY > 20000)     OR       EMPNO IN ('000340', '000300', '000010')     ORDER BY EMPNO, LASTNAME END-EXEC. 

The following is the INSERT statement:

 EXEC SQL     INSERT     INTO DEPT         (DEPTNO,          DEPTNAME,          MGRNO,          ADMRDEPT         )     VALUES         (:HOSTVAR-DEPTNO,          :HOSTVAR-DEPTNAME,          :HOSTVAR-MGRNO:NULLVAR-MGRNO,          :HOSTVAR-ADMRDEPT         ) END-EXEC. 

The following is the DELETE statement:

 EXEC SQL     DELETE     FROM    DEPT     WHERE   DEPTNO = 'E21' END-EXEC. 

The following is the UPDATE statement:

 EXEC SQL     UPDATE EMP     SET    JOB = 'MANAGER',            EDLEVEL = :HOSTVAR-EDLEVEL,            COMM = NULL,            SALARY = :HOSTVAR-SALARY:NULLVAR-SALARY,            BONUS = 1000     WHERE  EMPNO = '000220' END-EXEC. 

These examples demonstrate the following rules:

• Code keywords such as SELECT , WHERE , FROM , and ORDER BY so that they are easily recognizable and begin at the far left of a new line.

• For SQL embedded in a host program, code the EXEC SQL and END-EXEC clauses on separate lines.

• Use parentheses where appropriate to clarify the intent of the SQL statement.

• Use indentation to show the levels in the WHERE clause.

These examples are embedded SQL syntax, because this shows more detail for coding in the block style. You can easily convert these examples to interactive SQL by removing the EXEC SQL , END_EXEC , and host variable references.

Comment All SQL Liberally

Comment ad hoc SQL statements using SQL comment syntax. Comment all embedded SQL statements using the syntax of the host language. Code all comments above the SQL statement. Specify the reason for the SQL and the predicted results.

Consider Using SQL Assist

DB2 V7 introduced SQL Assist, a new feature that can aid application developers in coding SQL statements. Consider using SQL Assist to ensure that your SQL statements are syntactically correct. SQL Assist does not provide SQL performance advice, but it is useful for promoting a standard SQL format within your organization ”such as just discussed in the previous guideline.