large tables 1

[lpace]

Hi,
I've got a table with 31865 rows of data. When I do a simple select (retrieve 4 rows) from this table it takes 4 seconds to process the transaction. What can I do with this table to speed up this process as the table will get larger over time.
Thanks

 

[Pikkunero]

Check that you indexes are up to date.

And if you haven't already, create a new index into that table of yours that matches the columns in you SQL's WHERE-clause.

Example:

SELECT * FROM SOME_TABLE WHERE COLUMN2 = 'DEF1' AND COLUMN5 = 'DEF9'

In this case you should have created an index like this:

CREATE INDEX SOME_TABLE_IND ON SOME_TABLE (COLUMN2, COLUMN5)

It should speed up the query.

 

[sweetleaf]

I have a similar question but for table inserts rather than for selects.

In oracle you can PARALLELIZE your tables to a specified DEGREE/factor at design time (eg. if you specify DEGREE of 4 then 4 cpu's will be allocated to processing bulk inserts into this table). Is there such an equivalent within db2?

Also are there any HINTS or DIRECTIVES that can speed up reads/writes into db2 tables? Sorry to keep using oracle as an example, but in oracle when inserting you can say:

INSERT /*+ APPEND*/ INTO DIM_CUSTOMER_STG
SELECT * FROM CUSTOMERS ..

APPEND bypasses rollback/redo buffers which speeds up large table inserts operations.

Are there such equivalents in db2?

Many thanks,
Mark

 

[gregsimpson]

Mark,

max_querydegree ANY, 1 - 32,767

Default: ANY Specifies the maximum degree of parallelism that is to be used for any SQL statement executing on this instance of the DB2 Database Manager. For a multimode system, this parameter applies to the degree of parallelism used within a single node

From your description of APPEND, I believe this to be similar to db2's "not logged initially" parameter.

If you're doing lots of writes to a table you may find the "append on" option gives you a significant boost in throughput.

Cheers
Greg

 

[lpace]

thanks Pikkunero, that worked!

 

[sweetleaf]

Thanks Greg!