Introduction

Before the midterm we started talking about the SQL syntax used by DB2.
This was divided into three types of commands: DDL (data definition language), DML (data manipulation language), and DCL (data control language) commands.
We began talking about DDL commands before the midterm, and, in particular, had gone over the syntax for CREATE TABLE, ALTER TABLE, DROP TABLE, and CREATE INDEX.
Today, we will look at more DDL commands and then start talking about the SQL DML syntax in DB2.

Creating Storage Group

Throughout today our examples will come from a fictional bank database.

An example of the syntax to create a storage group is:

CREATE STOGROUP BANKSG VOLUMES (USER01,USER02)
VCAT UCATBANK;

VCAT is the name of the volume catalog.
We can see the effect of this on the system catalog by querying the two tables: SYSIBM.SYSSTOGROUP which will get one row for each storage group, and SYSIBM.SYSVOLUMES which will get one row for each volume of each storage group.

An example of queries against the catalog tables with look like:

SELECT * FROM SYSIBM.SYSSTOGROUP WHERE NAME = 'BANKSG'; 
SELECT * FROM SYSIBM.SYSVOLUMES WHERE SGNAME = 'BANKSG';

Creating Database

A banking example of the SQL syntax to create a database might look like:

CREATE DATABASE BANKDB 
   BUFFERPOOL BP0
   INDEXBP BP1 
   STOGROUP BANKSG 
   CCSID EBCDIC;

The SYSIBM.SYSDATABASE table contains one row for each database, except for database DSNDB01. We might query this table with a line like:
SELECT * FROM SYSIBM.SYSDATABASE WHERE NAME= 'BANKDB';

Creating Table Space

A banking example of the SQL syntax to create a tablespace might look like:

CREATE TABLESPACE BANKTS IN BANKDB
   BUFFERPOOL BP0 
   CCSID EBCDIC 
   CLOSE YES 
   COMPRESS NO 
   LOCKSIZE PAGE;

CLOSE is used to specify the order for closing open datasets when the maximum number of open datasets is reached. CLOSE YES datasets are closed before CLOSE NO ones.
The SYSIBM.SYSTABLESPACE table contains one row for each table space. We might query this table with a line like:
```
SELECT * FROM SYSIBM.SYSTABLESPACE WHERE NAME= 'BANKTS' AND DBNAME='BANKDB';
```

Designing Tables

The DDL commands we have given so far to day set up mainly the aspects of the physical structure of our bank database. We are now ready to populate our database with tables.
Once we do this, we will also want to set up constraints on these tables.
To keep things simple we will have three tables and the following diagram illustrates these tables and the constraints on them.

Branch Customer and Account tables, their primary keys and foreign key cosntraints

Creating the Branch Table

The syntax of how to create the BRANCH table looks like:

CREATE TABLE BRANCH ( 
   BRAN_ID CHAR (10) NOT NULL,
   BRAN_NAME VARCHAR (20) NOT NULL,
   BRAN_STATUS CHAR(2) NOT NULL
      WITH DEFAULT 'AC' 
) IN BANKDB.BANKTS;

Notice how we specify which database and tablespace to create the table in: BANKDB.BANKTS.
This command will affect two main tables in the catalog: SYSIBM.SYSTABLES and SYSIBM.SYSCOLUMNS
The SYSIBM.SYSTABLES table contains one row for each table, view, or alias.
The SYSIBM.SYSCOLUMNS table contains one row for every column of each table and view.

We could find information about the branch table in the catalog using:

SELECT * FROM SYSIBM.SYSTABLES WHERE NAME='BRANCH' AND CREATOR='TE02';
SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME='BRANCH' AND TBCREATOR='TE02';

Creating Index

Given our branch table we might want to add an index on the id of a branch:

CREATE UNIQUE INDEX C 
   ON BRANCH (BRAN_ID ASC)
   BUFFERPOOL BP1 
   CLOSE NO 
   DEFER YES 
   COPY YES;

DEFER says whether we want to build the contents of the index at the same time as creating it
COPY says whether the COPY utility is allowed to be used with the index.
The SYSIBM.SYSINDEXES table contains one row for every index.
This might be queried as:
SELECT * FROM SYSIBM.SYSINDEXES WHERE NAME='XBRANID1' AND CREATOR='TE02';

Adding a Primary Key

When we created BRANCH, we did not set up a primary key.

To do this we could issue an ALTER TABLE command:

ALTER TABLE BRANCH ADD CONSTRAINT PK_BRANID1
   PRIMARY KEY (BRAN_ID);

This has two main effects on the catalog: It will change SYSIBM.SYSCOLUMNS and SYSIBM.SYSINDEXES.
The SYSIBM.SYSCOLUMNS table identifies columns of a primary key in column KEYSEQ; a nonzero value indicates the place of a column in the primary key.
The SYSIBM.SYSINDEXES table identifies the primary index of a table by the value P in column UNIQUERULE.

To see this we could do the following queries:

SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = 'TE02' 
   AND TBNAME = 'BRANCH' AND KEYSEQ > 0;

SELECT * FROM SYSIBM.SYSINDEXES WHERE TBCREATOR = 'TE02' 
   AND TBNAME = 'BRANCH' AND UNIQUERULE = 'P';

Adding Check Constraints

Next we might want to add a constraint on the two character BRAN_STATUS field to say exactly which status strings are legal:
```
ALTER TABLE BRANCH ADD 
   CONSTRAINT CK_BRANST1
      CHECK (BRAN_STATUS='AC' 
      OR BRAN_STATUS='EP' );
```
This will alter two main tables in the catalog: SYSIBM.SYSCHECKS and SYSIBM.SYSCHECKDEP.
The SYSIBM.SYSCHECKS table contains one row for each check constraint.
The SYSIBM.SYSCHECKDEP table contains one row for each reference to a column in a check constraint.

We could find out information about what has change using the queries:

SELECT * FROM SYSIBM.SYSCHECKS
WHERE TBNAME='BRANCH' AND TBOWNER='TE02' AND CHECKNAME= 'CK_BRANST1';

SELECT * FROM SYSIBM.SYSCHECKDEP
WHERE TBNAME='BRANCH' AND TBOWNER='TE02' AND CHECKNAME= 'CK_BRANST1';

Creating Customer Table

Let's next look at doing creating a table and adding keys, etc. for the CUSTOMER table...

CREATE TABLE CUSTOMER ( 
   CUST_ID CHAR (10) NOT NULL,
   CUST_NAME VARCHAR (20) NOT NULL,
   CUST_BRAN_ID CHAR (10) NOT NULL 
) IN BANKDB.BANKTS;

CREATE UNIQUE INDEX XCUSTID1 
   ON CUSTOMER (CUST_ID ASC);

ALTER TABLE CUSTOMER 
   ADD CONSTRAINT
   PK_CUSTID1 PRIMARY KEY (CUST_ID);

ALTER TABLE CUSTOMER 
   ADD CONSTRAINT FK_BRANID1
   FOREIGN KEY (CUST_BRAN_ID ) 
   REFERENCES BRANCH (BRAN_ID);

Creating Account Table

Here is the same thing for the ACCOUNT table:

CREATE TABLE ACCOUNT ( 
  ACCT_ID CHAR (10) NOT NULL,
  ACCT_BAL DECIMAL (8,2) 
    WITH DEFAULT 0,
  ACCT_BRAN_ID CHAR (10) NOT NULL,
  ACCT_OPEN_TIME TIMESTAMP NOT NULL 
) IN BANKDB.BANKTS;

CREATE UNIQUE INDEX XACCTID1 ON ACCOUNT (ACCT_ID ASC);

ALTER TABLE ACCOUNT 
  ADD CONSTRAINT PK_ACCTID1
  PRIMARY KEY (ACCT_ID);

Adding Foreign Key

We have now created all of the tables for our simplified Bank database.

But we haven't created any of the foreign key constraints. To do this we could issue the commands:

ALTER TABLE ACCOUNT 
  ADD CONSTRAINT FK_CUSTID1
  FOREIGN KEY (ACCT_CUST_ID )
  REFERENCES CUSTOMER (CUST_ID);

ALTER TABLE ACCOUNT 
  ADD CONSTRAINT FK_BRANID1
  FOREIGN KEY (ACCT_BRAN_ID )
  REFERENCES BRANCH (BRAN_ID);

Foreign key constraints affect the SYSIBM.SYSRELS and SYSIBM.SYSFOREIGNKEYS tables in the catalog.
The SYSIBM.SYSRELS table contains one row for every referential constraint and each constraint is uniquely identified by the schema and name of the dependent table and the constraint name (RELNAME).
The SYSIBM.SYSFOREIGNKEYS table contains one row for every column of every foreign key including information about the columns of the foreign key that defines the constraint.

Creating Views

Often it is useful to create views of a set of tables, either for security reasons or to simplify the process of querying for data.

Here is an example view we could create on the bank database.

CREATE VIEW ACCTBAL AS 
  SELECT CUST_NAME,
         ACCT_ID, 
         ACCT_BAL, 
         BRAN_NAME
  FROM ACCOUNT A, 
       BRANCH B,
       CUSTOMER C
  WHERE A.ACCT_CUST_ID=C.CUST_ID 
        AND 
        A.ACCT_BRAN_ID=B.BRAN_ID

When you create a view it affects the catalog in several ways:
- A row is inserted into SYSIBM.SYSTABLES.
- For each column of the view, a row is inserted into SYSIBM.SYSCOLUMNS.
- A row is inserted into SYSIBM.SYSTABAUTH to record the owner's privileges on the view.
- One or more rows are inserted into the SYSIBM.SYSVIEWS table to record the text of the CREATE VIEW statement.
- For each table or view on which the view is dependent, a row is inserted into SYSIBM.SYSVIEWDEP to record the dependency.
- A row is inserted into SYSIBM.SYSVTREE, and possibly into SYSIBM.SYSVLTREE, to record the parse tree of the view (an internal representation of its logic).

Data Manipulation Language

We next start look at the syntax of DML statements in DB2...

INSERT

Single Row Insert:

INSERT INTO table_name (column1, column2, ... ) 
  VALUES (expr1, expr2, ... )

Multi-row insert:

INSERT INTO table_name (column1, column2, ... ) 
  VALUES ({expr1 | host_var_array1}, {expr2 | host_var_array2} ... ) 
  FOR {integer | host_variable} 
  ROWS [ATOMIC | NOT ATOMIC]

In an INSERT statement, you provide a value for each of the column in the table. You can only skip those columns which have default values.
In a distributed environment, you significantly reduce the network traffic by packaging multiple-rows insert into one single statement. The values for each column can be provided by a host_var_array.
The option of ATOMIC indicates that the multiple-rows insert is to be treated as a single unit of work. Say, if the insert is for 4 rows and the insert fails on the 3rd row insert, the 1st and 2nd inserted rows are to be rolled back by DB2, and the 4th row insert will not be attempted.
The option of NOT ATOMIC indicates that each row insert is a unit of work by itself. A failure of insert on the 3rd row does not rolled back the successfully inserted 1st and 2nd row, and the 4th row insert will be attempted.
After a multi-row insert, the application can use the GET DIAGNOSTICS statement to get the detailed SQLCA information from DB2.

Multi-row INSERT Example

INSERT INTO BOOKS (BOOKNO, TITLE,
AUTHOR)
VALUES (:bookno_hv, :title_hv, :author_hv)
FOR 5 ROWS

This is an example of Multi-Row insert written in C language.
In the C language, the indexing to the array starts with 0.
The contents of bookno_hv (0), title_hv (0), author_hv (0) are inserted into a row of the BOOK table.
The contents of bookno_hv (1), title_hv (1), author_hv (1) are inserted into another row of the BOOK table.
And so on..

DELETE

There are two types of delete statements to delete rows: searched and positioned.

Searched Delete:

DELETE FROM table_name 
  [WHERE search_condition]

Positioned Delete:

DELETE FROM table_name 
WHERE CURRENT OF cursor_name
[FOR ROW {integer | host_variable} OF ROWSET]

A searched delete statement operates on the set level. However many rows satisfy the search_condition are deleted as a whole.
A positioned delete statement works with a non read-only cursor statement. You first open the cursor and then fetch to position a cursor at a certain row. You then execute the "DELETE FROM ...WHERE CURRENT OF cursor_name". The row where the cursor is positioned at is deleted.

UPDATE

Update statements are used to modify existing rows of a table. They too come in search and positioned variants

Searched Update:

UPDATE table_name SET assignment1, assignment2, ... 
[WHERE search_condition]

Positioned Update:

UPDATE table_name 
SET assignment1, assignment2, ... 
WHERE CURRENT OF cursor_name 
[FOR ROW {integer | host_variable} OF ROWSET] 

assignment:
    column_name = expression | NULL | scalar_fullselect

SQL

Outline