In IBM DB2 for z/OS, the REORG TABLESPACE command has an option called SHRLEVEL CHANGE which allows you to change the sharing level of a table space while the table space is being reorganized. The sharing level determines how the table space is shared among different DB2 subsystems.

 

The two main sharing levels are:

 SHRLEVEL REFERENCE: This sharing level allows multiple DB2 subsystems to access the same table space, but each subsystem has its own copy of the data. This means that changes made to the data by one subsystem are not visible to the other subsystems until the table space is reorganized.

 

 SHRLEVEL CHANGE: This sharing level allows multiple DB2 subsystems to access the same table space, and changes made to the data by one subsystem are immediately visible to the other subsystems.

 

When you use the SHRLEVEL CHANGE option during a REORG TABLESPACE command, the table space is reorganized and the sharing level is changed at the same time.

 

For example, the following command can be used to change the sharing level of a table space called "MYTABLESPACE" to SHRLEVEL CHANGE while the table space is being reorganized:

REORG TABLESPACE MYTABLESPACE SHRLEVEL CHANGE

It's important to note that changing the sharing level of a table space can have an impact on the performance of the database and it's important to test the performance of the table space

SHRLEVEL REFERENCE is a sharing level that allows multiple DB2 subsystems to access the same table space, but each subsystem has its own copy of the data. This means that changes made to the data by one subsystem are not visible to the other subsystems until the table space is reorganized.

 

When you use the SHRLEVEL REFERENCE option during a REORG TABLESPACE command, the table space is reorganized and the sharing level is changed at the same time.

 

For example, the following command can be used to change the sharing level of a table space called "MYTABLESPACE" to SHRLEVEL REFERENCE while the table space is being reorganized:

REORG TABLESPACE MYTABLESPACE SHRLEVEL REFERENCE

It's important to note that changing the sharing level of a table space can have an impact on the performance of the database and it's important to test the performance of the table space after the change to ensure that it meets the desired requirements.

 

Also, SHRLEVEL REFERENCE is useful when you want to isolate different subsystems and prevent them from seeing each other's changes. This can be useful in a development or testing environment where different teams are working on different tasks and need to work independently.

Considerations / Limitation on PGB and PBR

 

Partition by growth has some limitations that should be considered when deciding whether to use this partitioning method:

 

1. Increased complexity: Partition by growth can add complexity to the database design, management, and maintenance. It requires a good understanding of the data, the access patterns, and the growth patterns of the table.
2. Performance impact: Partition by growth can have an impact on the performance of the database. The creation of new partitions and the maintenance of historical partitions can cause increased I/O and CPU overhead.
3. Storage limitations: Partition by growth can require a large amount of storage, especially if a large number of historical partitions are retained. This can increase the cost of storage and make it harder to manage the data.
4. Query complexity: Partition by growth can make queries more complex, especially when querying historical partitions. The query optimizer may have to search multiple partitions to find the required data, which can slow down query performance.
5. Archiving and purging: Partition by growth requires the archiving and purging of historical partitions to keep the table size manageable. This process can be time-consuming and requires additional resources.
6. Limited scalability: Because partition by growth is based on predefined thresholds, it may not be able to adapt to unexpected changes in the growth rate of the data.

It's important to note that partition by growth is not a suitable partitioning method for all use cases. It's best suited for tables that are subject to high levels of insert activity and are expected to grow over time. It's important to weigh the benefits of partition by growth against the limitations and costs before deciding to use this partitioning method.

Partition by range also has some limitations that should be considered when deciding whether to use this partitioning method:

1. Limited query optimization: Partition by range can make it difficult for the query optimizer to determine the best partition to access when executing a query, especially when the query includes a range of values that spans multiple partitions.
2. Maintenance complexity: Partition by range requires the maintenance of the partitioning key and the partitioning range. This can be time-consuming and requires additional resources, especially when new partitions need to be added or when the partitioning range needs to be adjusted.
3. Limited scalability: Partition by range can be limited by the number of partitions that can be created and the size of the partitioning range. This can make it difficult to scale the table as the data grows.
4. Limited data types: Partition by range is limited to certain data types, such as numeric and date data types, which means it can't be used on other data types such as varchar or clob.
5. Data skew: Partition by range can lead to data skew, where some partitions contain significantly more data than others. This can cause performance issues and make it difficult to balance the workload across the partitions.
6. Limited flexibility: Partition by range is based on a predefined partitioning key and partitioning range, which can limit the flexibility of the partitioning scheme.

It's important to note that partition by range is not a suitable partitioning method for all use cases. It's best suited for tables that need to be queried or searched based on specific values or ranges of values. It's important to weigh the benefits of partition by range against the limitations and costs before deciding to use this partitioning method.

PGB - PBR

PGB - PBR

 

 

In IBM DB2 for z/OS, partitioning is a technique used to divide a table into smaller, more manageable pieces called partitions. There are two main types of partitioning methods: partition by growth and partition by range.

 

Partition by growth (also known as "system-period temporal" or "SPT") is a type of partitioning that automatically creates new partitions as the data in the table grows. This method is typically used for large tables that are subject to high levels of insert activity and are expected to grow over time. With partition by growth, new partitions are created based on predefined thresholds, such as a maximum size or number of rows.

 

Partition by range (also known as "partitioned tables") is a type of partitioning that divides a table based on a specific column or set of columns. This method is typically used for large tables that need to be queried or searched based on a specific value or range of values. With partition by range, the table is divided into smaller partitions based on the values in the partitioning column(s), and each partition is assigned a range of values.

 

Partition by growth is used for tables that are subject to high levels of insert activity and are expected to grow over time, while partition by range is used for tables that need to be queried or searched based on specific values or ranges of values. Both methods offer benefits such as improved query performance, increased data manageability, and better availability.

 

Partition by growth is useful when you want to store historical data and you want to keep the current and historical data separate, for example keeping the last year data separate from the data older than a year. This way you can easily retrieve the current data, archive the historical data and delete it if it's not needed.

 

Partition by range is useful when you want to access the data based on a specific range, for example if you have a table that contains sales data and you want to retrieve the sales data for a specific quarter, you can use partition by range on the date column of the sales table. This way you can easily access the data for a specific range of dates without having to scan the whole table.

 

Both partition by growth and partition by range have their own advantages and disadvantages, and the choice between them depends on the specific use case and the requirements of the application. In general, partition by growth is more suitable for tables that are subject to high levels of insert activity and are expected to grow over time, while partition by range is more suitable for tables that need to be queried or searched based on specific values or ranges of values.

 

When implementing partition by growth on DB2 z/OS, it's important to consider the following:

 

•          The partitioning column or columns, which determine how the table will be partitioned.
•          The partitioning scheme, which determines the number and size of the partitions.
•          The partitioning granularity, which determines the frequency at which new partitions are created.
•          The partitioning history, which determines the number of historical partitions that will be retained.

On the other hand, when implementing partition by range on DB2 z/OS, it's important to consider the following:

 

•          The partitioning column or columns, which determine how the table will be partitioned.
•          The partitioning scheme, which determines the number and size of the partitions.
•          The partitioning range, which determines the range of values for each partition.
•          The partitioning key, which determines the order of the partitions.

It's important to note that both partition by growth and partition by range require a good understanding of the data, the access patterns and the growth patterns of the table. It's also important to consider the impact of partitioning on the overall performance of the database and the cost of maintaining the partitions.

 

Additionally, it is important to test the different partitioning methods and to monitor the performance of the table after partitioning to ensure that it meets the desired performance and scalability requirements.

 

In summary, partition by growth and partition by range are both powerful techniques that can help improve the performance, scalability, and manageability of large DB2 z/OS tables. The choice between the two methods depends on the specific use case and the requirements of the application. Both methods require a good understanding of the data and the access patterns, and both require monitoring and testing to ensure that the desired performance is achieved.