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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
10 | |
6 | |
4 | |
3 | |
3 | |
3 | |
2 | |
2 | |
2 |