Sybase ASE 12.5.0.3 - Index creation / build time

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks Bill for a really quick response. Great explanation.
I agree with your views. We had thought of following approaches:
1) Cross-platform dump/load across HP-UX and AIX with target ASE version 15.0.3 (big bang).
2) Patch ASE 12.5.0.3 on HP-UX to 12.5.3 or higher and then perform cross-platform migration using dump and load (phased)
3) Replication server
I am comfortable with second approach - I trust there will be absolutely no issues with patch upgrade from 12.5.0.3 to 12.5.4 on HP-UX (application will not break) and then using dump-and-load to move to AIX.
However, there is very much insistance about AS-IS move of ASE instance (no version change) from HP-UX to AIX. Replication server seems to be best suited option if ASE version has to remain same, but it will add cost as you have mentioned. With this background, I was trying to explore BCP option for cross-platform data migration. Index creation time is my major concern.
I am sorry, I do not have much details about hardware at production site at this moment. Depending on server capacity, parallelism could be built to speed up index creation with upto 3-4 threads.. this is what I am thinking as of now and hence wanted views from experts in this forums.
Thanks.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks alpmoon for your response.
50% of the tables have more than 1 million records - larget tables having 35million rows and table data size in GBs
e.g. few top tables:
rowcnt       TblSize MB
======== ============
35,022,418       13636.90
7,972,884       6827.97
7,843,554       6717.22
5,979,043       4491.51
4,392,986       3762.15
4,767,137       3546.11
17,261,056       3160.59
6,452,595       2416.01
13,412,844       1982.62
5,199,808       1558.72

Considering 4 parallel threads, can we expect index creation to be optimized?

Thanks for your time.

ASKER

Hi,

Any thoughts of using Sybmigrate in this scenario please....

Thanks.

ASKER

I guess with 30 very large databases, sybmigrate may not help as running more than one
session of sybmigrate on the same source and target database path is not allowed..

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi Bill,
Target machine is likely to be IBM Power 570 series.
http://www-03.ibm.com/systems/power/hardware/570/specs.html
This has 4 GHz processor whereas current HP server (HP9000-82) has 1 GHz processor.

From your experience, would you be able to comment if this IBM P 570 machine will be able to take up multiple parallel workers for migration of 3 TB data (10000 tables + 15000 indexes) over weekend?
Thanks for your time.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi Bill,

Thanks for elaborate explanation again.
Sorry, I did not clarify on number of CPUs - client is willing to know what resources will be required to optimize parallelism for this operation and not restricted to fewer CPUs or RAM.

Amongst 10000 tables, 50% of tables have millions of rows - more than 10 years data is residing in the databases and latest update is most of the data is loaded through flat files (which may provide us opportunities to preload some of the history data into target instance ahead of downtime window).

I am also of opinion that it may be possible to optimize BCP for data load, but index building and optimizing and testing parallelism is going to be a time taking task. The risk is higher than dump and load method as requirement is to complete prod migration by Oct. Testing/optimizing BCP or Sybmigrate in DEV/Test environment will certainly need more time before we rollout prod environment.

Regards,
Ganesh

ASKER

hi Alpmoon,

Thanks for your response.
Amongst 10000 tables, 50% of tables have millions of rows - more than 10 years data is residing in the databases. Amongst 15000 indexes, 150 are cluster indexes. A large chunk of data is loaded through flat files. The largest database is about 1 TB is size,2nd largest is about 500GB and rest of the databases are in range of 50-300GB. I agree with you about parallel operations and we are not considering serial option for large databases. My only worry is about likely contention due to many parallel threads causing delay in completing migration.

Regards,
Ganesh

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.