DB2 data types

DATE:

The ISO format date in DB2 is CCYY-MM-DD in the range of 0001-01-01 to 9999-12-31.

COBOL host variable equivalent: PIC X(10)

DB2 supports ISO data format CCYY-MM-DD.

Example:

CREATE TABLE reading_lists(
     user_id INT NOT NULL,
    book_id INT NOT NULL,
    added_on DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY(user_id, book_id)

);

INSERT INTO reading_lists(user_id, book_id) VALUES(1,1);

Converting COBOL field data to DB2 host variable format:

Example: 20200514

01 WS-DATE.

   05 WS-YEAR     PIC X(04).

   05 FILLER           PIC X VALUE ‘-‘.     

   05 WS-MONTH     PIC X(02).

   05 FILLER           PIC X VALUE ‘-‘.

   05 WS-DAY        PIC X(02).

   05 FILLER           PIC X VALUE ‘-‘.

MOVE 2020 TO WS-YEAR
MOVE 05     TO WS-MONTH
MOVE 14     TO WS-DAY

MOVE WS-DATE TO WS-HOST-VARIABLE

When date is 0, we need to insert NULL value in date field. Then please code like below

IF WS-DATE = ZEROS
    MOVE ‘0001-01-01’    TO WS-HOST-VARIABLE  => Moving null

TIME:

The ISO format time in DB2 is HH.MM.SS in the range of 00.00.00 to 24.00.00

Example:

CREATE TABLE daily_routines(
    routine_id INT
    GENERATED BY DEFAULT AS IDENTITY 
    NOT NULL PRIMARY KEY,
    routine VARCHAR(100) NOT NULL,
    start_at TIME NOT NULL
);

INSERT INTO daily_routines(routine, start_at) VALUES
    ('Get up','06:00'),
    ('Brush your teeth','06:05'),
    ('Have breakfast','06:15'),
    ('Go to school','06:45'),
    ('Go home','17:00');

COBOL host variable equivalent: PIC X(8).

01 WS-TIME.
   05 WS-HOURS         PIC X(02).
   05 FILLER           PIC X VALUE ‘.‘.     
   05 WS-MINUTES       PIC X(02).
   05 FILLER           PIC X VALUE ‘.‘.
   05 WS-SECONDS        PIC X(02).
   05 FILLER           PIC X VALUE ‘.‘.

Converting COBOL field data to DB2 host variable format:

After moving time to ws-time, then

MOVE WS-DATE TO HOST-VARIABLE

TIMESTAMP:

The ISO format time in DB2 is HH.MM.SS in the range of 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000

Example:

CREATE TABLE logs(
    log_id INT GENERATED ALWAYS AS IDENTITY NOT NULL,
    message VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(log_id)
);


INSERT INTO logs(message)
VALUES('Testing timestamp');

COBOL host variable equivalent: PIC X(26).

VARCHAR:

This data type in db2 is used to store VARIABLE-LENGTH character strings.

Syntax:  column-name VARCHAR(n)

'n' is positive integer which represents maximum length of bytes a column can store. It should be greater than 0 and less than 32740.

Example:

CREATE TABLE db2_varchars (
     v VARCHAR(20) NOT NULL
);

INSERT INTO db2_varchars (v) VALUES ('Db2 Tutorial');

Converting COBOL field data to DB2 host variable format:

VARCHAR field representation in COBOL (provided by DCLGEN) is like below (COBOL host variable equivalent)

01 column-name.
    49 column-len                  pic S9(4) comp.
    49 column-text                 pic x(n).

How to move data to a varchar field in DB2 from COBOL:

Rules: 

o   Remove padded spaces from the text

o   Populate the actual length of the text

Example:

77  WS-INDI             PIC X(7).
77  WS-INDCTR           PIC S9(4) COMP.
77  WS-ACT-INDI         PIC X(7).

MOVE 'A Z H' TO WS-INDI
INSPECT FUNCTION REVERSE(WS-INDI) TALLYING WS-INDCTR
                 FOR LEADING SPACES  => COUNT THE LEADING SPACES 
DISPLAY 'WS-INDCTR1: ' WS-INDCTR                        
SUBTRACT WS-INDCTR FROM 7 GIVING WS-INDCTR => SUBTRACTING LEADING SPACES FROM THE TOTAL LENGTH AND STORE THE ACTUAL LENGTH (ACTUAL LENGTH-LEADING SPACES)
DISPLAY 'WS-INDCTR2: ' WS-INDCTR        
MOVE WS-INDI(1:WS-INDCTR) TO WS-ACT-INDI => MOVING ACTUAL VALUE WITHOUT LEADING SPACES TO HOST-VARIABLE            
DISPLAY 'WS-ACT-INDI: ' WS-ACT-IND

Output:

WS-INDCTR1: +0002

WS-INDCTR2: +0005

WS-ACT-INDI: A Z H

SMALLINT:

To store small integers in 2 bytes.

Syntax: col-name SMALLINT

COBOL host variable equivalent: S9(4) COMP.

INT:

To store large integers in 4 bytes.

Syntax: col-name INT

COBOL host variable equivalent: S9(9) COMP.

BIGINT:

To store big integers in 8 bytes.

Syntax: col-name INT

COBOL host variable equivalent: S9(18) COMP.

Example:

CREATE TABLE db2_integers( 
             smallint_col SMALLINT, 
             int_col INT, 
             bigint_col BIGINT );

db2_integers represents table-name

INSERT INTO db2_integers (
    smallint_col, 
    int_col, 
    bigint_col )
VALUES (
    32767,
    2147483647,
    9223372036854775807
);

CHAR:

To store FIXED-LENGTH character strings in database.

Syntax: col-name CHAR(n)

COBOL host variable equivalent: PIC X(n)

Example:

CREATE TABLE db2_characters(
    char_col CHAR(3)
);

INSERT INTO db2_characters(char_col)VALUES('abc');

DECIMAL:

To store decimal type values in database.

The decimal number consists of two parts viz. the whole part and fractional part. They both are separated by comma.

Syntax: dec_col DECIMAL(m,n) or
        dec_col NUMERIC(m,n)

‘m’ represents whole part, ‘n’ represents fractional part.

ex. DECIMAL(5,3) => 12.345

Example:

CREATE TABLE db2_decimals(
    dec_col NUMERIC(5,3)
);

INSERT INTO db2_decimals(dec_col)
VALUES(12.345);

COBOL host variable equivalent: S9(m-n)V(n) COMP-3

DB2 LOAD error : INPUT FIELD 'field-name' NOT ENTIRELY WITHIN INPUT RECORD

Whenever you get an error 'INPUT FIELD 'field-name' NOT ENTIRELY WITHIN INPUT RECORD' while LOADing the data to a DB2 table,

Please check whether the length of the field-name in DB2 table is equal to the length of the corresponding field-name in LOAD file. The length of both the fields SHOULD match. 

SYNCPOINT

SYNCPOINT divides a long-running task into smaller units of work. 

It specifies that all  the changes made by the task since its last syncpoint are to be committed. 

SYNCPOINT ROLLBACK

It specifies that all the changes made to recoverable resources by the task since its last syncpoint are to be backed out.

In order to reset all CICS recoverable resources because the transaction is not finished, we back out the current unit of work (UOW) by performing the following command

EXEC CICS SYNCPOINT ROLLBACK
END-EXEC

WHENEVER Clause

WHENEVER specifies the host language statement to be executed when an exception condition occurs.

Three types of WHENEVER :-

1.WHENEVER NOT FOUND

Example:

go to the label ENDDATA for any statement does not return

EXEC SQL
     WHENEVER NOT FOUND
        GO TO ENDDATA
END-EXEC.

NOT FOUND => Identifies any condition that results in an SQLCODE of +100.

2.WHENEVER SQLERROR

Go to label handler for any statement that produces an error

EXEC SQL
     WHENEVER SQLERROR
     GO TO HANDLER
END-EXEC.

SQLERROR => Identifies any condition that results in negative SQLCODE.

3.WHENEVER SQLWARNING 

Continue the processing when any statement produces a warning

EXEC SQL
     WHENEVER SQLWARNING
     CONTINUE
END-EXEC.

SQLWARNING => Identifies any condition that results in warning condition (SQLWARN0 is W) or that results in Positive SQLCODE other than +100.

QMF ( Query Management Facility)

QMF is an MVS based query tool which allows end users to enter SQL queries to produce a variety of reports and graphs as a result of the query. 

In other words, this tool is used to issue SQL commands against DB2 database. The data results can be formatted as reports, charts etc. 

There are three different ways you can supply input to QMF viz., 

1. Entering SQL in Query Editor
2. By using prompted queries
3. Query by example (QBE)

DB2 Isolation levels

UR (Uncommitted Read) : 
  
  - For read only queries. 
  - No record locking. 
  - Good for accessing read only tables.

CS (Cursor Stability) :

- Default isolation level. 
- It locks and unlocks each row , 1 at a time. 
- Guaranteed to only return the data which was committed at the time of read

RS (Read Stability):

- Keeps all the qualifying rows locked until the transaction is complete.
- Releases the lock on those rows which doesn't satisfy the query

RR (Repeatable Read):

- Not just the rows that satisfy the query, but keeps the entire table locked until  the unit   of work is done.
- No other application program can update, insert or delete the row from the  table.

Database basics

Relational database - A database structured to recognize relations between stored items of information. 

Data redundancy in database means that some data fields are repeated in the database.This data repetition may occur either if a field is repeated in two or more tables or if the field is repeated within the table.Data can appear multiple times in a database for a variety of reasons. For example, a shop may have the same customer’s name appearing several times if that customer has bought several different products at different dates.

Clustered index determines the physical order of data in a table. Only one clustered index is possible per table. But one clustered index can have multiple columns. The data in the table is arranged based on the clustered index column. 

Non-clustered index is like an index in the text book where the data is stored in one place and index is stored in another place.Since the actual data is stored 
separately, one table can have multiple non-clustered indexes just like a text book will have an index for chapters and an index for common words.

The sharing of Resources by multiple users or application programs at the same time is called CONCURRENCY.


Differences between Primary Key and Foreign key:

Primary key uniquely identify a record in the table.
Foreign key is a field in the table that is primary key in another table.

Primary Key can't accept null values.
Foreign key can accept multiple null value.

By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.

We can have only one Primary key in a table.
We can have more than one foreign key in a table.

DB2 Error codes

SQLCODE Overview

If SQLCODE = 0, execution was successful.

If SQLCODE > 0, execution was successful with a warning.

If SQLCODE < 0, execution was not successful.

If SQLCODE = 100, "no data" was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.

New with DB2 V8, when DB2 processes a multiple row FETCH statement, the contents of SQLCODE is set to +100 if the last row in the table has been returned with the set of rows.

SQLCODE - Successful SQL Execution

SQL Return Code +100 ROW NOT FOUND FOR FETCH, UPDATE OR DELETE, OR THE RESULT OF A QUERY IS AN EMPTY TABLE.

Suggestion: If expecting data, verify WHERE clause for accuracy and completeness.

SQL Return Code +117 THE NUMBER OF INSERT VALUES IS NOT THE SAME AS THE NUMBER OF OBJECT COLUMNS.

Suggestion: Correct SQL statement to provide only one value for each column in the table.

SQL Return Code +231 CURRENT POSITION OF CURSOR cursor-name IS NOT VALID FOR FETCH OF THE CURRENT ROW.

Suggestion: Be certain to FETCH to position on a row after opening a cursor. If cursor is declared SENSITIVE STATIC SCROLL, the row may be a hole, from which no values can be fetched.

SQL Return Code +304 A VALUE WITH DATA TYPE data-type1 CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE THE VALUE IS NOT WITHIN THE RANGE OF THE HOST VARIABLE IN POSITION position-number WITH DATA TYPE data-type2.

Suggestion: Verify DCLGEN host variable definitions are current with DB2 catalog table/view attributes.

SQL Return Code +347 THE RECURSIVE COMMON TABLE EXPRESSION name MAY CONTAIN AN INFINITE LOOP.

Suggestion: Verify predicate in the SQL WHERE clause of the form "counter_col < constant" or "counter_col < :hostvar".

SQL Return Code +802 EXCEPTION ERROR exception-type HAS OCCURRED DURING operation-type OPERATION ON data-type DATA, POSITION position-number.

Suggestion: Check arithmetic operation for divide by zero or result to exceed size of host variable.

SQLCODE - Unsuccessful SQL Execution

SQL Error Code -117 THE NUMBER OF VALUES ASSIGNED IS NOT THE SAME AS THE NUMBER 

OF SPECIFIED OR IMPLIED COLUMNS.

Suggestion: Provide one value for each column in the table.

SQL Error Code -150 THE OBJECT OF THE INSERT, DELETE, OR UPDATE STATEMENT IS A VIEW, SYSTEM-MAINTAINED MATERIALIZED QUERY TABLE, OR TRANSITION TABLE FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED.

Suggestion: Be certain to specify base DB2 table/view names for INSERT statements.

SQL Error Code -180 THE DATE, TIME OR TIMESTAMP VALUE value IS INVALID.

Suggestion: Verify the data value is in the correct range and value type.

SQL Error Code -181 THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE.

Suggestion: Verify data format with the SQL Reference Guide.

SQL Error Code -204 name IS AN UNDEFINED NAME.

Suggestion: Correct DB2 CREATOR or OBJECT NAMEs located in SQL statements.

SQL Error Code -227 FETCH fetch-orientation IS NOT ALLOWED, BECAUSE CURSOR cursor-name HAS AN UNKNOWN POSITION (sqlcode,sqlstate).

Suggestion: CLOSE and re-OPEN the cursor; For scrollable use (FIRST, LAST, BEFORE, AFTER, or ABSOLUTE) to establish valid position.

SQL Error Code -305 THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE NO INDICATOR VARIABLE IS SPECIFIED.

Suggestion: Add null indicator variable to SELECT statement in the format of "column:hostvarind".

SQL Error Code -501 THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN.

Suggestion: Correct logic in application program to OPEN the cursor before the FETCH or CLOSE statement.

SQL Error Code -502 THE CURSOR IDENTIFIED IN AN OPEN STATEMENT IS ALREADY OPEN.

Suggestion: Correct logic in application program to CLOSE the CURSOR before the OPEN statement.

SQL Error Code -503 A COLUMN CANNOT BE UPDATED BECAUSE IT IS NOT IDENTIFIED IN THE UPDATE CLAUSE OF THE SELECT STATEMENT OF THE CURSOR.

Suggestion: Use FOR UPDATE statement in your cursor.

SQL Error Code -530 THE INSERT OR UPDATE VALUE OF FOREIGN KEY constraint-name IS INVALID.
Suggestion: Ensure that INSERT row for DB2 PARENT table is completed before INSERT row in CHILD table.

SQL Error Code -532 THE RELATIONSHIP constraint-name RESTRICTS THE DELETION OF ROW WITH RID X'rid-number'.

Suggestion: Change the program to DELETE CHILD table row before DELETE of row on PARENT table.

SQL Error Code -551 auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object-name.

Suggestion: Contact the support DBA to GRANT the needed privilege.

SQL Error Code -803 AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN INDEX SPACE indexspace-name CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS Xrid.

Suggestion: Verify DB2 INDEX and, if needed, change the statement to an UPDATE.

SQL Error Code -805 DBRM OR PACKAGE NAME location-name.collection-id.dbrm-name.consistency-token NOT FOUND IN PLAN plan-name. REASON reason.

Suggestion: Ensure COLLECTION name is in DB2 PLAN. Recompile and BIND the DB2 program. Verify correct LOAD library is being used.

SQL Error Code -811 THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE.

Suggestion: -811 is often detected after program check for DB2 data existence. Consider using new DB2 V8 FETCH FIRST ROW ONLY feature instead.

SQL Error Code -818 THE PRECOMPILER-GENERATED TIMESTAMP x IN THE LOAD MODULE IS DIFFERENT FROM THE BIND TIMESTAMP y BUILT FROM THE DBRM z.
Suggestion: Recompile and BIND the DB2 program. Verify correct LOAD library is being used.

SQL Error Code -904 UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

Suggestion: -904 is usually caused because a database utility job has started the desired DB2 object in utility mode. Check DB2 Master Log for more details on the resource name – contact DBA.

SQL Error Code -911 THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

Suggestion: Review DB2 Master Log to find process holding DB2 locks. Consider adding additional COMMITs to program holding the DB2 resource.

SQL Error Code -913 UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

Suggestion: Review DB2 Master Log to find process holding DB2 locks. Consider adding additional COMMITs to program holding the DB2 resource.

SQL Error Code -922 AUTHORIZATION FAILURE: error-type ERROR. REASON reason-code.

Suggestion: Connection to DB2 has failed due authority for USER or PLAN. Contact DBA to check DB2 authorizations.

SQL Error Code -927 THE LANGUAGE INTERFACE (LI) WAS CALLED WHEN THE CONNECTING ENVIRONMENT WAS NOT ESTABLISHED. THE PROGRAM SHOULD BE INVOKED UNDER THE DSN COMMAND.

NULL Indicator Variable

The Null indicator variable are used in an Application Program as a part of exception handling while dealing with the columns which are defined as NULL ( Or say the one which is not defined with NOT NULL).

Let us consider the following scenario:

EXEC SQL
  SELECT EMP_NUMBER,EMP_ADDRESS
  INTO :EMP_NUMBER,:EMP_ADDRESS
  FROM EMP
END-EXEC.

Let's say EMP_ADDRESS column in EMP tables is defined as NULL column and one of the Employee has not updated his address in database so it will be considered as null. Now what should happen if we try to fetch the row for that employee, what the host variable: EMP_ADDRESS suppose to contain after the Select operation. No it will not contain spaces as Null is absence of value and not blanks, spaces or any other character.

In this case you will get the error with -305 as SQLCODE.

To avoid this we need to use null indicator in our program which can be used to process the program flow in case of null.

The Null indicator variable should be defined for the every column which may contain the null value.

It must be defined as 2 Byte binary variable.

01   EMP_ADDRESS_INDICATOR S9(4) COMP

You can then refer this variable in your query as below.

EXEC SQL
  SELECT EMP_NUMBER,EMP_ADDRESS
  INTO :EMP_NUMBER,:EMP_ADDRESS :EMP_ADDRESS_INDICATOR
  FROM EMP
END-EXEC.

Note there is no comma between EMP_ADDRESS and EMP_ADDRESS_INDICATOR.

Now see how this null indicator resolves our problem.

If one of the employees have EMP_ADDRESS as NULL then EMP_ADDRESS_INDICATOR will be automatically updated with Negative value.

Then you can check like

IF EMP_ADDRESS_INDICATOR IS NOT ZERO
THEN < do some processing>

EMP_ADDRESS_INDICATOR will contain zero if the EMP_ADDRESS has some value (NOT NULL)

Null Indicators hold one of the following values

 0: Field value is not null
-1: Field value is null 
-2: Field value is truncated

DB2 Cursors

A CURSOR is mainly used to retrieve more than one row from a table.

Steps to use the cursor in COBOL-DB2 program:-

Declare - Declares the cursor name with the SELECT query

Syntax:

DECLARE cursor-name CURSOR [WITH HOLD]
  [WITH RETURN [TO CALLER | TO CLIENT]] 

FOR SELECT-STATEMENT

FOR UPDATE OF(column-names) -->Optional

Example:

       EXEC SQL

               DECLARE CSR1 CURSOR FOR

SELECT DEPNO, DEPNAME, MGRNO

FROM   DEP

WHERE  ADMRDEP = :ADMRDEP

FOR UPDATE OF --->Optional

    DEPNAME

   ,MGRNO          

END-EXEC.

Cursor WITHOUT HOLD - The cursor will be closed if there is any COMMIT operation in the program. This is the default option.

Cursor WITH HOLD - The cursor will still remain open even though there is a commit operation in the program. In other words if there is a commit in the program, the cursor will get closed. So, to avoid this situation we use WITH HOLD option. 

WITH HOLD option is mainly used when there is large amount of data to be processed and stored procedure wanted to save the work that was done to this point of time.

WITH RETURN is optional, which specifies the cursor will be returned to calling program (TO CALLER) or directly to the client (TO CLIENT)

FOR UPDATE OF - To delete the entire row

WHERE CURRENT OF - Used in the UPDATE statement to delete/update the last row fetched.

EXEC SQL
 UPDATE EMP
 SET EMP_NAME=:EMPNAME
 WHERE CURRENT OF EMP_CURSOR
END-EXEC

EXEC SQL
 DELETE  FROM EMP
 WHERE CURRENT OF EMP_CURSOR
END-EXEC.

Open - executes the sql query and stores corresponding rows in temporary result table

Syntax:

OPEN cursor-name

Example:

    

       EXEC SQL

              OPEN CSR1

       END-EXEC.

Fetch - Fetches each row from the temporary result table and moves the data to host variables for further processing

Syntax:

        FETCH cursor-name

     INTO  :host-variable-1,

           :host-variable-2,

                       .

                       .

                       .

           :host-variable-n

Example:

      EXEC SQL

          FETCH  CSR1

INTO :DEPNO,

:DEPNAME,

:MGRNO

       END-EXEC.

Close - closes the cursor. Releases all the resources used by the cursor.

Syntax:

     CLOSE cursor-name

Example:

     

       EXEC SQL

         CLOSE CSR1

    END-EXEC.

DB2 Tutorial - 1

Storage Group(STOGROUP) - A uniquely named collection of DASD ( Direct Access Storage Device ) volumes used to place the VSAM files containing the data.
- The tables and its indexes are actually stored in these VSAM files.
- One STOGROUP can have a maximum of 133 volumes.
- Storage group details will be stored in SYSIBM.SYSSTOGROUP table 

Syntax: 

CREATE STOGROUP storage-group-name

VOLUMES (Volume-1,volume-2…)

VCAT vcat-name

VCAT identifies the system integrated catalog facility for the storage group

Database - It is collection of information that is organized so that it can easily be accessed, managed and updated.
Database details can be stored in SYSIBM.SYSDATABASE table.

Create Database: To define the database

CREATE DATABASE database-name

STOGROUP storage-group-name

BUFFERPOOL buffer-pool-name

INDEXBP index-bp-name

INDEXBP is the bufferpool to place indexes.

Tablespace  - A VSAM file which stores the DB2 table data physically. Just like contents in the book, the data in this file is stored in fixed size pages.

One table space can contain one or more tables

One table space can be stored in more than one VSAM file.

Tablespaces are categorized into 3 types, they are

Simple – Table space is represented as pages and data will be stored in the form of pages.

Segmented – Tables space is represented as segments and data will be stored in the form of segments. Segment is nothing but set of pages.

Partitioned – Table space is represented as partitions and each partition can have only one table.

Syntax:

CREATE TABLESPACE tablespace-name

IN database-name

USING STOGROUP storage-group-name

Bufferpool - An area in the main storage where db2 stores pages fetched from tablespace. 

Bufferpool in detail - 

It is portion of a main memory space which is allocated by the database manager. The purpose of bufferpools is to cache table and index data from disk.

All databases have their own bufferpools. A default bufferpool is created at the time of creation of new database. 
Bufferpool can be multiples of 4k like 4k,8k,16k etc.

Table - A table is a data structure that organizes information into rows and columns. It can be used to both store and display data in the structured format.

- SYSADM and SYSCTRL authority is required to create a table. If the user id don't have this authority, then -551 error will be returned while creating CREATE TABLE.

Syntax:

CREATE TABLE table-name

( column1       datatype [not][null],

  Column2       datatype [not][null],

  Column3       datatype [not][null],

   .

   .

   .

 PRIMARY KEY(column-name))

 IN DATABASENAME.TABLESPACENAME

View is a table which can be derived from one or more tables based on selection criteria.
A maximum of 15 tables can be used to create a view.
A view can be created on single table or more than one table.

Syntax:

CREATE VIEW view-name AS

       (Select query)

ON DATABASENAME.TABLESPACENAME

Index is a key which is used to directly access a particular row.
INDEXSPACE is used to store indexes.
When the index is created, indexspace is created automatically.

Syntax:

CREATE UNIQUE INDEX index-name

ON table-name column-name

DDL Commands

CREATE

ALTER

DROP

ALTER is used to add/delete/modify any db2 object created by using CREATE

ALTER syntax:

ALTER TABLE table-name

ADD column-name declaration;

ALTER can be performed on any DB2 object

ALTER DB2-object-name

[Parameters newly added/modified]

DROP is used to drop the db2 objects created

Syntax:

DROP db2-object-name.

Ex. DROP TABLE table-name

DML Commands

SELECT

INSERT

DELETE

UPDATE

SELECT: To retrieve the data from one or more tables

SELECT * from table-name

INSERT: To insert the data into the table

INSERT INTO table-name(column-1, column-2..) VALUES(hostvar1,hostvar2..)

DELETE: To delete one or more rows from table

DELETE FROM table-name

WHERE conditions;

UPDATE: To update one or more rows from a table

UPDATE table-name

SET column-1 = value1,

    Column-2 = value2,

    .

    .

WHERE conditions

TCL – Transaction control language is used to control the transactions performed on the database.

COMMIT – Used to save all the transactions performed on the database

EXEC SQL

     COMMIT

END-EXEC

ROLLBACK – Used to revert back all the transactions performed on the database

EXEC SQL

     ROLLBACK

END-EXEC

DCL (Data control language) is used the control the data by giving/revoking the access for accessing the database based on the levels of users.

GRANT is used to grant the permissions to the user and also to add the additional permissions

Syntax: GRANT [statement] ON [db object] TO PUBLIC/group-of-users

Example: GRANT SELECT ON TABLE1 TO PUBLIC

REVOKE is used revert back the access which was granted earlier.

REVOKE SELECT TABLE1 ON PUBLIC

JOIN:

SELECT * FROM table1-name JOIN table2-name

INNER JOIN:

The similar values in both the tables will be displayed. For eg, i have emp table and dept table where emp_id is a common attribute in both.

In emp table, emp_id values are 101,102,103,104,105 and in dept table emp_id values are 101, 102, 103 and 104. So when you INNER join both the tables

using emp_id then four rows will be displayed with emp_id 101,102, 103 and 104 as they are common in both the tables.

SELECT col1,col2

FROM table1

INNER JOIN table2

ON joining cols

WHERE conditions

OUTER JOIN will join all the columns from left table and right table based on the conditions.

SELECT A-col-list, B-col-list FROM table-A OUTER JOIN table-B

WHERE condition

ON joining cols

Right outer join:

Displays all rows from the right table(table2) with the matching rows from the left table(table1). 

SELECT A-col-list, B-col-list FROM table-A 
RIGHT OUTER JOIN 
table-B

ON joining cols

WHERE condition

Left outer join:

Displays all rows from the left table (table1) and selected rows from the right table (table2)

SELECT cols-list FROM table-A WHERE condition LEFT 

OUTER JOIN

SELECT cols-list FROM table-b WHERE condition on Joining-cols