Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: Selecting a BLOB column in DB2 table

    I have a DB2 table column of type BLOB.
    XML files are stored in this column.

    I want to select this BLOB column in Enterprise Cobol (z/os) using a select statement.
    I have declared the working-storage variable as follows:

    01 MY-BLOB USAGE IS SQL TYPE IS BLOB(2M).

    When I select the Blob column into MY-BLOB and display the data,
    it displays non-readable characters.

    How do I convert this BLOB into Charater data?

    If this topic has already been discussed, please indiacate the corresponding thread.

    Thank you for your help!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You should define it as a CLOB, not a BLOB. A BLOB is for binary data and a CLOB is for character data.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    We cannot change it now as it is already in PROD.

    I have a to find a way to convert BLOB to CLOB or other string type.

    I appreciate any ideas from other members.

    Thank you.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You could rename the table and add a view in its place that does the conversion. However, you'd need to write a UDF that takes the BLOB and copies the data to return it as CLOB. This is bound to be slow.

    Another approach would be to adjust your application to accept the binary data and interpret it as string data. After all, its just a bunch of bytes.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You could rename the table and add a view in its place that does the conversion. However, you'd need to write a UDF that takes the BLOB and copies the data to return it as CLOB. This is bound to be slow.

    Another approach would be to adjust your application to accept the binary data and interpret it as string data. After all, its just a bunch of bytes.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Feb 2010
    Posts
    3
    Stolze,

    Thanks for your reply. Based on your suggestion, I converted the binary data into EBCDIC from in the cobol program.

    IBM - How to convert EBCDIC to ASCII or ASCII to EBCDIC in a COBOL Program?

    How to convert EBCDIC to ASCII or ASCII to EBCDIC in a COBOL Program?

    Question
    Within an Enterprise COBOL for z/OS program, how do I convert an item described as PICTURE X, which is EBCDIC on the mainframe, to ASCII?

    Cause
    Use the FUNCTION NATIONAL-OF and FUNCTION DISPLAY-OF. It is necessary to first convert it to Unicode. In the following example, WS-CCSID must match the code page of the data.

    Answer
    This example is for Enterprise COBOL on z/OS, not for COBOL on AIX.
    CBL DBCS,NSYMBOL(NATIONAL)
    IDENTIFICATION DIVISION.
    PROGRAM-ID. MAIN1.
    ENVIRONMENT DIVISION.
    DATA DIVISION.
    WORKING-STORAGE SECTION.
    01 WS-AREA PICTURE N(50).
    01 WS-CCSID PICTURE 9(5).
    01 WS-ASCII PICTURE X(50).
    01 WS-EBCDIC PICTURE X(50).
    PROCEDURE DIVISION.
    MOVE "ABCDE"
    TO WS-EBCDIC.
    DISPLAY " WS-EBCDIC = " WS-EBCDIC.
    MOVE FUNCTION NATIONAL-OF(WS-EBCDIC)
    TO WS-AREA.
    DISPLAY " WS-AREA = " WS-AREA.
    MOVE 819 TO WS-CCSID.
    MOVE FUNCTION DISPLAY-OF(WS-AREA, WS-CCSID)
    TO WS-ASCII.
    DISPLAY " WS-ASCII = " WS-ASCII.
    DISPLAY "Now let's convert that WS-ASCII back to EBCDIC".
    MOVE FUNCTION NATIONAL-OF(WS-ASCII,819 )
    TO WS-AREA.
    DISPLAY " WS-AREA = " WS-AREA.
    MOVE 1140 TO WS-CCSID.
    MOVE FUNCTION DISPLAY-OF(WS-AREA, WS-CCSID)
    TO WS-EBCDIC.
    DISPLAY " WS-EBCDIC = " WS-EBCDIC.
    STOP RUN.

    The EBCDIC letters ABCDE are hex C1C2C3C4C5. They will be converted to hex 4142434445. Then the 4142434445 will be converted back to C1C2C3C4C5.

    Thanks.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •