Why Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
Troubleshooting
Research
Professional Opinions
Ask a QuestionDid You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
troubleshooting Question
Load on DB2
In my application is in executing state for quite some time, which has increased load on DB. I got a snapshot for that application
below query is requiring a lot of CPU .Can I see how to optimize this query?
SELECT IMCVEHICLES.VID, YEAR, MAKE_CODE, MAKE_DESCRIPTION, MODEL_CODE, MODEL_DESCRIPTION, SUBMODEL_DESCRIPTION,ENGIN
I tried putting indexes on VID1,&VID7 didnt help.
I am attaching my snapshot.
below query is requiring a lot of CPU .Can I see how to optimize this query?
SELECT IMCVEHICLES.VID, YEAR, MAKE_CODE, MAKE_DESCRIPTION, MODEL_CODE, MODEL_DESCRIPTION, SUBMODEL_DESCRIPTION,ENGIN
I tried putting indexes on VID1,&VID7 didnt help.
I am attaching my snapshot.
Application Snapshot
Application handle = 984
Application status = UOW Executing
Status change time = Not Collected
Application code page = 1208
Application country/region code = 1
DUOW correlation token = GAFA0517.D8B0.0191A1174849
Application name = java
Application ID = GAFA0517.D8B0.0191A1174849
Sequence number = 4844
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =
Connection request start timestamp = 03/11/2009 10:48:49.999422
Connect request completion timestamp = 03/11/2009 10:48:49.999621
Application idle time =
CONNECT Authorization ID = IMCCRP
Client login ID = wasadmin
Configuration NNAME of client = usamt12ux13app
Client database manager product ID = SQL08028
Process ID of client application = 11642
Platform of client application = LINUX
Communication protocol of client = TCP/IP
Inbound communication address = 10.250.5.23 55472
Database name = RPRODIMC
Database path = /media/SAN/disk1/db2inst1/db2inst1/NODE0000/SQL00005/
Client database alias = PRODIMC
Input database alias =
Last reset timestamp =
Snapshot timestamp = 03/11/2009 11:18:03.340926
The highest authority level granted =
Direct DBADM authority
Direct CREATETAB authority
Direct BINDADD authority
Direct CONNECT authority
Direct CREATE_NOT_FENC authority
Direct LOAD authority
Direct IMPLICIT_SCHEMA authority
Direct CREATE_EXT_RT authority
Direct QUIESCE_CONN authority
Indirect CREATETAB authority
Indirect BINDADD authority
Indirect CONNECT authority
Indirect IMPLICIT_SCHEMA authority
Coordinating database partition number = 0
Current database partition number = 0
Coordinator agent process or thread ID = 17995
Agents stolen = 0
Agents waiting on locks = 0
Maximum associated agents = 1
Priority at which application agents work = 0
Priority type = Dynamic
Lock timeout (seconds) = 600
Locks held by application = 6
Lock waits since connect = 2
Time application waited on locks (ms) = 118
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = Not Collected
Total sorts = 32871
Total sort time (ms) = 195447
Total sort overflows = 23023
Data pages copied to extended storage = Not Collected
Index pages copied to extended storage = Not Collected
Data pages copied from extended storage = Not Collected
Index pages copied from extended storage = Not Collected
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Buffer pool data writes = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index physical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Buffer pool index writes = Not Collected
Total buffer pool read time (milliseconds) = Not Collected
Total buffer pool write time (milliseconds)= Not Collected
Time waited for prefetch (ms) = Not Collected
Unread prefetch pages = Not Collected
Direct reads = Not Collected
Direct writes = Not Collected
Direct read requests = Not Collected
Direct write requests = Not Collected
Direct reads elapsed time (ms) = Not Collected
Direct write elapsed time (ms) = Not Collected
Number of SQL requests since last commit = 13
Commit statements = 4843
Rollback statements = 0
Dynamic SQL statements attempted = 14429
Static SQL statements attempted = 4843
Failed statement operations = 250
Select SQL statements executed = 10301
Update/Insert/Delete statements executed = 586
DDL statements executed = 0
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 12
Rows inserted = 359
Rows updated = 193
Rows selected = 18834
Rows read = 132554884
Rows written = 65974899
UOW log space used (Bytes) = Not Collected
Previous UOW completion timestamp = Not Collected
Elapsed time of last completed uow (sec.ms)= Not Collected
UOW start timestamp = Not Collected
UOW stop timestamp = Not Collected
UOW completion status = Not Collected
Open remote cursors = 1
Open remote cursors with blocking = 229
Rejected Block Remote Cursor requests = 381
Accepted Block Remote Cursor requests = 9920
Open local cursors = 0
Open local cursors with blocking = 0
Total User CPU Time used by agent (s) = 314.490000
Total System CPU Time used by agent (s) = 343.250000
Host execution elapsed time = 0.000039
Package cache lookups = 10372
Package cache inserts = 10
Application section lookups = 14429
Application section inserts = 291
Catalog cache lookups = 68
Catalog cache inserts = 0
Catalog cache overflows = 0
Catalog cache high water mark = 0
Workspace Information
Shared high water mark = 0
Total shared overflows = 0
Total shared section inserts = 0
Total shared section lookups = 0
Private high water mark = 3131289
Total private overflows = 0
Total private section inserts = 291
Total private section lookups = 10349
Most recent operation = Fetch
Cursor name = SQL_CURSH200C45
Most recent operation start timestamp = 03/11/2009 11:07:09.301911
Most recent operation stop timestamp =
Agents associated with the application = 1
Number of hash joins = 188
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
Statement type = Dynamic SQL Statement
Statement = Fetch
Section number = 45
Application creator = NULLID
Package name = SYSSH200
Consistency Token = SYSLVL01
Package Version ID =
Cursor name = SQL_CURSH200C45
Statement database partition number = 0
Statement start timestamp = 03/11/2009 11:07:09.301911
Statement stop timestamp =
Elapsed time of last completed stmt(sec.ms)= 0.000039
Total Statement user CPU time = 311.150000
Total Statement system CPU time = 340.690000
SQL compiler cost estimate in timerons = 36689
SQL compiler cardinality estimate = 881
Degree of parallelism requested = 1
Number of agents working on statement = 1
Number of subagents created for statement = 1
Statement sorts = 23009
Total sort time = 194497
Sort overflows = 23009
Rows read = 131960949
Rows written = 65969670
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Blocking cursor = YES
Dynamic SQL statement text:
SELECT IMCVEHICLES.VID, YEAR, MAKE_CODE, MAKE_DESCRIPTION, MODEL_CODE, MODEL_DESCRIPTION, SUBMODEL_DESCRIPTION,ENGINE_TYPE, LITER, FUEL, FUEL_DELIVERY, ASPIRATION FROM IMCVEHICLES, IMCVIN WHERE IMCVIN.VIN LIKE ? AND (IMCVEHICLES.VID = IMCVIN.VID1 OR IMCVEHICLES.VID = IMCVIN.VID2 OR IMCVEHICLES.VID = IMCVIN.VID3 OR IMCVEHICLES.VID = IMCVIN.VID4 OR IMCVEHICLES.VID = IMCVIN.VID5 OR IMCVEHICLES.VID = IMCVIN.VID6 OR IMCVEHICLES.VID = IMCVIN.VID7) AND DISPLAYABLE_FLAG = 1 ORDER BY MODEL_DESCRIPTION, LITER, ENGINE_TYPE, FUEL, FUEL_DELIVERY, ASPIRATION FOR FETCH ONLY
Agent process/thread ID = 17995
Agent process/thread ID = 17995
Agent Lock timeout (seconds) = 600
Memory usage for agent:
Memory Pool Type = Application Control Heap
Current size (bytes) = 32768
High water mark (bytes) = 49152
Configured size (bytes) = 33734656
Memory Pool Type = Application Heap
Current size (bytes) = 3768320
High water mark (bytes) = 3899392
Configured size (bytes) = 8617984
Our community of experts have been thoroughly vetted for their expertise and industry experience.