MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

COBOL and SQL IS BINARY DB2 column

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Application Programming
View previous topic :: View next topic  
Author Message
misi01
Advanced


Joined: 02 Dec 2002
Posts: 620
Topics: 173
Location: Stockholm, Sweden

PostPosted: Tue May 07, 2024 1:23 am    Post subject: COBOL and SQL IS BINARY DB2 column Reply with quote

We have a DB2 table defined with a column defined as Binary (yes, completely new for me as well).

When the copybook is generated the resulting DB2 definition is
Code:
 ,DL1_SEGMENT                    BINARY(16)
                                 NOT NULL) 

and its equivalent COBOL definition is
Code:
05 DL1-SEGMENT      SQL TYPE IS BINARY(16).


My questions.

    - has anyone had any experience of such a DB2 column
    - can you, COBOL-wise, simply move a character group field to this COBOL
    DL1-SEGMENT field and then do an SQL INSERT?
    - might it be better to define the column as a BLOB?



Why is it being defined as BINARY you might ask?
Because, in reality, the example above might consist of 3 DL1-SEGMENT
fields, one packed, one character and another packed (using 16 bytes in total).

One of the problems I ran into was when trying to create test data for the column. No matter which variations I tried, the INSERT failed (both using DBEAVER and SPUFI in TSO).
There's lots of examples of how you can SELECT binary data from a table, but I haven't found one where they illustrate an INSERT of a row into a table where one column was binary.

We're trying to replace an Oracle database and there, the equivalent Oracle column is defined as raw/binary (what I was told).
We're then going to run a program that can mask sensitive data, but supposedly, this program can't handle low-values (as in an amount field containing '00000C'x - and yes, I'm aware that that logically contains low-values so what would the difference be if the column is defined as SQL BINARY or a VARCHAR field. Next question)
_________________
Michael
Back to top
View user's profile Send private message Send e-mail
misi01
Advanced


Joined: 02 Dec 2002
Posts: 620
Topics: 173
Location: Stockholm, Sweden

PostPosted: Tue May 07, 2024 2:37 am    Post subject: Reply with quote

I'll answer my own question in case anyone else has the same problem (a colleague helped me).
The following can insert a row into a table
Quote:
insert into TDRT3C0
values( 0 -- LOG_NR_ORDER
,0 -- DATABASE_NR
,1 -- EXTRACT_ORDER
,'DFFLANHI' -- DATABASE_NAME
,0 -- SEGMENT_CODE
,'SFLANH' -- SEGMENT_NAME
,0 -- RESERVED_FIELD
,6 -- CCK_LENGTH
,1 -- CCK_LAANNR
,16 -- SEGMENT_LENGTH
,binary(x'00000000001C40404040404040404040') -- DL1_SEGMENT1
);


Note the use of the x before the actual string. This way, I've defined the column as containing a packed value of 1 as well as 10 spaces.
_________________
Michael
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12370
Topics: 75
Location: San Jose

PostPosted: Wed May 08, 2024 3:08 pm    Post subject: Reply with quote

misi01,

If the db2 column is defined as BInary, and if you are loading a packed decimal number, do you get right values if there is a comparison or arithmetic operation on column ?

You are better off defining it as character
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
misi01
Advanced


Joined: 02 Dec 2002
Posts: 620
Topics: 173
Location: Stockholm, Sweden

PostPosted: Mon May 13, 2024 6:21 am    Post subject: Reply with quote

The actual content of the binary fields is only used as a sort of placeholder.

The actual s/w package is intended to mask things like SSN values so they can't be tied to a "real" person.We are replacing one bit of s/w with home-grown ditto.

This reads the DL/1 database, inserts the segment contents into one or more DB2 columns. The mask program then changes the SSN etc and writes the results back to the DB2 table. After that, a COBOL program will read the table and do a DLET/REPL on the root segment based on the contents of the table. This in turn means that that the binary fields should never change.
For that reason, the actual binary columns contain (for the most part) the equivalent of COBOL group variables.
The example I originally posted corresponded to a comp-3 field plus a PIC X OCCURS 10 group field.
_________________
Michael
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Application Programming All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group