DB2 – Multiple Result-Set Stored Procedure

So first off – I’m not sure if my problem lies with my DB2 code, or with my settings on DBeaver.

I have an issue when running the following code where only one result set is being displayed in DBeaver. My intention is to create a single procedure that returns 2 result sets to the UI. The purpose of this is so that I can run multiple scripts at once, and then have all of the result sets in the same location. I don’t have any other software than DBeaver, so I can’t exactly test this on any other management studio.

I’m doing this so that if I’m not in the office, someone else can easily run my queries. Let me know if any additional information is required / you have any questions. Table/column names have been made up for the question.

CREATE PROCEDURE dbo.procedure_name (IN start_date date, IN end_date date) 
DYNAMIC RESULT SETS 2   
BEGIN

DECLARE C1 CURSOR WITH RETURN TO CALLER FOR
    SELECT 
        d.Student_NUM AS student_number,
        c.Teacher_NUM AS teacher_number,
    FROM dbo.c AS teachers
    INNER JOIN dbo.d AS student ON c.class_num= d.class_num;
    WHERE d.entry_date >= start_date and d.graduation_date < end_date


DECLARE C2 CURSOR WITH RETURN TO CALLER FOR
    SELECT 
        c.birth_date AS teacher_birth_date,
    FROM dbo.c AS teachers;

    OPEN C2;
    OPEN C1;   
END

I only get results for C2

If I switch the ordering of the OPEN I only get C1. It seems like it only returns the first OPEN cursor.

UPDATE

This is how the stored procedure is called, and the return set I am seeing. NOTE: the name of the return set is being displayed as C and I know I should be expecting Result and Result-2. (sorry for heavy redacting)

DBeaver version: 5.2.5

DB2 version: 9.5 Fix Pack 5

OS: Windows

Answer

Resolved by ballbreaker with a dbeaver configuration change.

This symptom will only appear if your database connection uses the “old” driver supplied with dbeaver called “DB2 LUW 8.x”. That driver is not automatically installed with current dbeaver versions (but can be manually downloaded), because a more recent driver is installed by default with current dbeaver versions.

If your database connection uses the driver called “DB2 LUW” (as distinct from “DB2 LUW 8.x”) then you will not experience this symptom.

If your connection uses the old driver (DB2 LUW 8.x), then Under connection properties for the database, there is an “Edit Driver Settings” -> Adv. parameters -> “Driver supports multiple results” . With the old driver, this setting was disabled by default, which caused the symptom. With the newer driver, there is no such setting.