IBM Support

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

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.

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

Table of contents:

IBM Database Tools Overview

The following IBM database tools are covered in this technote:

  • IBM Data Studio V4.1.1
  • IBM InfoSphere Optim Query Workload Tuner V4.1.1
  • IBM InfoSphere Optim pureQuery Runtime V3.3
  • IBM InfoSphere Data Architect V9.1.2

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 the IBM database tools to perform certain administration and development tasks on the DB2 pureScale instances and individual databases. For more information about the user interface of IBM PureData System for Transactions, 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.

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


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.

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:

  1. 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.
  2. In the Workload Console, click Manage:
  3. In the Operation menu, click User Groups:
  4. 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.


Restrictions, Known Issues, and Workarounds

See the following restrictions, known issues, and workarounds for IBM database tools when connected to databases in IBM PureData System for Transactions:


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



Restrictions

The following restrictions exist for the IBM database tools:

IBM Data Studio



Supported versions of the IBM database tools

Only the following versions of the IBM database tools are supported with IBM PureData System for Transactions T1500 Fix Pack 7:

  • IBM Data Studio V4.1.1
  • IBM InfoSphere Optim Query Workload Tuner V4.1.1
  • IBM InfoSphere Optim pureQuery Runtime V3.3
  • IBM InfoSphere Data Architect V9.1,2



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

With the exception of restoring 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. For information about restoring your databases, see the Restoring databases topic in the information center.



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.


Known Issues and Workarounds

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

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 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 the database and select Migrate > Compare and Migrate Objects and then migrate the source schema to the IBM PureData System for Transactions database.
  4. Right-click the database and select Migrate > Migrate Data and then migrate the data to the IBM PureData System for Transactions database.
    Important: The IBM Data Studio 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

  5. 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

  6. 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.
  7. Return to the generated script in the script editor and then set the connection to the remote system.
  8. Highlight the remaining portion of the generated script.
  9. Right-click the selection and select Run SQL to load the data onto the target database.


Restoring databases from a backup using IBM Data Studio

For information about using IBM Data Studio to restore your databases, see the Restoring databases topic in the information center.



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.



Opening the IBM Query Tuning perspective from the database performance monitor

In some cases, clicking Tune or Tune All from the SQL or Extended Insight dashboards in the database performance monitor does not open the IBM Query Tuning perspective in the Data Studio client to tune the SQL statement or statements.

Workaround:

Refresh the browser that you use with the database performance monitor before launching the Data Studio client.

For more information about the tuning with the Data Studio client, see the topic, Overview of InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows in the IBM PureData System for Transactions Information Center.


[{"Product":{"code":"SS62YD","label":"IBM Data Studio"},"Business Unit":{"code":"BU001","label":"Analytics Private Cloud"},"Component":"--","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"4.1.1.0","Edition":""}]

Document Information

Modified date:
17 June 2018

UID

swg27042486