Creating tables on VM and VSE

Table 53. Creating tables in the database
If you are creating tables for your users:	If users are creating tables themselves:
Step 1 Acquire a dbspace and define it to DB2 for VM before its first use. Use the appropriate DB2 Administration Guide to help you decide on assigning authorities to create table spaces or dbspaces.	Step 1 Acquire a dbspace and define it to DB2 before its first use. Use the DB2 Server for VSE & VM Administration Guide to help you decide on a private or public dbspace.
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 the Using QMF manual for examples of creating tables.	Step 2 Assign the dbspace 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.	Step 3 Grant DB2 RESOURCE authority to users creating their own tables in public dbspaces, or acquire a private dbspace for the user. Users automatically have all SQL privileges on tables they create.
Step 4 Fill the tables with data. Use the DB2 DBS utility, QMF IMPORT commands (for transferring small tables), or other methods. The Using QMF manual 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 the QMF Reference manual for more information on these commands.
Step 5 Grant SQL privileges for the tables to users who need them.	Step 5 Grant 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.

Choosing and acquiring a dbspace for the user

A dbspace can be either private or public. Any QMF user with DB2 RESOURCE authority can create tables in a public dbspace. If the dbspace is private, only the assignee is allowed to create tables in it. For additional guidance on types of dbspaces, see the DB2 Server for VSE & VM Database Administration manual.

Using the SQL ACQUIRE statement

After you decide whether a public or private dbspace best suits your needs, acquire the dbspace using a statement similar to the one in below. You can enter this statement from the QMF SQL query panel, then press the Run function key to run the query.

Figure 102. Acquiring a dbspace

ACQUIRE PUBLIC DBSPACE NAMED dbspacename
(PAGES=1024)

Substitute PRIVATE for PUBLIC in the statement if you are acquiring a private dbspace and be sure to qualify dbspacename with the SQL authorization ID of the user for whom you are acquiring the dbspace.

Sizing a dbspace

The size of the dbspace in an acquire statement is given in pages, where one page is 4,096 bytes. If you do not specify a page size, a default value of 128 pages is assumed. Estimate the size you need by estimating the size of the tables the dbspace must hold, as though the tables are reports and you are estimating the size of a spill file to hold them. "Estimating the Space Required for a Spill File" on page 76 shows an algorithm for estimating the size of a spill file.

Whatever size you choose, first search the DB2 storage pools for an existing dbspace close to the size you need. If no dbspace of convenient size already exists, use the ADD dbspace statement to create one. Instructions for adding dbspaces are provided in the DB2 Server for VSE & VM System Administration manual.

Granting a user DB2 RESOURCE authority

You need to grant DB2 RESOURCE authority to any user who needs to create tables in a public dbspace. To grant a user RESOURCE authority, issue the SQL statement shown below where userid1, userid2 and userid3 represent SQL authorization IDs.

GRANT RESOURCE TO userid1, userid2, userid3...

A user with RESOURCE authority can acquire a private dbspace for his or her own use, and create tables in a public dbspace in addition to those created in a private dbspace.

If you want to allow a user to create tables, but need to maintain control over how much resources are used, acquire a private dbspace for the user rather than granting RESOURCE authority. That way, you can control the size of the dbspace and the amount of resources used. See the DB2 Server for VSE & VM Database Administration manual for more information on acquiring a dbspace and a discussion of DB2 authority levels.

Enabling users to confirm table changes before they are made

Using the QMF Table Editor, a user can add, delete, or update information in a database table. If the value of the CONFIRM field of a user's QMF profile is YES, QMF displays a panel before making database changes. This panel asks users if they are sure they want to change the database.

To enable users to confirm their database changes, first make sure the dbspace you chose for the user is recoverable. Because changes to DB2 tables stored in nonrecoverable table spaces or dbspaces cannot be rolled back or canceled, answering NO on the Table Editor confirmation prompt panel for database changes will not prevent the changes to the table from taking place.

As end users become more comfortable changing data in the database, they may not need QMF to display these confirmation panels. You can use the following global variables to disable the panels for specific categories of actions allowed by the Table Editor:

DSQCP_TEADD for the ADD category
DSQCP-TECHG for the CHANGE category
DSQCP_TEDEL for the DELETE category
DSQC[_TEEND for the END/CANCEL category
DSQCP_TEMOD for the MODIFY category

The Table Editor loads values for these variables when it is initialized. The possible values for each variable are:

0 Disables the confirmation panel for the category
1 Enables the confirmation panel for the category
2 (the default) Either disables or enables the panel for the category depending on how the SAVE keyword of the EDIT command is set:
- When SAVE=IMMEDIATE, the confirmation panel displays
- When SAVE=END, the confirmation panel displays for the DELETE, MODIFY, and END/CANCEL categories, but does not display for the ADD and CHANGE categories.

For more information about functions provided by the QMF Table Editor, see the Using QMF manual.

[ Previous Page | Next Page | Contents | Index ]