CODEWITHSUNDEEP
sqldb2
Erlend Aune
Erlend Aune

Reputation: 143

How do I create a query that does not cause DB2 to hang?

I have a DB2 database with three tables, A, B and C.

The database was created thus:

create database DB alias DB AUTOMATIC STORAGE YES ON /home/db2inst1 using codedeset UTF-8 territory en PAGESIZE 32768

All tables have the column PID

I need a table which aggregates some info in Table B and C. The query to select the appropriate items is:

SELECT
    T.*,
    (
        SELECT 
            COALESCE(SUM(ROW_COUNT),0)
        FROM 
            C as ITS,
            B as ITL
        WHERE 
            ITS.ROLE = 1
            AND ITS.PID = ITL.PID
            AND ITS.PID_S = ITL.C_SOURCE
            AND ITS.PID = T.PID
    ) AS RR
FROM 
    A as T;

When this query is run, the DB2 server quickly uses about 3Gb memory. Using top, the CPU usage, however, rarely goes beyond 5%, with some jumps into about 13%. The DB2 server is a RedHat6.2 VM, with 4 cores and 2Ghz per core.

I have let this query run for 24 hours without anything seeming to happen. Other queries, like simple selects and many more, work smoothly.

Questions:

  1. Do you have any suggestions for a different, more efficient, query that might accomplish the same thing?
  2. Is it possible that this performance issue has something to do with the configuration of the database?

Upvotes: 4

Views: 579

Answers (2)

M.Oukhouya
M.Oukhouya

Reputation: 21

You use a nested selection, I propose to call your table A inside the second query and leave the join condition on the table A wit the table C.

I think it could optimize the response time of your query but it all depends on the creation of the tables (Declaration of indexes for example).

Best regards,

Upvotes: 1

Peter Miehle
Peter Miehle

Reputation: 6070

i would try the "explain" feature, to see, what db2 is making out of your query

db2exfmt -d database -e schema -t -v % -w -1 -s % -# 0 -n % -g OTIC

Upvotes: 3

Related Questions