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
Db2 RUNSTATS as after SQL
Moderators: chulett, rschirm, roy
Re: Db2 RUNSTATS as after SQL
Hi,flynnjd5150 wrote:Anyone every try to issue a runstats command as an after SQL?
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
-
- Premium Member
- Posts: 7
- Joined: Wed Nov 11, 2009 6:57 am
Re: Db2 RUNSTATS as after SQL
Thanks! This is very helpful
-
- Participant
- Posts: 4
- Joined: Fri Dec 17, 2010 11:21 am
- Location: bangalore