Issues with the Debugger

[mykael22000]

Development environment where the bug occurred

• Db2 Developer Extension version: v2.1.5
• Editor platform
  • Visual Studio Code
  • Eclipse Theia
• Editor platform version:
• Operating system on which your editor runs (for example, Windows 10 2004 or MacOS Catalina 10.15.7): Windows 10
• Java Version (Run java -version and paste the details here): jdk-17.0.9+9
• Db2 for z/OS version (including function level for Db2 12): Db2 12, don't know function level
• Log files attached?: No

Problem description

The debugger seems to be getting lost when it's stepping through my code. The first few statements are ok, but then it starts pausing on comments and half way through multi-line strings:

The previous line it stopped on was line 52.

Clicking step over then stops on the IF on line 58 and then the END IF on line 64.

A couple of step overers later and I'm looking at:

The IF statements above it aren't even satisfied and why's it stopped half way through a string constant?

The value of the STMT variable is also truncated in the WATCH list:

Which is a problem because it's a dynamic SQL statement that I'm building up.

I suspect the issue may be related to my multi-line string constants, but it's valid SQL and I need them to keep the code legible.

The stored procedure runs to completion and produces the correct results, so Db2 is happy with it. It's just the debugger that doesn't work :-( .

Observed behavior

See above.

• It gets lost, not relating its position to my source code.
• Variable values are being truncated.
• Run to Line and Break points in the latter half of the code are ignored.

Expected behavior

• If should align itself properly with the source code.
• The full variable value should be shown - or at least returned when I copy value. I'd like to paste it into DBeaver to see if it actually runs.
• Run to Line and Break Points should work for all of my procedure, not just the first 50 lines.

[amadhavia]

@mykael22000 Hi, What kind of stored procedure are you trying to debug?
Is this a user defined function?
What's the language of your application?

Regards,
Madhavi

[mykael22000]

Just a simple native SQL one. Intended to end up backing a Db2 REST service, but for now just working with it from vscode.

-- ####################################################################
-- # Basic CREATE PROCEDURE statement (SQL - native)
-- # See https://www.ibm.com/docs/en/db2-for-zos/13?topic=statements-create-procedure-sql-native for complete syntax.
-- ####################################################################
--#SET TERMINATOR #
CREATE PROCEDURE TEST.GET_ELEMENTS ( IN DATA_SCHEMA CHAR(8), IN FIRST_DAY CHAR(10), IN LAST_DAY CHAR(10) )
    VERSION V1
    ISOLATION LEVEL UR
    RESULT SETS 1
    LANGUAGE SQL
    ALLOW DEBUG MODE
P1: BEGIN
    -- #######################################################################
    -- # Ask for all ELEMENTs between the supplied date
    -- #######################################################################
    DECLARE STMT VARCHAR(2000);
    -- Declare Cursor
    DECLARE CUR1 CURSOR WITH RETURN FOR PSTMT;

    -- Build the statement
    SET STMT  = '
        SELECT 
           DISTINCT ELEMENT_ID';
           
    SET STMT  = STMT||'
        FROM 
            '||TRIM(DATA_SCHEMA)||'.ELEMENTS_D';
            
    SET STMT  = STMT||' 
        WHERE 
            ELEMENT_SEEN = "LOCAL" 
            AND ELEMENT_TYPE IN ( ''A'', ''B'' )
	    AND DATE >= '''||FIRST_DAY||''' 
     	    AND DATE <= '''||LAST_DAY||'''';
           
    SET STMT  = STMT||'
        ORDER BY 
            ELEMENT_ID';
    
    -- Prepare the statement
    PREPARE PSTMT FROM STMT;

    -- Cursor left open for client application
    OPEN CUR1;
END P1#
--#SET TERMINATOR ;

It constructs a dynamic SQL statement and returns a cursor over its output...

Runs ok, but the statement alignment is off under debug and the value of the STMT variable is being truncated in the Watch and Variables display. Makes debug pretty useless for it.

Mik

[b-tsao]

Hi @mykael22000,

Thank you for reporting the issue.

We've verified the issue with your provided stored procedure (thanks for that!) and have opened an internal issue to track.

The issue is caused by the multi-line string that the debugger does not account for when determining the statement position and thus the offset ends up looking like it's lost.

[mykael22000]

Cool, thanks.

Will you also be looking at the statement truncation in the variable watch? I'd like to be able to copy the complete statement out at the end and past it into DBeaver for debugging...