Connecting to non-z/OS data sources

White Papers

Abstract

Many organizations need to integrate structured data from various RDBMS systems existing outside the mainframe environment. Distributed database environments running on Linux, Unix, Windows, and the cloud. DVM for z/OS can access relational databases using built-in DRDA connections. DVM for z/OS also provides an application server, JDBC Gateway Server, that provides access to a large variety of non-mainframe JDBC data sources.

Content

Connecting to non-zOS data sources

Many enterprises that maintain business-critical data on the mainframe also need to integrate structured data from various RDBMS systems existing off the mainframe environment (distributed, Linux, Unix, and Windows-based). Several distributed data sources can be accessed using built-in DRDA connections (described in section 1.2). DVM for z/OS also provides an application server, the JDBC Gateway server, which provides access to a large variety of non-mainframe JDBC data sources shown in Figure 1.

Figure 1 - non-z/OS endpoint support

This section references the configurations and processes specific to different distributed databases like IBM DB2 Big SQL, IBM Db2 Warehouse, on-premises Db2 Family, Microsoft SQL Server, and many other data sources that use the JDBC Gateway server.

Accessing data sources

Standard access to data sources is performed by client requesters that use ODBC or JDBC compliant connections. These protocols connect to the DVM Server and issue access requests to particular data sources with accompanying DML operations to be performed.

Distributed Relational Database Architecture (DRDA)

Many distributed databases like IBM Db2 Big SQL, IBM Db2, IBM Db2 Warehouse, IBM Integrated Analytics Platform, and IBM Performance Server over on-premises, private Cloud, public Cloud, and IBM's Cloud Pak for Data platform, as well as Microsoft SQL Server, as well as QMF, which can be accessed by configuring standard DRDA access methods.

To access distributed databases the DVM Server started task JCL should be running and a database created using the DEFINE DATABASE definition in the Data Virtualization Manager configuration member, located in the product target library, hlq.xVZy.SAVZEXEC.(xVZyIN00).

For IBM Db2 family products running with a built-in federation capability, you can leverage nicknames and remote tables to connect with the DVM Server to access mainframe data sources. The Db2 distributed portfolio, including Data Warehouse offerings are shipped with the DVM Server ODBC/JDBC drivers, are set up and configured, ready for use.

Connecting to non-z/OS sources through DRDA, like Oracle, the Oracle Database Provider for DRDA is required. A Host Integration Server (HIS) DRDA Service is required for connections to a Microsoft SQL Server database. Refer to the documentation on the Microsoft website.

Generally, the configuration member needs updating with more configuration for the DVM Server Event Facility (SEF) rules, as appropriate. Optionally, any alternate authentication information can be configured, as well. Within the DVM configuration member, specify the DRDA RDBMSs settings through a definition statement and provide local environment values for all the parameters. Define Database specification shown in Example 1, the DVM Started Task needs to be recycled.

"DEFINE DATABASE TYPE(type_selection)", 
"NAME(name)", 
"LOCATION(location)", 
"DDFSTATUS(ENABLE)", 
"DOMAIN(your.domain.name)", 
"PORT(port)", 
"IPADDR(1.1.1.1)", 
"CCSID(37)", 
"APPLNAME(DSN1LU)", 
"IDLETIME(110)"

Example 1 - DVM configuration member definition

IBM documentation provides more on configuring access to non-z/OS data sources.

Accessing non-z/OS data sources using the JDBC Gateway server

In this section, we demonstrate the installation and configuration process for the JDBC Gateway server and provide an example for accessing a remote data source. In our overview, we will refer to several components that are involved in the installation and configuration of the JDBC Gateway server environment:

JDBC Gateway server (JGATE). This server is the backend component that bridges the communication between the DVM Server and remote data source.
JDBC Gateway Admin UI is the administrative console that serves as the front-end web user interface used to configure data sources.
DVM Server is the resident server running in a z/OS started task that receives the incoming connections, virtualizes data sources, and processes the SQL requests against the data source.

Setting up the JDBC Gateway server

In this section, we look at how to download, install, and configure the JDBC Gateway server. IBM documentation provides more detailed information on the JDBC Gateway server installation process. The JDBC Gateway server is a pure Java application, which can run on any platform supporting Java 8 or higher.

Note:
If it is not practical to install the JDBC Gateway server on the target data source’s platform, it can also be installed in a UNIX System Services environment on the mainframe.

Installation

The installation file is located in the z/OS SMP/E target library for DVM for z/OS in the hlq.SAVZBIN(AVZBINJ) member. This file can be transferred from the mainframe that uses FTP, renamed, and unarchived. The resulting JAR file can be executed on the target platform to complete the server installation shown in Figure 2.

Figure 2 - FTP transfer of the JGATE server installation file

Once the installation file is downloaded, it can be renamed to JDBCGateway.zip and transferred (for example, FTP) to the target platform to be installed as shown in Figure 3.

Figure 3 - Renaming JGATE installation file

Installing the JDBC Gateway server

Figure 4 shows the process for extracting and installing the JDBC Gateway server client component.

Figure 4 - JGATE server installation

Extract the JDBCGateway.zip in the library where it was transferred to the target system. If your host machine does not have an extract utility, extract the contents of the installation file on a Windows workstation and copy the JDBCGatewaySetup11.jar file to the host machine.

Change to the directory where the installation package was extracted:
'cd jdbc-gateway-x.x.x.xxxxxxxx'

Run the command:
'java -jar JDBCGatewaySetup11.jar'

Running JDBC Gateway server using UNIX System Services

The JDBC Gateway server can also be installed in a UNIX System Services (OMVS) environment on the mainframe. Information on this process is described in the section Installing JGATE in z/OS UNIX System Services. IBM documentation provides some specific information on configuration settings required for a USS installation in the "About this task" section for the JDBC Gateway server.

The UNIX System Services environment provides a centralized location for the JDBC Gateway server to access remote data sources provisioned by the DVM Server. Isolating the JDBC Gateway server from the DVM server allows for better colocation with actual source data. Because the JDBC Gateway server is a pure Java application, it can run anywhere that Java 8 is supported.

Configuration

For installation in USS, it is recommended that you define the following environment variables:

export IBM_JAVA_OPTIONS="-Dfile.encoding=ISO8859-1"
export _BPXK_AUTOCVT=ON

When the JDBC Gateway server installer generates Start and Stop scripts, the following actions occur depending on these variables:

If you have not set the recommended environment variables, the scripts are generated in EBCDIC. You can run the gateway as normal for UNIX using the following command: sh startServer.sh
If you set the IBM_JAVA_OPTIONS variable, the scripts are generated in ASCII, and you will need to use the following command:
chtag -tc ISO8859-1 . (Tagging in USS basically means _BPXK_AUTOCVT must be ON if you want to edit or execute the script in the shell.)
Files generated by the JDBC Gateway server, such as log files and the jgate.properties file is generated in ASCII regardless of the previously mentioned environment variable settings (except for jetty.out, which is in EBCDIC). To browse these files natively in USS, you must use the chtag command and set _BPXK_AUTOCVT=ON.

Customized installation

As a best practice, install the JDBC Gateway server to a non-user library in USS. Doing so allows centralized access to the Gateway server by any authorized user and prevents issues where an administrator user's access permissions change or are no longer accessible to the platform.

When running the JDBCGatewaySetup11.jar executable, override the Duser.home variable by using the Duser.home argument and point it to a directory. This directory needs to be empty before proceeding with this type of installation

java -Duser.home=<dir where the properties and config files are created> -jar JDBCGatewaySetup11.jar

java -Duser.home="C:\ZDVM\product\JGateway3-config" -jar JDBCGatewaySetup11.jar

After successful installation, the startServer.cmd and stopServer.cmd need to be updated, and when the startServer.sh is executed the new directory path is used.

java -Duser.home=<dir where the properties and config files were created> -jar JDBCGatewaySetup11.jar

java -Duser.home="C:\ZDVM\product\JGateway3-config" -jar JDBCGateway.jar

Starting the JDBC Gateway server to use Batch execution

Use a batch interface as a best practice to start/stop the JDBC Gateway server in a USS environment. The JCL examples provided in Figure 5 can be used to invoke the startServer.sh and stopServer.sh scripts.

Using the start/stop scripts directly in OMVS limits the number of resources available to the JDBC Gateway server for a user's OMVS segment. Starting from JCL with BPXBATCH the customer can set the REGION size, shown in Figure 5.

Figure 5 - Use batch scripting to efficiently Start and Stop the JDBC Gateway server in USS mode

Managing JDBC Gateway server software upgrades

Periodically, the JDBC Gateway server needs to apply fix levels or new releases. To ensure that all of the current environment and configuration settings are preserved, a backup of the startServer.sh and stopServer.sh scripts are required. After the new fix or release level of the software are downloaded, install the newer version by using the following command:

java -jar JDBCGatewaySetupxx.jar

The installation script automatically installs using the original installation directory. Reply 'Y' when prompted to clear the directory and edit the new startServer.sh and stopServer.sh scripts to reimplement any changes from the previous installation.

Starting the JDBC Gateway server that uses administrative UI

The Gateway server can also be used as an administrative user interface. The JDBC Gateway server can be both started and stopped on an MS-Windows, Linux, UNIX, Mac OSx, and web browser.

At a command prompt in the JDBC Gateway server installation directory, run one of the following commands to either start or stop the Gateway server on MS-Windows or Linux, Windows, and Mac OSx.

MS-Windows: startServer, stopServer
Linux, UNIX or Mac OSX: sh startServer.sh, sh stopServer.sh

Figure 6 - Starting JGATE server

A Web browser can be used by connecting to the server IP address and port chosen for the Admin UI during installation (that is https://192.168.1.31:8091), whereby a username/password prompt appears. The default username is 'admin'. The user interface initializes and a user access dialog window will appear to prompt for a username: (default “admin”) and Password you chose at the installation of the server shown in Figure 7.

Figure 7 - New Data Source configuration screen

Configuring data sources that use the JDBC Gateway server UI

Data sources can now be configured using the administration UI console. For our example, we adds a connection to a PostgreSQL database on a remote server in Figure 8.

Figure 8 - Add New Data Source dialog

The Connections parameters drop-down for the JDBC driver allows the ability to select supported data sources. If the data source doesn't exist, the ellipsis can be selected for a new data source, as shown in Figure 9.

Figure 9 - JDBC driver selection dialog

Once the data source is selected, JDBC connection string details can be added for the target database that uses the appropriate format can also be maintained, as shown in Figure 10.

Figure 10 - Data source connection details

Access and authentication credentials are also maintained for access to the target database with the ability to test a successful connection, as shown in Figure 11.

Figure 11 - Define user access for a target data source

Configure the DVM Server to access the JDBC Gateway server

To create a mapping or linkage between the requesting DVM Server and a newly installed JDBC Gateway server, a z/OS TSO/e session is needed to update DVM Server configuration members. Depending on the SMP/e installation and the naming chosen for the DVM server subsystem, the server initialization member is found by locating the configuration libraries hlq.xVZy.SAVZEXEC(xVZyIN00).

Using ISPF the installation library can be located in DVM110.AVZ1.SAVZEXEC, where the ‘xVZyIN00’ configuration member exists, as shown in Figure 12 and Figure 13.

Figure 12 - Locating DVM 'zVZyIN00' configuration member

Figure 13 - Editing DVM ‘xVZyIN00’ configuration member

The AVZ1IN00 member needs the DRDA specification updated for the PostgreSQL data source defined in the JDBC Gateway server. Adding a DEFINE DATABASE TYPE block definition after sample definitions for Db2, MSSQL, and other DRDA connection types establishes a mapping for the DVM Server to connect to the PostgreSQL data source through the JDBC Gateway server. A portion of a sample DEFINE DATABASE TYPE is shown in Example 2.

"DEFINE DATABASE TYPE(JGATE)", 
"NAME(PSG1)", 
"LOCATION(POSTGRES)", 
"DDFSTATUS(ENABLE)", 
"DOMAIN(your.domain.name)", 
"PORT(443)", 
"IPADDR(192.168.164.74)", 
"CCSID(37)"

Example 2 - Define Database Type

This example uses the location ‘POSTGRES’ and port 443 (default) as defined in the previous Admin UI config in the previous section. We selected ‘PSG1’ as our connection Name. The IPADDR parameter is set to the IP address of the system where the JGATE server was installed previously.

Once the DEFINE DATABASE changes are completed, the edit session on SAVZEXEC(xVZyIN00) can be saved and closed.

Setting User Credentials for JDBC Gateway server

The DVM Server provides pass-through authentication for the user logged in to the current session that uses RACF credentials by default to the remote data sources that are connected through the JDBC Gateway server. In most cases, user credentials are not the same for the remote JDBC data sources and can to be mapped for each source.

AVZDRATH is a utility that sets encrypted passwords in GLOBALU variables. This utility can be used to list existing credential information.
Alternate user credentials can be set up with changes to auto-enable a SEF Rule, AVZEJGAG, and mapped with the AVZDRATH utility.
\AVZEJGAG is an ATH rule switches credentials when accessing the JGATE data source that uses DRDA. This rule uses AES encrypted passwords stored as GLOBALU system variables.

Secure access using AVZDRATH

Setting User Credentials for JDBC Gateway server

Alternate user credentials can be set up with changes to auto-enable a SEF Rule, AVZEJGAG, and mapped with the AVZDRATH utility.

To define alternate authentication information, edit the sample job for the AVZDRATH utility to add a global default user definition or authentication information for specific mainframe users.

The AVZDRATH member can be edited in the hlq.SAVZCNTL data set by adding a definition for the example PostgreSQL database to map the value from RACF to the user ID needed to access the PostgreSQL database. The DBTYPE=JGATE and DBNAME=’name’ are required to proceed with each set of user ID mappings for a specific data source instance in Figure 14.

Figure 14 - Specific User Authorization for PSG1 JGATE data source

Once the definition entries are complete, the AVZDRATH JCL job can be submitted. AVZDRATH also provides a means for setting a DEFAULTUSER. This allows the Jgate Server to establish a proxy or functional ID for a larger set of RACF IDs, providing default credentials for a JGATE-connected data source.

The example in Figure 15 shows a DEFAULTUSER on lines ‘002500’ and ‘003800’. Other keywords allow printing of the SYSIN statements in the job output (ECHO=ON/OFF), as well as providing more detailed or summary information of the AVZDRATH utility settings in the job output (REPORT=DETAIL/SUMMARY).

Figure 15 - Report Job Summary for AVZDRATH settings

Providing user credential entries in the AVZDRATH utility job can introduce a security risk if unauthorized users can browse the AVZDRATH member to view userid entries.

User access that uses Rules

AVZDRATH is a utility that sets encrypted passwords in GLOBALU variables. This utility can be used to list existing credential information.
AVZEJGAG is an ATH rule that switches credentials when accessing a JGATE data source using DRDA. This rule uses AES encrypted passwords stored as GLOBALU system variables.

List ZOSUSER mapping in the AVZ member

Another option to listing the ZOSUSER mappings directly in the member is to concatenate a separate sequential dataset entry with a SYSIN DD specification. The dataset specified in the SYSIN DD can be RACF protected to prevent unauthorized access to the user credentials, as shown in Figure 16 for DVM110.AVZ1.AUTH.

Figure 16 - ZOSUSER authorization mappings

The dataset specified in the SYSIN DD can be RACF protected to prevent unauthorized access to the user credentials shown in Figure 17 for DVM110.AVZ1.AUTH. 1.2.11

Figure 17 - Prevent unauthorized access that uses RACF

Using Rules to ensure global user authorization

Running the AVZDRATH utility ensures the new GLOBALU settings are referenced in the DVM Server by automatically enabling the SEF ATH rule for the PDS member AVZEJGAG. The following Using the TSO ISPF PANEL for the DVM ServerTSO ISPF PANEL workflow makes the needed changes to the SEF rule to address the user authorization security needed for the DVM Server.

Invoking ISPF panels that use TSO

In some instances, ISPF panels can be set up as a secondary menu from the ISPF Primary options menu or can be invoked by running EXEC from a TSO command line. The ISPF panel can be set up in the hlq.xVZy.SAVZEXEC library, and can be started by running a TSO command shell - EXEC ‘hlq.xVZy.SAVZEXEC(xVZ)’‘SUB(xVZy) shown in Figure 18.

Figure 18 - Starting DVM ISPF interface from TSO command line

Updating Rulesets using the DVM Server ISPF panel

The DVM Server ISPF panel allows the ability to define rules to handle specific business needs. The use of Rules Management is needed for this alternative method for managing secure access to a target data source through the JDBC Gateway server, as shown in Figure 19.

Figure 19 - Navigating to Rules Management on the DVM Server

ISPF panel The Event Facility provides the unique ability to customize rules for use of variables, handing of data or business triggers, and monitor their use. Option '2' for SEF Rule Management steps through the creation of a new rule for the ZOSUSER in SYSIN, as shown in Figure 20.

Figure 20 - SEF Rules Management

For the SEF change, all of the default values apply for rulesets, types, directory reads, confirmations, and the entry panel. To address this rule, the ATH ruleset can be ENABLED under status. Selecting this ruleset for DVM110.AVZ1.SAVZXATH displays a list of associated PDS members.

The PDS member for the ATH rule is AVZEJGAG, as highlighted in Figure 17. Selecting Option 'B' for this member changes the status to ENABLED and the auto-enabled column (AE) displays a 'Y' flag denoting that the rule is always-on shown in Figure 21.

Figure 21 - Enabling and automatically enabling the AVZEJGAG ATH rule

Once the ATH settings for the AVZEJGAG PDS member are completed and the ISPF panel existed, the DVM Server started task is to be stopped and restarted.

Connecting to a JGATE Data Source in DVM Studio

Let’s take a look at our Data Virtualization Manager Studio to view the sample PostgreSQL connection we configured using the JDBC Gateway server. The DVM Studio has a Server panel with DVM Server information. For this example, AVZ1 is the host DVM Server configured and connected to the JDBC Gateway server named JGATE. Figure 22 shows a tree structure for the AVZ1 server with references to Virtual Tables and Virtual Views mapped to local data sets and remote sources mapped that uses the JDBC Gateway server.

Figure 22 - Navigating in DVM Studio to other subsystems

The tree structure also lists 'Other Subsystems' configured for the DVM Server. The current view in Figure 19 displays Db2 members and a series of JDBC Gateway servers configured across various workstations representing Windows, Linux, and macOS. Each of the JGATE references represents database definitions linked to the AVZ1 DVM Server, and in particular, the PSG1 (JGate) server used to access Postgres data remotely in Figure 23.

Figure 23 - Displaying the PSG1 Postgres database running non-mainframe

The DVM Server maintains metadata in memory associated with each data source mapped for access. The DVM Server also performs data Discovery for the target data source and captures details about schema, tables, and views residing on those database systems. As a result, the DVM Studio has immediate access to all of the DVM Server metadata and can easily present database objects in a relational format for simple access by SQL API and other modern programming languages. In this case, Figure 20 displays the schema, tables, columns for all the database objects in the PSG1 Postgres database. The JDBC Gateway server serves as a literal gateway for DVM Server for this information, which then allows client applications to read/write to that source, as shown in Figure 24.

Figure 24 - Data Discovery of Postgres database

Figure 25 displays two tables that exist within the 'dvm_schema' for the PSG1 PostgreSQL database. One of the tables is named 'emp'. The DVM Studio has the ability to create a Virtual Table that maps to the 'emp' table, and generates both SQL and programming code (Java, Python, and so on). The DVM Studio can also create and publish web services that can be leveraged by RESTful applications.

Figure 25 - Tree structure of remote PSG1 PostgreSQL database with the 'emp' table selected

Generate Query

The DVM Studio generates SQL statements dynamically for the selected database object, defaulting to select all columns and rows up to a built-in limit to the Studio shown in Example 4.

-- Description:      Retrieve the result set for emp
-- Tree Location:   192.168.164.74/12050/SQL/Data/Other Subsystems/PSG1/dvm_schema/Tables/emp
-- Remarks:
SELECT "empno", "firstnme", "midinit", "lastname", "workdept", "phoneno", "job", "edlevel"
FROM "dvm_schema"."emp";

Example 4 - Result from SQL Query in emp table in PostgreSQL

Run and view generated query results

The DVM Studio can then display results from existing tables, view results of a newly created virtual table, and also join results from two heterogeneous database tables. Join operations can occur exclusive to relational, non-relational, or any combination of types.

The example in Figure 26 shows the result set from the query executed in Example 4. Notice that the generated query selects all columns and includes the normalized 3-part name.

Figure 26 - Result from executed SQL Query of emp table in PostgreSQL

Create a Virtual Table that uses the DVM Studio

A Target System needs to be referenced for the virtual tables from the remote JDBC source. Once the Target System exists for the remote database, the DVM Studio discovers the database objects for the target system that can now be virtualized. In the following dialog presented in Figure 27, the metadata library is displayed where the mapping for the data source is stored. In this example, a 3-part naming pattern is defined for the emp PostgreSQL table selected.

Figure 27 - New Virtual Target System

Once the Virtual Table is created, a query can be generated from the newly defined mapping, much in the same way the query was generated accessing the data directly in Figure 26. The results from the Virtual Table of emp base table display in the DVM Studio output in Figure 28.

Figure 28 - Query results from EMP Virtual Table

Joining data with Virtual Views that uses the DVM Studio

A Virtual View joining two tables from different data sources can also be created. In the example presented in Figure 29, a JOIN is created between DB1M_DSN81210_DEPT (from Db2 z/OS) and PSG1_DVM_SCHEMA_EMP (from PostgreSQL).

Figure 29 - Creating a Virtual View that JOINs across two Virtual Tables

With several Virtual Views defined, you can simply start with a single view and then add tables with JOIN criteria through a freeform SQL Editor, as displayed in Figure 30.

Figure 30 - SQL SELECT JOIN between EMP and DEPT virtual tables

For this example, the DEPT table from the Db2 for the z/OS database is joined with the EMP table from the PostgreSQL database. With the Virtual View created, a query can be generated and run to view the result of the JOIN operation between the two Virtual Tables and shown in Figure 31. The example accesses data on the mainframe and joins it with a table residing on a remote distributed environment through the JDBC Gateway server.

Figure 31 - Result from SQL Query on joined tables EMP and DEPT.

Secure access that uses AVZDRATH

Setting User Credentials for JDBC Gateway server

Alternate user credentials can be set up with changes to auto-enable a SEF Rule, AVZEJGAG, and mapped with the AVZDRATH utility.

To define alternate authentication information, edit the sample job for the AVZDRATH utility to add a global default user definition or authentication information for specific mainframe users.

The AVZDRATH member can be edited in the hlq.SAVZCNTL data set by adding a definition for the example PostgreSQL database to map the value from RACF to the user ID needed to access the PostgreSQL database. The DBTYPE=JGATE and DBNAME=’name’ are required to proceed with each set of user ID mappings for a specific data source instance, as shown in Figure 32.

Figure 32 - Specific User Authorization for PSG1 JGATE data source

Figure 33 shows a DEFAULTUSER on lines ‘002500’ and ‘003800’. Other keywords allow printing of the SYSIN statements in the job output (ECHO=ON/OFF), as well as providing more detailed or summary information of the AVZDRATH utility settings in the job output (REPORT=DETAIL/SUMMARY)

Figure 33 - Specific User Authorization for PSG1 JGATE data source

Security risks can result if clear text user credentials are entered in the AVZDRATH member.

[{"Type":"SW","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS4NKG","label":"IBM Data Virtualization Manager for z\/OS"},"ARM Category":[{"code":"a8m0z000000cxAYAAY","label":"Java Gateway"},{"code":"a8m0z000000cxAOAAY","label":"SQL Engine"},{"code":"a8m0z000000cxATAAY","label":"Studio"}],"Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"All Version(s)"}]

Product Synonym

DVM

Was this topic helpful?

Document Information

Modified date:
23 June 2021

UID

ibm16455651

Tips