DB2 SQL BASIC — 2 DML

3 min readJul 21, 2021

SELECT Statement

SQL SELECT Statement is a form of SQL QUERY which gives access to a result set(Temporary table). We can use SQL SELECT to look inside the table or create a new table based on the result set of queries.

There are 3 components of SQL SELECT:

SubSelect
FullSelect
Select-Statement

SubSelect

SubSelect is most basic form of SQL SELECT. Just simple select statement with minimum options.Here is how IBM describes SubSelect:>>-select-clause--from-clause--+--------------+----------------->
                               '-where-clause-'   

>--+-----------------+--+---------------+----------------------->
   '-group-by-clause-'  '-having-clause-'

This is a how a SubSelect looks like:

select [all|distinct] expression {, expression}
from tablename [corr_name] {, tablename [corr_name]}
[where search_condition]
[group by column {, column}]
[having search_condition]

FullSelect

FullSelect = SubSelect + SET OPERATION(If a set operator is not used, the result of the Fullselect is the result of the specified Subselect.)

UNION
EXCEPT
INTERSECT

2. A FullSelect does not allow any of the following clauses:

FOR READING ONLY
FOR FETCH ONLY
FOR UPDATE OF
OPTIMIZE FOR
WITH (for CTE)
QUERYNO

This is how a FullSelect looks like:

Subselect
{union [all] Subselect}
[order by result_column [asc|desc] 
          {, result_column [asc|desc]}]

Select-Statement

Select-Statement is the highest level. It is like
“ FullSelect + Every_Thing_Else_That_is_Not_ALLOWED_in_FullSelect “.

>>-+-----------------------------------+--fullselect--●--------->
   |       .-,-----------------------. |                  
   |       V                         | |                  
   '-WITH----common-table-expression-+-'                  

>--+------------------+--●--+---------------------+--●---------->
   +-read-only-clause-+     '-optimize-for-clause-'      
   '-update-clause----'                                  

>--+------------------+--●-------------------------------------->
   '-isolation-clause-'      

>--+-------------------------------------+--●------------------><
   '-concurrent-access-resolution-clause-'

Here is how we can look into SQL SELECT:

INSERT

SQL provides the INSERT statement to append new data in DB Table. There are multiple ways to use SQL INSERT:

Inserting rows using the VALUES clause( insert 1 or More rows) :

INSERT INTO table-name (column1, column2) VALUES (value1, value2)

INSERT into abc (x , y, z) values(1,2,3) ,(4,5,6); ==> will insert 2 rows

Inserting rows using a select-statement ( insert 0 or More) :

INSERT INTO EMPDETAIL (EMPNUMBER, PROJNUMBER, STARTDATE, ENDDATE)
SELECT EMPNO, PROJNO, EMSTDATE, EMENDATE FROM EMPPROJACT

Inserting multiple rows using the blocked INSERT statement ( insert multiple rows) :

INSERT INTO EMPDETAIL (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT) 10 ROWS VALUES(:DSTRUCT:ISTRUCT)

○ DSTRUCT has a dimension of at least ten to accommodate inserting ten rows.
○ ISTRUCT is a host structure array that is declared in the program.
○ ISTRUCT has a dimension of at least ten small integer fields for the indicators.

Inserting data from a remote database :

INSERT INTO SALES
(SELECT * FROM REMOTESYS.TESTSCHEMA.SALES WHERE SALES_DATE = CURRENT DATE - 1 DAY)

a server authentication entry must exist.
• Use the Add Server Authentication Entry (ADDSVRAUTE) command on the application requestor specifying the server name, user ID, and password. The server name and user ID must be entered in the upper case.

ADDSVRAUTE USRPRF(yourprf) SERVER(DRDASERVERNAME) USRID(YOURUID) PASSWORD(yourpwd)

UPDATE

DB2 SQL has given 2 options for SQL UPDATE statements:

UPDATE using the individual values

UPDATE EMPLOYEE SET WORKDEPT = ’D11’, PHONENO = ’7213’, JOB = ’DESIGNER’ WHERE EMPNO = ’000270’

UPDATE using SET of Values

UPDATE EMPLOYEE SET (WORKDEPT, PHONENO, JOB) = (’D11’, ’7213’, ’DESIGNER’) WHERE EMPNO = ’000270’

DELETE and TRUNCATE

SQL DELETE is quite straightforward.

DELETE FROM TABLENAME WHERE COLUMN1='X' and COLUMN2='Y'

There is one more SQL statement to delete records from a table, and that is the SQL TRUNCATE TABLE statement.

TRUNCATE TABLE table-name

Difference between DELETE and TRUNCATE:

TRUNCATE statement has no “WHERE” clause. So it will delete all the records of the table.
By default , the TRUNCATE statement does not call any DELETE TRIGGERS(IGNORE DELETE TRIGGERS).