Product documentation

Abstract

Use the IBM database tools to perform administration and development tasks on databases in PureData System for Transactions.

Content

This technote covers the restrictions, known issues, and workarounds for the IBM database tools and is focused towards users who perform administrative or development tasks on databases in IBM PureData System for Transactions.

Important: You must install the required interim fix for IBM Data Studio to connect to databases in IBM PureData System for Transactions. For information on how to download and install the required interim fix, see the technote at http://www.ibm.com/support/docview.wss?uid=swg27036336

The database performance monitor in IBM PureData System for Transactions

You use the web-based interface of IBM PureData System for Transactions to create and deploy DB2 pureScale databases in the cloud. You can then use IBM Data Studio to perform certain administration and development tasks on the DB2 pureScale instances and individual databases. For more information, see Using the workload console.

Each DB2 pureScale database is provisioned with a license for IBM Optim Query Workload Tuner, so you can use Data Studio to tune your SQL queries.

If you enable extended insight monitoring with the database performance monitor in IBM PureData System for Transactions, you can then access the performance metrics from the IBM Data Studio client.

Connecting to Databases

You create a connection to a database in IBM PureData System for Transactions by specifying the database connection information in the client. For details about obtaining the connection information and creating a connection to a database, see Connecting to databases in the information center.

The database performance monitor maintains a separate repository database named PERFDB, which you can also connect to with Data Studio for performance metrics and query workload tuning.

To connect to the PERFDB database with Data Studio, you use a JDBC URL which is similar to the URL you use to access the workload console in IBM PureData System for Transactions, for example jdbc:db2://example.server.com:50000/PERFDB. For information about how to specify the connection information in the client, see Creating a connection in the client in the information center.

To view the JDBC URL for the repository database of the database performance monitor, you must navigate to the system console of IBM PureData System for Transactions by clicking System Console > Hardware > Management Nodes and then clicking the management node.

You must use the tooluser user ID and password to connect to and access the PERFDB database. For more information about the tooluser user ID, see Required system-level user accounts in the information center.

Remember: When required, IBM Data Studio uses the JDBC URL to establish SSH connectivity. Not having an SSH connection will disable a number of IBM Data Studio features, for example: CLP execution of scripts, Remote System Explorer services for data movement, and execution of database operations that are available only through SSH.

User Groups and Privileges for Database Users

Use this section to create user groups and grant privileges to other user IDs that use Data Studio to connect to databases in PureData System for Transactions. You must grant the necessary privileges to the other user IDs before those database users can perform database administration and development tasks on the database.

By default, when you create a database in PureData System for Transactions, only the default user that is created with the database has the privileges to perform database tasks in Data Studio.

Before other database users can access the database with Data Studio, the default database user must create a user group on the database host and then grant privileges to that user group on the database. For information about user groups, see Managing user groups in the information center.

In the following sections, we will refer to the default user ID that is created with a database as user ID instuser.

1. Create a user group with non-instance-level privileges:

Log in to the Workload Console with a user ID that has the privileges to perform administrative tasks, for example, log in with the admin user ID. For information about the user privileges in PureData System for Transactions, see User Roles in the information center.

In the Workload Console, click Manage:

In the Operation menu, click User Groups:

Create a new user group by clicking and specifying the required fields:

In this example, the admin user created the DBAGRP user group and granted it non-instance-level privileges.

2. Grant privileges to the user group on the database:

To grant privileges to a user group, you must use the default database user ID instuser in Data Studio to run SQL statements against the database.

For example, the following statements grant the required privileges for a group named DBAGRP by using the DBADM, CREATE_NOT_FENCED, LOAD, and CREATE_EXTERNAL_ROUTINE commands:

connect to mydb user instuser using instuser_password grant CONNECT on database to DBAGRP grant DBADM on database to DBAGRP grant CREATE_NOT_FENCED on database to DBAGRP grant CREATE_EXTERNAL_ROUTINE on database to DBAGRP

Result: All users that are members of the DBAGRP group will inherit the privileges for performing database administration and application development tasks in Data Studio.

Information Centers

For more information about these products, see the following topics the IBM PureData System for Transactions Information Center:

Restrictions for IBM Data Studio

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:

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

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.

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://
:
/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.

Known Issues and Workarounds 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:

In IBM Data Studio V3.1.1 open to the Database Administration perspective.

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.

Right-click Change plans and create a new change plan named, for example, migration.

On the menu bar, select Migrate > Compare and Migrate Objects and then migrate the source schema to the IBM PureData System for Transactions database.

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

Click Edit to edit the generated script:

Specify the local connection.

Highlight all of the export statements.

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

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.

Return to the generated script in the script editor and then set the connection to the remote system.

Highlight the remaining portion of the generated script.

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:

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

To resolve this issue, you must revise the script by moving the alter statement before the create table statement.

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:

Choose the database that you want to backup. For example, database mydb, and then click Manage.

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:

Ensure that the database has been created with TSM and was configured for backup and restore.

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.

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.

In the Administration Explorer, right-click the database that you want to restore and then click Back Up and Restore / Restore.

Specify any options and then select the TSM backup image that you want to use to restore the database.

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

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

Open the SQL Statements dashboard to view a list of recent SQL statements, including any long running SQL statements:

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.

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.

Important: To use the Tune All feature, you must restart the database performance monitor server by clicking Restart Database Tools in the System Console:

Figure 7: Restart Database Tools

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.

Known Issues and Workarounds 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

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.

Select the Configuration Changes tab.

Select the managed connection from the managed connections list.

Navigate to the database configuration manager in the workload console

Open the Configuration Changes tab by clicking Show > Configuration Changes.

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:

As an administrative user, go to the setup URL for the database performance monitor: http://host:port/dbperfmon/setup/product/index.jsp

Click Product Setup > Services.

Select Email service and click Configure.

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:

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.

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.

Open Product Setup > Configuration Repository.

Click Select Repository Database. Important: Do not modify any fields.

Click OK.

Close all browser tabs that you used to access the PureData System for Transactions console.

Open a new tab in your browser and then access the Health Summary page of the database performance monitor as you normally would.