0

I have a stored procedure as follow:

CREATE OR REPLACE PROCEDURE "SMEADM"."COPY_TABLE_WITH_FILTER"
(IN IN_SOURCE_TABLE_NAME VARCHAR(50), IN IN_DEST_TABLE_NAME VARCHAR(50), IN IN_WHERE_CONDITION VARCHAR(1024))
NOT DETERMINISTIC
LANGUAGE SQL
SPECIFIC SQL_SME_DATA_RETENTION
BEGIN

  CALL SYSPROC.ADMIN_CMD('LOAD FROM (SELECT * FROM ' || IN_SOURCE_TABLE_NAME || ' WHERE ' || IN_WHERE_CONDITION || ' ) of cursor insert into ' || IN_DEST_TABLE_NAME || ' nonrecoverable');

END
@

And here is my java code to call this stored procedure:

public void copyData(String srcTableName, String bakTableName, String dateColName, String sqlDateStr) 
{
    String sqlWhereFilter = " date(" + dateColName + ") < date('" + sqlDateStr + "')";

    SimpleJdbcCall jdbcCall = new SimpleJdbcCall(this)
                .withSchemaName("SMEADM")
                .withProcedureName("COPY_TABLE_WITH_FILTER");

    SqlParameterSource in = new MapSqlParameterSource()
                                    .addValue("IN_SOURCE_TABLE_NAME", srcTableName)
                                    .addValue("IN_DEST_TABLE_NAME", bakTableName)
                                    .addValue( "IN_WHERE_CONDITION", sqlWhereFilter );

    jdbcCall.execute(in);
}

Everything is working fine in my local environment, SIT environment and UAT environment.

But I am getting a SqlException in production environment. Below is my stack trace:

[16-06-17 00:13:30] com.cv.ibs.batch.dao.impl.DataRetentionDAOImpl DEBUG: | Calling stored procedure [{call SMEADM.COPY_TABLE_WITH_FILTER(?, ?, ?)}]
[16-06-17 00:13:31] org.springframework.batch.core.step.AbstractStep ERROR: | Encountered an error executing the step
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call SMEADM.COPY_TABLE_WITH_FILTER(?, ?, ?)}]; SQL state [     ]; error code [-3015]; DB2 SQL error: SQLCODE: -3015, SQLSTATE:      , SQLERRMC: -551; nested exception is com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -3015, SQLSTATE:      , SQLERRMC: -551
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:969)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1003)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:391)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:354)
    at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:181)
    at com.cv.ibs.batch.dao.impl.DataRetentionDAOImpl.copyData(DataRetentionDAOImpl.java:38)
    at com.cv.ibs.batch.job.tasklet.DataRetentionCopyOldDataTasklet.execute(DataRetentionCopyOldDataTasklet.java:41)
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:386)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:264)
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:76)
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:367)
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:214)
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:143)
    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:250)
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195)
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:135)
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:61)
    at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:60)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:144)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:124)
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:135)
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:281)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:120)
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:48)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:114)
    at com.cv.ibs.batch.job.DataRetentionJob.execute(DataRetentionJob.java:56)
    at com.cv.ibs.batch.job.BatchProcess.run(BatchProcess.java:99)
    at com.cv.ibs.batch.job.launcher.DataRetentionLauncher.main(DataRetentionLauncher.java:36)
Caused by: 
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -3015, SQLSTATE:      , SQLERRMC: -551
    at com.ibm.db2.jcc.c.fg.d(fg.java:1340)
    at com.ibm.db2.jcc.b.gb.k(gb.java:351)
    at com.ibm.db2.jcc.b.gb.e(gb.java:96)
    at com.ibm.db2.jcc.b.w.e(w.java:83)
    at com.ibm.db2.jcc.b.vb.i(vb.java:164)
    at com.ibm.db2.jcc.c.fg.q(fg.java:1311)
    at com.ibm.db2.jcc.c.gg.d(gg.java:2386)
    at com.ibm.db2.jcc.c.hg.X(hg.java:115)
    at com.ibm.db2.jcc.c.hg.execute(hg.java:98)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
    at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:1005)
    at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:1)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:953)
    ... 28 more
[main] ERROR c.cv.ibs.batch.job.DataRetentionJob - [BATCH] Error on executing the batch job
com.cv.ibs.cib.bulkpayment.BatchProcessException: Error in spring batch process, please checklog file and BatchFailedDetails table.
    at com.cv.ibs.batch.job.DataRetentionJob.execute(DataRetentionJob.java:60) ~[com.cv.ibs.batch.jar:na]
    at com.cv.ibs.batch.job.BatchProcess.run(BatchProcess.java:99) [com.cv.ibs.batch.jar:na]
    at com.cv.ibs.batch.job.launcher.DataRetentionLauncher.main(DataRetentionLauncher.java:36) [com.cv.ibs.batch.jar:na]

I am suspecting this exception is cause by some access I did not grant to the db user. Hence, I try to take out some access (for example load on database and so on) from the db user in SIT environment, trying to hitting the same exception. However, in SIT, I am hitting other error, which is clearly tell me what is the root cause, not error code -3015

Trying to get some hint from Google but failed. I think I am miss out some point/steps to find the root cause. Kindly advise.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Panadol Chong
  • 1,793
  • 13
  • 54
  • 119
  • Your procedure is open to SQL Injection; granted, something like this would require dynamic SQL, but you should validate that the table names and column are both actual column and table names (probably from within the stored procedure). It would be best to pass the date parameter as an actual date type (preferably something out of the new `java.time` library). You probably want to explicitly set your concurrency level. Also, if possible, don't cast the comparison column to date in SQL, since that may be causing the system to ignore indices. – Clockwork-Muse Jul 22 '16 at 00:28

1 Answers1

0

You can use the DB2 Command Line for getting detailed Information on an Error, just use the SQL-Code from the Exception, remove the minus-sign and pad it with Zeros (at the beginning, if its shorter that 4):

db2 ? sql3015

SQL3015N An SQL error "<sqlcode>" occurred during processing.

Explanation:

An SQL error occurred during the utility call.

The utility stops processing.

User response:

Look at the SQLCODE (message number) in the message for more information. Make changes and resubmit the command.

I assume the SQLERRMC: -551 is the Error that happend in your Stored Procedure, which is:

db2 ? sql0551

SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "<authorization-ID>". Operation: "<operation>". Object: "<object-name>".

More on that Error here.

Sadly, the initial Message dosen't give much Information about the authorization-ID and the operation etc.

Catch the SqlException yourself in your code and see, if getNextException() (javadoc) will give you more info.

hinneLinks
  • 3,673
  • 26
  • 40