Power

In the days when IBM advised that we should use internal disk drives on IBM i for best performance, the DB2 optimizer would take in to consideration the number of disk controllers and disk arms available and would optimize SQL based up these resources (and other resources too).

Most of my customers are now running on FlashSystem storage with FCMs but still have many small volumes mapped to their IBM i LPARs. For example, one customer has a total of 18TB storage assigned to an LPAR with 78 volume mappings.

I'm looking at reducing the number of volumes to 36 x 500GB or 18 x 1TB. There's a requirement to add more storage in the near future so this LPAR may well grow to 22TB. I'd prefer to add storage in 1TB increments as and when they need this.

My concern is that reducing the number of "disk arms" (volumes) will impact their SQL queries IF the optimizer still tales in to account the number of "disk arms" available. Or has the optimizer been updated to allow for today's faster storage? I can't see the latter as a volume from SVC\Storwze\FlashSystem is always presented as a type 2145.

------------------------------
Glenn Robinson
------------------------------

Glenn,

excellent question - you are in wrong group. This is for IBM Power for AIX.  There is a group for IBM I and DB2 and May be flash systems.

if you google search you will find pdf - redbooks.

Best Practices Guide for Databases on IBM FlashSystem
IBM FlashSystems with IBM i
Db2 Advanced Copy Services (ACS) - IBM

------------------------------
minesh patel
------------------------------

Original Message:
Sent: Thu December 08, 2022 01:37 AM
From: Glenn Robinson
Subject: DB2 optimizer and number of disk arms - still applicable?

In the days when IBM advised that we should use internal disk drives on IBM i for best performance, the DB2 optimizer would take in to consideration the number of disk controllers and disk arms available and would optimize SQL based up these resources (and other resources too).

Most of my customers are now running on FlashSystem storage with FCMs but still have many small volumes mapped to their IBM i LPARs. For example, one customer has a total of 18TB storage assigned to an LPAR with 78 volume mappings.

I'm looking at reducing the number of volumes to 36 x 500GB or 18 x 1TB. There's a requirement to add more storage in the near future so this LPAR may well grow to 22TB. I'd prefer to add storage in 1TB increments as and when they need this.

My concern is that reducing the number of "disk arms" (volumes) will impact their SQL queries IF the optimizer still tales in to account the number of "disk arms" available. Or has the optimizer been updated to allow for today's faster storage? I can't see the latter as a volume from SVC\Storwze\FlashSystem is always presented as a type 2145.

------------------------------
Glenn Robinson
------------------------------

Ooops,

Thanks for spotting that. I've reposted to the IBM i group.

------------------------------
Glenn Robinson
------------------------------

Original Message:
Sent: Fri December 09, 2022 10:19 AM
From: minesh patel
Subject: DB2 optimizer and number of disk arms - still applicable?

Glenn,

excellent question - you are in wrong group. This is for IBM Power for AIX.  There is a group for IBM I and DB2 and May be flash systems.

if you google search you will find pdf - redbooks.

Best Practices Guide for Databases on IBM FlashSystem
IBM FlashSystems with IBM i
Db2 Advanced Copy Services (ACS) - IBM

------------------------------
minesh patel

Original Message:
Sent: Thu December 08, 2022 01:37 AM
From: Glenn Robinson
Subject: DB2 optimizer and number of disk arms - still applicable?

In the days when IBM advised that we should use internal disk drives on IBM i for best performance, the DB2 optimizer would take in to consideration the number of disk controllers and disk arms available and would optimize SQL based up these resources (and other resources too).

Most of my customers are now running on FlashSystem storage with FCMs but still have many small volumes mapped to their IBM i LPARs. For example, one customer has a total of 18TB storage assigned to an LPAR with 78 volume mappings.

I'm looking at reducing the number of volumes to 36 x 500GB or 18 x 1TB. There's a requirement to add more storage in the near future so this LPAR may well grow to 22TB. I'd prefer to add storage in 1TB increments as and when they need this.

My concern is that reducing the number of "disk arms" (volumes) will impact their SQL queries IF the optimizer still tales in to account the number of "disk arms" available. Or has the optimizer been updated to allow for today's faster storage? I can't see the latter as a volume from SVC\Storwze\FlashSystem is always presented as a type 2145.

------------------------------
Glenn Robinson
------------------------------