How to write Stored Procedure in DB2: Unique Ideas

A stored procedure is a program in DB2, and you can add logic and run it by assigning it to a job whenever you need it.

“Happiness is not something ready-made. It comes from your own actions.”
― Dalai Lama XIV

Ideas to write a high-performing stored procedure

Code Readability

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

Code Size

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


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


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


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


  • Use COMMIT and ROLLBACK explicitly

Related Posts


Author: Srini

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

Start Discussion

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.