DB2/400 vs Microsoft SQL Server

[jtaylor]

I am looking for good, unbiased, comparisons between DB2/400 and SQL Server.

[Lynne Noll]

SQL server is a lot better than it used to be. DB2 used to be much more stable, but it does not have much trouble that way any more.

SQL server has some advantages in syntax. For example, SQL Server has a join syntax that permits updating one file from another in a very natural way. The T-Sql stored procedure language is much easier than DB2's language and easily permits doing things you are apt to want to do like printing. On the other hand, DB2 allows you to easily incorporate code in regular programming languages like RPG in stored procedures, and last I knew, SQL server does not.

SQL server links up directly with delimited files, which are good for imports.

SQL server has a much more natural way of selecting the top so many records.

I believe index performance can be better in DB2, and DB2 can handle large volumes better than Sql Server. But I may not have enough experience to be reliable on this.

Db2 on Iseries (unlike other forms of DB2) permits treating data as either a table or a file (that is,you can do things like restore just one file that has been damaged by a misbehaving program or that is needed for special activity like reports as of a particular date.) This is unique to the Iseries; other systems have databases that are all one enormous file (or that are in segments, but not logical units).

SQL Server can link to DB2 via a driver and thus query the db2 data base if not stymied by things like JDE's declaring all the fields to be binary so that EBCDIC to ASCII does not work. (Just ran into that.) You can't go the other way except with JDBC.

DB2 permits linking between all instances of DB2 on a variety of platforms just via connection statements and some other stuff. (But this is not quite as easy as linking tables via ODBC the way SQL server can.)

There are tools that can be used with SQL Server that do not work with DB2. Thus, you can use Visio to map a SQL server data base just by following the key constraints, or alternatively, create a SQL server data base from diagrams. Navigator does not compare.

[satid]

SQL Server can link to DB2 via a driver and thus query the db2 data base if not stymied by things like JDE's declaring all the fields to be binary so that EBCDIC to ASCII does not work.

IIRC, JDE declares its character fields with "For bit data" attribute that effectively puts the fields in 65535 code page. This issue was taken care of since V5R3 ODBC/JDBC drivers with the data source parameter "Force translation of CCSID 65535". More information in "New Data Type" section of the following article: http://www.itjungle.com/fhg/fhg063004-story01.html.

Once you enable this parameter, you must put the server job into your desired CCSID by CHGJOB command or explicitly specify the CCSID in the user profile that you use to log on to the connection.

This should also work with V5R2 ODBC/JDBC drivers that connect to OS/400 V5R2 with its latest Cumulative PTF + DB2 fix pack applied.

[jtaylor]

I was hoping someone had a feature-by-feature comparison between DB2/400 and SQL Server.

[jjuracich]

jtaylor:

I think the problem is that you're looking for

good, unbiased, comparisons between DB2/400 and SQL Server

which are going to be hard to find! You can always get info from the vendors - but seriously, how "unbiased" is that going to be?

I'm going to assume that you've already Googled for this (is that really a word?!). I found quite a few comparisons when I did, but didn't review any of them in detail.

IBM published a Redbook a few years back titled "DB2 UDB for iSeries Porting Guide SQL Server to IBM eServer iSeries," from which you may be able to glean some points of comparison.

It probably doesn't, but I hope this helps a little...

Jon

[kellyj00]

all I can say is sql servers "data pump" is a great ETL tool for non-sqlers.

I used to be an oracle developer, then a sql server developer, then a postgres developer, and now I'm in a new job (18 months so far) on systemi. Here's my 2 cents.

Oracle is an amazing database. Very well documented and help is always just an IRC room away. It's also darned expensive. their pl/sql rocks, lots of built-in's that I miss, such as the dbms_mail package. Oracle also supports materialized views... just like DB2, but db2 UDB lacks a very nice feature, and incremental refresh of a materialized view. non-iseries DB2 supposedly supports this, but i've never tried it. Oracle's explain tool is the bees knees...not too mention that they also own JD Edwards, peoplesoft and are trying to take over the planet, which I've never seen as a bad thing.

SQL server, back in 2006, was much cheaper than Oracle. Not sure now. It's another great database that, last I checked, ONLY RUNS ON WINDOWS...which isn't a bad thing, necessarily. Windows 2000 and 2003 server software is pretty solid. (we reboot our iSeries each night, probably not necessary, so I can't compare the two. I've never had to reboot a windows server for a crash, only for updates, hardware maintenance or third party software issues). Anyhow, sql server is a solid database, with a good optimizer and it's very easy to administer as the GUI is pretty sweet. Their explain tools ain't so nice, but if you know what you're doing, you can probably decipher what they call a plan (I can't) but I've not used the newest version.

DB2 has the materialized query table (oracle calls these materialized views) functionality. when it's on an iseries, you can't incrementally update them (big waste of time) but the optimizer does a really good job of finding these pre-aggregated tables and using them. The optimizer on DB2 is pretty good at picking things out, once you drop all your select/omit BS access paths that you added a "Omit where 1=0" to back in 1986 as part of a cargo cult movement to speed up the performance of your iSeries.

Depending on what you want to do, there's a database that fits the need. DB2 won't leave a bad taste in your mouth, but MS SqL may if you don't want to run windows servers forever. DB2 (non-iseries) will be around for a long long time as it is IBM's only database product and everyone needs a database. The freebie databases, such as Postgres do what they are supposed to do, but I think those have a limited use for companies who don't want to pay for commercial support and the ease of finding a DBA. Considering that there's not that much more that can be done for databases, the freeware database may eventually eliminate the big commercial players by pecking them to death as if they were ducks, I still consider them a different market.

[jtaylor]

Let me elaborate on the situation...

We have an application that references DB2/400 tables on our iSeries. Some of our Windows staff want to move those tables to one of their MS SQL Servers. I feel like the tables should be left on the iSeries but would like some info comparing the two databases.

[hcedmondson]

You could simply replicate the tables; they would then be in both places at roughly the same time.

Replication can be done commercially using something like HiT software's dbMoto, or you can roll your own, using triggers or journaling with JDBC, or .net with ODBC.

A lot depends on what the Windows team wishes to do with the tables.

I'm not sure I would use replication if both sides want to change the data, otherwise it's a practical approach.

[Elvis B.]

DB2 for i5/OS is integrated into the operating system. No other db is. DB2 for i5/OS supports more of the 2003 ANSI SQL core standards than any other db, including DB2 LUW.
If your colleagues can't see value in that... point them to this site, perhaps they'll see the light:

http://publib.boulder.ibm.com/infoce...generalfaq.htm

[JoePluta]

Originally Posted by Elvis B.

DB2 for i5/OS is integrated into the operating system. No other db is. DB2 for i5/OS supports more of the 2003 ANSI SQL core standards than any other db, including DB2 LUW.
If your colleagues can't see value in that... point them to this site, perhaps they'll see the light:

http://publib.boulder.ibm.com/infoce...generalfaq.htm

And here's an even better page:

DB2 Feature Comparison

It lists a wide variety of DB2 features (showing that DB2 for i5/OS is clearly the most advanced of the DB2 dialects). You might use this list to compare against the database of your choice.

Add in the fact that you can access the database using the native I/O capabilities of RPG and COBOL and it's hard to find a database with comparable features. Then include the integrated security and recovery features and the fact that there has never been a birus on i5/OS, and I can't understand why anyone could ever justify moving their data off of the platform.

Joe