Tuesday, February 17, 2009

Cleanup AIA Cross Reference Table

Due to various reasons, the cross referenece could be filled with data that you do not expect. For example, you have duplicate values in your table.

In the very very first release of AIA, the cress-ref table did not have a unique/primary key. This could lead to duplicate records.

The following SQL script shows how to remove duplicate values from this table, it will save the 'double' records into a seperate table.

SET define OFF
SET echo OFF
SET termout ON

COLUMN XREF_TABLE_NAME FORMAT A20;
COLUMN XREF_COLUMN_NAME FORMAT A20;
COLUMN ROW_NUMBER FORMAT A20;
COLUMN VALUE FORMAT A30;
COLUMN IS_DELETED FORMAT A2;
COLUMN LAST_MODIFIED FORMAT A16;
COLUMN LAST_ACCESSED FORMAT A16;
COLUMN XREF_DATA_ROWID FORMAT A32;

PROMPT DROP OLD TABLE XREF_DATA_CLEANUP

DROP TABLE xref_data_cleanup;

PROMPT CREATE XREF_DATA_CLEANUP TABLE WITH DOUBLE VALUES;

CREATE TABLE XREF_DATA_CLEANUP AS
SELECT
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, ROW_NUMBER
, VALUE
, IS_DELETED
, LAST_MODIFIED
, LAST_ACCESSED
, ROWID XREF_DATA_ROWID
FROM XREF_DATA
WHERE EXISTS
(
SELECT ''
FROM XREF_DATA X
WHERE X.VALUE = XREF_DATA.VALUE
AND X.XREF_TABLE_NAME = XREF_DATA.XREF_TABLE_NAME
HAVING COUNT(*) > 1
GROUP BY X.VALUE
);

PROMPT COUNT DOUBLE VALUES RECORDS

SELECT
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, VALUE
, IS_DELETED
, COUNT(1)
FROM XREF_DATA
HAVING COUNT(1) > 1
GROUP BY
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, VALUE
, IS_DELETED;

PROMPT LIST DOUBLE VALUE RECORDS

SELECT
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, ROW_NUMBER
, VALUE
, IS_DELETED
, TO_CHAR((TO_DATE('01/01/1970','mm/dd/yyyy')
+ LAST_MODIFIED/86400000), 'dd-mm-yyyy hh24:mi:ss')
LAST_MODIFIED
, TO_CHAR((TO_DATE'01/01/1970','mm/dd/yyyy')
+ LAST_ACCESSED/86400000), 'dd-mm-yyyy hh24:mi:ss')
LAST_ACCESSED
, XREF_DATA_ROWID
FROM XREF_DATA_CLEANUP
ORDER BY
ROW_NUMBER
, XREF_TABLE_NAME
, XREF_COLUMN_NAME;

PROMPT LIST UNDOUBLED VALUE RECORDS

SELECT
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, ROW_NUMBER
, VALUE
, IS_DELETED
, TO_CHAR((TO_DATE('01/01/1970','mm/dd/yyyy')
+ LAST_MODIFIED/86400000), 'dd-mm-yyyy hh24:mi:ss')
LAST_MODIFIED
, TO_CHAR((TO_DATE('01/01/1970','mm/dd/yyyy')
+ LAST_ACCESSED/86400000), 'dd-mm-yyyy hh24:mi:ss')
LAST_ACCESSED
FROM XREF_DATA X
WHERE X.ROWID > ANY
(
SELECT XX.ROWID
FROM XREF_DATA XX
WHERE XX.VALUE = X.VALUE
AND XX.XREF_TABLE_NAME = X.XREF_TABLE_NAME
)
ORDER BY
ROW_NUMBER
, XREF_TABLE_NAME
, XREF_COLUMN_NAME;

PROMPT DELETE DOUBLE RECORDS IN ORGINALE XREF-DATA TABLE.

DELETE
FROM xref_data x
WHERE X.ROWID > ANY
(
SELECT XX.ROWID
FROM XREF_DATA XX
WHERE XX.VALUE = X.VALUE
AND XX.XREF_TABLE_NAME = X.XREF_TABLE_NAME
);

PROMPT CHECK IF DUPLICATED VALUES ARE CORRECT DELETED.
PROMPT THE OUTCOME SHOULD BE 0 ROWS SELECTED.

SELECT XREF_TABLE_NAME
, XREF_COLUMN_NAME
, VALUE
, IS_DELETED
, COUNT(1)
FROM AIA.XREF_DATA
HAVING COUNT(1) > 1
GROUP BY
XREF_TABLE_NAME
, XREF_COLUMN_NAME
VALUE
IS_DELETED;

PROMPT CHECK THAT NO RECORDS ARE LOSSED.
PROMPT CHECK THAT THE AMOUNT OF ROWS ARE EQUAL
PROMPT AS THE RESULTS OF PROMTS COUNT DOUBLE VALUES RECORDS.

SELECT XRF.*
FROM XREF_DATA XRF
, (
SELECT
XREF_TABLE_NAME
, XREF_COLUMN_NAME
, VALUE
, IS_DELETED
, COUNT(1)
FROM XREF_DATA_CLEANUP
HAVING COUNT(1) > 1
GROUP BY XREF_TABLE_NAME
, XREF_COLUMN_NAME
,VALUE
IS_DELETED
) DUB
WHERE XRF.XREF_TABLE_NAME = DUB.XREF_TABLE_NAME
AND XRF.XREF_COLUMN_NAME = DUB.XREF_COLUMN_NAME
AND XRF.VALUE = DUB.VALUE;

Monday, February 16, 2009

Oracle Cross Reference Table

In Oracle ESB the crosse reference table was introduced to create domain value maps.

Within Oracle AIA this mechnism is one of the technical foundation on which AIA runs.

The documentaion was limited on this functionality. Some core functions used in stylesheets were explained. Popular cross-reference functions are:
  • xref:populateXRefRow() - Insert a record in the XRef table
  • xref:markForDelete() - Delete a record from the XRef table
Note: The xref:markForDelete() will update the delete-column in the XRef-table. In future releases, this function could change that it will physicly delete the table instead of update.

Since Oracle SOA Suite 10.1.3.4, the documentati
on has improved dramaticly. Detailes description and examples are now documented. It can be found here:

http://download.oracle.com/docs/cd/E12524_01/doc.1013/e12638/esb_xref.htm

From conceptualpoint of view and almost technical it can be used a reference documentation in olde versions 10.1.3.3 and AIA 2.0.x.

Note that there are some challenges when you use the cross-reference table.

Check that the table has is optimzed for perfomance. That is has to correct indexes and contraints. The latest patches of the Oracle SOA Suite, applies these indexes and contraints.

One question on this cross-reference table is the date format. The table contains two columns that specifies the date since 1 january 1970. You can obtain the correct format as follows:


select
xref_table_name
, xref_column_name
, row_number
, value
, is_deleted
, to_char((to_date('01/01/1970','mm/dd/yyyy')
+ last_modified/86400000), 'dd-mm-yyyy hh24:mi:ss')

, to_char((to_date('01/01/1970','mm/dd/yyyy')
+ last_accessed/86400000), 'dd-mm-yyyy hh24:mi:ss')

from xref_data;

Wednesday, February 11, 2009

Oracle AIA 2.3

Now Oracle AIA is availble for a 'long' period. I see more and more working coming from customers. It looks like that Oracle AIA is on the right time on the right place. Recession is on, and customers are look to reduce their cost, but do want to innovate their buiness withour losing revenue.

Customers are seeking to solutions to use their existing (legacy) system for a lnger period, without investing huge amount of money to build new systems.

A good choice is to use Oracle AIA Foundation with or without a Process Integration Pack (PIP). It is relative a small investment to get an out-of-the-box business integration flow with two or more systems (Siebel, PeopleSoft, EBS, BRM/Protal, JD Edwards).

Last month Oracle AIA 2.3 was introduced, so what brings this. If I compare it with the versions I have worked with (2.0), it is a huge difference; complete setup/installation of AIA Foundation or select a PIP. While in previous releases the PIP's were available in a specific version.

The major difference is that the Enterprise Business Objects (EBO) have been extened or added.

The current verions of Oracle AIA is version 2.3. Oracle AIA Foundation 2.3 is certified on SOA Suite 10.1.3.4. On WLS the certification is in progress, while Oracle AIA Foundation 2.2.1 is certified on SOA Suite 10.1.3.3 and WLS 9.2.

A major improvements the Business Service Registry; build with ADF; but a complete different; improved layout; comparing to the previous versions.

Documentation of the business flows are written in the business process modeler (BPM) of the Oracle BPA Suite.

More features;

You are able to upgrade from previous version.

Generate ABCS BPEL processes.

Improved Error handling framework, with resubmission after errors.

General availble on major plaforms; Linux ; (soon on Window; Solaris; AIX; HP-UX.)

More info? Go to http://edelivery.oracle.com

Monday, February 02, 2009

Oracle Application Integration Framework (AIA) on OSB

As I early wrote in an article how to migrate ESB services to OSB service, Oracle has written an whitepaper on how to run AIA in combination with OSB.

This article describes the approach to be taken if you must used the OSB as service bus in an AIA environment.

Technical it is possible to move the ESB service to OSB, based on AIA. But their are some restrictions or pitfalls to take inton using AIA on OSB.


Stylesheets
The stylesheets in AIA are relative complex, I am not an XSLT guru, but sometimes it is hard to read/interpret the XSLT. They could work in OSB, but you do not have a nice diagrammer as in JDeveloper. You must use the Eclipse source editor.

Oracle functions
In Oracle SOA Suite and with AIA on top of it, Oracle has added some extended functions to the XSLT/XPATH functionality. These functions are available in BPEL/ESB, but when you use OSB, these functions are not available. You must add this functions, via a JAR file to the OSB classpath, but then you must make sure these functions work on OSB. OSB is running on Java 1.6, while Oracle SOA Suite + AIA is running on Java 1.5

XSD Schema
The AIA XSD schema's are installed in the AIA installation directory of the Oracle Application Server. If you use OSB, you should take care to copy the XSD/WSDL definitions to the OSB environment. You have to maintain two XSD/WSDL directory trees or, if possible, make a reference to the central AIA directory.

Oracle Support
Creating your own OSB services, instead of using the ESB ones, has impact on support of the AIA stack. IMHO you do not have AIA support on your own developed services, you can fall back on the normal Oracle OSB support.

The link to the Oracle AIA/OSB white paper:

http://www.oracle.com/applications/aia-foundation-pack-on-service-bus-white-paper.pdf

Oracle AIA with OSB will be available/planned in SOA 11gR2.