Performance Tuning and Query Optimization

Content

• % LPAR share
• % Processor capacity
• Memory limit
• MSU/H

1. If VPD is not used, then each client has to create a separate virtual table to access the same set of data from the source. With VPD, DVM creates one virtual table for accessing the data by all the clients.
2. The end-to-end read operation has to be carried out by each client application. With VPD, the data is read from the source once and the same data is consumed by each client application in parallel.
3. Input devices like tape can be read-only serially and hence parallelization is not possible. Each of the client applications has to read the data from source to destination serially.
4. With VPD, the DVM can read the data from tape serially once whereas the client applications can access the data in parallel from DVM buffers.
5. INPUT devices like Tape can be read-only serially and hence parallelization is not possible. Each of the client applications has to read the data from source to destination in serial. With VPD, the DVM can read the data from tape serially once whereas the client applications can access the data in parallel from DVM buffers.
6. Client applications that need to read data in a specific order, have to read the data from an original data source in its' specific order. Even when the data can be read from the source in parallel, VPD allows the data to be read into DVM buffers in parallel. Client applications can then read the data from buffers in any specific order.

IBM Z resources are assigned based on goals defined in the IBM Workload Manager shown in Figure 4.

Figure 4 - Resource priority assignments by WLM

• AVZ_SCHI ZIIPCLASS=AVZ High priority. This service class is for IBM Data Virtualization Manager for z/OS critical work. Assign this class goal as close to SYSSTC as possible.
• AVZ_SCNM ZIIPCLASS=AVZ Normal work. This class is for IBM Data Virtualization Manager for z/OS administrative work. Assign this class the same priorities as those used for DB2 or the IMS control region.
• AVZ_SCTX ZIIPCLASS=AVZ Client work. This service class is for client requests. Assign this class the same goals as those supporting the data source for the CICS, IMS/TM or DB2 WLM address space.

Figure 8 - Sysplex Load balancing using DVIPA

• Db2-Direct does not support tables with edit procedures or SQL statements containing joins, LOB columns, or key columns.
• The Db2 subsystem hosting a Db2 table must be active when Db2-Direct enabled tables are loaded or refreshed. The MAP building requires Db2 system access to identify data set information in the Db2 system catalog.
• The DVM Server requires READ access to the Db2 VSAM linear datasets and that datasets are available during map load or refresh for the virtual table.
• Virtual tables enabled for Db2-Direct must include all the columns defined in the base Db2 table as the column information is not available while loading directly from DB2 VSAM linear datasets.
• If Db2-Direct table security is enabled, the Db2 subsystem must be available to check security at SQL query time.
• If Db2-Direct security is disabled, the CCSIDs of table columns assumed based on the ENCODING_SCHEME (EBCDIC, Unicode, ASCII) of the table.
• Users can check the DVM server trace logs for the following messages to confirm whether DB2-Direct is enabled or used.
  Startup: DB2 direct processing enabled for <map-name>
  Running: Processing table that uses DB2 direct
  Failure: DB2 direct processing disabled for <map-name>

• Hierarchical direct access method (HDAM) – VSAM and OSAM
• Hierarchical indexed direct access method (HIDAM) – VSAM and OSAM
• Partitioned HDAM (PHDAM) – VSAM and OSAM
• Partitioned HIDAM (PHIDAM) – VSAM and OSAM
• Fast Path data entry database (DEDB)

Connection con = DriverManager.getConnection

(“jdbc:rs:dv://host:port;Key=Value;Key=Value;”,username,password);

  Statement stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery(“SELECT a, b, c FROM Table1”);
  while (rs.next())
  {

    int x = rs.getInt(“a”);
    String s = rs.getString(“b”);
    loat f = rs.getFloat(“c”);
  }

The client can experience a pause during the next() API calls when all rows in the current buffer are read and the driver fetches another buffer of data. This pause can be avoided by enabling the parallel I/O.

JDBC and Parallel I/O

When Parallel I/O is enabled, the JDBC driver creates multiple and separate threads using DVM server buffers to pre-fetch, read, and insert rows into a queue. The JDBC driver works to ensure this queue is fully used to ensure there is always at least one buffer ready for the next() API call to use. Pauses are eliminated so the client can continue to call the next() buffer in the results set. The process iterates until all rows in all buffers are read and queued.

The following illustration shows row data being sent to the client using both the main and the parallel thread. The ParallelIoBufferCount (PIOBC) property determines the number of buffers in use in Figure 12.

Figure 12. Parallel IO pre-fetches buffers

JDBC and MapReduce

MapReduce is a JDBC driver-controlled feature that can be used to improve SQL query performance by reading results with multiple connections between the driver and server. Thus, there are two types of MapReduce.

• Server-controlled MapReduce – MapReduce is performed on the server using the JDBC driver.
• Client-controlled MapReduce – MapReduce is performed on the client and the JDBC driver manages single connections, whereas the client manages other connections and aggregate the results from each connection.

If you are using MapReduce with RDBMS or IMS, you must complete metadata repository configuration requirements.  

Server-controlled MapReduce

The data mapping step creates threads that execute a query using more than one DVM server connection. Each thread maps one connection to one server. The reduce step reads results on each thread in parallel for each connection, and then transparently presents the data to the client in Figure 13.

Figure 13. Server controlled MapReduce with a single DVM server

Configure a single server connection by setting the MapReduceClient JDBC property:

Example

MapReduceClient = (Hostname, Port, TaskCount)

MapReduceClient= (dvs.example.com, 9999, 7)

MapReduce can also be configured to use multiple server connections. Figure 14 shows a client connected to two servers with MapReduce.

Figure 14. Server controlled MapReduce with two DVM servers

Configure multiple server connections by setting the MapReduceClient property:

Example

MapReduceClient = (Hostname1, Port, TaskCount1), (Hostname2, Port, TaskCount2)

MapReduceClient = (dvs1.example.com, 9999, 4), (dvs2.example.com, 1111, 3)

MapReduce can also be controlled to use a specific range of clients:

Example (clients 1-4 and 5-7)

MapReduceClient = (Hostname, Port, maxClientNo, startClientNo, endClientNo)

MapReduceClient = (host1, 1200, 7, 1, 4), (host2, 1200, 7, 5, 7)

Client-controlled MapReduce

In some use cases, using Apache Spark, the client application can perform the MapReduce process and still benefit from the server’s MapReduce feature. The JDBC driver supports this use case by specifying a single connection as the JDBC MapReduce connection. This connection is then available for use by a group of specified connections. The JDBC driver manages single connections. The client application must aggregate the results from each connection managed in Figure 15.

Figure 15. Client controlled MapReduce with seven parallel connections

Client-controlled MapReduce can be enabled by setting the JDBC driver properties MapReduceClientCount and MapReduceClientNumber as follows:

• The MapReduceClientCount property is used to specify the total number of connections associated with the group of client connections.
• The MapReduceClientNumber property defines a specific client connection within the group is managed by the JDBC driver and has a value between the number 1 and the number specified for the MapReduceClientCount property.
• The JDBC driver executes queries using the single MapReduce connection for the client connection specified in the MapReduceClientNumber property.
• Ddata for the specified connection are returned, as opposed to using MapReduceClientCount over one or more connections to get all rows of data.
• To configure client-side MapReduce, set the JDBC driver MapReduceClientNumber and MapReduceClientCount parameters as follows:
  
     Example
     MapReduceClientNumber, MapReduceClientCount
     MapReduceClientNumber=2; MapReduceClientCount=7;

The JDBC driver creates a single connection, indicated as connection 2 out of 7 available connections. Using a framework like Apache Spark, you must create and manage all remaining connections and aggregate the results from each of those connections.

ODBC Performance

The ODBC drivers are used by non-Java applications and tools to access data that is made available through the DVM server. The ODBC driver implements the ODBC Direct network protocol that is used to connect to the Data Virtualization Manager server and uses the ODBC API to execute SQL queries. The ODBC driver for the Windows platform is bundled in the driver installation package of DVM. For Linux/Unix platforms, the ODBC driver is not included in the driver installation package and a third-party driver manager can be installed.

The performance of ODBC drivers with DVM can be optimized with Connection Pooling and Optimized Fetch. The connection pooling in the Windows platform can be configured through the ODBC Data Source Administrator. For Unix/Linux platforms, connection pooling is managed by the ODBC Driver Manager. When optimized fetch is enabled, rows ahead of the current row are asynchronously extracted before the client application requests them. This data is then returned to the client application in blocks that can be as large as 32 KB. Enabling optimized fetch helps to minimize network traffic and speeds subsequent fetches as the requested data is likely already in a returned block.

Optimized fetch is enabled either by including the RO=YES connection property in a connection string (a connection string can be used with a DSN) or by appending the FOR FETCH ONLY clause to a SELECT statement.

The performance of ODBC connected applications can also be improved by the appropriate usage of catalog functions, retrieve the required data, select functions that optimize performance, and manage connections efficiently. For example, catalog functions are usually expensive, and hence minimize the use of catalog functions and avoid searching patterns in catalog functions can improve performance. Similarly, using bound columns (for example SQLBindCol instead of SQLGetData) and using SQLExtendedFetch instead of SQLFetch can help to improve the performance

Integrated Data Facility (IDF) and DS Client API

Data Virtualization Manager for z/OS also provides an interface to access virtualized data from within more traditional mainframe languages such as COBOL, Natural or PL/I. With Db2 Integrated DRDA Facility (IDF), the traditional mainframe applications can use standard EXEC SQL statements to access legacy data sources like VSAM, IMS,  and ADABAS.  The DS Client high-level API allows an application running on z/OS to use a call-level interface to communicate with Data Virtualization Manager to process SQL requests and to retrieve result sets.

The performance of Db2 IDF and DS Client APIs is similar for single user address space applications. However, Db2 IDF performed much better than DS Client API for multiuser environments like CICS and IMS. Also, the applications can use the popular API with Db2 IDF compared to specific high-level API with DS Client. Hence, it is recommended to use Db2 IDF to access data sources from traditional mainframe languages when Db2 is available in the customer environment. For those customer environments where Db2 is not available, DS Client APIs can be used.

The performance of DS Client API can be optimized with data buffering and MapReduce features similar to JDBC. The number of active client interface servers to process client requests can also be optimized by tuning the parameter ACIDVCLIENTMIN. The maximum number of MapReduce tasks that starts to process a request from a DS Client application can be specified using ACIMAPREDUCECLIMAX.

Similarly, the default size of the buffer, the maximum and minimum buffer size, and the maximum number of buffers can also be specified with DSCLIENTBUFFERSIZE, DSCLIENTBUFFERSIZEMAX, DSCLIENTBUFFERSIZEMIN, and DSCLIENTBUFFERNUMMAX respectively.

To configure the DS Client API for optimized performance, refer to the DS Client high-level API chapter of IBM documentation.

Query Optimization and Performance

Data Virtualization Manager for z/OS provides default values that are part of the standard installation in the IN00 configuration file that serves as standard level optimization. These defaults are fine-tuned over years of customer experience.

SQL Best Practices
Though increasing resources is an easy option, it is worthwhile to invest time into writing efficient SQL statements for the DVM server to consume. The DVM server is an SQL engine working over 35+ different legacy data sources, which don't conform to relational database rules. A best practice is to conform to the most compliant SQL that matches the source data system. This includes not only SQL, but also functions, routines, and data type.

Examples:

• Select only the required fields
• Understand how a Large and Small table is being JOINED
• Avoid scalar, arithmetic process into the SQL’s

Some best practices:

• Simple predicate rewrites cause the optimizer to generate significantly different query plans and force a different access path, which result in getting the best out of the SQL
• Avoid tablespace scans when your goal is to fetch a much smaller subset of data
• Sort only on the columns needed
• Minimize the number of times cursors are opened and closed

Designing SQL is an art form. While many different statements result in the same output, there are wild variances in the execution time based on how the query is constructed. Table 3 provides some guidance for creating queries.

Table 3 - Best practices for query design

Using Apache JMeter with the DVM server

The Apache JMeter™ application is open source software, a 100% pure Java application designed to load test functional behavior and measure performance. Customers can use JMeter to measure the performance of the services and queries run on the DVM server. Apache JMeter can be download from the following Url: https://jmeter.apache.org/

Copy the DVM for z/OS client JDBC driver into the lib directory of the Apache JMeter installation directory. The JDBC driver for DVM is available with the driver installation member AVZBIN2. From the mainframe, transfer the driver installation member has.SAVZBIN(AVZBIN2) to the client machine using the File Transfer Protocol (FTP) in binary mode. Rename the file to JDBCdriver.zip and extract its content into the lib directory of the Apache JMeter installation directory.

Add a Thread Group

Example

Name: AVZ1 JDBC Thread Group Number of Threads (users): 1 Ramp-Up Period (in seconds): 1 Loop Count: 1000

Add a JDBC Connection Configuration

Add JDBC Request

Example

Name: JDBC Request Variable Name Bound to Pool: AVZ1 Server Query Type: Select Statement Query: “SELECT * FROM STAFFVS”

Add a Summary Report

The summary report provides the number of samples, average elapsed time, maximum elapsed time, minimum elapsed time, Throughput, and much more information to evaluate the performance of the JDBC query. Now the configuration is completed and ready to start the performance test.

Run a Performance Test

Once the Thread Group, JDBC Connection Configuration, JDBC Request, and Summary Report are configured, a performance test can be run as shown in Figure 16.

Figure 16 - Apache JMETER

Based on the number of samples set, JMeter runs the JDBC query for the number of iterations defined and provide the average, minimum, and maximum elapsed time and throughput for the performance evaluation. The performance evaluation can be carried out further by testing with different query types and the number of connections.

Performance testing using the Command Line Tester (CLT)

You can use the Command Line Tester (CLT) for evaluating the performance of specific queries, understand the number of rows, bytes read, elapsed time, etc. CLT was developed by IBM specifically for DVM for z/OS and works to simplify development, troubleshooting, and diagnosing problem queries. This tool also is leveraged through RESTful API calls.

• No limit on the number of rows processed  Reports summarized execution time for data transfer
• Isolates performance issues from application requesters
• Uses ANSI SQL  Flexible OUTPUT options
• Command line driven
• Validated through a RESTful API

This is the preferred approach because it is more of a natural measure for the actual query execution and can be requested from IBM Support and downloaded for your use. It is not currently included in the DVM for z/OS packaged software or as part of PTF maintenance releases. CLT is not a host application that runs on the mainframe, but rather a Java-based application that executes from a command prompt on MS-Windows or a terminal on Linux in Figure 17.

Figure 17. CLT output reading a VSAM file with 4,000,000 records

It is encouraged to avoid using the DVM Studio for query performance evaluation. The DVM Studio has limits for the number of rows that can be displayed, as well as the amount of HEAP space available.

Setup

This package comprises the test harness jar files and includes log4j2. The easiest approach is to copy the JDBC driver jar (for example, dv-jdbc-3.1.xxx.jar) from your production area to the same directory as these tester jars.

Basic Usage

Run the tool using Java from a command prompt.

Example:

Windows: java -cp .;* com.rs.jdbc.dv.CommandLineTester Unix/Linux: java -cp .:* com.rs.jdbc.dv.CommandLineTester <options>

Options

Use the --help option to display the usage options.

Example (run a query and display the elapsed time):

java -cp .;* com.rs.jdbc.dv.CommandLineTester –help

java -cp .;* com.rs.jdbc.dv.CommandLineTester --url "jdbc:rs:dv..." --sql "SELECT * FROM VT"

Multi-threaded MapReduce client test using MRCC/MRCN

Use the CommandLineMrcnTester class to run a multi-threaded MapReduce client test using MRCC/MRCN:

Example:

java -cp .;* com.rs.jdbc.dv.CommandLineMrcnTester --url "jdbc:rs:dv://host:1234; MRCC=4; ..." --sql "SELECT ..." --verbose

The URL must include the MapReduceClientCount/MRCC property, and then the tool adds the appropriate MapReduceClientNumber/MRCN setting for each separate connection. The --verbose flag displays the per-thread fetched row count and other details.