DB2 Tutorial For Software Developers (4 of 5)

Big data+Puppet Labs Jobs+Career

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
  • Subqueries

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 ;

Corelated subquery 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.


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.