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
