9.5 Defining schemas, tables, and indexes

You use definition SQL statements to define schemas, tables, and indexes.

Example:

In this example, the schema of HADB user ADBUSER02 is defined and the following tables and indexes are defined:

Shop table (SHOPSLIST)
B-tree index (SHOP_CODE_IDX)
Text index (ADDRESS_TIDX)
Range index (RGN_CODE_RIDX)

ADBUSER02 is the authorization identifier of the HADB user created in 9.4.1 Creating an HADB user.

Procedure:

Execute the adbsql command.

adbsql
  USER-ID ?
ADBUSER02       Connects to the HADB server as ADBUSER02. 
  PASSWORD ?
XXXXXXXX        The entered password is not displayed.

Execute the CREATE SCHEMA statement to define a schema.
```
CREATE SCHEMA "ADBUSER02"
```
Executing the above SQL statement defines the schema of ADBUSER02.

Execute the CREATE TABLE statement to define the shop table (SHOPSLIST).

CREATE TABLE "SHOPSLIST"
      ("SHOP_CODE" CHAR(8) NOT NULL,
       "RGN_CODE" CHAR(6) NOT NULL,
       "SHOP_NAME" VARCHAR(20) NOT NULL,
       "TEL_NO" CHAR(10) NOT NULL,
       "ADDRESS" VARCHAR(300) NOT NULL BRANCH YES)
    IN "DBAREA01"
    PCTFREE=40
    CHUNK=100

Execute the CREATE INDEX statement to define a B-tree index (SHOP_CODE_IDX).
```
CREATE INDEX "SHOP_CODE_IDX"
      ON "SHOPSLIST" ("SHOP_CODE")
    IN "DBAREA01"
    PCTFREE = 50
    EMPTY
```
Executing the above SQL statement defines a B-tree index (SHOP_CODE_IDX) for the shop code column (SHOP_CODE) of the SHOPSLIST table.
Execute the CREATE INDEX statement to define a text index (ADDRESS_TIDX).
```
CREATE INDEX "ADDRESS_TIDX"
      ON "SHOPSLIST" ("ADDRESS")
    IN "DBAREA01"
    PCTFREE = 50
    EMPTY
    INDEXTYPE TEXT
```
Executing this SQL statement defines a text index (ADDRESS_TIDX) for the address column (ADDRESS) of the SHOPSLIST table.
Execute the CREATE INDEX statement to define a range index (RGN_CODE_RIDX).
```
CREATE INDEX "RGN_CODE_RIDX"
      ON "SHOPSLIST" ("RGN_CODE")
    IN "DBAREA01"
    EMPTY
    INDEXTYPE RANGE
```
Executing the above SQL statement defines a range index (RGN_CODE_RIDX) for the shop region code column (RGN_CODE) of the SHOPSLIST table.

Note: When you use the adbsql command to execute an SQL statement, make sure that a semicolon (;) is the last character you enter.

To Page Top