banner1

Why Modernize to SQL? Print E-mail

Did you know that modernizing your traditional database to SQL is part of achieving better application architecture as recommended by IBM's System i Developer Roadmap? It's the recognized standard for data access. And for good reason.

According to the authors of IBM's Redbook Modernizing iSeries Application Data Access, greater speed and performance is just one key benefit to moving to SQL. You also prepare your applications to evolve in a wider variety of ways, increase data security and data integrity, reduce future development time, and open your database to greater interoperability with other applications as well as reporting, design and management tools.

Learn More

When you modernize using Xcase for i, the process is fast, easy, and free of risk. You bring the power of SQL to your database automatically while your existing applications continue to function using native I/O techniques. There's no disruption to your business. When finished, you're fully prepared to leverage new SQL functions within your applications whenever the need arises. By modernizing to SQL using the Xcase method, you greatly increase the life expectancy of your existing applications by improving your ability to reuse components.

Overall, modernizing your database to SQL brings these advantages:

Built-in Data Integrity

Traditionally on the AS/400 and IBM i, programs dictate database integrity rules. With SQL, we move these business rules out of the application and into the database. According to the IBM Redbook, built-in data integrity gives you “several advantages:

  • Less coding required, because the rules do not have to be written in the program, making the program smaller and therefore easier to understand and maintain.
  • Better performance, because the DBMS handles these rules faster than a user-written application program.
  • Better portability, because the business rules are not hidden in the program but a part of the database.
  • More security: Business and data integrity rules defined in the database provide more security because they cannot be circumvented by a faulty or incompletely written application.

In addition, once these relationships and rules are defined to the database manager, the system automatically ensures that they are enforced at all times, regardless of the interface used to change the data.” (excerpted from IBM Redbook Modernizing iSeries Application Data Access.)

Snappier Performance

SQL speeds batch and interactive processing in several ways. First, the SQL optimizer and database engine alone produce faster response times than native I/O methods under most circumstances – roughly 20% on average. With SQL, data verification happens when data is written, not when it’s read. It's no wonder that write-validation has become a best practice – we typically spend 25x more time reading than writing data. When developers take the next step and move all data verification logic into the database, performance spikes again. And as developers learn to leverage SQL's “set-at-a-time” processing, you can expect even greater performance. Finally, IBM has formally stated that it will continue to improve database access only through SQL.

Increased Functionality

SQL allows you to implement several functions not supported by traditional DB2 for i databases, including:

  • Improved performance
  • Data integrity
  • Auto-incrementing of keys (column identifiers and sequence objects)
  • Column-level triggers
  • Encryption and decryption functions
  • Encoded Vector Indices
  • Large character and binary object support (BLOB, CLOB, DBCLOB, and datalink)

For additional detail on these SQL functions, see Chapter 2 of Modernizing iSeries Application Data Access.

Openness to SOA and other applications

If your company is moving towards Service Oriented Architecture (SOA), or if you’re looking at modernizing your applications, SQL will likely be your primary method for accessing data. Because SQL is the most widely used standard, migrating your database to SQL should be the first step in any modernization project you undertake. Your primary business applications and database can remain on the secure and reliable IBM i, even if your modernization plans call for accessing your data through applications running on other platforms.

Easier reusability of existing applications

When you modernize your database to SQL, you automatically increase the life expectancy of your existing applications. You’ve just laid the foundation to build additional SQL functionality into your programs, making it more likely that they will be re-used as your company moves forward with new development plans.  

Support from modern development tools

By restructuring your database to SQL, you’ll have vastly greater choices of tools that can significantly speed up the development process for you. For example, most advanced third-party development tools, reporting utilities and database design/modeling solutions support SQL. By comparison, very few support DDS-generated databases. Most Web-based application development tools, in particular, offer built-in support for data access through SQL in terms of generating all necessary SQL code for you.

Request More InfoGet the Free Diagnostic

 

Apples and Oranges?

lil appleNot at all!

DDS and SQL live in harmony when you use Xcase for modernization.

IBM White Paper

IBM's Kent Milligan discusses the many advantages of leveraging SQL within DB2 for i in his new paper
DDS & SQL: The Winning Combination for DB2 for iGet it here!

On-Demand Webcast

From DDS to SQL: How to fully exploit DB2 on i with DB2 expert Mike Cain. Learn why SQL is now the IBM i standard for defining and accessing database objects. View it here!

SQL Functionality

SQL Functions That Require Database Modernization

  • Improved performance
  • Data integrity
  • Auto-incrementing of keys (column identifiers and sequence objects)
  • Column-level triggers
  • Encryption and decryption functions
  • Encoded Vector Indices
  • Large character and binary object support (BLOB, CLOB, DBCLOB, and datalink)

For more details on these SQL functions, see Chapter 2 of Modernizing iSeries Application Data Access

Additional Resources

IBM REDBOOK:  
Modernizing IBM eServer iSeries Application Data Access

Business Strategy Bumps Into Database Deficiency
by Dan Burger
IT Jungle (November 2011)

Accessing Data Using SQL Views
by Dan Cruikshank
System iNEWS (November 2007)

Database Management Essentials
by Kent Milligan
System iNEWS (October 2007)

RPG Rules!
by Scott Klement
System iNEWS (October 2007)
 
Using DB2 Web Query with SQL Views and Stored Procedures
by Gene Cobb
System iNEWS (October 2007)

Performance Comparison of DDS-Defined Files and SQL-Defined Files
by Dan Cruishank
System iNEWS (May 2005)

Best Practices

According to IBM, transitioning your System i database to SQL is standard best practice. Learn More...