When designing a database, it is important to consider which tables users should be able to access. Access to tables is granted or revoked through authorizations. The highest level of authority is system administration authority (SYSADM). A user with SYSADM authority can assign other authorizations, including database administrator authority (DBADM).
There are other issues that you may want to consider in your design, such as audit activities, historical data, summary tables, security, data typing, and parallel processing capability.
For audit purposes, you may have to record every update made to your data for a specified period. For example, you may want to update an audit table each time an employee's salary is changed. Updates to this table could be made automatically if an appropriate trigger is defined. Audit activities can also be carried out through the DB2 audit facility. For more information, see Chapter 17, Auditing DB2 Activities .
For performance reasons, you may only want to access a selected amount of data, while maintaining the base data as history. You should include within your design, the requirements for maintaining this historical data, such as the number of months or years of data that is required to be available before it can be purged.
You may also want to make use of summary information. For example, you may have a table that has all of your employee information in it. However, you would like to have this information divided into separate tables by division or department. In this case, a summary table for each division or department based on the data in the original table would be helpful. For more information about summary tables, see Creating a Summary Table .
Security implications should also be identified within your design. For example, you may decide to support user access to certain types of data through security tables. You can define access levels to various types of data, and who can access this data. Confidential data, such as employee and payroll data, would have stringent security restrictions. For more information about security and authorizations, see Chapter 16, Controlling Database Access .
You can create tables that have a structured type associated with them. With such typed tables, you can establish a hierarchical structure with a defined relationship between those tables called a type hierarchy. The type hierarchy is made up of a single root type, supertypes, and subtypes.
A reference type representation is defined when the root type of a type hierarchy is created. The target of a reference is always a row in a typed table or view.
For more information about implementing a design that includes typed rows and tables, see Chapter 13, Before Creating a Database . Refer to the Data Movement Utilities Guide and Reference for information about moving data between typed tables that are in a hierarchical structure.
As your business grows, you may need the additional capacity and performance capability provided by DB2 Enterprise - Extended Edition. In this environment, your database is partitioned across several machines or systems, each responsible for the storage and retrieval of a portion of the overall database. Each partition (or node) works in parallel to handle SQL or utility operations.
Issues and considerations relating to parallel operations are included throughout this book.