How to Optimize COBOL-DB2 Program: 10 Killer Tips

1. The SQLCODE must be checked after every SQL statement. The SQL for beginners is highly useful course, if you would like to know more.
2. Every program must include SQLCA(only once), and DCLGEN for each table
3. Every program must have abend routine. In this routine make sure to use,SQLCODE and SQLSTATE to display
4. Never code SELECT * in program. It takes unnecessary I/O, and in turn more CPU usage
5. Make sure all Nullable columns must use Indicator variable along with host variable
6. Better use scalar functions, when you expect nulls – as below example
7. For example:

   SELECT IFNULL(AVG(SALARY), 0)    
   FROM EMP    
   WHERE WORKDEPT = 'XYZ'

8. Minimize the no of times Opens or Close a cursor
9. During SELECT,INSERT and UPDATE always use counters in Program. When problem comes, we can easily trace back to root.
10. SQLERRD(3), it will be updated, when INSERT,UPDATE and DELETE takes place. THIS IS NOT POPULATED WHEN MASS DELETES OCCURRED WITHOUT WHERE CLAUSE. For example deletes due to DELETE CASCADE.

---

More resources to read

• Decoding data mining and ETL with MS-Excel 2013