Data here, data there, data everywhere. This mantra could describe many AS/400 IT departments these days. You have data scattered throughout your organization on sources that can’t talk to one another. The majority of your business data resides on the AS/400, but what happens when an AS/400 application needs information from other sources? Users, for example, want your application to display all of the information associated with a customer. They don’t care about or appreciate the fact that the customer data is stored in DB2 Universal Database (UDB) for AS/400 as well as another database such as Oracle or SQL Server. Do you hire an army of programmers? If so, what interfaces or languages are available to those programmers? Or do you avoid programmer expense with a manual approach such as FTPing data between servers and risking stale or lost data?
These factors are why IBM developed DB2 DataJoiner, a whole new kind of database middleware that lets you access, join, and update data from different sources with a single SQL-based interface. Whether your data resides in Oracle, Sybase, SQL Server, Informix, Teradata, IBM, Virtual Storage Access Method (VSAM), or another DB2 UDB server, DB2 DataJoiner can access it seamlessly. DataJoiner is not just a middleware product; it’s a full-strength relational database, including global query optimization, with special support for heterogeneous data access.
Why AS/400 and DataJoiner?
So why does your AS/400 need additional software for heterogeneous database access? DB2 UDB for AS/400 (and the IBM DB2 family) has long supported the X/Open Distributed Relational Database Architecture (DRDA) standard for remote database access and interoperability. Today, AS/400 customers can use DRDA to access the other DB2 products running on UNIX and Intel servers, but not third-party databases.
Other database vendors such as Oracle and Sybase have actually delivered tools based on DRDA. Unfortunately, they have chosen to support only part of the standard: They implemented the DRDA specs that enable them to access data in other databases, but they have chosen not to support the DRDA support that would allow DRDA-compliant databases (e.g., DB2 UDB for AS/400) to access and update their data. That’s where DB2 DataJoiner comes into play in AS/400 land. The middleware portion of the product can
speak to both DRDA and non-DRDA interfaces (Oracle SQL*Net, ODBC, etc.) supported by other databases.
Think of DB2 DataJoiner as a language interpreter. Your AS/400 applications will send DRDA requests to DataJoiner, and DataJoiner will translate that request into a language that the third-party databases can understand.
Conversing with DataJoiner from the AS/400 is as simple as issuing an SQL request.
The SQL-based interface makes it very easy to access DB2 DataJoiner from just about any AS/400 application. With the DB2 UDB precompilers, you can embed SQL statements in RPG, C, C++, and COBOL applications on the AS/400. The following is a sample of the embedded SQL that you would use to read an Oracle table with DataJoiner from an AS/400 application:
EXEC SQL CONNECT TO DJOINDB;
EXEC SQL SELECT CUSTPREF INTO :LOCPREF FROM ORCUSTOMER WHERE CUSTID=22;
This example assumes that you have already completed the needed DataJoiner setup. (I cover this setup in my sidebars at www. midrangecomputing.com/mc.)
If you have used DRDA to access data on another AS/400 or another DB2 server in the past, you’ll notice that the SQL interface is exactly the same. These embedded SQL statements can coexist with an existing application that is using the native (i.e., non-SQL) AS/400 database interfaces, so you do not need to switch the entire application over to SQL. Other SQL-based interfaces on the AS/400—such as Net.Data, Java Database Connectivity (JDBC), Call Level Interface (CLI), and DataPropagator—can also leverage DataJoiner for heterogeneous database access.
When you look at DB2 DataJoiner closely, you’ll quickly notice that it doesn’t run on the AS/400; it runs only on Microsoft Windows NT and UNIX servers. You can easily install DB2 DataJoiner on the same NT or UNIX server that’s hosting the third-party database that you need to access. A more viable option is to install the product on the Integrated Netfinity Server for AS/400 (INS) to give you tighter integration between the AS/400 and DB2 DataJoiner.
More on DataJoiner
As I mentioned, you can view DB2 DataJoiner as just a language interpreter, but it’s also a full-fledged relational database. With DB2 DataJoiner, you can create a database and store and access data in that database. Thus, you can use DataJoiner for more than just accessing other databases; you can also use it as a database server. In addition, the DataJoiner product includes DB2 Spatial Extenders that enable you to store and manipulate geographic data and features components of IBM’s DataPropagator that enable heterogeneous data replication. Those features are outside the scope of this article.
After seeing how simple the SQL is for connecting to an Oracle database from the AS/400 with DataJoiner, you might be wondering how DB2 DataJoiner knows that it should connect to an Oracle server instead of another AS/400 or DB2 server. The answer is that you have to tell your DataJoiner database how to do this interpretation with some simple, one-time configuration steps.
In the SQL sample I gave, you will see that you’re connecting to a DataJoiner database called DJOINDB. You would create that database in DataJoiner and then set up your relational database directory on the AS/400 (using the Work with Relational Database Directory Entries [WRKRDBDIRE] command) so that the AS/400 can connect to DJOINDB on your NT or UNIX server. The DJOINDB DataJoiner database will then have its own catalog that contains information on how to access the Oracle database. To access a data source with DataJoiner, you update the catalogs by using the following DataJoiner SQL statements:
CREATE SERVER MAPPING
CREATE USER MAPPING
CREATE SERVER OPTION
CREATE NICKNAME
Essentially, these SQL statements allow you to tell DataJoiner where to find the target database server, how to communicate with this target database server (ODBC, SQL*Net, etc.), and how to find objects on the target database server. Depending on the server that you’re accessing, you may also have to load a data access module (ODBC, Sybase Open Client, etc.) on the server where DataJoiner is installed. This configuration allows DB2 DataJoiner to fulfill its role as a language interpreter for the AS/400. DataJoiner receives a DRDA request from the AS/400 and then looks in the catalogs to determine how it should translate the request into a language that the target database understands.
Once this configuration is complete, you can perform just about any SQL request against that target database: retrieve data, add or change data, create database objects, and even call stored procedures. When you’re accessing data, you are not limited to accessing a single database at a time; you can actually reference tables and combine them from different databases all on the same SQL statement—hence, the join part of the product name. So it’s very easy for an AS/400 application to present a consolidated view of the data for a customer with a single SQL statement, even when the customer data is scattered across multiple database servers.
Not only does DB2 DataJoiner translate the SQL request into a language that the target database understands, but it also optimizes that request by employing global optimization. When formulating how to translate your query, DB2 DataJoiner’s optimizer considers the relative CPU speed of each server, the relative I/O speed, and the relative network bandwidth. Then, the optimizer combines these factors with the statistics that it automatically gathers on each database server and determines the most efficient path to the information you need. The optimizer in DB2 DataJoiner also incorporates a sophisticated query rewrite phase that automatically transforms poorly written queries into a better, logically equivalent form that is less costly to execute. For some database management systems (DBMSs), users can attain better performance via DB2 DataJoiner’s unique query rewrite than they can by accessing the DBMSs directly.
From an AS/400 perspective, the only deficiency of DB2 DataJoiner is that it can understand only DRDA requests that are sent over an SNA connection. Thus, you will need to set up an Advanced Program-to-Program Communications (APPC) connection between the AS/400 and the server that’s hosting DB2 DataJoiner. Note that DB2 DataJoiner is not limited to using an SNA-based connection to access remote databases; only the conversations between the AS/400 and DataJoiner require an SNA connection. In fact, most of the heterogeneous database access that DataJoiner performs will be done over TCP/IP connections.
One possible alternative to eliminating this SNA requirement is the latest version—Version 7—of DB2 UDB for NT and UNIX. This version of DB2 UDB is able to process DRDA TCP/IP requests and has integrated some of the functionality of DB2 DataJoiner. The DataJoiner functionality that has been integrated into DB2 UDB’s federated database component includes support for DB2 server access and read-only access of Oracle databases. AS/400 customers will find DB2 UDB V7.1 to be an acceptable solution as long as they require only read-only access of Oracle data or need to combine DB2 and/or Oracle data in a single SQL statement.
Using DataJoiner with the AS/400
Now that I’ve covered all of the technology and terminology, I’ll take a look at the detailed configuration steps to enable DB2 DataJoiner to work with the AS/400. This configuration really has two distinct parts because DataJoiner accepts only DRDA SNA requests. First,
you’ll need to establish the SNA connection between the AS/400 and DataJoiner’s hosting server. Once the communications pipe is set up, you need to update your DataJoiner database catalogs with the target database information. In my sample installation, DB2 DataJoiner has already been installed on the INS. You can also use these steps when configuring DataJoiner on a standalone NT server.
The first step is configuring a DRDA SNA connection between DB2 UDB for AS/400 and DataJoiner running on the INS. For this step, the NT server hosting DataJoiner must have an APPC-capable server such as IBM’s Communications Server. IBM’s Communications Server (Version 6.1) was used in building the configuration information. The sidebar “Configuring Communications for DataJoiner,” which can be found on the Midrange Computing Web site at www.midrangecomputing.com/mc, details the steps.
Configuring the DataJoiner Connection
Once you have the communications configured to allow DB2 UDB for AS/400 to talk with DataJoiner, the next step is adding the necessary mappings and nicknames into DataJoiner so that it knows which database servers and objects you want to access.
The sidebar “Accessing Other DB2 UDB Servers,” which can be found on the Midrange Computing Web site at www. midrangecomputing.com/mc, shows the steps required for configuring a DataJoiner connection between the AS/400 and various other DB2 servers. I used the DataJoiner Command Line Processor Window to perform the configuration scenarios. Note that most configurations will use DataJoiner to perform read- only access of remote database servers; therefore, these configuration samples turn off two- phase commitment control. You should use this feature only when really needed.
As you can see, DataJoiner not only allows you to access non-DB2 databases from DB2 UDB for AS/400 but also gives you the capability to combine database tables from different database servers. If you wanted to join all of the tables configured in the sidebars, you would just issue the following SQL statements on the AS/400:
SELECT * FROM PIDAORD, SSAUTHORS, ORATABLE
A Whole New Class
DB2 DataJoiner is the heterogeneous access solution that your business might need for addressing the data scattered across your IT shop. DataJoiner not only allows your AS/400 applications to access and process non-DB2 data seamlessly but—via its global query optimization—also makes sure that your applications perform this remote data access as fast as possible. Simply put, DB2 DataJoiner is a whole new class of data access middleware.
REFERENCES AND RELATED MATERIALS
• DataJoiner Application Programming and SQL Reference Supplement (SC26-9148 )
• DataJoiner Web site: www.ibm.com/software/ data/datajoiner
• DB2 DataJoiner for UNIX Systems Planning, Installation, and Configuration Guide (SC26-9145)
• DB2 DataJoiner for Windows NT Systems Planning, Installation, and Configuration Guide (SC26-9150)
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online