Funny ESB?

3

Hi all,

I’ve used my ESB system – installed on SuSE9, connected to a 10.2 E.E. on the same machine – for a few
months now, so I thought it would be interesting to take a look at what’s
going on in my database schema created by my esb system (oraesb). This
led to some questions and raising eyebrows, I hope some of you esb-experts out there might have an answer for or a comment for that matter .....

* Is my system installed properly:
The installation itself was a true crime. Apparently there were some
issues in the ant tasks used for installing ESB and BPEL
systems, resulting in a total lack of connectors. I’ve managed to
correct these. Also my OHS was unable to start in SSL mode which
resulted in a missing WSM at the final stage of the installation. After
disabling SSL in opmn.xml WSM was able to get himself installed. The funny thing is that these issues only occur on a(ny) linux box and not on Windows.

I
further noticed that the oraesb schema created by running IRCA.zip installs
only tables, views, topic queues and 1 procedure (create_queue).
And all objects are installed in the SYSTEM tablespace: this is the way to see an angry DBA in the wild. Anyway, looking at the sql scripts in
${soaSuite_home}/integration/esb/sql/oracle there are far more stored
procedures defined. So, where are they? Not on my database anyway. I question you esb experts: is it normal or valid that these objects are not
installed or is my esb system faulty?

* No constraints or indices:
My
system is yet very small, so it is still performing good/fast. I
imagine that when the esb system is going further, performance and
locking becomes an issue due to lack of indices on foreign key columns
and primary/foreign constraints. Only the "Slide Tables" (see next paragraph) have constraints.

I also discovered by trial and error that it is possible to add the "missing" constraints and indexes manually. Note however that some constraints must be defined as deferred in order to keep the esb system working Smiley

* Only small part of schema used:
When
browsing through the tables of the oraesb user I notice that only a few
tables are filled with data. For example all "Slide Tables" (this is
the name given to these group tables in file
${soaSuite_home}/integration/esb/sql/oracle/wfeventc.sql) are empty. Is
this how it’s supposed to be? What kind of processes should enter data in these tables?
What is the use of the "Slide tables"? Are these for future use (soa suite 11g perhaps)? Besides the "Slide Tables" there appear two more subsystems: one for the system definitions (tables starting with wf_) and one for instance data (tables starting with esb_).

In order to monitor what’s being inserted, updated and deleted in the schema I’ve added a database trigger on all tables, except the tables starting with AQ$ as these are the advanced queuing meta tables. The database trigger calls a stored procedure that writes a log entry into an manually added logging or journal table. This gives me the opportunity to track all DML in the oraesb schema. Very handy!

* AQ-tables not being used:
My
esb system has five aq-queue tables (esb topics), but they are never
used! It is also not the case that the records are being deleted too fast too see upon successful processing. I can say this with certainty as all DML on these tables are being logged. I recall another thread on the OTN forum about these queue tables
growing enormous in size. I guess there must be a sort of switch
somewhere in the Enterprise Mgr tool or ESB Console to switch between jms-queue’s and aq-queue’s? Can someone please explain how to switch-on the aq-queue’s or point me to the proper
documentation. I must have overlooked this in the documentation myself.

Any comment or answers to the questions above are very appeciated!

Kind regards,
Happy new year,

H

Share.

About Author

3 Comments

  1. Hi Sandor,
    Thanks for your reply. Indeed, WF_ stands for WorkFlow. But looking at the data inside these tables it reflects the deployed systems, events and agents. By user tasks something like the BPEL user tasks come to my mind. The esb systems I deployed are mostly the samples and excersises from OTN and they are all automatic tasks.

    Is your system also lacking stored procedures, except the one I mentioned?

  2. Sandor Nieuwenhuijs on

    Not sure about the other tables’ use, but I know for sure the WF_ tables are used by the Human Workflow (“User Task”) part of the SOA Sutie.