DSXchange

Anyone every try to issue a runstats command as an after SQL? DB2 issues the following log message when I try to do so, but the SQL command works just fine in a SQL editor.

refreshMQT,0: Warning: refreshPRSFMQT.refreshMQT: SQLExecDirect: DB2 warning/info message '[IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "TABLE" was found following "RUNSTATS ON ". Expected tokens may include: "JOIN". SQLSTATE=42601
'. [dscapiop.C:2012]

exact syntax executed is:
RUNSTATS ON TABLE DMACCT.PROD_RPT_ALL_CO ON KEY COLUMNS WITH DISTRIBUTION ON KEY COLUMNS;

I've tried using the DB2 API stage and the DB2 Connector

flynnjd5150 wrote:Anyone every try to issue a runstats command as an after SQL?

Hi,

yes!

The problem is you want to specify a AFTER SQL and runstats is a DB2 command and not SQL!
This is why it will not work the way you tried it.

But you can do it using a nice little stored procedure DB2 provides since version 9: admin_cmd
So the trick is to use a stored procedure which wraps the DB2 command.

Your after SQL should look like this:

call admin_cmd('runstats on table schema.objectname with distribution and detailed indexes all')

You could specify other options of cause.

kind regards
Michael

Thanks! This is very helpful

its really help full

If your DB2 is on Z/OS , like we had, you need to use CALL SYSPROC.DSNUTILU() , with RUNSTATS TABLESPACE option.