Formatting Storedprocedure will add blank to !! (concat)
Closed this issue · 11 comments
In Version 2.1.4 if I format this code
set v_error = -99
,v_errormsg = 'No such IDSERVER (' !! strip(v_package) !! ', ' !! strip(v_klassenName) !! ', ' !! strip(v_attrName) !! ')'
,v_type = 'Unknown';
it will result in
set v_error = -99, v_errormsg = 'No such IDSERVER (' ! ! strip(v_package) ! ! ', ' ! ! strip(v_klassenName) ! ! ', ' ! ! strip(v_attrName) ! ! ')'
, v_type = 'Unknown';
Which causes the system to display several error messages as !! is replaced by ! ! which is not recognized as a concat operator.
Hi @ThomasLieser,
Thanks for reporting this issue. We'll take a look at this.
Hi,
Please note that the visual rendering of characters, on mainframe and locally, depends on the encoding used.
The same hexadecimal encoding can be interpreted with different glyphs.
Expressions with the concatenation operator, for EBCDIC IBM-037 encoding, use || (x'4F4F') sequence, with a tolerance for the ]] (x'BBBB') sequence and !! (x'5A5A') sequence.
The formatting should therefore take into account the encoding used on the mainframe to interpret the character sequences locally, (I think UTF-8), since the local transfer will not produce the same characters depending on the encoding on mainframe.
Hex 0037 0500 1141 1147
----- ---- ---- ---- ----
x4F4F || !! !! !! <-- use case for this issue
x5A5A !! ]] ÜÜ §§
xBBBB ]] || || ||
@ThomasLieser: I think you are using EBCDIC codepage IBM-1141 on your Mainframe/Db2 system, which is why you are seeing !! after local transfer (in UTF-8 encoding).
Warning: the codepage declared in the Db2 system is not necessarily the one declared for the 3270 terminal (which is not recommended).
@FALLAI-Denis
Looks like I didn't explain this properly - sorry - I'm entering two ! and then I apply the Formatter - the code is modified with a blank between the two characters - and marked down as being in error (without deploying it).
I've never had this problem with Datastudio (though that didn't like !! on the start of the line).
I will check once I'm back in the office and give a better description)
Hi @ThomasLieser,
The description of the issue is clear: the formatting applied does not recognize the séquence !! and introduces a space between each !, probably because formatting only recognizes the sequence ||.
The purpose of my comments is to explain that depending on the codepage used on mainframe (and also used by the Db2 system), we can have a different visual rendering of hexadecimal values, on mainframe but also after local transfer and translation to UTF-8 (or ASCII).
The SQL parser that should be the basis of the formatting (tokenization of the SQL code) must therefore take into account the variations introduced by the codepages used on the mainframe.
Perhaps the extension should support a setting for interpreting characters used by SQL statements that have a visual rendering that depends on the mainframe codepage.
See Db2 for z/OS - EBCDIC and ASCII support for conversions between EBCDIC and ASCII.
@FALLAI-Denis Thanks for your feedback - I have been using Datastudio for years now - always used that way of specifying a concat operator. But it will not be a problem switching over to double pipe symbol.
@FALLAI-Denis
P.S.: as the double pipe works in all tools (QMF Spufi, Datastudio and DB2 Extension) we will just changes the occurences in our code before finally moving to VSCode!
Thanks again
misunderstand on my side - solved
P.S.: as the double pipe works in all tools (QMF Spufi, Datastudio and DB2 Extension) we will just changes the occurences in our code before finally moving to VSCode! Thanks again
Before modifying the code of your procedures, I would advise you to wait for an official response from the team that develops Db2 for z/os developer extension.
For my part I only gave an explanation on the interpretation of hexadecimal values for their visual rendering according to the codepage (ccsid) used.
Concerning the concatenation operator, the recommended hexadecimal value remains x4F4F, which corresponds to the visual || in IBM-037 and visual !! in IBM-1141. Switch to the visual || in IBM-1141 will switch to xBBBB hexadecimal coding, corresponding to a visual ]] in IBM-037, which is also not recommended.
Managing the interpretation of characters according to the codepage (ccsid) is a recurring problem with Db2, already working only on the mainframe, (hence the recommendation to use the same codepage in the Db2 system and the 3270 emulator), but this becomes more complex with EBCDIC and non-EBCDIC conversions.
For information, I encounter the same problem with programs in Rexx language which only consider IBM-037 encoding while I work with IBM-1147, and this poses a problem when it comes to working on these Rexx programs with VS Code (and IBM Z Open Editor).
Db2 for z/OS developer extension must take into account the impacts of the codepage (ccsid) used in Db2 and on the mainframe in general, and in this specific case should recognize the sequence !! which is also accepted by Db2.
OK, Reopened - waiting for official feedback about !! for concat and ^= for not equal - good to have a workaround though
In the end, what is important is to see what is recorded at the Db2 package level in the SYSPACKSTMT catalog table, and possibly at the DBRM level, (but in the case of stored procedures in native SQL there is no DBRM).
In principle, the Db2 catalog tables save information in UTF-8 encoding, I think by applying a conversion from the codepage (ccsid) declared for the single byte EBCDIC.
If as I assume you are working on mainframe with a 3270 terminal emulator and in Db2 with the same EBCDIC IBM-1141 encoding, and you are using || sequence for the concatenation operator, then this should work fine because it must be the same conversion that is used by Db2 with CREATE PROCEDURE statement and populating the SYSPACKSTMT catalog table and by Db2 for z/OS developer extension to work locally in UTF-8.
@ThomasLieser !! can be recognized in version 2.1.5. Closing this issue. Feel free to reopen it if you have further questions.