DB2 Tutorial For Software Developers (4 of 5)

DB2 tutorial given in the form of questions and answers. So that any mainframe or DB2 developer can read and implement quickly. Useful to your projects too.

Read my previous post(3 of 5).

Functions: Types are two :

  1. column function
  2. scalar function

Column functions: compute from a group of rows aggregate value for a specified column(s)
avg, count, max, min, sum

scalar functions: are applied to a column or expression and operate on a single value.
char, date, day(s), decimal, digits, float, hex, hour, integer, length, microsecond, minute, month, second, substr, time, timestamp, value, vargraphic, year

you can also apply db2 jobs @ apply for db2 jobs

Complex sql’s

  • Sql provides two ways of coding a complexsql
    1. subqueries
    2. joins
  • one term a sql to be complex when data that is to be retrieved comes from more than one table

Enter your email address to subscribe to this blog and receive notifications of new posts by email.


Nested select statements
specified using the in(or not in) predicate, equality or non-equality predicate(‘=‘ or ‘<>‘) and comparative operator(<, <=, >, >=)
when using the equality, non-equality or comparative operators, the inner query should return only a single value

SELECT cust_no,
FROM customer
WHERE order_no IN
    (SELECT order_no
     WHERE no_products <5);


SELECT cust_no,
FROM customer
WHERE order_no =
    (SELECT order_no
     WHERE no_products = 5);

The nested loop statements gives the user the flexibility for querying multiple tables a specialized form is correlated subquery – the nested select statement refers back to the columns in previous select statements it works on top-bottom-top fashion, non-correlated subquery works in bottom-to-top fashion correlated subquery


SELECT a. Cust_name a. Cust_addr
FROM customer a
WHERE a. Order_no IN
    (SELECT order_no
     FROM customer b
     WHERE a. Cust_id = b. Cust_id)
ORDER BY a. Cust_id,
         a. Cust_no ;

Co-related sub-query using exists clause :


SELECT cust_no,
FROM customer a
    (SELECT *
     ORDER b
     WHERE b. Order_no = a. Order_no
       AND b. Order_no = 5);

Multiple levels of subquery


SELECT cust_no,
 FROM customer
 WHERE order_no IN
 (SELECT order_no
 WHERE prod_id IN
 (SELECT prod_id
 FROM products
 WHERE prod_name = ‘nuts’));
  • Joins

Outer join: for one or more tables being joined, both matching and non-matching rows are returned. Duplicate columns may be eliminated, the non-matching columns will have nulls in them.

Inner join: here there is a possibility one or more of the rows from either or both tables being joined will not be included in the table that results from the join operation

Other DML statement’s:


EX: Insert into tablename(column1, column2, column3 ,……)

values( value1, value2, value3 ,……. . )

  • if any column is omitted in an insert statement and that column is not null, then insert fails; if null it is set to null
  • if the column is defined as not null by default, it is set to that default value
  • omitting the list of columns is equivalent to specifying all values


Ex: Insert into temp (a#, b)


select a#, sum(b)

from temp1 group by a# ;


Ex: Update tablename

set columnname(s) = scalar expression where [ condition ]

single or multiple row updates
update with a subquery


e. G. Delete from tablename

where [condition ];

single or multiple row delete or deletion of all rows

Static sql

  • hard-coded into an application program
  • cannot be modified during the program’s execution except for changes to the values assigned to the host variables
  • cursors are used to access set-level data (i. E when a sql select returns more than 1 row)

The general form is

exec sql
[sql statements]

Dynamic sql

  • statements can change throughout the program’s execution
  • when the sql is bound, the application plan or package that is created does not contain the same information as that for a static sql program
  • the access paths cannot be determined before execution

Read my post Part-5 on DB2 tutorial.

Also read

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.