Transcript db2 - Binus Repository

Matakuliah
Tahun
: T0413
: 2009
Scripting
Pertemuan 4
Scripting
• There are two general forms of scripting:
– SQL scripts
– Operating system (shell) scripts
• SQL Scripts
– Simple to understand
– Platform independent
– Script parameters not supported
• Operating system scripts
– Greater flexibility
– Additional logic possibilities
– Supports parameters/variables
– Platform-dependent
Bina Nusantara University
3
A Basic SQL Script
• Suppose the following commands are saved in a file called
script1.db2
CONNECT TO EXPRESS;
CREATE TABLE user1.mytable
(
col1 INTEGER NOT NULL,
col2 VARCHAR(40),
col3 DECIMAL(9,2));
SELECT * FROM user1.mytable FETCH FIRST 10 ROWS
ONLY;
COMMIT;
Bina Nusantara University
4
Executing SQL Scripts
• An SQL script can be executed from the Command Editor or
the DB2 Command Window/Linux shell
• To run the previous script from the command line (DB2
Command Window), you would use the following command:
db2 –t –v –f script1.db2 –z script1.log
-t indicates statements use the default statement termination
character
(semicolon)
-v indicates verbose mode; causes db2 to output the command
being
executed
-f indicates the following filename contains the SQL statements
-z indicates the following message filename should be used for
appending screen output for later analysis (optional,
but
recommended)
Bina Nusantara University
Note: It is a good idea to delete these message files before the
execution of DB2 scripts so that output from a previous script execution
is not mixed with output from the current script execution
5
When a different statement termination character is needed
•A script containing SQL PL code needs to use a
different statement termination character other than
the default (semicolon)
•This is due to the fact that semicolons are used
within SQL PL object definitions to terminate
procedural statements
Delimit the end of each SQL PL application object
with a different statement termination character
Bina Nusantara University
6
When a different statement termination character is needed
– e.g. CREATE PROCEDURE P1()
declare x int;
….
END!
CREATE PROCEDURE P2()
…
END!
• When executing the script, inform DB2 that a different
statement termination character is being used:
db2 –td! –v –f procs.db2 –z procs.log
Bina Nusantara University
7
A Simple Operating System (Shell) Script
• Suppose the following statements are saved in a file called
create_database.bat
set
set
set
db2
DBPATH=c:
DBNAME=PRODEXPR
MEMORY=25
CREATE DATABASE %DBNAME% ON %DBPATH% AUTOCONFIGURE
USING
MEM_PERCENT %MEMORY% APPLY DB AND DBM
db2 CONNECT TO %DBNAME% USER %1 USING %2
del schema.log triggers.log app_objects.log
db2 set schema user1
db2 –t –v –f schema.db2 –z schema.log
db2 –td@ -v –f triggers.db2 –z triggers.log
db2 –td@ -v –f functions.db2 –z functions.log
• To execute this script from the command line,
you would
Passwor
UserID
d
issue the following command:
create_database.bat db2admin ibmdb2
Bina Nusantara University
8
DB2 Process Model – Threaded engine infrastructure
DB2 Data Server
db2sysc
Instance Level
db2start
Remote
Client
Application
Local
Client
Application
db2wdog
db2vend
db2fmp
db2sysc
F
I
R
E
W
A
L
L
Remote
Listeners
Other EDUs
db2ipccm
db2aiothr
Idle Agent
Pool
Database Level
Application Level
db2agent
db2agntp
db2agent
db2loggw
db2loggr
db2agntp
db2pfchr
db2dlock
Bina Nusantara University
db2lmr
db2med
db2pclnr
db2acd
Per Request
db2agnta
db2agnta
Other
EDUs
Other EDUs
9
Common Processes
• db2sysc
– The main DB2 system controller or engine. In DB2 9.5, there is only one multi-threaded main
engine process for the entire partition. All Engine Dispatchable Units (EDUs) are threads inside this
process. Without this process, the database server cannot function. On Windows the process
name is db2syscs.
• db2acd
– The autonomic computing daemon. It is used to perform client side automatic tasks, such as
health monitor, automatic maintenance utilities, and the admin scheduler. This process was
formerly called db2hmon. It runs multiple threads that establish connections to the database
engine to perform this work.
• db2wdog
– The DB2 watchdog. The watchdog is the parent of the main engine process, db2sysc. It cleans up
resources if the db2sysc process abnormally terminates, and spawns db2fmp processes as well as
the health monitor process.
• db2vend
– The fenced vendor process introduced in DB2 9.5 All vendor code runs in this process outside of
the engine.
• db2fmp
– Fenced processes that run user code on the server outside the firewall for both stored procedures
and user defined functions. This process replaces both the db2udf and db2dari processes that
were used in previous versions of DB2.
Bina Nusantara University
10
Common Threads
• db2sysc
– The system controller thread. This thread is responsible for the start-up and shutdown and the management of the running instance. Its EDU name is the same as
the process name of the main db2sysc process which spawned this thread.
• db2tcpcm
– tcpip communication/listener
• db2agent
– Coordinator agent that performs database operations on behalf of applications (at
least 1 per connection, depending if Connection Concentrator is enabled).
• db2agntp
– Active subagent spawned if INTRA_PARALLEL is set to YES. Will perform
database operations for the application. db2agent will coordinate the work
between the different db2agntp subagents.
• db2pfchr
– DB2 asynchronous I/O data prefetcher (NUM_IOSERVERS)
• db2pclnr
– DB2 asynchronous I/O data writer (NUM_IOCLEANERS)
Bina Nusantara University
11
DB2 Memory Model
Database Manager Shared Memory
(including FCM)
Monitor heap
(mon_heap_sz)
Audit buffer size
(audit_buf_sz)
Database Global Memory
Utility heap
(util_heap_sz)
Database heap
(dbheap)
Bufferpools
(buffpage)
Backup buffer
(backbufsz)
Log buffer
(logbufsz)
Extended memory cache
Restore buffer
(restbufsz)
Catalog cache
(catalogcache_sz)
Locklist
(locklist)
Package cache
(pckcachesz)
Application Global Memory
(app_ctl_heap_sz)
Agent Private Memory
Application
heap
(applheapsz)
Agent stack
(agent_stack_sz)
Statistics heap
(stat_heap_sz)
Sort heap
(sortheap)
DRDA heap
(drda_heap_sz)
UDF memory
(udf_mem_sz)
Statement heap
(stmtheap)
Java heap
(java_heap_sz)
Query heap
(query_heap_sz)
Client I/O block
(rqrioblk)
(remote)
Agent/Application Shared Memory
Bina Nusantara University
Application support layer heap
(aslheapsz)
Client I/O block
(rqrioblk)
12
(local)
Buffer Pool Basics
• Real memory cache for table/index data
• Reduces direct sequential I/O
– Promotes asynchronous reading (pre-fetching) and writing
• Allocates memory in units of 4K,8K,16K and 32K pages
• At least one buffer pool required per database
• At least one matching bufferpool for a table space based on
page size
Bina Nusantara University
13
Creating a Buffer Pool
Control Center > (expand) All Databases folder > (right-click) Buffer Pools folder > Create
Bina Nusantara University
14
Create Buffer Pool Dialog
Buffer Pool Name
Page Size
Number of Pages
Area for Blockbased Buffer Pools
When Buffer Pool Is
Created
Bina Nusantara University
15
Table Space Basics
• A place to store tables - abstracts the details of physical storage
• Composed of one or more "containers" (files, directories, or raw
devices)
• Characteristics
– page size of 4KB, 8KB, 16KB, or 32KB
• Must correspond to a buffer pool with same page size
– extent size
• the number of pages written to one container before writing to the
next
– pre-fetch size
• the number of pages read data when pre-fetching is performed
– buffer pool
• the buffer pool to use as cache
Bina Nusantara University
16
Table Space Types
• REGULAR
– for user tables
– example: USERSPACE1
• LARGE
– optionally separate out LOB data into its own table space.
– Use also for XML.
• TEMPORARY
– SYSTEM TEMPORARY
• used by DB2 for internal operations, such as sorts
• example: TEMPSPACE1
– USER TEMPORARY
• Used for User Defined Global Temporary tables (in-memory)
• Often confused with system temporary table spaces
Bina Nusantara University
17
Table Space Management
• MANAGED BY SYSTEM
– System Managed Storage (SMS)
– Easy to manage
– Containers:
• are file system directories
• space not pre-allocated, files grow dynamically
• fixed at creation time (containers cannot be added later)
– Cannot split index and LOB data across different table spaces
• MANAGED BY DATABASE
– Database Managed Storage (DMS)
– Containers:
• Pre-allocated files or raw devices
– If raw devices, data is written directly without O/S caching
• containers can be added/dropped/resized
– Can place index, table and LOB data in separate table spaces
Bina Nusantara University
18
Table Space Management
• MANAGED BY AUTOMATIC STORAGE
– Intended as a “single point of storage management” for table
spaces
– Create a database and associate a set of storage paths with it
– No explicit container definitions are provided
– Containers automatically created across the storage paths
– Growth of existing containers and addition of new ones
completely managed by DB2
– Redefine those storage paths during a database RESTORE
Bina Nusantara University
19
Automatic Storage
– CREATE DATABASE DB1
AUTOMATIC STORAGE YES
Examples
– CREATE DATABASE DB3
ON /data/path1, /data/path2
– CREATE TABLESPACE TS2
MANAGED BY AUTOMATIC STORAGE
– CREATE TEMPORARY TABLESPACE TEMPTS
– CREATE USER TEMPORARY TABLESPACE USRTMP
MANAGED BY AUTOMATIC STORAGE
Bina Nusantara University
– CREATE TABLESPACE TS1
INITIALSIZE 500 K
INCREASESIZE 100 K
MAXSIZE 100 M
20
Pages and Extents
 "This is a 4K table space with an extent size of 8 and uses 3 raw containers"
ƒ Translation: DMS table space. 4K page size. 32K of data is written to one
disk before writing to the next. (4K x 8 pages per extent = 32K)
 Tables do not share extents
Extents
Striped
across
containers
Bina Nusantara University
0
3
6
9}
12
1
4
7
10
2
5
8
11
Table1
Table2
21
Creating a Table Space
Control Center > (expand) All Databases folder > (right-click) Table Spaces folder > Create
Bina Nusantara University
22
Query parallelism
• Inter-query Parallelism
– Multiple queries running at the same time
• Intra-query Parallelism
– Intra-partition parallelism
• Turned on by the INTRA_PARALLEL dbm cfg parameter
– Inter-partition parallelism
Bina Nusantara University
23
Intra-partition Parallelism
SELECT...
FROM...
Data
Database Partition
Bina Nusantara University
24
Inter-partition Parallelism (DPF option in EE)
SELECT...
FROM...
Data
Database
Partition
Bina Nusantara University
Data
Database
Partition
Data
Database
Partition
A query is divided
into parts, each
being
executed in
parallel.
Data
Database
Partition
25
Inter and Intra partition parallelism !
SELECT...
FROM...
Data
Bina Nusantara University
Data
Data
Data
26