Category: SQL Server

Living, loving, caring for the database in my life

Categories
Computing Java SQL Server

DB2 Impressions

When I got my laptop at my new job it did not have a DBMS installed. Sad, but could be worse. Since the only free version of SQL Server is the MSDE, and the Oracle ‘personal’ edition is almost a gigabyte-sized download, I decided to check out the personal edition of DB2 – a mere 330MB.

The installation went pretty smooth but the first time I ran it I encountered a nagging missing DLL error thrown by the javaw. Weird because DB2 ships, runs and breaths its own JDK – IBM’s version 1.4.1. The error message recommended reinstalling the product, I did and things fared better – with the error going away. I did try to tell the installer to skip the IBM JDK installation but it ignored me. I do not care much because the system works.

DB2 is certainly a more mature product than SQL Server. It asks you for all sorts of information that has to do with tweaking its performance, from memory management limits to a variety of settings that have to do with automated optimization of the data stored on the server. What did bother me is that the wizards that are available in the DB2 Control Center application (parallel to Enterprise Manager in the SQL Server universe) – that are supposed to help you create databases, tables, etc. – do not seem to work too well.

The installation wizard ends with a ‘getting started’ window that encourages you to ‘create a database’. I tried that path and encountered a multitude of options that somehow – while using the defaults – failed to produce the promised database. What is also both neat and overwhelming is the fact that almost every operation that you try to make produces a result window that tells you if the operation succeeded. That’s all nice and well until you are returned an error. Each error reported is followed by a list of all the error codes that the error reports and it gets tedious when you look for the correct error.

I managed to create my database from a command line interface and then was able to use the wizard in the Control Center to create a table. Many things are really cool in DB2 – for example, you can ask a table what stored procedures or user defined functions use the table – an important feature when you are considering altering the table or modifying the stored procedure or UDF. Also neat it is the table creation wizard’s clean key definition – which in my opinion is superior to that of SQL Server.

Another thing I encountered was that as uncool a DBMS SQL Server is with Java, at least it produces readable error messages (most of the time…). DB2 provided me with this message when I violated a unique constraint on a column when I tried to insert a row:
SQLCODE: -803, SQLSTATE: 23505, SQLERRMC: 2;YZUKERMA.SUBMISSION
An A9 search on DB2 error codes lead me to the DB2 documentation page which is impressive in the amounts of information it provides. But all I want is a clear understanding what I did wrong, eh?

More impressions sooner than later…

Share
Categories
SQL Server

Copying SQL Server databases having full-text catalogs

I was having a problem using SQL Server data transformation services’ database import feature. It appears that the problem was (found by my buddy and co-worker Rob) with the fact that I had a stored procedure that relied on a full-text catalog.

Since the catalog is not copied with the database, the stored procedure, that uses the full-text-search-specific predicate CONTAINS, caused the problem. Not copying it, solved the issue. I then needed to re-create the index and then re-create the stored procedure.

Share
Categories
SQL Server

Handling comma-delimited lists as stored procedure input

Comma-delimited lists can be used as input to stored procedure, assuming their length is predictable.
Handling them inside the body of the stored procedure, is not a native matter (no FOREACH construct) and requires minor programming.
For example, using an input variable called @sad_date:


WHILE (LEN(@sad_date) > 0)
BEGIN
-- extract the string at the head of the list
SET @commaPos = CHARINDEX(',', @sad_date);
IF (@commaPos > 0)
BEGIN
SET @dateStr = SUBSTRING(@sad_date, 1, (@commaPos-1));
SET @sad_date = SUBSTRING(@sad_date, (@commaPos+1), LEN(@sad_date));
END
ELSE
-- we reached the last substring
BEGIN
SET @dateStr = SUBSTRING(@sad_date, 1, LEN(@sad_date));
SET @sad_date = '';
END
--
-- do something with the input here....
---
END

Share
Share