Topic
  • 5 replies
  • Latest Post - ‏2011-08-16T23:01:16Z by MatthiasNicola
optim_user
optim_user
3 Posts

Pinned topic CLOB Out parameter Size

‏2011-08-16T20:29:34Z |
Hi,
I am using CLOB(1M) for one of output parameters but output data is limited to 32768 while executing this SP. We are using DB2 V9.1.

Can you please help!!

Thank you.
Updated on 2011-08-16T23:01:16Z at 2011-08-16T23:01:16Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: CLOB Out parameter Size

    ‏2011-08-16T20:38:46Z  
    Which SP are you referring to?
    I think you need to provide more details and be more precise in your question before we can help you!

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • optim_user
    optim_user
    3 Posts

    Re: CLOB Out parameter Size

    ‏2011-08-16T20:55:30Z  
    Which SP are you referring to?
    I think you need to provide more details and be more precise in your question before we can help you!

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Sorry..
    Complete details..

    This is DB2 SQL Procedure where i want write all table contents(around 1000 records) into XML and return that XML.

    When I run this SQL I am getting first 32768 characters of data only, anything after that is truncated.

    Please tell me if I am missing anything here.

    CREATE PROCEDURE CLOB_TEST(OUT OUT_XML CLOB(1M))
    VALIDATE BIND
    WITH EXPLAIN
    PACKAGE OWNER XXXXXX
    COMMIT ON RETURN YES
    ISOLATION LEVEL CS
    LANGUAGE SQL
    SELECT XMLSERIALIZE(XMLELEMENT(NAME "ROOT_TAG",B.RESULT)
    AS CLOB(1M))
    INTO OUT_XML
    FROM (
    SELECT XMLAGG (XMLELEMENT(NAME "TEST_DATA",XMLFOREST
    (
    A.NAME AS "ACT_NAME",
    A.ADDRESS AS "ACT_ADDR"
    ))) AS RESULT
    FROM (
    SELECT NAME
    ,ADDRESS
    FROM TEST_TABLE
    ) A
    ) B;
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: CLOB Out parameter Size

    ‏2011-08-16T21:31:15Z  
    Sorry..
    Complete details..

    This is DB2 SQL Procedure where i want write all table contents(around 1000 records) into XML and return that XML.

    When I run this SQL I am getting first 32768 characters of data only, anything after that is truncated.

    Please tell me if I am missing anything here.

    CREATE PROCEDURE CLOB_TEST(OUT OUT_XML CLOB(1M))
    VALIDATE BIND
    WITH EXPLAIN
    PACKAGE OWNER XXXXXX
    COMMIT ON RETURN YES
    ISOLATION LEVEL CS
    LANGUAGE SQL
    SELECT XMLSERIALIZE(XMLELEMENT(NAME "ROOT_TAG",B.RESULT)
    AS CLOB(1M))
    INTO OUT_XML
    FROM (
    SELECT XMLAGG (XMLELEMENT(NAME "TEST_DATA",XMLFOREST
    (
    A.NAME AS "ACT_NAME",
    A.ADDRESS AS "ACT_ADDR"
    ))) AS RESULT
    FROM (
    SELECT NAME
    ,ADDRESS
    FROM TEST_TABLE
    ) A
    ) B;
    Thanks!

    Do you call this SP from application code (e.g. from a Java application) or in the DB2 Command Line Processor (CLP)?

    Note that the CLP is a very simplistic interface to DB2 and always truncates large LOB and XML values. If you want to retrieve very large values through the CLP, the best approach is to use the EXPORT utility that can write the result of any SQL SELECT statement to a file.

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • optim_user
    optim_user
    3 Posts

    Re: CLOB Out parameter Size

    ‏2011-08-16T21:55:06Z  
    Thanks!

    Do you call this SP from application code (e.g. from a Java application) or in the DB2 Command Line Processor (CLP)?

    Note that the CLP is a very simplistic interface to DB2 and always truncates large LOB and XML values. If you want to retrieve very large values through the CLP, the best approach is to use the EXPORT utility that can write the result of any SQL SELECT statement to a file.

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    I am using DB2 Optim Data Studio to run the SP(I didn't see any option to limit the column/result's length) and I see only 32768 chars of data.

    Surprisingly if I take out the query and execute it as plain SQL, I get complete information.
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: CLOB Out parameter Size

    ‏2011-08-16T23:01:16Z  
    I am using DB2 Optim Data Studio to run the SP(I didn't see any option to limit the column/result's length) and I see only 32768 chars of data.

    Surprisingly if I take out the query and execute it as plain SQL, I get complete information.
    Ah, ok. So it seems to be an issue with Data Studio.

    The fastest way to get advice from a Data Studio expert is to also post the problem in the Data Studio forum:
    https://www.ibm.com/developerworks/forums/forum.jspa?forumID=1086

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/