PureData System for Operational Analytics: Restrictions, Known Issues, and Workarounds for IBM database tools

The following restrictions exist for the IBM database tools:

• IBM Data Studio

  • Creating databases and running instance level operations
  • Monitoring databases and managing jobs
  • Collecting performance data within IBM Data Studio V3.1.1

• Database performance monitor

  • Firefox version 20.0 is unsupported

Restrictions for IBM Data Studio

Creating databases and running instance level operations

IBM PureData System for Operational Analytics hosts a single database on a single instance. You cannot use IBM Data Studio to create additional databases or database instances in PureData System for Operational Analytics.

Depending on user ID that you use to connect to the databases, the user ID might have the authority and privileges to run certain database instance level operations. You must verify with your system administrator which instance level operations you can run on your system.

Monitoring databases and managing jobs

The IBM Data Studio web console is not support for use with IBM PureData System for Operational Analytics, instead you can use the database performance monitor in IBM PureData System for Operational Analytics to monitor the database and manage jobs.

For more information about performance monitoring, see Database monitoring with the database performance monitor in the IBM PureData System for Operational Analytics Information Center.

For more information about managing jobs, see Creating and managing jobs in the IBM PureData System for Operational Analytics Information Center.

You can integrate the database monitoring and job management features of the database performance monitor into the Data Studio client. From in the Data Studio client, you can view alerts, applications, utilities, storage, and other related information and to create and manage jobs on your database. To integrate the database performance monitor, you specify the following information in the Data Studio Web Console Preferences dialog box of the Data Studio client:

• The URL to the database performance monitor
• The user ID and password that you use to access the database performance monitor

For more information about specifying the connection information in the Data Studio client, see Opening Data Studio web console from within the workbench in the IBM Data Studio Information Center for more information.

Connecting to the OPMDB repository database

Connecting to the OPMDB repository database to perform the following tasks is not supported for Data Studio V3.1.1:

• Retrieve and display the performance data in the SQL outline view of the Data Studio client.
• Run SQL statements and collect the performance data in the Performance metrics view of the Data Studio client.
• Import SQL statements and tune Query Tuner Workflow Assistant of the Data Studio client.

Note: Connecting to the OPMDB repository database to perform these tasks will be enabled in the next release of Data Studio. You can check for fixes in Fix Central to download the fix to enable these features in IBM Data Studio V3.1.1 when they become available. Open a search query in Fix Central for all the available IBM Data Studio V3.1.1 fixes.

To connect to the OPMDB repository database with the Data Studio client you must create an Optim Performance Manager Profile in the Preferences dialog box: Windows -> Preferences -> Data Management -> Optim Performance Manager Profiles. You specify the connection information for the OPMDB repository database in the Optim Performance Manager Profile. You must contact your system administrator for the connection information to the OPMDB repository database.

Restrictions for Database Performance Monitor

Firefox version 20.0 is unsupported

The GUI fails to load the login page or provide any corrective instructions when viewed through the latest version of Firefox (20.0) released on April 2, 2013.

The following known issues and workarounds exist for IBM Data Studio V3.1.1:

• Migrating data
• Migrating tables that contain XML columns
• Opening the IBM Query Tuning perspective from the database performance monitor
• Database performance monitor shows an error when you attempt to send an alert via email

Migrating data

You can use the Data Studio client to migrate data that exists in local databases to IBM PureData System for Operational Analytics databases.

In the following section, we assume that user ID you are using has SSH connectivity to the IBM PureData System for Operational Analytics database host. See the topic, Creating a connection in the IBM Data Studio client in the IBM PureData System for Operational Analytics Information Center for more information about user privileges and how to connect to the database.

To migrate your data:

1. In IBM Data Studio V3.1.1 open to the Database Administration perspective.
2. Create a connection to the IBM PureData System for Operational Analytics database. In the Administration Explorer, select the host -> db2inst1 -> db, where the host is the IP address of the database host and db is the name of the database.
3. Right-click Change plans and create a new change plan named, for example, migration.
4. On the menu bar, select Migrate > Compare and Migrate Objects and then migrate the source schema to the IBM PureData System for Operational Analytics database.
5. On the menu bar, select Migrate > Migrate Data and then migrate the data to the IBM PureData System for Operational Analytics database.
   The IBM Data Studio V3.1.1 migration services expect that a shared file system exists between the source host and the target host in order to move data files for the purpose of migration. If a shared file system does not exist between the two systems, create a directory path which is identical on both systems, for example, /tmp, and then set the export path in the generated data migration script.
   To set the export path in the generated data migration script, click “…” in the Review and Deploy dialog box and then specify the directory. See the example in Figure 1.
   Figure 1: Setting the export path in the Review and Deploy dialog box
   
6. Click Edit to edit the generated script:
   1. Specify the local connection.
   2. Highlight all of the export statements.
   3. Right-click the selection and select Run SQL to run the export statements on the local system.
      Result:
      The exported files are created in the source host /tmp directory. See the example in Figure 2.
   Figure 2: Editing the script generated from the data migration wizard
   
7. Switch to the Remote System Explorer perspective and then connect to both the source and target hosts. Copy the files from the source /tmp directory to the target /tmp directory using the copy/paste function in the Remote System Explorer perspective.
8. Return to the generated script in the script editor and then set the connection to the remote system.
9. Highlight the remaining portion of the generated script.
10. Right-click the selection and select Run SQL to load the data onto the target database.

Migrating tables that contain XML columns

Migrating tables that contain XML columns to the BCUDB database is not supported by the Migrate feature in IBM Data Studio.

Workaround

You must use the Copy and Paste commands in the Data Source Explorer to copy the table that contains the XML columns from one database to the BCUDB database. For information about how to copy database objects from one database to the BCUDB database with the Copy and Paste commands, see Copying database objects between and within databases in the IBM Data Studio Information Center.

Example of the issue:

The DB2 sample database includes the following table which contains the XML column DESCRIPTION:

DB2ADMIN.PRODUCT (PID VARCHAR(10) NOT NULL, NAME VARCHAR(128), PRICE DECIMAL(30,2), PROMOPRICE DECIMAL(30,2), PROMOSTART DATE, PROMOEND DATE, DESCRIPTION XML )

If you use the Migrate feature in Data Studio, the following statement is generated to migrate the table from a local instance of the Sample database to the BCUDB database:

CALL SYSPROC.ADMIN_CMD( 'LOAD FROM /tmp/default_DB2ADMIN_PRODUCT_SAMPLE.dat OF DEL XML FROM /tmp/ METHOD P (1,2,3,4,5,6,7) REPLACE INTO DB2ADMIN.PRODUCT (PID, NAME, PRICE, PROMOPRICE, PROMOSTART, PROMOEND, DESCRIPTION)' );

If you run the statement against the BCUDB database, it results in the following error:

Shared sort memory cannot be allocated for this utility. SQLCODE=-1406, SQLSTATE= , DRIVER=4.13.111

For information about this issue, see the following topic in the DB2 Information Center: http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.config.doc/doc/r0006014.html.

Opening the IBM Query Tuning perspective from the database performance monitor

To identify and analyze problematic SQL statements, you can open the IBM Query Tuning perspective in IBM Data Studio from the database performance monitor in IBM PureData System for Operational Analytics.

See the topic, Scenarios for database monitoring in the IBM PureData System for Operational Analytics Information Center for scenario information about using the IBM Query Tuning perspective in IBM Data Studio with the database performance monitor.

You must create a connection to the OPMDB repository database, see Collecting performance data within IBM Data Studio V3.1.1 for more information.

To open the IBM Query Tuning perspective from the database performance monitor:

1. Open the SQL Statements dashboard in IBM PureData System for Operational Analytics to view a list of recent SQL statements, including any long running SQL statements:
   1. On the database performance monitor page, click Performance > SQL Statements, see the example in Figure 3:
   Figure 3: SQL Statements dashboard of the database performance monitor
   
2. Ensure that the Data Studio full client is up and running. See the topic, Starting the IBM Data Studio full client in the IBM PureData System for Operational Analytics Information Center for more information.
   
   Important: The IBM Data Studio full client must be installed on the same computer as the browser that you are using to access the database performance monitor. See the topic, Finding problematic SQL by using the SQL Statements dashboard in the IBM PureData System for Operational Analytics Information Center for prerequisite information.
3. Open the IBM Query Tuning perspective in the Data Studio full client by clicking Tune in the Actions menu of the SQL Statements dashboard, see Figure 4. Note: The Tune All feature in the SQL Statements dashboard is not supported by IBM PureData System for Operational Analytics.
   
   The IBM Query Tuning perspective opens in the Data Studio full client, displaying the same SQL statement that is displayed in the SQL Statements dashboard in the database performance monitor.
   Figure 4: SQL Statements dashboard of the database performance monitor
   
   Result: You can now run the query tuning advisors in the IBM Query Tuning perspective in IBM Data Studio to analyze the SQL statements.
   
   Important: The Tune All feature in the SQL Statements dashboard is not supported by IBM PureData System for Operational Analytics. It is a feature that requires an active license for IBM InfoSphere Optim Query Workload Tuner to be installed on the database but currently the IBM InfoSphere Optim Query Workload Tuner product is not entitled for use with IBM PureData System for Operational Analytics and not supported.

Database performance monitor shows an error when you attempt to send an alert via email

On the Alerts page of the database performance monitor, there is an option to send an alert from the Alert List tab via email.

When you click the Send button, the following error is displayed:
EML37403E: The notification was not sent. Review the reason, take corrective action if possible, and try again. Reason: EMail not configured.

Workaround

To configure the SMTP mail server for the database performance monitor, complete the following steps:

1. As an administrative user, go to the setup URL for the database performance monitor:
   http://host:port/dbperfmon/setup/product/index.jsp
2. Click Product Setup > Services.
3. Select Email service and click Configure.
4. In the window that opens, specify the settings for the outbound SMTP mail server.