SEO

Hi,

I don't have much experience writting UDFs, so I don't know if this is
possible (and simple). I am basically looking to write a wrapper table
UDF that encapsulates reorgchk_tb_stats and reorgchk_ix_stats
procedures, so I can easily manipulate the result set using SQL.

TIA

P. Adhia

Hi.

You have to use external udf (c, java for example) to do this.

Sincerely,
Mark B.

> Hi,
>
> I don't have much experience writting UDFs, so I don't know if this is
> possible (and simple). I am basically looking to write a wrapper table
> UDF that encapsulates reorgchk_tb_stats and reorgchk_ix_stats
> procedures, so I can easily manipulate the result set using SQL.
>
> TIA
>
> P. Adhia

4.spam@mail.ru wrote:
> You have to use external udf (c, java for example) to do this.

Thanks for the response. It isn't as easy as I thought it would be.

I guess my next best option is to write queries against catalog tables.
Just so that I don't reinvent the wheel, has anyone attempted to create
views (or set of queries) on catalog tables that mimic the result-sets
returned by reorgchk_tb_stats and reorgchk_ix_stats?

Thanks again

P Adhia

Try something like this:
CREATE FUNCTION FUNC_REORGCHCK ()
RETURNS TABLE (
TABLE_SCHEMA VARCHAR(128)
,TABLE_NAME VARCHAR(128)
,CARD INTEGER
,OVERFLOW INTEGER
,NPAGES INTEGER
,FPAGES INTEGER
,ACTIVE_BLOCKS INTEGER
,TSIZE INTEGER
,F1 INTEGER
,F2 INTEGER
,F3 INTEGER
,REORG INTEGER
)
SPECIFIC DB2INST1.FUNC_REORGCHK


F1: BEGIN ATOMIC
-- LOOP variables
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;

-- DCGT to hold output of call to store proc
DECLARE GLOBAL TEMPORARY TABLE TB_REORGCHK
(
TABLE_SCHEMA VARCHAR(128)
,TABLE_NAME VARCHAR(128)
,CARD INTEGER
,OVERFLOW INTEGER
,NPAGES INTEGER
,FPAGES INTEGER
,ACTIVE_BLOCKS INTEGER
,TSIZE INTEGER
,F1 INTEGER
,F2 INTEGER
,F3 INTEGER
,REORG INTEGER
)

CALL reorgchk_tb_stats('T','ALL');
ASSOCIATE RESULT SET LOCATORS (LOC1)
WITH PROCEDURE reorgch_tb_stats;
ALLOCATE c1 CURSOR FOR RESULT SET loc1;

fetch_loop:
REPEAT
FETCH rsCur INTO
TABLE_SCHEMA
,TABLE_NAME
,CARD
,OVERFLOW
,NPAGES
,FPAGES
,ACTIVE_BLOCKS
,TSIZE
,F1
,F2
,F3
,REORG;

INSERT INTO SESSION.TB_REORGCHK VALUES (
TABLE_SCHEMA
,TABLE_NAME
,CARD
,OVERFLOW
,NPAGES
,FPAGES
,ACTIVE_BLOCKS
,TSIZE
,F1
,F2
,F3
,REORG)
UNTIL at_end > 0
END REPEAT fetch_loop;


RETURN
SELECT *
FROM SESSION.TB_REROGCHK

END
@




Norm
P. Adhia wrote:
> 4.spam@mail.ru wrote:
> > You have to use external udf (c, java for example) to do this.
>
> Thanks for the response. It isn't as easy as I thought it would be.
>
> I guess my next best option is to write queries against catalog tables.
> Just so that I don't reinvent the wheel, has anyone attempted to create
> views (or set of queries) on catalog tables that mimic the result-sets
> returned by reorgchk_tb_stats and reorgchk_ix_stats?
>
> Thanks again
>
> P Adhia

Norm wrote:
> fetch_loop:
> REPEAT
> FETCH rsCur INTO
> TABLE_SCHEMA
> ,TABLE_NAME
> ,CARD
> ,OVERFLOW
> ,NPAGES
> ,FPAGES
> ,ACTIVE_BLOCKS
> ,TSIZE
> ,F1
> ,F2
> ,F3
> ,REORG;
>
> INSERT INTO SESSION.TB_REORGCHK VALUES (
> TABLE_SCHEMA
> ,TABLE_NAME
> ,CARD
> ,OVERFLOW
> ,NPAGES
> ,FPAGES
> ,ACTIVE_BLOCKS
> ,TSIZE
> ,F1
> ,F2
> ,F3
> ,REORG)
> UNTIL at_end > 0
> END REPEAT fetch_loop;
>

Doesn't the last row in the cursor get inserted twice in this fetch
loop?

You can't
declare handlers,
declare session tables,
issue ASSOCIATE LOCATORS (ALLOCATE CURSOR),
issue FETCH
in SQL funcions.

That is the reason to use external UDF.

Sincerely,
Mark B.

Norm wrote:
> Try something like this:
> CREATE FUNCTION FUNC_REORGCHCK ()

Just wanted to thank you for making the suggestion. As one of the other
poster predicted, I ran into an error that pointed at unsupported
DECLARE HANDLER statement.

I am not sure why reorgchk_* are stored-procs, whereas most other
commands/utilities have a function instead (probably these were created
in pre-function days). Anyways, I at least (hopefully correctly)
learned that it's easier to convert a table function to a stored-proc,
rahter than the other way around. If IBM ever creates UDFs for REORGCHK
command, the existing stored-procedures, could be simple wrappers
around new UDFs.

Thanks

P Adhia

4.spam@mail.ru wrote:
> You can't
> declare handlers,
> declare session tables,
> issue ASSOCIATE LOCATORS (ALLOCATE CURSOR),
> issue FETCH
> in SQL funcions.
>
> That is the reason to use external UDF.
>
> Sincerely,
> Mark B.

But you can use those features in SQL procedures, right? If so, could
you call such a procedure from within a SQL function?

Regards
JohnO