Thursday, November 6, 2008

SQLException occured in the SQLPhysicalConnection.select(): | Table or View Name = F98751 - Data Source[0] = SSEDS com.microsoft.sqlserver.jdbc.SQ

SELECT FSJDEVERS,FSMRGMOD,FSMRGOPT,FSFFU1,FSFFU2,FSFDABLOB FROM dbo.F98751DV900FB WHERE (FSRCRDTP = ? )
05 Nov 2008 01:58:17,903 [APP ] - [JDBJ] SQL statement parameter marker values are :
05 Nov 2008 01:58:17,903 [APP ] - [JDBJ] Param1 : 2_Types.NUMERIC,
05 Nov 2008 01:58:27,949 [SEVERE] - [JDBJ] SQLException occured in the SQLPhysicalConnection.select(): | Table or View Name = F98751 - Data Source[0] = SSEDS com.microsoft.sqlserver.jdbc.SQLServerException: The query was canceled.
com.microsoft.sqlserver.jdbc.SQLServerException: The query was canceled.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.complete(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at com.jdedwards.database.impl.physical.JDBPreparedStatement.executeMSSQLQuery(Unknown Source)
at com.jdedwards.database.impl.sql.SQLPhysicalConnection.select(Unknown Source)
at com.jdedwards.database.impl.retry.JDBRetryableSelect.lowLevelAttempt(Unknown Source)
at com.jdedwards.database.impl.retry.JDBRetryableReadOperation.attempt(Unknown Source)
at com.jdedwards.base.util.Retryer.retry(Unknown Source)
at com.jdedwards.database.impl.retry.JDBRetryAccess.select(Unknown Source)
at com.jdedwards.database.impl.condensed.JDBCondensedAccessDelegate.select(Unknown Source)
at com.jdedwards.database.impl.transform.JDBTransformAccess.select(Unknown Source)
at com.jdedwards.database.impl.condensed.JDBCondensedAccessDelegate.select(Unknown Source)
at com.jdedwards.database.impl.condensed.JDBCondensedAccessDelegate.select(Unknown Source)
at com.jdedwards.database.impl.trigger.JDBTriggerAccess.select(Unknown Source)
at com.jdedwards.database.impl.condensed.JDBCondensedAccessDelegate.select(Unknown Source)
at com.jdedwards.database.impl.logical.JDBLogicalConnection.select(Unknown Source)
at com.jdedwards.database.impl.condensed.JDBCondensedAccessDelegate.select(Unknown Source)
at com.jdedwards.database.impl.security.JDBSecurityAccess.select(Unknown Source)
at com.jdedwards.database.impl.condensed.JDBCondensedAccessDelegate.select(Unknown Source)
at com.jdedwards.database.impl.businessview.JDBBusinessViewAccess.select(Unknown Source)
at com.jdedwards.database.impl.condensed.JDBCondensedAccessDelegate.select(Unknown Source)
at com.jdedwards.database.impl.usage.JDBUsageTrackingAccess.select(Unknown Source)
at com.jdedwards.database.impl.condensed.JDBCondensedAccessDelegate.select(Unknown Source)
at com.jdedwards.database.impl.condensed.JDBCondensedAccessDelegate.select(Unknown Source)
at com.jdedwards.database.impl.condensed.JDBCondensedAccessExpander.select(Unknown Source)
at com.peoplesoft.pt.e1.metadata.dataAccess.SpecAccessImpl.selectTemplate(Unknown Source)
at com.peoplesoft.pt.e1.metadata.dataAccess.SpecAccessImpl.select(Unknown Source)
at com.peoplesoft.pt.e1.metadata.dataAccess.SpecAccessImpl.select(Unknown Source)
at com.jdedwards.runtime.generator.engine.MD.AppGenEngineMD.buildFormSpecLists(Unknown Source)
at com.jdedwards.runtime.generator.engine.AppGenEngine.buildGenSet(Unknown Source)
at com.jdedwards.runtime.generator.engine.GeneratorEngine.startGeneration(Unknown Source)
at com.jdedwards.runtime.generator.view.GenerateAllView.run(Unknown Source)
at java.lang.Thread.run(Thread.java:799)
05 Nov 2008 01:58:27,965 [WARN ] - [RUNTIME] General Error during generation. Error: null
Scenario
SELECT query issued against a table threw an exception after 10 seconds.
The problem is due to long time taken by the database to process the query.The actual solution is to analyze the reason for slowness and accordingly finetune the database.However a work around is possible with the below said approach.
Approach
Execute the SELECT Query directly using sqlplus or query analyzer and observe the total time taken.
Now increase the
msSQLQueryTimeout under the JDBj-RUNTIME PROPERTIES of the JDBJ.ini to a value higher than the time taken for direct execution.

No comments: