True it's been a mystery lurking on our Data Integrator server for some time.

But more than perplexing, it was really annoying.  As part of the on-going to process to better understand, control and hopefully make use of the web based components installed on a Data Integrator server, I bumped into a vexing, albeit, innocuous security related issue.  As mentioned in the previous post, the Data Integrator installation utilizes Tomcat for web-based admin and management features.  And in the course of exploring the web serving components of that environment, occasionally a login dialog boxed would pop up requesting a user ID and password to proceed.  At first, I didn't even pay attention to exactly what the URL (URI) was that would cause this "oddity". 

Everything important related to the operation of Data Integrator has been working fine and accessing its web admin via the appropriate URL did not cause the login pop-up to appear so there's been no great urgency to find the cause of the mysterious dialog box.  In addition, the DI installation does not use the standard HTTP port of 80 to bring up the main admin page, instead using 28080.  While experimenting with several different Data Integrator URLs, all including the custom port reference, to access different DI admin features, I tried just the server name without any port reference and nothing came up.  Just the standard page not found message.

Knowing that DI was being served via Tomcat as well as the fact that Tomcat use port 8080 as a default , I tried a URL that included the server name and that port such as http://server:8080.  And viola, the mysterious login dialog box showed itself.  Gotcha!  Now we know what ever is throwing the login request is listening on 8080 and it definitely doesn't look like its Tomcat.

But what have I really found out?  What the heck is throwing up this login box?  Data Integrator has its own log in page and it works fine sans the dialog box when accessed through proper URL including the custom port assignment.  Time to get a clue.  And there was one right on the login box: "XDB" was displayed above the text entry fields in the dialog.  But again, now what?  Hmmm... XDB...  XDB...  What's that coming from?  And it looks familiar too.  Wait a minute, I've seen XDB.  It's schema in an Oracle 9iR2 database.

Not only is it a schema in Oracle 9 and higher, it's part of of entire XML feature set now standard with Oracle known as Oracle XDB.  Oracle XDB provides for built-in XML capabilities directly integrated within the Oracle database environment.  After researching I decided that perhaps the dialog box wants the credentials associated with XDB schema (I realize when properly configured other login credentials would likely work as well, but no part of XDB in our installation has been specifically configured at this point other than what goes in as default with database installation).  So I did an ALTER USER on XDB  to unlock the account and reset the password to give it try.

Thinking I've got it figured out and expecting a pleasant outcome I try it.  No dice.  What? This can't be right.  Must be a mistake; I'll try again.  Nope.  The dialog just keeps coming back with every attempt eventually resulting in an "unauthorized" page.  Son of gun...  More reading and a lot of Google uncovers that this is not a particularly unique situation, but the answers as to the cause and the solution are elusive.

Our Data Integrator, like many others, uses Oracle as its back-end repository (not familiar myself if other DBMS can be used for the repository but I suspect so).  And Oracle XDB is part of the Oracle installation by default.  But to further mask the cause in our particular situation, we have another separate Oracle instance running on the same box along with the Oracle instance used by Data Integrator.  It was in this other instance in which I originally changed the XDB login and it turns out this was not the instance listening on 8080, thus no access.

When I opened the XDB login on the Oracle instance used as the repository for Data Integrator it worked!  Up came a directory listing in my browser representing the XML structure residing in and served by the Oracle database serving as the Data Integrator repository.  In finalizing the solution I wanted to now switch from using the Oracle XDB feature in our DI repository to using XDB via the other Oracle database residing on the same box so as not "disturb" the DI repository database.

In the course of researching this situation, I found several options to accomplish this switch.  These included such tactics as disabling the Oracle XDB features completely or changing ports for listening.  What I ended up  doing was leaving everything associated with the Data Integrator repository database as is including XDB enabled on 8080.  I then reconfigured the other Oracle database to listen for the purposes of XDB on 8081 instead (I alse changed the default FTP port from 21 to 2111).   I found the details of these changes on the Internet will put up some links in a follow-up post.

The good news is all seems to be working under our control and now we can put Oracle XDB through a few paces without impacting the Data Integrator installation on the same box.  Mystery solved.  At least until the next one!

After successfully converting dozens of tables in an Oracle data warehouse from uncompressed to compressed without so much as a single glitch, several problems cropped up last week.

We are also using BusinessObjects (now SAP) Data Integrator for ELT operations and have been for over 2 1/2 years also without much issue generally speaking.  The tables in question were just recently converted to compressed and then we started seeing a relatively small number of problems with our Data Integrator loads.

The problem presented itself when we truncated the compressed tables and then subsequently reloaded them.  We then seemed to be having problems with duplicated data.  No errors were given with truncating the tables in question.  It wouldn't seem possible that we'd get a problem with duplication after truncating a table, but nothing is taken for granted.  There was apparently no duplication in the source data used to popuate these tables.

When we switched the tables involved that we had changed back to the original attribute of uncompressed, the problem immediately went away.  And though we have converted dozens of tables to use compression as mentioned, all of which part of Data Integrator load jobs, we've only seen these issues involving a few converted tables.

We still have more investigation to do on this and we're not entirely certain it's related to the use of compression, but it sure seems more than just coincidence.  Also I don't know if I have the facts stated 100% correct but I think my details are pretty accurate nonetheless.  We'll see what we can determine but I'm still satisfied the use of compressed tables is reliable and worth the space we have reclaimed.

An on-going task as part of an Oracle based data warehouse with which I've helped develop is to review and enhance the design of schema objects (tables and indexes for example).  Space utilization and performance are among the primary characteristics to review.  You may suggest, possibly even strongly, that these are factors to be included in the initial design stages.

To that I can respond that they were.  At least to the best knowledge available to the development team at the time of initial design.  The good news is that the data warehouse and the reporting and analysis activities it makes possible has continue to increase both in data available and in use and value to the business overall.  While growth is the majority trend, some data has not been used or does not change as much as initially estimated.

One of things we've been doing is implementing the compression feature of Oracle on tables and indexes were appropriate.  With many months of historical data now available from a substantial and growing list of source systems, we are going back to look for data with a high occurrence of repeated values as candidates for compression.  It turns out this is a majority of the data.  As implemented thus far over several months,  compression has reclaimed upwards of 50% of previously used space without compression.

And the performance hit as been unnoticeable.  In fact daily ELT process may have increased as increased I/O efficiency outweighs slightly increased CPU overhead for compression processing during DML.  The nice thing about this experience with implementing Oracle compression is that it is not a theoretical exercise.  This is our actual experience and we track before and after metrics to observe any positive or negative effects. 

After four or more months of applying compression, the results are only positive.