Db2

 View Only

  • 1.  DMC 3.1.4 Warnings about total activity time for sql, sql too big for DMC

    Posted Thu April 08, 2021 10:23 AM
    Hi DMC Team,

    I have many big sqls, causing long run time warnings via DMC. Problem is, these sqls cannot be captured entirely by DMC because the max size of sql text is 2500 bytes.

    When opening a warning with larger sqls, the following message occurs:

    Statement could not be formatted due to a syntax error. Please, validate your statement first.
    Error code 10, from line 1 col 2487 to line 1 col 2499 (offset 2486 to 2498), msg text: "EOF_TOKEN" reached after this token


    plus it is not possible to show explain etc, because sql text is cut off after 2500 bytes.

    Please make it possible to analyze SQL text larger than 2500 Bytes!

    Greetings from Hamburg

    ------------------------------
    Michael
    ------------------------------

    #Db2


  • 2.  RE: DMC 3.1.4 Warnings about total activity time for sql, sql too big for DMC

    Posted Fri April 09, 2021 04:21 AM
    Hi Michael,

    Thanks for reporting this issue. We support about 32kb of sql in dmc 3.1.4 and we are working at balancing SQL integrity with performance. Dose the message appear after clicking sql to view the whole statement? 



    ------------------------------
    Rui Li
    ------------------------------

    Original Message


  • 3.  RE: DMC 3.1.4 Warnings about total activity time for sql, sql too big for DMC

    Posted Fri April 09, 2021 05:35 AM
    Yes, the message appears after clicking the Message itself, if the "format SQL statement" Button has been pressed previously.
    The Table IBMCONSOLE.ALERT_PROPERTIES only has 2500 octets varchar for the SQL text. So it is cut off. When trying to format the SQL, the error occurs.


    Michael

    ------------------------------
    Michael Klemmer
    ------------------------------

    Original Message


  • 4.  RE: DMC 3.1.4 Warnings about total activity time for sql, sql too big for DMC

    Posted Fri April 09, 2021 08:43 AM

    ​I would like to see the captured SQL expanded as well. Max length of a SQL Statement is:2,097,152



    ------------------------------
    Douglas Kostelnik
    Applications Architect
    The Auto Club Group
    Tampa FL
    ------------------------------

    Original Message


  • 5.  RE: DMC 3.1.4 Warnings about total activity time for sql, sql too big for DMC

    Posted Mon April 12, 2021 10:21 PM
    This is a limitation. We need a better design to enhance this, for example, we may change the table to be CLOB, this will cause problem when upgrading.
    Also to support huge statement text, CLOB is not enough, we may cut the statement text into chunks and put it into CLOB columns, and merge back when pulling data.  Again we need a better design for this. Can you please open a RFE for this?

    ------------------------------
    XU QIN ZHAO
    ------------------------------

    Original Message


  • 6.  RE: DMC 3.1.4 Warnings about total activity time for sql, sql too big for DMC

    Posted Mon April 19, 2021 08:27 AM
    I do not quite understand: Do I have to request an enhancement for a program not to produce an error message?

    ------------------------------
    Michael Klemmer
    ------------------------------

    Original Message


  • 7.  RE: DMC 3.1.4 Warnings about total activity time for sql, sql too big for DMC

    Posted Tue April 20, 2021 11:34 PM
    We had internal discussion, no RFE is needed here, we are working on the fix.

    ------------------------------
    XU QIN ZHAO
    ------------------------------

    Original Message


  • 8.  RE: DMC 3.1.4 Warnings about total activity time for sql, sql too big for DMC

    Posted Thu April 22, 2021 08:13 AM
    Edited by System Fri January 20, 2023 04:15 PM

    @Douglas Kostelnik, sorry to jump in your trhead...

    @XU OIN ZHAO... 

    ​"Also to support huge statement text, CLOB is not enough, we may cut the statement text into chunks and put it into CLOB columns, and merge back when pulling data."

    ​Why do you believe CLOB wouldn't be enough..   CLOB columns can hold up to 2GB.   All Db2 STMT_TEXT columns in monitor table functions (MON_GET_...) are 2GB CLOB columns..   

    DMC should use the same..   AS the main IBM product to manage and monitor Db2 databases,  it should be capable of to monitor all Statements..  not only statements bellow a certain size.   ​

    ---   If performance is a concern,   use 32k tablespaces and configure the column as INLINE ,   to some good value, like 20k or something near that..  Most of the SQLs statements will be there in the bufferpool,  and will avoid the double IO. 


    ------------------------------
    Samuel Pizarro
    ------------------------------

    Original Message