Many enterprises running DB2 for z/OS have had the following pain for a long time. The organization uses multiple DB2 subsystems to store data specific to different divisions. However, there are other business users who need to work with data from these disparate systems in a single report.
For example, a number of user groups have threads like this that are filled with work arounds:
With of QMF 12.1, this question is answered in a powerful, simple and economical fashion using the QMF Data Service. This brief presentation will detail the user experience.
I have two tables, one in a DB2 Subsystem called EAST and one in a subsystem called WEST.
The data in these tables are different, but related, and I need them in a single report.
In the past I had to write two queries and then use a QMF PROC like this:
CONNECT TO EAST
RUN QUERY DEMO.GETREGIONB
CONNECT TO WEST
SAVE DATA AS DEMO.REGIONB(ACTION = REPLACE
RUN QUERY REGION_ANALYZE
This extracts the data from one DB2 subsystem and loads it into the second system and then runs a query that allows me to me to do a UNION or a JOIN or a CORRELATED SUBQUERY etc with these two tables.
With QMF 12, my administrator can create a data source that contains virtual tables representing both tables and I simply write and run the query with UNION or a JOIN or a CORRELATED SUBQUERY etc. with these two tables.
RUN QUERY REGION_ANALYZE
If I had twenty DB2 Subsystems, each with a version of this table, then the old style QMF PROC would have had to run twenty queries and do the I/O, SQL CREATE, and SQL INSERT, twenty times and THEN have DB2 perform the final JOIN, or UNION etc.
With this new approach, those twenty actions would still be reduced to a just single query going to QMF Data Service and the final JOIN, or UNION etc. would be done by QMF Data Service rather than DB2.
What is happening?
My administrative team installed the new QMF Data Service on the mainframe and configured it to look at these two tables. QMF Data Service only stores the info about where the tables are and what the datatypes of the columns are. My QMF for Workstation connects to QMF Data Service as if it were a DB2. When I send it the SQL, QMF Data Service parses the SQL, sends it to each of the DB2s and when the data returns to QMF Data Service, it performs the JOIN, UNION etc.
Simplicity for the user is not the only benefit.
When QMF Data Service connect to DB2 for z/OS, it uses a DRDA connection. So, the work done by DB2 is zIIP eligible at the rate for DB2 over DRDA. No matter which QMF client asks QMF Data Service for DB2 data, the final call from QMF Data Service to DB2 makes the work zIIP eligible.
Once the data is back in QMF Data Service, the work it does to JOIN or UNION etc. is 99% zIIP eligible. So, the transactional processing and complex SQL processing by DB2 is eliminated, the simplified work for DB2 is zIIP eligible, and the complex SQL processing handed off to QMF Data Service is 99% zIIP eligible.
QMF Data Service supports Views. Once the SQL has been properly designed and tested in QMF for Workstation, it can be used to create a QDS View so that the less technical users are not exposed to the complexities of the analytical SQL.
QDS will present other mainframe data alongside the DB2 data: IMS. ADABAS. SMF, VSAM, Sequential files. All are accessible via SQL from QMF for TSO, QMF for Workstation, QMF for WebSphere, and QMF Vision.