IBM PureApplication System: Restrictions, Known Issues, and Workarounds for IBM Database Tools

This technote is focused on the database users of IBM PureApplication System who perform administrative or development tasks on databases that are deployed in the following versions of IBM PureApplication System:

• IBM PureApplication System W1500
• IBM PureApplication System W1700

The following IBM database tools are covered in this technote:

• IBM Data Studio V3.1.1
• The database performance monitor in IBM PureApplication System

For more information about these products, see the IBM PureApplication System Information Center.

See the following restrictions, known issues, and workarounds for the IBM database tools:

• Restrictions
• Known Issues and Workarounds

For information about the known issues for IBM PureApplication System, see http://www.ibm.com/support/docview.wss?uid=swg27036448.

The following restrictions exist for the IBM database tools:

• IBM Data Studio

  • System and database users in IBM PureApplication System
  • Monitoring databases and managing jobs with IBM Data Studio web console
  • Collecting performance data within the Data Studio full client

• Database performance monitor

  • Firefox version 20.0 is unsupported

Restrictions for IBM Data Studio

System and database users in IBM PureApplication System

The users appdba and appuser, have JDBC connectivity to a provisioned database in IBM PureApplication System by default. For SSH connectivity to the virtual machine hosting the IBM PureApplication System provisioned database, users must be granted SSH connectivity by the user who provisions the database in IBM PureApplication System. If SSH connectivity is not granted to the user, an error message from the Data Studio full client displays each time database operations are run through SSH.

The appdba and appuser users can access deployed databases using the Data Studio full client. The credentials for these users are explicitly provided in the product interface of IBM PureApplication System within the endpoint link of the DB2 database. The appdba and appuser users have the following privileges:

• appdba – User appdba has the following DB2 privileges: DBADM, CREATE_NOT_FENCED, LOAD, CREATE_EXTERNAL_ROUTINE.
  

  The appdba user is a database administrator or database user who is responsible for exploiting specific IBM PureApplication System databases and their applications.

  The Data Studio full client can be used by the appdba user to, for example, create database schemas; load database tables; develop, test, and debug stored procedures; and define triggers. The appdba user can also use the Data Studio full client to grant the appuser user the necessary privileges to access and alter the tables created by an appdba user.

• appuser – User appuser has the following privileges: CREATETAB, BINDADD, CONNECT, IMPLICIT_SCHEMA.
  

  The appuser user is a database user but has fewer privileges than appdba.

  The Data Studio full client can be used by the appuser user to, for example, develop, test, and debug JDBC, SQLJ, and pureQuery database applications.

Monitoring databases and managing jobs with IBM Data Studio web console

The IBM Data Studio web console is not available within IBM PureApplication System although you can install and host the Data Studio web console outside IBM PureApplication System.

You can integrate Data Studio web console with the Data Studio full client to view alerts, applications, utilities, storage, and other related information and to create and manage jobs on your databases. See the topic, Opening Data Studio web console from within the workbench in the IBM Data Studio Information Center for more information.

Monitoring databases

Monitoring databases with IBM Data Studio web console is not supported by IBM PureApplication System and cannot be used concurrently with the integrated monitoring facilities in IBM PureApplication System. To monitor databases in IBM PureApplication System, you must use the database performance monitor in IBM PureApplication System. See the topic, Database monitoring with the database performance monitor in the IBM PureApplication System W1500 Information Center for more information.

Managing jobs with IBM Data Studio web console

Managing jobs with IBM Data Studio web console is not supported by IBM PureApplication System and there is not a similar feature that is integrated in IBM PureApplication System.

Collecting performance data within the Data Studio full client

Collecting performance data in the IBM Data Studio full client is not supported by the database performance monitor in IBM PureApplication System and you cannot collect or store performance data about your SQL scripts.

Note: Collecting performance data in the IBM Data Studio full client requires the InfoSphere Optim Performance Manager Extended Insight product which is not entitled with IBM PureApplication System.

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 with appdba user privileges
• Remote debugging of Java stored procedures for a database in the cloud
• Opening the IBM Query Tuning perspective from the database performance monitor

Migrating data with appdba user privileges

In the following section, we assume that appdba has SSH connectivity to the IBM PureApplication System virtual machine that is hosting the DB2 database. See the System and database users in IBM PureApplication System section above for information about enabling SSH connectivity.

In the Data Studio full client, the appdba user can migrate data that exists in local databases to IBM PureApplication System databases.

To migrate your data:

1. Open to the Database Administration perspective in the Data Studio full client.
2. Create a connection to the IBM PureApplication System 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 PureApplication System database.
5. On the menu bar, select Migrate > Migrate Data and then migrate the data to the IBM PureApplication System database.
   The migration services of the Data Studio full client 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.

Remote debugging of Java stored procedures for a database in the cloud

The Data Studio full client can create Data Development Projects in the Data Projects view. Data development projects facilitate the development, deployment, testing and debugging of Java, SQLJ, SQL, and PL/SQL stored procedures.

To perform remote debugging of Java stored procedures for a DB2 V9.7 database that is deployed in the cloud, you must ensure that you have the correct environment configured:

- The environment variable DB2LIBPATH is set correctly on the virtual machine where the DB2 database is deployed

To ensure the environment variable DB2LIBPATH is set correctly:

1. Use SSH to connect to the virtual machine where DB2 is deployed:
   • Log in with the DB2 Instances owner user ID and password (db2inst1).
   • Log in using the virtuser user ID and private key, and then run sudo su db2inst1 to become db2inst1.
     Note: Using this method to become db2inst1 does not require you to know the password for db2inst1.
2. On the command line, run the following commands:
   1. Run
      db2set DB2LIBPATH
      to ensure that the following path is set:
      /home/db2inst1/sqllib/java/jdk64/jre/lib/amd64
   2. If the path is not set, run:
      db2set DB2LIBPATH=/home/db2inst1/sqllib/java/jdk64/jre/lib/amd64
      
   3. Verify that the command was successful by running:
      db2set DB2LIBPATH
      
   4. Restart the instance by running the following two commands:
      db2stop force
db2start

- The Data Studio full client is using the correct Java service release level

To verify that the Data Studio full client is using the correct Java service release level, select Help > About IBM Data Studio. In the About IBM Data Studio dialog box, click Configuration Details and confirm that the Java service release level information is 1.6.0 with service level 10 or above.

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 PureApplication System.

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

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 from the Virtual Applications Instance tab 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 3:
   Note: If the Monitor link does not appear you might need to set up the database for performance monitoring. See the topic, Setup for database performance monitoring in the IBM PureApplication System Information Center for more information.

   
   Figure 3: Endpoint and Monitor links for a database in PureApplication System
   

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 4:

   
   Figure 4: 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 PureApplication System 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 PureApplication System Information Center for prerequisite information.
4. Open the IBM Query Tuning perspective in the Data Studio full client by clicking Tune or Tune All in the SQL Statements dashboard as circled in Figure 5.
   
   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.
   

   
   Figure 5: SQL Statements dashboard of the database performance monitor

   Important: Tuning query workloads requires a connection to the repository database of the database performance monitor so that the Data Studio full client can retrieve the set of SQL statements, see the following section for more information.

   Result: You can now run the query tuning advisors in the IBM Query Tuning perspective in IBM Data Studio to analyze the SQL statements.

Prerequisites for identifying and analyzing problematic query workloads

The database performance monitor automatically creates a connection profile in the Data Studio full client for the repository database. The repository database of the database performance monitor is a DB2 V9.7 database that is named PERFDB and resides on the same virtual machine as the database performance monitor.

Before you can identify and analyze problematic query workloads you must create a user ID and password in order to connect to the repository database with the Data Studio full client.

The credentials to the repository database are not available from the user interface of IBM PureApplication System and require you to access the virtual machine that is hosting the database performance monitor in order to create the user ID and password for connecting to the repository database.

To create credentials to connect to the repository database of the database performance monitor, the user who deployed database performance monitor must perform the following steps:

1. Provide an RSA public key for SSH access to the virtual machine that is hosting the database performance monitor.
2. Install the RSA public key on the virtual machine:
   1. Click Manage on the Instances Shared-service view of the database performance monitor.
   2. Click Operation > SSH category to open the SSH category view.
   3. Click Add or update virtual machine SSH public key to add the RSA public key for SSH access to the virtual machine.
   4. Log in to the virtual machine and create the credentials for the repository database of the database performance monitor:
      Important: The user that logs in to the virtual machine must meet the following requirements:
      • The user ID must match RSA private key
      • The user must log in by using SSH
      • The virtuser user ID must be used to log into the virtual machine that hosts the repository database of the database performance monitor
      1. Run the following command on the virtual machine host to become root:
         sudo su –
      2. Create a new user, for example, to create the perfuser user ID run:
         adduser -g db2iadm1 -G dasadm1 perfuser
      3. Run the following command to set the password of the new user ID:
         passwd perfuser
3. Enter the new user ID and password in the PERFDB connection profile of the Data Studio full client.

Result: You can now connect to the repository database of the database performance monitor with the Data Studio full client.