Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to insert a record into a table having BLOB with ROWID

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Tone

New User


Joined: 01 Oct 2008
Posts: 12
Location: India

PostPosted: Fri Dec 19, 2008 12:05 am    Post subject: How to insert a record into a table having BLOB with ROWID
Reply with quote

Hi all,

Requirement:
I need to transfer a picture (.bmp file) across MQ to DB2 tables in mainframe. For this the binaryvalue of the picture will be read using a cobol program from MQ and stored as a BLOB in DB2 table. The DB2 table has signature-BLOB field and ROW ID.

How this is working:
• The signature field that is of datatype BLOB, just contains the information of the field themselves , say whether it is a BLOB or CLOB.
•The BLOB field in this table (base table) is acting just like a pointer, ie. The actual data is in another table called as auxillary table.
•So inorder to uniquely connect each base table records to auxilary table we have some sort of address stored in ROWID datatype field.


How can we enter a record into the base table containing the BLOB datatype?
Considering the fact that base table consists only a pointer to actual data, if I have the binary value of a picture file, which field will get that in the base table?

As such we won't be able to directly edit the auxillary table.

Please Help!
Back to top
View user's profile Send private message

Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Dec 19, 2008 11:47 pm    Post subject:
Reply with quote

Hi

Define a Working Storage variable of size the same as the Blob data and you can use this variable in the INSERT query.

For example, the table column is defined as below

Code:

IN_BLOB BLOB(2097152) NOT NULL.

Declare a Working Stroage variable.
Code:

WS-IN-BLOB USAGE IS SQL TYPE IS BLOB(2097152).

After compiling this program, if we refer the Compile listing of this program, the above declared variable would be split as given below
Code:

01  WS-IN-BLOB.                                       
 02  WS-IN-BLOB-LENGTH                PIC S9(9) COMP.
 02  WS-IN-BLOB-DATA.                                 
      49 FILLER         PIC X(32767).                 
       --
       --
       --
       --
      49 FILLER         PIC X(64).

So once we get the input to the COBOL program

Find out the length of the data, move the length to WS-IN-BLOB-LENGTH and move the data to the WS-IN-BLOB-DATA. Use the variable WS-IN-BLOB directly in the INSERT query against the BLOB Column.
Code:

INSERT INTO <TABLE NAME>
                    (IN_BLOB)
            VALUES   
                    (:WS-IN-BLOB)


Hope this helps..
Back to top
View user's profile Send private message
anand_smail

New User


Joined: 05 Jun 2005
Posts: 13

PostPosted: Thu Apr 02, 2009 12:06 am    Post subject:
Reply with quote

Do you have a sample program, in my case i have BLOB data in a file with multiple rows. eg: my file have 15 lines of each and length of file is 32000.

Please help.
Back to top
View user's profile Send private message
Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 206
Location: Bangalore

PostPosted: Thu Mar 11, 2010 8:11 pm    Post subject:
Reply with quote

this info helps.
Back to top
View user's profile Send private message
Mr Swann

New User


Joined: 09 Jul 2020
Posts: 6
Location: France

PostPosted: Wed Jul 29, 2020 11:34 am    Post subject:
Reply with quote

Suresh Ponnusamy wrote:
Hi


Find out the length of the data, move the length to WS-IN-BLOB-LENGTH and move the data to the WS-IN-BLOB-DATA. Use the variable WS-IN-BLOB directly in the INSERT query against the BLOB Column.
Code:

INSERT INTO <TABLE NAME>
                    (IN_BLOB)
            VALUES   
                    (:WS-IN-BLOB)


Hope this helps..


Thank you @Suresh Ponnusamy

it was very helpfull for me !

I was facing various issues, but all solved by now.

My program is reading an unload from a DB2 of a table including BLOB collumns

and will insert them into another DB2

The issues

the DCLGEN does no match to the unload files ==> I have written a record description
( if anyone as a better solution ...)

the DCLGEN is not correct for the Insert ====>I have written a record description

the DCLGEN

Code:

     EXEC SQL DECLARE MYBLOB TABLE
     (
           CODREF CHAR(20) NOT NULL,
           FLGPREBLOB1 CHAR(1) NOT NULL,
           BLOB1 BLOB(20480) NOT NULL,
           FLGPREBLOB2 CHAR(1) NOT NULL,
           BLOB2 BLOB(102480) NOT NULL
       )
      END-EXEC


and the cobol part

Code:
 01 DCLMYBLOB.
          10  CODREF PIC X(20) .
          10 FLGPREBLOB1 PIC X(1).
          10 BLOB1 USAGE SQL TYPE IS BLOB LOCATOR.
          10 FLGPREBLOB2 PIC X(1).)
          10 BLOB2 USAGE SQL TYPE IS BLOB-LOCATOR.




the record defining the unload file is


Code:
 01 UNL-MYBLOB.
          10  CODREF PIC X(20) .
          10 FLGPREBLOB1 PIC X(1) .
          10 BLOB1 .
                 49 BLOB1-LENGTH PIC S9(9) COMP-5.
                 49 BLOB1-DATA PIC X(5625).
          10 FLGPREBLOB2 PIC X(1).
          10 BLOB2 .
                 49 BLOB2-LENGTH PIC S9(9) COMP-5.
                 49 BLOB2-DATA PIC X(9000).




for the figures 5625 and 9000, I have looked into the unload file ...


the record for the insert is

Code:
 01 INS-MYBLOB.
          10  CODREF PIC X(20) .
          10 FLGPREBLOB1 PIC X(1) .
          10 BLOB1 USAGE IS SQL
                 TYPE is BLOB(20480).
         
          10 FLGPREBLOB2 PIC X(1).
          10 BLOB2 USAGE IS SQL
                 TYPE is BLOB(102400)




the figures form the size of BLOBS are comming from the DCLGEN
Back to top
View user's profile Send private message
Mr Swann

New User


Joined: 09 Jul 2020
Posts: 6
Location: France

PostPosted: Wed Jul 29, 2020 11:53 am    Post subject:
Reply with quote

So I read the input FILE


Code:
MOVE CORRESPONDING UNL-MYBLOB
                              to  INS-MY-BLOB


and I can do the INSERT

Code:

EXEC SQL
    INSERT INTO MYBLOB
     (
        CODREF ,
        FLGPREBLOB1,
        BLOB1 ,
        FLGPREBLOB2 ,
        BLOB2
)

VALUES
(
        :INS-MY-BLOB.CODREF ,
        :INS-MY-BLOB.FLGPREBLOB1,
        :INS-MY-BLOB.BLOB1 ,
        :INS-MY-BLOB.FLGPREBLOB2 ,
        :INS-MY-BLOB.BLOB2
)




and it works well ..

Regards
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problem getting record length of a re... amitc23 COBOL Programming 2 Thu Jul 02, 2020 10:52 pm
No new posts Hex Table josephineyeow COBOL Programming 2 Thu Jun 18, 2020 7:26 pm
No new posts How to fetch the latest record from a... Dinesh Mani DFSORT/ICETOOL 8 Tue Jun 02, 2020 5:34 pm
No new posts Insert System Runtime in HEADER In SY... Rick Ryan SYNCSORT 9 Tue May 26, 2020 9:01 pm
No new posts Catalog table SYSSTATSFEEDBACK beikircherw DB2 2 Tue May 19, 2020 12:20 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us