Calling DB2 RUNSTATS command in a script

dataservices job design
1

Is it possible to call the DB2 RUNSTATS (to collect statistics) command in a script?

I tried this:
SQL(‘datastore’, ‘RUNSTATS ON TABLE dbowner.table
WITH DISTRIBUTION ON ALL COLUMNS
AND INDEXES ALL
ALLOW WRITE ACCESS’);

…but got this error instead:

Function call <sql ( datastore, RUNSTATS ON dbowner.table
WITH DISTRIBUTION ON ALL COLUMNS
AND INDEXES ALL
ALLOW WRITE ACCESS )

failed, due to error <70401>: <ODBC data source error message for operation
: <[IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=S1010>.>.
|Session JOBNAME INFO: The above error occurs in the context <|Session JOBNAME|sql(…) Function Body|>.
|Session JOBNAME ODBC data source error message for operation : <[IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=S1010>.

Did I use the proper syntax for the SQL, though?

Thanks in advance for any help/suggestions.

bluesky :us: (BOB member since 2006-03-02)

2

I found two statements:

  1. Timeout parameter of the ODBC driver (IBM DB2 Connect) http://www.dbforums.com/archive/index.php/t-749524.html

  2. “i have reinstalled db2 v7.2 and my programs in vb6 execute very well, but first i uninstall my version of db2 v8.2 and remove two groups DB2USERS AND DB2ADMNS.”
    http://www.dbtalk.net/ibm-software-db2-udb/error-ibm-cli-driver-cli0125e-106100.html

Werner Daehn :de: (BOB member since 2004-12-17)

3

Thanks, Werner, for your research.

Unfortunately, I don’t have the option to reinstall db2 or remove any groups. The command is run against a small table (42 rows, 9 columns). Anyway, this command works when used on DB2 admin tools client. It seems to be a problem when running inside DI. I’m wondering if I’m using the proper DI syntax to call a RUNSTATS statement…it’s not different from all other DB2 SQL commands, is it? I’ve been able to call DROP/CREATE/ALTER statements within DI successfully so far. Hmm…

bluesky :us: (BOB member since 2006-03-02)

4

sql() function sends the string as given to the database. Looks fine. All in one line maybe?

Werner Daehn :de: (BOB member since 2004-12-17)

5

Yup, I already tried that. Didn’t work though (same error).

bluesky :us: (BOB member since 2006-03-02)