Friday, February 16, 2007

Delete bulk BPEL instances

It can take a while to purge the BPEL instances from the dehydration store. It can be done faster, if you would like. The following step is usefull in a developement environement. First stop the BPEL instance via Enterprise manager.

BTW: If you do not want a bulk delete, but instance by instance for a particular date and domain and revision, look here.

Then in sqlplus orabpel/password (note: 10.1.3):
truncate table cube_instance;
truncate table cube_scope;
truncate table work_item;
truncate table wi_exception;
truncate table document_ci_ref;
truncate table document_dlv_msg_ref;
truncate table scope_activation;
truncate table dlv_subscription;
truncate table audit_trail;
truncate table audit_details;
truncate table sync_trail;
truncate table sync_store;
truncate table dlv_message;
truncate table invoke_message;
truncate table ci_indexes;

alter table cube_instance deallocate unused;
alter table cube_scope deallocate unused;
alter table work_item deallocate unused;
alter table wi_exception deallocate unused;
alter table document_ci_ref deallocate unused;
alter table document_dlv_msg_ref deallocate unused;
alter table scope_activation deallocate unused;
alter table dlv_subscription deallocate unused;
alter table audit_trail deallocate unused;
alter table audit_details deallocate unused;
alter table sync_trail deallocate unused;
alter table sync_store deallocate unused;
alter table dlv_message deallocate unused;
alter table invoke_message deallocate unused;
alter table ci_indexes deallocate unused;

alter table cube_scope enable row movement;
alter table cube_scope shrink space compact;
alter table cube_scope shrink space;
alter table cube_scope disable row movement;

alter table cube_instance enable row movement;
alter table cube_instance shrink space compact;
alter table cube_instance shrink space;
alter table cube_instance disable row movement;

exec dbms_utility.analyze_schema('ORABPEL', 'Compute');
Then in sqlplus orabpel/password (note: 10.1.2.0.2)
truncate table cube_instance;
truncate table cube_scope;
truncate table work_item;
truncate table wi_exception;
truncate table document;
truncate table scope_activation;
truncate table dlv_subscription;
truncate table audit_trail;
truncate table audit_details;
truncate table sync_trail;
truncate table sync_store;
truncate table tx_inferior;
truncate table tx_superior;
truncate table dlv_message;
truncate table dlv_message_bin;
truncate table invoke_message;
truncate table invoke_message_bin;

alter table cube_instance deallocate unused;
alter table cube_scope deallocate unused;
alter table work_item deallocate unused;
alter table wi_exception deallocate unused;
alter table document deallocate unused;
alter table scope_activation deallocate unused;
alter table dlv_subscription deallocate unused;
alter table audit_trail deallocate unused;
alter table audit_details deallocate unused;
alter table sync_trail deallocate unused;
alter table sync_store deallocate unused;
alter table tx_inferior deallocate unused;
alter table tx_superior deallocate unused;
alter table dlv_message deallocate unused;
alter table dlv_message_bin deallocate unused;
alter table invoke_message deallocate unused;
alter table invoke_message_bin deallocate unused;

alter table cube_scope enable row movement;
alter table cube_scope shrink space compact;
alter table cube_scope shrink space;
alter table cube_scope disable row movement;

alter table cube_instance enable row movement;
alter table cube_instance shrink space compact;
alter table cube_instance shrink space;
alter table cube_instance disable row movement;

exec dbms_utility.analyze_schema('ORABPEL', 'Compute');

Characterset conversion

Using the SOA Suite with the DBAdapter, even for Oracle EBunisses Suite (EBS), you must be ware of your character set you are using. The application server is running in Java and using XML, so it uses by default UTF-8.

If your database is configured not for UTF-8, it will convert the incoming payload to the character set of the database. Nine-out-of-ten it will run without any errors. But using special characters you could get errors when you insert data in the database.

This issue can be solved by using a PL/SQL function to convert the data from on characterset to another one.
PL/SQL using implicit the database character set. Using 'CONVERT()' function.
  • CONVERT(string, to-characterset, from-characterset);
Example:

SELECT CONVERT('Ä Ê Í Ó Ø A B C D E ', 'UTF8', 'WE8ISO8859P1') FROM DUAL;

Click here for more information in this function:

Wednesday, February 07, 2007

Migrate a monolithic application to SOA

More and more IT and business departments see the advantages of using SOA to make the business more efficient, adding more flexibility in their organisation, resulting in lower cost, thus making more profit. This can be in real money or in improvement of the employability or more satisfaction to the customer.

But most of the companies have already build and using their back-office systems. They run their business for several years and using these back-office applications. These applications are build or have evoluted into a monolithic application using one or more data sources and complex datamodels. These applications are build using technology that was state of the art during these days. But now it is hard to add SOA to these environments.

The Oracle SOA suite can help you to add SOA functionality to your back-office applications to start to migrate the monolithic application into an SOA application.

Type of monolithic applications
If we take a look in the Oracle world, there are various customers using their current applications and are happy with that. From business perspective they periodically add functionality to the application. But how longer they stay on their technology platform, it gets harder to implement additional functionality. The market is continuously moving forward, from business point as well as from technology point.

We define the following customers:
  • Oracle Forms and Oracle Reports.
  • PL/SQL based applications.
  • Applications with high interfacing.
  • Applications based on batch processing.
Oracle Forms and Oracle Reports.
Existing applications based on Forms and Reports can be enabled for SOA. There are some technical implications on this. Forms, and Reports support Java client side code. But the version of the Forms/Reports determines which Java can be accessed. An upgrade of the Forms/Reports environment can be a step in the migration to SOA. If the customer is already using Web based Forms/Reports, it will benefit of this, the migration can be carried more efficiently.

PL/SQL based applications.
Applications based on PL/SQL are completely running in the Oracle Database. To enable these applications for SOA, it depends of the version of the database the customer is using. The higher the version of the Oracle Database, the most benefit it will get to implement SOA. Customers on Oracle Database 9.0 or lower should think about migrating to a higher version.


Applications with high interfacing.
More and more applications are implemented in organizations for different reasons. By default it will have impact on the existing applications, because the application will communicate to other applications. This is implemented in various ways:
  1. Custom; end-user is typing data into the system.
  2. File based; files are read or written to a (remote)file system.
  3. Database schema; the application is reading/writing data of various schema's.
  4. Databases; the application is reading/writing data of various databases (via database links).
  5. Queues; the application is using queues to communication with the outside world.
This type of application will benefit most of SOA in short-term and is easy to implement. It will create a win-win situation for the IT-department as well the Business department.

Applications based on batch processing.
Applications are available to the end-users but they are using batch-processing to execute the business rules. Mostly this is done overnight. This is typically grown historically because the software and/or hardware were not able to perform these rules directly. Implementing SOA for these type of applications generates benefit to the end-user. The whole process is control and can be seen 'live'.

Project Approach
To migrate the monolithic application into a SOA environment we define some main points:
  • Use an evolution approach, no "big-bang".
  • Reuse what is good in monolithic environment.
  • The monolithic environment will not disappear (yet).
To prepare the organization for being SOA enabled execute the following steps:
  • Organize a meeting with IT and Business departments together for a SOA awerness session. What are the benefits for the organizations, the pitfalls, the quick-wins on short term and set the long-term approach. The result is a document/presentation how the SOA environment fits in the organization and how the organization can benefit it from, on short-term as well on long-term.
  • Together with IT start an analysis phase for creating the new technical architecture for the upcoming SOA environment. This architecture must fit in the current environment of the monolithic applications to enable the evolution approach. The result of this phase is a technical design of the SOA environment that fits in the current environment. The design describes the hardware components as well as the user components.
  • Based on the first step, the quick-wins of SOA for the organization is implemented. This means that the Oracle SOA suite is begin implemented. The quick-wins are analyzed and designed and implemented in the SOA environment.
  • Parallel to, or after the quick-win step a start is made with the analysis of the long-term benefits of SOA. This phase is crucial for SOA and the monolithic applications. Decisions are made which of the components/functionality/code is being reused or redesigned and when this is done. The result of this steps are a project plan, analysis/design document of the new SOA environment.
  • The last step is to implement the new SOA functionality based on on the project plan and the analysis documents.