Skip to main content

This content is closed to future replies and is no longer being maintained or updated.
Links may no longer function. If you have a similar request, please write a new post.

Question

 Bin Xu (GAVINHSU)
Mediatek Inc. China

Mediatek Inc. China
CN
GAVINHSU Member since 2011 37 posts
Mediatek Inc. China
Posted: Nov 10, 2015
Last activity: Nov 12, 2015
Posted: 10 Nov 2015 4:51 EST
Last activity: 12 Nov 2015 1:23 EST
Closed

DB2 SQL Error: SQLState 42884, Error code -440 after upgraded to 7 (split schemas)
Brief Description
DB2 SQL Error: SQLState 42884, Error code -440 after upgraded to 7
Detailed Description
We have upgraded our PRPC from 6.1 SP2 to 7.1.8 with split schemas.
The server can be started successfully when we using PegaRULES as both rule and data sources in prconfig.
But when we try to use PegaDATA as data source of data in prconfig, we've got "DB2 SQL Error: SQLCODE=-1477, SQLSTATE=55019" when server started.
Steps to Reproduce
Config prconfig.xml as below, start the server and errors come out.<env name="database/databases/PegaRULES/dataSource" value="java:comp/env/jdbc/PegaRULES"/>
Show More
Brief Description
DB2 SQL Error: SQLState 42884, Error code -440 after upgraded to 7
Detailed Description
We have upgraded our PRPC from 6.1 SP2 to 7.1.8 with split schemas.
The server can be started successfully when we using PegaRULES as both rule and data sources in prconfig.
But when we try to use PegaDATA as data source of data in prconfig, we've got "DB2 SQL Error: SQLCODE=-1477, SQLSTATE=55019" when server started.
Steps to Reproduce
Config prconfig.xml as below, start the server and errors come out.<env name="database/databases/PegaRULES/dataSource" value="java:comp/env/jdbc/PegaRULES"/>
<env name="database/databases/PegaDATA/dataSource" value="java:comp/env/jdbc/PegaDATA"/>
Error Message
2015-11-06 09:00:59,069 [aRULES LicenseDaemon] [ STANDARD] [ ] [ ] (l.access.ConnectionManagerImpl) ERROR - Not returning connection 4 for database "pegadata" to the pool as it previously encountered the following error
User ID:
Last SQL: {call DB2BPMD.sppr_aggregate_usage ()}
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=MIDNIGHT_SECONDS;FUNCTION, DRIVER=3.61.75
at com.ibm.db2.jcc.am.ed.a(ed.java:676)
at com.ibm.db2.jcc.am.ed.a(ed.java:60)
at com.ibm.db2.jcc.am.ed.a(ed.java:127)
at com.ibm.db2.jcc.am.gn.b(gn.java:2239)
at com.ibm.db2.jcc.am.gn.c(gn.java:2222)
at com.ibm.db2.jcc.t4.cb.k(cb.java:369)
at com.ibm.db2.jcc.t4.cb.e(cb.java:97)
at com.ibm.db2.jcc.t4.q.e(q.java:81)
at com.ibm.db2.jcc.t4.rb.k(rb.java:160)
at com.ibm.db2.jcc.am.gn.jb(gn.java:2188)
at com.ibm.db2.jcc.am.hn.b(hn.java:3917)
at com.ibm.db2.jcc.am.in.gc(in.java:113)
at com.ibm.db2.jcc.am.in.execute(in.java:96)
at weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:105)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:581)
at com.pega.pegarules.data.internal.access.SQLGenerator.callStoredProc(SQLGenerator.java:3276)
at com.pega.pegarules.data.internal.access.DatabaseImpl.callStoredProc(DatabaseImpl.java:10165)
at com.pega.pegarules.data.internal.access.DatabaseImpl.callAggregateUsageSP(DatabaseImpl.java:10230)
at com.pega.pegarules.monitor.internal.license.LicenseDaemonImpl.callUsageAggregationStoredProcedure(LicenseDaemonImpl.java:351)
at com.pega.pegarules.monitor.internal.license.LicenseDaemonImpl.run(LicenseDaemonImpl.java:313)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1259)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:1008)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:828)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:795)
at com.pega.pegarules.monitor.internal.license.LicenseDaemonImpl.takeSnapshot(LicenseDaemonImpl.java:271)
at com.pega.pegarules.monitor.internal.license.LicenseDaemonImpl.run(LicenseDaemonImpl.java:228)
at java.lang.Thread.run(Thread.java:744)
2015-11-06 09:00:59,070 [aRULES LicenseDaemon] [ STANDARD] [ ] [ ] (rnal.license.LicenseDaemonImpl) ERROR - problem while running the procedure in License Daemon:com.pega.pegarules.pub.database.DatabaseException: Database-General Problem encountered executing a stored procedure -440 42884 DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=MIDNIGHT_SECONDS;FUNCTION, DRIVER=3.61.75
From: (B1B310D70712C1C2B34456F745A746A59:(License Daemon))
SQL: {call DB2BPMD.sppr_aggregate_usage ()}
SQL Inserts:Caused by SQL Problems.
Problem #1, SQLState 42884, Error code -440: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=MIDNIGHT_SECONDS;FUNCTION, DRIVER=3.61.75
Properties of PegaDATA datasource:
user=db2bpmd
progressiveLocators=2
currentFunctionPath=SYSIBM,SYSFUNC,DB2BPMD
fullyMaterializeInputStreams=true
useJDBC4ColumnNameAndLabelSemantics=2
progressiveStreaming=2
databaseName=BPMD2
driverType=4
fullyMaterializeLobData=true

Show Less
To see attachments, please log in.
Installation and Deployment
Updates
Data Integration

Posted: 9 years ago
Posted: 10 Nov 2015 5:09 EST
Rajiv Nistala (nistr) Senior Manager - Engineering
Pegasystems
IN

<env name="database/databases/PegaRULES/dataSource" value="java:comp/env/jdbc/PegaRULES"/>
<env name="database/databases/PegaDATA/dataSource" value="java:comp/env/jdbc/PegaDATA"/>

 

We recommend to use the same JNDI datasource for both PegaRULES and PegaDATA so that we share the connection pool for both. Any specific reason why we are using two different connection pools?

To see attachments, please log in.
Posted: 9 years ago
Posted: 11 Nov 2015 21:51 EST
Bin Xu (GAVINHSU)
Mediatek Inc. China

Mediatek Inc. China
CN

We want to share the PegaDATA database with other application servers.

So we need use PegaRULES at server A and PeagDATA at sever B.

To see attachments, please log in.
Posted: 9 years ago
Posted: 10 Nov 2015 5:27 EST
Gowrishankar Ramasamy (GowrishankarR29)
Citicorp Services India Private Limited

Citicorp Services India Private Limited
IN

Hi Gavin,

          I believe the support article in the below PDN link would help.

          https://community.pega.com/support/support-articles/sql-error-when-server-started

          Please let me know if this helps.

Thanks,

Gowrishankar.

To see attachments, please log in.
Posted: 9 years ago
Posted: 12 Nov 2015 1:21 EST
Bin Xu (GAVINHSU)
Mediatek Inc. China

Mediatek Inc. China
CN

Hi Gowrishankar,

Thanks a lot for your kindly reply.

We've tried with the post you've recommended, it doesn't work.

We just found that even with the same JNDI name in prconfig, we still got -440 SQL error with TIMESTAMPDIFF function:

Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=TIMESTAMPDIFF;FUNCTION, DRIVER=3.61.75

Connection Pool properties of PegaRULES data source as below:

user=db2bpmd

progressiveLocators=2

driverType=4

progressiveStreaming=2

databaseName=BPMD2

fullyMaterializeInputStreams=true

fullyMaterializeLobData=true

useJDBC4ColumnNameAndLabelSemantics=2

currentFunctionPath=SYSIBM,SYSFUNC,BPMRULED,DB2BPMD

currentSchema=DB2BPMD

And at the same time, we also got -1477 SQL error with PR_HOURLY_USAGE:

Problem #1, SQLState 55019, Error code -1477: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-1477, SQLSTATE=55019, SQLERRMC=DB2BPMD.PR_HOURLY_USAGE

To see attachments, please log in.
Posted: 9 years ago
Posted: 12 Nov 2015 1:23 EST
Bin Xu (GAVINHSU)
Mediatek Inc. China

Mediatek Inc. China
CN

We just found that even with the same JNDI name in prconfig, we still got -440 SQL error with TIMESTAMPDIFF function:

Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=TIMESTAMPDIFF;FUNCTION, DRIVER=3.61.75

prconfig.xml was configured as below:

     <env name="identification/systemname" value="prpc"/>

     <env name="initialization/settingsource" value="merged" />

     <env name="database/databases/PegaRULES/dataSource" value="java:comp/env/jdbc/PegaRULES"/>

     <env name="database/databases/PegaDATA/dataSource" value="java:comp/env/jdbc/PegaRULES"/>

Connection Pool properties of PegaRULES data source as below:

user=db2bpmd

progressiveLocators=2

driverType=4

progressiveStreaming=2

databaseName=BPMD2

fullyMaterializeInputStreams=true

fullyMaterializeLobData=true

useJDBC4ColumnNameAndLabelSemantics=2

currentFunctionPath=SYSIBM,SYSFUNC,BPMRULED,DB2BPMD

currentSchema=DB2BPMD

And at the same time, we also got -1477 SQL error with PR_HOURLY_USAGE:

Problem #1, SQLState 55019, Error code -1477: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-1477, SQLSTATE=55019, SQLERRMC=DB2BPMD.PR_HOURLY_USAGE

To see attachments, please log in.

We'd prefer it if you saw us at our best.

Pega Collaboration Center has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice