Data drives business, but the increasing volume of data in today's IT shops complicates maintaining the databases in which to store it all.
Among other quotable quotes, comedian, actor, and writer Stephen Wright once quipped, "You can't have everything. Where would you put it?" That sentiment seems particularly apt when we consider the plight of enterprises that rely on databases. In other words, just about everybody. Just last spring, for example, UBM Tech's Information Week reported that nearly a third of the organizations it polled showed data growth of 25 percent or more each year. Enterprises using IBM i machines are not exempt from these pressures.
Databases: Like an Expanding Waistline with No Diet Plan
As the era of Big Data unfolds and the knowledge that data analysis has passed from an advantage to an absolute necessity, all but the tiniest enterprises face a growing problem: How can I properly maintain all this data? Such concerns are also affecting vendors of products that help with database maintenance tasks.
"Some of our customers have contacted us regarding a steady increase in data volumes," confirms Allen Hartley, CEO of ProData. "They had questions as to what the maximum number of records DBU could handle. We have increased this to just over two billion records. This is only restricted by the largest value that a four-byte binary field in RPG-ILE could handle. We are looking at other possible ways to handle this in the future as well."
In responding to the future demands of Big Data in particular, Stuart Milligan, director of business development at Databorough, notes: "Everything starts with some form of data analysis. Better indexing, architecture and design will be critical. Data model mapping is and will continue to be key (excuse the pun)."
"Big Data will require IT to utilize new strategies to store and manage data," predicts Mark Holm, founder of Centerfield Technology. "Advanced features like partitioning or distributed data will become very important as more data is necessary to keep online in order to support strategic analytics. These technologies require planning and education to pull off—some of which may need to come from the experts at IBM."
Databorough's Milligan sees two major problems for maintaining databases among his company's customers. "Increasingly, customers need to change the size of or add fields. This can cost millions and man years of work if done manually. The direct and inferred impact of such a change alone can take months, the actual changes even longer. Secondly would be understanding, creating, and managing an explicit relational data model.
Data Volume and Application Performance
How large databases can affect application performance is also a concern.
"Our customers face the challenge of optimizing their resources without spending a large number of people doing it," observes Centerfield's Holm. "The two primary challenges are different sides of the same coin. Initially, they need to create indexes to ensure their SQL-based applications and reports run quickly and efficiently. The second challenge is to eliminate unused indexes that are slowing down batch jobs, using memory, taking up disk space, and lengthening backup windows."
The IBM i platform is different in terms of the number of database-specific tuning options," Holm adds. "Unlike other databases, you can't get 'down and dirty' and configure every last performance attribute. Whether this is an 'issue' is in the eye of the beholder of course. IBM has always contended it is a good thing because the system is taking care of obscure and difficult technical details. While true, it does make it harder to really fine-tune if you need to have a process highly optimized."
Databorough's Milligan takes a bit of an exception to this idea. "IBM i shops should have very limited problems with performance with such a powerful box," he maintains.
Data Security Also a Challenge
Other vendors mention problems that touch on data access and data security as being most central to database maintenance tasks.
"I think organizations are finding it more difficult to be able to give users access to the data they want while being compliant with various regulations such as PCI, HIPAA, etc.," explains Brian Pick, sales manager at Linoma Software. "We have noticed that while most of our Surveyor/400 customers are glad to see that it's very easy to graphically work with, view, and export data, they are now making sure that they're able to restrict those functions by user, that there is an audit log of these actions, and the users are only able to view the files and even more specifically certain fields in a file."
"While restrictions on user authority is a good way to prevent unauthorized access to a file and specific fields, the best way to prevent sensitive data from being easily viewed or even stolen is to encrypt these fields. By encrypting these fields, the concerns over a data breach are minimized," Pick adds.
Can MS SQL Server Supplant DB2 and UDB?
Of course, over the long haul some of these problems may change if Microsoft's SQL Server, already paramount in IT shops generally, comes to be dominant in the IBM i world as well. When asked their view on this possibility, the vendors interviewed maintain faith in the longevity of DB2 and UDB.
"Yes, there are increasingly more MS SQL servers, and this is a trend, but it is not a dramatic change," reassures Milligan. “Many customers do it for BI and data warehousing more than anything."
"While we have seen some customers moving completely off the IBM i to Microsoft SQL server, more shops now have multiple servers with various databases. Finding the right tools to maintain those databases has been difficult for them, until they hear about our . . . RDB Connect. With this software they can integrate the remote data into their applications running on IBM i," offers Hartley.
"DB2 continues to be a robust, highly secure database system, but Microsoft has done a much better job of marketing and building tools around SQL Server that make it more visually appealing (especially when you take into account the integration with programming environments)," opines Holm. "Clearly, there has been a long trend of increased use of SQL Server in IBM i shops. While that trend does continue, many shops are finding that the cost advantages are not what they appear and the IBM i is a bargain in terms of database capability and capacity."
Whether the vendors' view of the future of databases on IBM i is correct or not, maintaining databases on the platform will continue to be a growing challenge. A number of software solutions can help with those tasks.
Following is a listing of database maintenance tools designed to work with the IBM i. Please note that the descriptions are brief and don't cover all features each product provides. The links provided will access product pages on each vendor's web site, and you should consult each vendor for the most complete information on each product's capabilities.
Database Maintenance Tools for IBM i
Applied Logic Corporation
FEU is a database editor and data-manipulation tool that lets users view and update database records without special update programs, as well as perform specialized functions on databases such as restoring deleted records, exporting data to other platforms, and converting spooled files to a variety of formats (e.g., HTML, PDF, XML).
Centerfield's HomeRun suite provides a multifaceted set of database maintenance tools that help developers improve SQL performance, optimize DDS logical files and SQL indices, control resources used by queries, and audit data access. Insure/INDEX identifies new database indexes needed and optimizes existing indexes to improve RPG app and SQL performance. Insure/ANALYSIS helps database administrators tune databases to achieve the best performance and availability. Insure/MONITOR alerts operators to database problems and lets them graphically view critical job metrics. Insure/RESOURCES audits query usage and helps IT personnel enforce policies for controlling database and system-resource use. Insure/SECURITY helps security officers protect application data from accidental or malicious damage.
Databorough, a Division of Fresche Legacy
X-Analysis is a multifunctional application suite of application analysis tools that includes utilities for database maintenance-related functions. Among many other capabilities, X-Analysis lets developers perform DDS-to-SQL conversions, achieve stateless service I/Os, diagram data flows, carry out data impact analysis, trace data and program relationships, and document or graphically display those relationships.
Excel Program Inventions
Database Assistant automates processes needed to apply field changes to database files and RPG applications for field expansion projects or adding new fields to a database. It automatically pinpoints and globally applies qualified field changes to DDS source members and data files, RPG source programs, and display and printer files.
EASY VIEW is a programmer utility that simplifies such processes as correcting database files, adding new records, changing existing records, viewing entries from journaled files, and debugging database applications. EASY VIEW also maintains an audit trail of all activities carried out by its users and can export records to column-delimited files that are accessible by any spreadsheet application.
DB2 Connect Unlimited Edition for System i offers database management and development functions, enables database application development using a variety of high-level language frameworks and APIs, and enables query access to DB2 databases residing on multiple platforms or in multiple IBM i partitions. It includes the IBM Data Studio, an Eclipse-based environment for building database objects, queries, and database logic.
DB2 for i is IBM's 64-bit relational database management system designed for Power Systems. Its benefits include scalability, an open development environment that supports a variety of languages (e.g., RPG, Java) and frameworks, and features such as data encryption, self-configuration, self-tuning, and self-healing.
Kisco Information Systems
QRYTool/400 is a utility that lets programmers analyze database queries in specific data libraries and review queries that use any specific file object to troubleshoot problems, improve efficiency, identify change needs, and view query output files.
Quick File Utility/400 is a set of utilities for database activities. It includes a record browse function that shows field names and values, lets users review records in single record mode or in multiple record browse mode, supports both physical files and logical files, helps users update individual records or perform group updates, and provides access to record layouts.
Surveyor/400 provides a GUI that helps both IT personnel and end users work with data, files, libraries, objects, and reports to modify data operations. Authorized users can add, modify, or delete database records; track database changes via audit logs; and automate data transfers and file export/import operations from PCs. Other functions include display of relationships between physical and logical files, display and change of data areas, and file-transfer automation.
PremiumSoft CyberTech Ltd.
Navicat Premium is a database administration tool for servers running Linux or Windows that lets users connect to MySQL, SQL Server, SQLite, Oracle, and PostgreSQL databases concurrently within a single application. Navicat Premium helps users transfer data across various database systems or to a plain text file with designated SQL format and encoding, schedule and run batch jobs for different kinds of databases, import and export data to and from external sources, and build queries and reports. PremiumSoft also offers standalone versions of Navicat that work with just MySQL, SQL Server, SQL Lite, Oracle, or PostgreSQL databases.
ProData Computer Services
The DBU database utility lets users view and modify database records on IBM i and other platforms with a single command. Capabilities include printing database layouts from remote systems, transferring data from remote systems to IBM i databases, and generating green-screen apps to access remote data. An optional plug-in for Oracle/JDE's Enterprise One/World apps is also available.
DBU Audit software lets DBU users track and monitor DBU changes or displays on any file. DBU Audit goes beyond the base DBU product audit-tracking by storing the data in journals rather than spooled files and simplifies the ability to track changes to the database files.
DBUnifier simplifies creation of database maintenance and query programs by offering such features as creating interactive query and maintenance apps, validating database fields and records, enabling exit-point accesses, and enabling creation of user-defined fields.
RDB Connect is a library of utilities that enables record-level access to any remote data of the developer's choosing. It also enables SQL access to remote data from apps written in RPG or other high-level languages, enables execution of any SQL query, and communicates directly with remote databases from any RPG, COBOL, or CL programs.
SQL Audit tracks and monitors modifications made to IBM databases using ProData's SQL/Pro or IBM's SQL, during either maintenance or production use. It uses system journal files to track all data inserts, changes, or deletes.
Raz-Lee's iSecurity DB-Gate enables secure access to any database (e.g., MySQL, Oracle, MS SQL, DB2, Informix, SQLite) or data source (e.g., Excel) that resides on any IBM or non-IBM platform using STRSQL, STRQM, or programs in languages such as RPG and COBOL. It expands IBM i DRDA functions, uses standard SQL syntax, and automatically authenticates users on remote servers.
Database Attender is a database management and archiving utility for Lotus Notes/Domino databases. The product offers help with infrastructure browsing, rule enforcement, information collection, document-retention activities, and maintaining control over database settings, usage, and growth.
Discovery Attender for Notes provides a universal search function across all Notes/Domino databases, including mail files, archives, journal files, customized applications, and text files in Windows environments. It can also provide de-duplication of email messages and application documents to simplify results sets, as well as searching text files in Windows environments.
TURNOVER for PDQ converts database data even while end users are currently accessing that data. This function simplifies data updates and conversions without having to take associated applications offline by journaling current user activity and applying it at a more convenient time in the database update process.