Some background info: We are currently using DataStage version 6 on a Windows Server with 8 processors. We are hoping to switch to version 7 soon, and hopefully Unix by the end of the year.
Currently we are using DB2 for all of our tables. We'd like to get completely off the mainframe, and do as much as possible on server only. Right now, we are in the process of trying to decide between switching to Oracle or SQL Server. I know pretty much nothing about either one of these options. (UDB has already been ruled out, and MS Access can't handle our volumes.) Can anybody provide some information that could possibly help us make a decision as to why or why not we'd want one over the other here? Thanks!
SQL-Server is cheap and easy. However, it doesn't have the shear power to deal with large volumes. It also matters how you're going to use the database, OLAP, transactional, whatever.
Oracle is expensive and requires careful infrastructure architecture for high volumes, whch SQL-Server can't handle. It's also the most robust by far of the two.
Your request is too vague. The short answer is for small volume databases nothing beats SQL-Server. For high volume databases, your only real choice (left in the world) are DB2 and Oracle. Your comparison is apples to toaster ovens in price. Have you seen what Oracle costs? You're comparing Fords to Ferraris.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
You're hoping to move to UNIX.
SQL Server doesn't operate on UNIX.
Does that make the choice easier?
Oracle is WAY more expensive than SQL Server.
Dare I suggest, if what you're doing is a star schema design (which all good DWs should be), that you investigate Red Brick Warehouse from IBM? Red Brick was originally designed from the ground up (by Ralph Kimball and others) as a database for star schemas. It has spectacular query performance, due in part to what they were able to leave out (such as all the transactional overhead) and in part to very clever indexes, including a multi-table "STAR" index.
Start here. Version 6.3 has recently been released.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Consider leaving your DataStage server on Windows and putting your database on a seperate server, Unix for Oracle or Windows for SQL Server. As Ray suggests there are databases out there specifically designed for reporting that offer better performance then Oracle or SQL Server.
Generally SQL Server is easier to install and administer than Oracle.
Version 7.1 and 7.5 have the dynamic database stage which lets you easily switch between database types. For example you can replace all your DB2 stages with the dynamic database stage and make database type a job parameter. You can then pass in DB2 as a database type and in the future change it to Oracle or SQL Server when you change databases.
The "database type" property for this stage type (the "Dynamic Relational Stage") can also be set to ODBC. This gives access to Red Brick Warehouse.
I note from the original post that you're currently on version 6.x, so you won't have it yet. If you think this is the way you want to head, bypass 7.0 and go straight to 7.1 or 7.5.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thanks for all the info so far! To answer some of your questions and provide a little more info.....
We actually have both Oracle ad SQL Server both in house already. And both databases reside on their own servers, separate from DataStage. We don't have our "Data Warehouse" / "Star Schema" on this yet. That is all in DB2, and not currently connected to our DataStage projects directly. We are mostly using the database for storage and retrieval, and updates throughout the month (using AQT for some updates, and also doing appends of larger files to the database).
Our data that we're using in DataStage (and thus our DB2 table) is currently about 15 million records.... about 5 gig when in a flat file. This is large to some, but not to others. How do those numbers compare to what would work with SQL Server vs. Oracle? I'm guessing that's a small database compared to some of heard people are working with.
It sounds like your data volume is low and well within the capabilities of SQL-Server. DS has ODBC drivers for Unix so that it can talk to SQL-Server. For your volumes, Oracle is more trouble than its worth. It's power is in materialized views, parallel query, partitioned tables, etc, all of which is unnecessary in low volumes especially balanced against high GHZ cpus available on the Wintel platforms. You can be really sloppy and the hardware compensates.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
