Hi,

I'm trying to port my application from Oracle to DB2 (v9.7).

I have set the "hibernate.default_schema" property to VRM in my hibernate.cfg.xml file. (<property name="hibernate.default_schema">VRM</property>)

The problem is that one of my queries (not all of them) fails with the following error:
DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.TRACK, DRIVER=3.58.82

From this error I can guess that hibernate is looking for the TRACK table under the DB2ADMIN schema. However, I have set the default schema to be VRM. Please note that other queries (even for the "TRACK" table) are working fine.

My HQL query is:
select track from Track as track left join fetch track.managementAuthorityCommand as mac left join fetch mac.managementAuthority as ma left join fetch mac.deviceClass as dc where
track.recoveredTrackId is null and
(1=1) order by track.creationTime desc

While Hibernate translate it to the following: (Please note that no DB2ADMIN string is present in the translated query)

Any ideas why DB2 throws this error?

select * from ( select rownumber() over(order by track0_.CREATION_TIME desc) as rownumber_, track0_.ID as ID41_0_, management1_.ID as ID25_1_, management2_.ID as ID24_2_, deviceclas3_.ID as ID15_3_, track0_.VERSION as VERSION41_0_, track0_.CREATION_TIME as CREATION3_41_0_, track0_.LAST_UPDATE_TIME as LAST4_41_0_, track0_.LAST_USER_NAME as LAST5_41_0_, track0_.LAST_APP_NAME as LAST6_41_0_, track0_.START_TIME as START7_41_0_, track0_.END_TIME as END8_41_0_, track0_.TERMINAL_NAME as TERMINAL9_41_0_, track0_.STATUS as STATUS41_0_, track0_.TRACK_DEFINITION_ID as TRACK11_41_0_, track0_.DESCRIPTION as DESCRIP12_41_0_, track0_.INVENTORY_LIST as INVENTORY13_41_0_, track0_.DCG_LIST as DCG14_41_0_, track0_.UPDATE_LIST as UPDATE15_41_0_, track0_.CONFIGURATION as CONFIGU16_41_0_, track0_.DEPLOYMENT_PACKAGE_ID as DEPLOYMENT17_41_0_, track0_.CURRENT_STEP as CURRENT18_41_0_, track0_.ERROR_CODE as ERROR19_41_0_, track0_.ERROR_PARAMS as ERROR20_41_0_, track0_.ADD_LIST as ADD21_41_0_, track0_.RECOVERED_TRACK_ID as RECOVERED22_41_0_, track0_.MSISDN as MSISDN41_0_, track0_.FRIENDLY_NAME as FRIENDLY24_41_0_, track0_.WAP_TIME as WAP25_41_0_, track0_.CREATION_USER_NAME as CREATION26_41_0_, track0_.IS_DP_FROM_CACHE as IS27_41_0_, track0_.IS_UPDATE_REQUEST_SENT as IS28_41_0_, track0_.INVENTORY_LIST_FILE_ID as INVENTORY29_41_0_, track0_.DCG_LIST_FILE_ID as DCG30_41_0_, track0_.UPDATE_LIST_FILE_ID as UPDATE31_41_0_, track0_.FULLY_INVENTORY_INFO as FULLY32_41_0_, track0_.current_state as current33_41_0_, track0_.META_DATA_STATUS as META34_41_0_, track0_.MA_COMMAND_ID as MA35_41_0_, track0_.MIN_START_TIME as MIN36_41_0_, track0_.CURRENT_RELEASE_NAME as CURRENT37_41_0_, track0_.RELEASE_NAME as RELEASE38_41_0_, management1_.VERSION as VERSION25_1_, management1_.CREATION_TIME as CREATION3_25_1_, management1_.LAST_UPDATE_TIME as LAST4_25_1_, management1_.LAST_USER_NAME as LAST5_25_1_, management1_.LAST_APP_NAME as LAST6_25_1_, management1_.COMMAND as COMMAND25_1_, management1_.MANAGEMENT_AUTHORITY_ID as MANAGEMENT8_25_1_, management1_.STATUS as STATUS25_1_, management1_.DEVICE_CLASS_ID as DEVICE10_25_1_, management1_.DP_DESCRIPTION as DP11_25_1_, management1_.MA_CORRELATOR as MA12_25_1_, management1_.DEADLINE as DEADLINE25_1_, management1_.FLOW_ID as FLOW14_25_1_, management1_.COMMAND_SIZE as COMMAND15_25_1_, management1_.RELEASE_ID as RELEASE16_25_1_, management1_.RELEASE_NAME as RELEASE17_25_1_, management1_.INITIATOR as INITIATOR25_1_, management1_.MA_USER_NAME as MA19_25_1_, ( select count(1)*100/management1_.command_size from track t where t.ma_command_id=management1_.id and t.current_state in ('UpToDate', 'UpdatedSuccessfully') and t.META_DATA_STATUS in ('Completed', 'Reported') ) as formula0_1_, management2_.VERSION as VERSION24_2_, management2_.CREATION_TIME as CREATION3_24_2_, management2_.LAST_UPDATE_TIME as LAST4_24_2_, management2_.LAST_USER_NAME as LAST5_24_2_, management2_.LAST_APP_NAME as LAST6_24_2_, management2_.URL as URL24_2_, management2_.STATUS as STATUS24_2_, management2_.DL_SERVER as DL9_24_2_, management2_.RB_USER as RB10_24_2_, management2_.NAME as NAME24_2_, management2_.NAME_BK as NAME12_24_2_, management2_.TYPE as TYPE24_2_, management2_.SMSC_NAME as SMSC14_24_2_, management2_.SMSC_URL as SMSC15_24_2_, management2_.SMSC_USERNAME as SMSC16_24_2_, management2_.SMSC_PASSWORD as SMSC17_24_2_, deviceclas3_.VERSION as VERSION15_3_, deviceclas3_.Name as Name15_3_, deviceclas3_.Name_BK as Name4_15_3_, deviceclas3_.Vendor as Vendor15_3_, deviceclas3_.Vendor_BK as Vendor6_15_3_, deviceclas3_.PLATFORM_ID as PLATFORM7_15_3_, deviceclas3_.DEVICE_DETAILS_ID as DEVICE8_15_3_ from VRM.TRACK track0_ left outer join VRM.MANAGEMENT_AUTHORITY_COMMAND management1_ on track0_.MA_COMMAND_ID=management1_.ID left outer join VRM.MANAGEMENT_AUTHORITY management2_ on management1_.MANAGEMENT_AUTHORITY_ID=management2_.ID left outer join VRM.DEVICE_CLASS deviceclas3_ on management1_.DEVICE_CLASS_ID=deviceclas3_.ID where (track0_.RECOVERED_TRACK_ID is null) and 1=1 order by track0_.CREATION_TIME desc ) as temp_ where rownumber_ <= ?




Thanks
Guy Hudara

Add:

:defaultSchema=VRM;

to your connection URL.

Pierce