Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

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

2 Comments
Labels in this area