Resultset Not able to handle 200 records using JBOSS.

[Saurabh Gupta]

Hi All,

In my application i am trying to fetch around 200 records in resultset.
But server hangs after executing the query.
see below code:

  try{
       openConnection(CommonConstants.db2JNDIString);
     if(IS_ESTABLISHED == true){
 
     pstmt = con.prepareStatement("SELECT * FROM Table1 where RTG_STATE_CD='32' and CO_ID in ('00') and LINE_OF_BUS in ('2') and STATUS_IND='P';");
 
   System.out.println("start query");
 
    rs = pstmt.executeQuery();
   if(rs != null){
    while(rs.next()){
System.out.println("Query ends");
myform = new Myform();
          myform.setSt(rs.getString(1));
myform.setCo(rs.getString(2));
myform.setLob(rs.getString(3));
myform.setFormCd(rs.getString(4));
myform.setIdenIdCd(rs.getBigDecimal(5));
myform.setEffDtNb(rs.getDate(6));
myform.setEffDtRn(rs.getDate(7));
myform.setGenName(rs.getString(8));
myform.setEditionDt(rs.getDate(9));
myform.setEndType(rs.getString(10));
myform.setBurKmprCd(rs.getString(11));
myform.setCurManOpt(rs.getString(12));
myform.setFormName(rs.getString(13));
myform.setEndName(rs.getString(14));
myform.setEndTitle(rs.getString(15));
myform.setEndIdenEnt(rs.getString(16));
myform.setOrigDtNb(rs.getDate(17));
myform.setOrigDtRn(rs.getDate(18));
myform.setStatusInd(rs.getString(19));
myform.setEndReq(rs.getString(20));
myform.setCancelSw(rs.getString(21));
myforms.add(myform);
}
}
}
}catch(SQLException sqe){
   sqe.printStackTrace();
}catch(Exception exp){
   exp.printStackTrace();
}  

It prints "Start query" and hangs after it.
When i execute this query in winsql i am getting 230 records only.
I am using Jboss 4.2 with DB2 8.x.
I am not sure why it is not able to handle such less no of records.

Please help....

Thanks
Saurabh
[ June 06, 2008: Message edited by: Saurabh Gupta ]

[David O'Meara]

1) remove the semicolon at the end of the statement
2) test the statement in a DB client
3) if you want to read columns by number, request the column names explicitly, don't use '*'
4) report exceptions, don't just use 'printStackTrace()'

[Saurabh Gupta]

Thanks David, but even after trying everything told by you i am not able to get the application working. The server log doesn't print anything after starting executing executeQuery().
Is it related to JNDI connection pooling or Jboss 4.2 issue ?
Any kind of help will be appreciable.

[David O'Meara]

Difficult to tell without finding a stack trace or debugging the application

[Saurabh Gupta]

below is the server log:

2008-06-06 07:29:56,924 INFO [STDOUT] Connection is NOW open
2008-06-06 07:29:56,924 INFO [STDOUT] SELECT RTG_STATE_CD,CO_ID,LINE_OF_BUS,FORM_ID_CD,IDENT_ID_CD,EFF_DATE_NB,EFF_DATE_RNEW,GENERIC_NAME,EDITION_DATE,END_TYPE,BUR_KMPR_CODE,CUR_MAND_OPT,FORM_NAME,ENDORS_NAME,END_TITLE,END_IDENT_ENT,ORIG_DATE_NB,ORIG_DATE_RNEW,STATUS_IND,END_REQUEST_NO,CANCEL_SW FROM TABLE1 where RTG_STATE_CD='32' and CO_ID in ('00') and LINE_OF_BUS in ('2') and STATUS_IND='P'
2008-06-06 07:29:56,924 INFO [STDOUT] *********start query*************2008-06-06 07:31:22,474 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - first pass <Fri, 6 Jun 2008 07:31:22>
2008-06-06 07:31:22,474 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] StatusModule: first pass
2008-06-06 07:31:22,474 DEBUG [com.arjuna.ats.txoj.logging.txojLoggerI18N] [com.arjuna.ats.internal.txoj.recovery.TORecoveryModule_3] - TORecoveryModule - first pass
2008-06-06 07:31:22,474 DEBUG [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.info.firstpass] Local XARecoveryModule - first pass
2008-06-06 07:31:32,452 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - second pass <Fri, 6 Jun 2008 07:31:32>
2008-06-06 07:31:32,452 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] AtomicActionRecoveryModule: Second pass
2008-06-06 07:31:32,452 DEBUG [com.arjuna.ats.txoj.logging.txojLoggerI18N] [com.arjuna.ats.internal.txoj.recovery.TORecoveryModule_6] - TORecoveryModule - second pass
2008-06-06 07:31:32,452 DEBUG [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.info.secondpass] Local XARecoveryModule - second pass
2008-06-06 07:33:32,194 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - first pass <Fri, 6 Jun 2008 07:33:32>
2008-06-06 07:33:32,194 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] StatusModule: first pass
2008-06-06 07:33:32,194 DEBUG [com.arjuna.ats.txoj.logging.txojLoggerI18N] [com.arjuna.ats.internal.txoj.recovery.TORecoveryModule_3] - TORecoveryModule - first pass
2008-06-06 07:33:32,194 DEBUG [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.info.firstpass] Local XARecoveryModule - first pass
2008-06-06 07:33:42,173 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - second pass <Fri, 6 Jun 2008 07:33:42>
2008-06-06 07:33:42,173 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] AtomicActionRecoveryModule: Second pass
2008-06-06 07:33:42,173 DEBUG [com.arjuna.ats.txoj.logging.txojLoggerI18N] [com.arjuna.ats.internal.txoj.recovery.TORecoveryModule_6] - TORecoveryModule - second pass
2008-06-06 07:33:42,173 DEBUG [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.info.secondpass] Local XARecoveryModule - second pass
2008-06-06 07:35:41,915 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - first pass <Fri, 6 Jun 2008 07:35:41>
2008-06-06 07:35:41,915 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] StatusModule: first pass
2008-06-06 07:35:41,915 DEBUG [com.arjuna.ats.txoj.logging.txojLoggerI18N] [com.arjuna.ats.internal.txoj.recovery.TORecoveryModule_3] - TORecoveryModule - first pass
2008-06-06 07:35:41,915 DEBUG [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.info.firstpass] Local XARecoveryModule - first pass
2008-06-06 07:35:51,893 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - second pass <Fri, 6 Jun 2008 07:35:51>
2008-06-06 07:35:51,893 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] AtomicActionRecoveryModule: Second pass
2008-06-06 07:35:51,893 DEBUG [com.arjuna.ats.txoj.logging.txojLoggerI18N] [com.arjuna.ats.internal.txoj.recovery.TORecoveryModule_6] - TORecoveryModule - second pass
2008-06-06 07:35:51,893 DEBUG [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.info.secondpass] Local XARecoveryModule - second pass
2008-06-06 07:36:18,804 DEBUG [org.jboss.resource.connectionmanager.IdleRemover] run: IdleRemover notifying pools, interval: 450000
2008-06-06 07:37:51,635 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - first pass <Fri, 6 Jun 2008 07:37:51>
2008-06-06 07:37:51,635 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] StatusModule: first pass
2008-06-06 07:37:51,635 DEBUG [com.arjuna.ats.txoj.logging.txojLoggerI18N] [com.arjuna.ats.internal.txoj.recovery.TORecoveryModule_3] - TORecoveryModule - first pass
2008-06-06 07:37:51,635 DEBUG [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.info.firstpass] Local XARecoveryModule - first pass
2008-06-06 07:38:01,613 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - second pass <Fri, 6 Jun 2008 07:38:01>
2008-06-06 07:38:01,613 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] AtomicActionRecoveryModule: Second pass
2008-06-06 07:38:01,613 DEBUG [com.arjuna.ats.txoj.logging.txojLoggerI18N] [com.arjuna.ats.internal.txoj.recovery.TORecoveryModule_6] - TORecoveryModule - second pass
2008-06-06 07:38:01,613 DEBUG [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.info.secondpass] Local XARecoveryModule - second pass
2008-06-06 07:40:01,355 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - first pass <Fri, 6 Jun 2008 07:40:01>
2008-06-06 07:40:01,355 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] StatusModule: first pass
2008-06-06 07:40:01,355 DEBUG [com.arjuna.ats.txoj.logging.txojLoggerI18N] [com.arjuna.ats.internal.txoj.recovery.TORecoveryModule_3] - TORecoveryModule - first pass
2008-06-06 07:40:01,355 DEBUG [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.info.firstpass] Local XARecoveryModule - first pass
2008-06-06 07:40:11,334 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - second pass <Fri, 6 Jun 2008 07:40:11>
2008-06-06 07:40:11,334 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] AtomicActionRecoveryModule: Second pass
2008-06-06 07:40:11,334 DEBUG [com.arjuna.ats.txoj.logging.txojLoggerI18N] [com.arjuna.ats.internal.txoj.recovery.TORecoveryModule_6] - TORecoveryModule - second pass
2008-06-06 07:40:11,334 DEBUG [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.recovery.info.secondpass] Local XARecoveryModule - second pass
2008-06-06 07:42:11,076 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] Periodic recovery - first pass <Fri, 6 Jun 2008 07:42:11>
2008-06-06 07:42:11,076 DEBUG [com.arjuna.ats.arjuna.logging.arjLogger] StatusModule: first pass

[David O'Meara]

}catch(SQLException sqe){
   sqe.printStackTrace();
}catch(Exception exp){
   exp.printStackTrace();
}

This needs to be fixed and will prevent anything useful being logged.