There are many types of DB2 applications, from batch to online to data warehouse to analytics to data science, you name it, there are as many types as you can think of. In this day and age, though, there are a growing number of applications that require the transmission of query results to other platforms so that this information will then be joined with information coming from other sources. Stop there for a moment and look into your site to try to count how many transmissions are executed hourly and then try to further refine those results to select which transmissions contain the edited result of a DB2 query, and you will be surprised by how close those two numbers are.
Now consider this situation: There’s a remote application for which you know the tables it works with, but the queries will constantly change because of the business needs and the nature of the information contained within those tables, and the query results must be transmitted in a text file to another server. Many, including myself, would say that it sounds like a usual call we receive almost every day, and for that reason I present a somewhat unusual feature of DB2 that you can consider for such cases: DB2 query support over FTP.
Implementing DB2 query support over FTP
It seems unlikely to hear that you can actually execute queries over FTP, but if you’re on z/OS 1.13 or later, it’s possible that your site already has it enabled it but it hasn’t been customized to your site.
In a nutshell, this feature allows users to execute SELECT statements on the DB2 for z/OS server by using FTP functions such as STOR and RETR, and it requires an additional PLAN to be bound and then registered on your FTP.DATA configuration member.
The logic of its use is very straightforward.
You create a text file on the client side containing the SELECT statement by calling the FTP STOR function and upload it into your MVS library.
Afterwards, all the user needs to do is logging on the MVS FTP server, switching filetypes to indicate this is a DB2 call and then calling the RETR function to obtain the member back. What the server will return is the result of the query.
Very easy to use, and also very easy to implement as you’ll soon see: The instructions to implement this feature are as follows:
- Locate your site’s SEZAINST dataset and find member FTOEBIND. Inside this member you will find the necessary BIND command to be executed and it will create the PLAN EZAFTPMQ that you will add on the next step. This member also contains the GRANT statement so that you can give access to the application user or any other users that you might find appropriate.
- Locate your FTP.DATA configuration member (the sample FTP.DATA is located as member FTPSDATA in your SEZAINST dataset), and find section number 7 (DB2 (SQL) interface options), and you’ll find the following fields that you will modify:
DB2 = The name of your DB2 subsystem.
DB2PLAN = The name of the plan to be executed, by default it points to EZAFTPMQ.
SPREAD = This field indicates whether you want the output in spreadsheet format or not (very important to set it to TRUE so that you can read it later with Spark or Python Pandas).
SQLCOL = This fields specifies if you want to display the column names on top.
Side note: the standard (default) location in which you can find the members for the previous two points is the official TCPIP.SEZAINST dataset, which would be SYS1.TCPIP.SEZAINST.
If this is the first time that you configure this feature, it might surprise you that these fields are already uncommented by default, meaning that your site already enabled it but didn’t fully configure it.
- Add your DB2 SDSNLOAD dataset on your FTP procedure located on your site’s PROCLIB, or you can skip this step if you added it on your LINKLIST.
- Request READ RACF access authority for the DB2 SDSNLOAD dataset to your FTP started task user or do the equivalent if you’re using another security server.
- Restart your FTP server.
This feature uses the modules DSNALI, DSNHLI2 and DSNTIAR for the execution of the plan EZAFTPMQ, and it will be invoked every time the application executes a SELECT statement over FTP.
Once you already have it set up, you can create any text file from your client workstation, upload it to an MVS member and then have it returned as the query result. For more information, the official documentation for this feature is not on the DB2 manuals and you will find it on the redbook “z/OS Communications Server: IP Configuration Guide” on the section named “Transferring files using FTP”.
DB2 query support over FTP has a drawback (depending on your judgment), though, and it’s that it requires an application user with OMVS and FTP access, which is not common on many sites, but it reduces the need to execute two JCL jobs (one by issuing the query on DSNTIAUL or DSNTEP and the other one to transmit the result to the end user).
RACF requirements for your application user
The requirements to assign OMVS/FTP access for a user are as follows:
- OMVS segment on the user RACF profile.
- A valid group and user ids for the OMVS segment.
- An existing directory assigned as the home directory with at least read permissions for this application user.
- A valid existing program for the initial login, such as /bin/sh or even /bin/false.
- RACF READ access for the corresponding profiles for class SERVAUTH in case your installation has FTP controlled over specific profiles (for example, controlling access to the port for specific locations).
How does it actually look when you use it?
Consider a text file on your workstation (or anywhere, really) containing the following query:
SELECT PROJNO,ACTNO,COUNT(*) AS EMPLOYEE_COUNT
FROM
Upload it to your favorite query library or anywhere you like, and then extract it back using the following instructions:
- FTP to the MVS server, login with your application user and change your directory to the library containing the member with your query you want to execute using DB2.
- Issue the following commands that instruct the FTP server that you wish to retrieve DB2 data:
- Finally, extract the member that holds the query, indicating on the last field the name of the file you want to create on your client side.
The final result will be located in a text file on the client’s side under the name you define in the field
PROJNO ACTNO EMPLOYEE_COUNT
AD3100 10 1
AD3110 10 1
AD3111 60 2
AD3111 70 2
AD3111 80 2
AD3111 180 1
AD3112 60 4
AD3112 70 3
AD3112 80 2
AD3112 180 1
AD3113 60 3
The query result will not be stored on your MVS origin and the output will already be saved into the client’s server, ready to be used by any kind of script or application. This means that on a single script/program you could write an application that creates the necessary query to be executed, then uploads it to z/OS and finally extracts the result, and this programming could be included in a UNIX/Linux cron job and not as two JCLs on the z/OS side lined up with other dozens of jobs waiting for a JES initiator to be executed. It’s up to your creativity to take advantage of the flexibility that this feature provides.