Implementing Db2 tables

Use the columns and rows of Db2 tables as logical structures for storing data.

Designing tables that many applications use is a critical task. Table design can be difficult because you can represent the same information in many different ways. This information briefly describes how tables are created and altered, and how authorization is controlled.

You create tables by using the SQL CREATE TABLE statement. At some point after you create and start using your tables, you might need to make changes to them. The ALTER TABLE statement lets you add and change columns, add or drop a primary key or foreign key, add or drop table check constraints, or add and change partitions. Carefully consider design changes to avoid or reduce the disruption to your applications.

Most organizations have naming conventions to ensure that objects are named in a consistent manner. The table name is an identifier of up to 128 characters. You can qualify the table name with an SQL identifier, which is a schema. When you define a table that is based directly on an entity, these factors also apply to the table names.

If you have DBADM (database administration) authority, you probably want to control the creation of Db2 databases and table spaces. These objects can have a big impact on the performance, storage, and security of the entire relational database. In some cases, you also want to retain the responsibility for creating tables. After designing the relational database, you can create the necessary tables for application programs. You can then pass the authorization for their use to the application developers, either directly or indirectly, by using views.

However, if you want to, you can grant the authority for creating tables to those who are responsible for implementing the application. For example, you probably want to authorize certain application programmers to create tables if they need temporary tables for testing purposes.

Some users in your organization might want to use Db2 with minimum assistance or control. You can define a separate storage group and database for these users and authorize them to create whatever data objects they need, such as tables.