Information Management Ecosystem Partnerships

IBM Canada Lab

Summer/Fall 2010

Working with Databases and Database

Objects in DB2

Information Management

2010 IBM Corporation

Information Management

Agenda

Servers, Instances, and Databases

Managing Instances

Creating a DB2 Database

Cataloging

Database (Data) Objects

2010 IBM Corporation

Information Management

2010 IBM Corporation

Information Management

Servers, Instances, and Databases

DB2 views the world as a hierarchy of objects
Server

..upon installation, the DB2 Database Manager

(program files) are copied to the server, and an
instance of the DB2 Database Manager is created.

..instances are responsible for managing system

resources and databases that fall under their control.

..databases are responsible for managing the

storage, modification, and retrieval of data.

INSTANCE is a logical database server

environment.

Instance

Database
1

Database
2

2010 IBM Corporation

IB M D B 2 9

Information Management

M o re o n in s ta n c e s
T h e d e fa u lt in s t a n c e f o r a p a r t ic u la r s y s t e m is d e f in e d b y th e
D B 2 IN S T A N C E e n v ir o n m e n t v a r ia b le . I n itia lly f o r W in d o w s
d e f a u lt is D B 2 a n d f o r L in u x / U n ix it is d b 2 in s t 1 .
D B 2 9 In s ta n c e M a n a g e m e n t C o m m a n d s :

4
5

C om m and

P u rp o s e

d b 2 ic r t [In s ta n c e N a m e ]

C re a te s a n e w in s ta n c e

d b 2 id r o p [In s ta n c e N a m e ]

D e le te s ( d r o p s ) a n e x is tin g in s ta n c e

d b 2 ilis t

L is ts a ll in s ta n c e s th a t h a v e b e e n d e fin e d

d b 2 s ta rt

S ta rts th e D B 2 D a ta b a s e M a n a g e r b a c k g ro u n d
p r o c e s s e s f o r th e c u rr e n t in s ta n c e

d b 2 s to p

S to p s th e D B 2 D a ta b a s e M a n a g e r b a c k g ro u n d
p r o c e s s e s f o r th e c u rr e n t in s ta n c e
2010 IBM Corporation

Information Management

Instances

Stand-alone DB2 environment

Can have multiple instances per data

server

All instances share the same executable

binary files

Each instance has its own configuration

Different software level for an instance

Command

Description

Example

db2start

Start the default instance

db2start

db2stop

Stop the current instance

db2stop -f

db2icrt

Create an instance

db2icrt u db2fenc1 db2inst1

db2idrop

Drop an instance

db2idrop f db2inst1

db2ilist

List all instances

db2ilist

db2imigr

Migrate an instance after upgrading DB2

db2imigr u db2fenc1 db2inst1

db2iupdt

Update an instance after installation of a fix pack

db2iupdt u db2fenc1 db2inst1

2010 IBM Corporation

Information Management

DB and DBM configurations

Description

Example

View Database Manager Settings

db2 get dbm cfg show detail

Change a Database Manager Setting

db2 update dbm cfg using <parameter> <value>

Description

Example

View Database Settings

db2 get db cfg for <database>

db2 connect to <database>
db2 get db cfg show detail

Change a DB Setting

Change value of logretain &

userexit db config parameters

db2 update db cfg using logprimary 10

Retrieving original value (on disk)

and updated value (in memory)
Querying the resulting global
Temporary table (DB_CONFIG)

UPDATE DB CFG USING LOGRETAIN RECOVERY USEREXIT ON

CALL SYSPROC.GET_DB_CONFIG()
SELECT DBCONFIG_TYPE, LOGRETAIN, USEREXIT FROM SESSION.DB_CONFIG

Result:

2010 IBM Corporation

Information Management

Database Storage Model

2010 IBM Corporation

Information Management

DB2 Storage Model

LOGICAL STORAGE MODEL

TABLES are made up of COLUMNS and ROWS
TABLES are placed into table spaces
TABLE SPACE can have more then one table

PHYSICAL STORAGE MODEL

CONTAINER is a physical storage device
Can be a device , directory or a file
Container is assigned to table space

2010 IBM Corporation

Information Management

TABLE SPACES

TABLE SPACES is a logical space between data and storage

devices.
Consists of more then one containers.
Tables, regular data, indexes, and long data resides in table
spaces.
SQL: CREATE / ALTER / DROP TABLESPACES
To display tablespaces info:
LIST TABLESPACES [ SHOW DETAIL]

Details about containers can be obtained with a following

command.
LIST TABLESPACE CONTAINERS for tablespace_id
[SHOW DETAIL]

10

2010 IBM Corporation

Information Management

DB2 Storage:: Table Spaces Overview

11

Logical objects in between logical

table and physical containers

Allows assignment of the location

of data to particular logical devices
or portions thereof

All tables, indexes, and other data

are stored in a table space

Can be associated to a specific

buffer pool

Containers can be files,

directories or raw devices

2010 IBM Corporation

Information Management

TABLESPACE STORAGE MANAGEMENT

System Managed Space (SMS):

OS's FILE SYSTEM MANAGER allocates and manages the
space.

Database Managed Space (DMS):

Here, the Database manager controls the storage space.

12

Automatic Storage with DMS:

Databases are enabled for automatic storage have a set of
one or more storage paths associated with them.
A table space can be defined as managed by automatic
storage and its containers assigned and allocated by DB2
based on those storage paths.
You cannot enable/disable storage once it has been defined
for database.
Default type in DB2 9

2010 IBM Corporation

Information Management

DB2 Storage:: Table Space Management

System Managed Spaces (SMS)

Data stored in files representing data objects
Space is allocated on demand
Access to data controlled using standard I/O functions of the OS

Ideal for small, personal databases and databases that grow/shrink rapidly
Low maintenance and monitoring
CREATE TABLESPACE tbsp1 MANAGED BY SYSTEM
USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')

Database Managed Spaces (DMS)

Data stored in files or on raw devices
Storage space pre-allocated in file system, typically contiguous
physically

Ideal for performance-sensitive applications

Increased maintenance and monitoring
CREATE TABLESPACE tbsp2
PAGESIZE 8K MANAGED BY DATABASE
USING (FILE ' /storage/dms1' 10 M) AUTORESIZE YES

13

2010 IBM Corporation

Information Management

DB2 Storage:: Table Space Management

Automatic Storage Table Space

DBM creates and extends containers as needed up the
limits imposed by the storage paths associated with the
database
Automatically handles resizing table spaces
Creates a DMS table space for regular/large table spaces
Creates a SMS table space for user or system temporary
table spaces
New DB & TBSP
are handled by automatic storage
by DEFAULT

CREATE DATABASE mydb AUTOMATIC STORAGE YES

CONNECT TO mydb
CREATE TABLESPACE tbsp1 MANAGED BY AUTOMATIC STORAGE
14

2010 IBM Corporation

Information Management

DB2 Storage:: Buffer Pools

Area of main memory used to cache table

and index data

Each database must have at least one

buffer pool
By default IBMDEFAULTBP is used
Buffer pools can be created, dropped or altered
SYSCAT.BUFFERPOOLS catalog view
accesses the information for the buffer pools
defined in the database

Every table space associates a specific

buffer pool of the same page size
Match buffer pool size with purpose of table
to increase hit ratio

Self-Tuning Memory Manager (STMM)

available
CREATE BUFFERPOOL bp4k PAGESIZE 4K
CREATE TABLESPACE tbsp1 PAGESIZE 4K BUFFERPOOL bp4k

15

2010 IBM Corporation

Information Management

Databases

What makes up a DB2 database?

A DB2 database is made up of a collection of objects
A database contains the following objects:
Tables, views, indexes, schemas
Locks, triggers, stored procedures, packages
Buffer pools, log files, table spaces

Which tools can help you create DB2 databases?

First Steps
Control Center (GUI wizard)
Command Line Processor (CLP)

16

2010 IBM Corporation

Information Management

Creating a DB2 Database First Steps

17

As part of the DB2

installation process, the
First Steps panel is
displayed allowing the
user to generate a
number of a sample
databases to work with

To launch the first steps

interface issue db2fs
from a DB2 command
line

2010 IBM Corporation

Information Management

Typical Directory Hierarchy Tree

Location specified when the database was created OR the
value of the dftdbpath DBM configuration parameter.

DATABASE_PATH
INSTANCE_NAME

Directory with the name of the instance that controls the database.

Directory with the name of the node number assigned to this

partition (always NODE0000 if database is nonpartitioned).
Directory with the name that was assigned to the
DATABASE_NAME
database.

NODExxxx

T0000000
T0000001
C0000000.TMP

Directories containing file or sub-directory

containers for the SYSCATSPACE, TEMPSPACE1,
and USERSPACE1 table spaces.

T0000002
Database directory (name matches the database
token assigned to the database).

SQL0000x
DB2EVENT

Directory for event monitor data.

SQLOGDIR

Directory for transaction log files.

Files needed for database recovery and

bookkeeping tasks,

18

2010 IBM Corporation

Information Management

Creating a DB2 Database Command Line

The CREATE DATABASE command initializes a new database

>>-CREATE--+-DATABASE-+--database-name----------------------->...

Examples:
CREATE DATABASE TESTDB1

database-name

CREATE DATABASE TESTDB2 ON C:

CREATE DATABASE TESTDB3

AUTOMATIC STORAGE YES
ON C:,D: DBPATH ON E:

Database is
created on drive C:

Database is
created on drive E:
Automatic store is enabled and
storage paths are C: and D:

19

2010 IBM Corporation

Information Management

With Database, DB2 automatically creates

20

The three table spaces are created:

SYSCATSPACE: This is where DB2 system catalog is kept

that tracks all of the metadata associated with DB2 objects.

TEMPSPACE1- A temporary work area where DB2 can

place intermediate results.

USERSPACE1- a place where all user objects (tables,

indexes) resides by default.

Configuration information (DB CFG)

Log files, History files etc

2010 IBM Corporation

Information Management

With databases, DB2 automatically creates...

System temporary
table space
TEMPSPACE1
(1 required)

System temporary space

Work area for operations,

for example: join, sorts

Catalog table spaces

SYSCATSPACE
(1 required)

Configuration
Information (DB CFG)

Log Files

Catalog tables with metadata

History Files

Must exist

Etc.

User Table Spaces

USERSPACE1
(1 or more required)

21

Default user table space

Can be deleted

All user defined tables

2010 IBM Corporation

Information Management

DB2 Data Server Clients

IBM Data Server Runtime Client

JDBC, ADO.NET, OLE DB, ODBC, CLI, PHP, and

Ruby

IBM Data Server Drivers

Light weight deployment solution for ISVs

Must be installed manually

Has CLP but GUI tools are not included

Support LDAP exploitation, TCP/IP and Named

Pipe, cataloging

IBM Data Server Client

All the functionality of IBM Data Server Runtime

Client

22

Plus functionality for database administration,

application development, and client/server
configuration.

IBM Data Server Driver for JDBC and SQLJ

Java stored procedures and userdefined functions
JDBC, SQLJ
IBM Data Server Driver for ODBC and CLI
ODBC API, or CLI API
IBM Data Server Driver Package
ODBC, CLI, .NET, OLE DB, PHP,
Ruby, JDBC, or SQLJ

Capabilities include GUI tools such as

configuration assistant, control center, visual
studio tools

2010 IBM Corporation

Information Management

Cataloging

DB2 has multiple directories that are used to access

databases

The system database directory contains a list and

pointer to where all the known databases can be found.
(list db directory)
The node directory contains information relating to how
and where remote systems or instances can be found.
( list node directory)
The Database Connection Services (DCS) Directory
contains information relating to how and where
databases on DRDA systems can be found.
( list dcs directory)

23

2010 IBM Corporation

Information Management

Ways of Cataloging a Database at a Client

Automated configuration using discovery

Search discovery client searches server on the network
Known discovery one particular server is queried for
information

Automated configuration using access profiles

Server profiles contains information on instances and
databases
Client profiles used to duplicate information from one client to
another

Manual configuration
Configuration Assistant (graphical)
Data Studio (graphical)
Command Line Processor (CLP)

24

2010 IBM Corporation

Information Management

Cataloguing:: Setting Up Communications

To use a remote database:

1. Catalog the remote system (node)
2. Catalog the database within the remote node
3 tools available:
Configuration Assistant (graphical)
Data Studio (graphical)
Command Line Processor (CLP)
alias

db2 list [database|node]

directory
can be used to find the locally
catalogued DB or node

host_name or ip_addr

catalog tcpip node db2_node remote mysystem server

db2tcp42
service_name
or port_number

database_name

database_alias

node_name

catalog database sample as mysample at node db2node

authentication server
25

authentication_type

2010 IBM Corporation

Information Management

Database (Data) Objects

Database objects, also known as data objects, are used to

control how all user data (and some system data) is stored
and organized within a DB2 database

Tables

Schemas

Indexes

Aliases

User-Defined
Functions

26

Views

Sequences

Stored
Procedures

Triggers

Packages

2010 IBM Corporation

Information Management

Database Object : SCHEMA

27

2010 IBM Corporation

Information Management

Schemas

Schemas (unique identifiers) are objects that are used to

logically classify and group other objects in the database.

Schemas have privileges associated with them that allow the

schema owner to control which users can create, alter, and
drop objects within them.

Benefits of a schema:
Tedious to search through entire database for objects with the
same name
The name of each object needs to be unique only within its schema

Access control

When an object is created without specifying a schema, object will

be associated with an implicit schema using the authorization ID.

28

2010 IBM Corporation

Information Management

Schemas

Most database objects have a two-part object name

The first part being the schema name (or qualifier)
The second is the object name

When an object is created, you can assign it to a specific schema

SCHEMA_NAME.OBJECT_NAME

For example
CREATE SCHEMA PAYROLL
CREATE TABLE PAYROLL.STAFF
Table named STAFF is
assigned to the PAYROLL schema

29

2010 IBM Corporation

Information Management

Schemas Example :: Using the command line

CREATE SCHEMA payroll AUTHORIZATION user1;

Schema
name

Creates a schema for an

individual user with the
authorization ID "USER1"

COMMENT ON SCHEMA payroll IS 'schema for payroll

application';
Schema to
comment on

30

Comment
string

2010 IBM Corporation

Information Management

Tables

A relational database presents data as a collection of tables

A table consists of data logically arranged in columns and

rows (records)
each column contains values of the same data type
each row contains a set of values for each column available
Value

The storage representation

of a row is called a record Record
(Row)

31

the storage representation

of a column is called a field
each intersection of a row
and column is called a value

DEPARTMENT Table
DEPTID

DEPTNAME

COSTCENTER

A000

ADMINISTRATION

10250

B001

PLANNING

10820

C001

ACCOUNTING

20450

D001

HUMAN RESOURCES 30200

E001

R&D

50120

E002

MANUGACTURING

50220

E003

OPERATIONS

50230

Field
(Column
)

2010 IBM Corporation

Information Management

Tables 3 Main Types of Tables

Base Tables
Userdefined tables designed to hold persistent user data

CREATE TABLE department

column-name:
(deptid
CHAR(4),
names a column of
deptname
VARCHAR(30),
the table
costcenter INTEGER);

table-name
data-type

Result Tables
DB2 Database Managerdefined tables populated with rows retrieved
from one or more base tables in response to a query

Declared Temporary Tables

Userdefined tables used to hold nonpersistent data temporarily, on behalf
of a single application.
Explicitly created by an application when they are needed and implicitly
destroyed when the application that created them terminates its last
database connection.
Created with DECLARE GLOBAL TEMPORARY TABLE statement

32

2010 IBM Corporation

Information Management

Views

Views can be seen as virtual tables derived from one or more

tables or views
Created to limit access to sensitive data or group together data
from different tables in a single object.
Views do not contain real data.
Only the view definition itself is actually stored in the database

Can be deletable, updatable, insertable, and read-only.

When changes are made to data through a view, the data is

changed in underlying table itself.

Can be used interchangeably with tables when retrieving data.

33

CREATE VIEW dept_view AS SELECT * FROM department

2010 IBM Corporation

Information Management

Views Example :: Simple view referencing two base tables

view-name

Defines the
view

T1

CREATE VIEW myview(empid, name, instname) AS

SELECT T1.empid, T1.name, T2.name
Identifies the view
FROM T1, T2
definition
WHERE T1.instid=T2.instid

EMPID

T2
NAME

INSTID

INSTID

NAME

001

JAGGER, MICK

H01

H01

HARMONICA

002

RICHARDS, KEITH

G01

G01

GUITAR

003

WOOD, RONNIE

G01

D01

DRUMS

004

WATTS, CHARLIE

D01

B01

BASS GUITAR

005

WYMAN, BILL

B01

K01

KEYBOARD

006

JONES, BRIAN

G01

S01

SAXAPHONE

myview

34

column-name(s)

EMPID

NAME

INSTNAME

001

JAGGER, MICK

002

RICHARDS, KEITH GUITAR

003

WOOD, RONNIE

GUITAR

004

WATTS, CHARLIE

DRUMS

005

WYMAN, BILL

BASS GUITAR

006

JONES, BRIAN

GUITAR

HARMONICA

2010 IBM Corporation

Information Management

Indexes

An index is an object that contains an ordered set of pointers

that refer to rows in a base table. They are based upon one
or more columns but stored as a separate entity.
DEPARTMENT Table
DEPTID

DEPTID Index

35

DEPTID

ROW

A000

B001

C001

D001

11

E001

E002

E003

F001

F002

F003

G010

10

Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7
Row 8
Row 9
Row 10
Row 11

DEPTNAME

COSTCENTER

F001

ADMINISTRATION

10250

B001

PLANNING

10820

E001

ACCOUNTING

20450

E003

HUMAN RESOURCES

30200

A000

R&D

50120

E002

MANUGACTURING

50220

F003

OPERATIONS

50230

C001

MARKETING

42100

F002

SALES

42200

G010

CUSTOMER SUPPORT 42300

D001

LEGAL

60680

2010 IBM Corporation

Information Management

Indexes Importance

..provide a fast, efficient method for locating specific rows of

data in very large tables.

..provide a logical ordering of the rows of a table

36

..can be used to enforce the uniqueness of records stored in

a table.

..can force a table to use clustering storage, which causes

the rows of a table to be physically arranged according to the
ordering of their index column values.

CREATE UNIQUE INDEX empno_indx ON

employees(empno)

2010 IBM Corporation

Information Management

Indexes Example

Suppose you had the following EMPLOYEES base table..

Column
Name ...

Data Type ...

EMPNO

INTEGER

FNAME

CHAR(20)

LNAME

CHAR(30)

TITLE

CHAR(10)

DEPARTMENT

CHAR(20)

SALARY

DECIMAL(6,2)

..and you wanted to create an index such that the index key
consists of the column named EMPNO and all employee numbers
entered will be guaranteed to be unique..

37

2010 IBM Corporation

Information Management

Indexes Example (continued)

UNIQUE prevents the table from
containing two or more rows with the
same value of the index key

CREATE UNIQUE INDEX empno_indx

ON employees(empno)

table-name identifies a table on

which an index is to be created

38

column-name identifies a column

that is to be part of the index key

2010 IBM Corporation

Information Management

Example of INDEX

CREATE TABLE parts ( part_no INT (9) NOT NULL,

part_name VARCHAR(24), pert_remain INT (9))

Creating an INDEX on column part_no

CREATE UNIQUE INDEX idx_partno ON parts(part_no)

39

2010 IBM Corporation

Information Management

Aliases

An alias is an alternate name for an object such as a table or

view.

Like other objects, an alias can be created, dropped, and have

comments associated with it.

Aliases are publicly referenced names, so no special authority or

privilege is required to use them.
However, access to the table or view that an alias refers to still
requires appropriate authorization.

40

Aliases can be created by executing the CREATE ALIAS SQL

statement

2010 IBM Corporation

Information Management

Aliases

Example
..you wanted to create an alias that references a table named
EMPLOYEES and you wanted to assign it the name EMPINFO..
CREATE ALIAS empinfo FOR employees
Names the alias. The name must not
identify a table, view, nickname, or alias
that exists in the current database.

Identifies the table,

view, nickname, or
alias for which aliasname is defined.

Why use an alias instead of the actual object name?

So that SQL statements can be constructed such that they are
independent of the qualified names that identify the base tables or
views they reference.

41

2010 IBM Corporation

Information Management

SEQUENCE

Sequence is an object that used to generate data values

automatically.

Default increment value is 1

PREVIOUS VALUE expression returns the most recent generated

generated value for sequence.

NEXT VALUE expression returns the next sequence value.

MAXVALUE of a sequence can be equal to MINVALUE.

42

CREATE SEQUENCE emp_id START WITH 100 INCREMENT BY 10

CACHE 5

2010 IBM Corporation

Information Management

Sequences

A sequence is an object that is used to generate data values

automatically. Unlike an identity column, a sequence is not
tied to any specific column or any specific table.
An identity column provides a way for DB2 to automatically
generate a unique numeric value for each row that is added to the
table.

Sequences have the following characteristics:

Values generated can be any exact numeric data type that has a
scale of zero (SMALLINT, BIGINT, INTEGER, or DECIMAL).
Consecutive values can differ by any specified increment value.
Counter values are recoverable. Counter values are reconstructed
from logs when recovery is required.
Values generated can be cached to improve performance.

43

2010 IBM Corporation

Information Management

Sequences

Sequences can generate values in one of three ways:

By incrementing or decrementing by a specified amount, without
bounds
By incrementing or decrementing by a specified amount to a
userdefined limit and stopping
By incrementing or decrementing by a specified amount to a
userdefined limit, and then cycling back to the beginning and
starting again

Example: if you wanted to create a sequence that generates

numbers, starting with the number 100 and incrementing
each subsequent number produced by 10
CREATE SEQUENCE emp_id START WITH 100 INCREMENT BY 10
Names the sequence
Specifies the first
value for the sequence

44

Specifies the interval

between consecutive
values of the sequence
2010 IBM Corporation

Information Management

Example of SEQUENCE
CREATE SEQUENCE my_seq START WITH 5 INCREMENT BY 5 CACHE 5

User1 successfully executes the following statements in Connection1:

VALUES NEXT VALUE FOR my_seq INTO :con1hvar

VALUES NEXT VALUE FOR my_seq INTO :con1hvar

User2 successfully executes the following statement in Connection2:

VALUES NEXT VALUE FOR my_seq INTO :con2hvar

After User1 & User2 are finished, User3 executes the following statement in
Connection3:
SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1

What will be output if above query is executed:

Answer: 55
45

2010 IBM Corporation

Information Management

Example of SEQUENCE
CREATE SEQUENCE my_sequence CACHE 10 ORDER

The following statements are successfully executed in sequence

through separate database connections:

CONNECTION1 - VALUES NEXT VALUE FOR my_sequence INTO :con1hvar

CONNECTION2 - VALUES NEXT VALUE FOR my_sequence INTO :con2hvar
CONNECTION1 - VALUES NEXT VALUE FOR my_sequence INTO :con1hvar

Question : What is the current value of the :con1hvar host

variable?

ANSWER: 3

46

2010 IBM Corporation

Information Management

Sequence Example
Consider the following scenario. We have a table, table_1, which has only one column
storing an integer value. The following statements are executed successfully in the orders
shown:
CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 CACHE 5

INSERT INTO table_1 VALUES (NEXT VALUE FOR my_seq);

INSERT INTO table_1 VALUES (NEXT VALUE FOR my_seq);

After a db2 terminate and reconnection to the database, the following statement was
issued:
INSERT INTO table_1 VALUES (NEXT VALUE FOR my_seq);
SELECT * FROM table_1

What value will be the last value returned by the query?

Ans: 6
47

2010 IBM Corporation

Information Management

Triggers

48

A trigger defines a set of actions that are performed in

response to an insert, update, or delete operation on a
specified table.

Like constraints, triggers are often used to enforce data integrity

and business rules.

Unlike constraints, triggers can also be used to update other

tables, automatically generate or transform values for inserted or
updated rows, and invoke functions to perform tasks such as
issuing errors or alerts.

Using triggers places the logic that enforces business rules inside
the database.

Ex.

CREATE TRIGGER pay_raise BEFORE UPDATE ON

employees FOR EACH ROW
SET new.salary = salary * 1.1
2010 IBM Corporation

Information Management

Example of TRIGGER
CREATE TABLE tab1 (col1 INT);
CREATE TABLE tab2 (col1 INT);
CREATE TRIGGER trig1 AFTER UPDATE ON tab1
REFERENCING NEW AS new1
FOR EACH ROW MODE DB2SQL
INSERT INTO tab2 VALUES(new1.col1);

INSERT INTO tab1 VALUES(2),(3);

What will be the output :

SELECT count(*) FROM tab2

Output:0
49

2010 IBM Corporation

Information Management

Triggers Example

Suppose you had the following EMPLOYEES base table..

Column Name ...

Data Type ...

EMPNO

INTEGER

FNAME

CHAR(20)

LNAME

CHAR(30)

TITLE

CHAR(10)

DEPARTMENT

CHAR(20)

SALARY

DECIMAL(6,2)

..and you wanted to create a trigger for EMPLOYEES that will

cause the value for the column named EMPNO to be incremented
each time a row is added to the table

50

2010 IBM Corporation

Information Management

Triggers Example cont'd

The action is to be applied after
the changes caused by the
actual update of the subject table

Names the
trigger

CREATE TRIGGER empno_inc

The action is to be
applied once for each row
AFTER INSERT ON employees
affected by the trigger
FOR EACH ROW
UPDATE empno SET empno = empno + 1

Specifies the action to be performed

when a trigger is activated

51

2010 IBM Corporation

Information Management

User-defined Functions

52

Userdefined functions (UDFs) are special objects that are

used to extend and enhance the support provided by the
builtin functions available with DB2.

Unlike DB2's builtin functions, userdefined functions can take

advantage of system calls and DB2's administrative APIs

User-defined functions are created (or registered) by executing

the CREATE FUNCTION SQL statement.

SQL Scalar, Table, or Row. Constructed using only SQL

statements and can return a value, row or table.

External Scalar/Table. Written using a high-level programming

language such as C, C++, or Java and returns a single value or
table.
2010 IBM Corporation

Information Management

Stored Procedures

An SQL stored procedure is an ordinary program composed

entirely of SQL statements that can be called by an
application.

Stored procedures can be called locally or remotely

An external stored procedure is a stored procedure that is written

using a high-level programming language
External stored procedures can be more powerful than SQL stored
procedures because they can take advantage of system calls and
administrative APIs along with SQL statements.

53

2010 IBM Corporation

Information Management

Stored Procedures Advantages / Benefits

Reduces network traffic

Access to features that exist only on the server

Enforcement of business rules

A remote stored procedure provides the most advantages:

It splits the application logic and encourages an even distribution
of the computational workload
It provides an easy way to call a remote program

54

2010 IBM Corporation

IB M D B 2 9

Information Management

C a llin g a S to r e d P r o c e d u r e
C R E A T E P R O C E D U R E g e t_ s a le s
(IN q u o ta IN T E G E R , O U T re tc o d e C H A R (5 ))
D Y N A M IC R E S U L T S E T S 1 L A N G U A G E S Q L
B E G IN
D E C L A R E s q ls ta te C H A R ( 5 ) ;
D E C L A R E s a le s _ r e s u lt s C U R S O R W IT H R E T U R N F O R
S E L E C T s a le s _ p e r s o n , S U M (s a le s ) A S to ta l_ s a le s
F R O M s a le s G R O U P B Y s a le s _ p e r s o n
H A V IN G S U M ( s a le s ) > q u o t a ;
D E C L A R E E X IT H A N D L E R F O R S Q L E X C E P T IO N
S E T r e tc o d e = s q ls ta te ; O P E N s a le s _ re s u lts ;
S E T r e tc o d e = s q ls ta te ;
EN D
C A L L g e t _ s a le s ( 2 5 , ? )
43

55

2010 IBM Corporation

IB M D B 2 9

Information Management

D a ta b a s e O b je c ts P a c k a g e s
A p a c k a g e is a n o b je c t th a t c o n ta in s in fo rm a tio n n e e d e d to p r o c e s s S Q L
s t a t e m e n t s a s s o c ia t e d w it h a s o u r c e c o d e f ile o f a n a p p lic a t io n p r o g r a m .
D u r in g th e p r e - c o m p ile p r o c e s s ,
- a s o u r c e c o d e f ile c o n t a in in g e m b e d d e d S Q L s t a t e m e n t s is c o n v e r t e d
in t o a s o u r c e c o d e f ile t h a t is m a d e u p e n tir e ly o f h ig h - le v e l p r o g r a m m in g
la n g u a g e s ta te m e n ts .
- A t t h e s a m e tim e , a c o r r e s p o n d in g p a c k a g e t h a t c o n t a in s t h e a c c e s s
p la n s t h a t w ill b e u s e d t o p r o c e s s e a c h S Q L s t a t e m e n t e m b e d d e d in t h e
s o u r c e c o d e f ile is a ls o p r o d u c e d .
- T h is p a c k a g e m u s t r e s id e in a D B 2 d a ta b a s e th a t c o n ta in s th e d a ta
o b je c ts r e f e r e n c e d b y th e p a c k a g e b e f o r e th e c o r r e s p o n d in g a p p lic a tio n
c a n b e e x e c u te d a g a in s t th a t d a ta b a s e .
T h e p r o c e s s o f c r e a tin g a n d s to r in g a p a c k a g e in a D B 2 d a ta b a s e is k n o w n
a s b i n d in g , a n d b y d e f a u lt , p a c k a g e s a r e a u t o m a tic a lly b o u n d t o a
d a t a b a s e d u r in g t h e p r e c o m p ile p r o c e s s .
4 56
4

2010 IBM Corporation

Information Management Ecosystem Partnerships

IBM Canada Lab

Questions?

Summer/Fall 2010

E-mail: imschool@us.ibm.com
Subject: DB2 Academic Workshop

Information Management

2010 IBM Corporation