Skip to content

SAP IQ 16: Script to Generate Rebuild Indexes Scripts

February 20, 2014

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.

Advertisements

From → SAP IQ

2 Comments
  1. Hi Raj,

    The “SYSTEM PROCEDURE DEFINER” option does not really matter for the migrated system procedures, they will execute with the privileged of the definer. For the new system procedures I personally prefer using invoker privileges, this model require a little more administration but in my opinion is more secure.

    For the weak performance during the rebuild index, you should try in your prod replica environment (do not do it in prod environment until you are completely sure of the success of the operation):

    1. drop and recreate the indexes instead of rebuilding them,
    2. remove “–iqnumbercpus 2 “ from the configuration file if you have more cpus and your license permit,
    3. Use the BEGIN PARALLEL IQ … END PARALLEL IQ statement that lets you execute a group of CREATE INDEX statements in parallel, creating indexes on multiple IQ tables at the same time (database must be in single user mode –gm 1).

    Hope this help, please share your result.

    DISCLAIMER: The author is not responsible for any data loss or damage resulting from the execution of the above described procedure.

  2. I upgraded IQ/15.3.0.6056 to SAP IQ 16.0 SP10 where rebuild indexes took more tha 52 hours to complete, This I have done in my prod replica environment. Now business is quite affraid to rollout in real prod environment due to large index rebuild window – Kindly advise what we can do to fasten the rebuild OR how we can achieve to rebuild entire indexes as recommended to use IQ 16 Optimizer.

    Steps I followed to upgrade

    Started sybase IQ using -gm 1 -iqro 1
    Run upgrade command successfully

    ALTER DATABASE UPGRADE

    (have security model-SYSTEM PROCEDURE DEFINER) but did not use the same in upgrade command, should I use this ?)

    started Database with normal cfg
    SET OPTION PUBLIC.FP_NBIT_IQ15_Compatibility = OFF
    SET OPTION PUBLIC.REVERT_TO_V15_OPTIMIZER=OFF
    Run query to generate index rebuild SQL

    select ‘sp_iqrebuildindex ”’+ u.user_name+’.’+T.[table_name] + ”’, ”index ‘ + I1.index_name +”” + convert(char(1),0x0A)+’go’+convert(char(1),0x0A)
    from sysidx I1, sysiqidx I2, systab T , sysuser u where T.table_id = I1.table_id and T.creator = u.user_id and (I2.table_id=I1.table_id and I2.index_id = I1.index_id)
    and T.creator > 0

    During first run temp ran out of space, added 200 GB to temp and again started rebuild from begining.

    cfg used to start IQ after upgrade –
    17:05:40 iqprd1@furaito01 /sybserver/iqprd1/cfg $ vi iqprd01.cfg
    “iqprd01.cfg” 28 lines, 627 characters

    iqprd01.cfg

    ————————————————————

    Default startup parameters for the ASIQ iqprd01 database

    ————————————————————

    -n iqprd01
    -x tcpip{port=5102}

    The following parameters are also found in the configuration file

    $ASDIR/scripts/default.cfg. Any parameters not specified below

    and not in the start up parameter list, will be added by start_asiq

    using default.cfg as a guide.

    -c 64m
    -gc 6000
    -gd all
    -gl all
    -gm 1
    -iqro 1
    -gp 4096
    -ti 4400
    -tl 300
    -iqmc 9000
    -iqtc 9000
    -iqnumbercpus 2
    -p 2048
    -n iqprd01

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: