Need Steps to migrate IQ 15.4 to 16 on solaris/unix

[Former Member]

Hi  Need help .

I want to upgrade Sybase IQ 15.4 to 16 in place  upgrade on Unix/Solaris environment .

really Appreciate your help if some one provide step by step (some detail doc) for the same  .

What i understand :

-- take backup of IQ 15 database backup

-- shutdown iq15 database

0) get the ebf file xyz.tgz   in a folder

1) gunzip xyz.tgz

2) tar -xvf  yz.tg

3) create new folder IQ16  . Where new $SYABSE will point

3)  run ( setup.bin -iconsole) and point to new $SYABSE ( new folder IQ16)

5) check version $SYABSE/$IQDIR16/bin64\start_iq -v2

6)  start iq server with new binary ..

Regards

Ajay Pandey

[Former Member]

Hi Rey,

Did you got any solution ..

Regards

Ajay Pandey

[Former Member]

As stated above . I upgraded IQ from 15.4 to 16 sp10 .

Post upgrade :

0) alter database  upgrade .

1) Run sp_iqcheckdb  (verify , allocation ,check)

2) took full backup of database

3)added -iqlm in cfg file

4)started IQ server in normal mode

Question : I have not changed any of following db options . Do i still need to rebuild index (sp_iqrebuildindex) .....

FP_NBIT_IQ15_Compatibility

CREATE_HG_WITH_EXACT_DISTINCTS

REVERT_TO_V15_OPTIMIZER

Appreciate your recommendation ... it is approx 15 TB database size .

Regards

Ajay Pandey

[markmumy]

You need to rebuild your default indexes, too.  And convert the LF indexes to HGs.  These are critical to an optimal IQ 16 system.

Set FP_N-bit_IQ15_Compatibility to OFF.

Set CREATE_HG_WITH_EXACT_DISTINCTS to ON

Set REVERT_TO_V15_OPTIMIZER to OFF

Once you've done that you will want to rebuild your default indexes so that you can get better compression as well as pick up other performance enhancements that only come through a rebuild.

What did you set -iqlm to?  What are -iqmc and -iqtc?

Mark

[Former Member]

I upgraded dev server ..

-iqlm to?  What are -iqmc and -iqtc?  -- 5 GB each

Option : CREATE_HG_WITH_EXACT_DISTINCTS

As per IQ doc :

To take advantage of the new tiered structure, set this option to

OFF. Use sp_iqrebuildindex to convert non-tiered HG indexes to

tiered HG and vice-versa.

default INDEX means FP index for all table and all columns ,,. Can i generate SQL for rebuild index as some thing like below  or can you provide ...so better sql to generate syntax for sp_iqrebuildindex ...

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 = 'FP'

Regards

Ajay Pandey

[markmumy]

You want to rebuild the 'column'.  That's the easiest method.

There are limited cases where you need tiered HGs.  Best practices dictate that you convert first, performance test, and only then move to tiered HGs should they show to be a bottleneck for loading. 

Mark

[Former Member]

Got it ...

DEFAULT INDEX ..

Rebuilds the default index on column

sp_iqrebuildindex 'owner.table_name','coulmn column_name'

what about db option : CREATE_HG_WITH_EXACT_DISTINCTS it should be OFF ?

Regards
Ajay Pandey

[Former Member]

Hi Marc,

As u suggested . i have disabled  below two dboptions .. and Rebuild default indexes for all columns.

SET OPTION PUBLIC.FP_NBIT_IQ15_Compatibility = OFF

SET OPTION PUBLIC.REVERT_TO_V15_OPTIMIZER=OFF

SQL i used below SQL to generate sql to rebuild default index : is this correct

select "sp_iqrebuildindex " +"'"+table_owner+"."+ table_name+"',"+"'column "+column_name+"'"+char(10)+'go'

from sp_iqcolumn()

Question : Older version (15.4) . I have not yet uninstalled .

New version i installed in separate folder /op/sybase/16.0

Old IQ is in /op/sybase/15.4

Now server is running using  new folder $SYBASE --> /op/sybase/16.0 .

Can i just tar the /op/sybase/15.4 and remove the folder .. /op/sybase/15.4

or do we need to unstall 15.4 ...

Appreciate your help .

Regards

Ajay Pandey

[markmumy]

You can simply delete the old directory. No need to really keep it but I guess you could.

[former_member187856]

Mark,

We encountered a strange performance issue, IQ 16 is 10 or 20 times slower than IQ 154.

The SQL has left outer join , just plain 2 table join; not a perfect written SQL.

Here is what I did:

1. Rebuilt wide column tables

2. Rebuild N-bit for these 2 tables

3. Convert to tiered HG

4. DBCC check tables, all clean for these 2 tables

set temporary option REVERT_TO_V15_OPTIMIZER='ON';

select top 10 ... from ..left outer join ... ;

Without REVERT_TO_V15_OPTIMZER='ON', this SQL takes over 80 sec to display 10 rows, and does not want to terminate here. It holds the table locks for long period (10 or 30 min).

[marco_antonini]

Hi,

Did you try to modify the option join_preference at the biginning of the procedure or query code ?

Es: SET TEMPORARY OPTION JOIN_PREFERENCE=7

value ranges from -7 and +7

look for the meanings into the IQ doc

[former_member187856]

IQ 154 and 16 show similar/same query plan, using #03 Right Outer Join (Hash PushDown).

And I believe if I truncate the table and reload the data back, IQ 16 works fine.

But the issue the DB is over 20TB, I can't accept reload data as a solution here.

[c_baker]

Which SP/PL of IQ16?

What are your -iqmc, -iqtc and -iqlm settings?

Can you post an HTML query plan?

What does the index advisor show?  Have you indexed the join columns?  Are there any indexes that should be HG-U as alternate keys?

Chris

[markmumy]

You mention that reloading seems to "fix" it?  Does that mean that you did not have the change to run sp_iqrebuildindex on each column of each table?  A complete reload should have the same affect as an sp_iqrebuildindex.  Check out the query plans and make sure they show n-bit and not FP indexes.

Mark

[former_member187856]

Mark,

You are correct, reloaded data still same issue here.

In place upgrade from IQ 154 to IQ 16 sp10pl10, I did N-bit index rebuild for all tables.

And dbcc check table on these 2 are clean.

Case is opened, and I am waiting for a call from support.

[markmumy]

After the server is started you then need to run alter database upgrade.  Once that is done, you then need to rebuild all column indexes via sp_iqrebuildindex.  This will force the FPs to be converted to N-bit.  Secondly, you want to drop all LF indexes and convert to HGs.  IQ 16 did a lot of overhaul in the HG  index to now include the features of the LF with little or no HG overhead on loading and improvements on queries.

Don't forget to reset the options even those that we set to non-standard when doing an upgrade.  This will allow IQ to use the newer N-bit indexes and the IQ 16 optimizer.

Mark

[Former Member]

is there any way to build sp_iqrebuildindex  on table level . instead of column level ..

[markmumy]

No, unfortunately.  You really want that fine grained control so that you can decide how many resources you consume at any point in time.

Mark

[tayeb_hadjou]

Hi Ajay,

You need review carefuly migration guide

SAP IQ Migration Guide - SAP Library

Upgrade tasks may differ depending on whether your are running Simplex or Multplex.

Check pre and post installation and upgrade tasks (eg. make sure OS release fits the compatibility requirements).

SAP IQ Installation and Configuration Guide - SAP Library

Also

1- before 15.4 database backup and upgrade, make sure database is consistent :

sp_iqcheckdb  'verfiy database';

2- Save into a file your current database setting : select * from sysoptions order by 2 ;

Regards,

Tayeb

[Former Member]

Thanks Tayeb,

Below is the detail step that i outlined ..

1)

sp_iqcheckdb('allocation database')

sp_iqcheckdb 'verify database'

sp_iqcheckdb 'allocation database'

   sp_iqcheckopion

2) backup of database ..

3) Extract binary in new IQ16 folder

mkdir work

mkdir iq16

cd work

<put the software in this directory>

tar -xf <software-distro>

Go into the directory with the installed software. Run the installer (for instance ./setup.bin).

./setup.bin

Answer the questions and install into /opt/sybase/iq16

/opt/sybase/iq16

4) shutdown IQ server (15)

stop_iq

5) Start IQ with new binary ...

In /opt/sybase/iq16 you will find files like IQ.sh and IQ.csh. Depending on the type of shell you use, you need to source in one of these. For instance, when

your default shell is bash you should do this:

. /opt/sybase/iq16/IQ.sh

Is is fairly common to activate such a script from your login script.

1) For the tcsh or C (csh) shell, enter:

source $SYBASE/IQ-16_0/IQ-16_0.csh

2) For the Korn (ksh), Bash, or Bourne (sh) shells, enter:

.$SYBASE/IQ-16_0/IQ-16_0.sh

In the directory /var/sybase/IQ1 create a configuration file called params.cfg with at least the following content

Simplex Start the SAP Sybase IQ 16.0 server using the -gm 1 and -iqro 1

startup flags.

start_iq @./params.cfg ./IQ1.db  -gm 1  -iqro 1

5) using DBISQL

alter database  upgrade .

====