DB2基础

– Declare temporary table
defines a declared temporary table for the current application process and instantiates an empty instance of the table for the process.
Benefits of partitioned TABLESPACE
– process can be limited to the distressed partitions (e.g.
REORG/RECOVER)
DB2 Object
TABLESPACE
Naming convention
– TABLESPACE name qualify by the
Access Environment
– TSO, CICS, Batch, IMS/TM
Language
– Structured Query Language (SQL) Data Manipulation Language (DML) e.g. SELECT, INSERT, UPDATE & DELETE Data Definition Language (DDL) e.g. CREATE, DROP, ALTER Control Statements e.g. GRANT, REVOKE
PACKAGE / PLAN
DB2 Programming
Handling of SQL
– Static the access path is developed when BIND process – Dynamic checks authorization & develop best access path during execution
DB2 Objects
Logical Concept
– TABLE, INDEX and VIEW
Physical Concept
– TABLESPACE, DATABASE, STOGROUP,
System Related
– BUFFERPOOL
Application Logic
– TRIGGER, TEMPORARY TABLE
DB2 Fundamentals I
Overview
DB2 Objects Access Environment & Language DB2 Programming Locking in DB2 DB2 Security Tools & Utilities DB2 Messages Further Reading
DB2 Plan
– Relate an application process to DB2. It can be A list of package name Bound form of SQL statements from DBRM – Every DB2 application requires an application
Locking in DB2
Lock Attributes
Lock Mode
– Shared (SELECT) – Exclusive (UPDATE, INSERT, DELETE)
Lock size
– – – –
PAGE & TABLE TABLE PAGE & TABLESPACE TABLESPACE
DB2 Programming
Coding in program
– embedded SQL in program – each statement is delimited (e.g. for COBOL
EXEC SQL .... END-EXEC)
data values can be gathered from host program variables
edp1wwy.table1)
DB2 Object
INDEX
Index build on table Naming convention
– INDEX name qualify by owner (e.g.
edp1wwy.index1)
DB2 Object
VIEW
A logical view on TABLE or TABLE(s) Naming Convention
– INSERT rules - must match parent table – UPDATE rules - must match parent table – DELETE rules - restrict, cascade & set null
Access Environment & Language
– VIEW name qualify by the owner (e.g.
edp1wwy.view1)
DB2 Object
TABLESPACE
VSAM contain one or more TABLE(s) divided into 4K/32K blocks called PAGE(s) unit of I/O transmission is a PAGE PAGE contain one or many ROW(s) ROW must in same PAGE
of PAGE – all ROWs in a segment belonging to one TABLE
Partitioned
– Contain Single table – Subdivided into partitions based on key range of a
nominated partitioning indexes
DB2 Object
TABLE
TABLE
is an entity have row & column
– Row - Occurrence of entity – Column - Field of entity
has naming convention
– TABLE name qualify by owner (e.g.
DB2 Programming
Host Language support
– COBOL, PL/I, ASSEMBLER, FORTRAN & C
Program Preparation should be done for DB2 programs
– PRECOMPILER/ COPROCESSOR
DB2 Object
Referential Integrity (RI)
System assures that all references from one table to another table are valid Several Restriction and Options for RI
plan
DB2 Package
Package
– Produced during program preparation – Bound or operational form of SQL from
database request module (DBRM)
Collection
– simply a way of grouping PACKAGEs into
Buffer Pool Trigger
– a set of actions that are automatically executed when a delete,
Other Objects
– determines the page size of the tablespace/Index – Identifies the buffer pool to be used
The timestamp of the load module MUST match that of BIND
DB2 Programming
1
SOURCE
3
2
4
NON SQL
DB2 PRECOMPILE
SQL (DBRM)
5
7
COMPILE & LINKEDIT
8
DB2 BIND
6
LOAD MOD
DB2 Object
TABLESPACE
Benefits of simple TABLESPACE
– interleaving data
Benefits of segmented TABLESPACE
– locking affect one TABLE only – data scan accesses one TABLE only – mass delete of a table is done on sets of PAGEs
separated SQL (DBRM) from non-SQL
– BIND
checks authorization develop best access path for each SQL statement Record the timestamp stored in DBRM
– EXECUTE
– ROW > 4K, use 32K PAGE
DB2 Object
TABLESPACE
Simple
different TABLE(s) will place in same PAGE
Segmented
– Contain multiple table – partition in segments, each segment contain certain no.