Results 1 to 8 of 8

Thread: CLOB conversion

  1. #1
    Join Date
    Oct 2011
    Posts
    27

    Lightbulb Unanswered: CLOB conversion

    I have couple of CLOB columns which is taking the 80% of the total db size. Now I wish to come up with new idea to convert it to different variable to reduce the storage space. Let me know for any questions...

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Insufficient detail in your question. Post relevant facts, such as the DDL (from db2look) for the table(s) concerned, your DB2 version+fixpack+operating-system-platform. You may need to engage with your application developers and/or a data modeller to assess the impacts of any changes you propose.

  3. #3
    Join Date
    Oct 2011
    Posts
    27
    CREATE TABLE "FIX "."ELGT_RULE_ENG_TABLE" (
    "ELGT_RULES_ENG_REQ_RES_ID" DECIMAL(12,0) NOT NULL ,
    "APPLN_ID" DECIMAL(12,0) ,
    "SERVICE_NM" VARCHAR(50 OCTETS) ,
    "REQUEST_XML" CLOB(10485760 OCTETS) INLINE LENGTH 500 LOGGED NOT COMPACT ,
    "RESPONSE_XML" CLOB(10485760 OCTETS) INLINE LENGTH 500 LOGGED NOT COMPACT ,
    "CRTD_BY_NB" DECIMAL(12,0) ,
    "UPDTD_BY_NB" DECIMAL(12,0) ,
    "CRTD_DT" TIMESTAMP ,
    "UPDTD_DT" TIMESTAMP ,
    "PROCESSED_IN" CHAR(1 OCTETS) ,
    "ARCHIVE_DT" TIMESTAMP(0) )
    IN "TS_FIXRULE_D_32K" INDEX IN "TS_FIXRULE_I_32K" LONG IN "TS_FIX_LARGE_32K"
    ORGANIZE BY ROW;

    LINUX - 10.5 FP6 - PureScale.

    I'm interested in converting the two CLOB on this table.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What do you want to convert them to?

    Andy

  5. #5
    Join Date
    Oct 2011
    Posts
    27
    I think i need to rephrase it. The CLOB has inline and outline.
    I need to calculate how much is the size of a row for both columns.
    then we need to check if it can be occomodated in VARCHAR if not what more options do we have to bring down the size.
    May be xml ?
    Just want to know what other option can bring these sizes low

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Since you want to reduce the amount of disk space being used, I would suggest this. Since it appears that (based on the column names) that they contain XML data. Change the columns to type XML and turn compression on to have DB2 make them smaller on disk.

    Obviously, you will need to work with the developers to change the data type.


    Andy

  7. #7
    Join Date
    Oct 2011
    Posts
    27
    Thanks for that great answer. Thats my doubt too. How do you think the CLOB conversion to XML will bring down the space utilization ? Turning on adaptive compression can surely bring down however I still wonder if XML variable will bring down the space utilization ?

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not think that just switching data types (to anything including XML) will save you any space. You will need to use compression and XML compresses very well.

    Andy

Posting Permissions

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