For ages, IBM had engineered DB2 to include “extenders”, which were add-ons that provided useful para-database features, like free-text style searches that were awkward to express in traditional SQL.  News from Leons is that this technology has been moved into the core DB2 engine starting with v9.5.2. I’m still scrounging for information on the changes on the IBM site, but this is great news.

Advertisements

… how I should have been doing PostgreSQL replication all along!

I can’t believe I’ve only just discovered SkyTools. If you’re like me, and hadn’t heard of it either until now, it’s a set of PostgreSQL replication and management tools put together by the folks at Skype. Now, you may love or hate Skype, and their eBay overlords, but they do run one of the larger public PostgreSQL clusters around, and it’s really nice to see them sharing their tools. Not as feature-packed as Slony, but something that is certainly only going to get better, and put the pressure on. You can check out the replication capabilities of PostgreSQL/Slony versus the other database “usual suspects” over at the Database Rosetta Stone. Of course, what PostgreSQL really needs is what’s currently being discussed (dare I say, rehashed), on the postgresql-general mailing list. It’s long past due that replication technology was brought into the PostgreSQL kernel! There’s just no valid excuse for avoiding this … and it is one of the two serious technical impediments to much wider adoption of PostgreSQL (the other being in-place upgrades). But enough griping. Go play with Skytools. Go on! Get!

The DB2 book is done!

August 20, 2008

After about a year’s effort, the book I was writing hits the shelves today (well, yesterday, actually). Beginning DB2: From Novice to Professional is pretty much self-explanatory. It’s targetted at anyone who wants to learn DB2 on Linux, Unix and Windows. It’s a little unusual in that it covers both database admin stuff, as well as developer stuff … that just means you get more bang for your buck!

Here’s what the cover looks like.

Beginning DB2

Beginning DB2

More details at www.beginningdb2.com (when I get some content up there 🙂 ).

Saw this comment today … it’s priceless:

If the same method that exchange/outlook uses to store email were used in the real world as a paper filing system: Every document is translated into Greek, and the original is burned. Then they are all glued together into one solid block and stuffed into a magic box with a tiny slot, through which you can talk to a little gnome who somehow gets each message for you as needed. Sometimes the gnome gets confused and it takes hours (sometimes days) for him to sort things out; meanwhile he can’t find your documents until he is totally finished becoming unconfused again. As an added bonus the gnome costs several thousand dollars and when he dies every few years you need to buy a new gnome. Oh and if the first box gets (arbitrarily) full you have to buy another special gnomebox, which of course costs $$$

It’s out, and it kicks butt! PostgreSQL 8.3 released, with 280 new features. Check it out!

There I was coding away a chapter for the new book (Beginning DB2, released by Apress if you must know), when I realised Rails had some cool features, but this wasn’t one of them.

If I want to use Rails’ neat scaffolding feature to throw up a framework for a table

ruby script/generate scaffold thing

Then I have to define my ActiveRecord in a half-assed, stupidest-idea-ever plural form.

class ThingTable < ActiveRecord::Migration
def self.up
create_table :things do |t|
t.column :thingname, :string, :null => false
t.column :description, :string
t.column :value, :integer
end
end

def self.down
drop_table :things
end
end

You read that right, fellow starship troopers. Random pluralisation for fun and profit. Do you think the Rails designers were savvy enough to ensure correct pluralisation? How about Schema/Schemata … Octopus/Octopodes … Sheep/Sheep … or Gateau/Gateaux ? Oh, wait, I’m sure Rails would spell that last one “Ghetto”. 😛

[The first article to be moved from the old site … hooray for progress :-)]

While I won’t ever be as organised as Howard, with his pantechnicon of useful articles, I did get my act in gear to write down the in’s and out’s of linking SQL Server to Oracle, and vice versa. In this first half of the topic, let’s look at how to create a linked server from SQL Server to Oracle – essentially putting SQL Server in the driving seat for any heterogeneous work you might like to do.

Step 1. You will need to install the Oracle client on your SQL Server host. Just so you’re completely clear … YOU WILL NEED TO INSTALL THE ORACLE CLIENT. We’re clear on that now, right? :-). The reason you need this is the same reason you need MDAC on a machine in order to access SQL Server. The client is what provides the network tranport interfaces that lets you talk Oracle’s transport protocol, Tranparent Network Substrate, or TNS. These are bundled up into a package that was historically called SQL*Net, and now is just known as Oracle Net. This is bundled in with the Oracle client, along with useful utilities like a query tool (SQL*Plus), network testing tool (tnsping), configuration wizards (such as Oracle Net Configuration Assistant) and other goodies.

To be completely honest, you can try to create a linked server using Oracle’s instant client, and even try using one of the “clientless” wire protocol drivers offered by third-parties. Best of luck with that :-P.

Step 2. Use the Oracle Net Configuration Assistant to configure tnsnames.ora properly. This is the file used by the Oracle client to find Oracle servers … in much the same way your operating system uses the hosts file to find hosts. The wizard will walk you through a set of dialogs and let you test your config at the end. If you can’t see the icon in the Configuration Tools start folder, you can simply type “netca” at the command prompt to launch the wizard. If that doesn’t work, then you can edit the tnsnames.ora file manually. The format is, in its simplest form:

MyServiceAlias =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = your-host-name-or-ip)(PORT = listener-port-usually-1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle-service-name)
)
)

Fill in the appropriate values for you host, listener port, service name and chosen local alias. Note that an Oracle service name is a published name chosen for the instance … don’t confuse it with the name you see in the windows Services.msc MMC applet if your Oracle instance happens to run on Windows :-). Be very careful not to mess up the formatting of other entries … dangling opening/closing parentheses will put a spanner in the works. The wizard is usually the best bet, as it ensures the formatting is correct. The listening port is usually 1521, the default, in much the same way that 1433 tcp / 1434 udp are SQL Server’s default listening ports.

Step 3. Before trying to configure the link in SQL Server, make certain you can talk to the listener and connect to the Oracle instance from the SQL Server box without any SQL Server involvement.

E.g:

c:> tnsping MyServiceAlias

[ boilerplate stuff … blah blah blah ]

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MyServiceAlias)))
OK (0 msec)

^^^ that’s the thing you want to see … “OK”

c:> sqlplus myusername/mypassword@MyServiceAlias

[ boilerplate stuff … blah blah blah ]

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> _

^^^ that’s the magic phrase and prompt … “Connected to:” and the SQL> prompt. type exit or quit to close.

Step 4. (Optional). You might like to create a dedicated Oracle user with which to use the soon-to-be-created link from SQL Server. Something simple will do for this test, but you can go to town if you like. Of course, an existing user could always be used.

SQL> create user oratest identified by oratest
2 default tablespace users
3 quota unlimited on users;

User created.

SQL> grant create session, resource to oratest;

Grant succeeded.

Step 5. Now you’re ready to create your link from SQL Server to Oracle. From within Query Analyzer, Isql, Osql or your favourite query tool, you can issue the following commands

EXEC sp_addlinkedserver @server = ‘TestOraLink’, @srvproduct = ‘Oracle’, @provider = ‘MSDAORA’, @datasrc = ‘MyServiceAlias’

EXEC sp_addlinkedsrvlogin ‘TestOraLink’, false, ‘sstest’, ‘oratest’, ‘oratest’

That creates a logical link called TestOraLink, mapped via the Microsoft OLEDB provider for Oracle to your Oracle service. We then associate a local SQL Server login – sstest – with an equivalent Oracle username and password, in this case oratest.

Step 6. Time to test your link.

SELECT * FROM OPENQUERY(TestOraLink, ‘select * from dual’)

D

X

Fantastic results! Well, OK, you’re just seeing the contents of the ‘dual’ view, but that’s come all the way from your Oracle instance, via your linked server definition.

So now you’re in business, you can go wild.

In the next installment, I’ll show off the three different ways to use your new link … the OPENQUERY, OPENROWSET and four-part-naming techniques. I’ll also highlight some of the flaws in SQL Server’s linked server logic, including the madness of “assumed” and “ignored” case-sensitivity in situations that should be just left in peace!

Happy linking.

IBM’s prolific Redbooks team have released the updated Oracle to DB2 conversion guide. While this is an excellent guide, it’s interesting to note that it still targets the previous version of DB2 for Linux, Unix and Windows … v8.1.

Oh well, maybe I can chip in an contribute to the re-write for version 9.1 🙂

Inspired by an attack of active laziness*, I’ve been building slack configurations to automatically and silently build new windows and linux boxes … yes, I’m just weird like that.

I’ve pretty much finished with linux world (the joys of apt-get, yum, etc) and am working on the windows side of things. Way down on my list of 50-odd things to install is my trusty old packet capture and analysis tool, ethereal. Or at least, that’s what it used to be called!. While trawling through the vagaries of the lack of silent installer for the packet capture library (winpcap), imagine my surprise when I discover that ethereal has recently changed its name to wireshark. Wireshark! I shit you not … I bet McKinsey graduates are crying into their Bollinger at the missed opportunity to charge $50,000 for that little gem!

It all came about when the original author went to work for commercial outfit with a small (though presumably innocent) conflict of interest, and all involved decided to “retire” the name ethereal. Kinda noble, I suppose, but nearly a decade of brand recognition and loyalty has come close to dying with the change. No doubt it’s been suggested before, but a small bit of legal gymnastics could have been performed to gift the name to the ethereal project and protect it from future misuse, I’m sure.

(* active laziness: The calculated act of doing something to avoid having to do ten times as much in the near future).

What do government systems and databases have in common? The concept of the separation of powers. In a Westminster-style democracy, the three arms of power – the executive, the legislature, and the judiciary – each have separate well defined powers that are forbidden to the other arms of government. For example (and something the crowd at Microsoft should remember), the power to determine punishments is exclusively the right of the judiciary … not criminally convicted monopolists who engage in “self help” :-). But I digress.

The same idea holds in database land. Separate powers and privileges out into discrete realms, and grant those well-defined powers to appropriate users. Works well for DB2, Oracle, SQL Server … but crashes into the safety barrier of poor design when we get to MySQL.

MySQL gets it right in the more-recently added powers, such as creating and executing routines, but its flakey bedrock is shown in the way basic object creation privileges are managed. Creating and dropping tables and databases are controlled by the same two privileges. Read that again – the create privilege spans tables and databases, and the drop privilege is equally-poorly scoped. What it means in practice is that developers who should be given a restricted authority to create objects can now create entire databases, and worse, the power to drop a table comes bundled with the “oops, was that the dev or production database I just wiped” ease of use feature. These should be separate powers … so let’s see how many other people have requested this in the past on the MySQL forums/bug site, and if in the unlikely event that count is zero, I’ll submit it and we’ll see what happens. Stay tuned to watch the fun.