Geek Answers Handbook

How to create a query that does not cause DB2 to freeze?

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

The database was created in this way:

create a database database alias DB AUTOMATIC STORAGE YES ON / home / db2inst1 using the UTF-8 encoded zone en PAGESIZE 32768

  • Table A is 28 columns wide by 1.8 millimeters. strings and PID are primary key. Columns mostly have int types, but some of them are varchar (200-400). Index: PID
  • Table B has a width of 7 columns with 14 millimeters. rows and primary key PID_L. It also has columns C_SOURCE and ROW_COUNT. Index: PID, C_SOURCE
  • Table C has a width of 20 columns with 14 millimeters. rows and primary key PID_S. It also has a ROLE column. Index: PID, PID_S

All tables have a PID column.

I need a table that aggregates some information in table B and C. A query to select the appropriate items:

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 started, the DB2 server quickly uses about 3 GB of memory. Using from above, using CPUs, however, rarely goes beyond 5%, with some jumps amounting to about 13%. The DB2 server is a RedHat6.2 VM, with 4 cores and 2 GHz per core.

I allowed this request to work 24 hours without any changes. Other queries, such as simple selections and many others, work smoothly.

Questions:

  • Do you have suggestions for another, more efficient query that can do the same thing?
  • Is it possible that this performance issue is related to database configuration?
+4
source share
2 answers

I would try the "explain" function to see what db2 is doing from your request

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

+3
source

You are using a nested selection, I suggest calling your table A inside the second query and leaving the join condition in table A with table C.

I think this can optimize the response time of your request, but it all depends on the creation of the tables (e.g. index declaration).

Yours faithfully,

+1
source

More articles:


All Articles