IBM DB2 z/OS in der Praxis – Relationale Datenhaltung trifft pureXML

IBM Software Group Information Management
IBM DB2 z/OS in der Praxis –
Relationale Datenhaltung trifft pureXML
14.01.2013
Christian Daser, Technical Sales DB2 z/OS
[email protected]
Informatik-Kolloquium
Friedrich-Schiller-Universität Jena
Regionalgruppe Ostthüringen der Gesellschaft für Informatik (GI)
Fachhochschule Jena
© 2013 IBM Corporation
IBM Software Group Information Management
Datenbank Grundlagen
ACID
–
Atomicity
Guarantee that either all tasks of a transaction are performed or none
–
Consistency
Data is in a legal state when transactions begin or end, integrity is always ensured
–
Isolation
transactions appear isolated from other operations. Noone can look into the ongoings of a TA
–
Durability
Once a transaction ends, the data is persistent and will survive a system failure.
Storage for important data
Huge amount of data
Backup, recovery
Auditing
High volume queries
Multiple users
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Referential integrity & joins
CREATE TABLE DEPTARTMENT
(DEPTNO
CHAR(3) FOR SBCS DATA NOT NULL ,
DEPTNAME
VARCHAR(36) FOR SBCS DATA NOT NULL ,
MGRNO
CHAR(6) FOR SBCS DATA WITH DEFAULT NULL ,
ADMRDEPT
CHAR(3) FOR SBCS DATA NOT NULL ,
LOCATION
CHAR(16) FOR SBCS DATA WITH DEFAULT NULL ,
CONSTRAINT DEPTNO PRIMARY KEY (DEPTNO))
CREATE TABLE EMPLOYEE
(EMPNO
CHAR(6) FOR SBCS DATA NOT NULL ,
FIRSTNME
VARCHAR(12) FOR SBCS DATA NOT NULL ,
MIDINIT
CHAR(1) FOR SBCS DATA NOT NULL ,
LASTNAME
VARCHAR(15) FOR SBCS DATA NOT NULL ,
WORKDEPT
CHAR(3) FOR SBCS DATA WITH DEFAULT NULL ,
PHONENO
CHAR(4) FOR SBCS DATA WITH DEFAULT NULL ,
HIREDATE
DATE WITH DEFAULT NULL ,
JOB
CHAR(8) FOR SBCS DATA WITH DEFAULT NULL ,
EDLEVEL
SMALLINT NOT NULL ,
SEX
CHAR(1) FOR SBCS DATA WITH DEFAULT NULL ,
BIRTHDATE
DATE WITH DEFAULT NULL ,
SALARY
DECIMAL(9, 2) WITH DEFAULT NULL ,
BONUS
DECIMAL(9, 2) WITH DEFAULT NULL ,
COMM
DECIMAL(9, 2) WITH DEFAULT NULL ,
CONSTRAINT EMP_PK PRIMARY KEY (EMPNO),
CONSTRAINT EMP_REF FOREIGN KEY (WORKDEPT) REFERENCES DEPARTMENT (DEPTNO))
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
EMPLOYEE
DEPARTMENT
select e.firstname, e.lastname
from employee e, department d
where e.workdept = d.deptno
and d.admrdept= ‘ D01‘
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Agenda
DB2 for z/OS Einführung
DB2 for z/OS Architektur & Utilities
Anwendungsentwicklung
–
Traditionell
–
Java, Web
Administration
–
DB2I
–
DB2 Admin Tool
Trends
–
pureXML
–
BigData, DynamicWarehousing, BAO
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
IBM‘s database history
<1970
IMS
1970
1983
1987
1988
90s
Codd, theory of relational databases, SQL
DB2 MVS Version 1
DB2 OS/2 („DB2/2“)
DB2 AS/400
DB2 LUW, Everyplace
today
DB2 Version 10
DB2Express-C
pureXML
IBM DB2 Analytics Accelerator
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
IBM Almaden Research Lab, Silicon Valley Lab, Böblingen Lab
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Warum setzen Kunden Datenbanken auf System z ein?
Continuous Availability
–
–
7 x 24
Reduces outages
High Performance and Scalability
–
–
–
–
–
Scalable
Growth
Single and multi-system
Huge amount of databases
Huge amount of users
Wide Range of Workloads
–
–
–
–
–
–
OLTP and Batch
Web Workload with peaks
ERP/CRM Solutions (SAP...)
BI & Warehousing
ODS and mixed workloads
...
DB2 for z/OS
Database Technology Leadership
–
–
–
Cost-based Optimizer, Query-Rewrite
Parallelism
Web-enablement, XML
Highly automated database environment
–
–
–
Complete utilities and tools portfolio
Offerings
z/OS Systemmanagement
Platform compatibility
Accounting
Support for Open Standards
–
–
SQL/ANSI, ODBC, JDBC/SQLJ
...,
© 2013 IBM Corporation
IBM Software Group Information Management
Kundenbeispiele
Banken
Öffentliche Verwaltung
–
Kernbankensysteme
–
Steuerwesen
–
Onlinebanking
–
Meldewesen
–
Geldautomaten
–
Arbeitsvermittlung
–
Bürgerverwaltung
–
Grundstücksverwaltung
Versicherungen
–
Versicherungsprozesse
–
Kundenverwaltung
Retail
–
Unternehmensführung
–
Warenwirtschaft
–
Mitarbeiterverwaltung
Automobilindustrie
–
Konfiguration, Stücklisten
–
Produktionssteuerung
–
Bestellprozesse
–
Lagerwirtschaft
Finanzdienstleister
–
Steuern
–
Lohnzahlung
Telekommunikation
–
Telefonie
–
Netzbetrieb
Outsourcing
–
Massenbetrieb
SAP – Hosting
und viele mehr…
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
System z: zEnterprise EC12
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Enge Verknüpfung mit Hardware und Betriebssystem
IBM
z/OS
DB2 for z/OS
®
Workload Manager (WLM) &
Intelligent Resource Director
z/OS XML System Services
Integrierte z/OS Security
RACF
System Management
…
DB2 for z/OS
CICS
MQ
IMS
WebSphere
…
Parallel Sysplex
64-Bit-Architektur
Hardware Compression
zIIP & zAAP Spezialprozessoren
HiperSockets
FICON Kanäle
...
© 2013 IBM Corporation
IBM Software Group Information Management
DB2 z/OS – 30 Jahre Erfahrung
15th Release
– DB2 10 for z/OS
10000+ Lizenzen
Typischer DB2 for z/OS Kunde
– Team of 2-10
– DASD 300TB
– 10-20 Mio Online Transactions per day
– 20 DB2 Systems
– Response Time < 1s: 99,9%
– CPU Utilization > 90%
– Threads per day: 3.5 Mio
– SQLs per day: 3.900 Mio
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Agenda
DB2 for z/OS Einführung
DB2 for z/OS Architektur & Utilities
Anwendungsentwicklung
–
Traditionell
–
Java, Web
Administration
–
DB2I
–
DB2 Admin Tool
Trends
–
pureXML
–
BigData, DynamicWarehousing, BAO
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
DBM1
Relational
Data System
DB2 Catalog
& Directory,
Sortfiles
User
Databases
Data
Manager
SNA
MSTR
MYDB1
IXS1
TS1
X.TB1
X.IX1
X.IX2
X.TB2
X.IX3
PTS1
IXS2
Y.IXP
Y.TB1
Y.IX1
DIST
(DDF)
BSDS
DRDA
Application
Requestor
& Server
Command
Processor
Log
Manager
Active
Logs
Off load
MYDB2
TS2 LOBTS1
Buffer
Manager
DB2 for z/OS
AUXTB
IXS3
Z.TB1 Z.IX1
TCP/
IP
Recovery
Manager
Distributed
Thread
Pooling
WLM
AS
WLM Managed Stored
Procedure Address Spaces
Batch
USS
TSO
Locking
IMS
IRLM
CICS
DB2 for z/OS Systemarchitektur und -prozesse im Detail
Archive
Logs
© 2013 IBM Corporation
IBM Software Group Information Management
DB2 components in the Job Entry Subsystem (JES)
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
DB2 z/OS Data Sharing
Ausnutzung von System z Hardware und Betriebssystemfunktionalität Coupling Facility
und Parallel Sysplex Konzept
z/OS Workload Manager genutzt für Workload Balancing
Anwendungen verbinden sich zu einer Gruppe von DB2 Systemen mit den selben
Daten
Erhöht die Datenbankverfügbarkeit, Skalierbarkeit und Performance
Erlaubt eine rollende Migration oder Upgrades
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Data Sharing Architektur
Coupling
Facilities
LOCK1
LOCK
SCA
SCA
Group
Group
Buffer
Pools
Buffer
Pools
Sysplex
Timers
...
Subsystem DB2A
Subsystem DB2B
Subsystem DB2C
IRLM
IRLM
IRLM
Shared
DASD
Buffer Pools
DB2A Log &
Archive Log
DB2 for z/OS
DB2B Log &
Archive Log
Buffer Pools
DB2C Log &
Archive Log
…
Buffer Pools
DB2 Catalog
& Directory
…
User
Databases
© 2013 IBM Corporation
IBM Software Group Information Management
DB2 Client Workload Balancing und Failover
DVIPA +
Sysplex
Distributor
WLM liefert
Serverliste
Web User
JDBC Connection
Concentration &
WLB
DB2 Data Sharing
Group
Client definiert
alternative Adresse
JDBC seamless &
cascade failover
Redbook SG24-6952-01: DB2 9 for z/OS: Distributed Functions
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Sicherheit und Datenschutz
Umfassender Schutz der Daten:
–
–
–
Authentifizierung und Authorisierung
Datenverschlüsselung (Platte oder Kabel)
Auditfähigkeit
Zertifizierte Sicherheitstechnologien
und Leadership
Common Criteria (EAL5)
Unterstützt durch Hardware und
Betriebssystem
–
–
–
–
ICSF
CryptoExpressCard
CPACF
SMF, …
Abschwächen von
Securitylöchern
DB2 for z/OS
Unternehmensimage
bewahren
Compliance
gewährleisten
© 2013 IBM Corporation
IBM Software Group Information Management
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
DB2 10 auf einen Blick
Vereinfachte
Anwendungsentwicklung
Performance /
Skalierbarkeit
Mehr Sicherheit
Reduzierte TCO
Vereinfachte
Administration
Dynamic
Warehousing
24
DB2 for z/OS
• Zeitreise: Datenverwaltung in der Zukunft und Vergangenheit zur
Abbildung von zeitl. Geschäftsprozessen
• Umfassende XML Erweiterungen, InlineLOBs
• Vereinfachte globale Verarbeitung über Zeitzonen hinweg
• SQL Kompatibilität, Offene Standards Erweiterungen
•
•
•
•
•
Performanceverbesserungen für OLTP, Java und SAP
DB Konsolidierung: Mehr aktive Connections pro DB2
Extrem schneller Zugriff für bestimmte Datenzugriffe (Hash)
Granulare Sicherheit: Vermeidung von Datenleaks
Erweiterte Sicherheitsmechanismen für “Angriffe von Innen”
•
•
•
•
•
CPU Reduzierung für bestimmte Workloads
Mehr zIIP Nutzung
Durchgängige 64-bit SQL runtime (5x – 10x mehr Threads)
Datenkomprimierung on the fly
Utility Erweiterungen für vereinfachte Administration
•
•
•
•
Neue DataWarehouse SQL Funktionen
Verbesserte, schnellere Verarbeitung von komplexen SQLs
Mehr und verbesserter Query Parallelismus
Erweiterte Querybeschleunigung und Stabilität
© 2013 IBM Corporation
24
IBM Software Group Information Management
Agenda
DB2 for z/OS Einführung
DB2 for z/OS Architektur & Utilities
Anwendungsentwicklung
–
Traditionell
–
Java, Web
Administration
–
DB2I
–
DB2 Admin Tool
Trends
–
pureXML
–
BigData, DynamicWarehousing, BAO
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Preparing a Cobol CICS DB2 program
Source Pgm
geändertes Source
Pgm
+ DSNCLI calls
geändertes Source
Pgm
+ DFHELI calls (Exec Intfc)
Object deck
DB2 Precompiler
DBRM
DSNHPC | DB2I panels
-check table und column
Referenzen gegen SQL
DECLARE Table stmnts.
-produziert DBRM
1
CICS Translator
2
-xlate EXEC CICS stmnts.
Language Compiler
-Cobol, PL/I, ASM, C, C++
Linkage Editor
-include SYSLIB DFHELI
-include SYSLIB DSNCLI
database Request Modul
3
Bind Options für CICS
RETAIN bind und exec Grants
ISOlation CS, möglichst nicht RR
VALIDATE(bind) plan validation time,
nicht RUNtime verwenden wenn mögl
ACQUIRE lock bei Allocate | USE
abh. Von Trx. Rate, Thread Typ
RELEASE lock bei Dealloc | Commit
-ausführbares Pgm
DB2A aktiv
-DB2 Subsys muß
verfügbar sein für Bind
DB2 for z/OS
-connect zu DB2
-produziert Appl. Plan gemäß
Bind Options
-update DB2 Catalog
Neue Enterprise
Compiler Cobol + PLI
erledigen Step 1 – 3 in
1 Step!
4
CICS DB2 Load
Modul
DB2 Bind
Info über jedes SQL stmnt. im Pgm
5
Appl. Plan
-ausführbares SQL
© 2013 IBM Corporation
IBM Software Group Information Management
JDBC Treiber – Ein Treiber für die Datenbankfamilie der IBM
IBM Data Server Driver for JDBC and SQLJ
Unterstützt JDBC und SQLJ 3.0 und 4.0
Type 2 und Type 4 Treiber
–
Type4 URL: jdbc:db2://SERVER:PORT/DATABASE
–
Type2 URL: jdbc:db2:DATABASE
Sehr gute Integration in WebSphere
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Typische Architekturen
Java
Application
-ADO.NET
DRDA
Type 2
App Server
DB2 Connect
DB2 Connect
Gateway
Java app
Batch
CICS
MQ
Cobol
PL/1
DRDA
Type 2
DRDA
Type 2
DB2
J
S
P
C
D
D
F
API
-ADO
-ODBC
-OLE DB
-ROR
-PERL
-JDBC
-SQLJ
Java
-DB2 CLI
App Server
Browser
http
Browser
Application
Type 4
Java
Application
Type 4
App Server
WS consumer
Java Batch
Webservice
SOAP
WS consumer
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
JDBC - correct usage
DB2Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
...
// Prepare the SELECT statement.
stmt = conn.prepareStatement("SELECT LASTNAME, FIRSTNME, SALARY"
+ " FROM SYSADM2.EMPLOYEE" + " WHERE SALARY BETWEEN ? AND ?");
// Set parameters for the SELECT statement.
stmt.setBigDecimal(1, new BigDecimal(30000));
stmt.setBigDecimal(2, new BigDecimal(50000));
// Execute the query to retrieve a ResultSet.
rs = stmt.executeQuery();
// Iterate over the ResultSet.
while (rs.next()) {
String lastname = rs.getString(1); // LASTNAME
String firstname = rs.getString(2); // FIRSTNME
System.out.println(lastname + ", "+ firstname);
...
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
...
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
IBM Data Studio
Mit XML
Files
arbeiten
SQL
Queries
erstellen
DatabankObjekte
browsen
Daten
browsen
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Agenda
DB2 for z/OS Einführung
DB2 for z/OS Architektur & Utilities
Anwendungsentwicklung
–
Traditionell
–
Java, Web
Administration
–
DB2I
–
DB2 Admin Tool
Trends
–
pureXML
–
BigData, DynamicWarehousing, BAO
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
DB2 Administration
DB2 Interactive
–
SPUFI (SQL processing using file input)
–
Program preparation
–
Bind
–
DB2 Commands
–
Utilities (LOAD, REORG, RECOVER, RUNSTATS...)
DB2 Administration Tool
–
Browse DB2 System Catalog
–
Explain
–
DDL creation, GEN, Authorizations
–
Utility Generation
Batch administration
ISV Tools
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
DB2 Utilities
Useful service routines that help maintaining and managing DB2 for z/OS
Are run either as batch jobs or as DB2 stored procedures
LOAD/UNLOAD (Store data into or extract data from DB2 via batch)
RUNSTATS (Generate statistical information about tables and indexes)
REORG (Reorganize the physical allocation of data)
COPY/RECOVER (Generate/recover to backup copies of table spaces)
BACKUP/RESTORE SYSTEM (Backup/restore the whole DB2 subsystem)
...
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
O
S
T
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
O
S
T
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Agenda
DB2 for z/OS Einführung
DB2 for z/OS Architektur & Utilities
Anwendungsentwicklung
–
Traditionell
–
Java, Web
Administration
–
DB2I
–
DB2 Admin Tool
Trends
–
pureXML
–
BigData, DynamicWarehousing, BAO
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Information as a Service
Moving From a Project-Based to a Flexible Architecture (SOA)
Insight
Tools & Applications
People
Processes
Insightful
Relationships
Standards-based
Data &
Content
e.g., XQuery, JSR170,
JDBC, Web Services...
Business
Context
Information as a Service
Optimize, Virtualize, Integrate, Accelerate
Master Data, Entity
Analytics, Decision
Portals, Executive
Dashboards,
Industry Data Models
Extracted or Real-time
Heterogeneous Applications & Information
DB2
DB2 for z/OS
abc…
IBM
Content
Manager
and
more…
xyz…
xyz…
© 2013 IBM Corporation
IBM Software Group Information Management
DB2: Hybrid Data Server
High cost development
Poor performance
Streamlined development
High performance
Or
Business data in XML form
managed in relational database
DB2 for z/OS
Business data in XML form
managed with DB2 pureXML
© 2013 IBM Corporation
IBM Software Group Information Management
Parsing
PurchaseOrder
Client
Items
Status=...
OrderDate
Ponum=...
Item
partid
100-100-01
Document
Node
Element
Node
DB2 for z/OS
Text
Node
Attribute Node
name
Snow Shovel ...
...
price
4
9.99
Serialization
© 2013 IBM Corporation
IBM Software Group Information Management
Create Table
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
XML Datentyp
Build-in Datentyp
UTF-8
Kein Grössenlimit (Datentyp)
Nur Well-formed XML
– E.g. Start tag: <...> and end tag: </...>
Parsing immer notwendig
– V10: Teilweises parsing möglich
Schema Validierung optional
– V9: manuell
– V10: Automatismus
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Native XML storage in DB2
XML index (user)
PO_PARTID_XMLIX
B+tree
B+tree
DocID index
NodeID index
I_DOCIDPURCHASEORDER
I_NODEIDXPURCHASEORDER
B+tree
B+tree
DocID
B+tree
B+tree
…
PORDER
DOCID MIN_NODEID
XMLData
PURCHASEORDER
A table with an XML column has a
DocID column, used to link from the
base table to the XML table.
A DocID index is used for getting to
base table rows from XML indexes.
DB2 for z/OS
XPURCHASEORDER
Each XMLData column is a VARBINARY, containing
a subtree or a sequence of subtrees, with context path.
Rows in XML table are freely movable, linked with a
NodeID index.
© 2013 IBM Corporation
IBM Software Group Information Management
Replace node in XML
POID
...
PORDER
11223344
...
<PurchaseOrder PoNum="11223355" OrderDate="2007-07-18">
<client>
<companyname>MyComp Ltd</companyname>
<address>
<line>10, East
Main
Street</line>
<street>75,
West
Street</street>
<city>1234
<line>1234 Southern City</city>
City</line>
</address>
</address>
</client>
<items>
<item>
<partid>100-100-01</partid>
<name>Snow Shovel, Basic 22 inch</name>
<quantity>4</quantity>
<price>9.99</price>
</item>
</items>
</PurchaseOrder>
UPDATE PURCHASEORDER SET PORDER = xmlmodify('replace node
/PurchaseOrder/client/address with $x', XMLPARSE(document '
<address>
<street>75, West Street</street>
<city>1234 Southern City</city>
</address>') as "x")
WHERE poid=11223344;
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
XPATH Axis-Navigation Direction
ForwardAxis:
E
parent
self
E
attribute
E
E
T
– child
– descendant
– self
.
..
@
– attribute
– text
child
/
text()
ReverseAxis:
– parent
descendant
E
descendant or self
DB2 for z/OS
//
© 2013 IBM Corporation
IBM Software Group Information Management
XPATH Expressions
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
XMLQUERY
POID
11223344
11223345
11223346
...
..
.
..
.
..
.
CUSTID
PORDER
1002
<PurchaseOrder PoNum="11223344"
OrderDate="2007-07-18" Status="Unshipped">
...
<items>
<item>
<partid>100-100-01</partid>
<name>Snow Shovel, Basic 22 inch</name>
<quantity>4</quantity>
<price>9.99</price>
</item>
...
</PurchaseOrder>
1002
1005
DB2 for z/OS
<PurchaseOrder PoNum="11223345"
OrderDate="2007-07-18" Status="Unshipped">
...
<items>
<item>
<partid>100-100-01</partid>
<name>Snow Shovel, Basic 22 inch</name>
<quantity>4</quantity>
<price>9.99</price>
</item>
...
</PurchaseOrder>
1. position to row
according to
WHERE clause
2. Evaluate
XMLQUERY
for XML document
of positioned row
Resultset:
<PurchaseOrder PoNum="11223346"
OrderDate="2007-07-18" Status="Unshipped">
...
<items>
<item>
<partid>100-103-01</partid>
<name>Snow Shovel, Super Deluxe 26 inch</name>
<quantity>4</quantity>
<price>9.99</price>
</item>
...
</PurchaseOrder>
© 2013 IBM Corporation
IBM Software Group Information Management
XMLEXISTS
POID
...
CUSTID
PORDER
11223344
...
1002
<PurchaseOrder PoNum="11223344"
OrderDate="2007-07-18" Status="Unshipped">
...
<items>
<item>
<partid>100-100-01</partid>
<name>Snow Shovel, Basic 22 inch</name>
<quantity>4</quantity>
<price>9.99</price>
</item>
...
</PurchaseOrder>
11223345
...
1002
<PurchaseOrder PoNum="11223345"
OrderDate="2007-07-18" Status="Unshipped">
...
<items>
<item>
<partid>100-100-01</partid>
<name>Snow Shovel, Basic 22 inch</name>
<quantity>4</quantity>
<price>9.99</price>
</item>
...
</PurchaseOrder>
11223346
...
1005
DB2 for z/OS
<PurchaseOrder PoNum="11223346"
OrderDate="2007-07-18" Status="Unshipped">
...
<items>
<item>
<partid>100-103-01</partid>
<name>Snow Shovel, Super Deluxe 26 inch</name>
<quantity>4</quantity>
<price>9.99</price>
</item>
...
</PurchaseOrder>
1. Evaluate all rows
according to
XMLEXISTS condition
3. Select columns to
return
Resultset:
© 2013 IBM Corporation
IBM Software Group Information Management
POID
...
CUSTID
PORDER
11223344
...
1002
<PurchaseOrder PoNum="11223344"
OrderDate="2007-07-18" Status="Unshipped">
...
<items>
<item>
<partid>100-100-01</partid>
<name>Snow Shovel, Basic 22 inch</name>
…
</item>
<item>
<partid>100-103-01</partid>
<name>Snow Shovel, Super Deluxe 26 inch</name>
…
</item>
...
</PurchaseOrder>
11223345
...
1002
<PurchaseOrder PoNum="11223345"
OrderDate="2007-07-18" Status="Unshipped">
...
<items>
<item>
<partid>100-100-01</partid>
<name>Snow Shovel, Basic 22 inch</name>
<quantity>4</quantity>
<price>9.99</price>
</item>
...
</PurchaseOrder>
11223346
...
1005
<PurchaseOrder PoNum="11223346"
OrderDate="2007-07-18" Status="Unshipped">
...
<items>
<item>
<partid>100-103-01</partid>
<name>Snow Shovel, Super Deluxe 26 inch</name>
<quantity>4</quantity>
<price>9.99</price>
</item>
...
</PurchaseOrder>
DB2 for z/OS
1. Evaluate all rows
according to
XMLEXISTS condition
2. select matching rows
3. Select columns to
return and evaluate
XMLQUERY function
Resultset:
© 2013 IBM Corporation
IBM Software Group Information Management
XML Indexes
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
OrderNr
Item
Quantity
Price
11223344
Snow Shovel, Basic 22 inch
4
9.99
11223344
Snow Shovel, Super Deluxe 26 inch
2
49.99
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
EU-Zinsrichtlinie
XML-Erstellung
Verwendung von XML SQL
sql-Abfrageergebnisse werden im xml-Format ausgegeben
einfaches Beispiel:
Adressangaben
Nutzung der folgenden Funktionen:
- Erzeugung von Einzelelementen
einfachen und komplexen Elementstrukturen
Attributen
- Namespacedefinition und -verwendung ist möglich
<sd:ADR
xmlns:dt="urn:cec.eu.int:taxud:directtax:v3"
xmlns:sd="urn:cec.eu.int:taxud:directtax:eusd:v3"
xmlns:stf="urn:oecd:ties:stf:v1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<sd:Address legalAddressType="registeredOffice">
<sd:CountryCode>FR</sd:CountryCode>
<sd:AddressStruct>
<stf:Street>Rue de la Pluemms</stf:Street>
<stf:BuildingIdentifier>44</stf:BuildingIdentifier>
<stf:PostCode>5445</stf:PostCode>
<stf:City>Paris</stf:City>
<sd:OtherLocalId>Nebeneingang</sd:OtherLocalId>
</sd:AddressStruct>
</sd:Address>
</sd:ADR>
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
POID
PORDER
11223344
DATE
---------2007-07-18
2007-07-18
NAME
QUANTITY PRICE PARTID
STATUS
------------------------------ -------- ----- ---------- --------Snow Shovel, Basic 22 inch
4 9.99 100-100-01 Unshipped
Snow Shovel, Super Delux 26 in
2 49.99 100-103-01 Unshipped
Be aware of DataTypes, Length, SpecialCharacters, NULL, Performance, …
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
EU-Zinsrichtlinie
XML-Verarbeitung
Abfrage der Zerlegungsergebnisse auf die Inhalte
der Elemente und Attribute
DB2 for z/OS
Feldname
Inhalt
adresstyp
registeredOffice
laendercode
DE
ort
Testhausen
strasse
Probeweg
hausnummer
2
© 2013 IBM Corporation
IBM Software Group Information Management
Xquery Sample: Which products are sold when it snows ?
POID
PORDER
WID
WEATHERDATA
1
11223355
11223356
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
Agenda
DB2 for z/OS Einführung
DB2 for z/OS Architektur & Utilities
Anwendungsentwicklung
–
Traditionell
–
Java, Web
Administration
–
DB2I
–
DB2 Admin Tool
Trends
–
pureXML
–
BigData, DynamicWarehousing, BAO
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
IBM DB2 Analytics Accelerator
IDAA to enable Data Analytics consolidation
Benefits
A single platform to
manage and
administer
DB2
Analytics
Accelerator
Data
Mart
•
Simplify management of
costly/complex Data
integration of System z Data
with distributed BI system.
•
Enable application queries
which would you prefer to run
with more real-time data on
System z
•
Consolidate isolated islands of
data on one secure Hub
•
Eliminate “Multiple Versions of
the Truth”
•
Increase time to value to
deploy new DataMarts
•
Integrated OLTP & BI
approach to achieve true
Operational BI
Analytic
System
Data
Mart
Data
Mart
Analytic
System
DB2 for z/OS
Consolidate the ever growing
proliferation of data marts onto a
single, easily managed platform
© 2013 IBM Corporation
IBM Software Group Information Management
IBM DB2 Analytics Accelerator
Netezza
Technology
zEnterprise (z196 or z114)
CLIENT
Primary
OSA-Express3
10 GbE
Private Service Network
10Gb
Backup
Data Studio
Foundation
DB2 Analytics
Accelerator
Admin Plug-in
Users/
Applications
57
DB2 for z/OS
BladeCenter
Data Warehouse application
SAP BW
DB2 for z/OS V9 or V10 enabled
for IBM DB2 Analytics Accelerator
IBM DB2
Analytics
Acelerator
© 2013 IBM Corporation
IBM Software Group Information Management
Customer sample
Initial Load Performance …
5.1 GB in 1 Min 25 Sec (24M rows)
400 GB in 29 Min (570M rows)
Query Performance…
1908x faster
2 Hours 39 Min to 5 sec
CPU Consumption…
99% less CPU
24M rows: 56.5 CPU-Sec to 0.4 CPU sec
Query
Total Rows
Reviewed
Query 1
Query 2
Query 3
Query 4
Query 5
Query 6
Query 7
Query 8
Query 9
591,941,065
591,941,065
813,343,052
283,105,125
591,941,089
813,343,052
591,941,065
813,343,052
813,343,052
DB2 for z/OS
Total
Qualifying
Rows
2,813,571
2,813,571
8,260,214
2,813,571
3,422,765
4,290,648
361,521
3,425,292
4,130,107
Total
Rows
Returned
853,320
585,780
274
601,197
508
165
58,236
724
137
DB2 Only
DB2 with
IDAA
Hours Sec(s)
2:39 9,540
2:16 8,220
1:16 4,560
1:08 4,080
0:57 4,080
0:53 3,180
0:51 3,120
0:44 2,640
0:42 2,520
Hours Sec(s)
5
0.0
0.0
5
6
0.0
5
0.0
0.0
70
6
0.0
4
0.0
0.0
2
193
0.1
Times
Faster
1,908
1,644
760
816
58
530
780
1,320
13
© 2013 IBM Corporation
IBM Software Group Information Management
DB2 for z/OS
© 2013 IBM Corporation
IBM Software Group Information Management
DB2 10 for z/OS – Informationen im Web
DB2 V10 Web page
http://www.ibm.com/software/data/db2/zos/db2-10/
http://www.ibm.com/software/data/db2/zos/index.html
DB2 Information Center
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc/db2prodhome.htm
Redbooks / Whitepapers
–
Temporal Data Management in DB2 z/OS
http://public.dhe.ibm.com/software/data/sw-library/db2/papers/A_Matter_of_Time_-_DB2_zOS_Temporal_Tables_-_White_Paper_v1.4.1.pdf
–
DB2 V10 – Proven, simplified and cost effective
http://public.dhe.ibm.com/software/data/sw-library/db2/brochures/db210_for-zos.pdf
–
DB2 V10 for z/OS – Technical Overview
http://www.redbooks.ibm.com/abstracts/sg247892.html
See the XML Guide
–
http://www-1.ibm.com/support/docview.wss?uid=pub1sc18985801
XML brochure
–
http://download.boulder.ibm.com/ibmdl/pub/software/data/sw-library/db2/brochures/db2-9-for-purexml.pdf
DB2 for z/OS
© 2013 IBM Corporation