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

Sub-queries

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
EX:

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

EX:

SELECT cust_no,
       cust_addr
FROM customer
WHERE order_no =
    (SELECT order_no
     FROM
     ORDER
     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

Ex:

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 :

Ex:

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

Multiple levels of subquery

Ex:

SELECT cust_no,
 cust_name,
 cust_addr
 FROM customer
 WHERE order_no IN
 (SELECT order_no
 FROM
 ORDER
 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:

Insert

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

INSERT

Ex: Insert into temp (a#, b)

SELECT

select a#, sum(b)

from temp1 group by a# ;

UPDATE

Ex: Update tablename

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

single or multiple row updates
update with a subquery

DELETE

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]
end-exec.

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

• DB2 Tutorial For Software Developers (5 of 5)
• DB2 Tutorial For Software Developers (4 of 5)
• DB2 Tutorial For Software Developers (3 of 5)
• DB2 Tutorial For Software Developers (2 of 5)