PureData System for Transactions T1500: Restrictions and Limitations for IBM database tools

The following restrictions exist for IBM database tools:

• IBM Data Studio

  • Do not use Data Studio to perform instance level operations on databases
  • Monitoring databases and managing jobs
  • You cannot collect performance data within IBM Data Studio V3.1.1

• Database performance monitor

  • Firefox version 20.0 is unsupported
  • You cannot update passwords for user IDs that are used to create monitored databases
  • You cannot import metadata from IBM Optim pureQuery Runtime applications
  • You cannot export and import connection profiles from the database performance monitor to IBM Data Studio
  • Do not change the settings on the database performance monitor Monitoring Server panel

Do not use Data Studio to perform instance level operations on databases

You must not use IBM Data Studio to perform instance level operations on databases in IBM PureData System for Transactions. Use the system and workload consoles of IBM PureData System for Transactions to perform those tasks.

Monitoring databases and managing jobs

The integrated database monitoring and job management features in the Data Studio client are not supported.

To monitor databases and manage jobs in IBM PureData System for Transactions, you can use the following features:

Monitoring databases:

You can use the in the database performance monitor in IBM PureData System for Transactions. For more information, see Database monitoring with the database performance monitor in the information center.

Managing jobs:

You can use either the database performance monitor or the database configuration manager in IBM PureData System for Transactions. For more information, see Creating and managing jobs in the information center.

Note: If issues occur while using the Data Studio web console to monitor databases and manage jobs, you can use the database monitoring and job management features in IBM PureData System for Transactions.

You cannot collect performance data within IBM Data Studio V3.1.1

Collecting performance data in the IBM Data Studio full client is currently not supported by the database performance monitor in IBM PureData System for Transactions. Currently, you cannot use the IBM Data Studio client to collect performance data from the database performance monitor in the following views:

• SQL Outline view
• Performance metrics tab of the SQL and XQuery editor view

Restrictions for Database Performance Monitor

The following restrictions exist for the 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.

You cannot update passwords for user IDs that are used to create monitored databases

A database is created in IBM PureData System for Transactions with a default user ID and password, which gets passed to the database performance monitor so that it has permission to monitor that database.

Currently, you cannot change the user ID or password for a database that you want to monitor with the database performance monitor. Changes to the user ID or password do not get propagated to the database performance monitor and you will no longer be able to monitor the health and performance of that database.

You cannot import metadata from IBM Optim pureQuery Runtime applications

Importing metadata from pureQuery applications into the database performance monitor is unsupported. Currently, you cannot import the detailed information about SQL statements from the application to display Java package names, method names, and line numbers in the Extended Insight Dashboard.

You cannot export and import connection profiles from the database performance monitor to IBM Data Studio

Currently, exporting database connection information from the database performance monitor and then importing it into IBM Data Studio is unsupported.

Do not change the settings on the database performance monitor Monitoring Server panel

When you access the database performance monitor Product Setup menu as an administrative user (for example: http://host:port/dbperfmon/setup/product/index.jsp), the Monitoring Server panel allows you to change the pre-configured Extended Insight ports and external IP address that are used by network address translators (NAT). However, changing these settings after they have been configured by IBM will cause the Extended Insight functionality of the database performance monitor to become unusable.

The following known issues and workarounds exist for IBM database tools:

• IBM Data Studio

  • Migrating data
  • Migrating tables
  • Restoring databases using IBM Data Studio
  • Opening the IBM Query Tuning perspective from the database performance monitor

• Database performance monitor

  • Cluster Caching Facility (CF) performance metrics are not displayed on the Overview dashboard
  • The database configuration manager does not open from the SQL Statements dashboard
  • Database performance monitor shows an error when you attempt to send an alert via email
  • Transparent LDAP users have no password-based secure shell (SSH) access to the operating system
  • Error on the Health Summary page of the database performance monitor

Known Issues and Workarounds for IBM Data Studio

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

Migrating data

To migrate data that exists in local databases to databases in IBM PureData System for Transactions, you must follow the steps in the following section.

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 Transactions database. In the Administration Explorer, select the virtual machine host -> db2inst1 -> db, where the virtual machine host is the IP address of the virtual machine that hosts the DB2 database, and db is the name of the database on the virtual machine host.
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 Transactions database.
5. On the menu bar, select Migrate > Migrate Data and then migrate the data to the IBM PureData System for Transactions database.
   Important: 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 2.
   

   
   Figure 2: 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 3.

   
   Figure 3: 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

The following known issues and workarounds exist when migrating tables with IBM Data Studio:

1. Statements that include clauses that are unsupported by DB2 pureScale databases result in an error:
   Example of a statement that includes an unsupported clause:
   CREATE TABLE DB2ADMIN.EMPMDC ( EMPNO INTEGER, DEPT INTEGER, DIV INTEGER ) ORGANIZE BY DIMENSIONS ( ( DEPT ), ( DIV ))
   Error due to the unsupported clause:
   The statement, clause, command, API, or function is not supported in a DB2 pureScale environment. Reason code: 6".. SQLCODE=-1419, SQLSTATE=56038, DRIVER=4.13.111
   

2. Statements for creating MQT tables in generated scripts are out of order:
   For example, the following statement fails during schema migration:
   CREATE TABLE DB2ADMIN.MQT1 ( EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM ) AS ( select * from db2admin.employee where lastname > 'A' ) DATA INITIALLY DEFERRED REFRESH IMMEDIATE MAINTAINED BY SYSTEM COMPRESS YES ADAPTIVE VALUE COMPRESSION
   In the example, the statement for db2admin.employee appears after the create table statement, but before an alter statement which defines the primary key EMPNO for db2admin.employee.
   
   Workaround:
   
   To resolve this issue, you must revise the script by moving the alter statement before the create table statement.

3. IBM Data Studio generates exports statements for tables that use the IXF format and incorrectly generates matching load statements that use the DEL format when migrating data from a DB2 database to a DB2 PureScale database.
   EXPORT/LOAD with IXF/DEL formats as shown in the screen shot below. For clarity, the screen shot only shows the statements generated for a single table.

   
   Figure 4: Review and Deploy dialog box
   

   
   
   Workaround:
   
   You must edit the file by clicking Edit in the Review and Deploy dialog box and then replace DEL with IXF, so that both export and load use the same file format.

Restoring databases from a backup using IBM Data Studio

Use the following steps to restore databases with a backup image by using IBM Data Studio.

Important: You must work with your system administrator and ensure you have the access privileges before using the following steps to restore your database as these steps require you to run instance level operations.

To run a backup from within the web console:

1. Choose the database that you want to backup. For example, database mydb, and then click Manage.
2. Create a backup image or schedule to run the database backup at another time.

To restore a database by using a backup image, perform the following steps:

1. Ensure that the database has been created with TSM and was configured for backup and restore.
2. Disable automatic collection for the database by using the database performance monitor.

   Important: This step is to ensure the database performance monitor does not attempt to connect to the database that you want to restore. You must also stop and ensure that other applications do not attempt to connect or reconnect to that database during the restore process.

3. Connect to the database by using the Administration Explorer in Data Studio. You must connect to the database as either the default database user or as an admin user that has sysadm privileges.

   Tip: You can create an admin user that has sysadm privileges by using the web console. For example, create admin user adminusr and password passw0rd for database mydb.

4. In the Administration Explorer, right-click the database that you want to restore and then click Back Up and Restore / Restore.
5. Specify any options and then select the TSM backup image that you want to use to restore the database.
6. Click Preview command to view the generated scripts. For example:
   CONNECT TO "mydb";
   QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
   UNQUIESCE DATABASE;
   TERMINATE;
   DEACTIVATE DATABASE "mydb";
   RESTORE DATABASE "mydb" USE TSM OPEN 0 SESSIONS TAKEN AT 20130604210314 WITHOUT PROMPTING;
   ROLLFORWARD DATABASE "mydb" TO END OF BACKUP AND COMPLETE;
7. Click Edit to open the SQL and XQuery editor.
8. Edit the script, to replace:
   OPEN 0 SESSIONS
   With:
   OPEN 1 SESSIONS.
9. Click Run SQL to run the script.
10. When the restore process has completed, re-enable automatic collection for the database by using the database performance monitor in the web console.

Opening the IBM Query Tuning perspective from the database performance monitor

The Tune All feature is currently not supported but will be resolved in a future release.

Workaround:

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

For scenario information about using the IBM Query Tuning perspective in IBM Data Studio with the database performance monitor, see the topic, Overview of database monitoring in the database performance monitor in the information center.

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

1. From the workload console, select the database that you want to monitor by clicking Database > DB2 pureScale Instances to display the database information, including the Endpoint and Monitor links. The Monitor link shows if the database performance monitor is up and running, see the example in Figure 5:
   

   
   Figure 5: Monitor link in the workload console
   

2. Open the SQL Statements dashboard to view a list of recent SQL statements, including any long running SQL statements:
   1. Click the Monitor link to open the database performance monitor page.
   2. On the database performance monitor page, click Performance > SQL Statements, see the example in Figure 6:

      
      Figure 6: SQL Statements dashboard of the database performance monitor
      

3. 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 Transactions 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.
4. Open the IBM Query Tuning perspective in the Data Studio full client by clicking Tune in the SQL Statements dashboard as circled in Figure 6.
   
   Remember: The Tune All feature is currently not supported but will be resolved in a future release.
   
   The IBM Query Tuning perspective opens in the Data Studio full client, displaying the same SQL statement or set of SQL statements (also known as query workloads) that are displayed in the SQL Statements dashboard in 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.

Back to top

Known Issues and Workarounds for the Database Performance Monitor

The following known issues and workarounds exist for the database performance monitor:

Cluster Caching Facility (CF) performance metrics are not displayed on the Overview dashboard

Some CF metrics are not displayed on the Overview dashboard of the database performance monitor. You might see a blank value or a "--" instead of the metric value.

Workaround:

You can view any available CF metrics on the System dashboard of the database performance monitor.

The database configuration manager does not open from the SQL Statements dashboard

Clicking View Configuration Changes in the SQL Statements dashboard of the database performance monitor does not open the database configuration manager.

Workaround:

Open the database configuration manager with one of the following methods:

Open the database configuration manager by using a URL

1. Open the following URL in your browser:
   http://server:55000/dbconfigmgr/ipasindex.jsp#tasks=ocm.configChanges.task.id&activeTask=ocm.configChanges.task.id
   Where server is the IP address for the workload console.
2. Select the Configuration Changes tab.
3. Select the managed connection from the managed connections list.

Navigate to the database configuration manager in the workload console

1. Open the database configuration manager by clicking Database > Database Configuration Manager.
2. Open the Configuration Changes tab by clicking Show > Configuration Changes.
3. Select the managed connection from the managed connections list.

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.

Transparent LDAP users have no password-based secure shell (SSH) access to the operating system

SSH access is not enabled for users created when LDAP authentication is in use. This compromises some Data Studio functionality.

Workaround:

Have the instance deployer or workload administrator create an instance-level (OS) user with SSH access. Use these credentials when prompted by Data Studio to enter an SSH userid and password.

Error on the Health Summary page of the database performance monitor

On rare occasions after the PureSystems Manager, database performance monitor, or database configuration manager is restarted, an error can appear on the Health Summary page of the database performance monitor, for example:

CDIH50011I: The Health Summary did not find any contributing products. Contact the administrator for the installed IBM Optim product."

Workaround:

1. From a browser, log in to the PureData System for Transactions console with a user that has View all workload resources (Read-only) permissions or greater.
2. Open a new tab in the browser and then enter the following URL into the address bar:
   http://host:55000/dbperfmon/setup/product/index.jsp
   where host is the host name or IP address that you use to access the PureData System for Transactions console.

   Important: Using the above URL to access PureData System for Transactions console allows you to modify the settings of the database performance monitor and database configuration manager.
   You must only use this URL as a temporary workaround because modifying any of the database performance monitor or database configuration manager settings can have a negative impact and cause issues.

3. Open Product Setup > Configuration Repository.
4. Click Select Repository Database.
   Important: Do not modify any fields.
5. Click OK.
6. Close all browser tabs that you used to access the PureData System for Transactions console.
7. Open a new tab in your browser and then access the Health Summary page of the database performance monitor as you normally would.

Back to top