Administration

Data warehouse administration is primary task that is required to make data warehouse up and running. Now let us discuss details about who can do the administration and what it is needed.

7.2.1 Who Can Do the Database Administration

Authority levels provide a method of grouping privileges and higher-level database manager maintenance and utility operations. Database authorities enable users to perform activities at the database level. A user, group, or role can have one or more of the following authorities:

Administrative authority level that operates at the instance level, SYSADM (system administrator)

Administrative authority levels that operate at the database level:

- DBADM (database administrator)

The DBADM authority level applies at the database level and provides administrative authority over a single database. This database administrator possesses the privileges required to create objects, issue database commands, and access table data. The database administrator can also grant and revoke CONTROL and individual privileges.

- SECADM (security administrator)

The SECADM authority level applies at the database level and it is related to security administration, such as the authority required to create, alter (where applicable), and drop roles, trusted contexts, audit policies, security label components, security policies, and security labels, which are used to protect tables. It is also the authority required to grant and revoke roles, security labels and exemptions as well as to grant and revoke the SETSESSIONUSER privilege. A user with the SECADM authority can transfer the ownership of objects that they do not own. They can also use the AUDIT statement to associate an audit policy with a particular database or database object at the server.

The SECADM authority has no inherent privilege to access data stored in tables and has no other additional inherent privilege. This authority can only be granted a user by a SYSADM user. The SECADM authority can be granted to a user but cannot be granted to a group, a role or to PUBLIC.

System control authority levels that operate at the instance level:

- SYSCTRL (system control)

The SYSCTRL authority level provides control over operations that affect system resources. For example, a user with SYSCTRL authority can create, update, start, stop, or drop a database. This user can also start or stop an instance, but cannot access table data. Users with SYSCTRL authority also have SYSMON authority.

- SYSMAINT (system maintenance)

The SYSMAINT authority level provides the authority required to perform maintenance operations on all databases associated with an instance. A user with SYSMAINT authority can update the database configuration, backup a

database or table space, restore an existing database, and monitor a database. Like SYSCTRL, SYSMAINT does not provide access to table data.

Users with SYSMAINT authority also have SYSMON authority.

The SYSMON (system monitor) authority level

SYSMON provides the authority required to use the database system monitor. It operates at the instance level.

Database authorities

To perform activities such as creating a table or a routine, or for loading data into a table, specific database authorities are required. For example, the LOAD database authority is required for use of the load utility to load data into tables (a user must also have INSERT privilege on the table).

Figure 7.2 illustrates the relationship between authorities and their span of control (database, database manager).

Figure 7.2 Hierarchy of Authorities

For more details on DB2 authorities, privileges and database object ownership, please refer to IBM Data Studio [30] or IBM DB2 Information Center [24].

7.2.2 What To Do as Database Administration

Important administration tasks are:

Use workload management to manage user concurrency, query concurrency and system availability. Workload management is very much like a traffic signal at intersection. A traffic signal helps in maintaining flow of traffic from all directions and for all types of vehicles. Due to this traffic signal, some vehicles may have to wait for sometime for their turn to cross the junction. Similarly, workload management in database makes sure that all types of queries can be executed, from all valid system users. Here as well, a query or the user may have to wait if system resources are not available, or rules defined for that particular user/query does not allow immediate execution. For more details on IBM DB2® Workload Management, please refer to IBM Redbook “DB2 Workload Manager for Linux, UNIX, and Windows” [25]. Workload management best practices [26] are also available for handy reference. This best practice document covers workload management design, implementation and monitoring.

Capture high water mark for number of - Database connections

This helps database administrator to be aware of any unknown behavior to application or user queries.

- Database users

This helps database administrator to keep track of applications or users logging into system. Every connected user uses database server resources even if no query is executed on that connection. Therefore for optimal usage of database server resources, it is important to allow only desired users and applications to keep the connection idle.

Keep history of changes in warehouse.

Database objects and environment change log helps administrator to keep track all changes applied to database. This information is very helpful in event of database malfunction. Not only the change can be reverted (if system exhibits any issues

after this change), DBA can share this change set with product vendor to identify potential reason for database malfunction.

Keep system as per the tested warehouse product stack

All software and hardware products are supported on a well-defined environment.

This environment includes hardware platform, firmware versions, operating system versions, compatibility with other applications etc. DBA should make sure that either there is no deviation from supported hardware and software stack, or all deviation should be approved from vendor before change implementation.

Keep watch on database logs, operating system logs and other appropriate logs DBA should monitor appropriate logs (database, operating system, application) so that any suspicious event can he detected and handled in time. Absence of log monitoring leads to system failures leading to unplanned downtime.

Scheduled downtime

DBA should always plan for schedule downtime as permitted by SLA to perform any maintenance activity. Even if system has no symptoms of abnormal behavior, maintenance should be performed as a planned database job.