Updating tables within cursor loop
Although we won't be examining complete details on how to implement the techniques, this information is available in the DB2 manuals unless otherwise indicated. Let's start by taking a simplified look at the processing steps for an insert of a single row.
When the statement is compiled, its access plan is stored in the package cache.
Being an SQL statement, an INSERT statement must be compiled by DB2 before it's executed.
This can take place automatically (e.g., in CLP, or a CLI SQLExec Direct call), or explicitly (e.g., through an SQL Prepare statement, CLI SQLPrepare, or JDBC prepare Statement).
It is highly desirable to replace a repeated statement such as: Comparing test 1 with test 2, and 61-64 with 65-68, we see that the use of parameter markers can make a series of inserts run several times faster.
(Using host variables in a static SQL program would have a similar benefit.)There are several different optimization techniques that fall into this category.