Db2 RUNSTATS as after SQL

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
flynnjd5150
Premium Member
Premium Member
Posts: 7
Joined: Wed Nov 11, 2009 6:57 am

Db2 RUNSTATS as after SQL

Post by flynnjd5150 »

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
MT
Premium Member
Premium Member
Posts: 198
Joined: Fri Mar 09, 2007 3:51 am

Re: Db2 RUNSTATS as after SQL

Post by MT »

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
flynnjd5150
Premium Member
Premium Member
Posts: 7
Joined: Wed Nov 11, 2009 6:57 am

Re: Db2 RUNSTATS as after SQL

Post by flynnjd5150 »

Thanks! This is very helpful
mahipalreddy0007
Participant
Posts: 4
Joined: Fri Dec 17, 2010 11:21 am
Location: bangalore

Re: Db2 RUNSTATS as after SQL

Post by mahipalreddy0007 »

its really help full
MAHIPAL
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

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