IBM Support

IBM Workload Deployer: Restrictions and Limitations for IBM Data Studio V3.1.1

Product Documentation


Abstract

Use IBM Data Studio V3.1.1 to administer and develop databases provisioned by IBM Workload Deployer.

Content

IBM Workload Deployer provisions DB2 V9.7 or V10.1 databases on Linux virtual machines. All IBM Workload Deployer databases are created with archive logging and use IBM Tivoli Storage Manager to manage backup and recovery. DB2 databases can be deployed either in standard mode or Oracle compatibility mode.

See the following known restrictions and workarounds for IBM Data Studio V3.1.1:


For more information about these products, see the following information centers:


Restrictions

The following restrictions exist for IBM Data Studio V3.1.1:



IBM Workload Deployer Users and IBM Workload Deployer Database Users

This technote is focused on IBM Workload Deployer database users who perform administrative or development tasks on databases that have been deployed in the cloud.

Users have JDBC connectivity to an IBM Workload Deployer provisioned database by default. For SSH connectivity to the virtual machine hosting the IBM Workload Deployer provisioned database, users must be granted SSH connectivity by the IBM Workload Deployer user who provisions the database. If SSH connectivity is not granted to the user, an error message from IBM Data Studio V3.1.1 displays each time database operations are run through SSH.

The appdba and appuser users can access deployed databases using IBM Data Studio V3.1.1. The credentials for these users are explicitly provided in the IBM Workload Deployer product interface from 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 Workload Deployer databases and their applications.

    IBM Data Studio V3.1.1 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 IBM Data Studio V3.1.1 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.

    IBM Data Studio V3.1.1 can be used by the appuser user to, for example, develop, test, and debug JDBC, SQLJ, and pureQuery database applications.

For information about the privileges required for certain tasks, see the DB2 information center:


Collecting performance data within IBM Data Studio V3.1.1

You can use IBM Data Studio V3.1.1 to connect to an instance of IBM InfoSphere® Optim™ Performance Manager Extended Insight. InfoSphere Optim Performance Manager Extended Insight can collect performance data about your SQL scripts and store the data in the repository database. See the following information center help topics for more information:

Note: Collecting performance data in IBM Data Studio V3.1.1 requires the InfoSphere Optim Performance Manager Extended Insight product which is not entitled with IBM Workload Deployer.

Workarounds

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



Migrating data with appdba user privileges

In the following section, we assume that appdba has SSH connectivity to the IBM Workload Deployer virtual machine that is hosting the DB2 database, see the IBM Workload Deployer Users and IBM Workload Deployer Database Users 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 Workload Deployer cloud databases.

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 Workload Deployer 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 Workload Deployer database.
  5. On the menu bar, select Migrate > Migrate Data and then migrate the data to the IBM Workload Deployer 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.

  7. Figure 2: Editing the script generated from the data migration wizard

  8. 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.
  9. Return to the generated script in the script editor and then set the connection to the remote system.
  10. Highlight the remaining portion of the generated script.
  11. 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

IBM Data Studio V3.1.1 has the ability to 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.

For IBM Data Studio V3.1.1, 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
  • IBM Data Studio V3.1.1 is using the correct Java service release level.

    To verify that IBM Data Studio V3.1.1 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.


Monitoring databases and managing jobs with IBM Data Studio web console

You can monitor databases that you deploy from IBM Workload Deployer and manage jobs with the Data Studio web console. For more information about monitoring database and managing jobs, see Configuring Data Studio web console and Creating and managing jobs in the IBM Data Studio Information Center.

You can also monitor databases and manage jobs from within an integrated view in the Data Studio full client. From the Data Studio full client, you can view alerts, applications, utilities, storage, and other related information and to create and manage jobs on your databases. See the Opening Data Studio web console from within the workbench in the IBM Data Studio Information Center for more details. The following sections include information about connecting to an instance of Data Studio web console that you deploy from IBM Workload Deployer:


Restrictions for the db2inst1 user ID

The available user IDs of IBM Workload Deployer such as appdba and appuser do not have sufficient privileges for monitoring database and therefore the Data Studio web console must connect using the db2inst1 user ID and password. The db2inst1 user ID and password exists on the virtual machine that hosts the DB2 database in the cloud and includes the instance and database level privileges that are required for monitoring databases.

Important: The db2inst1 user ID includes instance and database level privileges that allow you to perform some database tasks which are not recommended and cause issues. For example, the db2inst1 user ID includes privileges that allow you to run scripts to create additional databases on the virtual machine but you must not perform that task outside the user interface of the IBM Workload Deployer.

If the task you want to perform exceeds the privileges of the appdba and appuser user ID, you must perform the tasks from the user interface of the IBM Workload. For example, you must create and deploy databases from the user interface. For more information about administering databases in the IBM Workload Deployer, see Working with database in the IBM Workload Deployer Information Center.

There are no restrictions for using scripts to perform database administration tasks which do not exceed the privileges that are included within the appdba and appuser user IDs. For example, you can use scripts to create tables with the CREATE TABLE command because that command only requires the DBADM authority. For information about the authority included in the within the appdba and appuser user IDs, see the IBM Workload Deployer Users and IBM Workload Deployer Database Users section.


Deploying and connecting to Data Studio web console

To integrate Data Studio web console with the Data Studio full client:

  1. Deploy an instance of Data Studio web console as a virtual application instance in IBM Workload Deployer.
  2. After you deploy Data Studio web console you must retrieve the URL to the deployed instance by clicking Endpoint. see the circled link in Figure 3. The Endpoint dialog box opens.
  3. Copying the URL that appears in the Endpoint dialog box.


Figure 3: URL to Data Studio web console in the Endpoint dialog box of IBM Workload Deployer

  1. In the Data Studio full client, open the Data Studio web console preferences page Window -> Preferences -> Data Management -> Data Studio Web Console.
  2. Enter the URL of the deployed Data Studio web console that you copied from the Endpoint dialog box in the Data Studio web console preferences page.

    You must edit the URL from the Endpoint dialog box before entering the information in the Data Studio web console preferences page, see Figure 4 and the example below for details.

    Example: If you copy the following URL from the Endpoint dialog box:

    http://172.16.68.51.11080/datatools/console?j_username-db2admin&j_password=passw0rd

    then you enter the following in the Data Studio web console preferences page:
    • URL: http://172.16.68.51.11080/datatools
    • User name: db2admin
    • Password: passw0rd


Figure 4: Data Studio web console preferences page in the Data Studio full client


Monitoring databases

IBM Data Studio web console provides health and availability monitoring features for your databases. Use the Data Studio web console to view alerts, applications, utilities, storage, and related information.

In the Data Studio full client, you can access database monitoring features of the Data Studio web console from the Administrative Explorer, see the Opening Data Studio web console from within the workbench in the IBM Data Studio Information Center for more information about how you can access Data Studio web console from within the Data Studio full client.


Figure 5: Opening Data Studio web console from the Administration Explorer

For more information about monitoring databases with the Data Studio web console, see the following topics in the IBM Data Studio Information Center:


Managing jobs

IBM Data Studio web console provides job creation, job scheduling, and job management for your databases.

In the Data Studio full client you can schedule jobs from the SQL and XQuery editor, see the Opening Data Studio web console from within the workbench in the IBM Data Studio Information Center for more information about how you can access Data Studio web console from within the Data Studio full client.


Figure 6: Scheduling jobs from the Data Studio SQL and XQuery editor

For more information about managing jobs with the Data Studio web console, see the following topics in the IBM Data Studio Information Center:

[{"Product":{"code":"SS62YD","label":"IBM Data Studio"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"3.1.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
17 June 2018

UID

swg27025111