IBM Support

PTF UI78836 resolves a Data integrity issue in Data Virtualization Manager (DVM) server

Troubleshooting


Problem

End users applied PTF UI78836 to a Data Virtualization Manager (DVM) server and run a Java test program to perform impact check.
The DVM server fails to find PCB when two(2) or more SQL queries run against the same Virtual Table based on IMS database. As a result, it fails with Java error message: Unable to find available PCB.

Symptom

When the Java program runs, the following error message is displayed below:
02:39:20.638 [main] ERROR com.rs.jdbc.dv.cmbu.CmbuBuffer - Throwing
com.rs.jdbc.dv.cmbu.CmbuSqlException: [DV][JDBC Driver][3.1.202112141530] Unable to find available PCB
    at com.rs.jdbc.dv.cmbu.CmbuHlBody.validate(CmbuHlBody.java:486) [dv-jdbc-3.1.202112141530.jar:3.1.202112141530]
    at com.rs.jdbc.dv.cmbu.CmbuHlBuffer.(CmbuHlBuffer.java:37) [dv-jdbc-3.1.202112141530.jar:3.1.202112141530]
    at com.rs.jdbc.dv.cmbu.CmbuHlBuffer.wrap(CmbuHlBuffer.java:27) [dv-jdbc-3.1.202112141530.jar:3.1.202112141530]
    at com.rs.jdbc.dv.DvPreparedStatement.executeUpdate0(DvPreparedStatement.java:833) [dv-jdbc-3.1.202112141530.jar:3.1.202112141530]
    at com.rs.jdbc.dv.DvPreparedStatement.executeUpdate(DvPreparedStatement.java:753) [dv-jdbc-3.1.202112141530.jar:3.1.202112141530]
    at savz.RecreationNo17.main(RecreationNo17.java:52) [bin/:?]
com.rs.jdbc.dv.cmbu.CmbuSqlException: [DV][JDBC Driver][3.1.202112141530] Unable to find available PCB [sqlCode=-1 (0xffffffff), message1="Unable to find available PCB"]
    at com.rs.jdbc.dv.cmbu.CmbuHlBody.validate(CmbuHlBody.java:486)
    at com.rs.jdbc.dv.cmbu.CmbuHlBuffer.(CmbuHlBuffer.java:37)
    at com.rs.jdbc.dv.cmbu.CmbuHlBuffer.wrap(CmbuHlBuffer.java:27)
    at com.rs.jdbc.dv.DvPreparedStatement.executeUpdate0(DvPreparedStatement.java:833)
    at com.rs.jdbc.dv.DvPreparedStatement.executeUpdate(DvPreparedStatement.java:753)
    at savz.RecreationNo17.main(RecreationNo17.java:52)

Cause

This is the data integrity issue that is resolved by PTF UI78836.
Before this PTF, DVM server may have allowed a PSB with one(1) PCB to run the second SQL query; but that should not have worked.
That gap is closed by this PTF and this is how it should work.
This is documented in the IBM Data Virtualization Manager for z/OS1.1.0 manual under section SQL access to IMS DB.

Environment

IMS databases

Diagnosing The Problem

Here is a sample Java Test program to run to recreate the problem when DVM server is at the following build level:
DVM V1.1 (UI78836 level)
SQL Jan 25 2022 11:02:51 Version 2.1 AMODE 64 ARCH 11 Production OPINCM   High ASM Module 2022/01/11 09.50    SVFX0000 SVFX 2017-09-29 09:19:54
import java.lang.invoke.MethodHandles;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
	
public class JavaTest {
	public static void main(String[] args) {

		System.out.printf("start %s\n", MethodHandles.lookup().lookupClass());

		Connection con;

		Scanner scanner = new Scanner(System.in);
		
		try {
			Class.forName("com.rs.jdbc.dv.DvDriver");
			con = DriverManager.getConnection(
					"jdbc:rs:dv:DatabaseType=DVS; Host=xxxx; password=password; Port=nnnn; user=yyyy;"
					);

			con.setAutoCommit(false);
			
			//prepareStatement for UPDATE
			
			String sql = "UPDATE IMS_IDA4_FL00P1_DEP1_DIRECT SET DEP1KEY_SUFFIX = 'DP2' WHERE DEP1KEY_NUMBER = 2;";
			PreparedStatement pstmt = con.prepareStatement(sql);
			
			System.out.println("Wait for input 1; type into the console");
			String s = scanner.next();
			System.out.println("Accepted input is: " + s);
			
			//prepareStatement for SELECT and executeQuery
			String sql2 = "SELECT DEP1KEY, DEP1KEY_NUMBER, DEP1KEY_SUFFIX, RECORD_ID, PARENT_ID, CHILD_ID FROM IMS_IDA4_FL00P1_DEP1_DIRECT ORDER BY DEP1KEY LIMIT 10;";
			PreparedStatement pstmt2 = con.prepareStatement(sql2);
			ResultSet rs = pstmt2.executeQuery();
			while (rs.next()) {
				System.out.print(rs.getString(1));
				System.out.println(rs.getString(2));
			};
			
			System.out.println("Wait for input 2; type into the console");
			String s2 = scanner.next();
			System.out.println("Accepted input is: " + s2);
			
			//executeUpdate
			pstmt.executeUpdate();
			
			System.out.println("Wait for input 3; type into the console");
			String s3 = scanner.next();
			System.out.println("Accepted input is: " + s3);
			
			con.commit();
			pstmt.close();
			con.close();
			scanner.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return;
	}
}
In the Java program, it sets the connection's Auto Commit property to FALSE and does one(1) prepare UPDATE SQL statement and following by another prepare SELECT SQL statement.
After execution, the following error message in displayed in the output console or in the Java error log depending on how the test program is  run. Below is the screenshot of the output console from the Eclipse IDE.
Java error message

Resolving The Problem

To run multiple units of work, two or more PCBs in the PSB are required. Creating new PCB as needed for that PSB will resolve the issue.

Document Location

Worldwide

[{"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":"","label":""}],"Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"All Versions"}]

Document Information

Modified date:
13 April 2022

UID

ibm16565649