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
- Avoid GO TO statements
- Use indentation while writing loops
- Use meaningful Labels
- 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
COMMIT and ROLLBACK
- Use COMMIT and ROLLBACK explicitly