Table of contents

Working with integrated IBM Db2 databases

If you created a IBM® Db2® database deployment in your IBM Cloud Pak for Data cluster, you can access the integrated database console to complete common tasks, such as exploring the tables in the database or loading data into the database.

Accessing the database

Database users and administrators can access the database.

To access the database:
  1. Sign in to the Cloud Pak for Data web client.
  2. From the navigation menu, select Collect > My data.
  3. Open the Databases tab.
  4. Open the actions menu for the database that you want to access and select Open database.

Connecting to the database from another application

You can use the client drivers that are provided with the database to connect to the database from another application.

To access the client drivers:
  1. Access the database.
  2. From the menu, select Connection information.
  3. Select the operating system where your client application is running and follow the instructions in the web client.

Alternatively, you can use the JDBC URL to connect to the database from another application.

To connect to the database:
  1. From the Cloud Pak for Data navigation menu, select Collect > My data.
  2. Open the Databases tab.
  3. Open the actions menu for the database that you want to connect to and select Details.

    The connection URL is displayed under the Access Information section of the Database details page.

Use your Cloud Pak for Data credentials to connect to the database.

Monitoring the database

You can use the integrated monitoring console to ensure that the database is healthy.

To monitor the database:
  1. Access the database.
  2. You can get a high-level view of the health of the database from the Menu > Overview page:
    Metric Description
    Database availability Indicates whether the database is up or down, and the relevant alerts.
    Database responsiveness Indicates whether the system is responding well to the incoming requests.
    Database throughput Indicates how much work the system is handling.
    Database resource usage Indicates the resource usage of the database in terms of CPU, memory, storage, and log space.
    Database contention Indicates whether there is any contention between connections.
    Database time spent Indicates the percentage of time that is taken for database calls.
  3. To get more detailed information about the database, select one of the following options from the Menu:
    Option Description
    In-flight Executions See a list of the statements that are currently running on the database or that ran recently.

    You can use these metrics to identify costly or long running statements. You can cancel individual statements or disconnect a user or application if the statement needs to be stopped.

    Connections See the different users or applications that have open connections to the database. A user or application can open multiple connections.

    You can use these metrics to find the source of disruptive behavior. You can use this information to find connections that are holding multiple locks or applications that are wasting resources because they are idle but the connection is not closed.

    Table Performance See a list of the rows read, table scans, and accesses per minute. You can use this view to analyze the current storage and access status to determine which tables and schemas are responsible for most of the activity in the database.
    Storage See how much storage each table is using or how much storage all of the tables in a schema are using. You can use this view to identify the largest tables and schemas in the database.
    Package cache See information about statements that previously ran on the database. For example, you can see the number of executions and the total amount of CPU used

    You can use these metrics to identify statements that use a disproportionate amount of system resources.

    Workloads See the amount of work that is being done by the data server and how efficiently the work is being completed. A workload can collect information based on user ID, client IP address, or application name.

    For more information about workloads, see Introduction to Db2 workload management concepts.

    Service classes See the amount of work that is being done by the data server and how efficiently the work is being completed. Database requests are run in a service class and are assigned to a workload. A service class determines the resources that are assigned to requests. A service class also monitors and controls sets of activities in the database.

Exploring the database

You can visualize the schema, tables, views, and columns in the database using the Explorer feature. You can also see the groups, roles, and users that have permissions to the objects in the database.

To explore the database:

  1. Access the database.
  2. To explore the tables in the database:
    1. From the menu, select Tables.
    2. Select a schema to see the tables that are associated with the schema.
    3. Select a table to see the columns and data types that are defined in table.
    4. Click View Data to see the data that is in the table.
  3. To explore the views in the database:
    1. From the menu, select Views.
    2. Select a schema to see the views that are associated with the schema.
    3. Select a view to see the tables and columns that are available in that view.
  4. To explore the privileges in the database:
    1. From the menu, select Privileges.
    2. Select the group, role, or user for which you want more information.
    3. To see the objects that the group, role, or user can access, select Object Privileges from the action menu.
    4. To see the roles that are associated with the group, role, or user, select Membership.

      For more information about security, see the Roles section in the IBM Db2 documentation.

In addition to visualizing the objects in the database, you can create the following objects:

Creating a schema
To create a schema:
  1. Access the database.
  2. From the menu, select Tables.
  3. Click New schema.
  4. Specify the name to use for the schema and click Create.
Creating a table
To create a table:
  1. Access the database.
  2. From the menu, select Tables.
  3. Select the schema to use for the table.
  4. Click New table.
  5. Specify the name to use for the table.
  6. Specify the columns to create in the table.

    If you plan to create multiple tables with the same definition, click Generate DDL. This option creates a script that you can save and run to create identical tables.

  7. Click Create.
Creating a view
To create a view:
  1. Access the database
  2. From the menu, select Views.
  3. Select the schema for which you want to create the view.
  4. Click New view.
  5. Edit the SQL statement to define the data that can be displayed with the view.

    For more information on SQL, see the SQL reference in the IBM Db2 documentation.

  6. Save the view.

Managing access to objects in the database

You can create users, roles, and groups to manage access to objects in the database.

For more information about security, see the Roles section in the IBM Db2 documentation.

To create a user, role, or group:

  1. Access the database
  2. From the menu, select Privileges.
  3. Click Add and specify whether you want to create a user, role, or group.
  4. Click OK.
  5. To specify the objects that the user, role, or group has access to:
    1. Select Object Privileges from the action menu.
    2. Select the type of object that you want to give access to, such as a database, table, view, or nickname.
    3. Click Grant.
    4. Select the objects that you want to give access to and click OK.
  6. To specify the roles that the user, role, or group is a member of:
    1. Select Membership from the action menu.
    2. Click Grant Role.
    3. Select the roles that you want to give membership to and specify whether the membership includes admin options.
    4. Click OK.

Loading data

You can load data from the following sources:
  • From your local file system, you can load a single delimited text file (CSV or TXT).
  • From Amazon S3, you can load delimited text files (CSV and TXT).
  • From IBM Cloud Object Storage, you can load delimited text files (CSV and TXT).
To load data into the database:
  1. Access the database.
  2. From the menu, select Load.
  3. Select the method that you want to use to import the data:
    • To upload from your local file system, select My Computer and browse for the local file.
    • To upload from Amazon S3, select Amazon S3 and enter your credentials. Then, browse for the files that you want to load.
    • To upload from IBM Cloud Object Storage, select Cloud Object Storage and enter your credentials. Then, browse for the files that you want to load.
  4. Click Next.
  5. Select the schema and table where you want to load the data.
  6. Specify whether you want to append the data to the table or overwrite the existing data in the table. Then, click Next.
  7. Ensure that the data definition is correct. For example, ensure that the correct encoding is selected, the correct separator is used, and so on. Then, click Next.
  8. Review the settings for the load.
  9. Click Begin Load.
    Important: Do not sign out of the web client until the upload completes.

Running SQL

SQL is a standardized language for defining and manipulating data in a relational database.

You can use the Run SQL feature to execute SQL statements and scripts against the database.

To run SQL:
  1. Access the database.
  2. From the menu, select Run SQL.
  3. Create your SQL statement or script.

    For more information on SQL, see the SQL reference in the IBM Db2 documentation.

Deleting a database

A database administrator can delete a database.

To delete a database:
  1. From the navigation menu, select Collect > My data.
  2. Open the Databases tab.
  3. Open the actions menu for the database that you want to access and select Delete.