DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
flynnjd5150



Group memberships:
Premium Members

Joined: 11 Nov 2009
Posts: 7

Points: 81

Post Posted: Tue Jun 15, 2010 11:29 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Unix
Additional info: DB2 API/Connector Stage
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



Group memberships:
Premium Members

Joined: 09 Mar 2007
Posts: 198

Points: 1697

Post Posted: Tue Jun 15, 2010 1:46 pm Reply with quote    Back to top    

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


Hi,

yes! Smile

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
Rate this response:  
flynnjd5150



Group memberships:
Premium Members

Joined: 11 Nov 2009
Posts: 7

Points: 81

Post Posted: Thu Jul 08, 2010 8:58 am Reply with quote    Back to top    

Thanks! This is very helpful
Rate this response:  
Not yet rated
mahipalreddy0007
Participant



Joined: 17 Dec 2010
Posts: 4
Location: bangalore
Points: 24

Post Posted: Tue Mar 12, 2013 6:33 am Reply with quote    Back to top    

its really help full

_________________
MAHIPAL
Rate this response:  
Not yet rated
rameshrr3



Group memberships:
Premium Members

Joined: 10 May 2004
Posts: 609
Location: BRENTWOOD, TN
Points: 6937

Post Posted: Fri Jun 15, 2018 12:32 pm Reply with quote    Back to top    

If your DB2 is on Z/OS , like we had, you need to use CALL SYSPROC.DSNUTILU() , with RUNSTATS TABLESPACE option.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours