Db2 - L

 View Only
  • 1.  Storing JSONs in DB2 after Cobol's JSON GENERATE

    Posted May 18, 2022 01:25 PM
    Edited by Alina Dehler May 18, 2022 01:25 PM
    I hope I did not miss anything when looking for an answer in this forum.

    The Cobol function JSON GENERATE generates a JSON in UTF-8. When we then want to store this JSON in a DB2 table, we are trying to figure out what the best table definition would be. Our contenders:

    - Convert JSON to ebcdic before insert and store it in varchar or clob (ebcdic table)
       drawback: unnessacary conversion that possibly allocates huge storage chunks for the conversion
    - Store JSON in blob (ebcdic table)
       drawback: some tools and apis don't know how to interpret the blob-content
    - using a unicode table
      drawback: values for the other columns in the table have to be converted from ebcdic to UTF-8

    Does anyone have experience with a similar issue or any recommendation?
    Thank you in advance!


  • 2.  RE: Storing JSONs in DB2 after Cobol's JSON GENERATE

    Posted May 18, 2022 02:04 PM
    One question to ask is what will you do with the data once it is in Db2?

    Will you apply JSON functions to it? Then store it as BSON and use the JSON capabilities inside Db2.
    Are you going to simply store it and it's not to big? Then just put it in a varchar Unicode column.
    Will you simply store it and it is big? Then put it in a CLOB Unicode column.

    Cheers,
    Dan




  • 3.  RE: Storing JSONs in DB2 after Cobol's JSON GENERATE

    Posted May 19, 2022 06:28 AM
    Alina, Don't forget that you can add a Unicode column to an EBCDIC table.

    ------------------------------
    Mark Rader IBM
    ------------------------------



  • 4.  RE: Storing JSONs in DB2 after Cobol's JSON GENERATE

    Posted May 19, 2022 02:48 AM
    Hi ,

    You can add a single unicode- column on a EBCDIC-table. 

    Greetings
    Patrick





  • 5.  RE: Storing JSONs in DB2 after Cobol's JSON GENERATE

    Posted May 19, 2022 04:00 AM
    I'd indeed go for a UTF-8 column, not EBCDIC.
    One additional option (not yet mentioned) would be to create a new table with just the JSON column, plus of course a FK column (possibly with a unique constraint on it) pointing back to the table where you would otherwise have added this JSON column to.
    If necessary, a view (on the join) can make it appear as if the column belongs to the base table.
    One advantage of this construction is that the image copy (backup) scenario for this column can be different from the scenario for the base table. Plus, of course, any other physical choices like storage group, page size, partitioning, ... which (I guess) should indeed be chosen differently from those of the base table.

    ------------------------------
    Peter Vanroose
    ABIS Training & Consulting
    https://www.abis.be/html/enDB2Calendar.html
    ------------------------------



  • 6.  RE: Storing JSONs in DB2 after Cobol's JSON GENERATE

    Posted May 19, 2022 04:06 AM
    Hello,
    thank you all for your answers. We do not have to use the DB2 JSON capabilities and the JSON could get pretty big, so a CLOB would be best. Having the JSONs in a seperate table is also something that coul be considered.
    We were not sure that a unicode column in an ebcdic table ist possible. But we also just found this article here.
    Unfortunately there is no code example for the create table. Do you have by any chance an example or a link on how to define an ebcdic table with a single unicode column?

    ------------------------------
    AlinaDehlerR+V Allgemeine Versicherung AG
    ------------------------------



  • 7.  RE: Storing JSONs in DB2 after Cobol's JSON GENERATE

    Posted May 19, 2022 04:15 AM
    Edited by Peter Vanroose May 19, 2022 04:20 AM
    From the SQL Reference guide for Db2 12, p. 1219:

                ALTER TABLE xxx ADD COLUMN yyy CLOB CCSID 1208

    ------------------------------
    Peter Vanroose
    ABIS Training & Consulting
    https://www.abis.be/html/enDB2Calendar.html
    ------------------------------



  • 8.  RE: Storing JSONs in DB2 after Cobol's JSON GENERATE

    Posted May 19, 2022 10:09 AM
    In Version 12, you can have a Unicode column in an EBCDIC table.


    Best Regards,

    Chris

    Chris Crone
    Distinguished Engineer - Database Technology  |  Mainframe Software Division
    Broadcom


    This electronic communication and the information and any files transmitted with it, or attached to it, are confidential and are intended solely for the use of the individual or entity to whom it is addressed and may contain information that is confidential, legally privileged, protected by privacy laws, or otherwise restricted from disclosure to anyone else. If you are not the intended recipient or the person responsible for delivering the e-mail to the intended recipient, you are hereby notified that any use, copying, distributing, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. If you received this e-mail in error, please return the e-mail to the sender, delete it from your computer, and destroy any printed copy of it.