DB2 SQL BASIC — 1

Structured Query Language aka SQL is the industry standard to talk to Relation database management systems (RDBMS). RDBMS and SQL are like an alias to each other. To perform any action on RDBMS you need SQL.

All the basic concepts of SQL are the same for every RDBMS like SQLServer, DB2, MYSQL, Oracle, etc. But every database provides some special SQL, specifically for that database. Here will talk only for DB2/400.

Let’s Get to Point.

SQL statements are categorized as follows:

1. Data Control Language (DCL) statements: To handle data security. For example, GRANT and REVOKE statements.
2. Data Definition Language (DDL) statements: To work with SQL objects like Database, Table, Index, etc. CREATE, DROP and ALTER these kinds of statements come under this category.
3. Data Manipulation Language (DML) statements: To access and modify data. SELECT, INSERT, DELETE, and UPDATE come under this category.

So here is the image :

We will discuss mostly Data Definition Language (DDL) statements and Data Manipulation Language (DML) statements. As an IBM i developer, you are going to use DDL and DML in most cases.

DCL is about SQL object authority. So don’t worry about its use, WRKOBJAUT or DBA will take care of it.

In the next post, we will talk about Data Definition Language (DDL) statements.

CREATE TABLE

DB2 SQL provides 3 ways to use CREATE TABLE Statement.

1. Basic CREATE TABLE

CREATE TABLE STUDENT    
   (STUDENTID NUMERIC NOT NULL,
    CLASS VARCHAR(36) NOT NULL,
    TEACHERID NUMERIC ,        
    LOCATION CHAR(16) ,        
    PRIMARY KEY(STUDENTID) )

There are a lot more options available. Check This link for more details.

2. Use LIKE To Duplicate a Table’s Schema

Use the LIKE clause to create a table with the same columns as another table or view. The following SQL creates a new table NEW_PROJECT using the PROJECT table as a template:

CREATE TABLE NEW_PROJECT LIKE PROJECT;

The LIKE clause is handy in the following instances:
• When creating exception tables required by the CHECK utility
• When multiple instances of a similar table must be created
• When creating a PLAN_TABLE
• When creating the same table for multiple users

3. Use a Fullselect to Define a Table’s Schema (Using AS clause)

You can also use the AS clause to create a table based on a SELECT statement. Use the AS clause to provide names for unnamed elements, such as functions or expressions.

For example, the following SQL creates a new table with columns based on the results of joining the EMP table and the DEPT table:

CREATE TABLE EMP_WITH_DEPT AS
   (SELECT EMPNO, FIRSTNME, LASTNAME, PHONENO,
            SALARY+COMM+BONUS AS TOTAL_COMP,   
     FROM  EMP  E,  DEPT D  WHERE E.WORKDEPT = D.DEPTNO) 
     WITH DATA/WITH NO DATA;

Short and Long Column name

When we create DDS for a PF, we can create fields/columns with a maximum of 10 length names. Technically most of IBM I (like RPG or CL) work with “10 length” column names only. But SQL CREATE TABLE statement allows a much bigger length for column names. To support this “10 length,” standard SQL assigns a system-generated SHORT(10 length) name for every long column name. For example column, CUSTOMER_NAME will be assigned a short name like CUS0000001.

Actually, the SQL CREATE TABLE statement gives the option to define both Long name and Short name.

CREATE TABLE TABLE_NAME( COLUMN_LONG_NAME For Column COLUMN_SHORT_NAME COLUMN_DATA_TYPE )

• “FOR” or “For COLUMN” give the option to define a short name for the column

CREATE TABLE CUSTOMER( Customer_Name For Column CusName CHAR(10) )

ALTER TABLE

SQL ALTER TABLE statement gives options to change almost everything in TABLE structure. Like Add, Drop, or Change definition of column(s).

1. The altered table may consist of up to 8000 columns.

Adding a column

ALTER TABLE STUDENT ADD COLUMN CITY CHAR(10)

DROP a column

ALTER TABLE DEPT DROP COLUMN NUMDEPT

Changing a column

When you change the data type of an existing column, the old and new attributes must be compatible.

ALTER TABLE EX1 ALTER COLUMN COL2 SET DATA TYPE VARCHAR(30)

Multiple Actions in one statement

ALTER TABLE STUDENT
 ALTER COLUMN CITY SET DATA TYPE VARCHAR(100)
 ADD COLUMN STATE CHAR(2) 
 DROP COLUMN OLD_CITY ;

DROP TABLE

DROP TABLE statement is effortless and straightforward.

DROP TABLE <table_name>

The following statement drops the table called DEPARTMENT:

DROP TABLE DEPARTMENT

Please check this link for all about DROP TABLE