June 09, 2009

SQL Programming Guidelines for COBOL

For years, reviewing and fixing SQL performance problems was a big part of my job, and I remain in the habit of looking for ways to help people write efficient SQL. In my experience, developers who not only know DB2 for z/OS SQL but know how to tune the SQL are nearly impossible to find. People tend to have one skill, but not the other.

Because development team members don't have time to tune SQL, this task typically falls on the DBA. To make the job easier, DBAs like to come up with SQL coding guidelines to help prevent performance problems.

I've written a few blog entries on this topic, including "How to Write Efficient SQL" and "How to Write Efficient SQL (Part 2)."

One thing I didn't include in the series was guidelines to follow when imbedding SQL in a COBOL program. However, this topic recently came up on the DB2-L listserve. Cuneyt Goksu requested tips and guidelines to be used by developers who need to port an application to COBOL with DB2 for z/OS. COBOL developers haven't developed with DB2 for z/OS, but they know that the DB2 community must have some guidelines or best practices to avoid potential problems.

Cuneyt gathered all of the tips provided by the DB2-L community and compiled them in a single post, "SQL Programming Guidelines." This blog entry has good information for both DB2 novices and experts. Since developers are busy, they may not go back and review what's new in DB2 and how it fits into the guidelines. (A good example: USE the QUERYNO-Clause for every SQL. It can be used to facilitate comparing access paths between different versions of the program.)

If you have a good SQL programming tip, please post it in Comments.