On Friday, I saw an unanswered iSeries-related question posted to IT Knowledge Exchange, and thought I’d shoot the question straight to one of our experts at Search400 to see if they could help.

Ljjk122 posted this question on ITKE: I want to add data from a DB2 file to an Excel template that has a header row and totals certain columns without overwriting the header row or the totals.

I sent the question to Kent Milligan at IBM, who said:
It’s not clear what mechanism you’re using to get the DB2 data into the Excel spreadsheet. If you’ve purchased the iSeries Access Data Transfer, there is a solution. The iSeries Access Data Transfer Excel Add-in has the ability to download data to a user-specified range of cells of a spreadsheet, overwriting only the data within the selected range.

If you don’t have a license for the iSeries Access Data Transfer solution, then you may also want to consider evaluating DB2 Web Query, which offers excellent integration with Excel.

We encourage anyone with an iSeries question to submit it to Search400’s Ask the Experts, where all of our experts are available to answer your questions.

Along with i5/OS V6R1 is the DB2 that comes with it, and as there are enhancements in V6R1, so there are for DB2, IBM’s relational database management system. Let’s go over some of the details of those enhancements.

Usability

Many features have been added to System i Navigator, the user interface to System i servers, that enhance DB2 usability.

• DB2 On Demand Performance Center, which helps tune SQL performance on your System i, now includes customized reports that can be exported to more formats such as spreadsheets so users can more easily review and share SQL performance data.
• The DB2 Health Center, which reports on database performance and availability, now allows users to track their database environments against system limits.
• Fast Summary Compare, which allows users to look at a summary of captured data to determine which detailed data should be captured in the future.

Extended support for industry and DB2 standards

SQL Call Level Interface (CLI) is often the interface used when porting application data to DB2 on i5/OS. Now that interface can support wide-character APIs to increase the portability of certain SQL CLI applications.

The DB2 for i5/OS .NET provider now provides tighter integration with Microsoft Visual Studio development environment by adding support for Visual Studio database interfaces and Server Explorer, a server management console for Visual Studio.

The new DB2 provides enhancements to SQL Query Engine (SQE), a technology that IBM says improves queries for database access and is easier to use than the existing Classic Query Engine (CQE).

Application Development

IBM wanted the enhanced DB2 for i5/OS V6R1 to make it easier to use SQL within RPG applications. Included in those improvements are better pre-compilers, the ability to create a SQL statement “shell” that can be automatically copied into an application’s source code, and making coding SQL statements into RPG apps easier using WebSphere and Rational development tools.

DB2 Development Center has been renamed to DB2 Data Studio and offers an area to develop and deploy stored Java or SQL procedures, as well as wizards to help developers create web services based on DB2 data.

Support for data warehouse and BI applications

DB2 Web Query provides the ability to query and build reports from DB2 through browser-based interfaces. With V6R1, it now has licensing changes so users have more options and can save money.

The SQL Syntax support for DB2 now allows a user to group more kinds of data into a single query to reduce the amount of coding necessary for application developers.

The System i Network is hosting an interesting thread comparing DB2 native on the System i with SQL Server. Join the fun, or leave your comments here.

This week I spoke with the CEO of Resolution Software, a company that wants to make it easier for Data Description Specifications (DDS)-based System i databases to talk to SQL-based applications.

Resolution has been around for almost two decades, but it was just two years ago that it decided to delve into the System i. Starting in January, the company will start offering its Xcase software to users, an application that will allow DDS-based System i databases like DB2 to more easily communicate with SQL programs.

“We think that the System i market is a market where there is a lot to achieve,” Resolution CEO Elie Myal said. “Many shops today have not yet done the move to SQL, although they’re talking a lot about that. It is the time to do it, not 10 years ago, but now, when there is so much talk about modernization and so much talk about SQL.”

Resolution’s work is based largely on a 2005 IBM Redbook, “Modernizing IBM eServer iSeries Application Data Access,” which details a complex process on how to reverse engineer a DDS-based DB2 database and move toward SQL. IBM recommended the process because it said that SQL Data Definition Language (DDL) had surpassed DDS as the industry-standard way to define relational database management system (RDMS) databases like DB2.

Since that process is so long, Resolution has Xcase to basically automate it for you. At the same time, it doesn’t alter your original DDS-based databases, and thus doesn’t throw everything out of whack.

“The System i is a wonderful database machine because the database is built right into the operating system,” Muyal said. “It works much more smoothly and in a powerful way than when trying to combine hardware, OS and database technology from different vendors. But the database solutions are scarce.”

In addition to the software product, which you’ll be able to buy in January, Resolution is offering a modernization service on the System i right now.

Excel Program Inventions has upgraded its Database Assistant software for the System i to include Control Language (CL) support and a feature to generate SQL for changed files.

Version 2.2 of EPI’s database change management software, called Database Assistant, is available now. Its goal is to reduce the manual programming effort needed to make changes to database files and RPG applications on the System i.

In a recent conversation with software provider GridApp about the release of the latest version of GridApp’s flagship database automation software, Clarity 4.0, CEO Rob Gardos and Chief Scientist Matthew Zito described their product as “out-of-the-box.” But does anyone really believe that there is such thing as a true out of the box tool? As the briefing went on, it became clear that GridApp is getting closer to such a deployment with their online model-based best practices configuration.

Essentially, admins must create profiles (models) of their existing configuration and when Clarity runs, it lets you know what isn’t set up correctly according to their online best practices support, which you can then correct. And that’s it. Your database management is automated. Of course, I’m simplifying, but the point remains that although System i admins do not need to create and maintain any new scripts (thus, the out-of-the-box tagline), they still need to do some system modification.

Gardos and Zito said that Clarity is for organization that need to manage a minimum of 50 databases and that the product is intended for a enterprise-level organizations. But they point out that it’s not uncommon and it doesn’t take long for businesses to reach the 1000 database threshold.Clarity is cross-platform software, meaning that it can be used on Windows, Linux, Solaris, System i, pretty much anything. There are a few systems that aren’t yet fully functional, though. Support for zLinux isnt’ ready . . . yet.

If you use Clarity or any other database automation software, we’d like to know about it. You can always post comments to this blog or send me an email. We’d be interested to know your experience with automation or any other systems management tools and strategies.

IT Jungle has some good reader feedback about IBM’s decision to “yank support for DB2/400…as a data store for Notes and Domino databases…” Here’s one user comment:

I note with disappointment IBM’s decision to not fully support Lotus/Domino on DB2/400. Although this decision does not affect a huge swath of your customers, it creates an impression that IBM does not fully support the System i product, DB2, or both. The purchasing committee has noticed.

Check out all the feedback and then let me know what you think.

Actually, are all relational DBMSs dead? Maybe not yet, but according to a recent blog, they’re past their time.

Michael Stonebraker over at The Database Column argues that RDBMSs like DB2 and Oracle have outdated designs that don’t perform as well as SQL DBMSs today that are column-oriented:

In short, the world of 2007 is radically different from the world of the late 1970s. However, none of the major vendors have performed a complete redesign to deal with this changed landscape. As such they should be considered legacy technology, more than a quarter of century in age and “long in the tooth”.

In every major application area I can think of, it is possible to build a SQL DBMS engine with vertical market-specific internals that outperforms the “one size fits all” engines by a factor of 50 or so.

Does Mr. Stonebraker have any biases? Well, of course! He is the co-founder and CTO of Vertica Systems, which sells a column-oriented DBMS. Still, the guy is no slouch, as he was the main architect of Ingres and PostgreSQL databases.

That hasn’t stopped the lively discussion that Stonebraker’s post has caused. One development manager has already written that the RDBMS was dead; he later added another post saying that while not dead, RDBMS is legacy.

And as you can expect, there has been plenty of opinions differing from Stonebraker’s bold claim. One IBMer said that all the claims about older, well-run technologies dying out are getting old:

My concern is with someone once again announcing that a popular, highly used, highly effective piece of technology is on its way out the door. Heck, IMS is heading for Version 10 and growing and we still have a herd of shops running pure VSAM.

Another IBMer wrote that these claims are similar to the promise of flying vehicles and automatic tooth-brushers in The Jetsons. A third IBMer, in addition to disagreeing with Stonebraker, calls the post a “marketing shill.” Obviously IBM has a hand in this game as well.

What do you think? Have you explored DBMSs other than DB2 and Oracle, or are those working just fine, thank you very much?

One System i user I recently talked to would like to run all his Oracle software — both the databases and the E-Business Suite — on the same hardware and software platform. And seeing as he is a senior System i technical analyst, he would like that hardware platform to be the i5.

Therein lies the problem. The Oracle certification matrix is a guessing game, according to this person. First off, Oracle doesn’t certify its Database Server or E-Business Suite to run on i5/OS. It does certify both to run on AIX, which can be carved into a partition of its own on the System i, but the database team wants to run Oracle applications on Linux. Why? According to this person, that’s what Oracle recommends and besides, that’s what they’re familiar with anyway.

OK, so run them on Linux on Power, right? Wrong. Oracle has certified Oracle Database Server to run on Linux on Power, but not the E-Business Suite. So now this person isn’t sure what to do. Oracle Database Server will likely get migrated off the System i and onto x86 unless IBM and Oracle can come together and figure out how to certify the E-Business Suite on Linux on Power.

Who else out there is dealing with these kind of software certification issues? Let me know about them because I’d like to write a full story.

The recent release of Lotus Notes 8 was cause for celebration for many, but not for i5/OS users looking to do some DB2 work with it. According to Domino Blog blogger and IBM Domino product manager Rob Ingram, there are currently no plans to support Notes databases in DB2 on System i. This news is not sitting well and System i user group Common is starting an initiative to get to the bottom of this.

They are currently circulating a System i user survey that will help them build a case to get some answers from IBM. Ingram states in his blog that IBM is currently allocating resources elsewhere, but I just can’t help thinking that this is a move to position Lotus Notes for growth; but at the same time position System i as a legacy platform that will lose full support sometime soon.