I would like to know your advice about migrating a database from WE8ISO8859P15 to AL32UTF8. We have a DB running WE8ISO8859P15 properly with a lot of applications, but a client suggests that it would be a good idea to change it so it is the same in their Java applications. These applications are not multilingual and they will never be, so do you think it would have some benefit? or won't be a good idea because of space consumption or other issues?
and we said...
I answered this question online in the Office Hours session but I would like to answer it on the AskTOM site as well. Here is a short list of the pros and cons of migrating from WE8ISO8859P15 to AL32UTF8 without an immediate business need.
Pros: Why it may be better to migrate from WE8ISO8859P15 to AL32UTF8 now
1. Even if it seems that Unicode will never be a requirement, you may be surprised in a few years to learn that the market has changed, new opportunities need to be explored, and new data from other parts of the world will come in. Even technical data may start supporting internationalized user IDs, file names, web addresses, or similar seemingly ASCII-only data.
2. It is better to migrate earlier when the database has accumulated less data and fewer issues.
3. It is better to migrate where there is no immediate business pressure. You can prepare the project better, test it better, plan for the most convenient downtime window, etc.
4. WE8ISO8859P15 does not support all Windows characters, like: ‚ ƒ „ … † ‡ ˆ ‰ Š ‹ Œ Ž ‘ ’ “ ” • – — ˜ ™ š › œ ž Ÿ. With Windows clients (WE8MSWIN1252), you may already have illegal data in some columns (via a pass-through a.k.a. garbage-in garbage-out configuration). It is better to migrate to a character set that supports these characters, so why not directly to Unicode?
Cons: When migration may not be such a good idea:
1. The confidence ratio in the statement that the application is not going to be multilingual is very high, and the database is very large and most of the space is occupied by CLOB values. CLOB values double in length when migrated from a single-byte database to AL32UTF8. By very large, I mean a database where the cost of additional disks really matters.
2. You know that the database is close to its end of life, is going to be archived, and replaced with a new system.
3. There is a huge number of difficult-to-solve migration issues in the database, like exceeding the data type limit of 32K bytes. Without a business need, there is no justification for the high cost of the migration project.