Exploring integrated databases in Watson Query

Procedure

To explore integrated databases, follow these steps.

1. On the navigation menu, click Data > Data virtualization

   The service menu opens to the Data sources page by default.

2. Click Explorer > Tables to view all schemas and tables. Select a schema to view the Tables page.
   On the Tables page, you can perform the following tasks.

   • Click New table to create a table.
   • Click a table name to see the table definition.
   • Click the horizontal overflow menu () to get the properties of a table.
   On the vertical overflow menu (), you can perform the following tasks.

   • Export tables as CSV files.
   • Grant or revoke privileges for tables.
   • Generate DML statements for the table (select, update, insert, delete).
   • Generate Data Definition Language (DDL) statements for tables.
   • Get the dependency relationship for table.
   • Update statistics for the table.
   • Drop a single table or drop multiple tables.

   On the Table definition page, you can click View data to view table data.

3. Click Explorer > Views to view all views and schemas. Select a schema to view the Views page.
   On the Views page, you can perform the following tasks.

   • Click New view to create a view.
   • Click a view name to see the view definition.
   • Click the horizontal overflow menu () to get the properties of a view.
   On the vertical overflow menu (), you can perform the following tasks.

   • Export views as CSV files.
   • Grant or revoke privileges for views.
   • Generate DML statements for the view (select, update, insert, delete).
   • Generate DDL statements for views.
   • Get the dependency relationship for view.
   • Drop a single view or multiple views.

   On the View definition page, you can click View data to view data and get the properties of view in the column row.

4. Click Explorer > Remote Tables to view all schemas and remote tables. Select a schema to view the Remote tables page.
   On the Remote tables page, you can perform the following tasks.

   • Click a remote table name to see the remote table definition.
   • Click the horizontal overflow menu () to get the properties of a remote table.
   On the vertical overflow menu (), you can perform the following tasks.

   • Export remote tables as CSV files.
   • Grant or revoke privileges for remote tables.
   • Generate DML statements for the remote table (select, update, insert, delete).
   • Get the dependency relationship for remote table.

   On the Remote Table definition page, you can click View data to view remote table data.

5. Click Explorer > Aliases to view all schemas and aliases. Select a schema to view the Aliases page.
   On the Aliases page, you can perform the following tasks.

   • Click New Alias to create an alias.
   • Click an alias name to see the alias definition.
   • Click the horizontal overflow menu () to get the properties of an alias.
   On the vertical overflow menu (), you can perform the following tasks.

   • Export aliases as CSV files.
   • Generate DML statements for the alias (select, update, insert, delete).
   • Get the dependency relationship for alias.
   • Drop a single alias.

   On the Alias Definition page, you can click View data to view alias data.

6. Click Explorer > Authorization to view groups, roles, and users that have permission for the objects in the service. A database administrator can use the Authorization page to manage Db2 privileges for users, groups, and roles. On the Authorization page, existing Db2 users, roles, and groups are listed in the table. These users are not the same as Watson Query users. You cannot use the Authorization page to grant access to Watson Query users, groups, and roles.
   

   Click Grant multiples to grant multiple authorizations and privileges in batch and click Revokes multiples to revoke multiple authorizations and privileges in batch.

   Grant multiple users and groups' access to Db2 databases

   To open the grant multiples operation user interface, click Grant multiples.

   1. On the Select Authorizations page, you can add a Db2 user or group. Moreover, you can select one or more users, groups, or roles to grant to the newly added user or group. You cannot add a role for Watson Query.
   2. On the Select objects and privileges page, you can select databases, objects, and related privileges that you want to grant specific authorizations on the Select authorizations page. Sequentially, you can select authorizations to get the related operation privileges for database and objects.
   3. On the Select roles page, you can select one or more roles. You can choose to check or clear the with admin option. The selected authorizations are granted membership of one or more selected roles, with or without the admin option.
   4. Click Generate DDL to generate SQL statements for the grant operation from the SQL editor.
   5. Click Run to execute the grant operation immediately.

   Note: It is not mandatory to grant Select objects and privileges and Select roles at the same time, however you need to grant at least one of them.

   Revoke multiple users and groups' access to Db2 databases

   Click Revoke multiples to open the revoke multiples user interface. Follow the same procedure as granting multiple users and groups access to Db2 databases. When you hover over a row in authorization table, the overflow menu is shown.

   Object privileges

   On the Select objects and privileges page, you can grant or revoke the database, objects, and related privileges for a selected user, group, or role.

   Roles

   On the Select roles page, you can grant or revoke the privilege to be a member of specified roles and configure the with admin option privileges for a selected user, group, or role.

7. Click Explorer > Application objects to view stored procedures, user-defined types, and user-defined functions, which have a similar page structure.

   Stored procedures

   On the Schemas page, you can click New implicit schema to create a schema.

   On the Procedures page, you can perform the following tasks.

   • Click New procedure to create a procedure.
   • Click a procedure name to see the procedure definition.
   • Click the horizontal overflow menu () to get the properties of a procedure.

   On the vertical overflow menu (), you can perform the following tasks.

   • Export procedures as CSV files.
   • Grant or revoke the privileges for procedures.
   • Drop a single procedure or multiple procedures.

   On the Procedure parameters page, you can view procedure parameters, get the dependency relationship for sequence, and drop single sequence.

   User-defined Types

   On the Schemas page, you can click New implicit schema to create a new schema.

   On the User-defined Types page, you can perform the following tasks.

   • Click New UDT to create a UDT.
   • Click a UDT name to see the UDT definition.
   • Click the horizontal overflow menu () to get the properties of a UDT.

   On the vertical overflow menu (), you can perform the following tasks.

   • Export UDTs as CSV files.
   • Grant or revoke the privileges for UDT.
   • Drop a single UDT or multiple UDTs.

   On the User-defined Type definition page, you can perform the following tasks.

   • Get the dependency relationship for a UDT.
   • Revalidate a UDT.

   User-defined Functions

   On the Schemas page, you can click New implicit schema to create a new schema.

   On the User-defined Functions page, you can perform the following tasks.

   • Click New UDF to create a UDF.
   • Click a UDF name to see the UDF definition.
   • Click the horizontal overflow menu () to get the properties of a UDF.

   On the vertical overflow menu (), you can perform the following tasks.

   • Export UDFs as CSV files.
   • Grant or revoke the privileges for UDF.
   • Drop a single UDF or multiple UDFs.

   On the User-defined Function definition page, you can perform the following tasks.

   • Get the dependency relationship for a UDF.
   • Revalidate a UDF.

8. Click Explorer > Workloads to view all workload data and export all workload to a .csv file.
   • You can click the Add workload by template drop-down menu to select the different workload templates that can be used to generate the workload.
   • You can click in the Action Bar to enable Enable activity data collection for new workload and Enable aggregate activity data collection for new workload.
   • You can enable or disable Enable collect activity data and Enable collect aggregate activity for selected workloads
   • You can drop selected workloads.

9. Additionally, Watson Query Admins can explore schemas, MQT statements, and sequences.

   1. Click Explorer > Schemas to view all schemas.
      On the Schemas page, you can perform the following tasks.

      • Click New schema to create a schema.
      • Click a schema name to see the schema definition.
      • Click the horizontal overflow menu () to get the properties of a schema.
      On the vertical overflow menu (), you can perform the following tasks.

      • Export schemas as CSV files.
      • Grant or revoke the privileges for schemas.
      • Generate DDL statements for schemas.
      • Drop a single schema or multiple schemas.
   2. Click Explorer > MQTs to view all schemas and MQTs.
      On the MQTs page, you can perform the following tasks.

      • Click New MQT to create an MQT.
      • Click an MQT name to see the MQT definition.
      • Click the horizontal overflow menu () to get the properties of an MQT.
      On the vertical overflow menu (), you can perform the following tasks.

      • Export schemas as CSV files.
      • Grant or revoke the privileges for MQT.
      • Generate DDL statements for MQT.
      • Generate DML statements for MQT (select, update, insert, delete).
      • Get the dependency relationship for MQT.
      • Update statistics for MQT.
      • Drop a single MQT or multiple MQTs.

      On the MQT definition page, you can click View data to view MQT data.

   3. Click Explorer > Sequences to view all schemas and sequences.
      On the Sequences page, you can perform the following tasks.

      • Click New sequence to create a sequence.
      • Click a sequence name to see the sequence definition.
      • Click the horizontal overflow menu () to get the properties of a sequence.
      On the vertical overflow menu (), you can perform the following tasks.

      • Export sequences as CSV files.
      • Grant or revoke privileges for sequences.
      • Drop a single sequence or multiple sequences.

      On the Alter sequence page, you can alter sequence properties, get the dependency relationship for sequence, and drop single sequence.