Managing big data requires making some big decisions: how to store, query, and report on the constantly increasing stream of data. Although the practice of ‘data at scale’ or table partitioning is popular on IBM i, it creates a partitioning paradox that often adds complications to efficiently querying data.
What are the Benefits of Table or Range Partitioning?
Partitioning allows administrators to welcome more data into a single environment, and more easily support continuous updates and migrations. In fact, as many as 32,767 data partitions can be created on IBM i. Without partitioning, there are limits to the amount of data a table can store. By dividing the contents of the table into multiple partitions, you can effectively create databases that are virtually unlimited in size.
Are There Drawbacks to Creating Partition Files?
While partitions help IBM i users stay under storage limits, complexity quickly becomes an issue when overseeing all of these micro assets. With Query/400, the IBM i query standard, users are only afforded access to one partition member or file at a time. As a result, if three partition members are sitting on the same system, three or four queries will be required to retrieve them.
This is not the only issue presented by relying entirely on legacy software; administrators face the prospect of significantly longer query processing time, or incomplete and possibly inaccurate business intelligence. Even assuming that legacy software could more easily provide access to multiple partitions, legacy tools rely on the Classic Query Engine (CQE), which is much slower than the newer SQL Query Engine (SQE).
How Do You Solve the IBM i Partitioning Paradox?
Dividing your data into multiple partitions might make sense for data storage, but become illogical when it comes to accessing that data. This creates a partitioning paradox. That’s where data access tools such as SEQUEL come in. SEQUEL not only handles multipartitioned queries well, it easily maintains its fast processing speed (thanks to the SQE) and smooth end user access.
SEQUEL is a great tool for data access on IBM i because it provides:
- Access to multiple partitioned files from a single request
- Advanced query & reporting
- Real-time, dynamic data access
- Visualizations for complex data sets
- Graphical user interface options for non-technical users
These core features alone ensure that your organization can handle a huge amount of data faster than you would with a legacy solution, and it gives you the opportunity to store, sort, and query your data in as many partitions as you desire.