This document provides an introduction to the CONNX SQL Engine (CONNX).
CONNX is an SQL Gateway which enables you to use SQL to access your relational and non-relational business data.
CONNX can be used to unlock your organization's legacy data and makes it accessible to web-enabled front ends and critical applications at a fraction of the time and cost required for conventional in-house implementation.
CONNX offers real-time, read/write SQL access, using open data access standards, to virtually any legacy or relational data source. It can join multiple data sources (no matter where they reside) giving you a single view of your data as if it exists in a single relational database.
With CONNX, a single metadata model can be created that spans all enterprise data sources and applications requiring data access. The result is an enterprise-wide view of data that provides a reusable standards-based framework for information access.
The CONNX SQL Engine can be distributed throughout multiple processors and multiple platforms - from z/OS, to UNIX, to Windows - to scale for large enterprise requirements. The CONNX Data Dictionary Manager is the graphical interface for integrating and presenting enterprise information from multiple sources. Adapters for ODBC, OLE DB, JDBC, .net, or Java applications provide simple and efficient access to legacy data through CONNX Data Dictionaries (CDDs).
CONNX integrates enterprise data sources, whether relational or non-relational, making them appear as a single SQL-accessible database. CONNX includes adapters for the most common enterprise databases, a high-performance distributed SQL processing engine, and support for industry-standard programmatic access methods. CONNX enables Enterprise Information Integration (EII), a cornerstone of Service-Oriented Architectures (SOA) and Business Intelligence (BI) solutions, as a data services layer presenting data from multiple sources.
CONNX is a simplified data access solution that preserves and enhances database systems by providing heterogeneous, secure and real-time access to disparate databases through a single driver. This means that disparate data sources can be treated as one relational database, allowing the enterprise complete database flexibility. The CONNX product line provides access to several types of databases through open data access technologies including SQL, ODBC, OLE DB and JDBC. CONNX is compatible with any ODBC and JDBC compliant front-end application, thus providing flexibility for end users. CONNX also supports a vast amount of database-specific data types for data conversion.
CONNX has a distributed SQL engine which enables query processing to be distributed between the client and the server. Most of the CPU-intensive query processing, such as data conversion and sorting, is performed on the PC. All data retrieval is performed on the server. In the case of desktop databases, such as DataFlex or Access, all processing is done on the PC because there is no server component.
The CONNX Distributed SQL Engine provides:
Efficient Workload Distribution: The workload on the data server is minimal, because CPU-bound tasks are moved to the client, resulting in a reduction of load on the data server and better performance across the enterprise.
Fast Performance: CONNX utilizes this engine by distributing query processing between the client and the server without sending duplicate data across the network. As a result, the work is done on the machine that is best suited for the task
Scalability: CONNX is easily adaptable to all environments, enabling organizations to scale from project to enterprise; from two-tiered to n-tiered; and from one to many data sources. Thus, the amount of resources available to CONNX increases as you add clients.
Cross-Platform Joins: With a single SQL statement, CONNX allows your applications to join related data across systems as if all data existed in one relational database. When performing joins, significantly less data is sent across the network because no duplicates are transmitted.
Business Views: CONNX supports the creation of data views across and within data sources, allowing related data from different sources to appear as one logical table and enabling the simplification of complex structures.
Security: CONNX respects all existing database security and provides additional user group, row, field, table and database security.
Enterprise database security information is maintained in the distributed SQL-processing client, and provides access control on a per user, per group, or application basis. No custom development is required, while deployment time is reduced to a minimum.
The table below shows the workload distribution as implemented with CONNX:
| Client PC | Server |
|---|---|
| Meta Data Retrieval (CONNX CDD) | Indexed Retrieval |
| First Pass SQL Optimization | Non-Index Retrieval |
| Partial Joins | Partial Joins |
| Sorting | Data Compression (If Requested) |
| Grouping | RPCs (Remote Procedure Calls) |
| Extended SQL Functions | Database Security (Client) |
| CONNX Security |
CONNX provides connection to both relational and non-relational data sources, transaction systems and legacy applications through open data access standards across all major computing platforms.
| Relational Data | Non-Relational Data | ODBC and OLE DB |
|---|---|---|
| DB2 | C-ISAM | ODBC Compliant Data Sources Data Source |
| Informix | D-ISAM/DISAM96 | Database-Specific ODBC driver |
| Oracle | Codasyl DBMS | OLE DB Compliant |
| PostGreSQL | IMS | Any OLE DB Data Source |
| RDB | MicroFocus | Adabas D |
| SQL Server | DataFlex | |
| Sybase | POWERFlex | |
| RMS | ||
| VSAM | ||
| Adabas |
CONNX web enables enterprise data with the use of any standards-based development platform (such as Microsoft ASP.NET, IBM WebSphere, and Apache Tomcat). Custom applications can be created or turnkey applications can be used for Internet-based solutions. The CONNX SQL interface allows users to seamlessly plug into any application, report writer, or development tool that is ODBC-, JDBC-, OLE DB- or .NET-compliant. As a result, direct access is available to data from any preferred reporting or development tool, which allows new applications to be built with modern Web development tools, such as WebLogic, WebSphere or .NET.
Using CONNX web services:
it is easy to enable applicaltion users to access company data via the Web without IT involvement.
it is easy to Web enable any of the 40+ databases for which CONNX provides connectivity. Direct heterogeneous access is available to those databases from a number of platforms. As a result, joins can be performed across different data types.
CONNX provides quick and easy access to information from legacy and relational databases using virtually any tool that is compatible with ODBC and OLE DB including the CONNX desktop Query/Reporting Tool, InfoNaut Professional.
InfoNaut is a desktop query/reporting tool that enables end users to quickly and easily access information from enterprise and legacy databases without IT involvement. In addition, end users are able to view reports in a readable format. Queries and SQL statements created in InfoNaut can be saved. The standard version of InfoNaut is delivered with the product.
The major features of InfoNaut are:
| Feature | Description |
|---|---|
| Develop SQL Syntax | Use the query builder to graphically build SQL statements. |
| Test database connections | Verify connectivity to a specific database. |
| Statistics | View the CONNX query plan in order to optimize performance and ensure that table indexes are being executed correctly. |
| Universal SQL Syntax | Follows the ANSI 92 SQL standards. |
| Cross Platform Joins | Perform joins between tables and disparate databases. |
InfoNaut Professional has additional features enabling the end user to save queries and SQL statements created in InfoNaut Professional as XML files , Microsoft Query .dqy files, Microsoft Excel .xls files, HTML pages, Active Server Pages (.asp), .csv files, .sql files, and .txt files.
The Report Writer feature enables users to create reports from table information or queries. Reports can be used for simple or formal presentations and can be quickly customized using a wide range of formatting tools.
Reports created with the InfoNaut Report Writer can be saved as Adobe Acrobat .pdf files, HTML pages, Microsoft Excel spreadsheets, Rich Text Format files, .tif files, .csv files, and .txt files.
ACEINT is a command-line query/reporting tool that enables end users to quickly and easily access information from enterprise and legacy databases without IT involvement. ACEINT is delivered as a part of the Embedded SQL components.
CONNX supports industry access standards including: SQL, ODBC, OLE DB, JDBC and .NET.
CONNX preserves your existing legacy systems investment by providing standards-based real-time read/write access to the data you need, often eliminating the need to migrate data or create data warehouses.
A single metadata model can be created, spanning all enterprise data sources and applications requiring data access.
When you move from one application platform to another, your investment in CONNX will not be lost. CONNX acts as a middle layer between your applications and reports. Applications will continue to work as designed even though the data has moved and the data structures have changed.
CONNX is a reusable data access framework for projects throughout the enterprise: for developers who can treat the data as one large virtual database; for administrators, who can more easily monitor and maintain the database; and for users, who can take advantage of the convenient packaging of the data to query across all enterprise databases to provide compact, precise, and specific information.
The CONNX high-performance architecture can benefit any enterprise burdened with disparate operating systems and multiple legacy data sources that is striving to gain control of its data source development projects.
CONNX makes data more accessible and more user-friendly. Through CONNX features such as Aliases and Views, data structures, field names, and pre-join related data can be simplified for presentation to end-users without modifying the underlying database.
When it comes to delivering your Enterprise Information Integration project on time and on budget, the CONNX solution is unmatched in security, usability, flexibility, and performance.
The CONNX approach to data access is to treat all of the different data sources as a single database, providing access to all data in the enterprise through a single open-standards-based driver. CONNX provides secure read/write access to the data using the following main components:
The CONNX servers for Oracle RDBMS, DB2, Sybase, Informix, Ingres, CISAM, VSAM, SQL Server, Oracle DBMS, PostgreSQL, Oracle Rdb, Digital RMS Files, DataFlex, and Adabas are full-featured data servers that translate SQL requests into the native database requests. The CONNX ODBC Driver makes the server transparent to the end user. The CONNX Data Server features include:
Complete user level and group level security in addition to respecting existing security.
Low memory and disk resource utilization.
Complete file and/or table security.
RPC Support (for VMS & UNIX only).
The CONNX Data Dictionary (CDD) is a repository of information describing the data tables and fields in the accessed databases, including security. The CDD contains the metadata information about each data source and provides a graphical user interface for easy maintenance of the metadata, stored procedures, integrated security and views.
The CONNX Data Dictionary:
Describes the structure of the tables or files being accessed.
Creates a single metadata model that spans enterprise data sources and applications requiring data access. The result is an enterprise-wide view of data and provides a reusable standards-based framework for information access.
Imports metadata directly from existing file layouts.
Enables multiple views of the same data.
Allows access to data sources, including databases, flat files and OLE DB or ODBC compliant data sources.
Offers access to CONNX views, which combines tables from one or more data sources.
Facilitates automatic conversions of legacy data types to SQL data types.
Supports Occurs Clauses.
Allows importation of existing data definition information.
Resides on a shared server disk or on a client computer.
Provides protection to the field and record level. Encrypted metadata security.
Offers Password security to prevent unauthorized users from modifying the CDD.
Provides Wizard Assistance for common tasks.
CONNX also offers a desktop query/reporting tool, called InfoNaut Professional. This tool enables end users to quickly and easily access information from enterprise and legacy databases. The reporting feature allows end users to view reports in a readable format.
The CONNX Client is based on an ODBC driver, which is a dynamic link library that applications call to access data located in remote systems. The CONNX ODBC driver processes the ODBC function calls, submits requests to the appropriate data source, and then returns the results. The CONNX Client features include:
Tightly coupled with the CONNX Data Dictionary.
Uses Structured Query Language (SQL) as the standard for accessing information.
Provides the ability to use off-the-shelf ODBC compliant applications without additional programming effort.
The CONNX JDBC Thin Client allows read/write access to a CONNX data source from any client machine possessing a JDK (1.3). JDKs exist for most platforms. The CONNX JDBC Thin Client is a type 3 driver which processes the JDBC function calls and submits requests to the CONNX JDBC Server, then returns the results.
The CONNX JDBC Server handles requests from the CONNXJDBC Thin Client and accesses the target data sources. The CONNX JDBC Server component enables access to multiple heterogeneous data sources on both Windows and Linux/UNIX platforms.
The CONNX applets are served by a non-Windows Web server. The router is a Java application placed on the non-Windows Web server, and routes JDBC requests to the CONNX JDBC Server.
For many organizations, Adabas, IBM VSAM and IBM IMS applications running on z/OS, OS/390, z/Linux and VSE provide mission-critical functionality with exceptional reliability, availability, and serviceability. Therefore, migrating such systems make not make good business sense.
Nonetheless, many organizations have a need to integrate these legacy applications with relational applications, and are also searching for a solution that enables users with advanced SQL skills to access legacy data using standard SQL-based tools.
CONNX solutions gives such users secure real-time, read/write SQL access to multiple data sources whether they are legacy, relational, or both. CONNX supports Adabas, IBM VSAM and IBM IMS on z/OS, OS/390, z/Linux or VSE.
With CONNX, users can:
Use modern reporting tools such as Crystal Reports, MS Access, Excel or Cognos to directly access OpenVMS and other legacy data.
Build GUI or Web front-end interfaces to access legacy applications.
Join tables of multiple disparate data sources enabling the creation of several reports with a single query.
Take advantage of a integrated, centralized metadata repository.
CONNX features a native Linux/UNIX implementation of the proven CONNX SQL Engine, enabling Web services and Java applications to access enterprise data as a single virtual database. Mixed platforms are no longer required, reducing complexity and cost for the enterprise application developer.
The CONNX SQL Engine provides real-time and simultaneous access to a wide array of relational and non-relational data sources. The Linux/Unix platform makes use of the CONNX JDBC server, which plugs in to the CONNX pure-Java JDBC Driver. Organizations that use Linux, AIX, Solaris, or HP-UX as their primary operating system in the back office can deploy applications that store data on an open system, on mainframes, and on Windows. CONNX can be deployed behind Web servers in Linux or UNIX environments with no additional server hardware.
CONNX has been an essential tool for many years for organizations that want to preserve their investment in applications relying on ODBC and JDBC access methods and legacy data. The CONNX technology has traditionally been Windows-based, designed to function as a gateway to Java data sources. Developers using CONNX have been able to access and integrate legacy data with standard SQL-based technology, thereby extending the functionality of their current Java applications and avoiding costly migrations.
For developers, CONNX enables the use of a Windows platform with Microsoft’s SQL Server 2005 or Linux/UNIX platforms for total data access.