Skip to Content
0

Rebuild index very slow after upgrading IQ 15.3.0 to SAP IQ 16.0 SP10

Nov 01, 2016 at 08:20 PM

57

avatar image

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 $ iqprd01.cfg "iqprd01.cfg" 28 lines, 627 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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Roland Kramer
Dec 17, 2016 at 03:18 AM
0

see the findings about the new Indexes on SAP IQ SP10/11 and how to create them

SAP First Guidance – SAP NLS Solution with SAP IQ

Best Regards Roland

Share
10 |10000 characters needed characters left characters exceeded