Advise Index

[Mercury]

V5R4 is running on my box and I am at wit's end when I try to build the query in the attached SQL stored procedure. The query is a bit hit and mess but it'd work though.

Here are the error messages that I get.

- SQL7905 --> Table DBCNDIDX in MYLIB created but was not journaled.

- CPD4019 --> Select or omit error on field C010050001 member *QUERY0052.

- CPD4019 --> Select or omit error on field C010050002 member *QUERY0052.

- CPF5033 --> Select/omit error on member CONDE00001.
Cause . . . . . : A select/omit error occurred on member CONDE00001 file CONDIDXA in library QSYS2 because the fields in record number 1, record format , member number 2 have one of the following problems: ...

- CPF3220 --> Cannot do operation on file *QUERY0049 in QTEMP.
Cause 1 - Delete File (DLTF command).

- SQL0802 --> Data conversion or data mapping error. 8 -- Division by zero.

Can you pls help ?

TIA

[Mercury]

Re: Advise Index

Birgitta please, HELP !

[B.Hauser]

Re: Advise Index

Hi,

I'd help, if I had an idea.
I just copied your code and created the procedure successfully on my system. But it seems we are missing a PTF because the view CondensedIndexAdvice is not available on my system.

Could you please do a reverse engineering to generate the source code for the view CondesedIndexAdvice and post this code. (iSeries Navigator Database, Postition on the view CondensedIndexAdvice, Right click and Generate SQL).

Birgitta

[Mercury]

Re: Advise Index

Birgitta,
Thank you for your kindness.
Here's below the required view that you can find also attached.

--  Generate SQL 
--  Version:                       V5R4M0 060210 
--  Generated on:                  21/05/08 17:19:45 
--  Relational Database:           GFDIDB2 
--  Standards Option:              DB2 UDB iSeries 
  
CREATE VIEW QSYS2.CONDENSEDINDEXADVICE ( 
    TABLE_NAME FOR COLUMN TABNAME    , 
    TABLE_SCHEMA FOR COLUMN TABSCHEMA  , 
    SYSTEM_TABLE_NAME FOR COLUMN SYS_TNAME  , 
    PARTITION_NAME FOR COLUMN TABPART    , 
    KEY_COLUMNS_ADVISED FOR COLUMN KEYSADV    , 
    INDEX_TYPE , 
    LAST_ADVISED FOR COLUMN LASTADV    , 
    TIMES_ADVISED FOR COLUMN TIMESADV   , 
    ESTIMATED_CREATION_TIME FOR COLUMN ESTTIME    , 
    LOGICAL_PAGE_SIZE FOR COLUMN "PAGESIZE" , 
    MOST_EXPENSIVE_QUERY FOR COLUMN QUERYCOST  , 
    AVERAGE_QUERY_ESTIMATE FOR COLUMN QUERYEST   , 
    TABLE_SIZE , 
    NLSS_TABLE_NAME FOR COLUMN NLSSNAME   , 
    NLSS_TABLE_SCHEMA FOR COLUMN NLSSSCHEMA ) 
    AS 
    SELECT A.TABLE_NAME, A.TABLE_SCHEMA, A.SYSTEM_TABLE_NAME,
 B.TABLE_PARTITION, B.KEY_COLUMNS_ADVISED, B.INDEX_TYPE, B.LAST_ADVISED,
 B.TIMES_ADVISED, B.ESTIMATED_CREATION_TIME, B.LOGICAL_PAGE_SIZE,
 B.MOST_EXPENSIVE_QUERY, B.AVERAGE_QUERY_ESTIMATE, B.TABLE_SIZE,
 B.NLSS_TABLE_NAME, B.NLSS_TABLE_SCHEMA FROM (SELECT DISTINCT
 TABLE_NAME, SYSTEM_TABLE_NAME,      TABLE_SCHEMA FROM QSYS2.SYSIXADV)
 AS A, LATERAL ( SELECT *          FROM TABLE
 (QSYS2.CONDENSE_ADVICE(A.TABLE_SCHEMA, A.TABLE_NAME) ) AS X ) AS B   
    RCDFMT CONDE00001 ; 
  
LABEL ON COLUMN QSYS2.CONDENSEDINDEXADVICE 
( TABLE_NAME TEXT IS 'TABLE OVER WHICH AN INDEX IS ADVISED' , 
    TABLE_SCHEMA TEXT IS 'SCHEMA CONTAINING THE TABLE' , 
    SYSTEM_TABLE_NAME TEXT IS 'SYSTEM TABLE NAME ON WHICH THE INDEX IS ADVISED' ) ; 

Thanks again in advance.

[B.Hauser]

Re: Advise Index

Hi,

I just created the view on my system, run your procedure and checked my joblog.

I also got the messages CPF5033 and SQL0802, division by zero.
There is only a single situation where a division by zero can occur:
in the last row of your select-Statement:

AND ( b.MTI_Created * 100 ) / b.MTI_Used > 10 ) ) );

I changed this row as follows:

AND ( b.MTI_Created * 100 ) / case when b.MTI_Used = 0 then 1000000 else b.MTI_Used End > 10 ) ) );

If your table DBCNDIDX is not journaled, add either a SET OPTION Statement immediately before the Begin-Statement:

Create Procedure HauserSrc/CRTSIDXPR ()
Language SQL
set option commit = *None
Begin

or add WITH NC at the end of the insert-statement.

With these modifications the procedure runs on my box without problems.

Birgitta

[Mercury]

Re: Advise Index

Thank you very much Birgitta.

I corrected the SQL statement as you indicated and now it runs correctly.

Thanks again.

[gcraill]

Re: Advise Index

Nice one Birgitta,

It's like a DC Comic in here, someone says "Help" and Birgitta flies to the rescue !!!
(I actually prefer Marvel but they don't have funky spotlights ...)

I reckon you should put that ole "Bat Signal" icon up as your avatar Birgitta !!!