-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Query runs for 24 hrs when submitted by web app!
PostPosted: Thu Apr 24, 2008 4:50 pm 
Newbie

Joined: Thu Apr 24, 2008 3:52 pm
Posts: 1
Location: NY
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;


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.