Make DB2 administrative APIs available through SQL

[Stefan]

Hello all,

there are two procedures to get the reorgchk statistics for tables and
indexes.

I would like to have the result of those stored in a table.
Has one of you a hint for me how I could get this?
Is there a global temporary table I could read after calling the
procedure?
(Knut, is it possible?)

Many thanks
Stefan
Make DB2 administrative APIs available through SQL
http://www-128.ibm.com/developerwork...lze/index.html

db2 => call sysproc.reorgchk_ix_stats('t','sapr3.t000')

Result set 1
--------------
TABLE_SCHEMA
TABLE_NAME
INDEX_SCHEMA INDEX_NAME
CARD NLEAF NUM_EMPTY_LEAFS NLEVEL
S ISIZE NUMRIDS_DELETED FULLKEYCARD F4
F5 F6 F7 F8
REORG

----------------------------------------------------------------------------------------------------------------------
----------
-------------------------------------------------------------------------------------------------------------
------------------- --------------------------------
-------------------- ----------- ----------- --------------- ------
----- -------------------- -------------------- --------------------
----------- ----------- ----------- ----------- ---
-------- -----
SAPR3
T000
SAPR3 T000~0
3 1 0
1 3 0 3
100 -1 -1 0
0 -----

1 record(s) selected.

Return Status = 0

db2 => call sysproc.reorgchk_tb_stats('t','sapr3.t000')
Result set 1
--------------

TABLE_SCHEMA
TABLE_NAME
CARD OVERFLOW NPAGES FPAGES
ACTIVE_BLOCKS TSIZE F1
F2 F3 REORG

----------------------------------------------------------------------------------------------------------------------
----------
-------------------------------------------------------------------------------------------------------------
------------------- ----------- ----------- ----------- -----------
-------------------- -------------------- ----------
- ----------- ----------- -----
SAPR3
T000
3 0 1 1
-1 270
0 -1 100 ---

1 record(s) selected.

db2 => describe call sysproc.reorgchk_tb_stats('t','sapr3.t000')

SQLDA Information

sqldaid : SQLDA sqldabc: 1136 sqln: 20 sqld: 0

Column Information

sqltype sqllen sqlname.data
sqlname.length
-------------------- ------ ------------------------------
--------------
(empty ???)

[Knut Stolze]

Stefan wrote:

Hello all,

there are two procedures to get the reorgchk statistics for tables and
indexes.

I would like to have the result of those stored in a table.
Has one of you a hint for me how I could get this?
Is there a global temporary table I could read after calling the
procedure?
(Knut, is it possible?)

You can wrap the calls to reorgchk_??_stats into your own procedure and have
that procedure write the results into a table of yours. The following
procedure shows that. Please note that it only works on the tables and
indexes in the SYSIBM schema. You might want to parameterize that.

----------- snip ------------
DROP TABLE session.reorgchk_results@
DROP PROCEDURE reorgchk()@
CREATE PROCEDURE reorgchk()
LANGUAGE SQL
RESULT SETS 0
BEGIN
DECLARE table_check RESULT_SET_LOCATOR VARYING;
DECLARE index_check RESULT_SET_LOCATOR VARYING;

DECLARE table_schema, table_name VARCHAR(128);
DECLARE index_schema, index_name VARCHAR(128);
DECLARE nleaf, num_empty_leafs, nlevels, isize,
numrids_deleted, fullkeycard INTEGER;
DECLARE cardinality, overflow, npages, fpages,
active_blocks, tsize INTEGER;
DECLARE f1, f2, f3, f4, f5, f6, f7, f8 DOUBLE;
DECLARE reorg VARCHAR(5);
DECLARE got_all INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET got_all = 1;

DECLARE GLOBAL TEMPORARY TABLE session.reorgchk_results (
table_schema VARCHAR(128),
table_name VARCHAR(128),
index_schema VARCHAR(128),
index_name VARCHAR(128),
cardinality INTEGER,
overflow INTEGER,
npages INTEGER,
fpages INTEGER,
active_blocks INTEGER,
tsize INTEGER,
nleaf INTEGER,
num_empty_leafs INTEGER,
nlevels INTEGER,
isize INTEGER,
numrids_deleted INTEGER,
fullkeycard INTEGER,
f1 DOUBLE,
f2 DOUBLE,
f3 DOUBLE,
f4 DOUBLE,
f5 DOUBLE,
f6 DOUBLE,
f7 DOUBLE,
f8 DOUBLE,
summary VARCHAR(5),
reorg_recommended CHAR(1)
) ON COMMIT PRESERVE ROWS NOT LOGGED ON ROLLBACK PRESERVE ROWS;

CALL sysproc.reorgchk_tb_stats('S', 'SYSIBM');
ASSOCIATE RESULT SET LOCATOR ( table_check )
WITH PROCEDURE sysproc.reorgchk_tb_stats;
ALLOCATE table_cursor CURSOR FOR RESULT SET table_check;
SET got_all = 0;

fetch_table: LOOP
FETCH table_cursor
INTO table_schema, table_name, cardinality, overflow,
npages, fpages, active_blocks, tsize, f1, f2, f3, reorg;
IF got_all = 1 THEN
LEAVE fetch_table;
END IF;

INSERT
INTO session.reorgchk_results(table_schema, table_name,
cardinality, overflow, npages, fpages, active_blocks,
tsize, f1, f2, f3, summary, reorg_recommended)
VALUES ( table_schema, table_name, cardinality, overflow,
npages, fpages, active_blocks, tsize, f1, f2, f3, reorg,
CASE WHEN reorg LIKE '%*%' THEN 'Y' ELSE 'N' END );
END LOOP fetch_table;
CLOSE table_cursor;
CALL sysproc.reorgchk_ix_stats('S', 'SYSIBM');
ASSOCIATE RESULT SET LOCATOR ( index_check )
WITH PROCEDURE sysproc.reorgchk_ix_stats;
ALLOCATE index_cursor CURSOR FOR RESULT SET index_check;
SET got_all = 0;

fetch_index: LOOP
FETCH index_cursor
INTO table_schema, table_name, index_schema, index_name,
cardinality, nleaf, num_empty_leafs, nlevels,
isize, numrids_deleted, fullkeycard, f4, f5, f6, f7, f8,
reorg;
IF got_all = 1 THEN
LEAVE fetch_index;
END IF;

INSERT
INTO session.reorgchk_results(table_schema, table_name,
index_schema, index_name, cardinality, nleaf,
num_empty_leafs, nlevels, isize, numrids_deleted,
fullkeycard,
f4, f5, f6, f7, f8, summary, reorg_recommended)
VALUES ( table_schema, table_name, index_schema, index_name,
cardinality, nleaf, num_empty_leafs, nlevels,
isize, numrids_deleted, fullkeycard,
f4, f5, f6, f7, f8, reorg,
CASE WHEN reorg LIKE '%*%' THEN 'Y' ELSE 'N' END );
END LOOP fetch_index;
CLOSE index_cursor;
END
@
----------- snip ------------

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