Skip to Content
0

IQ16 Post upgrade steps

Jan 29, 2017 at 02:13 PM

100

avatar image
Former Member

Hi All

I have recently upgraded IQ154 instance to IQ16/Sp11, all went fine on upgrade and have completed post upgrade steps as explained below. I want to make sure am I following the correct approach for IQ upgrade and is there something I would need to add or omit to lessen the post upgrade time in my implementation plan.

  1. Alter database upgrade
  2. Configuration changes required after upgrade

    set option public.REVERT_TO_V15_OPTIMIZER='OFF'

set option public.FP_NBIT_IQ15_COMPATIBILITY='OFF'

set option public.CREATE_HG_WITH_EXACT_DISTINCTS='OFF'

3.Rebuilt existing indexes for all columns and completed in 3.5 hrs.

select 'declare @d1 datetime ' + convert(char(1),0x0A) + ' select @d1=getdate() ' + convert(char(1),0x0A) +

'exec sp_iqrebuildindex '''+ u.user_name+'.' + T.[table_name] + ''', ''column ' + C.column_name +'''' +

convert(char(1),0x0A) + 'select " Rebuild index on : ' + table_name + '..' + C.column_name + ' Started at :" + convert(char(30),@d1)

+ " and Finished at : "+ convert(char(30),getdate()) + + " completed in (mins):: " +convert(char(10),datediff(mi,@d1,getdate()))' +

convert(char(1),0x0A)+ 'go'

fromSYS.SYSTABLEas T,

SYS.SYSCOLUMNas C,

SYS.SYSDOMAINas D,

SYS.SYSUSERPERMas 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

4. Rebuilt HG indexes using RETIER option and completed in 10 hrs. Toggle between tier and non-tier HG index should be dependent on query performance. However, is there a way, can I cut short index rebuild time if further required to switch between tiered and non-tiered HG indexes?

select 'declare @d1 datetime ' + convert(char(1),0x0A) + ' select @d1=getdate() ' + convert(char(1),0x0A) +

'exec sp_iqrebuildindex '''+ u.user_name+'.' + T.[table_name] + ''', ''index ' + I1.index_name +' retier''' +

convert(char(1),0x0A) + 'select " Rebuild index on : ' + table_name + '..' + I1.index_name + ' Started at :" + convert(char(30),@d1)

+ " and Finished at : "+ convert(char(30),getdate()) + + " completed in (mins):: " +convert(char(10),datediff(mi,@d1,getdate()))' +

convert(char(1),0x0A)+ 'go'

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

and I2.index_type='HG'

go\Convert all LF indexes to HG indexes and completed in 3 hrs.

select 'declare @d1 datetime ' + convert(char(1),0x0A) + ' select @d1=getdate() ' + convert(char(1),0x0A) +

'CREATE HG INDEX '+ index_name + ' ON ' + table_owner + '.' + + table_name + '( ' + column_name +' )' +

convert(char(1),0x0A) + 'select " Creating index HG over LF on : ' + table_name + '..' + index_name + ' Started at :" + convert(char(30),@d1)

+ " and Finished at : "+ convert(char(30),getdate()) + + " completed in (mins):: " +convert(char(10),datediff(mi,@d1,getdate()))' +

convert(char(1),0x0A)+ 'go' from

sp_iqindex() where index_type='LF'

go

    6. DBCC checks and completed in 6.5 hrs.

    1. sp_iqcheckdb ('allocation database')
    2. sp_iqcheckdb 'check database'
    10 |10000 characters needed characters left characters exceeded
    * Please Login or Register to Answer, Follow or Comment.

    2 Answers

    Roland Kramer
    Jan 31, 2017 at 11:42 AM
    0

    Hi,

    You an also have a look to the following Document - SAP First Guidance – SAP-NLS Solution with SAP IQ

    Best Regards Roland

    Share
    10 |10000 characters needed characters left characters exceeded
    avatar image
    Former Member Feb 03, 2017 at 08:20 AM
    0

    Thank Rolan,

    I have used configured IQ caches as iqmc=150000 , iqtc=150000 and iqlm=130000

    and received promising results. But dbcc runs is still at large.

    Rebuild all column indexes1:33:00 (hh:mm:ss)Rebuild HG indexes using RETIER option0:08:00Drop LF indexes0:10:40Convert LF to HG index0:47:40DBCC Checks sp_iqcheckdb ('allocation database')

    0:01:40 DBCC Checks sp_iqcheckdb 'check database'9:01:00

    Share
    10 |10000 characters needed characters left characters exceeded