DSXchange

Hello All

I'm trying to fire the following RUNSTATS command from SQL>After in DB2 API stage:

RUNSTATS on BKH.CNTAC with distribution and sampled detailed indexes all allow write access;

Same command executes successfully when fired from DB2 client. But it's throwing warnings when run from DataStage:

db2_SALES_CNTAC_TRAN_ins,0: Warning: djp_SALES_CNTAC_TRAN.db2_SALES_CNTAC_TRAN_ins: SQLExecDirect: Error executing statement '
RUNSTATS on table BKH.CNTAC with distribution and sampled detailed indexes all allow write access'. See following DB2 message for details.

db2_SALES_CNTAC_TRAN_ins,0: Warning: djp_SALES_CNTAC_TRAN.db2_SALES_CNTAC_TRAN_ins: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "table" was found following "RUNSTATS on ". Expected tokens may include: "JOIN". SQLSTATE=42601


I don't think anything wrong with the command syntax since I'm able to run it from DB2 client.

Any help would be appreciated. Thanks!

You can only issue commands from DB2 API stage that is valid SQL and can be run from any SQL-editor. RUNSTATS in DB2 is a database command and therefore it cannot be run in db2 api stage.

Use db2 enterprise stage open and close command instead. It can issue sql and databse commands.

You can only issue commands from DB2 API stage that is valid SQL and can be run from any SQL-editor. RUNSTATS in DB2 is a database command and therefore it cannot be run in db2 api stage.

Use db2 enterprise stage open and close command instead. It can issue sql and databse commands.

Thanks stefanfrost1 for your response.
Is it a limitation of DB2 API stage or it is supposed to behave this way?

koolnitz wrote:Thanks stefanfrost1 for your response.
Is it a limitation of DB2 API stage or it is supposed to behave this way?

It's supposed to behave like that... runstats is not SQL. It's similar to Oracle and SQL*Plus, you can execute things in SQL*Plus which will throw errors at you in the Oracle Stage.

Hmm, so as a workaround I have written a small script that fires RUNSTATS statements, and calling this script from Execute Command in job sequence.

Thanks for the info, anyway!