Wed, Jul
0 New Articles

Technology Focus: Expanding Data Intensifies Database Maintenance Tasks

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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 offsome 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

File Edit Utility (FEU)

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 Technology


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

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.


IBM Corporation

DB2 Connect Unlimited Edition for System i

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

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

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.


Linoma Software


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

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

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

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

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 Security

iSecurity DB-Gate

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.


Sherpa Software

Database Attender

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

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.


SoftLanding Systems


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.



John Ghrist

John Ghrist has been a journalist, programmer, and systems manager in the computer industry since 1982. He has covered the market for IBM i servers and their predecessor platforms for more than a quarter century and has attended more than 25 COMMON conferences. A former editor-in-chief with Defense Computing and a senior editor with SystemiNEWS, John has written and edited hundreds of articles and blogs for more than a dozen print and electronic publications. You can reach him at This email address is being protected from spambots. You need JavaScript enabled to view it..



Support MC Press Online


Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: