Databases

Update your DB2 lingo so you don’t sound old

The bloggers over at iDevelop recommend updating your DB2 lingo so all the Oracle and SQL Server folks don’t think you’re from the 1970s.

Why? Well, not just because using the old lingo makes you seem old, but also because it makes DB2 seem old, outdated, and not as powerful as iDevelop believes it to be.

Oracle and other database users are often convinced that what we have is little more than a flat file system on top of which has been cobbled some half-baked database mechanism. If you think  about it, it’s hardly surprising. We constantly talk old-technology terms like  files and records, so we shouldn’t be surprised if others think that that they are the foundation of the system.

Of course nothing could be further from the truth. What we have is a fully relational database system which, when called upon, can cleverly disguise itself as a flat file system! Those of us who use the platform can understand what a terrific advantage this is–but it’s understandable that others would view it with suspicion.

So here is the translation dictionary, with the DB2 word followed by the “updated” DB2 word:

• A library should now be called a schema or collection
• A file should be called a table
• A record should be called a row
• A field should be called a column
• A logical file should be called a view when talking about how the program views the data
• A logical file should be called an index when talking about performance

The database debates: DB2 vs Oracle

This fall we have been examining the power of DB2. Andrew Borts first wrote about what DB2 does on the AS/400. And Joe Pluta contributed a well-received tip comparing DB2 to SQL. Continuing with the examination of DB2 versus competing databases, and in a nod to the current political campaign season, Andrew Borts chose to create a mock debate between DB2 and Oracle.

This content has been written as a humorous endeavor and is not meant to be a political statement. Please read and respond with this in mind.

by Andrew Borts, Contributor

Announcer: Ladies and gentlemen, tonight’s debate format will be a town hall style, with both candidates having limited time to respond. We’ll start the questioning with Oracle. Oracle: What are your advantages, and why are they so important in today’s world of business?

Oracle: First off, I’d like to thank the academy for allowing us to have this debate in public for the first time. To start off, I am available on almost all operating systems …

DB2: Except i5/OS …

Announcer: Please, would the candidates let the others complete their statements… we have certain time limitations!

DB2: Sorry … will I have time to rebut the comments?

Announcer: Yes… of course…

Oracle: As you know, Oracle is world class, and you don’t have to limit yourself to only IBM hardware. I cost less than my opponent, and I’m easy to manage. Using the same criteria, DB2 isn’t world class, and in my opinion, is not enterprise worthy. Just because DB2 is “pal-ing” around with i5/OS – which is certainly not enterprise worthy! We don’t even hardly see advertisements for it! Does IBM still SELL this i5 system??? Because you hear about me frequently, I am obviously far more popular. Why, we practically invented SQL, we’re so good at it. We’ve been around much MUCH longer than DB2, and cater to the Web world in today’s modern times. You don’t see really big applications using DB2 – only Oracle. We’re simply the Maverick of the Database world!

Announcer: DB2 – same question…

DB2: Thank you. And I’d also like the thank the Academy. My opponent would like to convince you that DB2 isn’t available on all platforms, but that’s simply not true – including Linux, Windows, and of course, our flagship the i5 or iSeries. The i5 system is where SQL the common database language was invented back when it was called the “System 38” now I know that Oracle has been on the market a whole two weeks longer than DB2; however, we still invented SEQUEL – which we had to rename SQL, and I noticed that you refer to that name yourself, Oracle. Running DB2 is least expensive on the i5/OS as it is built into the operating system, removing a layer of complexity not necessary. Application systems such as SAP run best using DB2, due to lower costs of ownership, and faster processing using less hardware.

Announcer: This question starts with DB2 — what is the most common misconception people might have about you?

DB2: That we’re not enterprise worthy, and we cost more. To say “enterprise worthy” we need to bring both of us on the same terminology. We support partitioning, database mirroring, and commitment control managed at an operating system level. These optional journals are used to send records remotely to backup systems. Giving more backup options and solutions for our customers. Our reputation may look bad, however many of these excellent solutions are available through third party applications such as DataMirror and iTERA solutions. Our flagship, the i5 server, is also the most reliable computer in the world, giving them the highest repurchase rate of any computer, bar none. If you buy an i5 OS, you’re going to buy one again – over 95% of the time! Who needs to advertise when you your customers are addicted to you!

Oracle: So you admit! Some of your customers are addicted to you!!!

DB2: In as much that they want us more. It’s like Chocolate – too much of a good thing, just makes you happy. In standard and enterprise configurations, we cost less per CPU then Oracle. And on the i5, there is only an operating system fee, and no separate license for the database. It’s built in!

Oracle: How about security! We’re a highly secure Database – how about you?

DB2: The i5 system has plenty of security. We’re even certified “secure” by the government – as a matter of fact, we were the first system certified with a C2 clearance. So our flagship system is secured far more than any system. If your database isn’t a part of the operating system, you’re only as good as the OS securing you. I must tell you, I sleep well at night.

Announcer: Oracle, same question – any misconceptions?

Oracle: None! I utilize triggers, indexes, arrays, commitment control relational tables — all industry standards. Something that we don’t hear much of from DB2!

DB2: [throat clearing] Actually, we have all those functions as well. All of our limitations, are within site of one another. It’s all a matter of preference these days, but it should be about return on investment, and lowering costs.

Announcer: Final Statements — Oracle?

Oracle: Do you want speed? Then you want Oracle! Do you have a Java application? Well then we’re your database! Do you have a legacy application? Then we’re your database as well. Want to make sure you have plenty of solutions for high availability? We’re your guy! I don’t need to waste more time. We’re faster, stronger, and lighter!

Announcer: DB2 — final statement?

DB2: I know you’re all concerned about speed. But looking at the numbers crunched by outside organizations that test the systems you’ll see that we’re speedier then you think. We’re tied with the top two places on the TPC chart – with almost 2 million transactions between us, and Oracle. 2 million. As far as Java, both Websphere and Tomcat are available. So if you want to do open source, we’re there. We support PHP as well with our product, all for low cost. As you know, PHP is Web-based, but years ago, a few of our nerds in the lab invented DB2WWW or Net.Data — delivering DB2 for the Web, back when people thought the Web was a passing phase. As far as highly available systems, with third-party mirroring solutions in place, we’re right there. So we’re really more like twins separated from birth, more than being different. Where there is a fundamental difference is our overall costs. Look at the return on investment. In the long run, you spend less with DB2, and especially the i5 or iSeries. With these troubled economic times, you need DB2 on your side, fighting for you.

Announcer: This ends the debate between DB2 and Oracle. In a moment, George Stephanopoulos will give his analysis of the debates. Right now we’re looking at both Debaters, shaking each others keyboards, and being friendly with one another. Maybe they’d interface well using XML or Soap services — we’ll have to let time tell the rest of this tale.

ABOUT THE AUTHOR: Andrew Borts is webmaster at United Auto Insurance Group in North Miami, Fla. He is a frequent speaker at COMMON and is past president of The Southern National Users Group, an iSeries-AS/400 user group based in Deerfield Beach, Fla.

System i news updates - 10/16/08

Using DB2/400 for MySQL: Alex Woodie at IT Jungle said sources have told her that IBM is working on developing DB2/400 to power MySQL, the open source database. According to the article: “When complete, the new software will enable organizations that run PHP applications running on MySQL on i OS to access data stored in DB2/400, the i OS’s native database, without modifying MySQL or PHP.” I’m sure the YiPs are happy about this.

New IBM storage options for System i: The System Storage 7214 Tape and DVD Enclosure Express works with Power systems running i5/OS V5R4 and IBM i 6.1, as well as AIX and Linux. The 7214 “allows users to externally connect one or two DAT, LTO, or DVD drives to Power Systems servers via SAS connections” and “is designed for use where there is no room for additional storage in the server’s internal bays, and where space is limited or non-existent in rack-mounted servers.”

Blade servers and IBM i 6.1 get friendlier: IBM will now pre-install IBM i 6.1 and pre-configure the Virtual I/O Server (VIOS) on JS12 and JS22 blade servers. VIOS has been a hindrance to some System i shops looking at blades, as “a company that bets its business on midrange platforms can’t pay a premium for blades, shift to VIOS, and move to i 6.1 all in one step. Especially when it is so hard to set up.” Now, hopefully, it is a little easier.

Some System i product news

This is courtesy of the Four Hundred Stuff report from IT Jungle:

• VAI upgrades S2K, its IBM i-focused ERP software for manufacturers and distributors
• NGS releases IQ Connexion to bolster business intelligence on the IBM i
• Vault400 updates backup agent
• ProData upgrades RDB Connect, its remote database management software for the i
• Applied Logic adds new XML and PDF printing capabilities to its DB2 file management software
• Cybra supports IBM i 6.1 with new version of its System i-based barcode and RFID tracking software

Coloring source code with SQL: Error encountered

We recently received a reader question in response to a reader-submitted tip on using SQL on System i to color source code and inline comments. On August 27, 2008, Don sent us this inquiry:

This is in response to a tip published 05/2008 concerning applying color coding using SQL to update SRCDTA in your program source files. I tried it and got the message (STRSQL): Argument *N of function CONCAT not valid. Any advice on this?

I passed it on to the tip author, Narendra Devireddy who responded with this information:

Although I cannot confirm with the details given, it might be because Don’s source file length may be less than 112 (i.e. SRCDTA less than 100 bytes), so when he tries to use SUBSTR for 100 bytes it might be giving this error.

Please let the user know that in the second query ” UPDATE QTEMP/ALIAS# SET SRCDTA = SUBSTR(SRCDTA,1,4)||X’22′||SUBSTR(SRCDTA,6,94) WHERE
SUBSTR(SRCDTA,7,1) = ‘*’”
the second parameter of highlighted SUBSTR should be  6 less than SRCDTA length(i.e src file length-18, as 12 bytes are reserved for SRCDAT & SRCSEQ), and try it out

Using Source files of length 112 is standard these days for RPGLE programs, and therefore I have built the query to fit that standard. Please do let me know the result after the change.

We passed this back to Don and he let us know that in fact, the problem was resolved but through slightly different means:

Thanks for the timely response, the problem was actually that the SQL session attributes was set to “SQL Rules” = *STD instead of *DB2, after the change it worked fine. Thanks for the help.

As always, if you find something in a tip that doesn’t work for you please let us know. Also, if you have general questions there are many helpful people on IT Knowledge Exchange ready to answer your AS/400 questions. Additionally, Narendra recently submitted a follow-up tip for creation of multicolored comment lines that may be of interest.

Adding data from DB2 to Excel

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.

The enhanced DB2 inside V6R1

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.

DB2/400 vs. SQL Server

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.

DB2 DDS and SQL on the System i

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.

Database change mangement software gets upgrade

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.