MB数据库输入节点

数据库输入节点 DatabaseInput Sample例子位置：WebSphere Message Broker Version Product overview Samples Application samples该例子是MB自带的例子，名字叫DatabaseInput Sample，下面简单说测试过程和测试过程中遇到的问题以及解决方法。具体详细信息参考帮助文档。例子简要信息：对数据库的监控主要是通过监测事件表，这个事件表是用来存储对业务表的各种操作，表字段可以随意，也可以按照例子说的建表，但是有几个必要的字段必须有。这个事件是怎么存进去的呢？是通过触发器，对要监控的表建立相应触发器，记录对表的相应操作。DatabaseInput节点的作用就是监控事件表，可以设置监控频率，默认是5。运行例子的时候首先是建数据库，然后是建表并建立触发器，SQL例子都有，拷贝出来就可以直接用，注意DB2CMD切到脚本的文件夹。建好库然后是导入例子，导入的例子并不能直接使用，需要做修改（这个问题折腾了一天）。需要把注释的语句都拿出来具体有三处。代码的位置是DatabaseInput.esql中灰色的部分，这部分的开头是解释，没有任何意义，我把需要拿出来的几句话贴出来ReadEvents方法中的：SET NewEvents.Event = SELECT DBINPUT_EVENTS.EVENT_ID AS Key, DBINPUT_EVENTS.EVENT_ID AS Usr.EVENT_ID, DBINPUT_EVENTS.OBJECT_VERB AS Usr.OBJECT_VERB, DBINPUT_EVENTS.OBJECT_KEY AS Usr.OBJECT_KEY FROM Database.DBINPUT_EVENTS;BuildMessage方法中的：SET Root.DataObject.ns:DBINPUT_CUSTOMER =SELECT DBINPUT_CUSTOMER.PKEY,DBINPUT_CUSTOMER.FIRSTNAME,DBINPUT_CUSTOMER.LASTNAME,DBINPUT_CUSTOMER.CCODEFROM Database.DBINPUT_CUSTOMERWHERE DBINPUT_CUSTOMER.PKEY = DispatchedEvent.Usr.OBJECT_KEY;EndEvent方法中的：DELETE FROM Database.DBINPUT_EVENTSWHERE DBINPUT_EVENTS.EVENT_ID = DispatchedEvent.Usr.EVENT_ID;下面是对DatabaseInput.esql的翻译，就是把这个翻译了一般才找到了例子不能运行的原因。如果看着不舒服，可以拷回toolkit中看。DECLARE ns1 NAMESPACE http:/dbinput/db2admin;/* * 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. * 此数据库事件模块采用调用自数据库输入节点的ESQL代码，用于处理新建-就绪-分配-完成等过程中发生的事件。 * 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 * characters. * 如果你使用一张数据库表来存储你的事件，你可以用可替代的东西将这些模版转换成成可扩展的代码。 * The following substitution strings are used: * - the database schema name. * - the database table used as your event store. * - the primary key of the database table used as the event store. * - 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 . * - the value written to the status column when the event is first * added. You need this only if you use . * - the value written to the status column after the event has been * processed. You need this only if you use . * - the name of the table that includes the changed data to be processed * by the DatabaseInput node. * - 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. * - 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 DatabaseInput_Retrieve_updates_from_database1/* * 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. * ReadEvents从事件表中读取数据，以便组成新事件结构。 * 当所有当前事件被分发后会调用ReadEvents。 * ReadEvents是在一个与消息流事务分开的事务上被调用的，这个事务是在消息流事务之上的。 * * * 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. * 在ReadEvents完成之后： * -在ReadEvents释放期间，当前事务会被提交以确保获得需要的数据库锁， * - 所有的新事件会被置为就绪状态，这意味着他们已经准备好被分发。 * 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. */ CREATE PROCEDURE ReadEvents( IN NewEvents REFERENCE ) BEGINDECLARE EXIT HANDLER FOR SQLSTATE LIKE D%BEGINRESIGNAL; /* 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. * * 这里你可以从存储事件的地方获得所有未被处理的事件 * 你只能在这个地方读取事件，不过可以在EndEvent中删除事件。 * 总之，不提倡在这个地方删除或更新事件，因为事务会被提交，即使BuildMessage被调用了 * 但是在某些情况下，例如，如果你不用保证事件的提交，你可以在这个地方更新或删除事件。 * 这意味着删除或更新操作会在消息流处理内存中备份的事件之前被提交。如果代理、执行组或消息流在这期间 * 被停掉了或重新发布了，那么在内存中的备份会丢失掉，这就意味着这个事件将不会被这个流处理。 * 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 DBINPUT_EVENTS.EVENT_ID AS Key, DBINPUT_EVENTS.EVENT_ID AS Usr.EVENT_ID, DBINPUT_EVENTS.OBJECT_VERB AS Usr.OBJECT_VERB, DBINPUT_EVENTS.OBJECT_KEY AS Usr.OBJECT_KEY FROM Database.DBINPUT_EVENTS;-! * 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. * BuildMessage构建传递到流的消息。 * 典型的，你可以利用当前正在分发的事件的事件数据来查看应用表中的数据，并且可以将这些数据拷贝到信息中。 * BuildMessage会在一些事件被置为就绪状态时被调用。 * BuildMessage是作为消息流事务的一部分被调用的，这个事务牵涉到EndEvent。 * * After BuildMessage returns, the message is propagated to the message flow. * 在BuildMessage返回后，消息会被传递到消息流中 * 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)BEGINDECLARE EXIT HANDLER FOR SQLSTATE LIKE D%BEGINRESIGNAL; /* 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.ns:DBINPUT_CUSTOMER =SELECT DBINPUT_CUSTOMER.PKEY,DBINPUT_CUSTOMER.FIRSTNAME,DBINPUT_CUSTOMER.LASTNAME,DBINPUT_CUSTOMER.CCODEFROM Database.DBINPUT_CUSTOMERWHERE DBINPUT_CUSTOMER.PKEY = DispatchedEvent.Usr.OBJECT_KEY;-! * 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. * EndEvent用来更新事件表，将事件更新为已处理状态。 * EndEvent会在消息流处理完成事件后被调用。 * EndEvent是作为消息流事务的一部分被调用的。这是事务涉及到BuildMessage。事务会在这个处理之后被提交。 * 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)BEGINDECLARE EXIT HANDLER FOR SQLSTATE LIKE D%BEGINRESIGNAL; /* pass the error back to the node */* To choose to handle Database errors yourself, delete the RESIGNAL statemen