Skip to Content

After migrating a simplex SAP IQ 15.2 ESD#2 database to SAP IQ 16.0 SP3 running on Sun Solaris 10 over a Sparc 64 machine, I encounter that some queries where returning zero rows or returning erroneous data;  Also, some of the queries where making the database crash.  To solve those problems I rebuild the indexes in the table.

After installing and configuring SAP IQ 16.0 SP3 I upgrade the database using follow statement:


ALTER DATABASE UPGRADE SYSTEM PROCEDURE AS DEFINER OFF

And, set the following options:


SET OPTION PUBLIC.FP_NBIT_IQ15_Compatibility = OFF                                                                                                    

SET OPTION PUBLIC.CREATE_HG_WITH_EXACT_DISTINCTS = OFF

SET OPTION PUBLIC.REVERT_TO_V15_OPTIMIZER=OFF

Then, I performed consistency and allocation test


sp_iqcheckdb ‘verify database’

sp_iqcheckdb ‘allocation database’

Both DBCC retuned “No Errors Detected”.

And, I rebuild all the indexes of all the columns over 250 char long.

Then, while testing I encounter the errors described in the first paragraph.

So, to generate the statements to rebuild all the indexes on the table bellowing to the user DBA I wrote the following 2 scripts:

select ‘sp_iqrebuildindex ”DBA.’ + T.[table_name] + ”’, ”index ‘ + I1.index_name + ‘ retier ”’ + char(13) + ‘go’

from sysidx I1, sysiqidx I2, systab T

where T.table_id = I1.table_id

and (I2.table_id=I1.table_id and I2.index_id = I1.index_id)

and T.creator = 1

and I2.index_type = ‘HG’

 

select ‘sp_iqrebuildindex ”DBA.’ + T.[table_name] + ”’, ”index ‘ + I1.index_name + ”” + char(13) + ‘go’

from sysidx I1, sysiqidx I2, systab T

where T.table_id = I1.table_id

and (I2.table_id=I1.table_id and I2.index_id = I1.index_id)

and T.creator = 1

and I2.index_type <> ‘HG’

And then, I just executed the generated scripts to rebuild all the indexes.

You can also modify these scripts to generate the statements to rebuild the indexes of any given table:


select ‘sp_iqrebuildindex ”DBA.’ + T.[table_name] + ”’, ”index ‘ + I1.index_name + ‘ retier ”’ + char(13) + ‘go’

from sysidx I1, sysiqidx I2, systab T

where T.table_id = I1.table_id

and (I2.table_id=I1.table_id and I2.index_id = I1.index_id)

and T.[table_name] = ‘myTable’

and T.creator = 1

and I2.index_type = ‘HG’

Union

select ‘sp_iqrebuildindex ”DBA.’ + T.[table_name] + ”’, ”index ‘ + I1.index_name + ”” + char(13) + ‘go’

from sysidx I1, sysiqidx I2, systab T

where T.table_id = I1.table_id

and (I2.table_id=I1.table_id and I2.index_id = I1.index_id)

and T.[table_name] = ‘myTable’

and T.creator = 1

and I2.index_type <> ‘HG’

Or, you can use these scripts to build dynamic SQL that will rebuild all the indexes in the database.

This post has first been published on my personal blog at http://wp.me/p1Gouv-6N

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Singh Raj Kumar

    select “sp_iqrebuildindex ” +”‘”+table_owner+”.”+ table_name+”‘,”+”‘column “+column_name+”‘”+char(10)+’go’
    from sp_iqcolumn()

    another

    SELECT ‘sp_iqrebuildindex ”’ || U.user_name ||’.’||table_name||”’,”column ‘||C.column_name ||’ 1”;’

    from SYS.SYSTABLE as T,

    SYS.SYSCOLUMN as C,

    SYS.SYSDOMAIN as D,

    SYS.SYSUSERPERM as U,

    SYS.SYS.SYSIQCOLUMN as IQC

    WHERE T.creator = U.user_id and

    T.table_id = C.table_id and

    C.domain_id = D.domain_id and

    IQC.table_id = C.table_id and

    IQC.column_id = C.column_id
    order by C.column_id asc;

    (1) 

Leave a Reply