Creating tables on OS/390

For more information on the CREATE TABLE, CREATE INDEX, and other SQL statements related to creating tables, see the DB2 UDB for OS390 SQL Reference manual.

Choosing and assigning a table space for the user (OS/390)

A table space can be either assigned to or created by the user. Any QMF user with CREATETAB authority can create tables in an assigned table space. If the table space is owned, only the owner can create tables in it unless they assign authority to others. For additional guidance on table spaces, see the DB2 UDB for OS390 Administration Guide .

When creating a table space, you must choose between the two options: explicit and implicit.

Explicit

With this option, all the tables created by the user's SAVE and IMPORT commands appear in a single table space created with an SQL CREATE TABLESPACE command. In DB2 terminology, this table space is "explicitly created". For example,

UPDATE Q.PROFILES
  SET SPACE='DBASE1.TSPACE1'
  WHERE CREATOR='USERA' AND TRANSLATION='ENGLISH'

Implicit

With this option, each table created by the user's SAVE and IMPORT commands goes into a table space created exclusively for that table by DB2. In DB2 terminology, this table space is "implicitly created". Such table spaces have the default LOCKSIZE, BUFFERPOOL, STOGROUP, and space attributes, and have names derived from their table names. For example,

UPDATE Q.PROFILES
  SET SPACE='DATABASE DBACE1'
  WHERE CREATOR='USERA' AND TRANSLATION='ENGLISH'

For information on the default attributes, see the description of the CREATE TABLESPACE query in the DB2 UDB for OS390 SQL Reference manual.

For information on the table spaces, see the DB2 UDB for OS390 Administration Guide.

You need to consider the following factors when you decide between the options for the table space.

Table sizes

The default attributes for implicitly created table spaces might not be suitable for the intended tables. The default values for the space parameters (PRIQTY and SECQTY) are intended for small sample and summary tables. If the user's tables are large, the explicit table space option is probably the better choice.

If the table space is too small, the new table remains in the table space but is empty. The table space must therefore be enlarged, before the SAVE or IMPORT command can run successfully. Procedures to do this are described in the DB2 UDB for OS390 Administration Guide.

Maintenance

When you use the QMF Explicit Table Space Option, you simplify maintenance if you take advantage of segmented table spaces. Implicitly created table spaces can also simplify maintenance.

For example, if the user creates various temporary tables and then erases them, creating and erasing these tables in a simple table space (not segmented) causes a rapid buildup of dead space that would soon have to be removed by reorganizing the table space. In contrast, when a table is dropped in a segmented table space, its segments become immediately available for reuse when the drop is committed. It is not necessary to wait for reorganization of the table space. An implicitly created table space is erased automatically when the table it contains is erased.

Resource contention

To avoid resource contention, use either the explicit table space option with a segmented table space, or the implicit table space option.

With a segmented table space, when a table is locked, the lock does not interfere with access to segments of other tables. Having a number of tables in a single simple table space, each used by more than one user, might cause resource contention, but placing the tables in a segmented or separate table space might avoid the resource contention.

Integrity and security

You might have to grant the user certain DB2 privileges that the user would not otherwise need. With the explicit table space option, you can limit these added privileges to the creation of tables in the chosen database. With the implicit table space option, you must grant the user the privilege to create table spaces for the database, and you cannot restrict this privilege to table spaces created with the SAVE and IMPORT commands.

Convenience

An explicitly created table space is already available for user created tables. It is created during QMF installation and used for the installation verification procedure. The table space is named DSQTSDEF, and its database is DSQDBDEF. You might find that this table space is large enough to hold the tables of your users.

Many users should use this table space only if the tables are primarily read only.

Choosing the type of table space

You can choose from three types of table spaces for your users.

• Simple
• Segmented
• Partitioned

For more information about the types of table spaces, see the DB2 UDB for OS390 Administration Guide .

Granting a user DB2 CREATETAB authority (OS/390)

You need to grant DB2 CREATETAB authority to any user who needs to create tables in a database. To grant a user CREATETAB authority, issue the SQL statement shown in Figure 101, where userid1, userid2, and userid3 represent SQL authorization IDs.

GRANT CREATETAB on database DBASEA TO userid1, userid2, userid3, ...

A user with CREATETAB authority can create tables in a table space. Users with CREATETS authority can create a table space for their own use.

If you want to allow a user to create tables, but need to maintain control over how much resource is used, assign a table space for the user rather than granting CREATETS authority. That way, you can control the size of the table space and the amount of resource used.

See DB2 UDB for OS390 Administration Guide for more information on creating a table space and a discussion of DB2 authority levels.