The latest release of DBD::Oracle is now ready and can be found at: CPAN DBD::Oracle. It is a Perl module that works with the DBI module to provide access to Oracle databases. It is maintained by me, John Scoles, under the auspices of The Pythian Group as open source/free software.

This release is largely a maintenance release that fixes a number of bugs (new and old) and cleans up the POD. I have also spent a good deal of time cleaning up the tests, compile warnings, and the Makefile.PL. A thank-you goes out to Martin Evans and H. Merijn Brand for their help on that.

As for new items, the ORA_VERBOSE attribute was added so that you can debug/trace DBD::Oracle without seeing any DBI debug/trace output. OCILobGetLength was added to the LOB functions, thanks to Milo van der Leij, I have also dropped support for Oracle 8 and earlier clients; see the POD for details on this.

Finally, I have also cleaned up and expanded how persistent LOBs work. A thank-you to Eric Simon for his work on this.

The release has been fully tested with version (1.601) of DBI. Below is the list of the changes and/or fixes in this release.

Read the rest of this entry . . .

After I moved back to Europe and Malta in order to set up our operations here, I was approached by a old friend of mine who wanted to know how to add a UNIQUE constraint and remove duplicates on a table, while keeping the newest records. He had been trying with ALTER TABLE but ran into problems as the older values were taken.

So, to help him out, I first solved it based on his original idea, and then figured I would post a small note about the solution here.

So, let’s say we have the following structure . . .

sql01 blogs> SHOW CREATE TABLE post1164\G
*************************** 1. row ***************************
       Table: post1164
Create Table: CREATE TABLE `post1164` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(5) DEFAULT NULL,
  `c` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

. . . with a small amount of data in it:

sql01 blogs> SELECT * FROM post1164;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | a    | a1   |
| 2 | a    | a2   |
| 3 | b    | b1   |
| 4 | c    | c2   |
| 5 | b    | b2   |
| 6 | c    | c1   |
+---+------+------+
6 rows in set (0.00 sec)

Now, if I were to use his original SQL, I would get a result similar to this:

Read the rest of this entry . . .

It’s time again for another edition of the weekly review of database blogs, Log Buffer. Since it was a big week for SQL Server, let’s start there, shall we?

The big news — SQL Server 2008 is released, as reported by SqlServer-qa.net, in seven different versions. Aaron Bertrand introduces a new kid on the block: SQL Server 2008 Web Edition — “. . . designed for highly available Internet facing web serving environments for the next generation Windows Server,” according to MS. (I’ve heard it rains quite a lot in Seattle. I guess it also Drizzles, too.) Says Aaron, “Basically, it has higher scalability than Express or Workgroup Editions : it supports up to 4 CPUs, no artificial limit on RAM, and unlimited database size.”

(Before you crack open your server case, Tara Kizer on Ramblings of a DBA has some advice on how to get physical CPU count on a server.)

The SQL Server Express Express blog helpfully offers a table outlining the differences in the features of the differences between SQL Server 2008 Express editions.

A couple little issues with the release have already emerged. Here’s Euan Garden on SQL Server 2008 Installation Confusion, VS 2008 Sp1 and NetFx 3.5 Sp1: “SQL Server 2008 has dependencies on (and includes) VS 2008 SP1 and its components (BIDS is just VS 2008, SSMS uses components), plus NetFx 3.5sp1. SQL includes the RTM versions of both of these, HOWEVER they are not broadly released yet. There is a check in SQL Server Setup that if you have an old version (Beta, RC, whatever) it will bounce the install and ask you to upgrade to the RTM bits…which of course are not yet available independent of SQL.” Click through for the KB and some other help. Denis Gobo and his readers have some more to say about this.

In all this change, you may lose sight of an important fact or two, and you may ask yourself, how did I get here . . . what version of SQL Server am I running? A couple resources are shared by Kevin Kline.

Read the rest of this entry . . .

My parents instilled upon me many values that I keep with me today. My twin brother and I are the youngest of four children, coming from a lower-middle class background. We children had the inevitable fights over material possessions, screeching “Mine! Mine!”

My father’s response to this was to look at us and say “These toys are mine; I bought those toys with money I worked for. What’s yours is what you make with your bodies.” While the sentiment is arguably harsh, crude and bordering on vulgar, I cannot argue that he had a certain point.

If you do not truly own something, you will be left squabbling like a child when your perceived ownership is threatened. When you assumed you owned something and the truth comes to light, you will be massively insecure and have a sense of injustice.

A few points from OSCon are haunting me and getting me to think about what FLOSS means to me, and what I want from it.

— Open source is important even if you never read one line of source code or make one modification. The fact that anyone can read and write the source code is critical even if nobody besides the original engineer(s) ever does.

Read the rest of this entry . . .

Interesting results you can see from some low quality news aggregators Looking at this page you might think that I’m awarded with the Liberty Medal:

I’ve got this link while browsing the search results for RAC Attack. I guess should ask my granddad to pass my best wishes to Mr. George H.W. Bush.

It’s all over now and I can’t describe how tired I am. As I type it, my flight from Chicago is delayed by 1.5 hour so far (the third delay already, 30 minutes each). I should say that I hate Chicago airport but I digress.

Last night, I left my presentation in a good state and verified the first batch of demos — all worked fine. This morning turned into a disaster as I found out that the rest of my demos stopped working — my 11g RAC cluster on Ubuntu (I have the reason for such unusual setup) got issues with network connectivity. I should probably blame VMware Fusion 2.0 beta combined with Ubuntu but the net result was failing network connections with weird TNS errors and hanging connection requests. Obviously, I couldn’t demonstrate advanced connection management without *simple* connections working.

Well, I didn’t have any other choice but to add new slides with some demo results. Since I did it in a rush, there were some overlaps and I ended up with way too many slides. Good news that the first demo generated sufficiently enough interest about services automation and connection load balancing so we spent significant time there. Well, nothing comes for free so the rest of the presentation had to be covered too quickly but I did manage to go through the failover scenarios and failover technologies available. Run-time load balancing and load balancing advisory wasn’t left aside either so I did make it till the very last slide!

All in all, today’s session wasn’t as good as yesterday but I managed to avoid a complete failure with the demos so I guess I shouldn’t complain.

This morning I checked how the labs went on the 24th floor and I should say that labs agenda looked very impressive and it seems that lab part was probably the best feature of the event. The amount of material Jeremy Schneider prepared was huge and there would be enough labs for 2 full days, not just one. The attendees could choose the areas they are interested in most and work on different threads with their own pace. Great job Jeremy!

Back to the reality and the airport… The flight Chicago-Ottawa is 3+ hours late and they moved us to another gate. Oh… Do I hate Chicago airport! In the meantime I managed to transfer this text from my MacBook to the Blackberry and publishing it from there. The Blackberry syncronisation utility for Mac is a big shame. Can’t wait for iPhone…

To finish on a good note — I’m very glad I came to RAC Attack! What a pleasure to see old friends and make new ones. I just hope I can actually *leave* this place…

Recently, I was involved in a discussion about import performance in this OTN forum in which the original poster raised the issue of whether or not import will resort to single-row inserts for tables with date columns.

For array inserts, the buffer parameter essentially specifies the size of this array. We know, however, that if a table has lob columns, the import parameter buffer is not honored, and the import utility will use single-row inserts for those tables. But tables with date columns, so the claim goes, also must suffer single-row inserts. In this blog, I will probe this further and validate that claim.

Let’s create a table and populate it with 300K rows.

 create table t1 (n1 number, v1 varchar2 (512), d1 date);

 insert into t1
 select n1, lpad(n1, 500, 'x'), sysdate
 from (select level n1 from dual connect by level <=300003);
 commit;

 REM Creating an  export file..
 host exp userid=cbqt/cbqt file=exp_t1.dmp log=exp_t1.log tables=t1

The above code fragment created a table, inserted 300,000 rows, and exported that table to an export dump file. This dump file is ready to be imported. But, we need to trace the import to measure the effect of the buffer parameter. The problem, though, is how to trace the import session alone without generating every session in the database. This can be achieved by creating a logon trigger as below. Only sessions from a test user will have trace enabled from this trigger (the username is CBQT).

REM I could use "on schema clause too, but this is part of generic code that I use.
REM Riyaj Shamsudeen - To trace a session through logon trigger
create or replace trigger
set_system_event
after logon  on database
declare
v_user dba_users.username%TYPE:=user;
sql_stmt1 varchar2(256) :='alter session set events '||chr(39)||'10046 trace name context forever, level 12'||chr(39);
begin
  if (v_user = 'CBQT') THEN
      execute immediate sql_stmt1;
  end if;
end;
/

Let’s drop the table, then import with a default buffer size of 64K. Through the logon trigger, a new SQL trace file will be generated. That trace file will be analyzed with the tkprof utility as shown in the code fragment below:

Read the rest of this entry . . .

If you work with databases long enough, you run into a certain problem. You have a mysqldump file* of a table or a database, and you need to import it into your new database. However, you need to change something in the file first. Maybe the INSERT statements need to be changed to INSERT REPLACE. You fire up vi and load the file, but when you go to search and replace, vi runs out of memory and doesn’t complete the operation. Or maybe the dump file is just so big it won’t even load in the first place. What do you do in this situation?

Well, one simple solution is to use the sed tool to modify the file. Sed actually stands for “stream editor”. The vi editor would be considered a static editor in that it loads all of the file into memory at once. If you run out of memory, you are out of luck. With sed there is a very limited amount of data in memory at any time because it streams the data “through”, manipulating it as it goes. So sed can work with files that are huge, and only use a minimal amount of memory for processing.

The format of the search and replace also is similar to vi's search and replace. For example:

Read the rest of this entry . . .

I should say that I had interesting experience this evening — a tornado warning in downtown Chicago.

The alarms went off as soon as we tried to get out of the restaurant. Below is a year old example but it sounded exactly like that + it was much darker — like if the skies fell on us:

We had to turn back and were stuck in the pub for another hour or so sipping Guinness while the mother-nature had some fun around us. Funny, looks like it decided to come back now — skies are falling again with heavy rain, clouds and lightnings everywhere. The thunder is very loud and sirens went off again — can’t even sleep. But I digress so let’s get back to the overview of the day…

I love small classes! I mean small rooms with all chairs taken when people are close to me as I present and close to each other. The audience today was exact fit for the room — 20 people could fit on the chairs around the desks and few more (organizers and presenters) next to the back wall. What I like about small classes is the intimate atmosphere in the room. I also like live speech where my voice delivered natively without electronic distortion keeping all the beauty of the Russian accent (some people *still* noticed bits of German influence there).

The only small problem was the location of the stand where a speaker hosts the laptop — it was in the middle of the room. That felt somewhat odd and I kept running between the middle of the room and the stage (well, or the place where it’s supposed to be). I used to the fact that I have another view on my laptop screen — speaker’s view with next slide/motion and my reminders. I also had to do several demo’s on my RAC cluster and I obviously needed the keyboard badly for that. I should apologize that 8 people had to observe my back instead of my face for some time and I also couldn’t see how they were taking the material but I tried to look back from time to time and as soon I did that — I could see confirming nods so thanks for that!
Read the rest of this entry . . .

I’m pleased to announce that there will be the formal launch of Pythian Europe at the premises of the Canadian Embassy in Prague on Wednesday the 6th of August from 17:00 to 18:30. This historic event will be announced by Mrs. Sameena Qureshi, Trade Counsellor, Embassy of Canada; and Paul Vallée, President and Founder, The Pythian Group. Present will be various members from the press (IT and Business), as well as representatives from Oracle and Sun Microsystems, the Canadian Chamber of Commerce in Prague, and many more. We will prepare some unusual and very tasty snacks and refreshments.

We would love for readers of this blog to join us, so please consider this your special, personal invitation from me. Please come if you’re in Prague on Wednesday. If you plan to attend, please contact Dan at elbl@pythian.com.