Hello,
I am having a host of problems getting hibernate2.1.2 to work with DB2 7.2 on OS390. Below is info on the latest. I did a search on the sqlcodes and I am still lost. Any help is greatly appreciated!

Code:
_________________________________________
Session s = factory.openSession();
Transaction tx=null;
try {
s.setFlushMode(FlushMode.NEVER);
tx = s.beginTransaction();
Criteria crit = s.createCriteria(CrsInf.class);
List courses = crit.list();
Iterator iter = new HashSet(courses).iterator();
while ( iter.hasNext() ) {
CrsInf crs = (CrsInf) iter.next();
System.out.println( "Crs: " + crs.getTitle());
System.out.println();
tx.commit();
};
}
catch (Exception e) {
if (tx!=null) tx.rollback();
throw e;
}
finally {
s.close();
}
________________________________________________


StackTrace:
________________________________________________
C:\hibernate-2.1\NTDG\generated>java Main
Mar 15, 2004 2:44:49 PM net.sf.hibernate.cfg.Environment <clinit>
INFO: Hibernate 2.1.2
Mar 15, 2004 2:44:49 PM net.sf.hibernate.cfg.Environment <clinit>
INFO: loaded properties from resource hibernate.properties: {hibernate.connection.driver_class=com.ibm.db2.jcc.DB2Driver, hibernat
e.cglib.use_reflection_optimizer=true, hibernate.cache.provider_class=net.sf.hibernate.cache.HashtableCacheProvider, hibernate.cac
he.use_query_cache=true, hibernate.max_fetch_depth=1, hibernate.dialect=net.sf.hibernate.dialect.DB2Dialect, hibernate.jdbc.use_st
reams_for_binary=true, hibernate.jdbc.batch_size=0, hibernate.query.substitutions=true 1, false 0, yes 'Y', no 'N', hibernate.prox
ool.pool_alias=pool1, hibernate.connection.username=pickle, hibernate.connection.url=jdbc:db2://111.111.2.222:2323/DATABASE, hib
ernate.connection.password=xxxxx, hibernate.connection.pool_size=1}
Mar 15, 2004 2:44:49 PM net.sf.hibernate.cfg.Environment <clinit>
INFO: using java.io streams to persist binary types
Mar 15, 2004 2:44:49 PM net.sf.hibernate.cfg.Environment <clinit>
INFO: using CGLIB reflection optimizer
Mar 15, 2004 2:44:49 PM net.sf.hibernate.cfg.Configuration addClass
INFO: Mapping resource: CrsInf.hbm.xml
Mar 15, 2004 2:44:50 PM net.sf.hibernate.cfg.Binder bindRootClass
INFO: Mapping class: CrsInf -> CRS_INF
Mar 15, 2004 2:44:50 PM net.sf.hibernate.cfg.Configuration secondPassCompile
INFO: processing one-to-many association mappings
Mar 15, 2004 2:44:50 PM net.sf.hibernate.cfg.Configuration secondPassCompile
INFO: processing one-to-one association property references
Mar 15, 2004 2:44:50 PM net.sf.hibernate.cfg.Configuration secondPassCompile
INFO: processing foreign key constraints
Mar 15, 2004 2:44:50 PM net.sf.hibernate.dialect.Dialect <init>
INFO: Using dialect: net.sf.hibernate.dialect.DB2Dialect
Mar 15, 2004 2:44:50 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Maximim outer join fetch depth: 1
Mar 15, 2004 2:44:50 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Use outer join fetching: true
Mar 15, 2004 2:44:50 PM net.sf.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Using Hibernate built-in connection pool (not for production use!)
Mar 15, 2004 2:44:50 PM net.sf.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Hibernate connection pool size: 1
Mar 15, 2004 2:44:50 PM net.sf.hibernate.connection.DriverManagerConnectionProvider configure
INFO: using driver: com.ibm.db2.jcc.DB2Driver at URL: jdbc:db2://111.111.2.222:2323/DATABASE
Mar 15, 2004 2:44:50 PM net.sf.hibernate.connection.DriverManagerConnectionProvider configure
INFO: connection properties: {user=pickle, password=xxx}
Mar 15, 2004 2:44:50 PM net.sf.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
Mar 15, 2004 2:44:51 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Use scrollable result sets: true
Mar 15, 2004 2:44:51 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Use JDBC3 getGeneratedKeys(): false
Mar 15, 2004 2:44:51 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: false
Mar 15, 2004 2:44:51 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: echoing all SQL to stdout
Mar 15, 2004 2:44:51 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {no='N', true=1, yes='Y', false=0}
Mar 15, 2004 2:44:51 PM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: cache provider: net.sf.hibernate.cache.HashtableCacheProvider
Mar 15, 2004 2:44:51 PM net.sf.hibernate.cfg.Configuration configureCaches
INFO: instantiating and configuring caches
Mar 15, 2004 2:44:51 PM net.sf.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Mar 15, 2004 2:44:51 PM net.sf.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: no JNDI name configured
Mar 15, 2004 2:44:51 PM net.sf.hibernate.cache.UpdateTimestampsCache <init>
INFO: starting update timestamps cache at region: net.sf.hibernate.cache.UpdateTimestampsCache
Mar 15, 2004 2:44:51 PM net.sf.hibernate.cache.QueryCache <init>
INFO: starting query cache at region: net.sf.hibernate.cache.QueryCache
Viewing all auction item objects
Hibernate: select this.YEAR as YEAR0_, this.TERM as TERM0_, this.CAMP as CAMP0_, this.CRS_NAM as CRS_NAM0_, this.CRS_NBR as CRS_NB
R0_, this.SECT as SECT0_, this.BEGIN_TIME as BEGIN_TIME0_, this.END_TIME as END_TIME0_, this.DAYS as DAYS0_, this.SSN as SSN0_, th
is.BLDG as BLDG0_, this.ROOM_NBR as ROOM_NBR0_, this.ROOM_SUFFX as ROOM_SUFFX0_, this.SUPPL_TITLE as SUPPL_T14_0_, this.ENRL_LIMIT
as ENRL_LIMIT0_, this.SEATS_OCCUP as SEATS_O16_0_, this.CR_HOURS as CR_HOURS0_, this.SHORT_NAM as SHORT_NAM0_, this.BLDG_DESC as
BLDG_DESC0_, this.EMAIL as EMAIL0_, this.TITLE as TITLE0_, this.DELIV_METH as DELIV_METH0_ from DWH.CRS_INF this where 1=1
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: -551, SQLState: 42501
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: DB2 SQL error: SQLCODE: -551, SQLSTATE: 42501, SQLERRMC: PICKLE;SELECT;DWH.CRS_INF
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: -516, SQLState: 26501
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: DB2 SQL error: SQLCODE: -516, SQLSTATE: 26501, SQLERRMC: null
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: -514, SQLState: 26501
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: DB2 SQL error: SQLCODE: -514, SQLSTATE: 26501, SQLERRMC: SQL_CURLH200C1
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: -551, SQLState: 42501
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: DB2 SQL error: SQLCODE: -551, SQLSTATE: 42501, SQLERRMC: PICKLE;SELECT;DWH.CRS_INF
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: -516, SQLState: 26501
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: DB2 SQL error: SQLCODE: -516, SQLSTATE: 26501, SQLERRMC: null
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: -514, SQLState: 26501
Mar 15, 2004 2:44:51 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: DB2 SQL error: SQLCODE: -514, SQLSTATE: 26501, SQLERRMC: SQL_CURLH200C1
Mar 15, 2004 2:44:51 PM net.sf.hibernate.JDBCException <init>
SEVERE: SQLException occurred
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -551, SQLSTATE: 42501, SQLERRMC: PICKLE;SELECT;DWH.CRS_INF
at com.ibm.db2.jcc.c.cl.e(cl.java:1213)
at com.ibm.db2.jcc.c.cm.executeQuery(cm.java:279)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:795)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:950)
at net.sf.hibernate.loader.Loader.list(Loader.java:941)
at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:118)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3571)
at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:223)
at Main.viewAllAuctionsSlow(Main.java:44)
at Main.main(Main.java:77)
Exception in thread "main" net.sf.hibernate.JDBCException: SQLException occurred
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3574)
at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:223)
at Main.viewAllAuctionsSlow(Main.java:44)
at Main.main(Main.java:77)
Caused by: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -551, SQLSTATE: 42501, SQLERRMC: PICKLE;SELECT;DWH.CRS_INF
at com.ibm.db2.jcc.c.cl.e(cl.java:1213)
at com.ibm.db2.jcc.c.cm.executeQuery(cm.java:279)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:795)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:950)
at net.sf.hibernate.loader.Loader.list(Loader.java:941)
at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:118)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3571)
... 3 more

________________________________________



This is my mapping file:
________________________________________

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<class
name="CrsInf"
table="CRS_INF"
schema="DWH"
>
<composite-id name="comp_id" class="CrsInfPK">
<key-property
name="year"
column="YEAR"
type="java.lang.String"
length="4"
/>
<key-property
name="term"
column="TERM"
type="java.lang.String"
length="1"
/>
<key-property
name="camp"
column="CAMP"
type="java.lang.String"
length="3"
/>
<key-property
name="crsNam"
column="CRS_NAM"
type="java.lang.String"
length="4"
/>
<key-property
name="crsNbr"
column="CRS_NBR"
type="java.lang.String"
length="4"
/>
<key-property
name="sect"
column="SECT"
type="java.lang.String"
length="3"
/>
<key-property
name="beginTime"
column="BEGIN_TIME"
type="java.lang.String"
length="4"
/>
<key-property
name="endTime"
column="END_TIME"
type="java.lang.String"
length="4"
/>
<key-property
name="days"
column="DAYS"
type="java.lang.String"
length="5"
/>
<key-property
name="ssn"
column="SSN"
type="java.lang.String"
length="9"
/>
<key-property
name="bldg"
column="BLDG"
type="java.lang.String"
length="2"
/>
<key-property
name="roomNbr"
column="ROOM_NBR"
type="java.lang.String"
length="5"
/>
</composite-id>

<property
name="roomSuffx"
type="java.lang.String"
column="ROOM_SUFFX"
not-null="true"
length="1"
/>
<property
name="supplTitle"
type="java.lang.String"
column="SUPPL_TITLE"
not-null="true"
length="21"
/>
<property
name="enrlLimit"
type="short"
column="ENRL_LIMIT"
not-null="true"
length="5"
/>
<property
name="seatsOccup"
type="short"
column="SEATS_OCCUP"
not-null="true"
length="5"
/>
<property
name="crHours"
type="short"
column="CR_HOURS"
not-null="true"
length="5"
/>
<property
name="shortNam"
type="java.lang.String"
column="SHORT_NAM"
not-null="true"
length="15"
/>
<property
name="bldgDesc"
type="java.lang.String"
column="BLDG_DESC"
not-null="true"
length="70"
/>
<property
name="email"
type="java.lang.String"
column="EMAIL"
not-null="true"
length="50"
/>
<property
name="title"
type="java.lang.String"
column="TITLE"
not-null="true"
length="45"
/>
<property
name="delivMeth"
type="java.lang.String"
column="DELIV_METH"
not-null="true"
length="4"
/>

<!-- associations -->
</class>
</hibernate-mapping>
_______________________________________

I haven't used the critera, but I have done find with great success.

Session s = factory.openSession();

try {
List courses = s.find("select o from CrsInf o");
Iterator iter = courses.iterator();
while ( iter.hasNext() ) {
CrsInf crs = (CrsInf) iter.next();
System.out.println( "Crs: " + crs.getTitle());
System.out.println();

};
}
catch (Exception e) {
if (tx!=null) tx.rollback();
throw e;
}
finally {
s.close();
}

I get the same error messages.

Try issuing the same SQL through DBVisualizer or through whatever SQL tool your database normally supports. In other words, do the same query by hand and see if you can get any more meaningful error information.

It's unlikely this error is related to Hibernate specifically.

Cheers,
Rob

I will try and post back the info. On a side not, I am an applicatiosn developer not a DBA and I know the userid I am using has permissions to select, inset, and delete. I would have to say otherwise it is limited. Does using a tool like Hibernate require strong access rights? I am trying to map to existing tables.

Thanks for the help thus far.

Hibernate is using plain JDBC underneath, so basically if you can't do it in JDBC, Hibernate can't do it either. And moreover, Hibernate has no concept of database permissions -- it is using whatever permissions your app server or other database layer is using.

If you are having problems, you should always try to replicate the problems either with direct SQL or with simple JDBC code, to isolate the real issue and remove Hibernate from the equation.

Cheers,
Rob

Yes understand the use of JDBC through the driver. It does make sense about testing the straight SQL. What I want to know is there a basic set of user rights I should request from the DBA in order to make hibernate work. For instants, I didn't have permission to execute the DDL statements hibernate was using to create the example tables.

If I had to guess about the latest error I am getting, it would have to be a permissions thing as well.

You have a DBA letting you use automatic schema generation? Wow...

Note sure about DB2, but I can tell you that on our Oracle production servers, our apps need the CONNECT, SELECT, INSERT, UPDATE, DELETE privileges if the db-user they use to connect with are not the schema owner. On our unit-test boxes, where we enable schema generation, we additionally need to add CREATE_TABLE and CREATE_SEQUENCE. If you plan on utilizing locking, you may also need any locking-specific privileges.

I think thats all we use.

I tried the query in DBVisualizer and I get the same error message. Posted below are the error messages and then the query. I looked the errors up in the DB2 manual and it says somethign about invalid tokens???
______________________________________

DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;, FROM INTO
______________________________________

select * from ( select rownumber() over() as row_, this.YEAR as YEAR0_, this.TERM as TERM0_, this.CAMP as CAMP0_, this.CRS_NAM as CRS_NAM0_, this.CRS_NBR as CRS_NBR0_, this.SECT as SECT0_, this.BEGIN_TIME as BEGIN_TIME0_, this.END_TIME as END_TIME0_, this.DAYS as DAYS0_, this.SSN as SSN0_, this.BLDG as BLDG0_, this.ROOM_NBR as ROOM_NBR0_, this.ROOM_SUFFX as ROOM_SUFFX0_, this.SUPPL_TITLE as SUPPL_T14_0_, this.ENRL_LIMIT as ENRL_LIMIT0_, this.SEATS_OCCUP as SEATS_O16_0_, this.CR_HOURS as CR_HOURS0_, this.SHORT_NAM as SHORT_NAM0_, this.BLDG_DESC as BLDG_DESC0_, this.EMAIL as EMAIL0_, this.TITLE as TITLE0_, this.DELIV_METH as DELIV_METH0_ from DWH.CRS_INF_01 this where 1=1 ) as temp_ where row_ <= ?

I know this isn't a hibernate bug, but probably a small issue someone with DB2 knowledge can help with. I am the only person who uses JAVA or any JAVA tools at my organization so this is my best chance. If I can't get Hibernate to work, my whole project will take a different direction and all of the JAVA naysayers here will say I told you so.

Do you have anyone in your organization who is 1) even moderately supportive of your work and 2) more familiar with DB2 than you are? Seems to me that you need a local person to pair up with on figuring out these DB2 issues. This forum is almost certainly not the right place to find DB2 cluefulness. (Though you might also try the "System Integration" forum.)

Cheers,
Rob

It might also be good to post your hibernate configuration. Perhaps some kind of issue with the hibernate dialect?

Cheers,
Rob

looks like maybe DB2/390 doesn't support the "rownumber() over" syntax. Hibernate actually has a customized DB2400Dialect that might work better. Perhaps you will need to make your own DB2390Dialect, modelled on that, though I know of a few others using Hibernate smoothly with 390, so I was under the impression that the default dialect was ok.

Thanks for the suggestion. I will tinker with the dialects, see what happens and post back results!

I switched to the AS400 Dialect and the query runs fine through Hibernate.
This is weird however because as you said, there has to be others running Hibernate with DB2 on MVS OS/390 who would have had the same problem. Are there any pointers that I need on writing a dialect for OS/390? I don't want to assume that the AS400 Dialect is completely compatible.

Thanks for the help,
Troy

The general pointers would just be to find the best damn reference on the OS/390 version of SQL that you can, and then the best damn reference on the OS/400 dialect, and then look for the differences.

Probably wouldn't hurt to start familiarizing yourself with the dialect classes in Hibernate in case you need to patch them.

And also, spike early and often -- if you are going to be doing a particular kind of query, write a test that performs it as soon as you are aware of it, so you can shake out dialect problems before they turn into unexpected roadblocks.

Finally, maybe post to the System Integration forum and ask that anyone using Hibernate with OS/390 please drop you an email so you can form an informal mutual support group.

Cheers!
Rob