Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Making a record insert with SQL

Making a record insert with SQL

When making a record insert with SQL (Insert into values) from the JDBC Toolbox driver, Visual Explain and Optimizer messages shows a table scan with reason T1(no index) for the access plan. The table has a primary key index (dspdbr), 50 active records 180.000 deleted. System version is V5R3. Why does DB2 make table scans and shows this reason code?
This is a case where the system feedback is not completely accurate. The DB2 Engine never uses an index on a simple Insert (INSERT INTO t1 VALUES(33)), it simply looks at some internal structures within the target table to determine the next available slot for a new row. DB2 describes this as a table scan even though the table is not being scanned since the table scan icon/message is the closest description of this internal processing.

If you're worried about the performance of this SQL Insert, I'd suggest analyzing if the Insert is being done as a Full Open or Reusable Open.


Visit the ITKnowledge Exchange and get answers to your DB2 questions fast.

Check out this Search400.com Featured Topic: Expert advice on DB2

Search400.com's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: Tips, tutorials and more.

Dig Deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.