data directions: New SQL Server 7.0 – Almost an AS/400
Microsoft’s SQL Server 7.0 is hitting the street with a bang with lots of new features and capabilities that we may be able to take advantage of on the AS/400.
Microsoft is marketing this as a major revision of SQL Server -- and it is. Years after the Sybase split, they are finally making SQL Server a real large-scale database with all the trappings needed to support that environment.
Naturally, there are lots of things to help get your data on the Web – all the e-commerce stuff. You know, let all your competitors and customers see all that data you have that you can’t figure out.
There are tools to assist in publishing data on the Web as HTML pages. This is not unlike what we received on the AS/400 a couple of releases ago. And there is record level database locking support. This is so that applications do not lock up too much data during update operations. Of course we have had this on the AS/400 since before the AS/400 was an AS/400!
Then there is the new facility to create maintenance jobs that schedule database jobs for you that can run unattended at off times. It’s a shame we don’t have something like this on the AS/400. Oh wait, we do. Well at least they have added an SQL performance analysis tool to help you optimize your indexes. Hmmm, I guess we have had that for a few years already too.
OK, OK enough Microsoft bashing (at least for now). There are some real capabilities that might make sense to look at. They have added some nice tools, such as the database diagram tool. This will draw a diagram (like in Access) of the relationships between all your tables and views. This is something that would be nice on the AS/400 without having to use a third-party tool.
Another addition is the support for multidimensional databases. Long term, I think this may be one of the more important features in the upgrade. It will take some time before the software vendors take advantage of it though. What this does is provide at a system level, the code required to store, manipulate and deliver multidimensional databases. This is critical in quality data warehouses since most of the data is stored this way for retrieval purposes.
If you are going to store data on multiple systems – as an example, your host is the AS/400, but you want to provide data warehousing on NT using SQL Server -- one of the most important capabilities you will need is data replication. Somehow you need to get data from the AS/400 to the SQL Server database conveniently and relatively easily. You could write programs to do this, but if all you are doing is moving tables (files) from one machine to another, then why not use replication. Version 7.0 contains a built-in replicator (much like DB/2 on NT). This replicator allows you to push data from the host to the SQL Server, or to subscribe to a database for data to be sent to it at some predetermined time.
These replication techniques should be very useful, but I have heard (not tried yet) that they purport to be. In a future column, we will take a look at what it takes to set up a database from the AS/400 and replicate portions of it.
And just in case triggers haven’t confused you yet, 7.0 allows you to have multiple triggers on one physical table. Actually, this “can” be a nice feature if you have complicated business rules that you implement in the database using triggers. You could have one insert trigger and 3 delete triggers that get fired when a delete occurs. So if you thought triggers were magic before, now you can set it up so that you never know what is happening!
And now that we are mostly Windows 95 literate (we think), you can use 7.0 on Windows 95 or higher. This will be nice for systems where you want a copy on desktops, or laptops, and sync them with a host. This is not as simple to use as Access, but it is a much more powerful database.
Soon too, you will see some vendor’s product with SQL imbedded in their code. Microsoft is now licensing it in various flavors for ISVs to build their product around. This will make having SQL somewhat transparent to you, which will be nice (until you load another copy for some reason!).
Any way you look at it, SQL Server 7.0 is a much better product that prior versions. It is just a little silly to me that there is a lot of comparisons of NT and SQL to the AS/400 and its database. Many of the features coming out, we have had for years. In some cases (record level locking as an example) I do not understand how a business system could operate any other way. The AS/400 was built as a business transaction processor and it is still currently the best one on the market.
John Bussert is president of Swift Technologies (Marengo, Ill.), a company specializing in AS/400 and Windows NT software. [email protected]