Optimization of INSERT in DB2 (1)

Many people are thinking about how to improve the INSERT efficiency in DB2 databases. This article will introduce you to the INSERT Process and corresponding optimization methods in DB2 databases for your reference, I hope to enlighten you.

Statement preparation

Dynamic

For example, in CLP or in one CLI SQLExecDirect call)
Explicitly, for example, using an SQL Prepare, CLI SQLPrepare, or JDBC prepareStatement Statement)
Static one-time Compilation

Send column value to server

The VALUES clause contains multi-row content. The sample program sqllib/samples/cli/tbload. c
Use batch processing in JDBC
Load is used to quickly load data into a staging table, and then INSERT... SELECT is used to fill the main table.
Combining Multiple statements into one statement can be achieved through Compound SQL ??

Allow the client to use the same code page as the database to be accessed
Reduce automatic data type conversion
Minimize the insert-related configuration overhead in the application ??

Find the location assignment and selection page of the storage row)

DB2 uses one of the three algorithms to determine where rows are inserted. If Multi-dimensional cluster Multi-dimen1_clustering, MDC)

The default mode is that DB2 searches for Free Space Control Records (Free Space Control Records, FSCR) distributed across all pages of the table to find pages with sufficient Free Space to store new rows. DB2 provides the DB2MAXFSCRSEARCH registry variable, allowing you to limit the search range to less than the default 5 pages
When a TABLE is placed in APPEND mode by using alter table, the second algorithm is used. This completely avoids FSCR search, because you only need to simply put rows directly at the end of the table.
When the table has a cluster index clustering index), the last algorithm is used. In this case, DB2 tries to insert each row into a page with similar key values. If there is no space on that page, DB2 will try a nearby page. If there is no space on the nearby page, DB2 will perform a FSCR search.

If you only want to optimize the insert time, using the APPEND mode is the fastest way to batch insert. However, this method is far less effective than many other methods discussed here. The second best method is to use the default algorithm. However, in the best environment, changing the value of DB2MAXFSCRSEARCH has little impact, but in an environment with few I/O constraints, the impact of such changes is considerable.