DB2, SQL, XML, IFS, ODBC

IBM i 7.1
DB2, SQL, XML, IFS, ODBC
Andreas Prouza 2010
Andreas Prouza 2010
1
ODBC
128-byte schema names
 Support for the IBM i XML Data Type
 Connection property to configure
Concurrent Access Resolution
 Support for multi-row UPDATE, DELETE,
and MERGE statements

Andreas Prouza 2010
2
.NET Provider






128-byte schema names
Support for the IBM i XML Data Type
Connection property to configure Concurrent
Access Resolution
Support for multi-row UPDATE, DELETE, and
MERGE statements
Support Visual Studio 2008
Online help now available in Visual Studio
Andreas Prouza 2010
3
Temporary user-defined file systems
Befehle:

Erstellen des Filesystems:
CRTUDFS UDFS('/dev/QASP01/myfs.tmpudfs')

Erstellen des Verzeichnis im IFS
MKDIR DIR('/home/test1')

Filesystem an das Verzeichnis anhängen
MOUNT TYPE(*UDFS) MFS('/dev/QASP01/myfs.tmpudfs')
MNTOVRDIR('/home/test1')

Filesystem abhängen
UNMOUNT TYPE(*UDFS) MNTOVRDIR('/home/test1')
Andreas Prouza 2010
4
Erklärung zum TMPUDFS


Können nur im System ASP erstellt werden (QASP01)
Höhere Performance durch geringen Overhead:





Kein Journal möglich
Kein Sichern oder Wiederherstellen
Für Objekte kann keine Berechtigungsliste erstellt werden
Maximal-Speicher des Users kann überschritten werden
Durch den Befehl Unmount geht auch der Inhalt verloren (daher
auch beim IPL)
Andreas Prouza 2010
5
Adaptive Query Processing
Erweiterung der SQL Query Engine
Bei Abfragen > 1 sek. kann das WÄHREND der
Ausführung Änderungen am Zugriffsplan
vornehmen.
Beispiel:
Wenn während der Abfrage ein neuer Index erstellt
wurde.
Wenn die Abfrage länger als geplant dauert und
ein Temporärer Index benötigt wird.

Andreas Prouza 2010
6
Expression Evaluator
(SQL Procedures & Functions)
SQL Proceduren werden teils in C-Code,
teils in SQL-Code geschrieben.
 C-Code ist schneller als SQL-Code
 Mit jedem Release werden immer mehr
Anweisungen in C unterstützt  Neu
erstellen der Procedure oder Function,
nach einem Releasewechsel, kann zur
Steigerung der Performance führen

Andreas Prouza 2010
7
Expression Evaluator
Beispiel:

IF v1 > 1 AND v1 < 100

Vor V5R4:
SELECT 1 INTO :H FROM QSYS2.QSQPTABL WHERE :H:H >
:H:H AND :H:H < :H:H

Nach V5R4:
VALUES ( CASE WHEN :H:H > :H:H AND :H:H < :H:H
THEN 0 ELSE 1 END ) INTO :H

AND und OR wird in C nicht unterstützt jedoch
wird ab V5R4 keine Dummy-Tabelle benötigt 
bessere Performance
Andreas Prouza 2010
8
SQL Merge-Statements
 Beispiel: Synchronisation 2er Tabellen
Merge into tab1
using tab11
on tab1.sp1 = tab11.sp1
when matched then
update set tab1.sp2 = tab11.sp2
when not matched then
insert (sp1, sp2) values (tab11.sp1,
tab11.sp2)
Andreas Prouza 2010
9
Bevorzugte Speichereinheit SSD

CHGPF FILE(TAB5) UNIT(*SSD)

UNIT SSD Klausel im Create/Alter Table
und Create Index
Andreas Prouza 2010
10
Progress status monitors

Beim ändern einer großen Tabelle, kann
im iSeries Navigator angezeigt werden
welche Änderungen noch durchgeführt
werden müssen, Index Rebuild, wie lange
es noch dauert, usw.
Andreas Prouza 2010
11
Andreas Prouza 2010
12
DB2 concurrent access resolution

USE CURRENTLY COMMITTED
Liest die zuletzt bestätigten Sätze aus einer
Tabelle

WAIT FOR OUTCOME

Wartet bis Satzsperre aufgehoben ist
SKIP LOCKED DATA (schon ab 6.1)
Überliest gesperrte setze

SQL_CONCURRENT_ACCESS_RESOLUTION QAQQINI

CONACC pre-compiler option
Andreas Prouza 2010
13
Create or Replace
Create or Replace Index Tab1_I1 …
 Create or Replace Variable v1 char(50)

Andreas Prouza 2010
14
Globale Variablen

Create Variable Var1 char(50)

Es können globale Variablen mit allen DB2 Datentypen
erstellt werden
Der Inhalt einer globalen Variable ist Jobbezogen
Beispiel:

Job1: Set var1 = ‚Hallo‘
 Job2: Set var1 = ‚Welt‘
 Job2: Values (var1)  Welt
 Job1: Values (var1)  Hallo


Bei globale Variablen gibt es kein Commitmen Control
Andreas Prouza 2010
15
EVI mit Aggregation


CREATE ENCODED VECTOR INDEX idx1 ON
sales(region) INCLUDE ( SUM(saleamt),
COUNT(*) )
SELECT region, SUM(saleamt) FROM sales
GROUP BY region
Andreas Prouza 2010
16
Expressions in SQL Call

Funktionsaufrufe sind können jetzt
innerhalb eines Procedure-Calls
angewendet werden.

CALL myprocedure('ABC', UPPER(MyName),
MyNumber*100 )
Andreas Prouza 2010
17
Neue Funktionen in RPG
Neue Funktionen in RPG werden im SEU
ab 7.1 nicht mehr unterstützt.
 IBM will uns dazu zwingen, weg vom SEU,
hin zum Rational zu gehen.
Denn dort gibt es die Unterstützung.

Andreas Prouza 2010
18
XML & DB2
Man kann XML-Files in eine Tabelle
hinzufügen.
 Man kann die Werte eines XML-Files in
ein oder mehrere Tabellen importieren
lassen
 Es können einfach und schnell XML-Files
mit Daten aus der DB2 erstellt werden.

Andreas Prouza 2010
19
XSD: login.xsd
<?xml version="1.0"?>
<!-- Generated using Flame-Ware Solutions XML-2-XSD v2.0 at http://www.flame-ware.com/Products/XML-2-XSD/ -->
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="ArrayOfLogIn">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="LogIn">
<xs:complexType>
<xs:sequence>
<xs:element name="User" type="xs:string" minOccurs="0" />
<xs:element name="Password" type="xs:string" minOccurs="0" />
<xs:element name="System" type="xs:string" minOccurs="0" />
<xs:element name="Library" type="xs:string" minOccurs="0" />
<xs:element name="DSN" type="xs:string" minOccurs="0" />
<xs:element name="Signon" type="xs:string" minOccurs="0" />
<xs:element name="OpsNav" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Andreas Prouza 2010
20
XSD: login_db2.xsd
<?xml version="1.0"?>
<!-- Generated using Flame-Ware Solutions XML-2-XSD v2.0 at http://www.flame-ware.com/Products/XML-2-XSD/ -->
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:db2-xdb="http://www.ibm.com/xmlns/prod/db2/xdb1">
<xs:annotation><xs:appinfo>
<db2-xdb:defaultSQLSchema>PRANLIB</db2-xdb:defaultSQLSchema>
</xs:appinfo></xs:annotation>
<xs:element name="ArrayOfLogIn">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="LogIn">
<xs:complexType>
<xs:sequence>
<xs:element name="User" type="xs:string" minOccurs="0"
db2-xdb:rowSet="LOGIN"
db2-xdb:column="USERNAME"
db2-xdb:condition="$DECOMP_CONTENT IS NOT NULL"/>
<xs:element name="Password" type="xs:string" minOccurs="0" />
<xs:element name="System" type="xs:string" minOccurs="0"
db2-xdb:rowSet="LOGIN"
db2-xdb:column="SYSTEMNAME"
db2-xdb:condition="$DECOMP_CONTENT IS NOT NULL"/>
<xs:element name="Library" type="xs:string" minOccurs="0"
db2-xdb:rowSet="LOGIN"
db2-xdb:column="LIB"
db2-xdb:condition="$DECOMP_CONTENT IS NOT NULL"/>
<xs:element name="DSN" type="xs:string" minOccurs="0" />
<xs:element name="Signon" type="xs:string" minOccurs="0" />
<xs:element name="OpsNav" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Andreas Prouza 2010
21
XML: login.xml
<?xml version="1.0" encoding="utf-8"?>
<ArrayOfLogIn xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<LogIn>
<User>pran</User>
<Password />
<System>192.168.0.10</System>
<Library>MyLib</Library>
<Signon>-1</Signon>
<OpsNav>false</OpsNav>
</LogIn>
<LogIn>
<User>andi</User>
<Password />
<System>192.168.0.201</System>
<Library />
<Signon>-1</Signon>
<OpsNav>false</OpsNav>
</LogIn>
</ArrayOfLogIn>
Andreas Prouza 2010
22
XML-Daten in Tabelle importieren

XSD-File in eine globale Variable hinzufügen
set pranlib.var1 = GET_xml_FILE('/home/prouza/login_db2.xsd‚

XSD in der XML-Repository registrieren
CALL SYSPROC.XSR_REGISTER('PRANLIB', 'ArrayOfLogin', null,
pranlib.var1 , null)

Registrierung vervollständigen
call sysproc.xsr_complete ('PRANLIB', 'ArrayOfLogin', null, 1)

XML-File in die globale Variable hinzufügen
set pranlib.var1 = GET_xml_FILE('/home/prouza/login.xml')

XML-Daten in der Datenbank übernehmen
call sysproc.XDBDECOMPXML ('PRANLIB', 'ArrayOfLogin',
pranlib.var1, null)
Andreas Prouza 2010
23
Ergebnis 1
Andreas Prouza 2010
24
DB2  XML
Daten aus einer
Tabelle als
XML
darstellen.
Andreas Prouza 2010
25
Beispiel 1
select sp1, xmlserialize (
xmlelement (NAME "LogIn",
xmlforest (c.sp1 as "System", c.sp2
as "Text"))
as varchar (50))
from pranlib.tab1 c
Andreas Prouza 2010
26
Ergebnis 1
Andreas Prouza 2010
27
Erklärung 1
XMLSERIALIZE: Damit kann ich einen
XML-Typ in ein lesbares Character
umwandeln
 XMLELEMENT: Hier wird ein XMLElement definiert.
(Erstes Element ist IMMER root!)
 XMLFOREST: Angabe der verwendeten
Spalten

Andreas Prouza 2010
28
Beispiel 2
Select xmlserialize (
xmlelement (NAME "ArrayOfLogIn",
xmlnamespaces (DEFAULT 'http://example.org'),
xmlagg (xmlelement (NAME "login",
xmlforest (c.sp1 as "System",
c.sp2 as "User")))) as
varchar (9999)) "XML-FILE"
From pranlib.tab1 c
Andreas Prouza 2010
29
Ergebnis 2
<ArrayOfLogIn xmlns="http://example.org">
<login>
<System>1</System>
<User>FFFFF</User>
</login>
<login>
<System>10</System>
<User>hallo123456</User>
</login>
<login>
<System>12</System>
<User>du da</User>
</login>
</ArrayOfLogIn>
Andreas Prouza 2010
30
Erklärung 2
XMLNAMESPACES: Für Angabe eines
Namespaces in einem Element
 XMLAGG: Aggregation von Datensätzen
(XMLFOREST)

Andreas Prouza 2010
31
Beispiel 3
Select xmlserialize (
xmlelement (NAME "ArrayOfLogIn",
xmlnamespaces (DEFAULT 'http://example.org'),
xmlagg (xmlelement (NAME "login",
xmlattributes (sp1 as "id"),
xmlforest (c.sp1 as "System",
c.sp2 as "User")))) as
varchar (9999)) "XML-FILE"
From pranlib.tab1 c
Andreas Prouza 2010
32
Ergebnis 3
<ArrayOfLogIn xmlns="http://example.org">
<login id="1">
<System>1</System>
<User>FFFFF</User>
</login>
<login id="10">
<System>10</System>
<User>hallo123456</User>
</login>
<login id="12">
<System>12</System>
<User>du da</User>
</login>
</ArrayOfLogIn>
Andreas Prouza 2010
33
Erklärung 3
XMLATTRIBUTES: Mit dieser Funktion kann
ein Wert aus einer Spalte als Attribut in
einem Element verwendet werden.
Andreas Prouza 2010
34
Beispiel 4
values (xmlserialize
(xmltext ('Sonderzeichen < >
as varchar (50)))
&‚)
Ergebnis:
Sonderzeichen < >
&
Erklärung: Die XMLTEXT-Funktion kann
auch für Verarbeitung von HTML
verwendet werden.
Andreas Prouza 2010
35
Stored Procedure Resultsets
Resultsets aus Stored Procedures
können nun in RPG oder anderen
Stored Procedures verarbeitet
werden.
Andreas Prouza 2010
36
Stored Procedure
CREATE PROCEDURE PRANLIB.TEST1 (
IN SPNR INTEGER )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC PRANLIB.TEST2
BEGIN
DECLARE C2 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM PRANLIB . TAB1
WHERE SP1 > SPNR;
OPEN C2;
END;
Andreas Prouza 2010
37
Code-Beispiel
Dtab1ds
Dc1
E DS
S
extname (tab1)
SQLTYPE(RESULT_SET_LOCATOR)
/Free
Exec Sql Call Test1 (10);
Exec Sql ASSOCIATE LOCATORS (:c1) WITH PROCEDURE Test1;
Exec Sql ALLOCATE mycur1 CURSOR FOR RESULT SET :c1;
Exec Sql Fetch mycur1 Into :tab1ds;
/End-Free
Andreas Prouza 2010
38
Erklärung

Hier wird ein Result-Set-Locator definiert.
SQLTYPE(RESULT_SET_LOCATOR)

Der Result-Set-Locator wird mit den Resultset
der Procedure verknüpft.
Exec Sql ASSOCIATE LOCATORS (:c1) WITH PROCEDURE
Test1;

Es kann nun ein Cursor erstellt werden, welcher
den Resultset einliest.
Exec Sql ALLOCATE mycur1 CURSOR FOR RESULT SET
:c1;
Andreas Prouza 2010
39
Viel Spaß
noch!
Andreas Prouza
[email protected]
Andreas Prouza 2010
40