Adabas 2006 SQL and XML Gateway by uzd19483

VIEWS: 39 PAGES: 43

									Adabas SQL Gateway


     Becky Albin
     Chief IT Architect

     Becky.Albin@softwareagusa.com
Adabas Architecture




                      Natural Boston Conference 2005 | Page 2
Get to Adabas from .NET and JAVA


Imagine …

   ¬ If access to Adabas from JAVA and .NET was as easy as it is from
     Natural
   ¬ If Adabas could drive your Java and .NET applications
   ¬ If the “native” power of Adabas was fully accessibly via open
     interfaces
   ¬ If your application programmers could access Adabas with no
     knowledge of Adabas

   ¬ … it is possible…




                                                     Natural Boston Conference 2005 | Page 3
Open Connectivity and
Real-time Data Sharing
                                                      Java & .NET,
                                        Business                            3GL and
Challenges                             Intelligence
                                                        Packaged
                                                       Application
                                                                            Natural
    ¬    Federated/Heterogeneous
         application and database
         landscape
    ¬    Cross-platform application
                                       SQL   Web Services        XQuery         Direct
         development: Mainframe,
         Java or Microsoft .NET                       Open Access


Adabas
    ¬    Adabas SQL Gateway (ACE)
    ¬    Adabas SOA Gateway (ASG)
    ¬    Real-time data sharing with
         Event Replicator for Adabas             Open Replication




                                       XML    SQL         EAI                Data
                                             DBMS      Middleware          Warehouse



                                                Natural Boston Conference 2005 | Page 4
Connecting Adabas to .NET, JAVA and Web Services




                    Adabas    SOAP/WSDL
                     SOA
                    Gateway   HTTP/XML

Adabas
                    Adabas     JDBC
                     SQL
                    Gateway    ODBC




                                          Natural Boston Conference 2005 | Page 5
 Adabas SQL Gateway (ACE) Architecture
                       Linux, Unix, Windows , z/Linux
                                                                      z/OS, VSE, Unix, Windows, Linux


                        O                                                       Connx Listener
                        D           Adabas SQL
                        B            Gateway                 TCP/IP
                                      Client                                      Adabas SQL
                        C
Desktop Apps:                                                                      Gateway
Crystal Reports,                                                                    Server
Business Objects
and access from .NET
and OLE DB                                                                         ADALNKR
                                         Data
environments                           Dictionary


                             Linux, Unix, Windows, z/Linux



                         J
                         D          Adabas SQL
                         B           Gateway                 TCP/IP
                         C          Java Server
Java Programs
with embedded
SQL statements

                                        Data
                                      Dictionary

                                                                           Natural Boston Conference 2005 | Page 6
Key Features – Adabas SQL Gateway


¬ Provides cost-effective mainframe data reusability by extending access to
  Adabas via standard SQL applications

¬ Eliminates the need to duplicate or move data to a data warehouse with
  direct access to Adabas information from the desktop

¬ Increases productivity for both IT programmers and business users by
  reducing the number of IT requests for Adabas information/reports

¬ Lowers operational costs with an easy to install, high performance SQL
  engine that provides quick and easy access to enterprise data

¬ Extensible by other SQL Adapter to do heterogeneous joins across
  disparate DBMS sources



                                                      Natural Boston Conference 2005 | Page 7
Natural Boston Conference 2005 | Page 8
Natural Boston Conference 2005 | Page 9
Bloor Research Report
                                   “  This ability to query multiple data sources means
                                   that the SQL Gateway provides support for a
                                   ‘single view’ of customers, products or whatever.”


                                   “…the XML support provided by Adabas is market
                                   leading: for example, very few, if any, competitors
                                   have yet introduced support for XQuery.”

                                       “The change data capture facility supported
                                       by Software AG is genuinely real-time, which is
                                       not always the case with some other vendors. ”




“Indeed, with Software AG’s emphasis on opening Adabas to any environment with
XQuery, Web Services and SQL, together with its ability to push data from Adabas to
any environment in real-time, Adabas provides comparable, if not better, capabilities
to other modern and progressive databases.”



                                                            Natural Boston Conference 2005 | Page 11
Architecture - Adabas SOA Gateway



               APACHE HTTP
                                SOAP/XML        Command              Adabas
                             Processing using     Level
  APACHE SSL   Communication
                                 XERCES         Interface             SOA
    Security
                     XML <-> ADABAS Mapping Technology
                                                                     Gateway




                                   Adabas




                                                  Natural Boston Conference 2005 | Page 12
SQL and SOA Gateway – What‟s the difference?


  Adabas SQL Gateway Adabas SOA Gateway
 Table-oriented data view              Document-oriented


 Uses client-site JDBC and ODBC        Uses standard Web Service
 database driver                       interfaces (SOAP, WSDL, HTTP) –
                                       loosely coupled
 Uses SQL for query processing and     Uses XML retrieval services or the
 DDL for data definition               XQuery standard and XML Schema
                                       (XSD) for definition
 Supports all major ad-hoc reporting   Supports any Web Services for new
 and data warehouse environments       application development



                                                     Natural Boston Conference 2005 | Page 13
Which Adabas Gateway Should be Used ?
        Requirement                         Recommended
                                              Gateway
 Data accessible using standard SQL    Adabas SQL Gateway
 Query tools

 Data accessible from .NET or JAVA     Adabas SQL Gateway or the Adabas
                                       SOA Gateway depending on
                                       preference.
 Corporate strategy to focus on data   Adabas SOA Gateway
 exchange using XML

 Corporate strategy to implement a Adabas SOA Gateway
 Service Oriented Architecture (SOA)

 Corporate focus on ODBC and JDBC      Adabas SQL Gateway
 access to data
                                                    Natural Boston Conference 2005 | Page 14
What the Gateways have in common?

¬ No additional programming required

¬ Support for Adabas security

¬ No knowledge of Adabas required by programmers

¬ Open Standards

¬ OLTP levels of performance

¬ Traditional „mainframe class‟ stability and service

¬ Simple installation and configuration

¬ Support for all Adabas platforms

¬ Close integration with Adabas


                                                        Natural Boston Conference 2005 | Page 15
Adabas SQL Gateway Strengths

High performance distributed SQL engine
   ¬   … distributes query processing between multiple systems.

ANSI 92 SQL Support for SELECT, INSERT, UPDATE, DELETE
   ¬   … supports complex SQL statements, correlated sub-queries, unions, etc. Giving
       the flexibility of using the full power of SQL to accomplish business goals.

Advanced SQL Optimizer
   ¬   … tools that will display the query plan and allows you to adjust SQL in order to
       control query execution paths.

Future integration with Adabas
   ¬   …more tightly integrated with Adabas, offering enhanced SQL capabilities.




                                                              Natural Boston Conference 2005 | Page 16
ACE - Functionality Overview
   ¬ ODBC SQL Level 2

   ¬ Security

   ¬ Meta Data Management

   ¬ Enhanced SQL Capability

   ¬ Views

   ¬ Heterogeneous Joins

   ¬ Bi-directional Data Conversion

   ¬ Read/Write Access to Data



                                      Natural Boston Conference 2005 | Page 17
ACE - Components

  ¬ Data Dictionary

  ¬ ODBC Driver

  ¬ OLE DB RPC Driver

  ¬ Database Server

  ¬ JDBC Driver (Thin client)

  ¬ JDBC Server

  ¬ JDBC Router




                                Natural Boston Conference 2005 | Page 18
ACE - Data Dictionary

Administrator Component – CDD contains

  ¬ information describing the data tables and fields

  ¬ in the accessed databases

  ¬ including security

  ¬ contains the metadata about the source information

  ¬ provides easy maintenance of the metadata, views, and
    integrated security



                                               Natural Boston Conference 2005 | Page 19
ACE - Data Dictionary


Administrator Component                                 CDD


¬   CDD parent/child topology

                                            CDD *                            CDD *
¬   One central CDD

¬ A logical CDD with one or more
child CDD

¬   Group CDD with its own rights   CDD *           CDD *                    CDD *



                                        * Department
                                        Schema, View, User, Tables, Security



                                             Natural Boston Conference 2005 | Page 20
ACE - ODBC Driver

  ¬ Is tightly coupled with the CONNX Data Dictionary.

  ¬ Uses Structured Query Language (SQL) as the standard for
    accessing information.

     ¬   ODBC SQL Level 2 compatibility.

  ¬ Enables the use of off-the-shelf ODBC-compliant reporting and
    development tools.




                                              Natural Boston Conference 2005 | Page 21
ACE - OLE RPC Server



    ¬ The OLE RPC Server allows the user to make remote
      procedure calls (RPC) from any programming language that
      supports OLE 2.0.

    ¬ Supported tools include:
       ¬   Microsoft® Access
       ¬   Microsoft® Excel
       ¬   Microsoft® Visual Basic®
       ¬   Microsoft® Visual C++®




                                             Natural Boston Conference 2005 | Page 22
ACE - Database Server



    ¬ Translates SQL requests into native database requests.
       ¬   The ODBC/JDBC driver makes the translation service
           transparent to the end user.

    ¬ The primary features of the driver include the following:
       ¬   SQL access to target sources
       ¬   Complete user-level and group-level security
       ¬   Low memory and disk resource utilization
       ¬   Complete file and/or table security




                                                   Natural Boston Conference 2005 | Page 23
ACE - Database Server



¬    Communication between client and server.


        Client                        1              Listener
        UserID / Password                            Port Number


                                      3 (4)      2

                               4
Import                                          User’s Worker Task
-   DBID          CDD                           Sub-Task
-   Host
-   User ID
-   Password
                               Firewall
-   Port Number

                                                  Natural Boston Conference 2005 | Page 24
ACE - JDBC Driver


    ¬ Used with Data Dictionary (CDD), the JDBC driver
      provides a means of using many popular querying
      tools and application development tools.

    ¬ Works with JDBC-compliant software.

    ¬ Enables applications to access data using the JavaSoft JDBC API.

    ¬ The JDBC Driver / Router:
        ¬   Runs as a Windows service
        ¬   Is the connection between client and server
        ¬   The router is used on Unix as a connector

    ¬ Type 3 - Net Protocol, Pure Java




                                                          Natural Boston Conference 2005 | Page 25
An inside tour of the Adabas SQL Gateway
     Adabas Related Functionality
ACE - Meta Data
Meta Data Import – Natural DDMs

   ¬ Unload Natural DDMs via the Natural Object Handler

   ¬ Select the appropriate parameter

      ¬   Transfer Format
      ¬   Enter location for the unload file
      ¬   Select Default Options

   ¬ Select DDMs




                                               Natural Boston Conference 2005 | Page 27
ACE - Meta Data
Meta Data Import – Natural DDMs

   ¬ Make the unload Natural DDMs available

      ¬   FTP or network drive

   ¬ Start the CDD Manager

      ¬   Select Import, Import Type – Adabas Systrans
      ¬   Enter the appropriate parameters
           ¬ DBID, Systrans file, ADASCR password, Host, etc.

   ¬ Start Import



                                                   Natural Boston Conference 2005 | Page 28
ACE - Meta Data
Meta Data Import – Adabas LF

  ¬ Start the CDD Manager

     ¬   Select Import, Import Type – Adabas LF
     ¬   Enter the appropriate parameters
          ¬ DBID, Systrans file, ADASCR password, Host, etc.

  ¬ Select file(s) to be imported
     ¬   Adjust definitions if necessary

  ¬ Save data either in a new CDD or in a existing one




                                                  Natural Boston Conference 2005 | Page 29
ACE - Meta Data
Meta Data Import – Adabas Dynamic DDL

  ¬ Start the CDD Manager

      ¬   Select Import, Import Type – DDL import
      ¬   Enter the appropriate parameters
           ¬ DBID, Systrans file, ADASCR password, Host, etc.

  ¬ Select file(s) to be imported
      ¬   Adjust definitions if necessary

  ¬ Save data either in a new CDD or in a existing one

  ¬ Support of Natural Date / Time fields


                                                   Natural Boston Conference 2005 | Page 30
ACE - Adabas Security Support

External Security

   ¬ ACE server-based
      ¬   Client user ID and password are used to access RACF at
          host-side
      ¬   APF authorized library needed
      ¬   Should not be used together with SAF Gateway
            ¬ Performance – double-checking

   ¬ AAF-based
      ¬   Client user ID and password are passed through to
          Adabas
      ¬   No APF authorization needed for ACE server




                                                              Natural Boston Conference 2005 | Page 31
ACE - Adabas Security Support
Database Security

   ¬   Adabas Security Support

       ¬   allows the usage of external security tools on mainframe

       ¬   Makes use of ADASCR
            ¬ Define a global password in CDD
            ¬ Specify a password in the SQL statement

            ¬              SELECT * FROM
            ¬              adabas_windows.dbo.CUSTOMERS_ADABAS
            ¬              {fn setadapassword CUSTOMERS_ADABAS,
            ¬              PASSWORD}




                                                            Natural Boston Conference 2005 | Page 32
ACE - Security Capability
Security model with the following features:
   ¬   Established Security Measures Remain Intact
        ¬    CONNX does not bypass security measures established by a database or operating system.
             For example read-only access to a table remains.

   ¬    Secure Management
        ¬    View, table, and column security levels can be assigned to an individual user, or to a group of users.
             Record security levels can be established through CONNX views.

   ¬    Seven Access Levels
        ¬    CONNX supports seven access levels:
        ¬    Select Read-only access
        ¬    Update Update queries
        ¬    Insert Insert new data
        ¬    Delete Delete data
        ¬    Drop    Drop tables
        ¬    Execute Execute stored procedures
        ¬    RFI     Enable       Referential Integrity




                                                                                 Natural Boston Conference 2005 | Page 33
ACD - Security Capability
Security model with the following features:

   ¬   Integrated Security/Database Account Management
       ¬   enables users to specify a single CONNX user name and password to access
           multiple databases that may each require a distinct user name and password.
       ¬   Secure Data Dictionary
       ¬   The Data Dictionary is encrypted to only allow access to authorized users.

   ¬   Maximum Security Option
       ¬   allows only users defined in the CONNX Data Dictionary to access data,
           regardless of database permissions.




                                                            Natural Boston Conference 2005 | Page 34
ACE - Security Capability


Security model with the following features:

   ¬   Read Only Default Access Option
       ¬   This option restricts all access to all tables in the CONNX Data Dictionary to
           Read Only.
           Can be overridden by specifying User or Column level security.




                                                              Natural Boston Conference 2005 | Page 35
ACE - MU / PE Fields


Presentation

    ¬   MU fields are presented as Sub-tables

    ¬   PE fields are presented as Sub-tables

    ¬   MU within PE fields are presented as
        Sub-tables




                                                Natural Boston Conference 2005 | Page 36
ACD - MU / PE Fields


 Example




                       Natural Boston Conference 2005 | Page 37
 ACE - MU / PE Fields
Create a Table

    ¬     Creates a SQL table to represent every top-level MU and PE
          field.




    ¬     Employees Sample Adabas file


        ADDRESS_LINE                     Multi value field
        INCOME                           Period group (this group contains a multi
                                         value field called BONUS)
        BONUS                            Multi value field (within the INCOME
                                         period group)
        LANG                             Multi value field
        LEAVE_BOOKED                     Periodic Group



                                                             Natural Boston Conference 2005 | Page 38
ACE - MU / PE Fields
The ISN is used to link the “ROOT” table to the rotated array tables.




                                                  Natural Boston Conference 2005 | Page 39
ACE - MU / PE Fields
Parameters

  ¬ CNXARRAYCOLUMN
     ¬   A pseudo column field that represents the # of the occurrence for this SQL
         record. This numeric field is zero based

  ¬ AutoNumber
     ¬   CONNX reports the ISN field or the CNXARRAYCOLUMN field as an
         “autonumber” field. This suggests to applications that the database
         automatically determines the next value for newly inserted records




                                                           Natural Boston Conference 2005 | Page 40
ACE - MU / PE Fields
Access to MU / PE fields
   ¬ Insert = Update?
       ¬   Yes, Inserts into Adabas rotated array fields are actually translated into an
           “update” of the existing record … adding the new occurrence.

   ¬ ISN field required
       ¬   When issuing a SQL Update against a rotated table, the ISN field # is required

   ¬ Deletes not possible for PE fields because of Adabas

   ¬ ISN field required.
       ¬   When issuing a SQL Update against a rotated table, the ISN field# is required.




                                                              Natural Boston Conference 2005 | Page 41
ACE - MU / PE Fields
Update examples

  ¬ update employees set LANG_2 = „ENG‟ where ISN_12_11 = 1004
  ¬ update employees_lang set LANG = „ENG‟ where ISN_12_11 = 1004 and
    cnxarraycolumn = 1




                                                  Natural Boston Conference 2005 | Page 42
Software AG.
Bright ideas, everywhere.

								
To top