Optimization of INSERT in DB2 (1)

Source: Internet
Author: User

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.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.