RUNSTATS from SQL for UDB

[Spencer]

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

[Troels Arvin]

Re: RUNSTATS from SQL for UDB

On Wed, 22 Sep 2004 07:31:14 -0700, Spencer wrote:
[color=blue]
> 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?[/color]

Doesn't a command like this work?:

db2 'runstats on table db2inst1.tablen ame'

(Exchange "db2inst1" with yourr schema name.)

--
Greetings from Troels Arvin, Copenhagen, Denmark

[Spencer]

Re: RUNSTATS from SQL for UDB

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 <troels@arvin.d k> wrote in message news:<pan.2004. 09.22.14.35.42. 253318@arvin.dk >...[color=blue]
> On Wed, 22 Sep 2004 07:31:14 -0700, Spencer wrote:
>[color=green]
> > 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?[/color]
>
> Doesn't a command like this work?:
>
> db2 'runstats on table db2inst1.tablen ame'
>
> (Exchange "db2inst1" with yourr schema name.)[/color]

[Knut Stolze]

Re: RUNSTATS from SQL for UDB

Spencer wrote:
[color=blue]
> 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.[/color]

Why don't you write a stored procedure that uses the "db2Runstat s" 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

[Spencer]

Re: RUNSTATS from SQL for UDB

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 <stolze@de.ibm. com> wrote in message news:<citql2$bh l$1@fsuj29.rz.u ni-jena.de>...[color=blue]
> Spencer wrote:
>[color=green]
> > 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.[/color]
>
> Why don't you write a stored procedure that uses the "db2Runstat s" API call
> internally? Then you could simply do this:
>
> db2 "CALL myRunstatsProc( ...)"
>
> and that is normal SQL which can be run through ODBC.[/color]

[Knut Stolze]

Re: RUNSTATS from SQL for UDB

Spencer wrote:[color=blue]
> Right....like I said has anyone done this?[/color]

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

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

[Spencer]

Re: RUNSTATS from SQL for UDB

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 <stolze@de.ibm. com> wrote in message news:<cive6b$b1 0$1@fsuj29.rz.u ni-jena.de>...[color=blue]
> Spencer wrote:[color=green]
> > Right....like I said has anyone done this?[/color]
>
> I haven't, but I don't see any particular problem. Do you need some help
> coding this?
>[color=green]
> > Any good info out there on
> > the DB2 SQL Procedural Language. Everything I find is extremely
> > vague.[/color]
>
> 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.[/color]

[Knut Stolze]

Re: RUNSTATS from SQL for UDB

Spencer wrote:
[color=blue]
> 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.[/color]

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_AR GS)
{
SQL_API_RC rc = SQL_RC_OK;
db2RunstatsData data;
struct sqlca sqlca;

if (SQLUDF_NULL(ta bleName_ind)) {
goto cleanup;
}

/* initialize data structures */
data.iSamplingO ption = 0;
data.piTablenam e = (unsigned char *)tableName;
data.piColumnLi st = NULL;
data.piColumnDi stributionList = NULL;
data.piColumnGr oupList = NULL;
data.piIndexLis t = NULL;
data.iRunstatsF lags = DB2RUNSTATS_ALL _COLUMNS |
DB2RUNSTATS_DIS TRIBUTION | DB2RUNSTATS_ALL _INDEXES |
DB2RUNSTATS_EXT _INDEX | DB2RUNSTATS_ALL OW_READ;
data.iNumColumn s = 0;
data.iNumColdis t = 0;
data.iNumColGro ups = 0;
data.iNumIndexe s = 0;
data.iParalleli smOption = 0;
data.iTableDefa ultFreqValues = -1; /* use default */
data.iTableDefa ultQuantiles = -1; /* use default */
data.iUtilImpac tPriority = 0;
data.iSamplingR epeatable = 0; /* unused */
memset(&sqlca, 0x00, sizeof sqlca);

rc = db2Runstats(db2 Version820, &data, &sqlca);
if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
memset(SQLUDF_S TATE, "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('stolz e.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 "db2Runstat s" 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

[Knut Stolze]

Re: RUNSTATS from SQL for UDB

Knut Stolze wrote:
[color=blue]
> Spencer wrote:
>[color=green]
>> 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.[/color]
>
> 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_AR GS)
> {
> SQL_API_RC rc = SQL_RC_OK;
> db2RunstatsData data;
> struct sqlca sqlca;
>
> if (SQLUDF_NULL(ta bleName_ind)) {
> goto cleanup;
> }
>
> /* initialize data structures */
> data.iSamplingO ption = 0;
> data.piTablenam e = (unsigned char *)tableName;
> data.piColumnLi st = NULL;
> data.piColumnDi stributionList = NULL;
> data.piColumnGr oupList = NULL;
> data.piIndexLis t = NULL;
> data.iRunstatsF lags = DB2RUNSTATS_ALL _COLUMNS |
> DB2RUNSTATS_DIS TRIBUTION | DB2RUNSTATS_ALL _INDEXES |
> DB2RUNSTATS_EXT _INDEX | DB2RUNSTATS_ALL OW_READ;
> data.iNumColumn s = 0;
> data.iNumColdis t = 0;
> data.iNumColGro ups = 0;
> data.iNumIndexe s = 0;
> data.iParalleli smOption = 0;
> data.iTableDefa ultFreqValues = -1; /* use default */
> data.iTableDefa ultQuantiles = -1; /* use default */
> data.iUtilImpac tPriority = 0;
> data.iSamplingR epeatable = 0; /* unused */
> memset(&sqlca, 0x00, sizeof sqlca);
>
> rc = db2Runstats(db2 Version820, &data, &sqlca);
> if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
> memset(SQLUDF_S TATE, "38XXX", SQLUDF_SQLSTATE _LEN);
> sprintf(SQLUDF_ MSGTX, "Error code %d encountered",
> rc == SQL_RC_OK ? SQLCODE : rc);
> goto cleanup;
> }
>
> cleanup:
> return SQLZ_DISCONNECT _PROC;
> }
> ---------------------------------------------------[/color]
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

[peteh]

Re: RUNSTATS from SQL for UDB

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:[color=blue]
> Knut Stolze wrote:
>[color=green]
> > Spencer wrote:
> >[color=darkred]
> >> Sure if you want to code it for me that would be great! So if I[/color][/color][/color]
am[color=blue][color=green][color=darkred]
> >> 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.[/color]
> >
> > Sorry that it took so long...
> >
> > Here is the procedure code. It is pretty basic and does not[/color][/color]
contain any[color=blue][color=green]
> > 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_AR GS)
> > {
> > SQL_API_RC rc = SQL_RC_OK;
> > db2RunstatsData data;
> > struct sqlca sqlca;
> >
> > if (SQLUDF_NULL(ta bleName_ind)) {
> > goto cleanup;
> > }
> >
> > /* initialize data structures */
> > data.iSamplingO ption = 0;
> > data.piTablenam e = (unsigned char *)tableName;
> > data.piColumnLi st = NULL;
> > data.piColumnDi stributionList = NULL;
> > data.piColumnGr oupList = NULL;
> > data.piIndexLis t = NULL;
> > data.iRunstatsF lags = DB2RUNSTATS_ALL _COLUMNS |
> > DB2RUNSTATS_DIS TRIBUTION | DB2RUNSTATS_ALL _INDEXES |
> > DB2RUNSTATS_EXT _INDEX | DB2RUNSTATS_ALL OW_READ;
> > data.iNumColumn s = 0;
> > data.iNumColdis t = 0;
> > data.iNumColGro ups = 0;
> > data.iNumIndexe s = 0;
> > data.iParalleli smOption = 0;
> > data.iTableDefa ultFreqValues = -1; /* use default */
> > data.iTableDefa ultQuantiles = -1; /* use default */
> > data.iUtilImpac tPriority = 0;
> > data.iSamplingR epeatable = 0; /* unused */
> > memset(&sqlca, 0x00, sizeof sqlca);
> >
> > rc = db2Runstats(db2 Version820, &data, &sqlca);
> > if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
> > memset(SQLUDF_S TATE, "38XXX", SQLUDF_SQLSTATE _LEN);
> > sprintf(SQLUDF_ MSGTX, "Error code %d encountered",
> > rc == SQL_RC_OK ? SQLCODE : rc);
> > goto cleanup;
> > }
> >
> > cleanup:
> > return SQLZ_DISCONNECT _PROC;
> > }
> > ---------------------------------------------------[/color]
> Oh, I forgot: you can compile the procedure using the bldrtn script[/color]
from[color=blue]
> sqllib/samples/c
>
> cd sqllib/samples/c
> ./bldrtn runstats_proc
>
> --
> Knut Stolze
> Information Integration
> IBM Germany / University of Jena[/color]

[Serge Rielau]

Re: RUNSTATS from SQL for UDB

peteh wrote:[color=blue]
> 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:
>[color=green]
>>Knut Stolze wrote:
>>
>>[color=darkred]
>>>Spencer wrote:
>>>
>>>
>>>>Sure if you want to code it for me that would be great! So if I[/color][/color]
>
> am
>[color=green][color=darkred]
>>>>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[/color][/color]
>
> contain any
>[color=green][color=darkred]
>>>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_AR GS)
>>>{
>>> SQL_API_RC rc = SQL_RC_OK;
>>> db2RunstatsData data;
>>> struct sqlca sqlca;
>>>
>>> if (SQLUDF_NULL(ta bleName_ind)) {
>>> goto cleanup;
>>> }
>>>
>>> /* initialize data structures */
>>> data.iSamplingO ption = 0;
>>> data.piTablenam e = (unsigned char *)tableName;
>>> data.piColumnLi st = NULL;
>>> data.piColumnDi stributionList = NULL;
>>> data.piColumnGr oupList = NULL;
>>> data.piIndexLis t = NULL;
>>> data.iRunstatsF lags = DB2RUNSTATS_ALL _COLUMNS |
>>> DB2RUNSTATS_DIS TRIBUTION | DB2RUNSTATS_ALL _INDEXES |
>>> DB2RUNSTATS_EXT _INDEX | DB2RUNSTATS_ALL OW_READ;
>>> data.iNumColumn s = 0;
>>> data.iNumColdis t = 0;
>>> data.iNumColGro ups = 0;
>>> data.iNumIndexe s = 0;
>>> data.iParalleli smOption = 0;
>>> data.iTableDefa ultFreqValues = -1; /* use default */
>>> data.iTableDefa ultQuantiles = -1; /* use default */
>>> data.iUtilImpac tPriority = 0;
>>> data.iSamplingR epeatable = 0; /* unused */
>>> memset(&sqlca, 0x00, sizeof sqlca);
>>>
>>> rc = db2Runstats(db2 Version820, &data, &sqlca);
>>> if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
>>> memset(SQLUDF_S TATE, "38XXX", SQLUDF_SQLSTATE _LEN);
>>> sprintf(SQLUDF_ MSGTX, "Error code %d encountered",
>>> rc == SQL_RC_OK ? SQLCODE : rc);
>>> goto cleanup;
>>> }
>>>
>>> cleanup:
>>> return SQLZ_DISCONNECT _PROC;
>>>}
>>>---------------------------------------------------[/color]
>>
>>Oh, I forgot: you can compile the procedure using the bldrtn script[/color]
>
> from
>[color=green]
>>sqllib/samples/c
>>
>>cd sqllib/samples/c
>>./bldrtn runstats_proc
>>
>>--
>>Knut Stolze
>>Information Integration
>>IBM Germany / University of Jena[/color]
>
>[/color]
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

[Knut Stolze]

Re: RUNSTATS from SQL for UDB

Serge Rielau wrote:
[color=blue]
> 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.[/color]

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