I'm having a problem with a query that runs for 24 hrs when submitted by our web app. Our app is on a distributed server and uses hibernate 3.1.3 & batch processing to submit queries to a remote DB2 V7 database on z/OS. When I run the query manually on either box, it ends quickly, returning 0 rows (as it should). When submitted by the web app, I can see that the query is running in DB2 and eating up an incredible amount of resources.
I've been investigating this for 3 days...Have worked with a DBA and used various DB2 tools to help determine what the problem is. Still, all I know for sure is that the query is fine when run manually and NOT fine when submitted by the app. At this point, I'm wondering if it may be a bug between hibernate and z/OS...?
Has anyone seen anything like this before? Any help would be MUCH appreciated!
DB2 Performance Monitor on z/OS: Notice that 99% of the time it's been running has been spent outside DB2.
Code:
Thread Times
Command ===> ________________________________________________________
More: +
Class 1 Class 2
In Appl In DB2 Outside DB2
Elapsed time . . . . . . : 3:53:46.7843 10.258418 3:53:36.5259
CPU time . . . . . . . . : 0.572802 0.570212 0.002590
TCB . . . . . . . . . : 0.572802 0.570212 0.002590
TCB - Stored Proc . . : 0.000000 0.000000
Parallel tasks . . . . : 0.000000 0.000000
Waiting time . . . . . . : N/A 9.688206
Suspension time . . . . : N/A 1:27.958498
TCB . . . . . . . . . : N/A 1:27.958498
Parallel tasks . . . . : N/A 0.000000
Not accounted . . . . . : N/A N/C
Time Event
Suspensions (Class 3) . . . . . . . . . : 1:27.958498 19327
Locks and latches . . . . . . . . . . : 0.998666 909
Synchronous I/O . . . . . . . . . . . : 1:18.114394 17609
DB2 Query Monitor on z/OS: GETPAGES for the fetch is the only number that has increased since the query's been running and it's ridiculously high!
Code:
2008/04/24 14:26:04 ------- Call Level Statistics ------ Row 1 of 5
Option ===> ________________________________________________ Scroll ===> PAGE
DB2 SSID: DSNT Plan: DISTSERV DBRM: SYSSH200 Coll: NULLID
Cursor: SQL_CURSH200C9 Section: 9
Filters Enabled: N
C:B-Buffers,C-SQLCA,D-Delays,L-Locks,H-Host Variables,S-Call Text
Q-Misc Stats
------------------------------------------------------------------------ <
CMD STMT# GETPAGES SQLCODE SQL Calls Call Type
- ------ -------------- ------- ----------- ------------------------
_ 0 175,025,256 0 1 FETCH
_ 0 38,070 0 1 OPEN
_ 0 0 0 2 DESCRIBE INPUT
_ 0 0 0 1 DESCRIBE
_ 0 123 0 1 PREPARE
Query:
Code:
select
hardwarere0_.HARDWARE_RECON_ID as HARDWARE1_9_0_,
physicalin1_.PHYSICAL_INVENTORY_ID as PHYSICAL1_10_1_,
hardwareba2_.HARDWARE_BASELINE_ID as HARDWARE1_8_2_,
hardwarere0_.RECON_ID as RECON2_9_0_,
hardwarere0_.HARDWARE_BASELINE_ID as HARDWARE3_9_0_,
hardwarere0_.PHYSICAL_INVENTORY_ID as PHYSICAL4_9_0_,
hardwarere0_.MATCH_TYPE_ID as MATCH5_9_0_,
hardwarere0_.REQUESTOR as REQUESTOR9_0_,
hardwarere0_.REMOTE_USER as REMOTE7_9_0_,
hardwarere0_.RECORD_TIME as RECORD8_9_0_,
hardwarere0_.STATUS as STATUS9_0_,
physicalin1_.RECON_ID as RECON2_10_1_,
physicalin1_.ASSET_TAG as ASSET3_10_1_,
physicalin1_.SERIAL_NUMBER as SERIAL4_10_1_,
physicalin1_.MACHINE_TYPE as MACHINE5_10_1_,
physicalin1_.MACHINE_MODEL as MACHINE6_10_1_,
physicalin1_.ASSET_TYPE as ASSET7_10_1_,
physicalin1_.ADDRESS as ADDRESS10_1_,
physicalin1_.CITY as CITY10_1_,
physicalin1_.STATE as STATE10_1_,
physicalin1_.COUNTRY as COUNTRY10_1_,
physicalin1_.CATEGORY as CATEGORY10_1_,
physicalin1_.SCANNER_INVENTORY_DATE as SCANNER13_10_1_,
physicalin1_.SCANNER_INVENTORY_ID as SCANNER14_10_1_,
physicalin1_.OWNER as OWNER10_1_,
physicalin1_.BUILDING as BUILDING10_1_,
physicalin1_.FLOOR as FLOOR10_1_,
physicalin1_.ROOM as ROOM10_1_,
physicalin1_.GRID as GRID10_1_,
physicalin1_.COMP_NAME as COMP20_10_1_,
physicalin1_.ORGANIZATION as ORGANIZ21_10_1_,
physicalin1_.OWNERSHIP as OWNERSHIP10_1_,
physicalin1_.OLD_TAG as OLD23_10_1_,
physicalin1_.FACE_DESC as FACE24_10_1_,
physicalin1_.TIV_INSTALL as TIV25_10_1_,
physicalin1_.TIV_MIF as TIV26_10_1_,
physicalin1_.TIV_STATUS as TIV27_10_1_,
physicalin1_.USER_ID as USER28_10_1_,
physicalin1_.OPER_SYS as OPER29_10_1_,
physicalin1_.PHONE_NUM as PHONE30_10_1_,
physicalin1_.SCANNER_COMMENT as SCANNER31_10_1_,
physicalin1_.UNMATCHED_CATEGORY as UNMATCHED32_10_1_,
physicalin1_.UNMATCHED_CATEGORY_COMMENT as UNMATCHED33_10_1_,
physicalin1_.REMOTE_USER as REMOTE34_10_1_,
physicalin1_.RECORD_TIME as RECORD35_10_1_,
physicalin1_.STATUS as STATUS10_1_,
physicalin1_.ASSET_STATUS as ASSET37_10_1_,
physicalin1_.RACK as RACK10_1_,
physicalin1_.IP_ADDRESS as IP39_10_1_,
physicalin1_.MAC_ADDRESS as MAC40_10_1_,
rtrim(
right(physicalin1_.serial_number,
5)) as formula3_1_,
rtrim(right(physicalin1_.serial_number,
7)) as formula4_1_,
hardwareba2_.RECON_ID as RECON2_8_2_,
hardwareba2_.ASSET_TAG as ASSET3_8_2_,
hardwareba2_.SERIAL_NUMBER as SERIAL4_8_2_,
hardwareba2_.MACHINE_TYPE as MACHINE5_8_2_,
hardwareba2_.MACHINE_MODEL as MACHINE6_8_2_,
hardwareba2_.ACCOUNT_NAME as ACCOUNT7_8_2_,
hardwareba2_.PLATFORM as PLATFORM8_2_,
hardwareba2_.PRIMBILL as PRIMBILL8_2_,
hardwareba2_.CITY as CITY8_2_,
hardwareba2_.STATE as STATE8_2_,
hardwareba2_.COUNTRY as COUNTRY8_2_,
hardwareba2_.CATEGORY as CATEGORY8_2_,
hardwareba2_.IN_ATP as IN14_8_2_,
hardwareba2_.SDC as SDC8_2_,
hardwareba2_.POD as POD8_2_,
hardwareba2_.INDUSTRY as INDUSTRY8_2_,
hardwareba2_.SECTOR as SECTOR8_2_,
hardwareba2_.ATP_SERIAL_FIVE as ATP19_8_2_,
hardwareba2_.ICCBILL as ICCBILL8_2_,
hardwareba2_.SAPCUST as SAPCUST8_2_,
hardwareba2_.MAINCUST as MAINCUST8_2_,
hardwareba2_.PCFCUST as PCFCUST8_2_,
hardwareba2_.PRCUST as PRCUST8_2_,
hardwareba2_.INSTADD2 as INSTADD25_8_2_,
hardwareba2_.INSTADD3 as INSTADD26_8_2_,
hardwareba2_.ZIPCODE as ZIPCODE8_2_,
hardwareba2_.SCAN_DATE as SCAN28_8_2_,
hardwareba2_.SCAN_BUILDING as SCAN29_8_2_,
hardwareba2_.SCAN_FLOOR as SCAN30_8_2_,
hardwareba2_.SCAN_ROOM as SCAN31_8_2_,
hardwareba2_.SCAN_USER as SCAN32_8_2_,
hardwareba2_.AAS_STATUS as AAS33_8_2_,
hardwareba2_.REMLSEMO as REMLSEMO8_2_,
hardwareba2_.LEASE_AMOUNT as LEASE35_8_2_,
hardwareba2_.LEASSTRT as LEASSTRT8_2_,
hardwareba2_.LEASEND as LEASEND8_2_,
hardwareba2_.LEASSETYP as LEASSETYP8_2_,
hardwareba2_.TRANSTYP as TRANSTYP8_2_,
hardwareba2_.CONTRACT as CONTRACT8_2_,
hardwareba2_.NBV as NBV8_2_,
hardwareba2_.REMDPRMO as REMDPRMO8_2_,
hardwareba2_.EMPNUM as EMPNUM8_2_,
hardwareba2_.OWNUSER as OWNUSER8_2_,
hardwareba2_.ONPCF as ONPCF8_2_,
hardwareba2_.ONSAP as ONSAP8_2_,
hardwareba2_.ONAAS as ONAAS8_2_,
hardwareba2_.ONICC as ONICC8_2_,
hardwareba2_.LPID as LPID8_2_,
hardwareba2_.SAPLPID as SAPLPID8_2_,
hardwareba2_.OEMSER as OEMSER8_2_,
hardwareba2_.FINLOC as FINLOC8_2_,
hardwareba2_.INITDATE as INITDATE8_2_,
hardwareba2_.MFG as MFG8_2_,
hardwareba2_.INVENTRY as INVENTRY8_2_,
hardwareba2_.DEFN as DEFN8_2_,
hardwareba2_.IGFSERIAL as IGFSERIAL8_2_,
hardwareba2_.ORDERNO as ORDERNO8_2_,
hardwareba2_.GRID as GRID8_2_,
hardwareba2_.NODE_NAME as NODE60_8_2_,
hardwareba2_.OTHMACH as OTHMACH8_2_,
hardwareba2_.PRIORITY as PRIORITY8_2_,
hardwareba2_.PRIORITY_REASON as PRIORITY63_8_2_,
hardwareba2_.EXCL_STATUS as EXCL64_8_2_,
hardwareba2_.UNMATCHED_CATEGORY as UNMATCHED65_8_2_,
hardwareba2_.UNMATCHED_CATEGORY_COMMENT as UNMATCHED66_8_2_,
hardwareba2_.PARTNO as PARTNO8_2_,
hardwareba2_.REMOTE_USER as REMOTE68_8_2_,
hardwareba2_.RECORD_TIME as RECORD69_8_2_,
hardwareba2_.STATUS as STATUS8_2_,
hardwareba2_.ANALYST_COMMENT as ANALYST71_8_2_,
hardwareba2_.IP_ADDRESS as IP72_8_2_,
hardwareba2_.MAC_ADDRESS as MAC73_8_2_,
(hardwareba2_.remlsemo * hardwareba2_.lease_amount) + hardwareba2_.nbv as formula0_2_,
rtrim(right(hardwareba2_.serial_number,
5)) as formula1_2_,
rtrim(right(hardwareba2_.serial_number,
7)) as formula2_2_ from
HART.HARDWARE_RECON hardwarere0_
inner join
HART.PHYSICAL_INVENTORY physicalin1_
on hardwarere0_.PHYSICAL_INVENTORY_ID=physicalin1_.PHYSICAL_INVENTORY_ID
inner join
HART.HARDWARE_BASELINE hardwareba2_
on hardwarere0_.HARDWARE_BASELINE_ID=hardwareba2_.HARDWARE_BASELINE_ID
where
hardwarere0_.HARDWARE_BASELINE_ID in (
select
hardwarere3_.HARDWARE_BASELINE_ID
from
HART.HARDWARE_RECON hardwarere3_,
HART.HARDWARE_BASELINE hardwareba4_
where
hardwarere3_.HARDWARE_BASELINE_ID=hardwareba4_.HARDWARE_BASELINE_ID
and hardwarere3_.RECON_ID=98
and hardwarere3_.STATUS='ACTIVE'
and (
hardwareba4_.ACCOUNT_NAME in (
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
)
)
group by
hardwarere3_.HARDWARE_BASELINE_ID
having
count(hardwarere3_.HARDWARE_BASELINE_ID)>1
)
order by
hardwarere0_.HARDWARE_BASELINE_ID;
Login
FAQ
Search