ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » DatabaseInput Node is taking maximum CPU usage ?

Post new topic  Reply to topic
 DatabaseInput Node is taking maximum CPU usage ? « View previous topic :: View next topic » 
Author Message
junaid
PostPosted: Mon Oct 21, 2019 11:06 pm    Post subject: DatabaseInput Node is taking maximum CPU usage ? Reply with quote

Acolyte

Joined: 29 Nov 2018
Posts: 58

Hi,
My Scenario is as following .
1st Flow:

SOAPInput > Transform > SOAP Req (response 00)> Transform > SOAP Reply
If response from SOAP is 01 then flow order 1> transform > MQOutput
flow order 2> transform > SOAP Reply

2nd Flow:
MQInput > DB Insert record using ESQL

3rd Flow:
DatabaseInput > Trace > MQOutput

4th Flow:
MQInput > Transform > SOAP Req > Db update Record in ESQL

Problem:
My problem is that DatabaseInput node is taking CPU usage and time to pool record and process .
Following error is coming when recording the flow record is processed but systems hangs.

BIP2087E: Integration node 'ESBBROK' was unable to process the internal configuration message.

The entire internal configuration message failed to be processed successfully.

Use the messages following this message to determine the reasons for the failure. If the problem cannot be resolved after reviewing these messages, contact your IBM Support center. Enabling service trace may help determine the cause of the failure.

BIP2039I: The configuration request was rejected because the integration server 'ESBBROK' stopped while processing the request.

While processing a configuration or deployment request on the integration server, the integration server process stopped.

Check the system logs for the reason that the integration server stopped, and correct any issues that are reported, such as the integration server process ran out of memory. When the issue is resolved, resubmit the configuration request.

Kindly resolve my issue Thanks in advance.

Regards,
Junaid
Back to top
View user's profile Send private message
timber
PostPosted: Tue Oct 22, 2019 1:08 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

Quote:
My problem is that DatabaseInput node is taking CPU usage and time to pool record and process
How do you know that? Does it happen with a single record, or only when the system is under heavy load?
Quote:
Following error is coming when recording the flow record is processed but systems hangs.

BIP2087E: Integration node 'ESBBROK' was unable to process the internal configuration message.
That error is not directly related to the DBInput node. BIP2087E is usually reported when the broker is unable to respond to a deployment or a stop/start request. But I agree that it sounds as if your broker or EG is locked up.
Quote:
Kindly resolve my issue
Only you can do that. We are happy to answer questions until you have the required information, though

You will probably need to provide some more detail about when the problem occurs - unless somebody else on this forum has seen the same issue.
Back to top
View user's profile Send private message
junaid
PostPosted: Tue Oct 22, 2019 1:41 am    Post subject: DatabaseInput Node is taking maximum CPU usage ? Reply with quote

Acolyte

Joined: 29 Nov 2018
Posts: 58

It is happening when single record is being pooled from db also on multiple.
Through iostat command on linux we are checking CPU utilization . In which %idle is normally around 99 when no record available in database for pooling. During polling from database it is utilizing and %idle goes from 99 to 50 or 40.
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Tue Oct 22, 2019 2:40 am    Post subject: Re: DatabaseInput Node is taking maximum CPU usage ? Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY

junaid wrote:
It is happening when single record is being pooled from db also on multiple.
Through iostat command on linux we are checking CPU utilization . In which %idle is normally around 99 when no record available in database for pooling. During polling from database it is utilizing and %idle goes from 99 to 50 or 40.
Check the execution plan of the DB Queries. Make sure you have the right indexes.
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
junaid
PostPosted: Tue Oct 22, 2019 2:57 am    Post subject: DatabaseInput Node is taking maximum CPU usage ? Reply with quote

Acolyte

Joined: 29 Nov 2018
Posts: 58

Default indexes on primary keys on both tables available ie. event table and other table . i did not got about this
Quote:
execution plan of the DB Queries.
Back to top
View user's profile Send private message
Vitor
PostPosted: Tue Oct 22, 2019 5:06 am    Post subject: Re: DatabaseInput Node is taking maximum CPU usage ? Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

junaid wrote:
i did not got about this
Quote:
execution plan of the DB Queries.


Use an explain (or equivalent) to determine exactly what the query is doing especially if it's using the indexes.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
junaid
PostPosted: Tue Oct 22, 2019 5:09 am    Post subject: DatabaseInput Node is taking maximum CPU usage ? Reply with quote

Acolyte

Joined: 29 Nov 2018
Posts: 58

Following is esql source which is used for db pooling
Code:

BROKER SCHEMA FTBrokerSchema
DECLARE ns1 NAMESPACE 'http://esbdb/esb';


/*
 * This DatabaseEvent module implements the ESQL code invoked from a DatabaseInput node to handle
 * events as they move through the states of New->Ready->Dispatched->Complete.
 *
 * NOTE: Events that result in unhandled exceptions in the message flow are moved to the Failed
 * state. "Unhandled" means either that the catch terminal was not wired, or that the catch
 * terminal threw an exception.  If an exception is unhandled, the transaction is rolled back.
 * If the exception is handled, even if it is handled on the catch terminal, the transaction is
 * committed.
 *
 * Events are moved to the Failed state after rollback (or after a number of retries, depending
 * on the settings on the Retry panel).  Events in the Failed state are propagated to the Failure
 * terminal.  If they are not handled on the failure terminal, they are discarded.  They are
 * still in the EventTable, and so are not lost.
 *
 * If you are using a database table as your event store, you can convert this template to deployable
 * code by replacing the substitution strings. Substitution strings in this module are enclosed by
 * < and > characters.
 * The following substitution strings are used:
 *   <MySchemaName>                 - the database schema name.
 *   <MyEventTableName>             - the database table used as your event store.
 *   <MyEventTablePrimaryKey>       - the primary key of the database table used as the event store.
 *   <MyStatusColumnName>           - the name of a column, if you update a column in the event table
 *                                    to indicate that the event has been processed.
 *                                    If you delete events from the event table after processing, you
 *                                    do not need <MyStatusColumnName>.
 *   <MyNewEventStatusValue>        - the value written to the status column when the event is first
 *                                    added. You need this only if you use <MyStatusColumnName>.
 *   <MyProcessedEventStatusValue>  - the value written to the status column after the event has been
 *                                    processed. You need this only if you use <MyStatusColumnName>.
 *   <MyApplicationTableName>       - the name of the table that includes the changed data to be processed
 *                                    by the DatabaseInput node.
 *   <MyEventTableForeignKey>       - the column in the event table that references the row in the application
 *                                    table containing the changed data to be processed by the DatabaseInput
 *                                    node. This is typically the primary key of the application table.
 *   <MyApplicationTablePrimaryKey> - the primary key of the database table used as the application table.
 * When you have finished editing the ESQL, set the data source and ESQL module properties on the basic tab
 * of this node.
 */
CREATE DATABASEEVENT MODULE FTReversal_Input

   /*
    * ReadEvents populates the NewEvents structure with event data read from the event table.
    * ReadEvents is called after all current events have been dispatched.
    * ReadEvents is called on a new transaction that is a separate transaction from the message
    * flow, and that runs before the message flow transaction.
    *
    * After ReadEvents completes:
    * - the current transaction is committed to ensure that any database locks obtained during
    *   ReadEvents are released.
    * - All events in NewEvents are moved to Ready state.  This means that they are ready to be
    *   dispatched.
    *
    * Parameters:
    * IN NewEvents REFERENCE.  This is a reference to a ROW. ReadEvents must create children of
    *                          this ROW for each event that is being processed.
    *                          Each child must include a 'Usr' field and a 'Key' field, with the
    *                          field names being case sensitive.
    */
    CREATE PROCEDURE ReadEvents( IN NewEvents REFERENCE )
    BEGIN
      DECLARE EXIT HANDLER FOR SQLSTATE LIKE 'D%'
      BEGIN
         RESIGNAL; /* pass the error back to the node */

         /* To choose to handle Database errors yourself, delete the RESIGNAL statement above
          * and uncomment the following procedure call */
--         CALL HandleDatabaseError('ReadEvents');
      END;

      /*
       * Here you select all unprocessed events from the event store.
       * You only read the events here; you delete them in EndEvent.
       * In general, it is not good practice to delete or update the events here because this
       * transaction will be committed even before the BuildMessage procedure is called.
       * Under certain circumstances, for example, if you do not need assured delivery of the
       * events, it is acceptable to update or delete the events here. This means that
       * the deletion or update is committed before the message flow processes the in-memory
       * copies of these events. If the broker, execution group, or message flow is stopped
       * or redeployed in the meantime, the in-memory copy is lost, and the events are never
       * processed by the flow.
       *
       * NOTE: You do not need to filter out events that are currently dispatched here.
       *       The framework ensures that events are not duplicated by comparing the 'Key' field
       *       to the 'Key' field of dispatched events.
       */

      --@!{ ******************** "ReadEvents" autogenerated code (1) ********************
      -- Please do not delete the start and end annotation comments if you want the tool to generate code
      -- for you. Any code that you add or change inside the start and end annotation comments will be lost
      -- in your next code generation.
      SET NewEvents.Event[] = SELECT REV_EVENT.EVENT_PKEY AS Key,
                              REV_EVENT.EVENT_PKEY AS Usr.EVENT_PKEY,
                              REV_EVENT.OBJECT AS Usr.OBJECT,
                              REV_EVENT.APP_PKEY AS Usr.APP_PKEY
                              FROM Database.ESB.REV_EVENT;
      --@!} ******************** "ReadEvents" autogenerated code (1) ********************
   END;


   /*
    * BuildMessage builds the message to be propagated to the flow.
    * Typically, you use the event data for the current dispatched event to look up data in
    * the application table, and copy that data into the message.
    * BuildMessage is called while some events are in the Ready state.
    * BuildMessage is called as part of the message flow transaction. This transaction also
    * involves EndEvent.
    *
    * After BuildMessage returns, the message is propagated to the message flow.
    *
    * Parameters:
    * IN DispatchedEvent REFERENCE.  A Reference to a ROW containing the event data for the
    *                                current dispatched event. This is a copy of one of the
    *                                events added to NewEvents by ReadEvents procedure.
    */
   CREATE PROCEDURE BuildMessage(IN DispatchedEvent REFERENCE)
   BEGIN
      DECLARE EXIT HANDLER FOR SQLSTATE LIKE 'D%'
      BEGIN
         RESIGNAL; /* pass the error back to the node */

         /* To choose to handle Database errors yourself, delete the RESIGNAL statement above
          * and uncomment the following procedure call */
--         CALL HandleDatabaseError('BuildMessage');
      END;
      /* Here you use the event data in the local environment to retrieve the application data. */

      --@!{ ******************** "BuildMessage" autogenerated code (1) ********************
      -- Please do not delete the start and end annotation comments if you want the tool to generate code
      -- for you. Any code that you add or change inside the start and end annotation comments will be lost
      -- in your next code generation.
      SET Root.DataObject.ns1:TXN_REV[] =
            SELECT TXN_REV.REV_PKEY,
               TXN_REV.TXN_TYPE,
               TXN_REV.SYSTEM,
               TXN_REV.RRN,
               TXN_REV.REV_REQ,
               TXN_REV.REV_DT,
               TXN_REV.STATUS,
               TXN_REV.COUNT
               FROM Database.ESB.TXN_REV
               WHERE TXN_REV.REV_PKEY = DispatchedEvent.Usr.APP_PKEY;
      --@!} ******************** "BuildMessage" autogenerated code (1) ********************
      RETURN;
   END;


   /*
    * EndEvent updates the event table to record the event as processed.
    * EndEvent is called after the message flow has processed the event.
    * EndEvent is called as part of the message flow transaction.  This transaction also involves
    * BuildMessage. The transaction will be committed when this procedure ends.
    *
    * Parameters:
    * IN DispatchedEvent REFERENCE.  A Reference to a ROW containing the event data for the current
    *                                dispatched event. This is a copy of one of the events added to
    *                                NewEvents by ReadEvents procedure.
    */
   CREATE PROCEDURE EndEvent(IN DispatchedEvent REFERENCE)
   BEGIN
      DECLARE EXIT HANDLER FOR SQLSTATE LIKE 'D%'
      BEGIN
         RESIGNAL; /* pass the error back to the node */

         /* To choose to handle Database errors yourself, delete the RESIGNAL statement above
          * and uncomment the following procedure call */
--         CALL HandleDatabaseError('EndEvent');

      END;
      /* Here you update the event table to ensure that this event is not processed again. */

      --@!{ ******************** "EndEvent" autogenerated code (1) ********************
      -- Please do not delete the start and end annotation comments if you want the tool to generate code
      -- for you. Any code that you add or change inside the start and end annotation comments will be lost
      -- in your next code generation.

      DELETE FROM Database.ESB.REV_EVENT
            WHERE REV_EVENT.EVENT_PKEY = DispatchedEvent.Usr.EVENT_PKEY;
      --@!} ******************** "EndEvent" autogenerated code (1) ********************
      RETURN;
   END;


   CREATE PROCEDURE HandleDatabaseError( IN FunctionName CHARACTER )
   BEGIN
      /* Throw a different exception; this could be changed. */
      DECLARE message CHARACTER 'Exception occured calling Database Input Node function: ' || FunctionName;
      THROW USER EXCEPTION VALUES( SQLCODE, SQLSTATE, SQLNATIVEERROR, SQLERRORTEXT, message );
   END;

END MODULE;



Last edited by junaid on Tue Oct 29, 2019 5:11 am; edited 1 time in total
Back to top
View user's profile Send private message
Vitor
PostPosted: Tue Oct 22, 2019 5:36 am    Post subject: Re: DatabaseInput Node is taking maximum CPU usage ? Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

junaid wrote:
Following is esql source which is used for db pooling


Very pretty.

Now use an explain (or equivalent) to determine exactly what the query is doing once it's submitted to the database especially if it's using the indexes.

Because what I think my worthy associate is getting at (and that I agree with) is a spike in resource usage such as you're describing sounds like a tablescan and/or a cartisian join.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
junaid
PostPosted: Tue Oct 29, 2019 5:08 am    Post subject: DatabaseInput Node is taking maximum CPU usage ? Reply with quote

Acolyte

Joined: 29 Nov 2018
Posts: 58

Issue has been resolved. Due to usage of 32 bit odbc driver on 64 bit machine whole problem was occurring. By changing libdb2.so with libdb2o.so in odbc.in file problem resolved. and every thing is working fine. Thanks all.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » DatabaseInput Node is taking maximum CPU usage ?
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.