Our web programmer was looking in his application log an found the
following error:
2006-08-31 16:33:35,129 ERROR org.hibernate.u til.JDBCExcepti onReporter
- <[IBM][CLI Driver][DB2/6000] SQL0723N An error occurred in a
triggered SQL statement in trigger "OLGCWEB.TBLPRO PS_INS_TRG".
Information returned for the error includes SQLCODE "-811", SQLSTATE
"21000" and message tokens "". SQLSTATE=09000
2006-08-31 16:33:35,139 ERROR
org.hibernate.e vent.def.Abstra ctFlushingEvent Listener - <Could not
synchronize database state with session>
(AbstractFlushi ngEventListener .java.performEx ecutions:277)
org.hibernate.e xception.Generi cJDBCException: could not insert:
[ca.olgc.proline .domain.PropDO]
at
org.hibernate.e xception.SQLSta teConverter.han dledNonSpecific Exception(SQLSt ateConverter.ja va:82)
at
org.hibernate.e xception.SQLSta teConverter.con vert(SQLStateCo nverter.java:70 )
at
org.hibernate.e xception.JDBCEx ceptionHelper.c onvert(JDBCExce ptionHelper.jav a:43)
at
org.hibernate.p ersister.entity .BasicEntityPer sister.insert(B asicEntityPersi ster.java:1869)
at
org.hibernate.p ersister.entity .BasicEntityPer sister.insert(B asicEntityPersi ster.java:2200)
at
org.hibernate.a ction.EntityIns ertAction.execu te(EntityInsert Action.java:46)
at org.hibernate.e ngine.ActionQue ue.execute(Acti onQueue.java:23 9)
at
org.hibernate.e ngine.ActionQue ue.executeActio ns(ActionQueue. java:223)
at
org.hibernate.e ngine.ActionQue ue.executeActio ns(ActionQueue. java:136)
at
org.hibernate.e vent.def.Abstra ctFlushingEvent Listener.perfor mExecutions(Abs tractFlushingEv entListener.jav a:274)
at
org.hibernate.e vent.def.Defaul tFlushEventList ener.onFlush(De faultFlushEvent Listener.java:2 7)
at org.hibernate.i mpl.SessionImpl .flush(SessionI mpl.java:730)
at
ca.olgc.proline .dao.hibernate. BaseHibernateDA O.batchSaveOrUp date(BaseHibern ateDAO.java(Com piled
Code))
at
ca.olgc.proline .facade.session beans.PropicksC ardBean.addOrUp dateProPicksCar d(PropicksCardB ean.java:233)
at
ca.olgc.proline .facade.session beans.EJSLocalS tatelessPropick sCard_464a659b. addOrUpdateProP icksCard(EJSLoc alStatelessProp icksCard_464a65 9b.java:159)
at
ca.olgc.proline .facade.Proline FacadeBean.addO rUpdateProPicks Card(ProlineFac adeBean.java:78 0)
at
ca.olgc.proline .facade.EJSRemo teStatelessProl ineFacade_cc482 172.addOrUpdate ProPicksCard(EJ SRemoteStateles sProlineFacade_ cc482172.java:5 67)
at
ca.olgc.proline .facade._Prolin eFacade_Stub.ad dOrUpdateProPic ksCard(_Proline Facade_Stub.jav a:1579)
at
ca.olgc.proline .messaging.help er.DatabaseUpda teHelper.add(Da tabaseUpdateHel per.java:106)
at
ca.olgc.proline .messaging.help er.DatabaseUpda teHelper.update DB(DatabaseUpda teHelper.java:5 0)
at
ca.olgc.proline .messaging.mdb. SportsRefresher Bean.updateDB(S portsRefresherB ean.java:81)
at
ca.olgc.proline .messaging.mdb. SportsRefresher Bean.onMessage( SportsRefresher Bean.java:65)
at
com.ibm.ejs.jms .listener.MDBWr apper$Priviledg edOnMessage.run (MDBWrapper.jav a:208)
at java.security.A ccessController .doPrivileged(N ative Method)
at
com.ibm.ejs.jms .listener.MDBWr apper.callOnMes sage(MDBWrapper .java:197)
at com.ibm.ejs.jms .listener.MDBWr apper.onMessage (MDBWrapper.jav a:175)
at com.ibm.mq.jms. MQSession.run(M QSession.java(C ompiled Code))
at com.ibm.ejs.jms .JMSSessionHand le.run(JMSSessi onHandle.java:9 23)
at
com.ibm.ejs.jms .listener.Serve rSession.connec tionConsumerOnM essage(ServerSe ssion.java:739)
at
com.ibm.ejs.jms .listener.Serve rSession.onMess age(ServerSessi on.java:524)
at
com.ibm.ejs.jms .listener.Serve rSession.dispat ch(ServerSessio n.java:491)
at java.lang.refle ct.Method.invok e(Native Method)
at
com.ibm.ejs.jms .listener.Serve rSessionDispatc her.dispatch(Se rverSessionDisp atcher.java:37)
at com.ibm.ejs.con tainer.MDBWrapp er.onMessage(MD BWrapper.java:9 1)
at com.ibm.ejs.con tainer.MDBWrapp er.onMessage(MD BWrapper.java:1 27)
at com.ibm.ejs.jms .listener.Serve rSession.run(Se rverSession.jav a:373)
at com.ibm.ws.util .ThreadPool$Wor ker.run(ThreadP ool.java:681)
Caused by:
COM.ibm.db2.jdb c.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0723N An
error occurred in a triggered SQL statement in trigger
"OLGCWEB.TBLPRO PS_INS_TRG". Information returned for the error
includes SQLCODE "-811", SQLSTATE "21000" and message tokens "".
SQLSTATE=09000
at
COM.ibm.db2.jdb c.app.SQLExcept ionGenerator.th row_SQLExceptio n(SQLExceptionG enerator.java(C ompiled
Code))
at
COM.ibm.db2.jdb c.app.SQLExcept ionGenerator.th row_SQLExceptio n(SQLExceptionG enerator.java(I nlined
Compiled Code))
at
COM.ibm.db2.jdb c.app.SQLExcept ionGenerator.ch eck_return_code (SQLExceptionGe nerator.java(Co mpiled
Code))
at
COM.ibm.db2.jdb c.app.DB2Prepar edStatement.exe cute2(DB2Prepar edStatement.jav a(Compiled
Code))
at
COM.ibm.db2.jdb c.app.DB2Prepar edStatement.exe cuteUpdate(DB2P reparedStatemen t.java:1642)
at
com.ibm.ws.rsad apter.jdbc.WSJd bcPreparedState ment.executeUpd ate(WSJdbcPrepa redStatement.ja va:462)
at
org.hibernate.j dbc.NonBatching Batcher.addToBa tch(NonBatching Batcher.java:22 )
at
org.hibernate.p ersister.entity .BasicEntityPer sister.insert(B asicEntityPersi ster.java:1853)
... 33 more
2006-08-31 16:33:35,156 ERROR ca.olgc.proline .dao.hibernate. CardDAOImpl
- <Exception encountered in saveOrUpdate()( ?.java:?)
I'm not sure what this is telling me. The Db2 Administration Guide
says:
sqlcode: -811
sqlstate: 21000
SQL0817N The SQL statement cannot be executed because the statement
will result in a prohibited update operation.
Explanation: The application attempted to execute an SQL statement that
would result in updates to user data or to the subsystem catalog. This
is prohibited for one of the following reasons:
The application is running as an IMS inquiry-only transaction.
The application is an IMS or CICS application that is attempting to
update data at a remote DBMS that does not support two-phase commit.
The application is attempting to update data at multiple locations and
one of the locations does not support two-phase commit.
These SQL statements include INSERT, UPDATE, DELETE, CREATE, ALTER,
DROP, GRANT, and REVOKE.
The statement cannot be executed.
User Response: If the application is running as an IMS inquiry-only
transaction, see your IMS system programmer about changing the
inquiry-only status of the transaction under which your application is
running.
If the IMS or CICS application is attempting a remote update, either
the application must be changed to run as a local application on the
server DBMS, or the server DBMS must be upgraded to support two-phase
commit.
If the application is attempting to update data at multiple locations,
either the application must be changed, or all DBMSs involved must be
upgraded to support two-phase commit.
There's only one database involved, Here's the SQL from the trigger:
CREATE TRIGGER TBLPROPS_ins_tr g
NO CASCADE
BEFORE INSERT ON TBLPROPS REFERENCING NEW AS T1 FOR EACH ROW
MODE DB2SQL
SET T1.PROD_GROUP_T YPE_CODE = 'LOTPRPICKS',
T1.PARTICIPANT_ DESC = (SELECT TEAM_NAME FROM TBLCARDS T2,
TBLTEAMNAMES T3
WHERE T3.TEAM_ABBR = T1.PARTICIPANT and
T3.SPORT_ID = T2.SPORT_ID and
T2.CARD_ID = T1.CARD_ID and
T2.LIST_NUMBER = T1.LIST_NUMBER)
Any help greatly appreciated. DB2 UDB 7.2.
Richard
following error:
2006-08-31 16:33:35,129 ERROR org.hibernate.u til.JDBCExcepti onReporter
- <[IBM][CLI Driver][DB2/6000] SQL0723N An error occurred in a
triggered SQL statement in trigger "OLGCWEB.TBLPRO PS_INS_TRG".
Information returned for the error includes SQLCODE "-811", SQLSTATE
"21000" and message tokens "". SQLSTATE=09000
(JDBCExceptionR eporter.java.lo gExceptions:72)
org.hibernate.e vent.def.Abstra ctFlushingEvent Listener - <Could not
synchronize database state with session>
(AbstractFlushi ngEventListener .java.performEx ecutions:277)
org.hibernate.e xception.Generi cJDBCException: could not insert:
[ca.olgc.proline .domain.PropDO]
at
org.hibernate.e xception.SQLSta teConverter.han dledNonSpecific Exception(SQLSt ateConverter.ja va:82)
at
org.hibernate.e xception.SQLSta teConverter.con vert(SQLStateCo nverter.java:70 )
at
org.hibernate.e xception.JDBCEx ceptionHelper.c onvert(JDBCExce ptionHelper.jav a:43)
at
org.hibernate.p ersister.entity .BasicEntityPer sister.insert(B asicEntityPersi ster.java:1869)
at
org.hibernate.p ersister.entity .BasicEntityPer sister.insert(B asicEntityPersi ster.java:2200)
at
org.hibernate.a ction.EntityIns ertAction.execu te(EntityInsert Action.java:46)
at org.hibernate.e ngine.ActionQue ue.execute(Acti onQueue.java:23 9)
at
org.hibernate.e ngine.ActionQue ue.executeActio ns(ActionQueue. java:223)
at
org.hibernate.e ngine.ActionQue ue.executeActio ns(ActionQueue. java:136)
at
org.hibernate.e vent.def.Abstra ctFlushingEvent Listener.perfor mExecutions(Abs tractFlushingEv entListener.jav a:274)
at
org.hibernate.e vent.def.Defaul tFlushEventList ener.onFlush(De faultFlushEvent Listener.java:2 7)
at org.hibernate.i mpl.SessionImpl .flush(SessionI mpl.java:730)
at
ca.olgc.proline .dao.hibernate. BaseHibernateDA O.batchSaveOrUp date(BaseHibern ateDAO.java(Com piled
Code))
at
ca.olgc.proline .facade.session beans.PropicksC ardBean.addOrUp dateProPicksCar d(PropicksCardB ean.java:233)
at
ca.olgc.proline .facade.session beans.EJSLocalS tatelessPropick sCard_464a659b. addOrUpdateProP icksCard(EJSLoc alStatelessProp icksCard_464a65 9b.java:159)
at
ca.olgc.proline .facade.Proline FacadeBean.addO rUpdateProPicks Card(ProlineFac adeBean.java:78 0)
at
ca.olgc.proline .facade.EJSRemo teStatelessProl ineFacade_cc482 172.addOrUpdate ProPicksCard(EJ SRemoteStateles sProlineFacade_ cc482172.java:5 67)
at
ca.olgc.proline .facade._Prolin eFacade_Stub.ad dOrUpdateProPic ksCard(_Proline Facade_Stub.jav a:1579)
at
ca.olgc.proline .messaging.help er.DatabaseUpda teHelper.add(Da tabaseUpdateHel per.java:106)
at
ca.olgc.proline .messaging.help er.DatabaseUpda teHelper.update DB(DatabaseUpda teHelper.java:5 0)
at
ca.olgc.proline .messaging.mdb. SportsRefresher Bean.updateDB(S portsRefresherB ean.java:81)
at
ca.olgc.proline .messaging.mdb. SportsRefresher Bean.onMessage( SportsRefresher Bean.java:65)
at
com.ibm.ejs.jms .listener.MDBWr apper$Priviledg edOnMessage.run (MDBWrapper.jav a:208)
at java.security.A ccessController .doPrivileged(N ative Method)
at
com.ibm.ejs.jms .listener.MDBWr apper.callOnMes sage(MDBWrapper .java:197)
at com.ibm.ejs.jms .listener.MDBWr apper.onMessage (MDBWrapper.jav a:175)
at com.ibm.mq.jms. MQSession.run(M QSession.java(C ompiled Code))
at com.ibm.ejs.jms .JMSSessionHand le.run(JMSSessi onHandle.java:9 23)
at
com.ibm.ejs.jms .listener.Serve rSession.connec tionConsumerOnM essage(ServerSe ssion.java:739)
at
com.ibm.ejs.jms .listener.Serve rSession.onMess age(ServerSessi on.java:524)
at
com.ibm.ejs.jms .listener.Serve rSession.dispat ch(ServerSessio n.java:491)
at java.lang.refle ct.Method.invok e(Native Method)
at
com.ibm.ejs.jms .listener.Serve rSessionDispatc her.dispatch(Se rverSessionDisp atcher.java:37)
at com.ibm.ejs.con tainer.MDBWrapp er.onMessage(MD BWrapper.java:9 1)
at com.ibm.ejs.con tainer.MDBWrapp er.onMessage(MD BWrapper.java:1 27)
at com.ibm.ejs.jms .listener.Serve rSession.run(Se rverSession.jav a:373)
at com.ibm.ws.util .ThreadPool$Wor ker.run(ThreadP ool.java:681)
Caused by:
COM.ibm.db2.jdb c.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0723N An
error occurred in a triggered SQL statement in trigger
"OLGCWEB.TBLPRO PS_INS_TRG". Information returned for the error
includes SQLCODE "-811", SQLSTATE "21000" and message tokens "".
SQLSTATE=09000
at
COM.ibm.db2.jdb c.app.SQLExcept ionGenerator.th row_SQLExceptio n(SQLExceptionG enerator.java(C ompiled
Code))
at
COM.ibm.db2.jdb c.app.SQLExcept ionGenerator.th row_SQLExceptio n(SQLExceptionG enerator.java(I nlined
Compiled Code))
at
COM.ibm.db2.jdb c.app.SQLExcept ionGenerator.ch eck_return_code (SQLExceptionGe nerator.java(Co mpiled
Code))
at
COM.ibm.db2.jdb c.app.DB2Prepar edStatement.exe cute2(DB2Prepar edStatement.jav a(Compiled
Code))
at
COM.ibm.db2.jdb c.app.DB2Prepar edStatement.exe cuteUpdate(DB2P reparedStatemen t.java:1642)
at
com.ibm.ws.rsad apter.jdbc.WSJd bcPreparedState ment.executeUpd ate(WSJdbcPrepa redStatement.ja va:462)
at
org.hibernate.j dbc.NonBatching Batcher.addToBa tch(NonBatching Batcher.java:22 )
at
org.hibernate.p ersister.entity .BasicEntityPer sister.insert(B asicEntityPersi ster.java:1853)
... 33 more
2006-08-31 16:33:35,156 ERROR ca.olgc.proline .dao.hibernate. CardDAOImpl
- <Exception encountered in saveOrUpdate()( ?.java:?)
I'm not sure what this is telling me. The Db2 Administration Guide
says:
sqlcode: -811
sqlstate: 21000
SQL0817N The SQL statement cannot be executed because the statement
will result in a prohibited update operation.
Explanation: The application attempted to execute an SQL statement that
would result in updates to user data or to the subsystem catalog. This
is prohibited for one of the following reasons:
The application is running as an IMS inquiry-only transaction.
The application is an IMS or CICS application that is attempting to
update data at a remote DBMS that does not support two-phase commit.
The application is attempting to update data at multiple locations and
one of the locations does not support two-phase commit.
These SQL statements include INSERT, UPDATE, DELETE, CREATE, ALTER,
DROP, GRANT, and REVOKE.
The statement cannot be executed.
User Response: If the application is running as an IMS inquiry-only
transaction, see your IMS system programmer about changing the
inquiry-only status of the transaction under which your application is
running.
If the IMS or CICS application is attempting a remote update, either
the application must be changed to run as a local application on the
server DBMS, or the server DBMS must be upgraded to support two-phase
commit.
If the application is attempting to update data at multiple locations,
either the application must be changed, or all DBMSs involved must be
upgraded to support two-phase commit.
There's only one database involved, Here's the SQL from the trigger:
CREATE TRIGGER TBLPROPS_ins_tr g
NO CASCADE
BEFORE INSERT ON TBLPROPS REFERENCING NEW AS T1 FOR EACH ROW
MODE DB2SQL
SET T1.PROD_GROUP_T YPE_CODE = 'LOTPRPICKS',
T1.PARTICIPANT_ DESC = (SELECT TEAM_NAME FROM TBLCARDS T2,
TBLTEAMNAMES T3
WHERE T3.TEAM_ABBR = T1.PARTICIPANT and
T3.SPORT_ID = T2.SPORT_ID and
T2.CARD_ID = T1.CARD_ID and
T2.LIST_NUMBER = T1.LIST_NUMBER)
Any help greatly appreciated. DB2 UDB 7.2.
Richard
Comment