Power Programming Point: Cobol/DB2 - A Powerful Duo

When you integrate COBOL and DB2 in a mainframe environment you can certainly expect for a very efficient acess to your data whether your SQL access is direct or not. To improve your results you can count on DB2 tools. They allow you to analyze and improve your queries.

What happens when you have DB2 in your COBOL program?
When you compile a COBOL program with DB2, the SQL commands embedded in your code will suffer a binding process. When a bind occurs DB2 creates an access path for that SQL Command. DB2 optimizer helps in creating this access path using statistics generated by the Runstas tool based on tables sizes, columns cardinalities, and indexes clustering.

The access path is defined in a way that efficiency is improved; however, if the table or the SQL commands are poorly defined, the result will be worse than expected. We should use DB2 explain tool to create a report showing how data will be accessed and, after analyzing it, improve the SQL, or the table when possible, to obtain a faster access to data.

The main point here is that, sometimes, your data will not be accessed as you thought. Sometimes, if you are acessing part of a key restraining the fields not with an igual condition, optimizer may decide that the most efficient way to obtain your data is using a table scan i.e. scanning the entire table.

To finalize, I suggest you analyze the Explain result after compiling/binding your program. This way you will know how the data will be accessed and will have more control over the results and your program's efficiency. I'll talk about Explain later.

Power Programming Point