Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums 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   Reply to topic    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: 205
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts How to insert a value on specific pos... Poha Eater DFSORT/ICETOOL 9 Sun Oct 01, 2017 3:04 am
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm

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