Friday, April 22, 2011

Comparing Oracle DB objects from different schemas

Today, we have been working on a migration from webMethods 7.1.2 to 8.0 at a customer of ours. Those who are familiar with webMethods probably know that the objects in a database for one of the webMethods components (Integration Server, Trading Networks, My webMethods Server) may slightly start to differ between environments after some time, because of the installation, testing or roll back of various patches, service packs, etc...

This may lead to errors or differences in the behaviour of the webMethods DB configurator when migrating your development, test, production or other environments. Then it can be helpful to be able to quickly compare all the objects in your DB schemas, to make sure all your environments are in sync. There is no out-of-the-box way of comparing all objects in different Oracle databases or schemas. To solve this challenge, you can extract the DDL of the Oracle database objects creating an SQL script as follows:


1) First you need to determine all the objects in the Oracle schema you want to analyse and extract the DDL for it. This can be achieved by creating an "extractDDL.sql" SQL script, based on the query below (you can't extract the metadata for a LOB object). You should execute this query for each environment using Oracle's SQL Developer or some other SQL IDE with the Oracle user that owns the database schema:

select 'select dbms_metadata.get_ddl('''||OBJECT_TYPE||''','''||OBJECT_NAME||''') from dual;'
from user_objects
where object_type != 'LOB'
order by OBJECT_NAME asc


2) The list of statements returned by the above query can be copied into an SQL script, which you can execute using sqlplus and that will result into a list of DDL statements you can easily compare. The scripts looks something like this:

set heading off;
set echo off;
set pages 999;
set long 90000;
set trims on;
spool ddl_list_enviromentX.sql

select dbms_metadata.get_ddl('TABLE','ACTIVITYLOG') from dual;
select dbms_metadata.get_ddl('TABLE','ADDRESS') from dual;
select dbms_metadata.get_ddl('TABLE','ARCHIVESERVICE') from
...
select dbms_metadata.get_ddl('TABLE','TPA') from dual;
select dbms_metadata.get_ddl('TABLE','TPALOCK') from dual;

spool off;

The statements in italic are copied from the result of the SQL statement in step 1. Spool the results to a different file for each environment. Executing the script using sqlplus can be done as follows from the command line:

sqlplus <username>/<password>@<db tns name> @extractDDL.sql


3) After you have executed the script from step 2 in each environment you want to compare the Oracle DB objects for, you can use a text comparison tool such as WinMerge to quickly determine the differences between 2 or more result files. You may need to perform some small manipulations on the files produced in step 2 to come to perfectly comparable results, because the dbms_metadata function may return indexes on tables for example in a different order per environment, but all in all, this technique should allow you to fairly quickly determine the potential differences between 2 Oracle database schemas that should be identical.

Author: Kristof Lievens

Thursday, April 7, 2011

Better Message Exchange Patterns

With Web Services a number of Message Exchange Patterns (MEP) are defined
  • Request-response: the endpoint receives a message and sends a correlated message
  • One-way: the endpoint receives a message
  • Solicit-response: the endpoint sends a message and receives a correlated message
  • Notification: the endpoint sends a message
This goes even further with optional or robust variations of the same theme.  In practice, 99% of all web services are request-respone. 1% will be one-way, so POST a SOAP message and only get a 200 back.  The remaining patterns are not encountered in practice.

A better and more relevant categorization in our opinion are the service communication patterns as defined by SAP:
  • Query/Response pattern: inquiry regarding a state or subject matter, to which an answer is expected
  • Request/Confirmation pattern: requisition or requirement to execute an activity which involves the change of a state or subject matter
  • Information pattern: An informal, asynchronous message regarding a state or subject matter
  • Notification pattern: A formal, asynchronous notice regardign a state or subject matter
These patterns build upon the following transaction communication patterns:
  • Information: informal message regarding a state or subject matter
  • Notification: formal notice or advice regarding a state or subject matter
  • Query: inquiry regarding a state or subject matter, to which an answer is expected
  • Response: an answer to an inquiry
  • Request: a requisition or requirement to execute an activity which involves the change of a state or subject matter
  • Confirmation: an assurance with obligation about the (degree of) fulfillment of a request
Bottom line: there are better ways to categorize service patterns, independent of the actual technical implementation.


Authored by: Guy

    Tuesday, April 5, 2011

    Fun stuff: geek&poke

    For those who don't know geek&poke, very funny;
    We particularly like this one!

    Authored by: Guy

    Blog lift-off

    The I8C consultants are continuously learning and applying new technologies in the domain of "integration".  In this blog, we want to share some of that interesting stuff.

    Note: previous posts were copied from the personal blog of Guy