Moving to SMS to surpass 4Gb (z/os v11)

Art McEwen

Moving to SMS to surpass 4Gb (z/os v11)
We've got a partition by range tablespace that's knocking on the 4Gb limit on some of it's partitions. We're moving to SMS managed storage which will solve that but I'm not clear on the logistics. Can I alter the STOGROUP (to an SMS managed one) then alter the DSSIZE at the same time and reorg just once or do I have to reorg/move it to SMS first and then reorg again to expand? DSSIZE has to be the only thing altered in it's alter statement but does that imply each requires a stand alone reorg to materialize?

Thanks,

Art McEwen

Sr DBA, Database & Mainframe Support
Payment & Registration I&IT Solutions Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

[login to unmask email]<mailto:[login to unmask email]>

Office 613-548-6622
Cell 613-539-3903

Lizette Koehler

Moving to SMS to surpass 4Gb (z/os v11)
(in response to Art McEwen)
Make sure your stg team help you with this



The vsam file needs to be defined with ea/ef



That allows the vsam linear file to beyond 4gb



Lizette





From: McEwen, Art (MOHLTC) <[login to unmask email]>
Sent: Wednesday, August 28, 2019 1:53 PM
To: [login to unmask email]
Subject: [DB2-L] - Moving to SMS to surpass 4Gb (z/os v11)



We've got a partition by range tablespace that's knocking on the 4Gb limit on
some of it's partitions. We're moving to SMS managed storage which will solve
that but I'm not clear on the logistics. Can I alter the STOGROUP (to an SMS
managed one) then alter the DSSIZE at the same time and reorg just once or do I
have to reorg/move it to SMS first and then reorg again to expand? DSSIZE has
to be the only thing altered in it's alter statement but does that imply each
requires a stand alone reorg to materialize?



Thanks,



Art McEwen



Sr DBA, Database & Mainframe Support

Payment & Registration I&IT Solutions Br.

Health Services Cluster

4th flr, 49 Place d'Armes

Kingston ON K7L 5J3



[login to unmask email] <mailto:[login to unmask email]>



Office 613-548-6622

Cell 613-539-3903





-----End Original Message-----

Kai Stroh

RE: Moving to SMS to surpass 4Gb (z/os v11)
(in response to Art McEwen)

Here's how I would approach that. *DEFINITELY* try this on a sample object first. I think this method only has a very small impact on the availability because for a brief moment the tablespace needs to be stopped and a SHRLEVEL CHANGE REORG is required, but I can't be responsible if something goes wrong and there is a big outage of a production tablespace.

That said, first of all you will need an SMS data class that has the following settings:

Data Set Name Type . . . . . : EXTENDED
If Extended . . . . . . . . : REQUIRED
Extended Addressability . . : YES

This is required so that the VSAM on file system level can grow beyond 4 GB. You now need to make Db2 use that data class for the VSAM clusters. This will only work for new VSAMs. I think there is no way to change the data class of an existing VSAM. Therefore you will need to have Db2 re-allocate them (by running REORG).

To make sure that newly allocated VSAMs use the data class, you can change the ACS routines so that it is automatically assigned to all new VSAM data sets under the first level qualifier that the tablespace's VSAMs use. This has the advantage that the next REORG which allocates a J001 data set will automatically use the new data class, but the drawback is that it will affect other objects whose VSAMs also use that FLQ. That's why I would not use that approach. Instead, create a new Db2 storage group and only assign it to the partitions of the tablespace that you want to grow.

Problem is, changing the Db2 storage group of a tablespace is an immediate change. If you do that (which, by the way, only works while the tablespace is stopped) and the new storage group uses a different VCAT prefix, then the next REORG will fail with error DSNPCNP0 (ERROR IN ICF CATALOG LOCATE FUNCTION FOR data-set-name) because the REORG utility will try to locate the old VSAM cluster under the new first level qualifier. I think even doing a SELECT after changing to a storage group with a different VCAT prefix can result in Db2 not finding the VSAM data set, but I have not tried that.

So you need to create a new Db2 storage group - let's call it EXTSG - that uses the same VCAT prefix and explicitly specifies your newly created SMS data class. The statement would look like this:

CREATE STOGROUP EXTSG
VOLUMES(*)
VCAT same-old-vcat-prefix
DATACLAS your-new-extended-dataclass;

After that, stop the tablespace, assign the new storage group EXTSG to all partitions:

ALTER TABLESPACE dbname.tsname USING STOGROUP EXTSG;

Start the tablespace, then execute this:

ALTER TABLESPACE dbname.tsname DSSIZE 64 G;

You will get SQL Warning +610 because this is a pending change (you will see an entry in SYSIBM.SYSPENDINGDDL). Now run REORG with SHRLEVEL CHANGE. The REORG utility will allocate new VSAMs and they will use the new data class with extended addressability, and the DSSIZE will also be changed to 64 G in the same REORG job. I think REORG will end with RC=4 because materializing the pending DDL change will invalidate some statistics and the dynamic statement cache.

I think there is no way to avoid the STOP TABLESPACE - can anyone confirm that?

Again, you really want to try this on some dummy objects first to make sure nothing breaks.

 

--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5.
Learn how the Test Data Management Field Guide can help you to improve your own process.

Art McEwen

RE: Moving to SMS to surpass 4Gb (z/os v11)
(in response to Kai Stroh)

Thanks Kai, just what I was looking for.   Setting up my test environment now to mimic my plan, just curious how many reorgs I'd need to plan for, you sort of confirmed my thinking that it's just 1.   I don't mind the stops.   

Edited By:
Art McEwen[Organization Members] @ Aug 29, 2019 - 02:00 PM (America/Eastern)