Working with integrated IBM Db2 Warehouse databases

Accessing the database

Database users and administrators can access the 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.

Monitoring the database

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

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.

Loading data

Exploring the database

You can visualize the schema, tables, views, and columns in the database by 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
• Creating a table
• Creating a view

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 Db2 documentation.

6. Save the view.

Running SQL

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

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

Running instance commands

A database administrator can run instance level commands, for example to start or stop the Db2 Warehouse instance to update database manager or Db2 registry variables.

A db2start or db2stop command is interpreted as a recovery event by the high availability (HA) components of Db2, so to run the maintenance tasks, the HA monitoring must first be turned off.

Deleting a database

A database administrator can delete a database.

To delete a database:

1. Sign in to the Cloud Pak for Data web client.
2. From the navigation menu, select Data > Databases.
3. Open the actions menu for the database that you want to access, and select Delete.