DB2 Stored Procedure Useful Guidelines

Here’re guideline to write DB2 Stored procedures. You can use these for other RDBMS also.

The purpose is easy maintenance. Also, improves readability.

Guidelines

Code Readability

  • Avoid GO TO statements
  • Use indentation while writing loops
  • Use meaningful Lables

Code Size

  • If Triggers or Function is too large, you can convert it to a procedure.
  • If SQL procedure is too small, you can convert it to a function

Grouping

  • Group all the procedures of the same application under one schema
  • For instance – payroll.add_employee/payroll.remove_employee

Names

  • Use proper names while CREATE, DELETE and UPDATE of stored procedures

Exceptions

  • Always us SQLSTATE to handle it
  • Don’t use SQLCODE

COMMIT and ROLLBACK

  • Use COMMIT and ROLLBACK explicitly

Related Posts

Get new posts delivered to your inbox.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.