jfw.id.au

I’m migrating a database from a postgres 7.4 server to a 8.1 server and hit a small issue with the import of the dumpfile. I’m in the middle of testing the migration at the moment and the complaint coming back from the import was about invalid UTF-8 characters in the input file. A quick google and the first link was to a page on the PostgreSQL site with the following snippet:

Some users are having problems loading UTF-8 data into 8.1.X. This is because previous versions allowed invalid UTF-8 byte sequences to be entered into the database, and this release properly accepts only valid UTF-8 sequences. One way to correct a dumpfile is to run the command iconv -c -f UTF-8 -t UTF-8 -o cleanfile.sql dumpfile.sql. The -c option removes invalid character sequences. A diff of the two files will show the sequences that are invalid. iconv reads the entire input file into memory so it might be necessary to use split to break up the dump into multiple smaller files for processing.

It works :) I had no doubt, but it is good to know that it has solved this problem.

Now my only problem is to work out why there are invalid characters in the database in the first place.  But that can wait for another day.  I’m off to bed!

At work we are all pretty busy with the project known as ‘Phase 2′. A project that the sales and marketing team had decided on a deadline before we even knew that it existed. This should be the last, or at the worst next to last time that something like this happens on such a massive project.

I have no illusions about it not happening again, however we can dream can we not?

Any way, the point of this post is simply to marvel at a lovely way to do some PL/SQL to build a bit of a data layer for the extensions that we are adding. I’ve not done a lot of PL/SQL in the past, and the stuff that I have done is fairly basic so I was quite impressed with the simplicity and functionality of this little block:

PROCEDURE getMenu
   (pDetails    OUT grcDetails,
    pMenuID   IN Menu.MENUID%TYPE DEFAULT NULL) IS
BEGIN
   OPEN pDetails FOR
   SELECT *
   FROM MENU
   WHERE MENUID = DECODE(pMenuID, 0, MENUID, NULL, MENUID, pMenuID);
END getMenu;

All the smarts of the statement are in the DECODE. For those that don’t recognise it, this is a small function written in PL/SQL for Oracle. Basically this function allows us to load all of the records in the MENU table, or the one specified by the pMenuID if it is supplied.

I was just impressed with it’s power and simplicity and had to share.

During our recent Oracle upgrade we encountered a lovely little bug.  This was a particularly annoying bug as it was not publicly acknowledged, nor was a patch publicly available.

The bug is apparently specific to deployments of Clusterware from release 2 of 10G database server on EMC storage devices, or devices that use the EMC powerpath software for multi-channel communication with the disk device.

Clusterware is very touchy during installation, and if anything is not 100% right it complains and dies.  On a brand new storage device, using brand new disks with brand new partitioning on them we were unable to complete the installation without help from Oracle support.

__(’Read the rest of this entry »’)

At work we run a dotNet web application over an Oracle Database for our Central Reservations system. When we launched the site we launched the database on a dual 2.4Ghz Xeon system with 6 internal hard drives and 4Gb of RAM.

A little background on the system. The new dotNet application was written to replace an aging and poorly written cold fusion CMS, an equally aged and similarly bad vb6 application.

On top of replacing these applications, were a host of enhancements and new features that could not be done in the old systems.

At first things went really smoothly. The system was humming along, which considering it was an un monitored, unadministered Oracle server was pretty good.

It wasn’t long before the box began to suffer.

__(’Read the rest of this entry »’)

It’s a beautiful thing, referential integrity, when used properly. It provides such power and stops situations like the one that I am currently dealing with at work from eventuating.

A few months ago we launched a redeveloped version of most of our core systems at work. I didn’t feel it was ready for launch, but we won’t go there now. At the time we launched we imported all records for the current financial year. I always believed that we needed to have all information across, but management decided that it wasn’t.

1 week before the end of the financial year I was informed that the auditors that we had in had informed management that we had to have all the information across, and that I had to have it done before the end of financial year. FUN! NOT!

Any way, to cut a long story short I am now trying to pick the last few pieces of information up out of the old system and pull it into the new one. I’ve gotten 98% of it done and these last 100 are annoying the hell out of me. Or rather, the lack of any form of referential integrity on the database is annoying me. I’ve got orphaned data in the old system that I have to find some way of pulling into the new one. The only answer I could use was to create dummy records for the deleted information and to map the orphaned data to the dummy ids. This is not ideal, and I personally think it’s a bit of a “dirty hack” to borrow another developers favourite phrase.

The old system was a mishmash of technologies over a postgresql database. It’s not like we couldn’t have foreign keys in the system. Hell, even access supports them, and in my current situation I would have prefered an Access system with foriegn keys over the postgres one without.

To make matters worse, we’re migrating to a single platform. DotNet over Oracle. This should be much better, but no it’s currently no better - at least from where I sit. The problem is that the developers that were the initials on this project didn’t implement foreign keys in the database.

I’ll have the last laugh though. Eventually propper foreign keys will be implemented on the system and the developers here that dont like it will just have to get over it. If I am to be responsible for the database, and I believe this is what a DBA is responsible for, I am going to ensure that we are making the best use of the power and functionality that it provides.