Error running RUNSTATS from DB2 API stage

koolnitz · Post by **koolnitz** » Tue Nov 27, 2007 3:11 am

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!

stefanfrost1 · Post by **stefanfrost1** » Tue Nov 27, 2007 5:08 am

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.

stefanfrost1 · Post by **stefanfrost1** » Tue Nov 27, 2007 5:09 am

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.

koolnitz · Post by **koolnitz** » Tue Nov 27, 2007 6:23 am

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

ogmios · Post by **ogmios** » Tue Nov 27, 2007 7:24 am

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.

koolnitz · Post by **koolnitz** » Tue Nov 27, 2007 7:33 am

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!