Selectorweb.com New York
home > DB2 Email

DB2
 
This page Other Pages
- intro, links
- books
- tools
- porting from Sybase to DB2
- getting help, simple commands
- import / export
- backup / recovery
- get_help
- SQL procedure
- db2 SQL: value(), values(), with, recursive SQL
- Starting with db2
- db2_instance
- db2_database
- db2_tablespaces
- db2_tables, data types
- db2_authentication
- db2_concurrency
- db2_more_sql_hints
- db2_sql: recursive, outer joins
- db2 OLAP regression
- db2_sql - with, examples, hexadecimal, optimizier
- sample_db2.cgi
- db2_sql_pl


 
intro, links home - top of the page - email

DB2 traditionally was running on Mainframe computers (since 1983). But in mid-90s it was ported on Unix and other OSs - and now it is called:  UDB - Universal DataBase
- http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7s0frm3toc.htm - SQL Reference - very good place to use
- ftp://ftp.software.ibm.com/ps/products/db2/ - download FTP site
- www-4.ibm.com/software/data/db2/linux/ - download version for linux

- directory.google.com/Top/Computers/Software/Databases/IBM_DB2/ - on Google
- www-4.ibm.com/software/data/db2/udb/ - Official DB2 home page on IBM web site - including downloads, and technical libraries.
- www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7pubs.d2w/en_main - manuals (html & pdf)
- www-3.ibm.com/software/data/db2/os390/v7books.html - HTML & PDFs books for download
- http://www-4.ibm.com/software/data/db2/library/ - Manuals

- http://www.redbooks.ibm.com/ - Redbooks
- http://www7b.boulder.ibm.com/dmdd/ - db2 developer domain
- http://www-3.ibm.com/services/learning/spotlight/db2/db2fastpath.html?c=IBM_ChannelNewswatch&n=various&t=aff - Fastpath -  self-study, e-learning course on DB2 UDB (download).

- www.webcom.com/raberd/db2info.html- Ron Rabe's DB2 Page.

- www.db2mag.com - DB2 magazine
- www.esj.com - Enterprise Systems Journal (see also www.research.ibm.com/journal/ )
- www.idug.org -   IDUG: International DB2 Users - IDUG is the International DB2 Users Group. IDUG provides information and support to DB2 Users and Vendors about DB2 database products, DB2 conferences, other DB2 user groups, and other DB2 vendors.

- members.aol.com/rexxauthor/sqlbk.htm -   SQL Books - Descriptions and ordering information on books about SQL for DB2.
- ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM -   DB2 UDB Cookbook Downloads - Graeme Birchall.

- jupiter.ryci.com/archives/db2-l.html -   Search Archives of DB2-L discussion list.
- www.multimania.com/db2usa/eliendb2.htm -   DB2usa: DB2 for OS/390 - links.

- www.dblinks.net/DB2/ -   Links and resources.
- www.gseukdb2.org.uk/  -   GSE UK DB2 Working Group -

OLAP:

- OLAP functions - OLAP Functions - from SQL reference
- http://www7b.software.ibm.com/dmdd/library/techarticle/lyle/0110lyle.html - good review of OLAP functions

- http://www.almaden.ibm.com/cs/people/peterh/b15.pdf - Peter J. Haas DB2 UDB Advanced Analytics for Business Intelligence
- http://www.almaden.ibm.com/cs/people/peterh/b16.pdf - Peter J. Haas Speeding up DB2 UDB Using Sampling

- http://www.ibm.com/search?en=utf&v=11&lang=en&cc=us&lv=c&q=db2+olap+function+reference - search IBM site

- http://www.ibm-siebel.com/us/ - IBM - Siebel


 
books home - top of the page - email

DB2 books:
- DB2 Fundamentals Certification for Dummies - by Paul C. Zikopoulos, Jennifer Gibbs, Roman B. Melnyk (2001)
- DB2 : The Complete Reference - by Roman B. Melnyk, Paul C. Zikopoulos (2001)
- DB2 Developer's Guide, Fourth Edition (2000) by Craig S. Mullins
- A Complete Guide to DB2 Universal Database - by D. D. Chamberlin (1998)
- DB2 Universal Database v7.1 for UNIX, Linux, Windows and OS/2 Database Administration Certification Guide (4th ed.) -  by George Baklarz, Bill Wong, Jonathan Cook (2000).
- DB2 Universal Database in the Solaris Operating Environment - by Tetsuya Shirai et al (1999)

DB2 Magazine - www.db2mag.com/subscribe/
DB2 resources - www.idug.org/db2/index.html - www.db2mag.com/resources/

 
tools home - top of the page - email

Tools:
You can work fom the pompt (unix or dos). This is good, because you can use many convenient shell features - and run commands from a file - like this: db2 -tvf myfile .
Or you can use DB2's own set of graphical tools
You can also use  DBArtisan and RapidSQL from embarcadero .


 
Porting from Sybase to DB2 home - top of the page - email

As DB2 now (starting ver.7) has Transact SQL, you can easily port from Sybase to DB2.
Read this book: "DB2 UDB V7.1 Porting Guide" - order it from FatBrain.com or read / download PDF:
* http://publib-b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/sg246128.html?Open
 
 
Sybase DB2 UDB
Sybase server DB2 UDB Instance
database database
device table space and containers
segment
login to server attach to instance (a)
use database connect to database
master database system catalogs
empdb  temporary table spaces

(a) You can execute the  ATTACH command to attach to the DB2 instance on the same workstation or remote workstation. To perform instance administrative tasks like creating a database, updating the database manager, and killing connected database user, you have to attach to the DB2 instance. If you have not executed the ATTACH command, all the instance level commands are executed against the current instance, specified by the DB2INSTANCE environment variable.

Next table shows common admin tasks (most of them can be also invoked from the Sybase Central or, for DB2 - from the Control Center, the Command Center or the Stored Procedure Builder (SPB)).
 
 
Task Sybase DB2
View the server options sp_configure GET DBM CFG
View the database options GET DB CFG FOR  dbname
Update server options sp_configure 'option_name', new_value UPDATE DBM CFG USING 'config_parameter', new_value
Update database options UPDATE DB CFG FOR  dbname USING  'config_parameter', new_value
Display active servers or instances showserver (executed from UNIX shell) db2ilist (executed from UNIX shell)
Access server or instance isql -Uuser  -Ppswd -Sserver ATTACH TO  instance_name user user_name using  pswd
Access database use  dbname CONNECT TO dbname user user_name using  pswd
List databases in server or instance sp_helpdb LIST DB DIRECTORY
List devices or files used by the databases sp_helpdevice LIST TABLESPACES  or LIST TABLESPACE CONTAINERS
Find space used or available space sp_helpdb dbname or sp_helpsegment segname or sp_spaceused LIST TABLESPACE CONTAINERS FOR  (tsid)  SHOW DETAIL or LIST TABLESPACES SHOW DETAIL
List database tables sp_help LIST TABLES
List table characteristics sp_help tablename DESCRIBE TABLE   tablename
List source for stored procedures sp_helptext procname Use 'Get Source' function of the DB2 Stored Procedure Builder
Administer security grant revoke sp_helpuser sp_addlogin sp_adduser sp_addalias sp_dropalias sp_dropuser sp_droplogin sp_addgroup sp_helpgroup sp_changegroup sp_password GRANT REVOKE UPDATE DBM CFG USING SYSADM_GROUP  group_name UPDATE DBM CFG USING SYSCTRL_GROUP  group_name UPDATE DBM CFG USING SYSMAINT_GROUP  group_name All the authentication set up is done by an external security mechanism such as Operating System (mkuser, chuser, mkgroup, chgroup, passwd)
Start a server or instance startserver -fsrvr_name (from UNIX shell) db2start (from UNIX shell)
Backup database dump database  db_name  to '/path/file' BACKUP DATABASE  db_name TO  /path/file
Restore database load database  db_name from '/path/file' RESTORE DATABASE  db_name FROM  /path/file
Export a text file from a table bcp  table_name  out filename
for example:
bcp dbase..tab1  out  tab1.dat -S DBSERV1 -U someuser -P somepasswd -c > /dev/null
EXPORT TO  filename  OF  type SELECT
for example:
export to names.del of del  select * from names
Load a text file into a table bcp table_name in filename
for example:
bcp dbase..tab1  in  tab1.dat -S DBSERV2 -U someuser -P somepasswd -c > /dev/null
LOAD FROM  filename  OF  type INSERT INTO  tablename 
or 
IMPORT FROM  filename OF  type INSERT INTO  tablename
for example:
import from names.del of del  replace into names
List connected users sp_who LIST APPLICATIONS
Kill connected users kill  spid_number FORCE APPLICATION
Generate DDLs defncopy db2look -e


 
db2 Getting Help, Simple Commands home - top of the page - email

 
add your username into /etc/group file:
db2fadm1:x:104:
db2iadm1:x:103:db2as,user1,user2,user3
db2asgrp:x:105:db2inst1,user1,user2,user3

Command-line processor - consists of 2 processes:
db2  - frontend shell
db2bp - backend process keeping connection withthe database 
db2 terminate - removes db2bp process

Getting help:
db2 ?
db2 ? some_command
db2 ? options
db2 ? help
db2 ? error_code

db2 list command options

db2 create database test
db2 drop database test
db2 create database acct

db2 connect to acct
db2 list tables for user
db2 terminate

db2 connect to ACCT user db2inst1 using ibmdb2

db2 "create table test ( col1 varchar(60), col2 int, col3 timestamp)"
db2 "drop table test"

db2 "create table test1 ( fname varchar(60),lname varchar(60), age int)"
db2 "insert into test1 values ('Peter','Johnson',40)"
db2 'select * from test1 where 1=2'
db2 'select * from test1'

db2 "insert into test1 values ('Jayson','Couper',44)"
db2 "insert into test1 values ('Lev','Smith',50)"
db2 "update test1 set fname='Michael', age=5 where lname='Smith'"

update and delete with joins - correlated query:
When you need to update one table based on the other table - 
it is called "correlated update".  You have to repeat the same "where"
logic 2 times. 

update maintab m 
set (m.fname, m.lname) = 
 (select u.fname,u.lname from updatetab u where m.id=u.id) 
where exists 
 (select null from updatetab u where m.id=u.id); 

or variation: use "in" expression:

update maintab m 
set (m.fname, m.lname) =
 (select u.fname,u.lname from updatetab u where m.id=u.id) 
where m.id in 
 (select u.id from updatetab); 

or delete expression:

delete from maintab m 
where exists 
 (select null from updatetab u where m.id=u.id); 

Note: If you don't include the 2nd "where" clause - then  ALL rows in the 
main table will be updated (putting NULLs in all rows which are not part of the join)
======================
Please note: the following Sybase syntax DOES NOT WORK in DB2:
                update maintab
                set m.fname=u.fname, m.lname=u.lname
                from  maintab m, updates u
                where m.id=u.id 
======================

How to insert a row - but only if the row with the value of a file doesn't exist:
insert into mytable (mycolumn) 
select '12345' from table (values 1) as dummy
where not exists (select 1 from mytable where mycolumn='12345')

Many examples:
insert into test1 (LNAME,FNAME,AGE) values ('LL','FF',7);
insert into test1 (AGE,LNAME,FNAME) values (8,'LL','FF');
insert into test1 (age,lnameE,fname) values (9,'LL','FF');
select * from test1
db2 -r  file1.txt  "select * from test3 order by fname"
db2  "select * from test3 order by fname" > file2.txt

db2 'export to test1.txt of del select * from test1'
db2 'import from test1.txt of del insert into test1'
db2 'import from test1.txt of del replace into test1'

Saving DDL for the database 
(or for selected tables and their indexes) in a file:
db2look -d acct -e >myfile.txt
db2look -d acct -e -a -t test1 test2
db2look -d acct -e -a -t test1 test2 -o myfile.txt
db2look -d acct -e -a -t test1 test2  > myfile.txt

db2 list database directory
db2 ? list tables
db2 list tables for user
db2 list tables for schema db2inst1

db2 "select * from test1"
db2 'select * from test1 fetch first 10 rows only'
db2 describe table test1
db2 "select TABNAME from SYSCAT.TABLES where TABNAME like 'A+_%' escape '+'"
db2 "select TABNAME from SYSCAT.TABLES where TABNAME like 'A%'"

db2 describe table A_ACCOUNT
db2 "create unique index ia_transactions on a_transactions (tran_id,dt_tran) cluster"

vi test.sql
select * from test1 where fname='Lev';
select * from test1 where fname='Vera';

--  run many commands from a file.
--  Don't forget to separate SQL statments with ";"
db2 -tvf test.sql

db2 "create table session.ss ( fname varchar(60),lname varchar(60), age int)"
db2 "insert into session.ss values ('aa','bb',5)"
db2 "select * from session.ss"

db2 list tables for user
db2 list tables for schema session
db2 terminate
exit
db2 connect to ACCT user db2inst1 using ibmdb2
db2 list tables for schema session

-- calling shell commands from inside db2 using '!':
db2  '! ls -ltr'
db2  "! ls -ltr"

Some commands for Windows:
db2ic  - inform.center
db2cca -client configuration assistant
db2cmd - db2 command window


 
Import / Export home - top of the page - email

There are three utilities for bulk load/unload:    Export, Import, Load
Supported file types:
    DEL - delimited ASCII format - this is what you mostly use.
    ASC - for import (1 line => 1 row, column selection based on byte position in the line).
    IXF - Integrated Exchange Format (contains table def. and data) - you will probably never use it.
    WSF - Work Sheet Format - used with some old spreadsheets.

EXPORT:
export to names.del of del  select * from names
IMPORT:
  insert - add (append) rows of data
  insert_update - add rows (or update existing data)
  replace - deletes all data from the table - then inserts new data
  replace_create - (only with IXF files) - deletes / re-creates the table and fill it with data
import from names.del of del  insert into names
import from names.del of del  replace into names

Here is how to truncate a table:
   import from /dev/null of del  replace into mytab

LOAD:
  Load utility is faster, than import utility, but import utility is logged and is checking constraints
load from names.del of del  replace into names

Modifying a delimiter
  export ...... modified by chardel *


 
Backup / Recovery home - top of the page - email

Recovery is a serious issue - consult with administration guides.

Two simple commands:
BACKUP DATABASE  db_name TO  /path/file
RESTORE DATABASE  db_name FROM  /path/file


 
get_help home - top of the page - email

db2 ?
db2 ? some_command
db2 ? options
db2 ? help
db2 ? error_code

for example:
 > db2 crocodile
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003
 > db2 ? SQL1024N

 SQL1024N A database connection does not exist.

Explanation:  There is no connection to a database.  Other SQL
statements cannot be processed unless an SQL CONNECT was
previously executed.

The command cannot be processed.

User Response:  If the error occurred when disconnecting from the
database, continue processing.  If the error occurred on another
SQL statement, issue an SQL CONNECT statement and resubmit the
command or statement.

sqlcode:  -1024

sqlstate:  08003

Another way to get help fast is to copy/paste the error string into google (internet search engine). Especially Google Groups is very helpful. You usually get the answer on the very first screen.

Download documentation:
- www-4.ibm.com/software/data/db2/udb/ - Official DB2 home page on IBM web site - including downloads, and technical libraries.
- www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7pubs.d2w/en_main - manuals (html & pdf)
- www-3.ibm.com/software/data/db2/os390/v7books.html - HTML & PDFs books for download

- http://publib.boulder.ibm.com/cgi-bin/bookmgr/download/dsnmch11.pdf - Messages and Codes (~ 7 MB in size)
- http://publib.boulder.ibm.com/cgi-bin/bookmgr/download/dsnsqh11.pdf - SQL reference (~ 5 MB in size)

- http://publib-b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/sg246128.html?Open - "DB2 UDB V7.1 Porting Guide"


 
SQL procedure home - top of the page - email

 
-- This is a CLP script that creates an SQL procedure.
-- To create the SQL procedure using this script, perform the following steps:
-- 1. connect to the database
-- 2. issue the command "db2 -td@ -vf <script-name>"
--    where <script-name> represents the name of this script
--
-- To call this SQL procedure from the command line, perform the following steps:
-- 1. connect to the database
-- 2. issue the following command:
--    db2 "CALL create_dept_table ('D11', ?)"
--
-- The sample "dynamic.sqc" demonstrates how to call this SQL procedure using
-- an embedded C client application.

CREATE PROCEDURE create_dept_table
(IN deptNumber VARCHAR(3), OUT table_name VARCHAR(30))
LANGUAGE SQL
  BEGIN
    DECLARE SQLSTATE CHAR(5);
    DECLARE new_name VARCHAR(30);
    DECLARE stmt VARCHAR(1000);

    -- continue if sqlstate 42704 ('undefined object name')
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
      SET stmt = '';
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
      SET table_name = 'PROCEDURE_FAILED';

    SET new_name = 'DEPT_'||deptNumber||'_T';
    SET stmt = 'DROP TABLE '||new_name;
    PREPARE s1 FROM stmt;
    EXECUTE s1;
    SET stmt = 'CREATE TABLE '||new_name||
     '( empno CHAR(6) NOT NULL, '||
     'firstnme VARCHAR(12) NOT NULL, '||
     'midinit CHAR(1) NOT NULL, '||
     'lastname VARCHAR(15) NOT NULL, '||
     'salary DECIMAL(9,2))';
    PREPARE s2 FROM STMT;
    EXECUTE s2;
    SET stmt = 'INSERT INTO '||new_name || ' ' ||
     'SELECT empno, firstnme, midinit, lastname, salary '||
     'FROM employee '||
     'WHERE workdept = ?';
   PREPARE s3 FROM stmt;
   EXECUTE s3 USING deptNumber;

   SET table_name = new_name;
END @


 
db2 SQL: value(), values(), with, recursive SQL home - top of the page - email

comment:
-- comment should be on a separate line outside an SQL statement

select 1 from (values 1) as aa -- this is not a valid db2 comment
 

way to create simple tests against dynamic table:
select 'a' as col1,'b' as col2 from table (values 1) as dummy

or shorter:
select 'a','b' from (values 1) qq

In fact, for simple tests you don't even need select and a dummy table.
values ( 'mama', 1)
values (cast (2.5 as decimal(10,3)) * cast (2.5 as decimal(10,3)) )

using cast( ) and values( ), building null values on the fly:
select 
  'abc' as col1, 
  cast(null as varchar(80)) as col2
from 
  table (values (1,2),(3,4)) as dummy

Hint how to make running tests from the prompt easy:
I create 2 aliases:

alias vv='vi test.sql'
alias rr='db2 -tvf test.sql'

Type 'vv' to edit test.sql file (remember - you should end each SQL statement with a semicolon ';')
Then type 'rr' to run this SQL.

simple insert:
insert into inst1.test (A) values ('a'), ('b'), ('c')

using case:
select
  case 
    when 1<2 then 'mama'
    when 1>2 then 'papa'
  end
  as person
from table (values 1) as qq

concatenating:
select 'mama' concat ' papa' from table (values 1) as qq;
select 'mama' || ' papa' from table (values 1) as qq;

union, intersect & except:
select .. from T1 union T2 - remove duplicates
select .. from T1 union all T2  -- preserve duplicates
select .. from T1 intersect T2 - remove duplicates
select .. from T1 intersect all T2  -- contains min number of repetition
select .. from T1 except T2 - remove duplicates, then do except
select .. from T1 except all T2 do except, then remove duplicates

value( ) function:
-- value() function - accepts a variable number of parameteres and returns a
first non-null value
-- parameters should be of compatible types
-- the actual name for this function is coalesce() - means "to arise from a
combination of distinct elements"
select value(1,2) from (values 1) as aa 
-- returns 1
select value(cast(null as int),2) from (values 1) as aa 
-- returns 2
select value(cast(null as int),cast(null as int),3) from (values 1) as aa 
-- returns 3

------------------------------------------------------------

2 ways (common table and inline table expression) to define table dynamically in the SQL statement:
 
with tree (id,pid) as (
    values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
  )
select * from tree

        ID      PID
        1       2
        2      3
        3       4
        4       [NULL]
        5       3
        6       5
-------------------------------------------- another way to do the same
select * from (
  values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
  )
  as a

Getting root of the tree using recursive SQL:
 
with 

  tree (id,pid) as (
    values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
  ),

  rr (id,pid) as (
    select tr.id, tr.pid from tree tr where tr.id=1
      union all
    select tt.id,tt.pid from tree tt, rr
    where tt.id = rr.pid
  )

select id from rr where rr.pid is null

More recursive SQL:
 
We have table storing a tree (each row has id and parent_id).
To get root id for a given id (12345) we fill out temporary table rr:
first with given id and its parent_id. Then union with recursively calculated
id and parent_id as we go up the tree:

  with rr (parent_id, id) as
  (
      select p.parent_id, p.id
        from mytree p
       where p.id = 12345
  union all
      select p.parent_id, p.id
        from mytree p, rr
       where p.id = rr.parent_id
  )
  select id ROOT from rr where parent_id is null;

Here is an example going in the oposite direction (down the tree). We showing the whole tree under a given id:

  with rr (id, level) as
  (
      select id, 1
        from mytree 
       where id = 10001
  union all
      select child.id, parent.level + 1
        from mytree child, rr parent
       where parent.id = child.parent_id
  )
  select * from rr;

  Result:
           ID         LEVEL 
  ----------------------------
         10001            1
         29361            2
         23044            3
         25162            3
         25302            3

Here is how to combine 2 above queries together:
 
----------------------------------------------------------------------------
  -- Given any node in any tree, this query will drill up to the top
  -- level of the tree, and then query down to give all nodes that exist
  -- within the tree (i.e. given any node_id, show the whole
  -- tree that contains it).
  --
  -- Note: This query will never go deeper than 'stop_level' levels (10).
  ----------------------------------------------------------------------------

  with rec_root (parent_id, child_id, sub_query, level, stop_level) as
  (
      select my_parent_id, my_id, 1, 0, 0
        from mydb.mytable
       where my_id = 25162
  union all
      select parent.my_parent_id, parent.my_id, 2, 1, child.level + 1
        from mydb.mytable parent, rec_root child
       where parent.my_id = child.parent_id
         and sub_query in (1,2)
         and child.stop_level < 10
  union all
       select my_parent_id, my_id, 3, parent3.level + 1, parent3.level + 1
         from rec_root parent3, mydb.mytable child3
        where parent3.child_id = child3.my_parent_id
          and (   (parent3.sub_query = 2 and parent3.parent_id is null)
                or parent3.sub_query = 3
              )
          and parent3.stop_level < 10
  )
  select * from rec_root
   where parent_id is null
      or sub_query = 3;

Example comparing DB2 and Sybase:
 
DB2...
with tt (is_asst) as (
  SELECT 1 is_asst
  FROM TABLE (VALUES 1) AS temp_table_1
  where exists (
     select 1 from coverage.desk_sales
     where user_id = 'myuser'
     and rel_type in ('D', 'C', 'L', 'A', 'N')
  )
  or exists (
     select 1 from coverage.regional_sales
     where user_id = 'myuser'
     and rel_type in ('D', 'C', 'L')
  )
  union
     SELECT 0 is_asst
     FROM TABLE (VALUES 0) AS temp_table_0
  )
  select * from tt order by is_asst desc fetch first 1 rows only;

Sybase...
create proc is_user_asst
  @kid varchar(20)
as
declare @is_asst int
select @is_asst = 0
if exists (
   select 1 from cov..desks
   where user_id = @kid
      and rel_type in ('D', 'C', 'L', 'A', 'N')
)
begin 
   select @is_asst = 1 
end
else
if exists (
    select 1 from cov..reg_sales
    where user_id = @kid and rel_type in ('D', 'C', 'L')

begin
  select @is_asst = 1
end

select "is_asst" = @is_asst


 
starting with db2 home - top of the page - email

Starting working with DB2 on unix:
 
You have to install dbd2 client software on your unix workstation and reboot it.
You have to source a db2profile file with all necessary environment
You have to catalog properly, for example:

  db2 catalog tcpip node xxx remote somer_server server some_port
  db2 catalog db some_db at node xxx
  db2 connect to some_db
  db2 list database directory

  db2 catalog db sample at node xxx
  db2 connect to sample
  db2 "select * from syscat.tables fetch first 5 rows only"
     (note the quotes around select - to hide from shell processing)

  db2 -tvf myfile.sql    - to run many commands from a file.  Don't forget to separate SQL statments with ";"

db2 "select char(reviewed_date, ISO) from oe.OE_contact fetch first 10 rows only"
-------------------------------------------------

 
db2 instance home - top of the page - email

Instance:
Creating / updating / dropping the instance (be careful)
db2icrt  <instance_owner_id>
db2icrt -u <fenced_user_id> <instance_owner_id>
db2iupdt <instance_owner_id>
db2idrop <instance_owner_id>
db2ilist  - list all instances on the machine

creating an instance of DAS (db admin server) on Windows:
db2admin create /user:<name> /password:<password>
db2admin
db2admin start
db2admin stop
db2admin drop
dasidrop <das_instance_owner_id>
 

configuring the instance:
db2 get dbm cfg
db2 get database manager configuration

db2 update dbm cfg using diaglevel 4
db2 update database manager configuration using <param_name> <new_value>

restart the instance:
db2stop
db2start
db2 reset dbm cfg

instance config commands:
db2 get admin cfg
db2 update admin cfg using <param_name> <new_value>
db2 reset admin cfg


 
db2 database home - top of the page - email

Database:
create database:
db2 create database test
db2 create database test on /mydir
db2sampl <location>  -- create a sample database
db2 create database test using codeset IBM-1252 territory US

using database directories:
db2 list database directory - to view list in the System Database Directory
 db2 list database directory on /home/db2inst1 - to view list of databases residing in certain location
db2 list database directory on e:  - same on Windows

The database directory has the following structure:
     $DBHOME/instance/NODE0000/SQL00001
For example:
myserver : /home/db2inst1/db2inst1/NODE0000 > ls
SQL00001/  SQL00002/  SQL00003/  sqldbdir/

Configuring the database:
db2 get db cfg for <dbname>
db2 get database configuration for <dbname>

db2 update db cfg for <dbname> using locktimeout 10
db2 update database configuration for <dbname> using locktimeout 10

deleting a database:
db2 drop database <dbname>


 
 db2 tablespaces home - top of the page - email

Tablespaces:
Tablespace - logical space grouping containers to hold db stuff
Tablespace containers (physical) - directories, files, raw devices

SMS - System Managed Space - directory containers managed by the operatin system. Automaticall create a file for each new table or index to hold the data - as long as there is enough disk space. 

DMS - Database Managed Space - managed by the database (use command 'add container' to change their size)

Three (3) default tablespaces (created with one container each by default):
  SYSCATSPACE - system catalog tables in the directory   ..../NODE0000/SQL00001/SQLT0000.0
  TEMPSPACE - temporary tables   ..../NODE0000/SQL00001/SQLT0001.0
  USERSPACE1 - user tables  ..../NODE0000/SQL00001/SQLT0002.0

db2 create tablespace myDMSspace managed by database using (FILE 'file1' 1000, FILE 'file2' 2000)
db2 create tablespace mySMSspace managed by system   using ('directory1', 'directory2')
db2 drop tablespace <tablespace_name>
db2 'select * from SYSCAT.TABLESPACES' - 


 
db2 tables, datatypes, indexes, views home - top of the page - email

Tables:
system tables (SYSCATSPACE) may have one of four (4) schema names:
  SYSIBM - read-only system tables (e.g. SYSIBM.SYSTABLESPACES, SYSIBM.SYSTABLES, etc.)
  SYSCAT - read-only views on the system tables (e.g. SYSCAT.TABLESPACES, SYSCAT.TABLES, etc.)
  SYSFUN - UDF (User Defined Functions)
  SYSSTAT - hold statistics (e.g. SYSSTAT.TABLES,  SYSSTAT.COLUMNS, SYSSTAT.INDEXES, etc.)

db2 list tables for system
db2 list tables for schema db2inst1
db2 list tables

Compare:
db2 'select * from SYSIBM.SYSTABLES fetch first 1 rows only'
db2 'select * from SYSCAT.TABLES fetch first 1 rows only'
db2 "select TABNAME from SYSCAT.TABLES where TABNAME like 'A%'"
 
Database Object SYSIBM system table SYSCAT system table view SYSSTAT updateable view
Tablespace SYSTABLESPACES TABLESPACES
Table SYSTABLES TABLES TABLES
Schema SYSSCHEMATA SCHEMATA
View SYSVIEWS VIEWS
Column SYSCOLUMNS COLUMNS COLUMNS
Index SYSINDEXES INDEXES INDEXES
Data Type SYSDATATYPES DATATYPES
Check Constraint SYSCHECKS CHECKS
Referential Integrity SYSRELS REFERENCES
Function SYSFUNCTIONS FUNCTIONS FUNCTIONS
Bufferpool SYSBUFFERPOOLS BUFFERPOOLS

User tables - simply tables created by users (permanent tables)
Temporary tables: 
   - derived temp.tables - created dynamically by DB2 as needed in temp. tablespaces. Automatically dropped at the end of transaction.
   - declared temporary tables (version 7) - created by user with the DECLARE command with the schema called SESSION.
      db2 declare global temporarytable ... ( ????)

db2 'select typename from syscat.datatypes'

TYPENAME
------------------
BIGINT          (8 bytes)
BLOB          (Binary Large Object - up to 2 GB - video, sound & pictures)
BOOLEAN
CHARACTER   (char(1) is default, char(n), where n = 1..254)
CLOB          (Character Large Object - 32K .. 2 GB) - also DBCLOB to store 2-byte texts (Chinese, etc.)
DATALINK
DATE             (4 bytes)
DECIMAL        (decimal(5,0) by default)
DOUBLE         (similar to real,but 8 bytes)
INTEGER         (4 bytes)
LONG VARCHAR   (old type, when varchar was shorter)
REAL              (4 bytes)
REFERENCE
SMALLINT       (2 bytes)
TIME              (3 bytes)
TIMESTAMP    (10 bytes)
VARCHAR       (1..32672 bytes)

  17 record(s) selected.
There are also some other types (and user-defined types)
 
Difference between date, time, and timestamp types:

DATE (4 bytes)
TIME (3 bytes) 
TIMESTAMP (10 bytes)

select CURRENT DATE from (values 1) a
select CURRENT TIMESTAMP from (values 1) a
select CURRENT TIME from (values 1) a

select CURRENT SERVER from (values 1) a
select user from (values 1) a 
 db2 "values(user)"

create table inst.test (d date, t time, ts timestamp) in inst_4K
insert into inst.test values (date(current timestamp), time(current timestamp), timestamp(current timestamp))
select * from inst.test 
        D       T       TS
1       2002-04-29      09:33:53        2002-04-29 09:33:53.207283
--------------------------------------------

datetime arithmetic:

labeled:
  select sales_date, sales_date + 1 YEAR + 2 DAYS from sales

date: yyyymmdd - decimal(8,0)
  select sales date, sales_date + 00010001. from sales
  select sales_date, char(sales_date - '04/01/1994') from sales

time:  hhmmss - decimal (6,0)
  select ... time('12:23:10') - 011005.
               time('12:23:10') - '01:01:01'

timestamp:  yyyyMMddHHmmSS.nnnnnn   decimal(20,6)
  timestamp(20010301171230') - 00000000011005.000022
  char( timestamp(20010301171230') - '2001-03-01-16.02.24.999978' )

useful functions:
date(), day(), days(),hour(), minute(), month(), second(), time(), timestamp(), timestapm_iso(),year()

Identity columns:

create table test_identity ( 
  myid int generated always as identity,
  lname varchar(25), 
  fname varchar(20)
); 
insert into test_identity (lname, fname) values ('l1','f1');
insert into test_identity  (lname, fname) values ('l2','f2');
insert into test_identity  (lname, fname) values ('l3','f3');
insert into test_identity  (lname, fname) values ('l4','f4');

select * from test_identity

Note: when defining identity column, you can also provide attributes, for example:

  • start with NN
  • increment by NN
  • minvalue NN
  • no minvalue
  • maxvalue NN
  • no maxvalue
  • cycle
  • no cycle
  • cache NN
  • order
  • no order

You can also provide a identity "generation-expression"

not null , with default:
create table inventory (
  productID int not null with default 12345,
  quantity integer,
   price decimal(4,2),
   dept smallint with default)

Table Constraints:
  Primary key, foreign key
db2 alter table salary add constraint chk_salbonus CHECK (salary + bonus < 200000)
db2 create table salary ( employeeID smallint not null, ...... ,  constraint uempID UNIQUE(employeeID))

Insert rule
delete rules  (NO ACTION, RESTRICT, CASCADE, SET NULL)
update rules (NO ACTION, RESTRICT)

create table tab1 ( id, ...)
create table tab2 (id, ...)
create table tab3 ( 
   a .., 
   b ..,
   c ..,
   constraint ca foreign key(a) references tab1(id), 
   constraint cb foreign key(b) references tab2(id)) 

Indexes:
db2 create index  i1 on tab1 ( col1 desc)
db2 create index  i2 on tab1 (col1, col2)
db2 create unique index i1 on tab1(col1)
db2 create index i1 on tab1(col1) cluster
db2 create index i1 on tab1(col1) allow reverse scans
db2 alter index ...
db2 drop index ...

Views:
db2 "create view as select col1 from tab1 where col1 like 'A%' "

Bufferpools - cache for database pages (see in SYSCAT.BUFFERPOOLS table)
Log Files - logging can be turned off to speed up operations
 


 
Authentication home - top of the page - email

attach - get access to the instance
connect - get access to the database
 
db2 attach to db2inst1 user myuser using mypassword
db2 connect to mydatabase user myuser using mypassword
db2 connect to mydatabase user myuser using mypassword new newpass confirm newpass

changing auth.type of the database server:
db2 update dbm cfg using AUTHENTICATION <authentication_type>

Auth.types: 
  SERVER, SERVER_ENCRYPT,
  CLIENT,
  DCE, DCE_SERVER_ENCRYPT (DCE = Distributing Computer Environment), 
  KERBEROS, KRB_SERVER_ENCRYPT
  DCS, DCS_ENCRYPT (DCS = Database Connection Services)

TRUST_ALLCLNTS parameter - YES, NO, DRDAONLY
YES:

On the server:
db2 update dbm cfg using authentication client
db2 update dbm cfg using trust_allclnts yes
db2 update dbm cfg using trust_clntauth server

On the client:
db2 catalog database mydb at node nd1 authentication client

NO:

On the server:
db2 update dbm cfg using authentication client
db2 update dbm cfg using trust_allclnts no
db2 update dbm cfg using trust_clntauth server

On the client:
db2 catalog database mydb at node nd1 authentication client

TRUST_CLNTAUTH parameter - CLIENT, SERVER
 
CLIENT:

On the server:
db2 update dbm cfg using authentication client
db2 update dbm cfg using trust_allclnts no
db2 update dbm cfg using trust_clntauth client

On the client:
db2 catalog database mydb at node nd1 authentication client

SERVER:

On the server:
db2 update dbm cfg using authentication client
db2 update dbm cfg using trust_allclnts yes
db2 update dbm cfg using trust_clntauth server

On the client:
db2 catalog database mydb at node nd1 authentication client

Gaining authorization = gaining group membership in one of 5 groups:
SYSADM - instance king,
SYSCTRL - instance control - almost as powerful as SYSADM, but needs explicitly granted privileges,
SYSMAINT - instance maintenance, can not access user data
DBADM - database-level authority (db - not instance).
LOAD - database-level authority to insert large amounts of data.

SYS* authorities - can be assigned only to groups, are instance-level
DBADM, LOAD authorities - can be assigned to group and users, are database-level
 
Opration SYSADM SYSCTRL SYSMAINT DBADM LOAD
update dbm cfg Yes -- -- -- --
control which group have SYSADM, SYSCTRL, SYSMAINT authority Yes -- -- -- --
Control which users or groups have DBADM authority Yes -- -- -- --
Create/Drop database Yes Yes -- -- --
Create/Drop tablespace Yes Yes -- -- --
Backup database/Restore/Rollforard Yes Yes Yes -- --
db2start / db2stop Yes Yes Yes -- --
update database cfg file Yes Yes Yes --  --
grant/revoke db privileges Yes -- -- Yes --
grant control privilegeon an object Yes -- -- Yes --
create table Yes -- -- Yes --
load into tables Yes -- -- Yes Yes
runstats Yes Yes Yes Yes Yes

db2 update dbm cfg using SYSCTRL_GROUP db2grp
db2update dbm cfg SYSMAINT_GROUP db2mnt

db2 grant dbadm on database to user <username>
db2 grant dbadm on database to group <groupname>

db2 revoke dbadm on database from user <username>
db2 revoke dbadm on database from group <groupname>

db2 grant load on database to user <username>
db2 grant load on database to group <groupname>

db2 revoke load on database from user <username>
db2 revoke load on database from group <groupname>

Privileges - are stored in the database system catalog views:
SYSCAT.DBAUTH - databases authorities (db2 get authorization - will report the database authorities of the current user)
   (DBADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED, IMPLICIT_SCHEMA, LOAD)
SYSCAT.TABAUTH - table and view priveleges
   (CONTROL, ALTER, DELETE, INDEX, INSERT, SELECT, REFERENCE, UPDATE)
SYSCAT.INDEXAUTH - index privileges
   (CONTROL)
SYSCAT.SCHMAAUTH - schema privileges
   (ALTERIN, CREATEIN, DROPIN)
SYSCAT.PACKAGEAUTH - package privileges
   (CONTROL, BIND, EXECUTE)

Here is how to grant/revoke those privileges:
db2 grant select on mytable to public

db2 grant <db_privilege> on database to user <username>
db2 grant <db_privilege> on database to group <groupname>

db2 revoke <db_privilege> on database from user <username>
db2 revoke <db_privilege> on database from group <groupname>

More examples:
db2 grant <privilege> on <tbl/vw name> to user <username>
db2 grant control on index <index_name> to user <user_name>
db2 grant <privilege> on schema <schema_name> to user <user_name>
db2 grant <privilege> on package <package_name> to user <user_name>
2 revoke all privileges on table mytable from myuser

Privileges to an object are also granted implicitly when somebody creates an object. (database, table, view, etc.).


 
concurrency home - top of the page - email

package - executable form of one or more queries - SYSCAT.PACKAGES
a package is created using PREP and BIND commands.
You can set level of isolation (or concurrency) of the package
There are 4 levels: UR (Uncommitted Reads), CS (Cursor Stability), RS (Read Stability), RR (Repeatable Read)
The lowest isolation (highest concurrency) is UR, the highest isolation - RR. Default - CS.
 
db2 prep something.sqc isolation RS
db2 bind something.bnd isolation RR
db2 prep something.sqc bindfile using something.bnd isolation RS
db2 bind something.bnd

Locks
- mode (state) - IN (Intent None), IS (Intent Share), NS (Next Key Share), S (Share), IX (Intent Exclusive), SIX (Share with Intent Exclusive), U (Update), NX (Next Key Exclusive), NW (Next Key Weak Exclusive), X (Exclusive), W (Weak exclusive), Z (Super exclusive)
- Object,
- Duration.
 
db2 lock table test in share mode
db2 alter table test locksize table
db2 alter table test locksize row


 
More SQL hints home - top of the page - email

More SQL: temp.tables, where, order by, aliases, subqueries, column & scalar functions, group by, having, union, except, intersect
 
create table tab1 ( ... )  in userspace1;
alter table tab1 add primary key (...)
drop table tab1

 
declare global temporary table temp1
like tab1
on commit preserve rows
not logged
in mytempspace

where clause:
x = y, x <> y, x < y, x > y, x <= y, x >= y, 
IS NULL, IS NOT NULL
IN, NOT IN
BETWEEN, NOT BETWEEN
LIKE, NOT LIKE
EXISTS, NOT EXISTS

select name, salary from stuff where salary > 20000

... where years in (1,2,3)
... where years not between 3 and 8
... where name like 'S_____'
... where name like 'S%'
... order by salary desc

Aliasses can be used in the "order by":
Bad news is that the name aliases for fields (simple or calculated) can not be used in the "where" clause, because the "where" clause is processed before the alias name is applied to the result.  So you may need to repeat the whole expression in the where clause.
But good news is that the name aliases can be used in the "order by" clause.
 
select  value(x.param1, x.param2) myparam
from test1 x
where (x.param1, x.param2) >= 40
order by myparam;

Subquery:
select lname 
from employee 
where lname in (select sperson from sales where sales_date < '01/01/1996')

Column Functions:
SUM, AVG, MIN, MAX, COUNT
note: don't try to specify column functions in a where clause - they will NOT work, because where clause is evaluated before the select clause. 

select name, salary 
from employee 
where salary > (select avg(salary) from employee) 
order by salary desc

Scalar Functions:
ABS, HEX, LENGTH, YEAR, MONTH, DAY, LCASE or LOWER, UCASE or UPPER

select min(length(deptname) as min, max(length(deptname)) as max from dept

group by  &  having:
select sales_date, max(sales) as max_sales
from sales
group by sales_date where max(sales) > 25

union, except, intersect:
select sales_person from sales where region = 'reg1'
union
select sales_person from sales where sales > 3

note: union processes both queries eliminates duplicates between them, and returns the final combined result set

select sales_person from sales where region = 'reg1'
except
select sales_person from sales where sales > 3

select sales_person from sales where region = 'reg1'
intersect
select sales_person from sales where sales > 3

insert, update, delete:
insert into tab1 values (123,'something',....)
insert into tab1 select ... from ... where ..

update tab1 set (a,b) = (123,'something') where ...

delete from tab1 where ...


 
SQL_2 - recursive queries, combined outer joins home - top of the page - email

select col1, cast(round(avg(col2),2) as decimal(9,2)) as aver_col2
from tab1
group by col1
order by col1
 

recursive query:
with exp1 (col1, col2, col3) as 
(
-------- select top nodes
    select ... from tab1 where ... 

union 

-------- select other nodes recursively
    select ... from tab1, exp1 where ...

)
select ... from exp1
 

Here is an example:

with exp1 (row_id, par_postn_id, X_POSITION_NAME, PARENT_NAME) 
as (

  select 
    pp.row_id, pp.par_postn_id, 
    pp.X_POSITION_NAME, '' PARENT_NAME
  from inst1.s_postn pp
  where pp.par_postn_id is null

  union all

  select 
    tt.row_id, tt.par_postn_id, 
    tt.X_POSITION_NAME, ee.X_POSITION_NAME PARENT_NAME 
  from inst1.s_postn tt, exp1 ee 
  where ee.row_id = tt.par_postn_id

   ) 

select 
  p.PARENT_NAME, e.Last_Name, e.FST_NAME, 
  e.login, e.pager_pin as rep_id, p.X_POSITION_NAME
from 
  inst1.s_employee e, 
  inst1.s_emp_postn ep, 
  exp1 p
where e.row_id = ep.emp_id
  and ep.position_id = p.row_id
order by p.PARENT_NAME, e.Last_Name, e.FST_NAME

combined outer joins:
select empno, deptno, projname
from ( employee left outer join project on respemp=empno )
  left outer join department on mgrno = empno

 
SQL OLAP home - top of the page - email

- OLAP functions - OLAP Functions - from SQL reference
- http://www7b.software.ibm.com/dmdd/library/techarticle/lyle/0110lyle.html - good review of OLAP functions

- http://www.almaden.ibm.com/cs/people/peterh/b15.pdf - Peter J. Haas DB2 UDB Advanced Analytics for Business Intelligence
- http://www.almaden.ibm.com/cs/people/peterh/b16.pdf - Peter J. Haas Speeding up DB2 UDB Using Sampling

- http://www.ibm.com/search?en=utf&v=11&lang=en&cc=us&lv=c&q=db2+olap+function+reference - search IBM site

star schema grouping:
select empno, firstname, lastname, sum(sales) as tot_sales, (salary + bonus + comm) as compensation
from employee, sales
where sex='M' and year(sales_date) = 1996 and lastname = sales_person
group by empno, firstname, lastname, (salary + bonus + comm)

rollup grouping:
select year(sales_date) as year, count(*) as tot_sales
from sales
group by rollup (year(sales_date))

year      tot_sales
-----         -------------
 -             41
 1995        5
 1996        36

select year(sales_date) as year, region, count(*) as tot_sales
from sales
group by rollup (year(sales_date), region)

select year(sales_date) as year, region, sales_person, count(*) as tot_sales
from sales
group by rollup (year(sales_date), region, sales_person)

cube grouping:
select year(sales_date) as year, region, count(*) as tot_sales
from sales
group by cube (year(sales_date), region)

select year(sales_date) as year, region, sales_person, count(*) as tot_sales
from sales
group by cube (year(sales_date), region, sales_person)
 

other OLAP features:
some useful functions: sum, count,average, stddev, corr, regr_* rownumber, rank, windows aggregates

simple statistics:
select country, year, count(*) as count, sum(amount) as sum, avg(amount) as avg, max(amount) as max, stddev(amount) as stddev
from mytable
group by country, year

Detect transactions which are larger than usual:
create view profile(cust_id, avg_amt, sd_amt) as 
select cust_id, avg(charge_amt), stddev(charge_amt) from trans
where date between '2002-0101' and '2002-03-31'
group by cust_id

Create trigger big_chrg
after insert on trans
referencing new as newrow for each row mode db2sql
when (newrow.charge_amt > (select avg_amt + 2.0 * sd_amt from profile where profile.cust_id = newrow.cust_id))
insert into big_charges (cust_id,charge_amt)
values(newrow.cust_id, newrow.charge_amt))

Equi-width histogram:
with dt as (
  select
    t.transid,
    sum(amount) as trans_amt,
    case
        when sum(amount)/3000 < 0 then 0
        when sum(amount)/3000 >19 then 19
        else int(sum(amount)/3000)
    end as bucket
  from trans t, transitem ti
  where t.transid=ti.transid
  group by t.transid
)
select bucket,count(bucket) as height, (bucket+1)*3000 as max_amt
from dt
group by bucket;

Equi-Height histogram:
with dt as (
  select
    t.transid,
    sum(amount) as trans_amt,
    rownumber( ) over(order by sum(amount))*10/(select count(distinct transid)+1 from stars.transitem) as bucket
  from stars.trans t, stars.transitem ti
  where t.transid=ti.transid
  group by t.transid
)
select bucket,count(bucket) as b_count, max(trans_amt) as part_value
from dt
group by bucket;

Note:
There are 3 ranking functions: rank(), denserank(), rownumber().
 - row_number() & rownumber are synonyms. Also dense_rank & denserank are synonyms.
The 3 ranking functions are ranking rows, that is, they assign numbers (1,2,3, etc). If all results are different - all 3 functions produce the same result. If we have duplicates - then:
- rank() - will give them the same number - and skip the next number(s)
- denserank() - will give them the same number - and not skip the next number(s)
- rownumber() - will not give same number and will not skip. Will simply give different numbers to every row. This is the only ranking function that does not require an ordering.
 
 select empnum, dept, salary, 
   rank() over(partition by dept 
         order by salary desc nulls last) as rank, 
   dense_rank() over (partition by dept order by salary desc nulls last)as denserank, 
   row_number() over (partition by dept order by salary desc nulls last)as rownumber 
  from emptab; 

   EMPNUM  DEPT SALARY  RANK  DENSERANK   ROWNUMBER 
   ------  ---- ------  ----  ---------   --------- 
   6       1    78000   1     1           1 
   2       1    75000   2     2           2 
   7       1    75000   2     2           3 
   11      1    53000   4     3           4 
   5       1    52000   5     4           5 
   1       1    50000   6     5           6 
  -------------------------------------------------- 
   9       2    51000   1     1           1 
   4       2       -    2     2           2 

over() - to specify sets. This includes partitionning and ordering inside sets (see many examples below).
   partition by
   order by    ( asc , desc ,  nulls last )
   rows
   between ... and ...
   N preceding & N following
   unbound preceding  &  unbound following    (to include the entire preceding/following partition(s))
   current row
   range between unbound preceding and unbound following
   range between current row and unbound following
 

Example: Smoothed Time Series (over 1 week : 3 preceding days and 3 following days):
select
  date, symbol, close_price,
  avg(close_price) over(order by date rows between 3 preceding and 3 following) as smooth_cp
from stocktab
where symbol = 'IBM' and date between '1999-08-01' and '1999-09-01';
 

Attention: adding ordering to the set changes the behaviour of calculations to become "cumulative". That means, calculating on the current row - and all rows in the set that precede it with respect to the ordering.

scalar-aggregate functions - (ver.7 and up) - perform scalar calculations on values from multiple rows within a set to compute the result
 
 
    select dept, salary, 
           sum(salary) over(partition by dept) as deptsum, 
           avg(salary) over(partition by dept) as avgsal, 
           count(*) over(partition by dept) as deptcount, 
           max(salary) over(partition by dept) as maxsal 
     from emptab; 

    DEPT  SALARY  DEPTSUM  AVGSAL  DEPTCOUNT MAXSAL 
    ----- ------- -------- ------- --------- -------- 
        1   50000 383000   63833         6    78000 
        1   75000 383000   63833         6    78000 
        1   52000 383000   63833         6    78000 
        1   78000 383000   63833         6    78000 
        1   75000 383000   63833         6    78000 
        1   53000 383000   63833         6    78000 
        2       -  51000   51000         2    51000 
        2   51000  51000   51000         2    51000 
        3   79000 209000   69666         3    79000 
        3   55000 209000   69666         3    79000 
        3   75000 209000   69666         3    79000 
        -       -  84000   84000         2    84000 
        -   84000  84000   84000         2    84000

Note: the query above contains no GROUP BY clause. Instead, the OVER clause is used to partition the data so that the sum function is
  computed over rows in the same department, and the sum of all the salaries in each department is returned for each row within the department.

Adding ordering into a set turns calculations into cummulative over the set:
 
select 
  date, sales, 
  sum(sales) over(order by date) as cume_sum, 
  count(*) over(order by date) as setcount 
from sales 
where year(date) = 2000; 

    DATE       SALES        CUME_SUM     SETCOUNT 
    ---------- ------------ ------------ --------- 
    01/01/2000    968871.12    968871.12         1 
    02/01/2000     80050.05   1048921.17         2 
    03/01/2000    757866.14   1806787.31         3 
    04/01/2000     58748.13   1865535.44         4 
    05/01/2000     40711.69   1906247.13         5 
    06/01/2000    241187.78   2147434.91         6 
    07/01/2000    954924.16   3102359.07         7 
    08/01/2000    502822.96   3605182.03         8 
    09/01/2000     97201.45   3702383.48         9 
    10/01/2000    853999.45   4556382.93        10 
    11/01/2000    358775.59   4915158.52        11 
    12/01/2000    437513.35   5352671.87        12

Example for several years:
select date, sales, 
  sum(sales) over(partition by year(date) 
                  order by month(date)) as cume_sum 
from sales 
where year(date) >= 2000;

Note: the order of evaluation of a query is as follows:
    1.From Clause
    2.Where Clause
    3.Group By Clause
    4.Having Clause
    5.Select List (over...)

Example taking advantage of this order:
select 
  year(date) as year, 
  sum(sales) as sum, 
  sum(sum(sales)) over(order by year(date)) as cume_sum 
from sales 
where year(date) >= 1995 
group by year(date); 

Correlation: ( 1 (or -1) - perfect positive (or negative) relationship ):
select country, state, correlation(annual_purchases, income) as correlation
from mytab
group by country, state
having abs(correlation(annual_purchases, income)) > 0.1;

Note: similar function - covariance()

select a.custid as custid1, b.custid as custid2, corr(a.amount, b.amount) as corr
from mytab a, mytab b
where a.prodid=b.prodid and a.custid < b.custid
broup by a.custid, b.custid
having corr(a.amount, b.amount) >=0.5 and count(*) > 100
order by corr desc;
======
with
  dt (prod, year, sales0, sales1,sales2) as (
    select
      prod, year, total_sales,
      max(total_sales) over(partitionby prod order by year rows between 1 preceding and 1 preceding),
      max(total_sales) over(partitionby prod order by year rows between 2 preceding and 2 preceding)
    from mytab
  )
select
  prod,
  corr(sales0,sales1)*100 as "corr1(%)",
  corr(sales0,sales2)*100 as "corr2(%)"
from dt
group by prod;

Least-Squares Fit (Linear Regression):  y=ax+b   for a set of non-null (y,x) values:
select
  regr_count(sales, ad_budget) as num_cities,
  regr_slope(sales, ad_budget) as a,
  regr_icpt(sales, ad_budget) as b,
  regr_r2(sales, ad_budget) as r-squared
from ad_camp;

Here R-squared is a measure of the quality of the fit (roughly, the square of the corr of x and y)

If you need y = ax*x + b - use regr_slope(y,x*x)
etc.

Note: for non-linear curves you can not compute R-squared like this:
  select regr_r2(log(hits), log(days)) as r2 from traffic_data;
because it will give you 0.99, instead of 0.95.
A correct way is to use the with ... expression to:
  1. calculate the regr_slope and regr_icpt for the model
  2. calculate the residuals (differences between data and the model for each point)
  3. calculate the r2 like this:
     select 1e0 - (sum(error*error)/regr_syy(hits,days)) as r2

To find if one or two points strogly influence the model - we can use HAT Matrix.
Here is how to do the HAT Diagonal Computation:
with stats(mx,mx2,sxx) as  (
  select
    regr_avgx(sales,ad_budget),
    regr_avgx(sales,ad_budget*ad_budget),
    regr_sxx(sales,ad_budget)
  from mytab
)
select d.label as city, (s.mx2 - 2*s.mx*d.x + d.x*d.x) / s.sxx as HAT
from xy_data d, stats s
order by HAT desc;

Fit the line in the form y=ax :
We need to computer a as following: a = (x1*y1 + x2*y2 +..+xn*yn)/(x1^2 + ... + xn^2)
Note that regr_sxx = (x1-mx)^2 + ... + (xn-mn)^2
(And similarly regr_sxy)
So:
   (x1^2 + ... + xn^2) = regr_sxx + n*mx^2
   (x1*y1 + x2*y2 +..+xn*yn) = regr_sxy + n*mx*my
Thus we get:

select
  regr_count(kwh,hours_run) as num_machines,
  (regr_sxy(kwh,hours_run) + regr_count(kwh,hours_run) * regr_avgx(kwh,hours_run) * regr_avgy(kwh,hours_run))
   /
  (regr_sxx(kwh,hours_run) + regr_count(kwh,hours_run) * regr_avgx(kwh,hours_run) * regr_avgx(kwh,hours_run))
  as a
from power_data;

Example: we want to find cities where add campaign is particularly effective.
We use the with ... construct and calculate a,b, and sigma as following:
     regr_slope() as a
     regr_icpt() as b
     sqrt((regr_syy() - (regr_sxy()*regr_sxy()/regr_sxx()))/regr_count() - 2)) as sigma

where all regr functions have 2 arguments: (sales, ad_budget)
Now we can select ... where sales > a*ad_budget + b + 2e0*sigma

Same regression functions can also be used to computer other statistical data, like F-statistics.

To compare 2 sets and verify a hypothesis - people have always used t-test (which presumes normal distribution).
Or more modern procedure called Wilcoxon Rank Test which avoids above restrictions.
It is calculated using the rank() function ( or dense_rank() function for dealing with duplicate totals)
Here are how ranks are calculated:
with
  ranked_sales(city,ranks) as (
    select city, rank( ) over(order by sales) from feb_sales
  )
select sum(ranks) as W from ranked_sales where city = 'B'

Then one needs tables from some statistics books to compute theresult.

----- Removing duplicates:
create view aaatemp(rn) as select rownumber() over(partition by col1 || '_' || col2) from mytab;
delete from aaatemp where rn > 1;
drop view aaatemp;

Note: the view doesn't have any columns of the original table. Still deleting from the view does the deletion from the original table

----- Finding duplicates without creating a view:
with
  mytemp(name, desc_text, rn) as (
    select name, desc_text, rownumber() over(partition by desc_text) as rn
    from s_org_ext where cust_stat_cd !='Deleted'
  )
select * from mytemp where rn > 1

----- Delete 100 rows at a time by hand:
create view aaatemp(rn) as select rownumber() over() as rn from mytab where ....;
delete from aaatemp where rn < 100;

----- Very-very slow way to go 10 at a time:
select a.row_id from yourtable a
where 10 > (select count(*) from youtable b where a.row_id < b.row_id)
order by row_id desc

----- Paging through table:
select myname
from (select myname, rownumber() over(order by myname) as rn from mytable) as tr
where rn between 10000 and 10020

----- rank:
select name, dept, salary, rank() over(partition by dept order by salary desc) as salary rank
from staff
order by dept, salary_rank

----- moving average:
select sales_date, avg(sales) over(order by sales_date rows between 1 preceding and 1 following) as smoothed_sales
from sales
where sales_date > '03/28/1996' and sales_person = 'LEE' and region =
"some-region'

----- grouping sets:
select year(sales_date) as year, region, sales_person, count(*) as tot_sales
from sales
group by grouping sets (year(sales_date), region, sales_person, ( ))
order by year(sales_date), region, sales_person


 
More SQL: examples, with, hexadecimal, optimizer home - top of the page - email

Examples,

Some SQL examples:
select count(*)  from tab1.s_employee a 
inner join inst1.s_emp_postn b on a.row_id = b.emp_id 
where not login like 'NO-LOGIN%'

 
select count(*)  from inst1.s_employee a
inner join inst1.s_emp_postn b on a.row_id = b.emp_id 
inner join inst1.s_postn c on b.position_id = c.row_id
where 
c.created > '2002-03-12-00.00.01.000000' 
and c.created < '2002-03-17-23.59.59.999999'
and not a.login like 'NO-LOGIN%' 

 
select distinct a.login KERBEROS,
e.name POSITION ,
value(c.yes,'NO') WITH_COVERAGE 
from inst1.s_employee a
inner join inst1.s_emp_postn b on a.row_id = b.emp_id
inner join inst1.s_postn e on b.position_id = e.row_id 
left outer join (select 'YES' yes, position_id from inst1.s_accnt_postn) c on e.row_id = c.position_id 
where not a.login like 'NO-LOGIN%'

 
    select
       value(z.KERBEROS,Y.KERBEROS) MY_KERBEROS,
       value(Z.FIRST_NAME,Y.FIRST_NAME) MY_FIRST_NAME, 
       value(Z.LAST_NAME,Y.LAST_NAME) MY_LAST_NAME, 
       value(Z.REP_ID,Y.REP_ID) MY_REP_ID, 
       value(Z.DESK_NAME,Y.DESK_NAME) MY_DESK_NAME
     from
     
        select
          a.login KERBEROS, 
          a.FST_NAME FIRST_NAME, 
          a.Last_Name LAST_NAME, 
          a.PAGER_PIN REP_ID, 
          c.X_POSITION_NAME DESK_NAME 
        from
          inst1.s_employee a, inst1.s_postn b, inst1.s_emp_postn d, inst1.s_postn c 
        where
          a.row_id = d.emp_id  and d.position_id = b.row_id and b.PAR_POSTN_ID = c.row_id  and  a.pager_pin is not null 
      ) y
      left outer join

     
        select
          a.login KERBEROS, 
          a.FST_NAME FIRST_NAME, 
         a.Last_Name LAST_NAME,
          a.PAGER_PIN REP_ID, 
          c.X_POSITION_NAME DESK_NAME
        from
          inst1.s_employee a, inst1.s_postn b, inst1.s_postn c 
        where
          a.PR_POSTN_ID = b.row_id and b.PAR_POSTN_ID = c.row_id and a.pager_pin is not null 
      ) z
     on y.KERBEROS = z.KERBEROS
  where value(z.KERBEROS,Y.KERBEROS) not in ('something1','something2') 
  ORDER BY MY_REP_ID


 
select col1 from tab1 group by col1 having count(*) > 1

select * from tab1 where lcase(col1) = 'o''brien'

Some (non)-trivial examples:
In Sybase we frequently use temp. tables and stored procedure when we need to get a result from many tables.
For example,

create temp table #temp1
insert #temp1 select ... -- populate some of its columns with data
update #temp1 set ...  -- populate other columns
update #temp1 set ... -- again
update #temp1 set ...  -- again
select ... from #temp1 where ...

in DB2 this can be all expressed as one SQL statement like this:

with query1 ( col1, col2, col3, ..., colN) as ( select ... ),
       query2 (col, ....) as (select .. from query1 left outer join ... on ... ),
       query3 (col, ....) as (select .. from query2 left outer join ... on ... ),
       query4 (col, ....) as (select .. from query3 left outer join ... on ... )
select  ..... from query4 where ...

---------------------------- one more query using the 'with' construct

with
  myKid (kid) as (
    select user_id from ... where ...
    union
    select kid from ... where ...
  ),
  myAccountIDs (AccountId) as (
    select distinct AccountId from myKid t, ... where ...
  ),
  mySomething (col1, col2, ...) as (
    select ... from ... where ...
  )
select ... from ... where ...
 

---------------------------- return 1 if something exists, otherwise return 0

with t1 (is_mgr) as (
  select 1 is_mgr from table (values 1) as t2
  where exists (select 1 from ... where ...)
  union
  select 0 is_mgr from table (values 0) as t2
)
select * from t1 order by is_mgr desc fetch first 1 rows only

---------------------------- 

select distinct ... , cast(NULL as char) as first_name, cast(NULL as char) as last_name
from tab1, table (select ... from ... where ... ) as T
where ... = cast(substr(T.user_id,1,locate('_', T.user_id) -1_ as int)

----------------------------

select col1 from tab1 group by col1 having count(*) > 1

select * from tab1 where lcase(col1) = 'o''brien'

---------------------------- 

Get top ten:

select name, salary from employee a
where 10 > (select count(*) from employee b where a.salary < b.salary)
order by salary desc

select name, salary from employee a order by salary desc fetch first 10 rows only
 

---------------------------- 

Get list of pairs with equal values:

select a.name, a.birthday, b.name
from employee a, employee b
where a.birthday = b.birthday
and a.emp_no > b.emp_no

 

Working with hexadecimal:
 
values( hex(1024) )
values ( X'3233' )
values ( cast(X'3233' as integer) + 2 )
values ( cast(X'3233' as char(2)) || ' - twenty three' )
values ( X'4672616E6B' )
-- Frank
values ( X'30' )
-- 0
values ( X'30313233' )
-- 0123

-- now let's get names with a digit 8 in them:
select name from siebel.s_org_ext 
where name like '%' || X'38' || '%'
fetch first 10 rows only

Optimizing queries:

db2 -tvf test.sql
db2 runstats on table mytab with distribution and detailed indexes all shrlevel change
 
visual explain A graphical tool available in the db2  Command Center (or Control Center) - to examine single queries, whether static or dynamic. Shows a color-coded tree. Clicking on a node allows you to view the arguments, statistics, and cost estimate of the node. You can also jump directly to DB2's documentation on that node type. Visual Explain can also be run from the command line as db2vexp.exe, though it then lacks static SQL ability.
db2expln The "bare bones" tool, giving text output from static SQL packages only. This can optionally include a character mode graph.
dynexpln Gives a text-mode analysis of a dynamic SQL query. It actually packages the dynamic query and calls db2expln to do the work.  From unix command line invoke it as:
  dynexpln -d mydb -f test.sql
  dynexpln -h
db2exfmt  A formatter for previously stored explain data.

- http://www.idug.org/member/journal/Nov01/articl05.cfm -
- http://www.student.math.uwaterloo.ca/~cs448/db2_doc/html/db2n0/frame3.htm -
- http://gethelp.devx.com/techtips/db2_pro/10Min1101/gn1101-2.asp -
- http://www.cs.umb.edu/~db2/db2n0/db2n0.htm -  IBM  DB2  Command  Reference -

--------------------------------------------