In this article I will be taking a look at a feature we should all be installing, and using, on our IBM i servers.

As I mentioned in a recent article I wrote on the advance job scheduler (which can be found here), IBM announced in May 2022 that half a dozen previously chargeable licensed programs would now be free of charge, if you had a current software maintanence agreement (SWMA). DB2 for i Multisystem was one of those said products.

What is DB2 for i MultiSystem?

DB2 for i MultiSystem is a feature of the IBM i operating system that allows multiple IBM i servers to share a single instance of the DB2 for i database.

This feature enables companies to consolidate multiple IBM i systems into a single system, reducing hardware, software, and management costs.

In this article, we will be taking a look at the partitioning component of DB2 for i MultiSystem.

DB2 for i with partitioning, allows more data to be stored in a table.  Check IBMs documentation for full details.

If you saw my temporal tables presentation at the iUG User Group meetings recently, I mentioned that temporal history tables grow large, very quickly, very easily, and one way to manage them is with the use of DB2 for i partitioning.

No this is not system partitioning, where your Power Server may be sliced and diced into many independent systems, this is database partitioning.  Two totally different concepts.

Database partitioning in DB2 for i, is like a physical file with multiple members. 

Yes, we still have many clients using traditional multi-member files.  As with many things on the IBM i, they have been used for many, many years and are too integrated into their applications to allow change.

One of the great benefits of database partitioning is that it is an excellent tool for archiving.

How do I get it & Install it?

As I mentioned previously, if you have current software maintenance for your IBM i, you have to place an order for this software from either your IBM business partner or IBM directly.

DB2 for i Multisystem is option 27 of the operating system and is installed like any other licensed program.

Using Partitioning

In the example of slicing a table with partitioning that I used in my presentation, a date column in the table is used to split the table into separate partitions.

The SQL alter table statement below, for an existing table, will split the rows into partitions for each year using the transaction date as the partition slicer.

If you need to create a new table, with partitioning, an example is shown in the figure below.

In this example, the employee number is used to partition the table, with the employee number range for the partitioning rules.

Database Operations

As far as SQL operations are concerned, we can run any SQL statement over the table and the database manager will decide which partition it should, or should not, use.  When querying a table, you do not need to know if it is partitioned or not.  No changes to queries are necessary.

If you are using RPG native IO then, just like a traditional multi-member access, you will have to use traditional override database commands (OVRDBF) to access the correct member.

Testing

If I insert a few records into my employee table, the database manager should populate the partitioning members for me.

As far as a green screen is concerned, PDM will show the partitions as a multi-member file, as can be seen below.

Using a quick Display Physical File Member (DSPPFM) for the first partition it shows 2 records.

The second partition shows three records.

And lastly, the third partition has one record.

However, a SQL statement selecting all records, shows, just as we would expect, all records, even though they are in separate partitions.

select * from employee

ACS

As always, using Access for Client Solutions, makes working with partitions a lot easier to recognise.

In the figure below, it shows all the tables in my schema, with the two partitioned tables highlighted.

Then, showing the attributes of the employee table, I can see the full partitioning elements.

Straight away we can see if our table is using partitioning.  ACS is great product I’m sure you are already using.

Conclusion

As well as we think we know IBM, only they know how long these products will be free of charge, place that order now and start using partitioning.  They will make your database even more manageable!

All the examples I have written for this article, and previous ones, can be found on my companies open-source repository on GitHub, which can be found at https://github.com/formaserve/f_Learning

If you have any questions, either on this article, or anything else on the IBM i, please use the comments below, or send me a message on twitter @AndyYouens

Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems with over 40 years IBM midrange experience. 

IBM Champion