By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,286 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,286 IT Pros & Developers. It's quick & easy.

RUNSTATS from SQL for UDB

P: n/a
We are on 8.1.6 currently on AIX. Has anyone been able to come up
with a solution that allows the ability to do a RUNSTATS from SQL
prompt instead of the command line? I see lots of posts about writing
a C procedure or something along those lines. Has anyone done this?
Does IBM have the proc already built? Does this get easier in 8.2
with extended support for the SQL procedural language?
Spencer
Nov 12 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
On Wed, 22 Sep 2004 07:31:14 -0700, Spencer wrote:
Has anyone been able to come up with a
solution that allows the ability to do a RUNSTATS from SQL prompt instead
of the command line?


Doesn't a command like this work?:

db2 'runstats on table db2inst1.tablename'

(Exchange "db2inst1" with yourr schema name.)

--
Greetings from Troels Arvin, Copenhagen, Denmark

Nov 12 '05 #2

P: n/a
That is from the db2 command line interface not the sql interface. I
need to be able to execute the runstats from a sql interface because
of security setup on a particular application. The application can
make an odbc connection to the database to issue sql commands it
cannot issue command line interface commands.
Spencer
Troels Arvin <tr****@arvin.dk> wrote in message news:<pa****************************@arvin.dk>...
On Wed, 22 Sep 2004 07:31:14 -0700, Spencer wrote:
Has anyone been able to come up with a
solution that allows the ability to do a RUNSTATS from SQL prompt instead
of the command line?


Doesn't a command like this work?:

db2 'runstats on table db2inst1.tablename'

(Exchange "db2inst1" with yourr schema name.)

Nov 12 '05 #3

P: n/a
Spencer wrote:
That is from the db2 command line interface not the sql interface. I
need to be able to execute the runstats from a sql interface because
of security setup on a particular application. The application can
make an odbc connection to the database to issue sql commands it
cannot issue command line interface commands.


Why don't you write a stored procedure that uses the "db2Runstats" API call
internally? Then you could simply do this:

db2 "CALL myRunstatsProc(...)"

and that is normal SQL which can be run through ODBC.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4

P: n/a
Right....like I said has anyone done this? Any good info out there on
the DB2 SQL Procedural Language. Everything I find is extremely
vague.
Spencer
Knut Stolze <st****@de.ibm.com> wrote in message news:<ci**********@fsuj29.rz.uni-jena.de>...
Spencer wrote:
That is from the db2 command line interface not the sql interface. I
need to be able to execute the runstats from a sql interface because
of security setup on a particular application. The application can
make an odbc connection to the database to issue sql commands it
cannot issue command line interface commands.


Why don't you write a stored procedure that uses the "db2Runstats" API call
internally? Then you could simply do this:

db2 "CALL myRunstatsProc(...)"

and that is normal SQL which can be run through ODBC.

Nov 12 '05 #5

P: n/a
Spencer wrote:
Right....like I said has anyone done this?
I haven't, but I don't see any particular problem. Do you need some help
coding this?
Any good info out there on
the DB2 SQL Procedural Language. Everything I find is extremely
vague.


Hmmm... did you have a look at the SQL Reference or do you know about this
book: http://www.informit.com/title/0131007726

p.s: SQL/PL won't help you with invoking the db2Runstats API.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #6

P: n/a
Sure if you want to code it for me that would be great! So if I am
not writing it in SQL/PL then I suppose it is C? Any primers in
writing stored proc's for DB2. I have not written any for DB2.
spencer

Knut Stolze <st****@de.ibm.com> wrote in message news:<ci**********@fsuj29.rz.uni-jena.de>...
Spencer wrote:
Right....like I said has anyone done this?


I haven't, but I don't see any particular problem. Do you need some help
coding this?
Any good info out there on
the DB2 SQL Procedural Language. Everything I find is extremely
vague.


Hmmm... did you have a look at the SQL Reference or do you know about this
book: http://www.informit.com/title/0131007726

p.s: SQL/PL won't help you with invoking the db2Runstats API.

Nov 12 '05 #7

P: n/a
Spencer wrote:
Sure if you want to code it for me that would be great! So if I am
not writing it in SQL/PL then I suppose it is C? Any primers in
writing stored proc's for DB2. I have not written any for DB2.


Sorry that it took so long...

Here is the procedure code. It is pretty basic and does not contain any
serious error handling. But you should be able to build on that.
--------------------------------------
#include <sql.h>
#include <sqludf.h>
#include <db2ApiDf.h>

int runstats_proc(
SQLUDF_VARCHAR *tableName,
SQLUDF_NULLIND *tableName_ind,
SQLUDF_TRAIL_ARGS)
{
SQL_API_RC rc = SQL_RC_OK;
db2RunstatsData data;
struct sqlca sqlca;

if (SQLUDF_NULL(tableName_ind)) {
goto cleanup;
}

/* initialize data structures */
data.iSamplingOption = 0;
data.piTablename = (unsigned char *)tableName;
data.piColumnList = NULL;
data.piColumnDistributionList = NULL;
data.piColumnGroupList = NULL;
data.piIndexList = NULL;
data.iRunstatsFlags = DB2RUNSTATS_ALL_COLUMNS |
DB2RUNSTATS_DISTRIBUTION | DB2RUNSTATS_ALL_INDEXES |
DB2RUNSTATS_EXT_INDEX | DB2RUNSTATS_ALLOW_READ;
data.iNumColumns = 0;
data.iNumColdist = 0;
data.iNumColGroups = 0;
data.iNumIndexes = 0;
data.iParallelismOption = 0;
data.iTableDefaultFreqValues = -1; /* use default */
data.iTableDefaultQuantiles = -1; /* use default */
data.iUtilImpactPriority = 0;
data.iSamplingRepeatable = 0; /* unused */
memset(&sqlca, 0x00, sizeof sqlca);

rc = db2Runstats(db2Version820, &data, &sqlca);
if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
memset(SQLUDF_STATE, "38XXX", SQLUDF_SQLSTATE_LEN);
sprintf(SQLUDF_MSGTX, "Error code %d encountered",
rc == SQL_RC_OK ? SQLCODE : rc);
goto cleanup;
}

cleanup:
return SQLZ_DISCONNECT_PROC;
}
---------------------------------------------------
And to register the procedure in the database, run the following SQL
statement:
---------------------------------------------------
CREATE PROCEDURE runstats(IN tableName VARCHAR(256))
SPECIFIC runstats
DYNAMIC RESULT SETS 0
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
LANGUAGE C
EXTERNAL NAME 'runstats_proc!runstats_proc'
NOT FENCED THREADSAFE
NO EXTERNAL ACTION
PARAMETER STYLE SQL;
---------------------------------------------------

Then I ran the procedure on an existing table:

db2 "call runstats('stolze.a')"

and verified that the statistics were indeed updated:
---------------------------------------------------
$ db2 "select * from sysstat.tables where tabname in ('A', 'B')"

TABSCHEMA TABNAME CARD NPAGES FPAGES OVERFLOW CLUSTERED ACTIVE_BLOCKS
--------- ------- ---- ------ ------ -------- --------- -------------
STOLZE A 0 0 1 0 - 0
STOLZE B -1 -1 -1 -1 - -1

2 record(s) selected.
You might also want to read up on the "db2Runstats" API. For example, that
API causes a few locks to be taken and it is suggested that you run a
COMMIT after the API call. The current procedure code lets the client
application do the commit.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #8

P: n/a
Knut Stolze wrote:
Spencer wrote:
Sure if you want to code it for me that would be great! So if I am
not writing it in SQL/PL then I suppose it is C? Any primers in
writing stored proc's for DB2. I have not written any for DB2.


Sorry that it took so long...

Here is the procedure code. It is pretty basic and does not contain any
serious error handling. But you should be able to build on that.
--------------------------------------
#include <sql.h>
#include <sqludf.h>
#include <db2ApiDf.h>

int runstats_proc(
SQLUDF_VARCHAR *tableName,
SQLUDF_NULLIND *tableName_ind,
SQLUDF_TRAIL_ARGS)
{
SQL_API_RC rc = SQL_RC_OK;
db2RunstatsData data;
struct sqlca sqlca;

if (SQLUDF_NULL(tableName_ind)) {
goto cleanup;
}

/* initialize data structures */
data.iSamplingOption = 0;
data.piTablename = (unsigned char *)tableName;
data.piColumnList = NULL;
data.piColumnDistributionList = NULL;
data.piColumnGroupList = NULL;
data.piIndexList = NULL;
data.iRunstatsFlags = DB2RUNSTATS_ALL_COLUMNS |
DB2RUNSTATS_DISTRIBUTION | DB2RUNSTATS_ALL_INDEXES |
DB2RUNSTATS_EXT_INDEX | DB2RUNSTATS_ALLOW_READ;
data.iNumColumns = 0;
data.iNumColdist = 0;
data.iNumColGroups = 0;
data.iNumIndexes = 0;
data.iParallelismOption = 0;
data.iTableDefaultFreqValues = -1; /* use default */
data.iTableDefaultQuantiles = -1; /* use default */
data.iUtilImpactPriority = 0;
data.iSamplingRepeatable = 0; /* unused */
memset(&sqlca, 0x00, sizeof sqlca);

rc = db2Runstats(db2Version820, &data, &sqlca);
if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
memset(SQLUDF_STATE, "38XXX", SQLUDF_SQLSTATE_LEN);
sprintf(SQLUDF_MSGTX, "Error code %d encountered",
rc == SQL_RC_OK ? SQLCODE : rc);
goto cleanup;
}

cleanup:
return SQLZ_DISCONNECT_PROC;
}
---------------------------------------------------

Oh, I forgot: you can compile the procedure using the bldrtn script from
sqllib/samples/c

cd sqllib/samples/c
../bldrtn runstats_proc

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #9

P: n/a
Hello Knut;
I just wanted to thank you for this sample. We have successfully
implemented it and it opens up lots of potential for API-related procs.
So - thanks!

Pete H
Knut Stolze wrote:
Knut Stolze wrote:
Spencer wrote:
Sure if you want to code it for me that would be great! So if I am not writing it in SQL/PL then I suppose it is C? Any primers in
writing stored proc's for DB2. I have not written any for DB2.
Sorry that it took so long...

Here is the procedure code. It is pretty basic and does not contain any serious error handling. But you should be able to build on that.
--------------------------------------
#include <sql.h>
#include <sqludf.h>
#include <db2ApiDf.h>

int runstats_proc(
SQLUDF_VARCHAR *tableName,
SQLUDF_NULLIND *tableName_ind,
SQLUDF_TRAIL_ARGS)
{
SQL_API_RC rc = SQL_RC_OK;
db2RunstatsData data;
struct sqlca sqlca;

if (SQLUDF_NULL(tableName_ind)) {
goto cleanup;
}

/* initialize data structures */
data.iSamplingOption = 0;
data.piTablename = (unsigned char *)tableName;
data.piColumnList = NULL;
data.piColumnDistributionList = NULL;
data.piColumnGroupList = NULL;
data.piIndexList = NULL;
data.iRunstatsFlags = DB2RUNSTATS_ALL_COLUMNS |
DB2RUNSTATS_DISTRIBUTION | DB2RUNSTATS_ALL_INDEXES |
DB2RUNSTATS_EXT_INDEX | DB2RUNSTATS_ALLOW_READ;
data.iNumColumns = 0;
data.iNumColdist = 0;
data.iNumColGroups = 0;
data.iNumIndexes = 0;
data.iParallelismOption = 0;
data.iTableDefaultFreqValues = -1; /* use default */
data.iTableDefaultQuantiles = -1; /* use default */
data.iUtilImpactPriority = 0;
data.iSamplingRepeatable = 0; /* unused */
memset(&sqlca, 0x00, sizeof sqlca);

rc = db2Runstats(db2Version820, &data, &sqlca);
if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
memset(SQLUDF_STATE, "38XXX", SQLUDF_SQLSTATE_LEN);
sprintf(SQLUDF_MSGTX, "Error code %d encountered",
rc == SQL_RC_OK ? SQLCODE : rc);
goto cleanup;
}

cleanup:
return SQLZ_DISCONNECT_PROC;
}
---------------------------------------------------

Oh, I forgot: you can compile the procedure using the bldrtn script

from sqllib/samples/c

cd sqllib/samples/c
./bldrtn runstats_proc

--
Knut Stolze
Information Integration
IBM Germany / University of Jena


Nov 12 '05 #10

P: n/a
peteh wrote:
Hello Knut;
I just wanted to thank you for this sample. We have successfully
implemented it and it opens up lots of potential for API-related procs.
So - thanks!

Pete H
Knut Stolze wrote:
Knut Stolze wrote:

Spencer wrote:
Sure if you want to code it for me that would be great! So if I
am
not writing it in SQL/PL then I suppose it is C? Any primers in
writing stored proc's for DB2. I have not written any for DB2.

Sorry that it took so long...

Here is the procedure code. It is pretty basic and does not
contain any
serious error handling. But you should be able to build on that.
--------------------------------------
#include <sql.h>
#include <sqludf.h>
#include <db2ApiDf.h>

int runstats_proc(
SQLUDF_VARCHAR *tableName,
SQLUDF_NULLIND *tableName_ind,
SQLUDF_TRAIL_ARGS)
{
SQL_API_RC rc = SQL_RC_OK;
db2RunstatsData data;
struct sqlca sqlca;

if (SQLUDF_NULL(tableName_ind)) {
goto cleanup;
}

/* initialize data structures */
data.iSamplingOption = 0;
data.piTablename = (unsigned char *)tableName;
data.piColumnList = NULL;
data.piColumnDistributionList = NULL;
data.piColumnGroupList = NULL;
data.piIndexList = NULL;
data.iRunstatsFlags = DB2RUNSTATS_ALL_COLUMNS |
DB2RUNSTATS_DISTRIBUTION | DB2RUNSTATS_ALL_INDEXES |
DB2RUNSTATS_EXT_INDEX | DB2RUNSTATS_ALLOW_READ;
data.iNumColumns = 0;
data.iNumColdist = 0;
data.iNumColGroups = 0;
data.iNumIndexes = 0;
data.iParallelismOption = 0;
data.iTableDefaultFreqValues = -1; /* use default */
data.iTableDefaultQuantiles = -1; /* use default */
data.iUtilImpactPriority = 0;
data.iSamplingRepeatable = 0; /* unused */
memset(&sqlca, 0x00, sizeof sqlca);

rc = db2Runstats(db2Version820, &data, &sqlca);
if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
memset(SQLUDF_STATE, "38XXX", SQLUDF_SQLSTATE_LEN);
sprintf(SQLUDF_MSGTX, "Error code %d encountered",
rc == SQL_RC_OK ? SQLCODE : rc);
goto cleanup;
}

cleanup:
return SQLZ_DISCONNECT_PROC;
}
---------------------------------------------------


Oh, I forgot: you can compile the procedure using the bldrtn script


from
sqllib/samples/c

cd sqllib/samples/c
./bldrtn runstats_proc

--
Knut Stolze
Information Integration
IBM Germany / University of Jena


Knut,

have you placed this on developerWorks? Also do you have an example for
LOAD? Wrappers for the LOAD and runstats API are probably FAQ.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #11

P: n/a
Serge Rielau wrote:
Knut,

have you placed this on developerWorks? Also do you have an example for
LOAD? Wrappers for the LOAD and runstats API are probably FAQ.


Serge, it's on my TODO list. Actually, I want to finish this and a few
other articles by the end of this month. I'll include some examples and
code for LOAD as well, no big deal. ;-)

If you (or anyone else) has some other wishes of APIs that should be made
available via SQL and are not yet natively, just let us know.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.