Release Notes

Data Warehouse Center

• When creating editioned SQL steps, based on usage, you might want to consider creating a non-unique index on the edition column to speed performance of deleting of editions. Consider this for large warehouse tables only, since the performance of inserts can be impacted when inserting a small numbers of rows.
• In the Process Model window, if you change a source or target, the change that you made is automatically saved immediately. If you make any other change, such as adding a step, you must explicitly save the change to make the change permanent. To save the change, click Process --> Save.
• You can specify up to 254 characters in the Description field of notebooks in the Data Warehouse Center. This maximum replaces the maximum lengths specified in the online help.
• You cannot successfully run a Sample Contents request that uses the AS/400 agent on a flat file source. Although you can create a flat file source and attempt to use an AS/400 agent to issue a sampleContent request, the request will fail.
• You might receive an error when you run Sample Contents on a warehouse target in the process modeler. This error is related to the availability of a common agent site to warehouse source, warehouse target, and step in a process. The list of available agent sites for a step is obtained from the intersection of the warehouse source IR agent sites, the warehouse target IR agent sites, and the agent sites available for this particular step (The steps are selected in the last page of the agent sites properties notebook). For example, You want to view the Sample Contents for a process that runs the FTP Put program (VWPRCPY). The step used in the process must be selected for the agent site in the agent site definition. When you run Sample Contents against the Target file, the first agent site on the selected list is usually used. However, database maintenance operations might affect the order of the agent sites listed. Sample contents will fail if the agent site selected does not reside in the same system as the source or target file.
• If Import of a tag language file larger than about 500KB is run from the Warehouse Center, the import utility (iwh2imp2.exe) might hang. If you notice that iwh2imp2 is not using a lot of CPU, it has probably hung and you should end the iwh2imp2 process and try the import again from the DOS command line.
• When you try to edit the Create DDL SQL statement for a target table for a step in development mode, you see the following misleading message: "Any change to the Create DDL SQL statement will not be reflected on the table definition or actual physical table. Do you want to continue?"

  The change will be reflected in the actual physical table. Ignore the message and continue changing the Create DDL statement.

  The corrected version of this message for steps in development mode should read as follows: "Any change to the Create DDL SQL statement will not be reflected in the table definition. Do you want to continue?"

  For steps in test or production mode, the message is correct. The Data Warehouse Center will not change the physical target table that was created when you promoted the step to test mode.

• If you want to migrate Visual Warehouse metadata synchronization business views to the Data Warehouse Center, promote the business views to production status before you migrate the warehouse control database. If the business views are in production status, their schedules are migrated to the Data Warehouse Center. If the business views are not in production status, they will be migrated in test status without their schedules. You cannot promote the migrated steps to production status. You must create the synchronization steps again in the Data Warehouse Center and delete the migrated steps.
• When the Data Warehouse Center generates the target table for a step, it does not generate a primary key for the target table. Some of the transformers, such as Moving Average, use the generated table as a source table and also require that the source table have a primary key. Before you use the generated table with the transformer, define the primary key for the table by right-clicking the table in the DB2 Control Center and clicking Alter.
• To access Microsoft SQL Server on Windows NT using the Merant ODBC drivers, verify that the system path contains the sqllib\odbc32 directory.
• When you define a warehouse source or warehouse target for an OS/2 database, type the database name in uppercase letters.
• The DB2 Control Center or the Command Line Processor might indicate that the warehouse control database is in an inconsistent state. This state is expected because it indicates that the warehouse server did not commit its initial startup message to the warehouse logger.
• In the data warehousing sample contained in the TBC_MD database, you cannot use SQL Assist to change the SQL in the Select Scenario SQL step, because the SQL was edited after it was generated by SQL Assist.
• To use the FormatDate function, click Build SQL on the SQL Statement page of the Properties notebook for an SQL step.

  The output of the FormatDate function is of data type varchar(255). You cannot change the data type by selecting Date, Time, or Date/Time from the Category list on the Function Parameters - FormatDate page.

• On AIX and the Solaris Operating Environment, the installation process sets the language for import and export to the information catalog, and export to the OLAP Integration Server. If you want to use these functions in a language other than the language set during installation, create the following soft link by entering the following command on one line:

  On AIX

   /usr/bin/ln -sf  /usr/lpp/db2_07_01/msg/locale/flgnxolv.str
   
  

   /usr/lpp/db2_07_01/bin/flgnxolv.str
  

  locale

  The locale name of the language in xx_yy format

  On the Solaris Operating Environment

  /usr/bin/ln -sf  /opt/IBMdb2/V7.1/msg/locale/flgnxolv.str
  

  /opt/IBMdb2/V7.1/bin/flgnxolv.str
  

  locale

  The locale name of the language in xx_yy format

• When you use the Update the value in the key column option of the Generate Key Table transformer, the transformer updates only those rows in the table that do not have key values. (That is, the values are null). If the starting key value is coming from a column other than the designated key column, duplicate keys might be generated if you select the maximum value of the column as the starting value, which is used and incremented to create the values in the key column. When additional rows are inserted into the table, the key values are null until you run the transformer again. When you run the transformer again using the update option and the same non-key column that you specified in the previous run, and if the maximum value of the non-key column in the newly inserted rows is less than the maximum value used in the previous run, then duplicate key values will be generated.

  To avoid this problem, use one of the following approaches:

  • Use the designated key column as the source of the starting key value when you use the update option. This approach ensures that the keys will always be in sequence and duplicates will not be created.
  • After the initial run of the transformer, use the Replace all values option to create the keys for all the rows again.
• The warehouse server does not maintain connections to local or remote databases when the DB2 server that manages the databases is stopped and restarted. If you stop and restart DB2, then stop and restart the warehouse services as well.
• When you install the DB2 Administration Client and the Data Warehousing Tools to set up a Data Warehouse Center administrative client on a different workstation from the one that contains the warehouse server, you must add the TCP/IP port number at which the warehouse server workstation is listening to the services file for the client workstation. Add an entry into the services file as follows:

   vwkernel        11000/tcp
  

• When you define a warehouse source for a DB2 for VM database, which is accessed through a DRDA gateway, there are restrictions on the use of CLOB and BLOB data types:
  • You cannot use the Sample Contents function to view data of CLOB and BLOB data types.
  • You cannot use columns of CLOB and BLOB data types with an SQL step.
  This restriction is a known restriction on the DB2 for VM Version 5.2 server in which LOB objects cannot be transmitted using DRDA to a DB2 Version 7.1 client.
• When you define a DB2 for VM or DB2 for VSE target table in the Data Warehouse Center, do not select the Grant to public check box. The GRANT command syntax that the Data Warehouse Center generates not supported on DB2 for VM and DB2 for VSE.
• To enable delimited identifier support for Sybase and Microsoft SQL Server on Windows NT: select the Enable Quoted Identifiers check box in the Advanced page of the ODBC Driver Setup notebook.

  To enable delimited identifier support for Sybase on UNIX, edit the Sybase data source in the .odbc.ini file to include the connect attribute EQI=1.

20.1 Data Warehouse Center Publications

20.1.1 Data Warehouse Center Application Integration Guide

In Chapter 8. Information Catalog Manager object types, the directory where you can find the .TYP files, which include the tag language for defining an object type, has been changed to \SQLLIB\DGWIN\TYPES.

20.1.2 Data Warehouse Center Administration Guide

• The Data Warehouse Center troubleshooting information has moved to the DB2 Troubleshooting Guide.
• In "Chapter 5. Defining and running processes", section "Starting a step from outside the Data Warehouse Center", it should be noted that JDK 1.1.8 or later is required on the warehouse server workstation and the agent site if you start a step that has a double-byte name.
• On page 180, section "Defining values for a Submit OS/390 JCL jobstream (VWPMVS) program," step 8 states that you must define a .netrc file in the same directory as the JES file. Instead, the program creates the .netrc file. If the file does not exist, the program creates the file in the home directory. If a .netrc file already exists in the home directory, the program renames the existing file and creates a new file. When the program finishes processing, it deletes the new .netrc file that it created and renames the original file to .netrc.
• In the Data warehousing sample appendix, section "Viewing and modifying the sample metadata", the GEOGRAPHIES table should be included in the list of source tables.
• In the Data warehousing sample appendix, section "Promoting the steps", in the procedure for promoting steps to production mode, the following statement is incorrect because the target table was created when you promoted the step to test mode:

     The Data Warehouse Center starts to create the target table,
     and displays a progress window.
  

• On Microsoft Windows NT and Windows 2000, the Data Warehouse Center logs events to the system event log. The Event ID corresponds to the Data Warehouse Center message number. For information about the Data Warehouse Center messages, refer to the Message Reference.
• The example in Figure 20 on page 315 has an error. The following commands are correct:

  "C:\IS\bin\olapicmd" < "C:\IS\Batch\my_script.script" >
  "C:\IS\Batch\my_script.log"
  

  The double quotation marks around "C:\IS\bin\olapicmd" are necessary if the name of a directory in the path contains a blank, such as Program Files.
• In "Appendix F. Using Classic Connect with the Data Warehouse Center", the section "Installing the CROSS ACCESS ODBC driver" on page 388 has been replaced with the following information:

     Install the CROSS ACCESS ODBC driver by performing a custom install
     of the DB2 Warehouse Manager Version 7, and selecting the Classic
     Connect Drivers component. The driver is not installed as part of
     a typical installation of the DB2 Warehouse Manager.
   
     The CROSS ACCESS ODBC driver will be installed in the ODBC32 subdirectory
     of the SQLLIB directory. After the installation is complete, you must manually
     add the path for the driver (for example, C:\Program Files\SQLLIB\ODBC32)
     to the PATH system environment variable. If you have another version
     of the CROSS ACCESS ODBC driver already installed, place the ...\SQLLIB\ODBC32\
     path before the path for the other version. The operating system will use
     the first directory in the path that contains the CROSS ACCESS ODBC driver.
  

• In "Appendix G. Data Warehouse Center environment structure" on page 401, there is an incorrect entry in the table. C:\Program Files\SQLLIB\ODBC32 is not added to the PATH environment variable. The only update to the PATH environment variable is C:\Program Files\SQLLIB\BIN.

20.1.3 Data Warehouse Center Messages

Data Warehouse Center message DWC3778E should read as follows: "Cannot delete a Data Warehouse Center default Data Warehouse Center Program Group."

Data Warehouse Center message DWC3806E should read as follows: "Step being created or updated is not associated with either a source resource or Data Warehouse Center program for population."

Data Warehouse Center message DWC6119E should read as follows: "The warehouse client failed to receive a response from the warehouse server."

20.1.4 Data Warehouse Center Online Help

• A table or view must be defined for replication using the DB2 Control Center before it can be used as a replication source in the Data Warehouse Center.
• Before running the Essbase VWPs with the AS/400 agent, ARBORLIB and ARBORPATH need to be set as *sys environment variables. To set these, the user ID must have *jobctl authority. These environment variables need to point to the library where Essbase is installed.
• Publish Data Warehouse Center Metadata window and associated properties window: In step 10 of the task help, there is an example states that if you specify a limit value of 1 (Limit the levels of objects in the tree) and publish a process, only 1 step from that process is published and displayed. This example is not correct in all situations. In step 8, on the second bulleted item, the first statement is incorrect. It should read "Click at the column level to generate a transformation object between an information catalog source column and a target column."
• Any references in the online help to "foreign keys" should read "warehouse foreign keys."
• Any references in the online help to the "Define Replication notebook" should read "replication step notebook."
• Importing a tag language online help: In the bulleted list showing common import errors, one item in the list is "Importing a tag language file that was not exported properly". This item is not applicable to the list of common input errors.
• In the "Add data" topic of the online help, the links to the "Adding source tables to a process" and "Adding target tables to a process" topics are broken. You can find these topics in the help index.

20.2 Warehouse Control Database

This section covers the following topics related to the management of warehouse control databases:

• The default warehouse control database
• The Warehouse Control Database Management window
• Changing the active warehouse control database
• Creating and initializing a warehouse control database
• Migrating IBM Visual Warehouse control databases for use with the DB2 Version 7.1 Data Warehouse Center

20.2.1 The default warehouse control database

During a typical DB2 installation on Windows NT or Windows 2000, DB2 creates and initializes a default warehouse control database for the Data Warehouse Center if there is no active warehouse control database identified in the Windows NT registry. Initialization is the process in which the Data Warehouse Center creates the control tables that are required to store Data Warehouse Center metadata.

The default warehouse control database is named DWCTRLDB. When you log on, the Data Warehouse Center specifies DWCTRLDB as the warehouse control database by default. To see the name of the warehouse control database that will be used, click the Advanced button on the Data Warehouse Center Logon window.

20.2.2 The Warehouse Control Database Management window

The Warehouse Control Database Management window is installed during a typical DB2 installation on Windows NT or Windows 2000. You can use this window to change the active warehouse control database, create and initialize new warehouse control databases, and migrate warehouse control databases that have been used with IBM Visual Warehouse. The following sections discuss each of these activities.

Stop the warehouse server before using the Warehouse Control Database Management window.

20.2.3 Changing the active warehouse control database

If you want to use a warehouse control database other than the active warehouse control database, use the Warehouse Control Database Management window to register the database as the active control database. If you specify a name other than the active warehouse control database when you log on to the Data Warehouse Center, you will receive an error that states that the database that you specified does not match the database specified by the warehouse server.

To register the database:

1. Click Start --> Programs --> IBM DB2 --> Warehouse Control Database Management.
2. In the New control database field, type the name of the control database that you want to use.
3. In the Schema field, type the name of the schema to use for the database.
4. In the User ID field, type the name of the user ID that is required to access the database
5. In the Password field, type the name of the password for the user ID.
6. In the Verify Password field, type the password again.
7. Click OK.

   The window remains open. The Messages field displays messages that indicate the status of the registration process.

8. After the process is complete, close the window.

20.2.4 Creating and initializing a warehouse control database

If you want to create a warehouse control database other than the default, you can create it during the installation process or after installation by using the Warehouse Control Database Management window. You can use the installation process to create a database on the same workstation as the warehouse server or on a different workstation.

To change the name of the warehouse control database that is created during installation, you must perform a custom installation and change the name on the Define a Local Warehouse Control Database window. The installation process will create the database with the name that you specify, initialize the database for use with the Data Warehouse Center, and register the database as the active warehouse control database.

To create a warehouse control database during installation on a workstation other than where the warehouse server is installed, select Warehouse Local Control Database during a custom installation. The installation process will create the database. After installation, you must then use the Warehouse Control Database Management window on the warehouse server workstation by following the steps in 20.2.3, Changing the active warehouse control database. Specify the database name that you specified during installation. The database will be initialized for use with the Data Warehouse Center and registered as the active warehouse control database.

To create and initialize a warehouse control database after the installation process, use the Warehouse Control Database Management window on the warehouse server workstation. If the new warehouse control database is not on the warehouse server workstation, you must create the database first and catalog it on the warehouse server workstation. Then follow the steps in 20.2.3, Changing the active warehouse control database. Specify the database name that you specified during installation.

When you log on to the Data Warehouse Center, click the Advanced button and type the name of the active warehouse control database.

20.2.5 Migrating IBM Visual Warehouse control databases

DB2 Universal Database Quick Beginnings for Windows provides information about how the active warehouse control database is migrated during a typical install of DB2 Universal Database Version 7.1 on Windows NT and Windows 2000. If you have more than one warehouse control database to be migrated, you must use the Warehouse Control Database Management window to migrate the additional databases. Only one warehouse control database can be active at a time. If the last database that you migrate is not the one that you intend to use when you next log on to the Data Warehouse Center, you must use the Warehouse Control Database Management window to register the database that you intend to use.

20.3 Setting up and running replication with Data Warehouse Center

1. Setting up and running replication with Data Warehouse Center requires that the Replication Control tables exist on both the Warehouse Control database and the Warehouse Target databases.

   Replication requires that the Replication Control tables exist on both the Control and Target databases. The Replication Control tables are found in the ASN schema and they all start with IBMSNAP. The Replication Control tables are automatically created for you on a database when you define a Replication Source via the Control Center, if the Control tables do not already exist. Note that the Control tables must also exist on the Target DB. To get a set of Control tables created on the target DB you can either create a Replication Source using Control Center, then remove the Replication Source, just leaving the Control tables in place. Or you can use the DJRA, Data Joiner Replication Administration, product to define just the control tables.

2. Installing and Using the DJRA

   If you want or need to use the DJRA to define the control tables, you will need to install it first. The DJRA ships as part of DB2. To install the DJRA, go to the d:\sqllib\djra directory (where your DB2 is installed) and click on the djra.exe package. This will install the DJRA on your system. To access the DJRA after that, on Windows NT, from the start menu, click on the DB2 for Windows NT selection, then select Replication, then select Replication Administration Tools. The DJRA interface is a bit different from usual NT applications. For each function that it performs, it creates a set of SQL to be run, but does not execute it. The user must manually save the generated SQL and then select the Execute SQL function to run the SQL.

3. Setup to Run Capture and Apply

   For the system that you are testing on, see the Replication Guide and Reference Manual for instructions on configuring your system to run the Capture and Apply program. You must bind the Capture and Apply programs on each database where they will be used. Note that you do NOT need to create a password file. The Data Warehouse Center will automatically create a password file for the Replication subscription.

4. Define a Replication Source in the Control Center

   Use the Control Center to define a Replication Source. The Data Warehouse Center supports five types of replication: user copy, point-in-time, base aggregate, change aggregate, and staging tables (CCD tables). The types of User Copy, Point-in-Time, and Condensed Staging table require that the replication source table have a primary key. The other replication types do not. Keep this in mind when choosing an input table to be defined as a Replication Source. A Replication Source is actually the definition of the original source table and a created CD (Change Data) table to hold the data changes before they are moved to the target table. When you define a Replication Source in the Control Center, a record is written out to ASN.IBMSNAP_REGISTER to define the source and its CD table. The CD table is created at the same time, but initially it contains no data. When you define a Replication Source you can choose to include only the after-image columns or both the before and after-image columns. These choices are made via checkboxes in the Control Center Replication Source interface. Your selection of before and after image columns is then translated into columns created in the new CD table. In the CD table, after-image columns have the same name as their original source table column names. The after-image columns will have a 'X' as the first character in the column name.

5. Import the Replication Source into the Data Warehouse Center

   Once you have created the Replication Source in the Control Center, you can import it into the Data Warehouse Center. When importing the source, be sure to click on the checkbox that says "Tables that can be replicated". This tells the Data Warehouse Center to look at the records in the ASN.IBMSNAP_REGISTER table to see what tables have been defined as Replication Sources.

6. Define a Replication Step in the Data Warehouse Center

   On the process modeler, select one of the five Replication types: base aggregate, change aggregate, point-in-time, staging table, or user copy. If you want to define a base aggregate or change aggregate replication type, see the section below about How to setup a Base Aggregate or Change Aggregate replication in the Data Warehouse Center. Select an appropriate Replication Source for the Replication type. As mentioned above, the replication types of: user copy, point-in-time, and condensed staging tables require that the input source have a primary key. Connect the Replication Source to the Replication Step. Open the properties on the Replication Step. Go to the Parameters tab. Select the desired columns. Select the checkbox to have a target table created. Select a Warehouse target. Go to the Processing Options and fill in the parameters. Press OK.

7. Start the Capture Program

   In a DOS window, enter: ASNCCP source-database COLD PRUNE

   The COLD parameter indicates a COLD start and will delete any existing data in the CD tables. The PRUNE parameter tells the capture program to maintain the IBMSNAP_PRUNCNTL table. Leave the Capture program running. When it comes time to quit, you can stop it with a Ctrl-Break in its DOS window. Be aware that you need to start the Capture program before you start the Apply program.

8. Replication Step Promote-To-Test

   Back in the Data Warehouse Center, for the defined Replication Step, promote the step to Test mode. This causes the Replication Subscription information to be written out to the Replication Control tables. You will see records added to IBMSNAP_SUBS_SET, IBMSNAP_SUBS_MEMBR, IBMSNAP_SUBS_COLS, and IBMSNAP_SUBS_EVENT to support the subscription. The target table will also be created in the target database. If the replication type is user copy, point-in-time, or condensed staging table, a primary key is required on the target table. Go to the Control Center to create the Primary Key. Note that some replication target tables also require unique indexes on various columns. Code exists in the Data Warehouse Center to create these unique indexes when the table is created so that you do NOT have to create these yourself. Note though that if you define a primary key in the Control Center and a unique index already exists for that column then you will get a WARNING message when you create the primary key. Ignore this warning message.

9. Replication Step Promote-To-Production

   No replication subscription changes are made during Promote-to-Production. This is strictly a Data Warehouse Center operation like any other step.

10. Run a Replication Step

    After a Replication Step has been promoted to Test mode, it can be run. Do an initial run before making any changes to the source table. Go to the Work-in-Progress (WIP) section and select the Replication Step. Run it. When the step is run, the event record in the IBMSNAP_SUBS_EVENT table is updated and the subscription record in IBMSNAP_SUBS_SET is posted to be active. The subscription should run immediately. When the subscription runs, the Apply program is called by the Agent to process the active subscriptions. If you update the original source table after that point, then the changed data will be moved into the CD table. If you run the replication step following that, such that the Apply program runs again, the changed data will be moved from the CD table to the target table.

11. Replication Step Demote-To-Test

    No replication subscription changes are made during Demote-to-Test. This is strictly a Data Warehouse Center operation like any other step.

12. Replication Step Demote-to-Development

    When you demote a Replication Step to development, the subscription information is removed from the Replication Control tables. No records will remain in the Replication Control tables for that particular subscription after the Demote-to-Development finishes. The target table will also be dropped at this point. The CD table remains in place since it belongs to the definition of the Replication Source.

13. How to setup a Base Aggregate or Change Aggregate Replication in the Data Warehouse Center.
    • Input table. Choose an input table that can be used with a GROUP BY statement. For our example we will use an Input table that has these columns: SALES, REGION, DISTRICT.
    • Replication step. Choose Base or Change Aggregate. Open the Step properties.
      • When the Apply program runs, it needs to execute a SELECT statement that looks like: SELECT SUM(SALES), REGION, DISTRICT GROUP BY REGION, DISTRICT Therefore in the output columns selected you will need to choose REGION, DISTRICT and one calculated column of SUM(SALES). Use the Add Calculated Column button. For our example enter: SUM(SALES) in the Expression field. Save it.
      • Where clause. There is a Replication requirement that when you set up a Replication step that only requires a GROUP BY clause then you must also provide a DUMMY where clause, such as 1=1. Do NOT include the word "WHERE" in the WHERE clause. Therefore in the Data Warehouse Center GUI for Base Aggregate there is only a WHERE clause entry field. In this field, for our example: enter: 1=1 GROUP BY REGION, DISTRICT For the Change Aggregate, there is both a WHERE clause and a GROUP BY entry field: In the WHERE clause field enter: 1=1 and in the GROUP BY field enter: GROUP BY REGION, DISTRICT
      • Setup the rest of the step properties, as you would do for any other type of Replication. Press OK to save the step and create the target table object.
    • Open the target table object. You now need to rename the output column for the calculated column expression to a valid column name and you need to specify a valid data type for the column. Save the target table object.
    • Run Promote-to-Test on the Replication step. The target table will be created. It does NOT need a primary key.
    • Run the step like any other Replication step.

20.4 Troubleshooting tips

• To turn on tracing for the Apply Program, set the Agent Trace value = 4 in the Warehouse Properties panel. The Agent turns on full tracing for Apply when Agent Trace = 4.

  If you don't see any data in the CD table, then most likely either the Capture program has not been started or you have not updated the original source table to create some changed data.

• The mail server field of the Notification page of the Schedule notebook is missing from the online help.
• The mail server needs to support ESMTP for the Data Warehouse Center notification to work. In the Open the Work in Progress window help, click Warehouse --> Work in Progress rather than Warehouse Center --> Work in Progress.

20.5 Correction to RUNSTATS and REORGANIZE TABLE Online Help

The online help for these utilities states that the table that you want to run statistics on, or that is to be reorganized, must be linked as both the source and the target. However, because the step writes to the source, you only need to link from the source to the step.

20.6 Notification Page (Warehouse Properties Notebook and Schedule Notebook)

On the Notification page of the Warehouse Properties notebook, the statement:

   The Sender entry field is initialized with the string <current user's logon ID>.

should be changed to:

   The Sender entry field is initialized with the string <current logon user email address>.

On the Notification page of the Schedule notebook, the sender will be initialized to what is set in the Warehouse Properties notebook. If nothing is set, it is initialized to the current logon user e-mail address. If there is no e-mail address associated with the logon user, the sender is set to the logon user ID.

20.7 Agent Module Field in the Agent Sites Notebook

The Agent Module field in the Agent Sites notebook provides the name of the program that is run when the warehouse agent daemon spawns the warehouse agent. Do not change the name of the field unless IBM directs you to do so.

20.8 Accessing DB2 Version 5 data with the DB2 Version 7.1 warehouse agent

DB2 Version 7.1 warehouse agents, as configured by the DB2 Version 7.1 install process, will support access to DB2 Version 6 and DB2 Version 7.1 data. If you need to access DB2 Version 5 data, you must take one of the following two approaches:

• Migrate DB2 Version 5 servers to DB2 Version 6 or DB2 Version 7.1.
• Modify the agent configuration, on the appropriate operating system, to allow access to DB2 Version 5 data.

DB2 Version 7.1 warehouse agents do not support access to data from DB2 Version 2 or any other previous versions.

20.8.1 Migrating DB2 Version 5 servers

For information about migrating DB2 Version 5 servers, see DB2 Universal Database Quick Beginnings for your operating system.

20.8.2 Changing the agent configuration

The following information describes how to change the agent configuration on each operating system. When you migrate the DB2 servers to DB2 Version 6 or later, remove the changes that you made to the configuration.

20.8.2.1 UNIX warehouse agents

To set up a UNIX warehouse agent to access data from DB2 Version 5 with either CLI or ODBC access:

1. Install the DB2 Version 6 run-time client. You can obtain the run-time client by selecting the client download from the following URL:

   http://www.ibm.com/software/data/db2/udb/support
   

2. Update the warehouse agent configuration file so that the DB2INSTANCE environment variable points to a DB2 Version 6 instance.
3. Catalog all databases in this DB2 Version 6 instance that the warehouse agent is to access.
4. Stop the agent daemon process by issuing the kill command with the agent daemon process ID. The agent daemon will then restart automatically. You need root authority to kill the process.

20.8.2.2 Microsoft Windows NT, Windows 2000, and OS/2 warehouse agents

To set up a Microsoft NT, Windows 2000 or OS/2 warehouse agent to access data from DB2 Version 5:

1. Install DB2 Connect Enterprise Edition Version 6 on a workstation other than where the DB2 Version 7.1 warehouse agent is installed.

   DB2 Connect Enterprise Edition is included as part of DB2 Universal Database Enterprise Edition and DB2 Universal Database Enterprise Extended Edition. If Version 6 of either of these DB2 products is installed, you do not need to install DB2 Connect separately.

   Restriction:

   You cannot install multiple versions of DB2 on the same Windows NT or OS/2 workstation. You can install DB2 Connect on another Windows NT workstation or on an OS/2 or UNIX workstation.

2. Configure the warehouse agent and DB2 Connect Version 6 for access to the DB2 Version 5 data. For more information, see the DB2 Connect User's Guide. The following steps are an overview of the steps that are required:
   1. On the DB2 Version 5 system, use the DB2 Command Line Processor to catalog the Version 5 database that the warehouse agent is to access.
   2. On the DB2 Connect system, use the DB2 Command Line Processor to catalog:
      • The TCP/IP node for the DB2 Version 5 system
      • The database for the DB2 Version 5 system
      • The DCS entry for the DB2 Version 5 system
   3. On the warehouse agent workstation, use the DB2 Command Line Processor to catalog:
      • The TCP/IP node for the DB2 Connect system
      • The database for the DB2 Connect system

   For information about cataloging databases, see the DB2 Universal Database Installation and Configuration Supplement.

3. At the warehouse agent workstation, bind the DB2 CLI package to each database that is to be accessed through DB2 Connect.

   The following DB2 commands give an example of binding to v5database, a hypothetical DB2 version 5 database. Use the DB2 Command Line Processor to issue the following commands. db2cli.lst and db2ajgrt are located in the \sqllib\bnd directory.

   db2 connect to v5database user userid using password
   db2 bind db2ajgrt.bnd
   db2 bind @db2cli.lst blocking all grant public
   

   where userid is the user ID for v5database and password is the password for the user ID.

   An error occurs when db2cli.list is bound to the DB2 Version 5 database. This error occurs because large objects (LOBs) are not supported in this configuration. This error will not affect the warehouse agent's access to the DB2 Version 5 database.

   Fixpak 14 for DB2 Universal Database Version 5, which is available in June, 2000, is required for accessing DB2 Version 5 data through DB2 Connect. Refer to APAR number JR14507 in that fixpak.

20.9 Accessing warehouse control databases

In a typical installation of DB2 Version 7.1 on Windows NT, a DB2 Version 7 warehouse control database is created along with the warehouse server. If you have a Visual Warehouse warehouse control database, you must upgrade the DB2 server containing the warehouse control database to DB2 Version 7.1 before the metadata in the warehouse control database can be migrated for use by the DB2 Version 7.1 Data Warehouse Center. You must migrate any warehouse control databases that you want to continue to use to Version 7.1. The metadata in your active warehouse control database is migrated to Version 7.1 during the DB2 Version 7.1 install process. To migrate the metadata in any additional warehouse control databases, use the Warehouse Control Database Migration utility, which you start by selecting Start --> Programs --> IBM DB2 --> Warehouse Control Database Management on Windows NT. For information about migrating your warehouse control databases, see DB2 Universal Database for Windows Quick Beginnings.

20.10 Accessing sources and targets

The following tables list the version and release levels of the sources and targets that the Data Warehouse Center supports.

Table 3. Version and release levels of supported IBM warehouse sources

Source	Version/Release
IMS	5.1
DB2 Universal Database for Windows NT	5.2 - 7.1
DB2 Universal Database Enterprise-Extended Edition	5.2 - 7.1
DB2 Universal Database for OS/2	5.2 - 7.1
DB2 Universal Database for AS/400	3.7 - 4.5
DB2 Universal Database for AIX	5.2 - 7.1
DB2 Universal Database for Solaris Operating Environment	5.2 - 7.1
DB2 Universal Database for OS/390	4.1 - 5.1.6
DB2 DataJoiner	2.1.2
DB2 for VM	3.4 - 5.3.4
DB2 for VSE	7.1

Source	Windows NT	AIX
Informix	7.2.2 - 8.2.1	7.2.4 - 9.2.0
Oracle	7.3.2 - 8.1.5	8.1.5
Microsoft SQL Server	7.0
Microsoft Excel	97
Microsoft Access	97
Sybase	11.5	11.9.2

Table 4. Version and release levels of supported IBM warehouse targets

Source	Version/Release
DB2 Universal Database for Windows NT	6 - 7
DB2 Universal Database Enterprise-Extended Edition	6 - 7
DB2 Universal Database for OS/2	6 - 7
DB2 Universal Database for AS/400	3.1-4.5
DB2 Universal Database for AIX	6 -7
DB2 Universal Database for Solaris Operating Environment	6 -7
DB2 Universal Database for OS/390	4.1 - 6
DB2 DataJoiner	2.1.2
DB2 DataJoiner/Oracle	8
DB2 for VM	3.4 - 5.3.4
DB2 for VSE	3.2, 7.1
CA/400	3.1.2

20.11 Accessing DB2 Version 5 information catalogs with the DB2 Version 7.1 Information Catalog Manager

The DB2 Version 7.1 Information Catalog Manager subcomponents, as configured by the DB2 Version 7.1 install process, support access to information catalogs stored in DB2 Version 6 and DB2 Version 7.1 databases. You can modify the configuration of the subcomponents to access information catalogs that are stored in DB2 Version 5 databases. The DB2 Version 7.1 Information Catalog Manager subcomponents do not support access to data from DB2 Version 2 or any other previous versions.

To set up the Information Catalog Administrator, the Information Catalog User, and the Information Catalog Initialization Utility to access information catalogs that are stored in DB2 Version 5 databases:

1. Install DB2 Connect Enterprise Edition Version 6 on a workstation other than where the DB2 Version 7.1 Information Catalog Manager is installed.

   DB2 Connect Enterprise Edition is included as part of DB2 Universal Database Enterprise Edition and DB2 Universal Database Enterprise Extended Edition. If Version 6 of either of these DB2 products is installed, you do not need to install DB2 Connect separately.

   Restriction:

   You cannot install multiple versions of DB2 on the same Windows NT or OS/2 workstation. You can install DB2 Connect on another Windows NT workstation or on an OS/2 or UNIX workstation.

2. Configure the Information Catalog Manager and DB2 Connect Version 6 for access to the DB2 Version 5 data. For more information, see the DB2 Connect User's Guide. The following steps are an overview of the steps that are required:
   1. On the DB2 Version 5 system, use the DB2 Command Line Processor to catalog the Version 5 database that the Information Catalog Manager is to access.
   2. On the DB2 Connect system, use the DB2 Command Line Processor to catalog:
      • The TCP/IP node for the DB2 Version 5 system
      • The database for the DB2 Version 5 system
      • The DCS entry for the DB2 Version 5 system
   3. On the workstation with the Information Catalog Manager, use the DB2 Command Line Processor to catalog:
      • The TCP/IP node for the DB2 Connect system
      • The database for the DB2 Connect system

   For information about cataloging databases, see the DB2 Universal Database Installation and Configuration Supplement.

3. At the warehouse with the Information Catalog Manager, bind the DB2 CLI package to each database that is to be accessed through DB2 Connect.

   The following DB2 commands give an example of binding to v5database, a hypothetical DB2 version 5 database. Use the DB2 Command Line Processor to issue the following commands. db2cli.lst and db2ajgrt are located in the \sqllib\bnd directory.

   db2 connect to v5database user userid using password
   db2 bind db2ajgrt.bnd
   db2 bind @db2cli.lst blocking all grant public
   

   where userid is the user ID for v5database and password is the password for the user ID.

   An error occurs when db2cli.list is bound to the DB2 Version 5 database. This error occurs because large objects (LOBs) are not supported in this configuration. This error will not affect the warehouse agent's access to the DB2 Version 5 database.

   Fixpak 14 for DB2 Universal Database Version 5, which is available in June, 2000, is required for accessing DB2 Version 5 data through DB2 Connect. Refer to APAR number JR14507 in that fixpak.

20.12 Additions to supported non-IBM database sources

The following table contains additions to the supported non-IBM database sources:

Database	Operating system	Database client requirements
Informix	AIX	Informix-Connect and ESQL/C version 9.1.4 or later
Informix	Solaris Operating Environment	Informix-Connect and ESQL/C version 9.1.3 or later
Informix	Windows NT	Informix-Connect for Windows Platforms 2.x or Informix-Client Software Developer's Kit for Windows Platforms 2.x
Oracle 7	AIX	Oracle7 SQL*Net and Oracle7 SQL*Net shared library (built by the genclntsh script)
Oracle 7	Solaris Operating Environment	Oracle7 SQL*Net and Oracle7 SQL*Net shared library (built by the genclntsh script)
Oracle 7	Windows NT	The appropriate DLLs for the current version of SQL*Net, plus OCIW32.DLL. For example, SQL*Net 2.3 requires ORA73.DLL, CORE35.DLL, NLSRTL32.DLL, CORE350.DLL and OCIW32.DLL.
Oracle 8	AIX	Oracle8 Net8 and the Oracle8 SQL*Net shared library (built by the genclntsh8 script)
Oracle 8	Solaris Operating Environment	Oracle8 Net8 and the Oracle8 SQL*Net shared library (built by the genclntsh8 script)
Oracle 8	Windows NT	To access remote Oracle8 database servers at a level of version 8.0.3 or later, install Oracle Net8 Client version 7.3.4.x, 8.0.4, or later. On Intel systems, install the appropriate DLLs for the Oracle Net8 Client (such as Ora804.DLL, PLS804.DLL and OCI.DLL) on your path.
Sybase	AIX	In a non-DCE environment (ibsyb15 ODBC driver): libct library In a DCE environment (ibsyb1115 ODBC driver): Sybase 11.1 client library libct_r
Sybase	Solaris Operating Environment	In a non-DCE environment (ibsyb15 ODBC driver): libct library In a DCE environment (ibsyb1115 ODBC driver): Sybase 11.1 client library libct_r
Sybase	Windows NT	Sybase Open Client-Library 10.0.4 or later and the appropriate Sybase Net-Library.

20.13 Information Catalog Manager Initialization Utility

If you get the following message::

FLG0083E: You do not have a valid license for the IBM Information Catalog Manager
Initialization utility.  Please contact your local software reseller or IBM 
marketing representative. 

You must purchase the DB2 Warehouse Manager or the IBM DB2 OLAP Server and install the Information Catalog Manager component, which includes the Information Catalog Initialization utility.

If you installed the DB2 Warehouse Manager or IBM DB2 OLAP Server and then installed another Information Catalog Manager Administrator component (using the DB2 Universal Database CD-ROM) on the same workstation, you might have overwritten the Information Catalog Initialization utility. In that case, from the \sqllib\bin directory, find the files createic.bak and flgnmwcr.bak and rename them to createic.exe and flgnmwcr.exe respectively.

If you install additional Information Catalog Manager components from DB2 Universal Database, the components must be on a separate workstation from where you installed the Data Warehouse Manager. For more information, see Chapter 3, Installing Information Catalog Manager components, in the DB2 Warehouse Manager Installation Guide.

20.14 Information Catalog Manager Administration Guide

Chapter 6. Exchanging metadata with other products: "Converting MDIS-conforming metadata into a tag language file", page 97

You cannot issue the MDISDGC command from the MS-DOS command prompt. You must issue the MDISDGC command from a DB2 command window. The first sentence of the section, "Converting a tag language file into MDIS-conforming metadata," also says you must issue the DGMDISC command from the MS-DOS command prompt. You must issue the DGMDISC command from a DB2 command window.