Db2 Administration

Review System Tables

Let’s continue with the example Create Tables from Script above, we’ll use the db already in our IBM Cloud instance. The HR database.

Load db into Db2

  • Left menu > Resource list > Choose my instance Db2-xx
  • Manage > Go to UI

Tables Information

  • Start New Query
  • Run query
  • There are numerous results with only the first few displayed. You can see the first few columns showing the name of the table, its creator, and the type of the database object (T for table). The first five shown are all system tables and you can see that the creator is SYSIBM.
SELECT *
FROM   SYSIBM.SYSTABLES;

Metadata for Tables

Let’s perform a more advanced query to retrieve the metadata for the tables.

  • Let’s query the table based on the creator column
  • It’s the column which stores the schema the the given table belongs to
  • find the data icon on the left
  • then click on the Tables tab on top as shown here

  • Find the schema name
  • copy it
  • Edit SQL query setting the creator’s name to the string you just copied

SELECT  * 
FROM    SYSIBM.SYSTABLES
WHERE   CREATOR = 'NZX60439'
  • Run the query and the output will be:

Update Creator of System Tables

In Db2, the system tables CANNOT be edited directly with INSERT, UPDATE, or DELETE statements. When you create, modify, or delete other objects in the database, the system tables are automatically updated to reflect the changes. Therefore, system tables are only for retrieving metadata about other objects in your database, you do not modify them directly.

The current name for the “CREATOR” of the five tables that belong to the sample HR database appears to be some random combination of letters and numbers, but suppose you wanted to modify the entries in “SYSTABLES” to have a different schema name in the “CREATOR” column for those tables.

Let’s see what happens if you try to modify the “CREATOR” column in “SYSTABLES”.

  1. In the SQL editor, enter the following command and replace <current creator name> with the schema name you copied earlier in Step 3 of Task B. Replace <new creator name> with a new name of your choice, then click the “Run all” button.
UPDATE  SYSIBM.SYSTABLES 
SET     CREATOR = 'santa'
WHERE   CREATOR ='NZX60439';

Manage User Accounts & Roles

User management is the process of controlling which users are allowed to connect to the Db2 server and what permissions they have on each database. In Db2, users are created and assigned a specific built-in user role with a predefined set of privileges on the system. The built-in user roles are simple to use and easy to manage.

In Db2 on Cloud Standard plan, a user is created by the administrator and typically assigned one of the following built-in user roles: Administrator or User. These built-in user roles provide different levels of access commonly needed in a database system. The following general summary describes what each of the built-in user roles can do:

Administrator

  • Has access to all of the features in the console, and has the privileges to manage other users.

User

  • Has access to many of the features in the console, and has the privileges to manage their own user profile. Users with this role also have full access to their own tables and can use their tables with all permissions, including giving other users permission to access and use their tables.

Under Administration > User management of Db2 on Cloud Standard plan, the Add option allows you to create/add new user with either of the built-in user roles. You can see some created users shown in the above image, for example James with user privilege and sandip with administrator privilege.

The built-in set of user roles is simple to use and easy to manage, but if it does not provide you with the desired set of fine-grained permissions, you can also create your own user-defined user roles to satisfy your special requirements. A role is a database object that groups together one or more privileges and can be assigned to users. A user that is assigned a role receives all of the privileges of that role. A user can have multiple roles. A role hierarchy is also supported. This allows one of the roles to inherit all of the privileges and authorities of the other role or roles that they have been granted. A user-defined user role simplifies the administration and management of privileges by allowing the administrator to group authorities and privileges into a single role and then grant this role to the users that need those authorities and privileges to perform their jobs.