Joined: 02 Dec 2002 Posts: 629 Topics: 176 Location: Stockholm, Sweden
Posted: Thu May 16, 2024 5:04 am Post subject: Displaying binary data as "readable" such
I'm selecting data from a DB2 table that contains 2 binary fields. These fields (in reality) contain the equivalent of a DL/1 segment, so they can easily contain a mixture of character and COMP-3 fields.
The following is the actual select statement I'm using
Code:
SELECT T6H0.PNR
,hex(SUBSTR(T6H0.DL1_SEGMENT1,01,2)) AS GENNR
,T6H0.NAMN
,T6H0.ADR
,T6H0.POSTNR
,T6H0.ORT
,T6H0.NAMNKOD
,hex(SUBSTR(T6H0.DL1_SEGMENT2,01,1)) AS ROLL
,hex(SUBSTR(T6H0.DL1_SEGMENT2,02,5)) AS DFLDAT
,hex(SUBSTR(T6H0.DL1_SEGMENT2,07,1)) AS KUNDKOD1
,hex(SUBSTR(T6H0.DL1_SEGMENT2,08,1)) AS KUNDKOD2
,hex(SUBSTR(T6H0.DL1_SEGMENT2,09,1)) AS KUADRKOD
,hex(SUBSTR(T6H0.DL1_SEGMENT2,10,5)) AS ANSVDC
,hex(SUBSTR(T6H0.DL1_SEGMENT2,15,1)) AS DCRESERV1
,hex(SUBSTR(T6H0.DL1_SEGMENT2,16,5)) AS TRANSDAT
,objektid as OBJEKTID
,objektbort as OBJBORT
FROM TDRT6H0 T6H0
inner join TDRT6g0 T6g0
on T6H0.CCK_PNR = T6g0.CCK_PNR
WHERE T6H0.CCK_PNR = 96611168177
ORDER BY T6H0.EXTRACT_ORDER ASC
and these are the results
Quote:
0
0005
SIMPSON, MICHAEL
Any st 110
38276
MALERAS
10
40
000000000C
40
40
D6
4040404040
40
020200403C
6158902711100
What I would like to do would be to show the EBCDIC characters as ASCII (?), ie, show a 40 as a "space" and the D6 results as the letter O (as in Oscar).
In addition, it would be nice to show the 020200403C character as the decimal it really is.
I'm assuming I need SOMETHING like CAST or similar, but whatever I try doing doesn't result in what I'm after.
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Thu May 16, 2024 2:52 pm Post subject:
misi01,
misi01 wrote:
What I would like to do would be to show the EBCDIC characters as ASCII (?), ie, show a 40 as a "space" and the D6 results as the letter O (as in Oscar).
It is not ASCII. X'40' is space in EBCDIC and in ASCII it is X'20'.
Joined: 02 Dec 2002 Posts: 629 Topics: 176 Location: Stockholm, Sweden
Posted: Mon Jun 17, 2024 7:46 am Post subject:
Thank you for your suggestions. I'll almost certainly revisit the CASE example for the situation where the database can contain negative values (very unusual), but for all the "ordinary" positive values (such as SSN numbers) the following worked fine (or,at least, good enough for my needs).
Quote:
select
left(hex(SUBSTR(T3t0.DL1_SEGMENT1,01,06)), 11) as laannr
,left(hex(SUBSTR(T3t0.DL1_SEGMENT1,07,02)), 3) AS offant
LAANNR is packed, 6 long, and OFFANT is packed 3 long.
As you can imagine, indexing into the correct positions in the binary column is hard-coded enough, so adding one extra LEFT(....... , n) where n is the last character is not really adding that much extra work.
My real need is to be able to split up these binary columns and see their "constituent" parts which can consist of any number of individual DL/1 segment fields.
The solution above is good enough for me, but thank you again. _________________ Michael
Joined: 02 Dec 2002 Posts: 629 Topics: 176 Location: Stockholm, Sweden
Posted: Thu Sep 19, 2024 1:21 am Post subject:
It turned out to be much easier than I thought. Basically, I wrote a simple UDF (after testing how to define one via ChatGTP - although that generated the wrong definitions in the linkage section, but that was easy to get past).
Here is the resulting COBOL code I wrote
Code:
****************************************************************
* Arbetsareor
****************************************************************
01 arbetsareor.
05 ws-date pic 9(8).
05 ws-time pic 9(8).
*
****************************************************************
* LINKAGE SECTION.
****************************************************************
LINKAGE SECTION.
*
01 input-argument.
05 in-len pic s9(4) comp.
05 in-argument pic x(100).
*
01 output-argument.
05 out-len pic s9(4) comp.
05 out-argument pic x(100).
*
****************************************************************
* PROCEDURE DIVISION
****************************************************************
procedure division using
input-argument
output-argument
.
****************************************************************
perform b-init
*
move in-len to out-len
move in-argument(1:in-len) to out-argument
*
move 0 to return-code
*
goback.
****************************************************************
*
****************************************************************
b-init section.
*
accept ws-date from DATE YYYYMMDD
accept ws-time from time
* This ONLY so you know which reslts were produced when
* (if you include other DISPLAYs as well)
display 'MSUDF3 Current date/time 'ws-date
' '
ws-time(1:2)
':'
ws-time(3:2)
':'
ws-time(5:2)
end-if
*
exit.
That was the trivial program. The real magic is produced via the DB2 CREATE FUNTION. I would never have got to this without the ChatGTP code (which included an example).
Code:
DROP FUNCTION BINARY_TO_EBCDIC ;
CREATE FUNCTION BINARY_TO_EBCDIC(VARBINARY(100))
RETURNS VARCHAR(100)
EXTERNAL NAME 'MSUDF3'
LANGUAGE COBOL
PARAMETER STYLE DB2SQL
NO SQL
NO EXTERNAL ACTION
NOT DETERMINISTIC
RETURNS NULL ON NULL INPUT;
It's all in the definitions specified for the input/output arguments.
Originally, I had defined the input as VARCHAR (rather than the VARBINARY above). This resulted in the data coming in as I originally detailed above, ie, F0F1F2 instead of the 123 I wanted.
By changing the argument to VARBINARY, it comes into MSDF3 as 123 from the very start, and all the program has to do is send it back.
Now I can run my SQL select as
Quote:
select binary_to_ebcdic(substr(dl1_segment1, 13, 5)) as HANDL
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
