IBM Informix and SOA Integration

IBM Informix and SOA Integration How to enable your Informix based application for an OnDemand business • Alexander Koerner, Channel Technical Sales – Informix / SOAAlexander.Koerner@de.ibm.com • Certified Senior IT-Specialist, IBM Germany (Munich)

Agenda • Service-oriented Architectures (SOA) • Informix and SOA - Overview • Informix and SOA - Details • Additional Information

What is SOA? • Service-oriented architecture (SOA) helps hide the IT complexity inherent in even seemingly simple interactions • One key technical foundation of SOA are Web services • SOA is an architectural style that allows Web services applications to interoperate dynamically with one another • SOA isn’t really new, but the availability of more and improved Web services applications is making SOA much more powerful and easier to implement • SOA makes it less expensive for companies to link their business processes to those of their suppliers, vendors and other business partners

SOA Example: Internet Bookstore • Customer point of view: • A book is ordered online and a few days later delivered to your house • What happens behind the scenes: • Your identity must be authenticated • Your charge card validated • Your order acknowledged by e-mail • The order sent to a distributor • The book located and boxed for shipping • E-mail notification that your order has shipped • Hand-off to the shipper (at which point the shipper’s supply chain management system tracks the movement of the purchase) • And finally delivery and acknowledgement of receipt • How SOA comes into play • Each of the applications above performs a service that is orchestrated increasingly by SOA

What are Web services? • ‘Remote Function Calls’ via Inter-/Intranet • Based on XML-formatted messages • Utilize internet protocols like HTTP (important if proxys/firewalls are involved) • Base technology for complex applications • Some reasons why Web services are successful: • Loosely coupled systems • Independent from the used programming language (e.g. Java, 4GL, ESQL/C, Cobol, C++, etc.) • Easy application integration due to open standards

Why SOA now? • To keep pace with global competition: • “We are taking apart each task and sending it … to whomever can do it best, … and then we are reassembling all the pieces” from Thomas Friedman’s ‘The World is Flat’ • The standards and technology are finally in place, with broad industry support • Availability of best practices for effective governance • The necessary software to get started is available today

Insightful Relationships BusinessContext Data & Content Information as a Service Information as a ServiceMoving From a Project-Based to a Flexible Architecture (SOA) Tools & Applications Dashboards Insight In-line, Real-time Standards based: e.g., XQuery, JSR170, JDBC, Web Services... Metadata Management Real Time : e.g., On Line Help, Synchronized Master Data… Extracted: e.g. Basel II, Business Optimization… Heterogeneous Applications & Information and more… IBM Content Manager DB2 abc… InformixIDS

IBM Information Management SoftwareDelivering Value Beyond Traditional Repositories Information Delivered on Demand Based on Services Oriented Architecture • Data Services • Informix IDS, DB2, IMS, Cloudscape, U2 • Content Services • Content Management Services • Content Integration Services • Information Integration Services • Federation Services • Movement Services • Transformation Services • Quality Services… • Information Accelerators • Master Data Management • Entity Analytics • Information Warehousing • Customizable Dashboards • Industry Data Models

IBM Information Management SoftwareDelivering Value Beyond Traditional Repositories #1 in Share 450,000+ Clients Information Delivered on Demand Based on Services Oriented Architecture • Data Services • Informix IDS, DB2, IMS, Cloudscape, U2 • Content Services • Content Management Services • Content Integration Services • Information Integration Services • Federation Services • Movement Services • Transformation Services • Quality Services… • Information Accelerators • Master Data Management • Entity Analytics • Information Warehousing • Customizable Dashboards • Industry Data Models #1 in Share 13,000+ Clients #1 in Share 5,000+ Clients No Other Vendor Delivers The Breadth and The Depth of Capabilities Leading The Space 1,000+ Clients

Agenda • Service-oriented Architectures (SOA) • Informix and SOA - Overview • Case Study 1: IDS as a Web service provider • Case Study 2: IDS as a Web service consumer • Case Study 3: Informix 4GL and Web services integration • Informix and SOA – Details • Additional Information

Case Study 1: Supply Chain Integration • Problem:An IDS 7 based application (manufacturer order entry system) should be integrated into the supply chain management of a world wide operating retail customer. • Solution:IDS 10 as a Web service provider • In-place update of IDS 7 to IDS 10 • Utilizing WORF (Web services Object Runtime Framework) with IDS 10 • Result:WORF provides Web service access to an IDS 10 Stored Procedure which is the interface to the manufacturer’s order entry system.

Informix IDS and SOA: Web services integration • IDS as a Web service provider • IBM WORF (Web services Object Runtime Facilty) support for IDS • Easy IDS Web service definition based on SQL Statements (SELECTs, INSERTs, UPDATEs and Stored Procedure Calls) • Requires WebSphere Application Server or any other J2EE server as a runtime environment • Integrated easy to use test environment • IBM EGL (Enterprise Generation Language) allows easy Web service providing • Easy to learn, Java based 4GL-like language • Strong .NET support for IDS and Web services integration for non-Java environments • Any Java object (Servlet, Bean etc.) could be used as an IDS Web service provider

Case Study 2: Web service access from a Legacy App • Problem:An Informix ESQL/Cobol application should be enhanced to be able to access a credit card verification (Web-) service. • Solution:IBM IDS 10 as a Web service consumer • JFoundation, Apache AXIS Framework • Result:An IDS 10 Java Stored Procedure (User Defined Routine) which accesses the remote Web service. Callable via SQL statements. Only minor changes in the ESQL/Cobol application are required since SQL is being used to interact with the Web service.

Informix IDS and SOA: Web services integration • IDS as a Web service consumer • IDS leading extensible architecture provides the perfect foundation for web service access • IDS JFoundation in combination with the Axis framework allows transparent access to web services from the Informix SQL layer • IDS DataBlade API for any kind of custom Web service access (e.g. via C routines in the server) IDS Web services consumer example: Google Search

Case Study 3: Provide Web service API for a 4GL App • Problem:An Informix ISV gets the customer requirement to add an Web service API to an existing Informix 4GL legacy application. • Solution:Conversion of the 4GL application to IBM’s Enterprise Generation Language (EGL) and utilization of EGL’s Web services support • Rational Application Developer, 4GL to EGL conversion tool, EGL Web services, IDS 10 • Result:After converting the 4GL App to EGL, some former 4GL functions (now in EGL) could be offered as Web services w/o any major changes to the original code.Positive side effect: Due the Java based EGL language, parts of the application could be web-enhanced and also could be deployed to more platforms (e.g. Windows)

Informix 4GL and SOA: Web services integration • Informix 4GL applications • Consider a conversion to Enterprise Generation Language (EGL) • EGL allows easy Web service provision and Web service access • Web service support is part of the EGL language! • Easy to learn, Java based 4GL-like language • EGL supports character based (4GL-like), batch and web applications (soon also GUI applications) • An included conversion utility allows an easy 4GL to EGL conversion process • Additional platform deployment support (e.g. Windows)

Informix IDS / SOA in a real world: Retarus GmbH • Business Problem • Deep business and process integration in a heterogeneous B2B environment • Solution • COMMUnix XC by Retarus GmbH (Munich, Germany) is a high performance EDI XML cross converter that can easily manage hundreds of connections between different applications and organisations on a single processor machine. The converter supports the following formats: EDIFACT, XML, SWIFT, DTA, SAP, openTrans, ODETTE, ANSI X.12, FORTRAS, Chem eStandards and RosettaNet. Interfaces like X.400, SMTP, S/FTP, HTTP/S, IBM MQSeries, APIs, RFC and OFTP • Who is using that solution? • BAX Global, Sigma-Aldrich, K2, Avnet, Caparol, DAW (Deutsche Amiphibolin Werke), DZ Bank, Exel, Sony Pictures Home Entertainment, Fiducia and more • Why Informix IDS? • IDS its being used due to its low admin overhead, performance and ease of embeddebility

Summary • SOA and Web services allow an easy integration of heterogeneous applications • IBM‘s Information Management portfolio provides Data services to meet all current and future customer requirements • IBM Informix based applications can be easily integrated into existing SOA environments • Due to its leading extensible architecture, IDS 10 provides the necessary foundation for SOA applications • Informix legacy applications (e.g. 4GL-based) can be easily transformed into services  Investment protection

Recommended Products for Informix SOA Integration • IBM Informix IDS 10 (any edition) • Contains enhancements for better SOA integration • IDS 10 Extensions (Datablades) • WebSphere MQ Datablade • XML Generating UDRs DataBlade (Informix DeveloperZone) • XSLT DataBlade (Alphaworks) • WORF (Web Services Object Runtime Framework) • Part of the Rational Software Development Platform (SDP) • and WebSphere Application Server • IBM Rational EGL • Part of the Rational SDP (e.g. Rational Application Developer) • Integrated 4GL to EGL conversion tool

Agenda • Service-oriented Architectures (SOA) • Informix and SOA - Overview • Informix and SOA - Details • More on Web services • Informix SOA Foundation Technologies • IDS 10 as a Web service provider • IDS 10 as a Web service consumer • Additional Information

Web services: Characteristics • Web services are self-contained • On the client side only a programming language with HTTP and XML support is required • The client and server can be implemented in different environments • Web services are self-describing • The definition of a Web service message format travels with the message • No external metadata repositories or code generation tools are required • Web services are modular • Simple Web services can be aggregated to form more complex Web services • E.g. by using workflow techniques or by calling lower layer Web services from a Web service implementation.

Web services: Typical Categories • Business information • A business shares information with consumers or other businesses • Examples: news streams, weather reports, or stock quotations • Business integration • A business provides transactional, "for fee" services to ist customers • Examples: bid and auction e-marketplaces, reservation systems, and credit checking • Business process externalization • A business differentiates itself from its competition through the creation of a global value chain • The business uses Web services to dynamically integrate its processes • Example: associations between different companies to combine manufacturing, assembly, wholesale distribution, and retail sales of a particular product.

Web services: Standards • An extensible Web service description language... • WSDL • A limited number of protocols being used... • HTTP GET/POST • SOAP • An Web service directory protocol... • UDDI • A commen, open foundation... • XML

Web services: Standards (cont.)

Web services: A simple SOAP Message <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/1999/XMLSchema" xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance"> <SOAP-ENV:Body> <ns1:getCustomerencodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns1="urn:placeOrder"><Customer_numxsi:type="xsd:int">106</Customer_num></ns1:getCustomer> </SOAP-ENV:Body> </SOAP-ENV:Envelope>

Web services: Best Practices • Do not use Web services between layers of an application or e.g. within a Java application server. The parsing of every Web service message is very costly and will slow down your application. • Do not use Web services if you’re not exposing external interfaces e.g. for interoperability or if you don’t use an XML document based workflow. • Use Web services on the edge of your application server to expose external APIs or if you need to execute remote calls through a firewall. • If you have a need to execute function calls between Java application servers you might want to consider other protocols, e.g. RMI/IIOP.

IDS and SOA, the Foundation: XML and Messaging • Rational Development Tools / WebSphere Application Server (XML Input, XML Output) • XML Generation and Transformation with User Defined Routines (XML Output, XML Shredding [planned], XPath Support [planned]) • Web DataBlade (XML Output) • XSLT DataBlade (XML Transformation) • JAXP Support in the Informix JDBC driver (XML Validation) • CLOB/TEXT/LVARCHAR data types (XML Storage) • Excalibur Search DataBlade (Full Text Searches) • Spatial DataBlade 8.20 (XML/GML Output) • WebSphere MQ DataBlade (reliable Messaging integration)

IDS and XML: XML Generation with UDRs • Based on the extensibility features of IDS 10 (DataBlade Technology) • Basic building block is the genxml() routine • Code can be found on the IBM Informix Developer Zone (Author: Jacques Roy) • http://www7b.software.ibm.com/dmdd/zones/informix/library/techarticle/0302roy/0302roy2.html Output of the Statement on the left side: <customer> <customer_num>101<//customer_num> <fname>Ludwig </fname> <lname>Pauli </lname> <company>All Sports Supplies </company> <address1>213 Erstwild Court </address1> <city>Sunnyvale </city> <state>CA</state> <zipcode>94086</zipcode> <phone>408-789-8075 </phone> </customer> SQL Statement: SELECT genxml("customer", customer) FROM customer; genxml() UDR example

IDS and XML: Web DataBlade • Since version 4.13.xC2: XML support builtin! • New attributes for the MISQL tag: XMLDOC, XMLROW, XMLVERSION • Focus on XML output (XML input possible in combination with XSLT DataBlade) • XML templates are stored in IDS • Allows usage of IDS server features like ER, HDR or Onbar! • Perfect companion to the XSLT DataBlade • IBM Informix Developer Zone article (Author: Susan Cline) • http://www7b.software.ibm.com/dmdd/zones/informix/library/techarticle/0303cline/0303cline.html <?xml version="1.0" ?> <TABLE> <ROW ID="1"> <code>311</code> <desc>Food Products</desc> <ecomm>54837.00</ecomm> <total>434261.00</total> </ROW> .... additional output not shown </TABLE> <?MISQL SQL="select code,desc,ecomm,total from ecommerce;" XMLDOC="TABLE">$1<?/MISQL> Web DataBlade template example

IDS and XML: XSLT DataBlade • IDS 10 DataBlade, based on libxslt, the Gnome XSLT library • Available via IBM Alphaworks • http://www.alphaworks.ibm.com/tech/xsltblade • Creates several UDRs which take two parameters: an XML document and an XSL stylesheet • xslt_udr (returns LVARCHAR) • xslt_udr_AsClob (returns CLOB) • xslt_udr_AsHtml (returns HTML – Web DataBlade type) select xslt_udr(s.style_doc, x.xml_doc) FROM style_sheets s, xml_docs x WHERE s.style_title = 'quote.xsl' and x.xml_title = 'ecommerce.xml'; XSLT DataBlade usage example

IDS and XML: JAXP Support (Informix JDBC Driver) • JAXP = Java API for XML Parsing • Part of the current Informix Type 4 JDBC driver • The API has two subsets • SAX • DOM • plus a plugability layer (to standardize access to the SAX or DOM layer) • Facilitate storage and retrieval of XML data in IDS database columns • Methods used for storage ensure that only valid XML data are stored • Methods used for retrieval assist in converting XML data to DOM objects or Input-Source type (can be used for SAX or DOM methods) • The Informix JAXP API supports all IDS 7 and IDS 10 text data types (incl. TEXT, CLOB and LVARCHAR)

IDS and XML: CLOB/TEXT/LVARCHAR data types • All IDS text data types can be used for XML storage • Typically used for storage of complete documents or fragments • Archived copies of generated documents for legal purposes • Device independent publishing • Temporary storage of XML fragments • Can be easily combined with • JAXP API for XML parsing • XSLT DataBlade for transformation • Web DataBlade for publishing • Text Search DataBlades for searching (e.g. Verity Text Search) • CLOB/LVARCHAR allow seamless replication (ER, HDR)

IDS and XML: Spatial DataBlade 8.20 • Since version 8.20 the Spatial DataBlade supports the conversion into GML (Geography Markup Languge, based on XML) • Function SE_AsGML(ST_Geometry) (returns ST_Geometry) • Returns only XML fragments, not complete documents! CREATE TABLE mytable (id integer, location ST_Point); INSERT INTO mytable VALUES(1, ST_PointFromText('point (10.02 20.01)',1000)); SELECT SE_AsGML(location) FROM mytable WHERE id = 1; <gml:Point srsName="UNKNOWN"> <gml:coord><gml:X>10.02</gml:X><gml:Y>20.01</gml:Y></gml:coord> </gml:Point> SE_AsGML() function usage example

IDS and WebSphere MQ • WebSphere MQ: Reliable messaging infrastructure for SOA applications • Easy IDS / WebSphere MQ integration via WebSphere MQ DataBlade • New WebSphere MQ API for IDS 10, modelled after an existing DB2 MQ API • Allows an Informix application to easily interact with a WebSphere MQ based infrastructure • Additional Virtual Table Interface (VTI) support for simple SQL-/Table-based access to WebSphere MQ queues! • Comes bundled with IDS 10

execute function MQSend('AMT.SAMPLE.SERVICE', 'AMT.SAMPLE.POLICY', 'Hello Queue'); (expression) 1 1 row(s) retrieved. execute function MQReceive('AMT.SAMPLE.SERVICE','AMT.SAMPLE.POLICY'); (expression) Hello Queue 1 row(s) retrieved. insert into vtiMQ (msg) values ('Hello World!'); 1 row(s) inserted. select msg from vtiMQ; msg Hello World! 1 row(s) retrieved. WebSphere MQ DataBlade: Examples API Example: VTI Example:

IDS as a Web service provider: What is supported? • WORF based Web services • EGL based Web services • Stateless Session EJB Web services • Java Bean Web Services • The Java Bean could e.g. contain Informix JDBC calls • Java Bean Web Services based on the SQLtoXML/XMLtoSQL Java class libraries • .NET IDS provider allows easy integration into .NET Web services frameworks

IDS 10 IDS as a Web service provider: WORF • WORF (Web services Object Runtime Framework ) is the runtime component of DADX based Web services. • WORF uses the SOAP protocol and DADX files and provides the following features: • Resource based deployment and invocation • Automatic service redeployment, at development time, when defining resource changes • HTTP GET and POST bindings, in addition to SOAP • Automatic WSDL and XSD generation, including support for UDDI Best Practices • Automatic documentation and test page generation

IDS as a Web service provider: WORF (cont.) • Document Access Definition eXtension (DADX) Web services: originally developed with IBM DB2 and XML Extender in mind • Easily wrap IBM DB2 XML Extender or regular SQL statements inside a Web service. • Supported DADX functions for IDS: • Query • Insert • Update • Delete • Call Stored Procedures (limited support for IDS 7)

WORF Example (1): Generated DADX file <?xml version="1.0" encoding="UTF-8"?> <dadx:DADX xmlns:dadx="http://schemas.ibm.com/db2/dxx/dadx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:schemaLocation="http://schemas.ibm.com/db2/dxx/dadx dadx.xsd"> <dadx:operation name="insertOneCustomer"> <dadx:documentation xmlns="http://www.w3.org/1999/xhtml"> </dadx:documentation> <dadx:update> <dadx:SQL_update> <![CDATA[ INSERT INTO itso.customer ( customer_num, fname, lname, company, address1, address2, city, state, zipcode, phone ) VALUES ( 0, :fname, :lname, :company, :address1, :address2, :city, :state, :zipcode, :phone ) ]]> </dadx:SQL_update> <dadx:parameter name="fname" type="xsd:string"/> <dadx:parameter name="lname" type="xsd:string"/> <dadx:parameter name="company" type="xsd:string"/> <dadx:parameter name="address1" type="xsd:string"/> <dadx:parameter name="address2" type="xsd:string"/> <dadx:parameter name="city" type="xsd:string"/> <dadx:parameter name="state" type="xsd:string"/> <dadx:parameter name="zipcode" type="xsd:string"/> <dadx:parameter name="phone" type="xsd:string"/> </dadx:update> </dadx:operation> </dadx:DADX>

WORF Example (2): Web service test client

IDS as a Web service provider: EGL based services • EGL is a Java based, 4GL-like language which is easy to learn • EGL has builtin Web services support • EGL communicates via JDBC with IDS 10 • EGL 6.x requires WebSphere Application Server for Web services deployment (additional app server deploymenmt support with EGL 7.x) • EGL is part of the Rational Software Development Platform (powerful IDE) • Very interesting for existing Informix 4GL customers • Supported 4GL to EGL conversion path!

IDS as a Web service provider: EGL based services

IDS 10 as a Web service consumer: Overview • Utilization of the leading extensible database architecture (DataBlade technology) of IDS 10 • User defined routines (stored procedures) in C or Java, to allow access to existing Web services • Simple integration of IDS J/Foundation and the Apache AXIS (SOAP) environment for Web service access • Web service access through IDS 10 adds new capabilities to existing SQL based applications (e.g. 4GL, ODBC, JDBC, ESQL/C etc.)!

IDS 10 as a Web service consumer: Development Development steps: • Step 1: Obtain access to the Web service description (WSDL file) • Step 2: Automatically generate Java classes with the AXIS code generator • Step 3: Write a simple Java ‚wrapper‘ UDR • Step 4: Compile the Java classes and create a JAR file • Step 5: Register the new Java UDR(s) with the database server (IDS 10) • Step 6: Execute the new Java UDR to access the Web service

IDS 10 as a Web service consumer: Step 2

IBM Informix and SOA Integration