Creating DB2 Server for VM Tables

You must have RESOURCE authority to create a table, unless you have a private DBSPACE. If you are not sure that you have RESOURCE authority, speak to your database administrator.

A DBSPACE is a portion of the database that can contain one or more tables and any associated indexes. Each table stored in DB2 Server for VM is placed in some particular DBSPACE chosen by the creator of the table. The database administrator defines DBSPACEs when the database is generated. Additional spaces can be added later using the ADD DBSPACE function. Each DBSPACE remains as an unnamed available DBSPACE until it is acquired by means of an ACQUIRE DBSPACE statement. The acquiring user gives a name to the DBSPACE and defines certain characteristics for it (or allows default characteristics).

With DB2 Server for VM, you can define new tables in the database without stopping the system or calling special utilities. You can accomplish this by using the CREATE TABLE SQL data definition statement. The table-id operand of the CREATE TABLE statement specifies the table name. As a default, your table name is prefixed with your user ID. The specifications for the table are pairs of column-names and data types with or without the qualifier NOT NULL. This qualifier tells DB2 Server for VM not to allow null values in a particular column. Any statement that later tries to put a null value in that column is rejected with an error code. The optional DBSPACE parameter lets you choose a specific database space in which to create the table.

For example, in a FORTRAN application, the following statement creates a table called NAMELIST in the DBSPACE called TEST.DBSP:
      EXEC SQL CREATE TABLE NAMELIST
     1         (FRSTNAME  CHAR(16) NOT NULL,
     2          LASTNAME  CHAR(16) NOT NULL,
     3          SERIALNO  CHAR(6) NOT NULL,
     4          AREACODE  CHAR(3),
     5          ZIPCODE   CHAR(5),
     6          PHNUMBER  CHAR(7))
     7    IN TEST.DBSP

Once you create a table, you cannot change the data types of its columns or drop a column from the table. However, you can add new rows to the table using the INSERT command. You can also add new columns to a table using the ALTER TABLE command or drop or delete a table using the DROP TABLE command. You must be the creator of the table or have database administrator authority to delete a table.