New Conference Specializes in DB2 for i and SQL

April 19, 2011 Dan Burger

SQL is not shorthand for squirrel. And if that’s just about all you know about it, you need to get out a little more often. Companies that haven’t changed the design, configuration, and implementation of their database systems since the cell phone was invented have a few lessons to learn about IBM DB2 for i (DB2/400) and SQL. Coincidentally, that’s what the folks at System i Developer had in mind for a new conference called DB2 for i Directions.

System i Developer is the same group that has presented the popular RPG & DB2 Summit conference twice a year since the spring of 2007. The partners in this organization are the well-known educators Susan Gantner, Jon Paris, Paul Tuohy, and Skip Marchesani.

With their latest endeavor, they’ve focused an agenda relevant to developers, database engineers, and database administrators. The emphasis will be on IBM i’s database capabilities, SQL programming techniques, performance management, and query and reporting methods.

The conference–scheduled for June 20 through 23 at the IBM Innovation Center in Waltham, Massachusetts–is configured with several options for attendees. There are two days of classroom education, led by Marchesani and three DB2/SQL experts from IBM’s DB2 for i Center of Excellence: Mike Cain, Kent Milligan, and Gene Cobb. Each day features two tracks of sessions detailing the latest in DB2 and SQL tips, techniques, and strategies. In addition, Skip Marchesani leads a workshop on SQL basics June 20 and Mike Cain leads a second workshop on IBM i’s built-in SQL performance tools June 23. The two workshops are priced separately from the main two-day conference.

The conference early registration (through May 6) fee is $1,195. The workshop registration fee is $395 for each session and requires conference registration.

Benefits of SQL and DB2 for i

The traditional view of data, databases, and applications was all about storing and processing data. There’s a certain amount of tunnel vision that has companies blind to the bigger picture, which is developing data into more readily usable business information.

During a telephone conversation with Kent Milligan last week, I got his input on the benefits SQL provides. Milligan has made presentations on database innovations in the IBM i world at a couple of user group conferences since the beginning of the year. He says the enthusiasm at both events indicate SQL is gaining awareness and is being used within the IBM i community.

“People had to be turned away from those sessions because the rooms weren’t big enough,” Milligan says. “People were sitting on the floor after the chairs were all filled for a session on advanced SQL at the Northeast Users Groups Conference, there were about 40 in the room. At the Dallas Metro Midrange Systems Association user group they had 60 seats to fill for an all day session on SQL and they turned people away.”

It’s been a long process of raising the awareness of SQL and the benefits it brings in DB2 for i environments. And it’s fair to say modern database techniques are far from being described as widespread. Milligan points out that since the introduction of OS/400 V5R1 it’s been clear that IBM was putting its database development emphasis on SQL.

“There have been about a half-dozen enhancements to DDS and native record-level access interfaces since V5R1 versus 70-plus enhancements on the SQL side,” he notes. “People are beginning to realize their jobs will only get more difficult if they aren’t leveraging some of the things that SQL does to allow the database to do more of the work that developers have done in the past.”

Milligan says two of the factors that come to the surface when people start looking at SQL are improved performance–particularly things that are query related–and faster delivery on IT requirements, because the logic gets defined once in the database allowing all the interfaces and applications (and, of course, the developers) to benefit from that. Traditionally developers had to duplicate the business logic in every new application.

“I see a lot of people prioritizing database projects by first moving the database definitions from DDS to SQL, because the definition side of SQL is simple to learn,” Milligan says. “It gives them a foundation for using new SQL data types that can be exploited by new applications.”

Tips and techniques are always appreciated when just starting out and Milligan shared one bit of advice based on experience that would be smart to keep in mind.

“It’s great to learn and program with SQL, but you need at least one person on the team who will be focused on the SQL performance side,” he says. “Make sure that’s part of the skills plan. You need someone on your team that knows which SQL performance tools to use and how to analyze the data. Otherwise people get frustrated because SQL doesn’t perform as well as RPG with native record-level access. And then some people give up on SQL or limit it to just creating database objects.

“I remind RPG developers that when they started to learn RPG, they didn’t know all the performance tricks and best practices. SQL is no different,” Milligan continues. “It is a programming language and there are best practices and skills that relate to optimizing performance.”

Developers can learn syntax from a lot of different places, but every database is going to have its set of best practices from a performance perspective and developers need to keep performance in mind.

“Also keep in mind that SQL can replace native record-level access,” Milligan says. “However, replacing one line of native RPG data access with one line of SQL is a common mistake made by a lot of developers. That means they are using SQL for record-at-a-time programming and although that functionally works, it’s not going to scale as the size of the database increases or the number of users increase.

“SQL is a set-based language designed to process multiple rows of data a time instead of one row,” he continues. “I tell people to look at a whole set of code and think about how those native operations can be consolidated into one SQL statement rather than individual SQL statements.”

For more information on the DB2 for i Directions conference, including a sessions grid, click here.

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search