Don't read this if you don't like to read rambling griping...

-------------------------

For the longest time, Oracle was my least favorite database platform billed as "enterprise level". Now, I ceremoniously pass that gold belt to DB2. And here is why:

1. If you let connections pool, you get nasty "Cursor C1 already open" errors. Well, guess what the default behavior of their OleDb driver is? Yep, pooled connections. Of course,... pooled connections in an "enterprise" setting wouldnt be a very important feature to make work. Nah.
2. You want to only select rows from a table using OleDb where a certain field value is null? Tough luck, NULL support is unpredictable at best. Most times if you attempt to filter or join on a field that has null values, you wont get a resultset back. Period. No error, no warnings. Just no resultset (not even an empty one with field metadata but no rows).
3. Multiple statements in one batch? nope. Create a NEW connection every time and issue your query (no pooling, remember).
4. So you just want the database driver installed? Too bad, you must install the WHOLE ClientAccess/400 product to talk to an AS/400. (not small)
5. Oh yeah, this is talking about DB2/400, not the other flavors. I have no idea how bad they are, but I can imagine.
6. Speaking of DB2/400, if you want to pull data from a table (really maps down to os/400 "file members") then most likely you will need to specify system name and library in order for the object to resolve. If you haven't seen AS/400 system names, they are remarkably easy to remember and type. "S3059863" is an example. And of course AS/400's are still living in the 1980's and all objects are limited to 8 characters uppercase. This includes files and file members (a.k.a. tables and fields in SQL). Yeah. So to issue a SQL query it looks something like this "select GSDOCO, GSORG, GSCHGN1, GSHHYT from S3059863.SOMELIB.F59603 where GSJDMT > 105058". Why, of course that means "select the ID, sales rep, charge amount, and sales tax from the sales table that were updated after 2/27/2005". Or does it.
7. Oh, and half the time when you install a os/400 "service release" update, it breaks untold other things... with the DB2 interface being one of the common victims. Then, to fix your application you get a ClientAccess/400 service release. Which, invariably breaks yet something else. Fun stuff. This is why most as/400 systems operate a year or two (at least) behind current patching levels. It's just too scary to update your machine!

And whats the saddest thing about dealing with DB2 in this kind of environment? The cost of that hunk of iron is two orders of magnitude above even a significantly overarchitected system built using nearly any other architecture which would give comparable reliability and performance.

Sometimes, it feels like interoperability with an AS/400 via DB2 isn't worth the headaches.

I have two potential topics I wish to cover. Can't do both though. Both can get pretty deep, and I will have a tough time fitting it into a single session as it is. So even after concluding a topic, I will need to whittle down the details. But first on to the topics.

Leave a comment, send me a message, whatever. Just let me know which of these two topics interests you most for a Code Camp session. I will probably save the other one for a possible Fall code camp, or another user group presentation. The subjects are both just too interesting to me for me to totally ignore either one.

• Topic #1: Asynchronous Programming

"Fully Baked" CLR support: Async Delegates, Thread Pools, System.Timer, and Queued Work Items. "Partially Baked" CLR support: Thread/ThreadStart, interthread communication, monitoring, signaling, and syncronization. Also would touch on important topics such as debugging, concurrency, Control.Invoke, lock(this), and deadlocking/performance implications. The end of the presentation would also talk about some exciting new async support in Whidbey for event-based async processing. In other words: "This ain't your daddy's multithreading presentation"

• Topic #2: Performance Planning, Design, and Tuning

In my opinion, a really important topic, but also really big. I would cover results-based performance programming, memory management (yes, its still important!), algorithm selection, profiling, and evaluating trade-offs. Would also touch on other aspects of performance, such as choosing your battles and managing the user experience/expectations. Lastly would talk a little about impact of Whidbey (generics/boxing, etc).

Let me know what you want to hear about!

Thanks everyone that posted comments or sent messages and stuff over the past week.

I seem to have passed the thing yesterday, and just as fast as it started, it's now over. It is difficult explaining just how incredibly painful this was, but others who have gone through it before will understand. You guys who have been through it before have my complete and total respect. And to others who have not had this before: I hope you never get it. I was lucky my wife happened to be home when it hit me.

This is how I spent mine:

Doped up on Morphine and a few other things I do not have memory of.

Started out with incredible abdominal pain, getting progressibly worse. We finally called an ambulance when my hands and feet started going numb, and I couldn't control the shuddering.

Turns out I have four kidney stones, the largest (4mm) of which was trying to pass down to my bladder. The other three are still partying in my kidneys. Guess I am in for a really fun week ahead.

In addition to learning how to screw up database schema manipulation in SQL 2005, I also picked up a few nifty tidbits in today's ISV event (all subject to change before RTM, and always the chance I misunderstood the material presented):

1. SQL 2005 supports synonyms. Well, it always supported them within a query, but now it supports them as permanent object aliases too.
2. DDL triggers. You can setup triggers for most DDL operations, such as CREATE TABLE, DROP PROCEDURE, ALTER LOGIN, etc.
3. "text" and related BLOB types are being depricated. "varchar" now supports unlimited size, which is defined using the MAX keyword. In other words, use "varchar(MAX)" instead of "text".
4. DML statements now support an OUTPUT clause where you can dump the contents of the "inserted" and/or "deleted" special virtual tables out to a table-valued variable.
5. Native encryption and signing support. Built-in functions that accept a text value and a passphrase (symmetric encrypt) or x509 certificates (asymm encrypt). SQL Server can also store certs internally for these purposes.
6. XML support is pretty cool, but jeez using .insert() or .update() can get really nasty in a hurry if the XPath strings get complex.
7. Kinda neet that you can create a CONSTRAINT based on an XPath expression. You can also create an index based on an XPath expression.
8. SQL Notification Services shows some promise. But aside from old-school client/server scenarios, the SqlDependency class seems to be of limited usefulness. Not very viable for smart-client or n-tier deployments without a lot of extra plumbing code (CAO's or sponsored SAO's and such). However, if you ARE doing client/server, SqlDependency is a nice way to watch for changed query results.
9. Service Broker is rather pimp. It's a message queueing engine that operates within SQL Server. It's contract/schema based, and lets you enqueue/dequeue messages within DML transactions. New MESSAGE, CONTRACT, QUEUE, and SERVICE objects in T-SQL are used to support this.
10. CLR integration looks pretty useful (as long as applied correctly). UDT's and Aggregate UDF's look most promising here.
11. You can use Service Broker to perform long-running transactions, as long as the requester does not need to be notified of completion/failure, or you can use Notification Services to send a message back to them.
12. Microsoft decided to not supply Query Analyzer tool with SQL 2005. Bad move IMO. QA was nice because it was very lightweight, and it also didnt give developers a whole lot of chance to screw up with the "right-click database drop" stuff.
13. ADO.NET 2.0 has a few new handy features...
1. SNAC - Sql Native Client, no longer need MDAC. I am guessing this is a full native .NET implementation of SqlClient, but not clear on that point.
2. MARS - Multiple Active Result Sets. You can essentially have more than one DataReader open on a single connection (useful when doing nested loops) (of course, nested loops are probably bad in your middle tier anyways, but who am *I* to judge this feature?).
3. New Async versions of operations. BeginConnect()/EndConnect(), BeginExecute*()/EndExecute*(), etc.
4. You can now load a DataTable directly from an existing DataReader. No longer need a DataAdapter... MyTable.Load(MyReader).
5. New DataTableReader is an IDataReader implementation that sits on top of a DataTable. MyReader = MyTable.GetReader().
6. In order to enable MARS and async operations, you need to specify "Asynchronous Processing=true" in your connection string. This might end up unnecessary when SQL 2005 is released.

This will work in SQL 2005...

Create trigger trgHmm1 on DATABASE for CREATE_TABLE, DROP_TABLE, ALTER_TABLE

AS

   ROLLBACK;

GO

Create trigger trgHmm2 on DATABASE for CREATE_TRIGGER, DROP_TRIGGER

AS

   ROLLBACK;

GO