Business Unit Empowerment Through Declarative Systems Why IBM
Business Unit Empowerment Through Declarative Systems Why IBM DB 2 Pure. XML™ Rocks! Dan Mc. Creary & Associates September 11, 2007 M D Metadata Solutions 1
DISCLAIMER • The following views are my own and do not necessarily reflect the views of the Minnesota Department of Revenue or the State of Minnesota M D 2
Presentation Abstract Many non-programmers feel that complex Java and. Net middle-tier system prevents business units from being able to quickly build and modify simple web applications (aka CRUD applications). DB 2 version 9 has powerful support for quickly storing and searching complex XML documents. This feature combined with storing XML in a web browser using XForms makes middle-tier code unnecessary, putting non-programmers in control of applications. This presentation will cover a case study that allows a business unit to "draw pictures" to build simple web applications. These pictures (XML Schema diagrams) are transformed directly into XForms and stored in DB 2 version 9. Rapid searching is provided using XQuery. Very little (if any) procedural code is involved in this process. The presentation finishes with a set of 10 recommendations that empower business units to build and maintain their own applications and leverage the power of a central DB 2 database. This presentation is appropriate for business and data strategists as well as DBAs and application architects. M D 3
Agenda • How did we get here? – Why trees not tables? • Demos – NIEM, XML Schemas, XForms • Costs of translation from XML to OO to tables • XQuery in DB 2 • DB 2 and your Enterprise Technology Strategy M D 4
Incoming! L M X Has this web thing gone away yet? web M D 5
More New XML Data is Being Generated Than Relational Data • Growing at 2 X rate of total database market [IDC] • XML is now pervasive in many of organizations • Almost every sector has XML based standards "XML needs its own entry here to reflect its transformation of information … XML is enabling the revolution of Web services and driving a database revolution. " -- e. Week "25 More Top Technologies", September 18, 2006 M D 6
A Happy Partnership XForms DB 2 Pure. XML M D 7
Strategic Consequences • Empowerment of business units to build and maintain their own applications with much less involvement of IT staff • Dramatically reduced need for “middleware” IT staff using traditional procedural languages like Java and C# • Evolution of DB 2 to a web service data providor and core component of ESB M D 8
Did you Know… • That with XForms you can build rich-client web applications without writing a single line of Java. Script? • That IBM Developer. Works has almost 300 articles that discuss XForms? • That DB 2 version 9 "Pure. XML" has fantastic support for native XML data types and supports W 3 C XQuery standards? M D 9
How did we get here? • From punch cards… to tables… to trees… to cubes and graphs… M D 10
In the Beginning… There were puchcards… …and there were 80 columns… and the widths were fixed… and batches of cards were called "decks“… and they were loaded into tables in “core” memory… M D 11
And Codd and Date said… • Let there be relationships… Person person_id first_name last_name birth_date Role person_id project_id role_nm time_pct Project project_id project_nm description status and we had the relational databases… and we had SQL… and we had joins… and we had ER diagrams… and we had Power. Builder… and all seemed right with the world…until M D 12
Sir Berners-Lee Said… • Let there be HTML… <html> <head> <title>Dan’s Homepage</title> </head> <body> <h 1>Welcome!</title> <p>Welcome to my web site!</p> <a href="about. htm">About Me</a> </body> </html> and we had URIs… and we had HTTP… and we had we links and resources… and we had trees (not tables)… and we had search…until M D 13
Customers Said. . • We wanted to access our tables…on the web…in HTML! html ? head body h 1 p a and there were objects… and there was Java and JSP … and there was ASP. Net and there were models, views, controllers… and there was conversion from tables to objects to trees and trees to objects to tables… and there were rooms and rooms of programmers… M D 14
And there Was Chaos… HTML Forms html Objects JSP/ASP head body Hibernate h 1 p a Serialization 100+ Frameworks OR Mapping Java. Script XML Struts JDBC SQL . Net AJAX M D Sessions Stored Procedures And the number of languages you need to learn and the headcount of the IT department went up…and up RDBMS 15
The Translation “Pain Chain” Name: Street: City: Zip: Web Forms Objects RDBMS • From web forms to objects…to SQL inserts…to selects…to objects and back to web forms – Many format translations… M D 16
And the W 3 C Said… • Let their be simplicity…what if we…allowed you to XML in the browser…and database html Database head body model input Person label First. Name Last. Name Projects Roles Project M D 17
A Single Line of SQL INSERT into MY_TABLE values(XMLPARSE(document ' <My. Document> … </My. Document> ')) The equivalent SQL for the e. CRV project would have required “shredding” the XML document into over 25 tables (i. e. 25 separate INSERT statements). M D 18
Enter the Metadata Registry… • A metadata registry is a central location in the enterprise that the semantics or meaning of data elements is captured and maintained <Data. Element> <Namespace>http: //purl. org/dc/elements/1. 1</Namespace> <Data. Element. Name>Resource. Title. Text</Data. Element. Name> <Label>Title</Label> <Definition>The name given to the resource, usually by the Creator or Publisher. </Definition> </Data. Element> Begin with the end in mind…semantic precision. M D 19
Demos • NIEM Subset Generator http: //niem. gtri. gatech. edu/niemtools/ssgt • Certificate of Real Estate Value – http: //proptax. mdor. state. mn. us: 8088/exist/rest//db/crv/forms/submit/index. xhtml • Contacts Demo – http: //proptax. mdor. state. mn. us: 8088/exist/rest/db/contacts/index. xhtml M D 20
M D 21
M D 22
XML Schema (XMLSpy Diagram) Solid line means required Data Element Sequence of items Definition Dashed line means optional Cardinality Graphical notation that most non-programmers can learn in 20 minutes. M D 23
XForms User Interface (Generated) Use XSL to transform XML Schema directly into Xforms Use the metadata registry to extract element-specific rules Separate semantics from constraints M D 24
Metadata Driven Apps Data Flow NIEM MDR wantlist. xml Local MDRs NIEM Sub Schemas. xsd xforms-global. css field-widths. css instance. xml import Document Constraint Schema. xsd patterns. xml read-only. xml screen-labels. xml import niem 2 xforms. xsl import My-Form. xhtml This is a simplified version of the actual workflow. M D 25
How About Reporting on XML data? • OK… it is easy to get XML into DB 2. . …but how about reporting? How do I do “joins” on XML? …How do I update XML documents? …Can I mix SQL and XQuery? M D 26
DB 2 Viper Architecture • Many benefits of mix and match SQL/Xquery • Use the right tool for the right job M D 27
M D 28
M D 29
XQuery • W 3 C Recommendation 23 January 2007 • Created by experts from SQL industry – IBM, Microsoft, Oracle – IBM played a leadership role • XQuery is to XML as the SELECT statement is to SQL M D 30
XQuery Structure • FLWOR expression (pronounced “flower”) – For – select the sequence of nodes in the tree – Let – temporary variables and lists – Where – restrict results – Order – order results – Return – return the data embedded in a tree M D 31
Sample XQuery for $d in db 2 -fn: xmlcolumn(“depts")//deptno let $e : = db 2 -fn: xmlcolumn(“emps")//employee[deptno = $d] where count($e) >= 10 order by avg($e/salary) descending return <big-dept> { $d, <headcount>{count($e)}</headcount>, <avgsal>{avg($e/salary)}</avgsal> } </big-dept> Returns a list of all big departments (over 10 employees), their headcount and average salary directly in XML format M D 32
XSLT and XQuery • Functionally similar languages developed by the W 3 C • Working together to ensure a common approach where appropriate. • They share the same – data model – type system(XML Schema data types) – function library – Both us XPath 2. 0 as a sublanguage M D 33
What I like About XQuery • XPath expressions are very short but can still easy to read (c. f. Perl) • Consistent with XSL, XForms, XML Schema and Schematron (XML rules) • You can extend XQuery using stored procedures • Recursion simplifies many tasks M D 34
But Can I… • • Do joins between XML documents…yes Extend XQuery…yes Create “functions” with XQuery…yes Integrate Java functions…yes M D 35
Joins in XQuery declare variable $first. Name external; <videos featuring="{$first. Name}"> { for $v in db 2 -fn: xmlcolumn(‘videos’)//video, $a in db 2 -fn: xmlcolumn(‘actors’)//actors/actor where ends-with($a, $first. Name) and $v/actor. Ref = $a/@id order by $v/year return List of videos in one file <video year="{$v/year}"> List of actors in another {$v/title} Return a list of all videos </video> } That have an actor with a </videos> given first name. M D 36
XQuery Update • Adds XQuery expressions to: – Insert – Update – Delete XML documents • Still in W 3 C Working Draft – Last call for Comments is Oct. 2007 – Should be full recommendation by 2008 • Available in DB 2 9. 1 as a stored procedure • Additional support in DB 2 9. 5 M D 37
Stored Procedures For Update CREATE PROCEDURE ECRVADMIN. INCREMENT_COUNTY_CRVID (IN COUNTY VARCHAR(255), OUT SUCESS INT) LANGUAGE SQL BEGIN SET ARG 1 = CONCAT( '<updates><update action="compute" col="1" path="//County[Code=" ' COUNTY " ]/Next. ID/text()">) CPMCALL DB 2 XMLFUNCTIONS. XMLUPDATE( ARG 1, ‘ select COUNTY from ECRVADMIN. COUNTY where id=0', 'update ECRVADMIN. COUNTY set COUNTY=? where id=0', ? ); CALL DB 2 XMLFUNCTIONS. XMLUPDATE( '<updates> <update action="compute" col="1" path="//County[Code=" ]/Next. ID/text()"> ? +1 </update> </updates>', 'select COUNTY from ECRVADMIN. COUNTY where id=0', 'update ECRVADMIN. COUNTY set COUNTY=? where id=0', ? ); CALL DB 2 XMLFUNCTIONS. XMLUPDATE( '<updates> <update action="compute" col="1" path="//County[Code=" COUNTY" ]/Next. ID/text()"> ? +1 </update> </updates>', 'select COUNTY from ECRVADMIN. COUNTY where id=0', 'update ECRVADMIN. COUNTY set COUNTY=? where id=0', ? ); M END D 38
Sidebar: The Semantic Web Node Subjec Predicate Objec t t Node M • Note that the semantic web infrastructure is based on graphs, not trees • Each node in the graph can be a URL • External web sites can be used to create “joins” based on a URL D 39
Strategic Impact • Consider this: – 50% of Applications are simple CRUD Applications – Create, Read, Update, Delete • Complex business logic can be stored in an XML -based rules engine such as Schematron • How will this transform an organization? • How will it allow business units to build and maintain IT applications? • How will it allow the business to be responsive to custom needs? M D 40
Change Where the Line is Drawn Requirements BAs SME Developers vs. Graphical Requirements and Specifications SME/BA M IT Staff • Shorten the “distance” between the business unit and the IT staff 41 D
Many Small vs. One Large XForms XQuery XPath XSLT XML Schema Requirements Schematron It is easier to build a GUI on a small, precise language than a single large language M D 42
Data Stewardship/Governance • Importance of the business units role in defining data elements semantics “on-thewire” • How to write and maintain enterprise definitions – Concise – Precise – Distinct – Non-circular – Unencumbered M D 43
Security and Performance • Security and access control based on URL patterns • A middle tier still needed to cache web pages that have similar URLs • Deep REST considerations (don’t regnerate a pages if you don’t have to) • Stateless web server farms can still be used M D 44
Modules and Quality Assurance • XQuery “Modules” allow encapsulation of functions to manipulate complex XML • Need better unit testing tools for XQuery (similar to JUnit) • Need support for regression testing frameworks (similar to Cruise. Control) M D 45
Parker Projection Relative Code Base 100% Proce dural code (Java , Java Scrip t, VB , C#, C++) ) ms r o , XF y r ue Q X , LT S X S, S , C ML ec v i t a HT X ( e od lar c e D Time M D 46 Source: Jason Parker, Minnesota Department of Revenue, November 2006
1. 2. 10 Business Empowerment Steps Make data stewardship a business unit imperative Start by putting a simple glossary of terms and their definitions in a database 3. Build a simple metadata registry based on a simple taxonomy 4. Build a metadata "shopping cart" 5. Build exchange documents using XML Schemas that import your metadata shopping lists 6. Transform XML Schemas into XForms 7. Put your XML data in DB 2 XML data types (no shredding!) 8. Use XQuery for reports directly into XML and HTML 9. Express level-two business rules by selecting XPath expressions 10. Build XML-centric ESB with DB 2 databases as sources M D 47
Next Steps 1. Proof of Technology (POT) – hands on labs 2. Get the evaluation version…start with some small XForms and XQueries 3. Create a pilot project – goal – small middle tier 1. Pick a "friendly" business unit 2. Find a medium sized problem 4. Train staff with XPath-centric tools 5. Roll out to the enterprise in stages M D 48
M D 49
M D 50
Developerworks Article http: //www. ibm. com/developerworks/library/x-xformsniem M D 51
XForms Wikibook http: //en. wikibooks. org/ wiki/Xforms Over 75 working XForms example programs M D 52
XQuery Book • • • XQuery Priscilla Walmsley Tutorial and Reference O’Reilly - 2007 Everything except XQuery update M D 53
Devx Article • Semantics and the Evolution of Specialized Languages http: //www. devx. com/semantic/Article/34805 M D 54
The IBM Workplace XForms Video http: //www. ibm. com/developerworks/library/xxformspointclick. html • One-click generation of XML instance data • Drag-and-drop of complex type elements to create groups of UI controls that are already bound to data • Drag-and-drop of UI controls from the design palette • Drag-and-drop association of data to UI controls created from the design palette • Wizard-based generation of data-driven hiding/showing of groups of UI controls • Wizard-based generation of tables, including add row and delete row buttons • Wizard-based generation of intelligent row calculations and column summations M D 55
More Resourc es • Keywords: XForms, XQuery, Pure. XML • This presentation: – http: //www. danmccreary. com/presentations/db 2 ug M D 56
Useful Links • DB 2 9 Trial – http: //www. ibm. com/software/data/db 2/v 9 • DB 2 9 Express-C - Free limited cpu version – http: //www. ibm. com/software/data/db 2/udb/db 2 expres s/download. html • IBM Developer. Works – http: //www. ibm. com/developerworks/xml – http: //www. ibm. com/developerworks/db 2 • IBM DB 2 9 Pure. XML Wiki – http: //www. ibm. com/developerworks/wikis/display/db 2 xml/Home M D 57
Thank You! Dan Mc. Creary & Associates Enterprise Data Architecture and Strategy Consulting dan@danmccreary. com http: //www. danmccreary. com Cell: (612) 986 -1552 Home: (952) 931 -9198 M D 58
- Slides: 58