Architektur und Betrieb von Power Systems kommerziellen

Module 8

Power Systems

© Copyright IBM Corporation 2008 1

Architektur und Betrieb von kommerziellen Anwendungssystemen

Inhalt: Rechnerarchitektur, Softwarearchitektur, Systemadministration und -betrieb müssen aufeinander abgestimmt sein, um für ein kommerziell eingesetztes Informationssystem eine hohe Leistung bei gleichzeitig geringen Betriebskosten zu erzielen. Typische Probleme sind dabei Antwortzeitverhalten, Durchsatz, Sicherheit, Schutz vor Datenverlust, Serverkonsolidierung, Skalierbarkeit, Hochverfügbarkeit und die Integration existierender Infrastruktur. In dieser Lehrveranstaltung werden mögliche Lösungen und die Vorteile einer integrierten Betriebssystemumgebung am Beispiel einer iSeries Umgebung ganzheitlich studiert. In den begleitenden Übungen arbeiten die Teilnehmer an einem IBM eServer i5.

Dauer: 2h Vorlesung, (optional 2h Lab)

Einordnung: SP 2 (Rechnerarchitektur, eingebettete Systeme und Simulation)

SP 5 (Sicherheit und Verifikation)

Lab (optional): Die Übungen können über eine VPN-Verbindung und mittels Remote Desktop an einem 4-Prozessor eServer i5 Modell 550 durchgeführt werden.

Module 8

Power Systems

© Copyright IBM Corporation 2008 2

Content (planned)

• Requirements for Modern Application Systems• i5/OS Architecture• OS Security• Work Management• Networking• Databases and Filesystems• Consolidation of Windows Environments• Storage Consolidation• e-business Infrastructure• High Availablility• Backup and Restore• Logical Partioning• Virtualization Technologies

© Copyright IBM Corporation 2008Material may not be reproduced in whole or in part without the prior written permission of IBM.

Module 8

Power Systems

3

8.1 DB Basics


Module 8

Power Systems

4

The Evolution of Database Modeling Techniques

Source: “Beginning Database Design and Implementation”, Gavin Powell, ISBN:0764574906

http://www.books24x7.com/viewer.asp?bkid=12449&image_src=http://images.books24x7.com/bookimages/id_12449/fig33%5F01%5F0%2Ejpg&image_id=4&previd=IMG_4&titlelabel=Figure+1%2D3%3A+&title=The+evolution+of+database+modeling+techniques%2E


Module 8

Power Systems

5

File Systems

- data are stored in flat files- no data types- no indexing, any searching through flat files

for data has to be explicitly programmed

- data or better file management have to be done by operating system

- CSV files are still very popular


Module 8

Power Systems

6

Program-Described Database File

Each Program has it‘s own data definitions!


Module 8

Power Systems

7

Hierarchical Database


Module 8

Power Systems

8

Information Management System (IMS)

• hierarchical database

• IBM introduced IMS in 1968– simple structure

– performance• IMS can store parent/child close to another

minimizing disk I/O

• still very widely used on IBM mainframes– examples: ATM transactions

Source: “SQL: The Complete Reference”, James R.Groff, Paul N.Weinberg, McGraw-Hill/Osborne 2002


Module 8

Power Systems

9

Network Database

• extends hierarchical database structure– allows childs to have more than one parent

• 1971 standardization (named CODASYL )

Disadvantages of hierachical and network databases:

• Changing database structure typically required rebuilding the entire database

• Programs have to be written for every report


Module 8

Power Systems

10

Relational Database Architecture

• Created to remove program maintenance of data– Invented by IBM, E.F. Codd's 12 rules

• Data Integrity, Relationships, Security, Business Rules

– Managed by RDBMS, not applications• Isolate Programmer from managing data on physical disk units

– DBA's created to now manage at the database level• (on most client/server RDBMS's)

• Concepts of RDBMS– Tables, Columns, Views, Indexes

• Physical Files, Logical Files, Members– Referential Integrity– Triggers, Stored Procedures– Concurrency

• SQL (Structured Query Language)– The "open" RDBMS programming language– JDBC, ODBC, CLI, SQL, Query Tools

Customer

Order Header

Order Line Item

Inventory Item

Inventory Item

Description

STRUCTURED QUERY LANGUAGE (SQL)

Select A.CUSTOMER, B.ORDER_TOTAL, C.PRODUCT

From Customer_Master A, Order_Header B, Order_Line_Item C,

Where A.CUSTID =B.CUSTID ANDB.ORDER_ID=C.ORDERID AND

A.REGION="Central"


Module 8

Power Systems

11

The Business Problem

• The Relational Database Management Systems were not an effective manager of unstructured or semi-structured data (documents, images, e-mails, engineering drawings, video clips, and other business formats):– Most of these data types remain outside the database in a file

system– These files are often related to traditional data stored in the

database• Users want a content management system that

integrates:– Management of the file and its associated data– Synchronizes updates, backup, recovery, and other functions

across both the RDBMS and the file system


Module 8

Power Systems

12

Object-Oriented DBMS

• in the 90th OO became popular

• research on OO databases

• no standardized query language like SQL• not sucessful in the marketplace


Module 8

Power Systems

13

Object-Relational Databases

• extends relational database model

• some object-oriented capabilities are added


Module 8

Power Systems

14

Unstructered Datas

• Complex Objects– LOBs

• Large Objects stored in table columns

– DataLinks• Downloadable to PC Applications

Name Address Picture

John Doe 123 First Ave

Name Address Picture

John Doe 123 First Ave URL

File System


Module 8

Power Systems

15

DatalinkThe DataLink data type is one of the basic building blocks for extending the types of data that can be stored in database files

The DATALINK column holds information about an object, without actually containing the object itselfThe data stored in the column is a pointer to the object

A Uniform Resource Locator (URL) is storedThe object can be anything (image file, voice recording, text file, and so forth)The URL is used to resolve to the objectThere is a relationship between the row and the object.

SQL Table

<URL>

<URL>

<URL>


Module 8

Power Systems

16

DataLinks Components

RDBMSManages file access requests from the DataLinks FilterApplies Transaction Integrity to Link/Unlink actions

Employee Table

Name Dept Picture

DATALINKtype (URL)

Applications

SQL API Requests File API Requests

DBMS/ DLFM APIs

DataLinks File Manager (DLFM)Link/unlink Apply transaction consistency

DataLinks FilterReferential integrity for file rename, move, and deleteDatabase access control for file open

Imagesin external files

Doe 25E


Module 8

Power Systems

17

The Need for Large Objects (LOBs)

• Provide support for Web-based multimedia applications• Provide a built-in data type that can store a large amount of data

• There are three different types of data:– BLOB (Binary Large Object): For scanned documents, digital images, and so forth – CLOB (Character Large Object): Large character string data type– DBCLOB (Double Byte Character Large Object): Large double-byte character string data

type

SOLD ONHAND RATING ARTIST TITLE COVER VIDEO MUSIC SCRIPT

234 59 PG-13 Arnold The Exterminator

13 45 R Kevin Dancing with Bulls

1295 209 G Glenn 101 Doll Imitations

379 112 G Buzz Toy Glory


Module 8

Power Systems

18

User-Defined Types - Example

SMALLINT

INTEGER

DECIMAL

DOUBLE

CHAR

VARCHAR

CLOB BLOB

DATE

TIMESTAMP

TIME

+ / * -

SUM AVG

SUBSTR POSSTR

DAYS

HOUR MONTH

MINUTE

DECIMAL

CREATE DISTINCT TYPEUS_DOLLAR AS DECIMAL(11,2) WITH COMPARISONS

US_DOLLAR DECIMAL

CREATE DISTINCT TYPEEURO AS DECIMAL(11,2) WITH COMPARISONS

EURO DECIMAL

US_DOLLAR

EURO

CREATE DISTINCT TYPE us_dollars AS DECIMAL(11,2);

CREATE DISTINCT TYPE can_dollars AS DECIMAL(11,2);

CREATE TABLE products ( prod_id CHAR(5), prod_desc VARCHAR(50), prod_weight DECIMAL(11,2), usa_price US_DOLLARS, can_price CAN_DOLLARS, product_imageBLOB(1M) )


Module 8

Power Systems

19

User-Defined Functions - Examples

FIND_PICTURE( ,SLIDE_SHOW)Large Object

Search

15,78, 54, 67, 67, 94, 99, 53, 47, 87, 72, 34, 63, 54, 59, 82ARRAY_AVERAGE(CLASSROOM_MARKS)

Built-in Functions:SQRT() EXP() SIN() COS()TAN() LOG() RAND() POWER()

Type Cast Functions:MONEY() DOLLAR()


Module 8

Power Systems

20

Structured Query Language

• Portability of code & skills• Strategic database interface for industry & i5/OS

– SQL required for certain functions & middleware• J2EE architecture based on SQL interfaces

– Data types: BLOB, CLOB, Datalink, ...– Auto-Incrementing Constructs: Sequence & Identity column

attribute– Column-level Triggers– Encryption & Decryption functions – Encoded Vector Indices– InsteadOf Trigger– ...

• SQL as a programming language can reduce total lines of code

• DB2 Symmetrical Multiprocessing - parallel database processing


Module 8

Power Systems

21

SQL Components

Data Definition

CREATEDROP

ALTER

MISCELLANEOUS

CONNECT

DECLARE

DYNAMIC

DESCRIBEEXECUTE

PREPARE

Security

GRANT

REVOKESQL

Structured Query Language

Data Manipulation

INSERTUPDATEDELETE

SELECT


Module 8

Power Systems

22

DBMS Architectures

• Enterprise DBMS– Designed for scalability and high performance,

• very large databases • large number of concurrent user• multiple types of application

• Departmental DBMS– For small or medium-sized workgroups

• Personal DBMS– Single user environment

• Mobile DBMS– Specialized form of a departmental or enterprise DBMS– Needs synchronization for remote database changes

Therefore, every vendor have different products.


Module 8

Power Systems

23

Types of Databases

• Transactional Databases– based on small changes to the database – transaction oriented

• Decision Support Databases– Data warehouse database – Data mart

– Reporting database

• Hybrid Databases – a mixture of both


Module 8

Power Systems

24

OLTP - Online Transaction Processing

PRODUCTS

Product_NoProd_NameProd_type

PARTS

Prod_typeTypeLengthWidthHieghtWieght

CUSTOMER_INVOICES

Invoice_DateProduct_NoCust_NoQuantityDiscountAddress

SUPPLIER

suppkeySupp_NameAddressPhone_NoCountry

GEOGRAPHY

TerritoryCountryContinentRegion

CUSTOMER_ADDRESS

Cust_NoAddressPhoneCountry

PURCHASE_ORDERS

Invoice_NoLinenumberSuppkeyProduct_No

1

m

m

m

m m

m

m

11

1

111

Online Transaction Processing is a "data handling method"

Characteristics :detail orientedhighly repetitiveend-users next action is predictable to a certain degree

Examplesorder entrypayroll applicationsinventory management applications... all applications that support "day-to-day" business requirements


Module 8

Power Systems

25

OLAP - Online Analytical Processing

OLAP databases are typically:

Replicatedsummarized (de-normalized / highly indexed) and cleansed data created from operational transaction systems

Placed on separate database server

Refreshed on timed basis from operational transactions

Multidimensional in nature - read only databases

Could be quite LARGE (several years of historical data)

CustomerOrders

MonthlySummaries

ProductSummaries

RegionalSummaries


Module 8

Power Systems

26

The Enterprise DW Architecture

Data Propagation

ODS Data Warehouse

Operational System(s)

SalesFinance

Data Mart

Data Mart

Data Mart

Mfg

Extraction, Transformation and Loading

Tactical o

perational

decision support

Data Staging Area

PC or Browser Web Visualization Products


Module 8

Power Systems

27

OLTP versus OLAP Databases

OLAPOLTP

update drop

deletereplace

change

update

insert load

load

load

access

access

access

access

data is regularly updated on a record by record basis

data is loaded into the data warehouse and is accessed there

but is not updated


Module 8

Power Systems

28

8.2 System i Integrated Relational Database Management System

DB2 UDB for IBM i


Module 8

Power Systems

29

IBM i and DB2 UDB Family

• Similarities– SQL Syntax– Universal Functionality– Open, Common Interfaces

• DRDA, ODBC, JDBC, CLI

– Common Tools and Middleware

• Differences– Not the same codebase

• Some algorithms and technology is ported

– Administration and Integration• i5/OS have a single system-wide database instance, no

need for multiple database instances like other DB2‘s


Module 8

Power Systems

30

Two Interfaces

Data Description

Specifications(DDS)

STRSQL

Object Type =*FILE

Native

SQL

Library

Schema*LIB

Strategic


Module 8

Power Systems

31


Module 8

Power Systems

32


Module 8

Power Systems

33

8.2.1 Native Database Implementation


Module 8

Power Systems

34

Database Objects - Terminology

OS/400SQL

schema/collection

table

view

index

row

column

log

library

physical file

logical file

keyed logical file

record

field

journal


Module 8

Power Systems

35

Terminology


Module 8

Power Systems

36

Native Library Structure

QSYS System Library

File

Job Queues

Data Areas

Program

Spool

User Libraries LIBA LIBB LIBC

File

Job Queues

Data Areas

Program

Spool

File

Job Queues

Data Areas

Program

Spool

File

Job Queues

Data Areas

Program

Spool

User Objects

System Objects

QTEMPQTEMP

QTEMP


Module 8

Power Systems

37

Physical/Logical Files

File_x File_y

PhysicalFiles (data)

Logical Files (INDEXES)(no data)

selectionprojection

unionjoin

ApplicationProgram

Filea

Open DataPaths(ODP)

Schema/Database/Library

Fileb

FilecFiled

Filee


Module 8

Power Systems

38

Physical File Structure• File Header

– File Description • Qualified File library/name• Owner• Object Authority• Number of records

– Field Level Description • Field names• Attributes

• Dataspace– Actual Data– Multiple File Members

• Not used by SQL

• Index Space– Index

• Cursor

data

datadata

LIBRARYX/DBFILE

fld-1 fld-2 fld-3 fld-4Header

data

Dataspace

Cursor

Member-1Member-2

Member-n

iSeries Unique

{


Module 8

Power Systems

39

File Members

datadata

LIBRARYX/DBFILE


data

Dataspace

Cursor

Members

Display Physical File Member File . . . . . . : CUSTOMERS Library . . . . : ERPDATA Member . . . . . : SECOND Record . . . . . : 1 Control . . . . . Column . . . . . : 1 Find . . . . . . . *...+....1....+....2....+....3....+....4....+....5....+....6 918374Davon A C249 North St LondonTX7521750003003700000000 833245Lee D S21B NW 135 StClay NY1304104001010000000000 123859Jenner T PPO Box 79 BrotonVT0504607001043900000000 345485James S A3 Alpine Way Helen GA3054599992398750003350 393457Peters D A13 Myrtle Dr HectorNY1484110001000000000000 323472Wang C M208 Snow PassDenverCO8022604001005875000150 889900Hoover E J787 Lake Dr SydneyMN5634250003001000000000 423438HumphreyH W59 Archer Rd SutterCA9568507002025000010000 693829Thomas A N3 Dove CircleCasperWY8260999992000000000000 593029WilliamsE D485 SE 2 Ave BangorTX7521802001002500000000 192837Lee F L5963 Oak St ForestNY1484107002048950000050 583990Abraham M T392 Mill St IslandMN5634299993050000000000 ****** END OF DATA ******

Display Physical File Member File . . . . . . : CUSTOMERS Library . . . . : ERPDATA Member . . . . . : CUSTCDT Record . . . . . : 1 Control . . . . . Column . . . . . : 1 Find . . . . . . . *...+....1....+....2....+....3....+....4....+....5....+....6 938472Henning G K4859 Elm Ave DallasTX7521750003003700000000 839283Jones B D21B NW 135 StClay NY1304104001010000000000 392859Vine S SPO Box 79 BrotonVT0504607001043900000000 938485Johnson J A3 Alpine Way Helen GA3054599992398750003350 397267Tyron W E13 Myrtle Dr HectorNY1484110001000000000000 389572Stevens K L208 Snow PassDenverCO8022604001005875000150 846283Alison J S787 Lake Dr Isle MN5634250003001000000000 475938Doe J W59 Archer Rd SutterCA9568507002025000010000 693829Thomas A N3 Dove CircleCasperWY8260999992000000000000 593029WilliamsE D485 SE 2 Ave DallasTX7521802001002500000000 192837Lee F L5963 Oak St HectorNY1484107002048950000050 583990Abraham M T392 Mill St Isle MN5634299993050000000000 ****** END OF DATA ******

Display Physical File Member File . . . . . . : CUSTOMERS Library . . . . : ERPDATA Member . . . . . : THIRD Record . . . . . : 1 Control . . . . . Column . . . . . : 1 Find . . . . . . . *...+....1....+....2....+....3....+....4....+....5....+....6 323472Wang C M208 Snow PassDenverCO8022604001005875000150 889900Hoover E J787 Lake Dr SydneyMN5634250003001000000000 423438HumphreyH W59 Archer Rd SutterCA9568507002025000010000 693829Thomas A N3 Dove CircleCasperWY8260999992000000000000 593029WilliamsE D485 SE 2 Ave BangorTX7521802001002500000000 192837Lee F L5963 Oak St ForestNY1484107002048950000050 583990Abraham M T392 Mill St IslandMN5634299993050000000000 ****** END OF DATA ******


Module 8

Power Systems

40

File Information (1)• File Description Header

– Explicit File Name• Library Name• File Name (10 characters)

– Alternative File Name (20 characters)

– File Type• Data - • Source - program code

• Database Attributes– File Definitions

• Externally Described– Column (or Field) Level Specifications– Field information stored with file

• Program Described – Row (or Record) level Description– Field information within program(s)

– File Creation Date– File Size Limits

• Record Length• Initial records• Size of increment• Number of increments

datadata

LIBRARYX/DBFILE


data

Dataspace

Cursor

Members

DSPFD Command


Module 8

Power Systems

41

File Information (2)

• Database Attributes– Number of members

• Maximum• Current

• Reuse Deleted Records• Force Write to Disk• File Activity

– Number • Opens/Closes• Update/Delete Operations

– Date of Last• Change• Save• Restore

• File Member Information– Name/Creation Date– Last Update (date/time)– Number or Records

datadata

LIBRARYX/DBFILE


data

Dataspace

Cursor

Members

DSPFD Command


Module 8

Power Systems

42

Feld Field Description - Record Information• Columns (Field) Information

–Field Name

–Data Type•Character•Numeric•Binary

–Length–Buffer Positions

–Usage•Input/Output

–Column Heading

datadata

LIBRARYX/DBFILE


data

Dataspace

Cursor

Members

DSPFFD Command (Green Screen)


Module 8

Power Systems

43

Create a Physical File with DDS

Field Reference File :

R FLDREFP PERSNR 5S 0 COLHDG('Personal-' 'nummer') NAME 1000 VARLEN(36) COLHDG('Name') GEBDAT L COLHDG('Geburts-' 'datum') ABTLG 4S 0 COLHDG('Abteilung') GEHALT 7 2 COLHDG('Gehalt') ALWNULL ...

REF(FLDREFP) R PERSTPF1 PERSNR R NAME R GEBDAT R ABTLG R ALWNULL GEHALT R

Physical File Description with DDS :


Module 8

Power Systems

44

8.2.2 IBM i Data Retrieval


Module 8

Power Systems

45

Disk Storage Management

• All data is spread across available disk arms – Optimum performance - automatically

• Not all information is necessarily contiguous– Improved performance

• Balanced disk arm utilization

• Optional rebalancing– space/arm utilization

• Information accessed by name not hardware address• Minimal Database Administration

IBM i


Module 8

Power Systems

46

Multiple ASPs

OS/400

Data

ASP-1(system)

Data

ASP-3(user)

Databasechanges

ASP: Auxiliary (DISK) Storage Pool

ASP-2(user)


Module 8

Power Systems

47

Impact on Data Retrieval

• An ASP can (does) span multiple disks– Contains Multiple Libraries

• Data Objects not bounded by Disk Volumes– Data may span multiple disk drives

• Application Database– Multiple Libraries

• Multiple Objects– Physical Files– Logical Files– Data Areas– Data Queues– And so forth

– IFS Objects• Multiple directories

– Sub-directories

Installed Disk DrivesASP-1 ASP-2 ASP-n

Library-ALibrary-B

Library-C

object-Aobject-B

object-C


Module 8

Power Systems

48

Physical Files (Data)• Actual Information Stored in DB2 UDB Physical Files

– Type *DATA (versus *SOURCE)• Specify

– File Name • <library-name>/<file-name>

– Data held in <member-name>

• Number of Members in file– Data Format

• Record (row) Length• Field (column) Organization

– TypeCharacterDecimalBinary and so forth

– Length

– Reuse Deleted Records• REUSEDLT = *YES/*NO

– Records to force a write• FRCRATIO=*NONE/<value>


Module 8

Power Systems

49

Deleted Records

• Through DBMS Support (DB2 UDB)– REUSEDLT = *YES

• Flagged by DB2 UDB– Records not made available to applications on *READ

• Parameter REUSEDLT in CRTPF/CHGPF – *YES

Space freed by deletion of a record is available for additions (new records)– *NO

Space is not freed when a record is flagged for deletion

– Space "freed" by record deletion• File "compression" possible

– Reorganize Physical File Mbr (RGZPFM command)

• Reduces File size (dependent on number of deletions)

File

REUSEDLT=*YES

deleted records (DB2 UDB)

"new" records to be added

REUSEDLT=*NO


Module 8

Power Systems

Force Records to Disk

• Arises from Single Level Storage Implementation– Frequently access pages tend to remain in memory

• Applies to ALL OBJECTS

• Override for Physical Files– Data written to Disk determined by parameter FRCRATIO

• *NONE ..... System Managed• <value>..... Number of Records

– Added/Updated

• Consider Impact of Sudden Power Outage!– Memory is Volatile

• Data loss? – Battery Failure– Memory Dumps/Recovery

• At next IPL

MemoryDisk

recently used data/updated

Data

determined by FRCRATIO


Module 8

Power Systems

51

Accessing the Database

Database

Technology Independent Machine Interface

SQL/ISQLQuery Manager

Query/400 OPNQRYFClient

Access File Transfer

ODBC

Query APIs

DB2 for iSeriesOptimizer

OS/400Query

Component

Native

I/O(record)

record-levelprocessing set-level

processing

Data Management


Module 8

Power Systems

52

Data Access

• Software interacting with DB2 UDB for i5/OS– Applications

• High Level Languages• Application Packages

– iSeries Query Tools• GUI

– iSeries Access for Windows (5722-XE1)– iSeries Navigator

• Character-based interface– Query Manager (5722-QU1)– DB2 Query Manager (5722-ST1)– OS/400 Commands

Copy File (CPYF)Create Duplicate Object (CRTDUPOBJ)Display File (DSPPFM)

• Non-IBM Tools


Module 8

Power Systems

53

File Transfer (iSeries Access)

More information through Hands-on Lab Exercises!


Module 8

Power Systems

54

CPYF

Copy File (CPYF) Type choices, press Enter. From file . . . . . . . . . . . <input-file> Name Library . . . . . . . . . . . <library> Name, *LIBL, *CURLIB To file . . . . . . . . . . . . <output-file> Name, *PRINT Library . . . . . . . . . . . <library> Name, *LIBL, *CURLIB From member . . . . . . . . . . *FIRST Name, generic*, *FIRST, *ALL To member or label . . . . . . . *FIRST Name, *FIRST, *FROMMBR Replace or add records . . . . . *NONE *NONE, *ADD, *REPLACE... Create file . . . . . . . . . . *NO *NO, *YES Print format . . . . . . . . . . *CHAR *CHAR, *HEX

Bottom F3=Exit F4=Prompt F5=Refresh F10=Additional parameters F12=Cancel F13=How to use this display F24=More keys


Module 8

Power Systems

55

8.2.3 To DBA or not to DBA?

Follow-On Advantages of i5/OS Architecture


Module 8

Power Systems

56

Traditionally, a DBA is a database administrator performing tasks such as...

ƒInstalling the RDBMS

ƒConfiguring the RDBMS

ƒStarting and Stopping the RDBMS

ƒAllocation of disk space for database objects

ƒPartitioning and balance of disk drives

ƒReorganizing / rebalancing of system index structures

ƒMaintaining statistics used in query optimization

ƒLogical and physical data modeling

ƒDetermining and implementing indexing strategies

ƒUser administration and security

ƒMonitoring and tuning database access and processes

Database Administrator Tasks

! ?


Module 8

Power Systems

57

Traditionally, DBAs are separated from the application development community

ƒTechnically different roles and responsibilities

ƒOrganizationally different roles and responsibilities

The DBA


Module 8

Power Systems

58

What's the difference between...

Database Administrator

Database Architect

Database Analyst

Data Steward

Data Owner

Why are they needed...

Technical reasons

Organization reasons

Philosophical reasons

Historical reasons

What is a DBA …?


Module 8

Power Systems

59

Database Skill/Tasks

UNIX/Wintel DBA Tasks DB2 UDB for i5/OSManage DASD Space Allocation Completely Automated

Review Table Space Allocations and Extents Completely Automated

Review and Balance Indexes Completely Automated

Application Rebinding Completely Automated

Maintain Database Integrity Completely Automated

Update Database Statistics Completely Automated

Synchronized OS and DB User Security Completely Automated

Reload Data for Hardware and Software Upgrades

Completely Automated

Load Data into Data Base Integrated Utility (Parallel)

Build and Manage DB Backup and Recovery Integrated GUI

Create and Review Indexes for Tables Integrated GUI

Performance Analysis and Tuning (DB and System)

Integrated GUI

Create and Maintain DB Schema Integrated GUI, DB2 OLAP, and 3rd Party Tools (ERwin,S-Designer)

Automated DB Performance Profiling insure/SQL

Advanced DB Performance Analysis and Tuning insure/SQL

Data Replication and Consolidation Multiple IBM and 3rd Party Products

Integrated GUI - iSeries Navigatorinsure/SQL by Centerfield Technologies


Module 8

Power Systems

60

Consider...

Database Analyst or Architect (not administrator)

Technical issues of moving to a data-centric world

Business logic moving into the database

More data, used in more places, by more clients

Organizational issues

Division of work

Level and type of knowledge required is specialized

Do you need a DBA on IBM i? Maybe...


Module 8

Power Systems

61

8.2.4 Performance


Module 8

Power Systems

62

System i Database Architecture

ODBC / JDBC / ADO / DRDA / XDA

Host Server

Static Dynamic Extended Dynamic

Compiled embedded statements

Prepare every time

Prepare once and then reference

Optimizer

DB2 UDB

Native(Record

I/O)

SQL

Network

(Data Storage & Management)

CLI / JDBC

SLIC

Optimizer

Query Dispatcher

CQE Optimizer SQE Optimizer

DB2 UDB (Data Storage & Management)

SQE Optimizer

SQE Primitives

SQE Statistics Manager

CQE Database Engine

SLIC

Optimizer

Query Dispatcher

CQE Optimizer SQE Optimizer

DB2 UDB (Data Storage & Management)

SQE Optimizer

SQE Primitives

SQE Statistics Manager

CQE Database Engine

The “classic” optimizer and database engine merged to form the SQL Query Engine and much of the work was moved to SLIC.


Module 8

Power Systems

63

Five Factors of DB Performance• Workload

– OLTP, OLAP, ad-hoc queries, system commands• Can fluctuate drastically

• Throughput– Overall capability to process data

• CPU speed, I/O speed, parallel capabilities

• Resources– Hardware- and software tools

• Database kernel, disk storage, RAM, cache, microcode

• Optimization– Query optimization is primary accomplished internal– SQL formulations, index strategy and database parameter

• Contention– Condition, where two or more components are attempting to use

a single resource in a conflict way– As contention increases, throughput decreases


Module 8

Power Systems

64

Therefore, database performance can be defined as the optimization of resource use to increase throughput and minimize contention, enabling the largest possible workload to be proceed.

* Source: Craig S. Mullins, Database Administration – The Complete Guide to Practices and Procedures


Module 8

Power Systems

65

Various Factors of Optimization

Server configurationServer attributes

Version/Release/ModificationLevel

SMP

Database design

Table sizes, number of rows

Views and Indexes (Radix, EVI)

Work management

StaticDynamic

Extended DynamicInterfaces

SQL Request

Job, Query attributes

Server performance

The Plan


Module 8

Power Systems

66

SQL Optimizer

• Why?– Improved Database performance

• Identify technique used to implement query• Selects most efficient technique

• Access Plan – Internal structure defining method to process SQL statement– Created during SQL parse, syntax check

• Optimal access method selection based on – Implementation cost

• SQL Statement• Current state of the database

– Indexes/File Sizes

Database

AccessPlanBindOptimization

SQL Statement


Module 8

Power Systems

67

Query Dispatcher

D A T A S Q EC Q E

System Licensed Internal Code (SLIC)

Parts of the SQE Optimizer now reside in

SLIC

Query Dispatcher

Optimizer

Non-SQL InterfacesOPNQRYFQuery/400

QQQQry API

SQL Based InterfacesODBC / JDBC / CLI

Embedded & Interactive SQLRun SQL ScriptsQuery Manager

Net.DataRUNSQLSTM

S Q EC Q E


Module 8

Power Systems

68

SQL Packages

• IBM i Extended Dynamic SQL Support– Control structures used to execute SQL statements– Access Plans stored in *SQLPKG object – Improves Performance

– IBM i commands : CRTSQLPKG, DLTSQLPKG

– Dynamic re-optimization• File size• New/Deleted indexes

SQL Package

Bind

Open Data Path (ODP)

SQL Statement

Database

AccessPlan

Access Plan

First time

Subsequent

Optimization

SQL Statement

Database

Validate


Module 8

Power Systems

69

Parallelism

QUERYSQL

M EMORY

IOP IOPIOPIOPIOPIOPIOP

Single Level Storage

Cust #1 Cust #2 Cust #3 Cust #4 Cust #5 Cust #6 Cust #7

Parallel table scan

Parallel index scan

Parallel hash join

Parallel hash group by

Parallel index build

Parallel Data Load (Import PTF)

Parallel index ANDing/ORing of dynamic bit maps

Parallel Data Load (Export PTF)

Parallel data load

Parallel index maintenance

Parallel Encoded Vector Index


Module 8

Power Systems

70

Indexing

• Binary Radix Tree

• Bitmap Index

• Encoded Vector Index• When will be indexes created?


Module 8

Power Systems

71

Binary Radix Index

Key values are compressedCommon patterns stored onceUnique portion stored in "leaf" pagesPositive impact on size and depth of the "tree"

Algorithm used to find valuesBinary searchVery efficient process to find a unique value or small range of valuesModified to fit the data structureUsed to materialize a bitmap or relative record number (RRN)

MaintenanceIndex data is automatically spread across all available disk unitsTree is automatically "rebalanced" to maintain an efficient structureNo "index reorganization" required


Module 8

Power Systems

72

Binary Radix Index

IZONA005

ROOT

AR

ISSIPPI002

OURI003

MISS

KANSAS001

Test Node

IOWA004

ARKANSASMISSISSIPPIMISSOURIIOWAARIZONA...

DB Table

ADVANTAGES:Quick access to a single key value (million-entry index, on average, only 20 tests)Also efficient for small, selected range of key values (low cardinality)

DISADVANTAGES:Table rows retrieved in order of key values (not physical order) which equates to many RANDOM I/Os when selecting a large number of keys (high cardinality)No way to predict which physical index pages are next when traversing the index for large number of key values

Keycompression


Module 8

Power Systems

73


Module 8

Power Systems

74

Vergleich zu B* Baum• Baumhöhe:

– Der Radix-Baum ist von Natur aus nicht balanciert, wogegen beim B*-Baum alle Blätter den gleichen Abstand zur Wurzel haben

• Aufbau:– Während beim Radix-Baum

lediglich ein Bit an einer vorbestimmten Stelle eines Bitstrings auf 0 oder 1 geprüft wird, muss beim B*-Baum jede Page, deren Größe von der Ordnung k des Baumes abh¨angt, von links nach rechts nach einer bestimmten Relation (z. B. <=) geprüft werden.

• Storage:– B* : jeder Knoten eine Page– Radix: Page enthält Teilbäume


Module 8

Power Systems

75

Bitmap Index

000100000.......

Row123456789...

Bitmap

Set bits in bitmap or returns an RRN

Binarysearchindexforkey(s)androw no(s)

Given an index on table EMPLOYEE keyed on STATE...

...WHERE STATE = 'Iowa'...

IZONA005

ROOT

ARISSIPPI

002OURI003

MISS

KANSAS001

Test Node

IOWA004


Module 8

Power Systems

76

Encoded Vector Index (EVI)

Index object for delivering fast data access in analytical query and reporting environments

Advanced technology from IBM Research, that is a variation on bitmap indexing

Complement to radix index (keyed logical file or SQL index)

Easy to access data statistics improve query optimizer decision making

Used to materialize a bitmap or relative record number (RRN) list

Easy to maintain


Module 8

Power Systems

77

Encoded Vector Index (EVI)

Key Value Code Count

Alabama 1 1000Alaska 2 450Arizona 3 5000California 4 10000Colorado 5 6500... ... ...Wisconsin 49 340Wyoming 50 2760

11759 274949 5...

SYMBOL TABLE:

VECTOR:

Symbol table contains information for each distinct key value. Each key value is assigned a unique code

Code is 1, 2, or 4 bytes - depending on number of distinct key values

Rather than a bit array for each distinct key value, the index has one array of codes (a.k.a., the Vector)

Object type is File, subtype is LFEVI is composed of two parts...

Row123456789...

OnlyCodes


Module 8

Power Systems

78

Encoded Vector Indexing

New type of index that can significantly improve performance, especially for star schema 10% to 30% faster index builds1/3 to 1/16 the size 1/2 the time for index scans1/3 the time for bit map generation

Vector

1 13 12 28 2 17 38 2 26 33

Row 1 Row 2 ....

Symbol Table

Key Value Code First Row

Last Row

Count

Arizona 1 1 80005 5000Arkansas 2 5 99760 7300......Virginia 37 1222 30111 340Wyoming 38 7 83000 2760

Pre EVI 740-12 EVI-Stage 20

50

100

150

200

250

300

350

Tim

e (

Min

ute

s)

350 GB Table Query4 Table Join (Star Schema)

4.8 Hours

29 Minutes

3 Min.

(S40-12) 740-12-V4R4


Module 8

Power Systems

79

EVI Example

Key Code

A00 1A01 2A04 3B00 4B01 5B02 6B05 7C00 8... ...

117592749495.......

Symbol TableVector

001000001.......

Row123456789...

Bitmap

Set bits in bitmap

Scanvector

forcode(s)

Binarysearchsymboltableforkey(s)andcode(s)

Given an EVI on table EMPLOYEE keyed on WORKDEPT...

SELECT * FROM EMPLOYEE WHERE WORKDEPT IN ( 'B01', C01, 'E01')


Module 8

Power Systems

80

Indexing Strategy

• You must create some indexes– Statistics– Implementations

• Proactive– Create indexes over primary, foreign key columns and

dependent columns– Create indexes for selection and joining– Create indexes for selection, grouping and ordering

• Reactive– Create indexes based on optimizer feedback

• Visual Explain• Database monitors• Joblog messages

• Create indexes based on optimization, implementation, system resources and performance


Module 8

Power Systems

81

8.3 Application Development with SQL


Module 8

Power Systems

82

SQL Packaging

Database ManagerSQL parser and runtime supportQuery ManagementSeveral SQL APIsCall Level InterfacePerformance ToolsRUNSQLSTM

DB2 UDB for iSeries Query Manager and SQL Development Kit (5722-ST1)

STRSQLSQL PreprocessorsQuery Manager SQL REXX Interface

No Charge

Licensed Program


Module 8

Power Systems

83

Stored Procedure

UPDATEINVOICE

UPDATECUSTOMER

UPDATEARBLNCE

INVOICE

CUSTOMER

ARBLNCE

INVOICE

CUSTOMER

ARBLNCE

HEADQUARTERSBRANCH

EXEC SQL

CALL INVCLR (:INVNO,:AMT, :STATUS)

END EXEC

UPDATEINVOICE

UPDATECUSTOMER

UPDATEARBLNCE

OR

An SQL "Program" that contains:• HLL Program with or without embedded SQL• SQL Language• Stored Procedure Created by CREATE PROCEDURE• Can pass parameters• Can return parameters, result set• Widely used in Client / Server applications to reduce traffic


Module 8

Power Systems

84

Trigger

5436

DB2/400

4711

4711

SENDFAX program

When a new order is inserted or updated, a trigger is fired:• Trigger retrieves information about order and customer• A confirmation fax is automatically sent

Application-independent•Written once; used by many

Activated by database manager when operations performed on database

•'Fired' by specified database operations and take action written in trigger•Interface-independent

Triggers:•Enforce business rules•Enforce data validation and audit trail•Preserve data consistency

Triggers versus Stored Procedures•Triggers invoked by database events•Stored procedures invoked by application CALL


Module 8

Power Systems

85

Trigger Example

SELECT TABLE2UPDATE TABLE2INSERT TABLE3 . . . . .

Client ApplicationCommunication Line Data Flow

Database Server with No Triggers

UPDATE TABLE1 . .

SELECT TABLE2. . .

UPDATE TABLE2 .

INSERT TABLE3 . .

Client ApplicationCommunicationLine Data Flow

Database ServerWith Triggers

UPDATE TABLE1. . .

Trigger Program

TABLE1

TABLE2

TABLE3

TABLE3

TABLE2

TABLE1


Module 8

Power Systems

86

Trigger Mode: DB2ROW versus DB2SQL

DB2ROW DB2SQLFOR EACH ROW

DB2SQLFOR EACH STATEMENT

DB OPERATION

DB table

First rowprocessed

Second rowprocessed

Nth (last) rowprocessed

First execution

Nth execution

. . .

Only oneexecution

DB2ROW DB2ROW DB2ROW

DB2SQLFOR EACH ROW

DB2SQLFOR EACH STATEMENTAnother row

not processed

Legend:

When are the different modes of trigger that are fired?


Module 8

Power Systems

87

8.4 Scalability


Module 8

Power Systems

88

Scalability of a DBMS

Capacity to meet increasing requirements

– Often caused by business factors• Numbers of user• Data volume• Transaction volume and complexity

– Depends on• Hardware• Operating system• Database configuration or design

– May not affect application design and code• „think big, start small“ approach


Module 8

Power Systems

89

Vertical and Horizontal Scaling

• Vertical scaling– typically refers to adding more processors and storage to an

SMP to pump up processing capability– this form of scaling employs only one instance of the operating

system

• Horizontal scaling– usually refers to tying multiple independent computers together

to provide more processing power. – typically implies multiple instances of operating systems,

residing on separate servers


Module 8

Power Systems

90

9.1.1 Vertical Scaling


Module 8

Power Systems

91

System i5 CPW

0 20000 40000 60000 80000 100000 120000 140000 160000 180000 200000

CPW520 Value/Express520 Value/Express520 Value/Express

520 1-way520 1-way520 1-way

520 1/2-way

550 1/4-way

570 2/4-way 570 4/8-way

570 8/16-way

595 8/16-way595 16/32-way595 32/64-way

184,000

Note: 64-way measured as two 32-way partitions


Module 8

Power Systems

92

Permanent Capacity: CUoD … pay when purchased (processors & memory)

Temporary Capacity: On/Off CoD … pay after use (processors & memory)

Reserve Capacity: CoD … pay before use (processors) Trial Capacity: CoD … no-charge for use (processors & memory)

Business Peaks

Planned Growth

Capacity on Demand


Module 8

Power Systems

93

9.1.2 Database Clustering Concepts


Module 8

Power Systems

94

Database Implementation Models• Shared Nothing

– Either a single instance SMP database using local storage or multiple database files distributed across servers that own specific data sets yet share one data dictionary

– Multiple servers implement 'function shipping' to access specific information

– Performance depends upon accurate data partitioning scheme

• Shared Disk– Cluster of SMP servers using centralized storage to preserve a single

system database image with no concept of data ownership– All servers have equal access– Performance depends upon efficient inter-node synchronizations

• Shared Disk + Distributed Cache– Clustered server, shared disk approach with a global cache manager

using memory resources of cluster nodes for avoiding disk I/O

• Federated– Multiple server nodes with separate databases and data dictionaries;

servers do not guarantee uniqueness of records


Module 8

Power Systems

95

1 n2

1 2 n

Memory 1

CPU 1

Memory 1

CPU 1

Memory 2

CPU 2

Memory n

CPU n

1 n2

Memory 2

CPU 2

1 n2

Memory n

CPU n

Shared-Nothing vs. Shared Disk

• Can exploit simpler, cheaper hardware

• Almost unlimited scalability• Work well in a high-volume, read-

write environment• Data is partitioned across the

cluster

• Quick adaptability to changing workloads• High availability• Performs best in a heavy read environment• Data need not to partitioned

Source:Craig S. Mullins, Database Administration – The Complete Guide to Practices and Procedures


Module 8

Power Systems

96

DB2 Multisystem• Loosely Coupled - Massively Parallel

– Shared Nothing Architecture– Partitioned Database, Table is spread across nodes– User-Defined partitioning or random partitioning

• SINGLE TABLE VIEW to the application– Data Warehousing, OLAP, Data Mining, DSS Reporting

• Performance and Capacity Scalability virtually unlimited• Single Table can be spread across up to 32 systems

- up to 190 Terabytes disk space per system• From the user’s perspective, the database appears as a single database

– the user can run queries in parallel across all the systems in the network


Module 8

Power Systems

97

Hash Partitioning and Range Partitioning

Example: To partition table PAYROLL in library PRODLIB with partitioning key EMPNUM into 3 partitions, using

• Hash partitioning (places rows at random intervals)– CREATE TABLE PRODLIB.PAYROLL

(EMPNUM INT, FIRSTNAME CHAR(15), LASTNAME CHAR(15), SALARY INT) PARTITION BY HASH(EMPNUM) INTO 3 PARTITIONS

• Range partioning– CREATE TABLE PRODLIB.PAYROLL

(EMPNUM INT, FIRSTNAME CHAR(15), LASTNAME CHAR(15), SALARY INT) ´ PARTITION BY RANGE(EMPNUM)

(STARTING FROM (MINVALUE) ENDING AT (500) INCLUSIVE, STARTING FROM (501) ENDING AT (1000) INCLUSIVE, STARTING FROM (1001) ENDING AT (MAXVALUE)


Module 8

Power Systems

98

Partitioned Tables

• Allow a table to be stored in multiple members but treated as one table

• ONLY should be used in cases where the single table limit of 1.7 TB or 4.2 billion rows is exceeded

• Partition tables should not be used to improve performance

• Limited optimizer awareness of partitions, especially the CQE query optimizer

• Fast delete of rows in a partition is supported

• Requires the DB2 Multisystem feature of i5/OS

i5/OS does not require partitioned tables to fully exploit parallelism!


Module 8

Power Systems

99

Table Sizes

To understand just how large these limits are, here is a list of various row lengths and the approximate number of rows needed to reach the 1.7TB limit:

A partitioned table can have up to 256 partitions, - Each partition able to grow to the respective maximums. - Fully populating would result in 1 trillion rows and a table size of 435 TB!


Module 8

Power Systems

100

Next Lecture:

• Scalability– SMP and Clustering

• High Availability– Backup and Recovery– Save While Active – Cluster

– iASP

Documents

Architektur und Betrieb von Power Systems kommerziellen