Accessing IBM Db2 LUW databases with IBM Db2 for z/OS
Introduction
Have you ever wondered if IBM Db2 for z/OS can access data stored in other (even non-z/OS) DBMS systems, for example, IBM Db2 Database (commonly known as Db2 LUW)? The answer is "yes". In this article, I'm going to show you how to set up communication between IBM Db2 for z/OS and other DBMS systems, and how you can use this configuration to access and unload remote non-z/OS data.
DRDA
Db2 for z/OS supports the DRDA standard (Distributed Relational Database Architecture) to intercommunicate with other DBMS systems. Some examples of DBMS systems that support the DRDA standard include:
- Db2 LUW
- Oracle Database
- Informix
- Apache Derby
Therefore, Db2 for z/OS supports communication with other DBMS systems that also support the DRDA standard. In this article, we are going to focus on Db2 LUW.
Installing Db2 LUW
If you want to experiment with your own independent instance of Db2 LUW then there's a simple way to do that. First of all, use the latest version (11.5 at the moment) of the Community Edition of Db2 LUW, which is free but limited to 16GB of memory, 4 CPU cores, and 100GB of database size - plenty of resources for experimenting.
Db2 LUW can be installed directly under Windows, Linux, or other operating systems. The Windows installer may cause various problems during installation. We are going to use a more solid installation option - Docker. Docker allows you to describe the exact software required (like operating system version, etc.) for a specific software product, providing a more reliable option to install and operate software. There's a Db2 LUW Docker image with installation instructions available here: https://hub.docker.com/r/ibmcom/db2.
Docker itself can be installed under Windows or Linux. The Linux version is more feature complete and is production-ready, but for the sake of experimenting, the Windows version of Docker may be used as well.
Here are some simple steps to follow in order to install Db2 LUW using Docker under Windows. (Refer to the Docker CLI documentation for the descriptions of the parameters for each of the following commands):
- Install Docker.
- Open a command prompt to run the following commands.
- We'll need to be able to save (persist) the data of our Db2 container. Let's create a Docker volume for it. The volume can have any name, but in our case we'll use "db2":
docker volume create db2
- Run a Db2 Docker container. The -e option just sets environment variables to be used by the Db2 installation (refer to the Docker image page for more information on environment variables). We'll need to specify the database name, the password, and the port number to be mapped to Db2's port 50000. The "ibmcom/db2" is the name of the Docker image we are going to use.
docker run -itd --name db2 --privileged=true -p <port to be mapped>:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=<password> -e DBNAME=<dbname> -v db2:/database ibmcom/db2
- Check the container logs for the "Setup has completed" message:
docker logs -f db2
- Log into the container. The following command executes the su command required to login as a specific user, "db2inst1" in this case:
docker exec -ti db2 bash -c "su - db2inst1"
- Create a sample database using the following Db2-supplied utility:
db2sampl
- Connect to the database (the database name is "SAMPLE" in our case) and execute a SELECT statement against the sample database.
db2 'CONNECT TO SAMPLE' && db2 'SELECT * FROM EMPLOYEE'
Communications Database
The communications database (CDB) in Db2 for z/OS is a set of catalog tables which define remote DBMS systems (both z/OS and non-z/OS) to the Db2 subsystem.
Here are some useful resources about CDB:
There are many ways to configure communication and authentication between Db2 for z/OS and Db2 LUW - using trusted contexts, with and without passwords, etc. Let's look at an example of how the CDB tables of the catalog can be populated.
Here are some explanations of important CDB columns:
- SYSIBM.LOCATIONS
That's where you define remote locations.
- LOCATION
The remote location name. Note that the location name must match the remote database name or you'll get an "SQLCODE = -30061, ERROR: RDB NOT FOUND" error.
- LINKNAME
Can be any name.
- PORT
The remote DBMS port.
- SYSIBM.IPNAMES
That's where you define remote hosts.
- LINKNAME
Must be the same as the LINKNAME specified in SYSIBM.LOCATIONS.
- IPADDR
The remote DBMS address (domain name or IP address).
- SYSIBM.USERNAME
That's where you define users for the remote DBMS.
- LINKNAME
Must be the same as the LINKNAME specified in SYSIBM.LOCATIONS.
- AUTHID
Local authorization ID to be translated.
- NEWAUTHID
Result of the authorization ID translation, i.e. the remote user name.
- PASSWORD
The remote user password.
And here's a full example of the CDB population:
INSERT INTO SYSIBM.LOCATIONS (LOCATION, LINKNAME, PORT)
VALUES
(
'SAMPLE', -- MUST BE THE SAME AS LUW DATABASE NAME
CURRENT SQLID, -- MAY BE ANY NAME
<LUW port> -- LUW PORT
);
COMMIT;
INSERT INTO SYSIBM.IPNAMES (LINKNAME, SECURITY_OUT, USERNAMES, IPADDR)
VALUES
(
CURRENT SQLID, -- THE SAME AS THE LINKNAME IN SYSIBM.LOCATIONS
'P', -- OUTBOUND CONNECTION REQUESTS A PASSWORD
'O', -- OUTBOUND ID IS SUBJECT TO TRANSLATION
'<LUW host address>' -- LUW HOST ADDRESS
);
COMMIT;
INSERT INTO SYSIBM.USERNAMES
(TYPE, AUTHID, LINKNAME, NEWAUTHID, PASSWORD)
VALUES
(
'O', -- FOR OUTBOUND TRANSLATION
CURRENT SQLID, -- AUTHORIZATION ID TO BE TRANSLATED
CURRENT SQLID, -- THE SAME AS THE LINKNAME IN SYSIBM.LOCATIONS
'db2inst1', -- LUW user ID
'<password>' -- LUW user password
);
COMMIT;
Accessing Remote Tables
Now you are all set to access the remote Db2 LUW data. One way to go is to define an alias for a 3-part table name (LOCATION.SCHEMA.NAME), and use it in SELECT statements.
One other way to access the remote data is to use CA Fast Unload for DB2 for z/OS. CA Fast Unload for Db2 for z/OS is a high speed utility for offloading Db2 table data to various sequential file formats. It provides exceptional performance, zIIP offload (up to 50%) to save CPU time, multiple options for unloading, converting and filtering Db2 data, support of several output formats. Let's have a look at how CA Fast Unload can be used to access remote tables.
Running CA Fast Unload
First, we'll need to bind some CA Fast Unload packages on the remote DBMS systems. It only needs to be done once for the remote Db2 LUW instances we are going to use. Refer to the documentation of how to bind packages and plans using the Post-Install (note: use the "DRDA" as the location type to define your remote DBMS systems).
We'll need the following CA Fast Unload parameters to unload the data correctly:
- SQL-ACCESS ONLY to make CA Fast Unload use SQL access to unload the data.
- MULTI-ROW-FETCH CONTINUE to use single-row fetch instead of multi-row fetch which is the CA Fast Unload default. Multi-row fetch is only available in Db2 for z/OS.
- EBCDIC to translate the remote data into EBCDIC, if you wish to do so.
This is the full JCL to create an alias for the remote table and unload the remote data with CA Fast Unload:
//*--------------------------------------------------------------------*
//* DEFINE GLOBAL PARAMETERS. *
//*--------------------------------------------------------------------*
// EXPORT SYMLIST=* EXPORT ALL SYMBOLS
// SET LUWDB=SAMPLE LUW DATABASE NAME
//*--------------------------------------------------------------------*
//* DEFINE AN ALIAS FOR THE REMOTE TABLE *
//*--------------------------------------------------------------------*
//SQL EXEC PGM=IKJEFT01
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM (<SSID>)
RUN PROGRAM (DSNTEP2) PLAN (DSNTEP2)
END
//SYSIN DD *,SYMBOLS=EXECSYS
CREATE ALIAS EMPLOYEE FOR &LUWDB..DB2INST1.EMPLOYEE; COMMIT;
//*--------------------------------------------------------------------*
//* RUN CA FAST UNLOAD *
//*--------------------------------------------------------------------*
//UNLOAD EXEC PGM=PTLDRIVM,PARM='EP=UTLGLCTL/<SSID>'
//STEPLIB DD DISP=SHR,DSN=<DB2 TOOLS LOAD LIBRARY>
//PTIPARM DD DISP=SHR,DSN=<DB2 TOOLS PARAMETER LIBRARY>
//PTIMSG DD SYSOUT=*
//PTIIMSG DD SYSOUT=*
//SYSREC01 DD SYSOUT=*
//SYSIN DD *
FASTUNLOAD
OUTPUT-FORMAT COMMA-DELIMITED
SQL-ACCESS ONLY
MULTI-ROW-FETCH CONTINUE
EBCDIC
SELECT * FROM EMPLOYEE;
And here's the result:
------------------------------
Software Engineer
Broadcom
------------------------------