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 » Error Handling in Database Input Node

Post new topic  Reply to topic
 Error Handling in Database Input Node « View previous topic :: View next topic » 
Author Message
PankajKr
PostPosted: Mon Jun 22, 2020 3:13 am    Post subject: Error Handling in Database Input Node Reply with quote

Newbie

Joined: 06 Nov 2019
Posts: 4

Hi,

I am using IBM ACE v11 and have following query in Database Input node but then I have a requirement where in case of Error in flow I want DB Input node to retry only 5 times and in case of failure it should stop processing said record after updating Event status Flag to 'E' instead of 'Y' in case of success scenario.
So can please anyone suggest changes need to be done in DB input query for same as said query runs perfect when flow is in success and update flag to 'Y'

DB Input query:
Code:

DECLARE ns1 NAMESPACE 'http://oradevdb/appconnect';


/*
 * 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 Sync_ExchangeRateMaster_Database_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 F59T5040.Q1TRNK AS Key,
                              F59T5040.Q1YINT AS Usr.Q1YINT,
                              F59T5040.Q1URAT AS Usr.Q1URAT,
                              F59T5040.Q1URRF AS Usr.Q1URRF,
                              F59T5040.Q1PNID AS Usr.Q1PNID,
                              F59T5040.Q1USER1 AS Usr.Q1USER1,
                              F59T5040.Q1UPDTDATE AS Usr.Q1UPDTDATE,
                              F59T5040.Q1USER AS Usr.Q1USER,
                              F59T5040.Q1JOBN AS Usr.Q1JOBN,
                              F59T5040.Q1DL011 AS Usr.Q1DL011,
                              F59T5040.Q1YDIR AS Usr.Q1YDIR,
                              F59T5040.Q1GDSC AS Usr.Q1GDSC,
                              F59T5040.Q1EDDT AS Usr.Q1EDDT,
                              F59T5040.Q1PID AS Usr.Q1PID,
                              F59T5040.Q1TRNK AS Usr.Q1TRNK,
                              F59T5040.Q1UPMJ AS Usr.Q1UPMJ,
                              F59T5040.Q1URCD AS Usr.Q1URCD,
                              F59T5040.Q1URAB AS Usr.Q1URAB,
                              F59T5040.Q1SBD2 AS Usr.Q1SBD2,
                              F59T5040.Q1SBD1 AS Usr.Q1SBD1,
                              F59T5040.Q1MCU AS Usr.Q1MCU,
                              F59T5040.Q1TCG AS Usr.Q1TCG,
                              F59T5040.Q1AA10 AS Usr.Q1AA10,
                              F59T5040.Q1PID1 AS Usr.Q1PID1,
                              F59T5040.Q1YTHP AS Usr.Q1YTHP,
                              F59T5040.Q1EDSP AS Usr.Q1EDSP,
                              F59T5040.Q1TDAY AS Usr.Q1TDAY,
                              F59T5040.Q1URDT AS Usr.Q1URDT
                              FROM Database.appconnect.F59T5040
                              WHERE F59T5040.Q1EDSP = 'N';
      --@!} ******************** "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:F59O5040[] =
            SELECT F59O5040.SYTRNK,
               F59O5040.SYEDUS,
               F59O5040.SYEDBT,
               F59O5040.SYEDTN,
               F59O5040.SYEDLN,
               F59O5040.SYEDCT,
               F59O5040.SYTYTN,
               F59O5040.SYEDFT,
               F59O5040.SYEDDT,
               F59O5040.SYDRIN,
               F59O5040.SYEDDL,
               F59O5040.SYEDSP,
               F59O5040.SYPNID,
               F59O5040.SYTNAC,
               F59O5040.SYCRCD,
               F59O5040.SYCRDC,
               F59O5040.SYCRR,
               F59O5040.SYEFT,
               F59O5040.SYURCD,
               F59O5040.SYURDT,
               F59O5040.SYURAT,
               F59O5040.SYURAB,
               F59O5040.SYURRF,
               F59O5040.SYTORG,
               F59O5040.SYDOC,
               F59O5040.SYDCT,
               F59O5040.SYUSER,
               F59O5040.SYPID,
               F59O5040.SYJOBN,
               F59O5040.SYUPMJ,
               F59O5040.SYTDAY,
               F59O5040.SYSBD1,
               F59O5040.SYSBD2,
               F59O5040.SYUSER1,
               F59O5040.SYPID1,
               F59O5040.SYUPDTDATE,
               F59O5040.SYTCG,
               F59O5040.SYDL011,
               F59O5040.SYGDSC
               FROM Database.appconnect.F59O5040
               WHERE F59O5040.SYTRNK = DispatchedEvent.Usr.Q1TRNK;
      --@!} ******************** "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.
      UPDATE Database.appconnect.F59T5040
            SET Q1EDSP = 'Y'
            WHERE F59T5040.Q1TRNK = DispatchedEvent.Usr.Q1TRNK;
      --@!} ******************** "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;
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Wed Jul 08, 2020 3:29 am    Post subject: Reply with quote

Knight

Joined: 17 Jul 2017
Posts: 516
Location: UK

hi...have a look at 'Retry' properties of the DB input node - 'Retry threshold' and the retry intervals. If the flow has failed at the DB input node itself then it would mostly like be due to Database config/connection issues in which case it is impractical for the flow to expect to make any database updates.

Even in the scenario of the failure occurring at downstream nodes why would you want to mark the event record as failed as the failure as such is not directly related to the record as such and ideally you would want the same record to be picked up in the next/retry attempt?
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 » Error Handling in Database Input Node
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.