db2, sql, xml, ifs, odbc

Andreas Prouza 2010 1

DB2, SQL, XML, IFS, ODBC

Andreas Prouza 2010

IBM i 7.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


.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


Temporary user-defined file systemsBefehle:

Erstellen des Filesystems:CRTUDFS UDFS('/dev/QASP01/myfs.tmpudfs')

Erstellen des Verzeichnis im IFSMKDIR DIR('/home/test1')

Filesystem an das Verzeichnis anhängenMOUNT TYPE(*UDFS) MFS('/dev/QASP01/myfs.tmpudfs') MNTOVRDIR('/home/test1')

Filesystem abhängenUNMOUNT TYPE(*UDFS) MNTOVRDIR('/home/test1')


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)


Adaptive Query Processing

Erweiterung der SQL Query EngineBei 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.


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


Expression EvaluatorBeispiel: 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


SQL Merge-Statements

Beispiel: Synchronisation 2er TabellenMerge 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)


Bevorzugte Speichereinheit SSD

CHGPF FILE(TAB5) UNIT(*SSD)

UNIT SSD Klausel im Create/Alter Table und Create Index


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.


DB2 concurrent access resolution

USE CURRENTLY COMMITTEDLiest die zuletzt bestätigten Sätze aus einer Tabelle

WAIT FOR OUTCOMEWartet bis Satzsperre aufgehoben ist

SKIP LOCKED DATA (schon ab 6.1)Überliest gesperrte setze

SQL_CONCURRENT_ACCESS_RESOLUTION QAQQINI CONACC pre-compiler option


Create or Replace

Create or Replace Index Tab1_I1 … Create or Replace Variable v1 char(50)


Globale Variablen

Create Variable Var1 char(50)

Es können globale Variablen mit allen DB2 Datentypen erstellt werden

Der Inhalt einer globalen Variable ist JobbezogenBeispiel: Job1: Set var1 = ‚Hallo‘ Job2: Set var1 = ‚Welt‘ Job2: Values (var1) Welt Job1: Values (var1) Hallo

Bei globale Variablen gibt es kein Commitmen Control


EVI mit Aggregation

CREATE ENCODED VECTOR INDEX idx1 ON sales(region) INCLUDE ( SUM(saleamt), COUNT(*) )

SELECT region, SUM(saleamt) FROM sales GROUP BY region


Expressions in SQL Call

Funktionsaufrufe sind können jetzt innerhalb eines Procedure-Calls angewendet werden.

CALL myprocedure('ABC', UPPER(MyName), MyNumber*100 )


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.


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.

<?xml version="1.0"?><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>

XSD: login.xsd

XSD: login_db2.xsd<?xml version="1.0"?><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>


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>


XML-Daten in Tabelle importieren

XSD-File in eine globale Variable hinzufügenset pranlib.var1 = GET_xml_FILE('/home/prouza/login_db2.xsd‚ XSD in der XML-Repository registrierenCALL SYSPROC.XSR_REGISTER('PRANLIB', 'ArrayOfLogin', null,

pranlib.var1 , null) Registrierung vervollständigencall sysproc.xsr_complete ('PRANLIB', 'ArrayOfLogin', null, 1) XML-File in die globale Variable hinzufügenset pranlib.var1 = GET_xml_FILE('/home/prouza/login.xml') XML-Daten in der Datenbank übernehmencall sysproc.XDBDECOMPXML ('PRANLIB', 'ArrayOfLogin',

pranlib.var1, null)


Ergebnis 1


DB2 XML

Daten aus einer Tabelle als XML darstellen.


Beispiel 1

select sp1, xmlserialize (

xmlelement (NAME "LogIn",

xmlforest (c.sp1 as "System", c.sp2 as "Text"))

as varchar (50))

from pranlib.tab1 c


Ergebnis 1


Erklärung 1

XMLSERIALIZE: Damit kann ich einen XML-Typ in ein lesbares Character umwandeln

XMLELEMENT: Hier wird ein XML-Element definiert. (Erstes Element ist IMMER root!)

XMLFOREST: Angabe der verwendeten Spalten


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


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>


Erklärung 2

XMLNAMESPACES: Für Angabe eines Namespaces in einem Element

XMLAGG: Aggregation von Datensätzen (XMLFOREST)


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


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>


Erklärung 3

XMLATTRIBUTES: Mit dieser Funktion kann ein Wert aus einer Spalte als Attribut in einem Element verwendet werden.


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.


Stored Procedure Resultsets

Resultsets aus Stored Procedures können nun in RPG oder anderen Stored Procedures verarbeitet werden.


Stored ProcedureCREATE 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;


Code-BeispielDtab1ds E DS extname (tab1)Dc1 S 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


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;


Viel Spaß noch!

Andreas [email protected]

db2, sql, xml, ifs, odbc

Documents