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.
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.)
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.
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.