How to Write Stored Procedure in DB2

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.

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

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 use SQLSTATE to handle it
• Don’t use SQLCODE

COMMIT and ROLLBACK

• Use COMMIT and ROLLBACK explicitly

Related

• How to schedule a job in DB2