DB2 OLAP Server Installation Guide

Migrating DB2 OLAP Server

This section describes how to upgrade from an earlier version of DB2 OLAP Server to DB2 OLAP Server Version 8.1 on the same computer and describes what occurs during the upgrade process.

Migrate from RSM to MSM before upgrading to Version 8.1

Caution:
Starting in Version 8.1, the Relational Storage Manager (RSM) is withdrawn from DB2 OLAP Server. Applications created using the RSM in previous versions must be migrated to the Multidimensional Storage Manager (MSM) before installing Version 8.1, or you will lose data.

If you are upgrading from an earlier version of DB2 OLAP Server and you have applications that use the Relational Storage Manager (RSM), you must first migrate them to the Multidimensional Storage Manager (MSM) in the earlier version of DB2 OLAP Server before installing Version 8.1, or you will lose data. After migrating your RSM applications to the MSM, you can install Version 8.1, and then migrate your applications to Version 8.1.

The following steps show you how to migrate data from the RSM to the MSM:

1. Start the DB2 OLAP Server.
2. Create an OLAP database in the appropriate application using the Application Manager or a command from the command prompt.
3. Using the Application Manager, click Start > Open to open the outline for the database you want to migrate.
4. Determine which dense dimension you want to be the anchor dimension, and remove the RELANCHOR user-defined attribute to that dimension.

   You must specify an anchor dimension.

5. Click File > Save As to save the outline for the new database that you created. If you are prompted to replace or overwrite an existing file, select Yes.

   The OLAP database is activated and restructured, and the modified outline becomes the outline for the new database. During this process, the cube is stored in a multidimensional database.

6. Use the Application Manager to copy any rules files, calculation scripts, or report scripts from the original database to the migrated one. The rules and scripts are not affected by the differences in the two storage managers. The files exist in the same locations in the file system for both products.
7. Change any settings of the new OLAP database to match the original database.
8. Load data into the newly migrated database, and recalculate it in the same manner as the original. As data is loaded and calculated, it is stored in the multidimensional database.

Understanding input/output defaults and upgrading

Read this section before upgrading to understand the two I/O access modes available with Version 8.1, and how OLAP databases are affected by an upgrade to Version 8.1, in terms of cache sizes and the I/O access modes.

Determining which I/O access mode to use

Buffered I/O uses the buffer cache of the file system. If you are upgrading from a release prior to Version 7.1, your databases are using buffered I/O.

Direct I/O bypasses the file system buffer cache and is able to provide faster response time and more potential to optimize cache sizes. If you are upgrading from Version 7.1 or later, your databases are using direct I/O.

Cache memory locking can only be used if direct I/O is used. You also must use direct I/O if you want to use the no-wait (asynchronous) I/O of the operating system. For platform-support information related to I/O, see Table 24

Table 23 shows the default I/O access mode for each release, and the I/O access mode choices available (if applicable) for each release. Use this table and this section to determine whether you are currently using buffered or direct I/O, and to decide which you will use after you upgrade to Version 8.1.

Table 23. Default I/O access modes for each DB2 OLAP Server version

The following list can help you to determine which I/O access mode your databases currently use, and how those databases will upgrade if you do not make any changes.

• Databases in Version 7.1, FixPak 8 and later (Essbase Release 6.2) use buffered I/O, unless essbase.cfg contains the setting DIRECTIO TRUE. When upgraded to Version 8.1, those databases will use same the I/O access mode that they used previously. The I/O access mode for any database can be changed after it is upgraded to Version 8.1, using the database settings.
• Databases in Version 7, up to FixPak 7 (Essbase Releases 6.0 and 6.1) use direct I/O. When upgraded to Version 8.1, these databases will use buffered I/O, unless essbase.cfg contains the setting DIRECTIO TRUE. The I/O access mode for any database can be changed after you upgrade to Version 8.1 by using the database settings.
• Databases in releases prior to Version 7.1 (Essbase 6.0) use buffered I/O. When upgraded to Version 8.1 (Release 6.5), all pre-Version 7.1 databases will use buffered I/O, unless essbase.cfg contains the setting DIRECTIO TRUE. The I/O access mode for any database can be changed after you upgrade to Version 8.1 by using the database settings.

The DIRECTIO setting introduced in Version 7 FixPak 8 is server-wide, and affects all databases. With Version 8, the access mode specified by DIRECTIO is only read once for each database, after upgrading or first creation of a database. Thereafter, the I/O access mode must be changed per database using the database settings.

Changing or preserving the I/O access mode

With Version 8.1, the I/O access mode is a database setting stored in the security file, rather than a server-wide essbase.cfg file setting that affects all databases. The essbase.cfg configuration-file setting DIRECTIO TRUE|FALSE is maintained for backward compatibility with Version 7 FixPak 8. It is also used to provide a default value for newly created databases and for databases that are upgraded from an earlier release.

If a DIRECTIO setting exists in the essbase.cfg file at upgrade time, only newly created or upgraded databases will be affected by the setting. DB2 OLAP Server reads the I/O access mode specification from essbase.cfg once for each database, and copies that information to the security file (essbase.sec). Thereafter, if you want to change the I/O access mode used by any database, you must change it at the database level using the database setting.

The I/O access mode can be set from Application Manager (Database Settings > Storage tab), MaxL (alter database set io_access_mode), or programmatically using the Application Programming Interface. For more information, see the Database Administrator's Guide, the MaxL documentation in the Technical Reference, or the API Reference.

If you want to use the no-wait I/O of an operating system, select direct I/O as the DB2 OLAP Server I/O access mode. DB2 OLAP Server attempts to use no-wait I/O, when available, as long as direct I/O is the I/O access mode. To determine whether DB2 OLAP Server is using no-wait I/O at a particular time, view the database information in Application Manager (Database Information > Storage tab), MaxL (display database), or programmatically using the Application Programming Interface. For a list of platforms on which DB2 OLAP Server supports no-wait I/O, see Table 24.

If you set a database to use direct I/O, DB2 OLAP Server will attempt to use direct I/O the next time the database is started. If direct I/O is not available on the platform at the time the database is started, DB2 OLAP Server will use buffered I/O, which is the default. However, DB2 OLAP Server stores the I/O access mode you selected as a setting in the security file, and will attempt to use that I/O access mode each time the database is started.

Understanding how cache sizes are affected by an upgrade

When you upgrade, your cache sizes for existing databases will not change. If you are currently running Version 7.1 up through FixPak 7 and using the default I/O access mode (direct), your cache sizes for existing databases are probably large, because direct I/O requires larger cache sizes. If, after you upgrade, you plan to use the default Version 8.1 I/O access mode (buffered), you should reduce the cache size settings before upgrading or before you start the upgraded database.

The following list explains default cache sizes for DB2 OLAP Server databases when upgrading to Release 6.5, for each I/O access mode:

• The data cache default size is the same for direct I/O and buffered I/O: 3 MB.
• The index cache default size is smaller for buffered I/O than for direct I/O: 1 MB for buffered I/O and 10 MB for direct I/O.
• The data file cache default size is 32 MB when direct I/O is used. The data file cache is not used when buffered I/O is in effect.
• With Version 8.1, the size of index pages is fixed at 8 K, regardless of the I/O access mode. The index page size is unchangeable.

To override the defaults after upgrading, change the database settings or properties before you start the upgraded database for the first time.

For more information on buffered I/O and direct I/O, see the Database Administrator's Guide.

Platforms on which DB2 OLAP Server supports no-wait I/O

Table 24 shows the platforms on which DB2 OLAP Server supports no-wait (or asynchronous) I/O. Although no-wait I/O is not used by DB2 OLAP Server on Solaris Operating System and AIX, direct I/O is still available for those platforms.

Table 24. Platforms on which DB2 OLAP Server supports no-wait (asynchronous) I/O

Notes:

1. On Windows XP, Windows NT, and Windows 2000, the DB2 OLAP Server Agent must be started by an administrator or power user to enable Cache Memory Locking (optional). Specifically, a power user is one who can run processes with the SE_INC_BASE_PRIORITY_NAMEprivilege set.

2. After the server software is installed, the root user must run a script, root.sh, to enable Cache Memory Locking (optional).

3. Direct I/O not supported on HFS on HP-UX.

Considerations when upgrading from previous releases

The following additional migration considerations may apply to your upgrade situation:

• As of Version 7.1, you are no longer required to install and run DB2 OLAP Server from an account called arbor. Any user can install, but this must be the user who starts DB2 OLAP Server.
• Version 7.1 introduced Cache Memory Locking, which locks the memory used for the index cache, data file cache, and data cache into physical memory, giving the OLAP kernel priority use of system RAM. After installing DB2 OLAP Server, you can use the Cache Memory Locking feature available in Release 6.0 or later if the following prerequisites are met:
  • You are using direct I/O. Direct I/O is the default for Version 7.1, and direct I/O is the only option for that release.
  • On Solaris, this feature requires you to first run the Bourne shell script, root.sh, from the root account as part of the installation procedure.

Upgrading databases to Version 8.1

This section provides migration details and tells you how to upgrade databases from earlier versions of DB2 OLAP Server to Version 8.1.

When does DB2 OLAP Server migrate the files?

DB2 OLAP Server migrates databases when the database is started. By default, a database is set to start when its application starts. The OLAP kernel checks for files resulting from previous unsuccessful migrations, restarting the migration if necessary.

DB2 OLAP Server migrates the ESSxxxxx.IND, dbname.ESM, and thedbname.TCT files when the database is started. DB2 OLAP Server migrates the ESSxxxx.PAG file as data blocks are accessed; hence, the .PAG file migrates when you run the VALIDATE command after starting the database. After the kernel files are migrated, they are not backwards-compatible with the earlier release.

Steps for upgrading databases to Version 8.1

The following steps explain how to upgrade to Version 8.1 from an earlier release on the same computer. To upgrade and migrate databases to another computer, see Migrating applications and databases across servers.

After migration, you can restore databases from earlier releases only from backups. Therefore, be sure to back up databases before starting to upgrade.

To upgrade to Version 8.1 on the same computer, proceed in the following order for each database:

1. Run the VALIDATE command in ESSCMD against the database. VALIDATE prompts you to specify a name for the error log file that it will use.

   If VALIDATE returns errors, revert to a backup that is free of those errors.

2. Back up all application files, database files, and the security file.
3. If you are using LROs in a production environment, run the LISTLINKEDOBJECTS command in ESSCMD before upgrading. This command returns a list of LROs contained in the databases.

   The VALIDATE checks for LRO errors.

4. Stop the OLAP server, if it is running.
5. Install DB2 OLAP Server Version 8.1 to the same directory as the earlier DB2 OLAP Server installation.
6. Start the DB2 OLAP Server Agent (ESSBASE.EXE ).

   If you want to change database settings, this is a convenient point at which to do so. If you change the settings now, you will not have to restart the database to make the settings effective. See Determining which I/O access mode to use for information on the default settings.

7. Select a database or load an application.
8. Run the VALIDATE command in ESSCMD against the migrated database. VALIDATE prompts you to specify a name for the error log file that it will use.
9. If VALIDATE returns only LRO-related errors to the log file after upgrading, you must restore data from the earlier backup and re-create the LROs:
   1. Either restore data from a backup of the database that does not contain LROs, or reload from a database export.
   2. Restart the database in DB2 OLAP Server Version 8.1.

      DB2 OLAP Server migrates the database to Version 8.1 format if the database was restored.

   3. Run the VALIDATE command.
   4. Re-create the LROs, using the LISTLINKEDOBJECTS output as a guide. You may need to review the output from LISTLINKEDOBJECTS manually to verify its completeness.
10. Upon successful completion, unload the database and then back up the Version 8.1 database files.

Opening retrieval wizard files in Query Designer

In Version 7.1 and later, Essbase Query Designer (EQD) replaces Retrieval Wizard for creating queries. If you have Retrieval Wizard (.WIZ) files, the EQD may not properly translate Retrieval Wizard subsets to EQD member filters, which could cause the following problems:

• The EQD does not interpret or acknowledge parentheses within the subsets.
• If the subset contains both AND and OR conditions, the EQD may not group the conditions as expected, which may affect the result of the retrieval.

After opening Retrieval Wizard files, make sure the navigation panel nodes define the member filters as you want them. If necessary, manually promote, demote, or move nodes around or add new nodes.

Windows system file updates

On Windows platforms, the DB2 OLAP Server installation program updates the client or server system environment to run Version 8.1 software. Microsoft system files are installed to the Windows system directory (for example, C:\Winnt\System32 on Windows NT 4.0) if the files do not already exist, or if the version shipped with DB2 OLAP Server is newer.

The following table lists Windows system files installed with each DB2 OLAP Server component on supported Windows platforms. A check mark in a column indicates that the system file is installed with the specified DB2 OLAP Server component. These files will be installed to your system directory only if the files do not already exist, or if older versions exist.

Table 25. System files installed with each DB2 OLAP Server component

Notes:

1. Do not use file versions 6.00 through 6.00.8336 because these file versions may cause memory allocation problems.

2. Do not use file versions 6.00 through 6.00.8266 because these file versions may cause memory allocation problems.

Migrating applications and databases across servers

To move databases to a different computer, or to upgrade to Version 8.1 on a different computer manually, proceed in the following order for each database:

1. Run the VALIDATE command in ESSCMD against the database you will migrate. VALIDATE prompts you to specify a name for the error log file that it will use.

   If VALIDATE returns errors, revert to a backup that is free of those errors.

2. Back up all application files, database files, and the security file on the original server (that is, the server from which you are migrating).
3. Install DB2 OLAP Server on the new server computer. If you are upgrading to a newer release of DB2 OLAP Server, install that release to the new server.
4. Copy the ESSBASE.CFGfile from the ESSBASE\BINdirectory on the original OLAP server to the same directory on the new OLAP server using the file system.
5. On the new OLAP server, define disk volumes. To allocate a new volume, use SETDBSTATEITEM 23 in ESSCMD and follow the prompts. You can also use the Database Settings dialog box in Application Manager or the alter database statement in MaxL.

   Consider carefully how you configure your disk volumes. Any changes you make to your disk volumes settings after you have loaded data on the new OLAP Server are reflected only in new data loads; changes are not retroactive.

6. Using Application Manager, MaxL, or ESSCMD, create new applications and databases on the new OLAP Server.

   Note:

   You can use the Security Migration Tool or Essbase Administration Services to migrate applications and databases (without re-creating them) from one OLAP Server to another, regardless of platform. For example, you can develop an application on a Windows server and migrate it to a production server running UNIX. When you migrate applications and databases across servers using Administration Services, the following information is copied to the target server: all databases and outlines, OLAP objects, user/group security for the application or database (including filter associations), and security filters.

   The names of the applications and database you create do not need to be the same as the ones on the original server. However, if you make changes to the names, make sure that these changes are reflected as necessary in script files, spreadsheet macros, and API-based applications. In addition, make sure that these changes are clearly communicated to the user base.

   Caution:
   Do not move the application directory to the new server through a file transfer via the operating system or via FTP.

7. On a client or server computer that has connectivity to both the original and new OLAP Servers, open Application Manager.
8. Select File > Open, click Client, and select the original server, application, and database.
9. Open the original outline (.OTL ) file, and select File > Save As.
10. Save the .OTL file to the new application and database on the new server.

    At this point, you should avoid making changes to the outline if you want to be able to import the data file that you will export from the original application.

11. Move all calc scripts, rules files, and report scripts to the new server using one of the following procedures:
    • Repeat steps 8 through 11 of this procedure.
    • Move the files to the new server via the file system or via FTP.

    If you are migrating between different server platforms, be sure to use the procedure described in steps 8 through 11 of this procedure. If you are migrating between the same server platforms, calc script and report script files can be moved using the operating system. If you move these files via the operating system, check to make sure that they function properly after moving them.

    Caution:
    Data load rules files are binary files and should always be migrated as described in steps 8-11.

12. Move any ESSCMD or MaxL scripts to the new server using the file system or via FTP. You can use the MaxL ESSCMD-to-MaxL script conversion utility.
13. If you are migrating a Version 8.1 database to another computer and the database uses linked reporting objects, export the LROs so you can re-import them to the migrated database. Use the export lro and import lro MaxL statements.

    Note:

    If you decide to clear data from the original database, note that any LROs you want to migrate must be exported before data is cleared. When data is cleared, LROs are also cleared.

14. Export data from the application on the original server in one of the following ways:
    • Choose All Data, Input Level Data, or 0 Level Data, depending on the size of your data set as well as how the database was initially loaded.
    • Use the initial data load files that were used to populate the database on the original server.
15. Import data to the new application on the new server.
16. Recalculate your database if:
    • You loaded input-level data from data load files, or
    • You exported only partial data from the database (for example, if you chose "Level 0" or "Input Level" data only)
17. If you exported any linked-reporting objects (LROs) from the original database, re-import them to the migrated database using the import lro MaxL statement.
18. Repeat these steps for all other databases on the original server that you want to migrate to the new server.
19. Migrate security information by recreating user filters, groups, and permissions on the new server.

    Note:

    If you are migrating Version 8.1 databases from one server to another, you can use the Security Migration tool to migrate security information.

    Caution:
    Moving the security file (ESSBASE.SEC and its backup ESSBASE.BAK) between computers is not recommended or supported.