Creating tables on z/OS
Table 31. Creating tables in the database
If you are creating tables for your
users: |
If users are creating tables themselves: |
- Step 1
- Create a table space and define it to DB2 before its first
DB2 use. Use the appropriate DB2 UDB Administration Guide to
help you decide on assigning authorities to create table spaces
or dbspaces.
|
- Step 1
- Use the DB2 UDB for z/OS Administration Guide to
grant a user DB2 CREATETS authority or DB2 CREATETAB authority. Create
a table space (if you have only given them CREATETAB authority)
and define it to DB2 before its first use.
|
- Step 2
- To create the table, issue either an SQL CREATE TABLE statement,
a QMF DISPLAY command followed by a SAVE DATA command, or an
IMPORT TABLE command. See Using DB2 QMF for examples
of creating tables.
|
- Step 2
- Assign the table space in the user's QMF profile, using an
SQL UPDATE statement for the SPACE field. You can update the SYSTEM
profile if you need to change its default values.
|
- Step 3
- Create one or more indexes on the tables you create, to improve
DB2 performance. See the DB2 UDB for z/OS SQL Reference for
information on the CREATE INDEX statement and details on logical
design of tables.
|
- Step 3
- Grant CREATETAB authority to users creating their own tables
in table spaces, or assign CREATETS authority and allow users to
create table spaces for their own use. Users automatically have
all SQL privileges on tables and table spaces they create.
|
- Step 4
- Fill the tables with data. Use the DB2 UDB for z/OS LOAD Utility,
QMF IMPORT commands (for transferring small tables), or other methods. DB2 UDB for z/OS Utility Guide and Reference explains
how to use the LOAD Utility. Using DB2 QMF explains
exporting and importing objects in QMF.
|
- Step 4
- Provide education on the SQL CREATE TABLE statement, QMF SAVE
DATA and IMPORT commands, and other guidelines your site has for
creating tables. See DB2 QMF Reference for
more information on these commands.
|
- Step 5
- Grant DB2 and SQL privileges for the tables to users who need
them.
|
- Step 5
- Grant DB2 and SQL privileges on any table or view on which
users issue SAVE DATA or IMPORT commands to create new tables. Grant
at least the SELECT privilege, or QMF cannot read the data to create
a new table.
|
For more information on the CREATE TABLE, CREATE INDEX, and other
SQL statements related to creating tables, see DB2 UDB for z/OS SQL Reference.
Choosing and assigning a table space for the user
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 DB2 UDB for z/OS 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 DB2 for z/OS SQL Reference.
For information on the table spaces, see DB2 UDB for z/OS 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 for OS/390 and z/OS 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 z/OS Administration Guide.
Granting a user DB2 CREATETAB authority (z/OS)
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 33, where userid1, userid2,
and userid3 represent SQL authorization
IDs.
Figure 33. SQL statements to grant CREATETAB authority to more than one user
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 the DB2 UDB for z/OS Administration Guide for
more information on creating a table space and a discussion of DB2
authority levels.
