Who is afraid of FL504?

Jorg Lueke

Who is afraid of FL504?

I've heard several users express fears about the deprecation of non-UTS tablespaces. I think the segmented ts conversions have been lagging behind the simple ones.

Who else is afraid of FL504?

Who has gone ahead and were all the non-UTS TS converted?

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/wnew/src/tpc/db2z_fl_v12r1m504.html

Sam Baugh

Who is afraid of FL504?
(in response to Jorg Lueke)
All new tables creations are UTS, and currently working on converting
everything else to UTS. Hoping there will be some help for converting
tablespaces with multiple tables soon (by REORG). We have a few of these.

So far, the biggest pain has been having to REORG the entire tablespace
where many physical tablespace and indexspace datasets exists. The
extended amount of unavailable resource time begins during the LOG phase
and lasts through the UTILTERM phase (even though the DISPLAY DB shows
UTRW). It has been taking about 5 seconds per dataset and the UTILTERM
does these serially, and its just painful to watch. Would be nice if these
happened in parallel. Some of tables had 200+ datasets, so excluding the
log and switch phase, and the UTILTERM phase was about 17 minutes by
itself.

We are still Db2 11 z/OS.

On Mon, Sep 9, 2019 at 9:53 AM Jorg Lueke <[login to unmask email]> wrote:

> I've heard several users express fears about the deprecation of non-UTS
> tablespaces. I think the segmented ts conversions have been lagging behind
> the simple ones.
>
> Who else is afraid of FL504?
>
> Who has gone ahead and were all the non-UTS TS converted?
>
>
> https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/wnew/src/tpc/db2z_fl_v12r1m504.html
>
> -----End Original Message-----
>

Jack Campbell

RE: Who is afraid of FL504?
(in response to Jorg Lueke)

Jorg,

I'm in the same 'state' as Sam, all new tables are UTS-PBG / PBR. Segmented to UTS-PBG conversion in-progress using semi-automated REXX scripts / utilities.

You may want to check out the article below from IBM, which reviews deprecated objects at FL504 - and how to get around it:

https://www.ibm.com/developerworks/community/blogs/897a7c98-57af-4523-9cfa-07ebc3f996b4/entry/You_can_prevent_the_creation_of_new_deprecated_objects_in_Db2_12_function_level_504?lang=en

 

HTH

Jack

David Simpson

Who is afraid of FL504?
(in response to Jack Campbell)
One issue I've encountered is the installation of third party (and sometimes IBM) products that assume the availability of multi-table segmented tablespaces. I've had to change some scripts to add SET CURRENT APPLICATION COMPATIBILITY in order to get them to run. Also had to override APPLCOMPAT on some bind cards.

On Sep 9, 2019 11:41 AM, Jack Campbell <[login to unmask email]> wrote:

Jorg,

I'm in the same 'state' as Sam, all new tables are UTS-PBG / PBR. Segmented to UTS-PBG conversion in-progress using semi-automated REXX scripts / utilities.

You may want to check out the article below from IBM, which reviews deprecated objects at FL504 - and how to get around it:

https://www.ibm.com/developerworks/community/blogs/897a7c98-57af-4523-9cfa-07ebc3f996b4/entry/You_can_prevent_the_creation_of_new_deprecated_objects_in_Db2_12_function_level_504?lang=en



HTH

Jack

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

Walter Jani&#223;en

AW: Who is afraid of FL504?
(in response to David Simpson)
Wouldn't that be the task of the tool vendor? If you are on FL 504 and want to recreate a classic segmented tablespace to run that statement?

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: David Simpson <[login to unmask email]>
Gesendet: Montag, 9. September 2019 19:03
An: [login to unmask email]
Cc: [login to unmask email]
Betreff: [EXTERN] [DB2-L] - RE: Who is afraid of FL504?

One issue I've encountered is the installation of third party (and sometimes IBM) products that assume the availability of multi-table segmented tablespaces. I've had to change some scripts to add SET CURRENT APPLICATION COMPATIBILITY in order to get them to run. Also had to override APPLCOMPAT on some bind cards.

On Sep 9, 2019 11:41 AM, Jack Campbell <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Jorg,

I'm in the same 'state' as Sam, all new tables are UTS-PBG / PBR. Segmented to UTS-PBG conversion in-progress using semi-automated REXX scripts / utilities.

You may want to check out the article below from IBM, which reviews deprecated objects at FL504 - and how to get around it:

https://www.ibm.com/developerworks/community/blogs/897a7c98-57af-4523-9cfa-07ebc3f996b4/entry/You_can_prevent_the_creation_of_new_deprecated_objects_in_Db2_12_function_level_504?lang=en



HTH

Jack

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

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

  • image001.png (2.6k)

Michael Hannan

RE: Who is afraid of FL504?
(in response to Sam Baugh)

In Reply to Sam Baugh:

All new tables creations are UTS, and currently working on converting
everything else to UTS. Hoping there will be some help for converting
tablespaces with multiple tables soon (by REORG). We have a few of these.

So far, the biggest pain has been having to REORG the entire tablespace
where many physical tablespace and indexspace datasets exists. The
extended amount of unavailable resource time begins during the LOG phase
and lasts through the UTILTERM phase (even though the DISPLAY DB shows
UTRW). It has been taking about 5 seconds per dataset and the UTILTERM
does these serially, and its just painful to watch. Would be nice if these
happened in parallel. Some of tables had 200+ datasets, so excluding the
log and switch phase, and the UTILTERM phase was about 17 minutes by
itself.

We are still Db2 11 z/OS.

Sam, 

Yes I see this a pain in the ass. You mean't Switch phase perhaps for delays to objects switching, or Drain ALL part (before last Log Apply iteration). I am not really familiar if UTILTERM phase has a that much work to do.

I worry about the design if running a Reorg has become very impractical. I hope all the tables sharing a TS are small and number of tables is not too ridiculously large. Then it comes down to how much outage is feasible, what method to use. If all applications are shut down I imagine the last phases of the Reorg become much quicker. I assume originally most sites shared a TS to save space for lots of small tables and cut down the total objects to be allocated (only a little since Indexes still needed). Would have made little sense for any large tables. Would be extra silly if the TS had a small Segsize, stuffing up Prefetch. I like 64 myself except for very small objects (64 could waste a lot of space and make Image Copies run longer)

If there is a feasible time to have a short complete application outage, I think that can make the conversion task easier. Create new tables and new indexes, copy data across, etc. (while application still running), use triggers to capture all further application Inserts/Updates/Deletes since copy data, apply them to new tables and check if in sync (to make sure it works). Final application outage required for last apply of changes (just like Reorg would do log apply), and finally rename tables to effect the Switch, REBIND the Packages using the tables. In another env pre-check access paths, so access paths will be pretty much known. Smaller TS could mean choice of undesirable TS scan. Indexes, Tables, Columns can have pretty much the same stats as before. I don't think Tablespace NACTIVEF is really used very much for access path selection, however is noted as an optimizer used column. NPAGESF for tables is more important. 

This is how I go if an application outage was possible for a small time. If that is not feasible, then It becomes so much harder as you describe.

I suppose it is feasible not to convert all tables in the shared TS at the one time, to reduce an outage, but makes for much more long term effort.

If was me, I am not sure I would be waiting too long for relief from IBM unless we know that is coming for certain and soon.

If sites have very active high Insert volume tables in shared tablespaces, that sounds pretty ugly to me. Not sure why a site would have chosen that.

When Richard Yevich suggested one TS per Database years ago (at V2.3  I think), I don't think he was thinking of lots of tables in that one TS. 

In the end, what sort of application outage is possible against the problem TS and what can be achieved in such an outage if any. Would have been nice if Cloned tables were allowed different underlying physical structures, and a bit more flexible to help effect such conversions. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

David Simpson

AW: Who is afraid of FL504?
(in response to Walter Janißen)
Yes, it should be the task of the vendor. They haven't all updated their install jobs yet.

On Sep 10, 2019 12:47 AM, "Walter Jani&#223;en" <[login to unmask email]> wrote:
Wouldn’t that be the task of the tool vendor? If you are on FL 504 and want to recreate a classic segmented tablespace to run that statement?

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: David Simpson <[login to unmask email]>
Gesendet: Montag, 9. September 2019 19:03
An: [login to unmask email]
Cc: [login to unmask email]
Betreff: [EXTERN] [DB2-L] - RE: Who is afraid of FL504?

One issue I've encountered is the installation of third party (and sometimes IBM) products that assume the availability of multi-table segmented tablespaces. I've had to change some scripts to add SET CURRENT APPLICATION COMPATIBILITY in order to get them to run. Also had to override APPLCOMPAT on some bind cards.

On Sep 9, 2019 11:41 AM, Jack Campbell <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Jorg,

I'm in the same 'state' as Sam, all new tables are UTS-PBG / PBR. Segmented to UTS-PBG conversion in-progress using semi-automated REXX scripts / utilities.

You may want to check out the article below from IBM, which reviews deprecated objects at FL504 - and how to get around it:

https://www.ibm.com/developerworks/community/blogs/897a7c98-57af-4523-9cfa-07ebc3f996b4/entry/You_can_prevent_the_creation_of_new_deprecated_objects_in_Db2_12_function_level_504?lang=en



HTH

Jack

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

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

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

Daniel Luksetich

Who is afraid of FL504?
(in response to Michael Hannan)
An IBM solution to multi-table table spaces conversion to UTS is expected late 2020. When Richard advocated one TS per DB it was indeed one table per TS per DB. It was a high availability proposition. It was never implemented in any database design that we worked on together, but he was making a point.

Cheers,

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Michael Hannan <[login to unmask email]>
Sent: Tuesday, September 10, 2019 4:01 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?



In Reply to Sam Baugh:

All new tables creations are UTS, and currently working on converting
everything else to UTS. Hoping there will be some help for converting
tablespaces with multiple tables soon (by REORG). We have a few of these.

So far, the biggest pain has been having to REORG the entire tablespace
where many physical tablespace and indexspace datasets exists. The
extended amount of unavailable resource time begins during the LOG phase
and lasts through the UTILTERM phase (even though the DISPLAY DB shows
UTRW). It has been taking about 5 seconds per dataset and the UTILTERM
does these serially, and its just painful to watch. Would be nice if these
happened in parallel. Some of tables had 200+ datasets, so excluding the
log and switch phase, and the UTILTERM phase was about 17 minutes by
itself.

We are still Db2 11 z/OS.

Sam,

Yes I see this a pain in the ass. You mean't Switch phase perhaps for delays to objects switching, or Drain ALL part (before last Log Apply iteration). I am not really familiar if UTILTERM phase has a that much work to do.

I worry about the design if running a Reorg has become very impractical. I hope all the tables sharing a TS are small and number of tables is not too ridiculously large. Then it comes down to how much outage is feasible, what method to use. If all applications are shut down I imagine the last phases of the Reorg become much quicker. I assume originally most sites shared a TS to save space for lots of small tables and cut down the total objects to be allocated (only a little since Indexes still needed). Would have made little sense for any large tables. Would be extra silly if the TS had a small Segsize, stuffing up Prefetch. I like 64 myself except for very small objects (64 could waste a lot of space and make Image Copies run longer)

If there is a feasible time to have a short complete application outage, I think that can make the conversion task easier. Create new tables and new indexes, copy data across, etc. (while application still running), use triggers to capture all further application Inserts/Updates/Deletes since copy data, apply them to new tables and check if in sync (to make sure it works). Final application outage required for last apply of changes (just like Reorg would do log apply), and finally rename tables to effect the Switch, REBIND the Packages using the tables. In another env pre-check access paths, so access paths will be pretty much known. Smaller TS could mean choice of undesirable TS scan. Indexes, Tables, Columns can have pretty much the same stats as before. I don't think Tablespace NACTIVEF is really used very much for access path selection, however is noted as an optimizer used column. NPAGESF for tables is more important.

This is how I go if an application outage was possible for a small time. If that is not feasible, then It becomes so much harder as you describe.

I suppose it is feasible not to convert all tables in the shared TS at the one time, to reduce an outage, but makes for much more long term effort.

If was me, I am not sure I would be waiting too long for relief from IBM unless we know that is coming for certain and soon.

If sites have very active high Insert volume tables in shared tablespaces, that sounds pretty ugly to me. Not sure why a site would have chosen that.

When Richard Yevich suggested one TS per Database years ago (at V2.3 I think), I don't think he was thinking of lots of tables in that one TS.

In the end, what sort of application outage is possible against the problem TS and what can be achieved in such an outage if any. Would have been nice if Cloned tables were allowed different underlying physical structures, and a bit more flexible to help effect such conversions.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Michael Hannan

RE: Who is afraid of FL504?
(in response to Daniel Luksetich)

In Reply to Daniel Luksetich:

An IBM solution to multi-table table spaces conversion to UTS is expected late 2020. When Richard advocated one TS per DB it was indeed one table per TS per DB. It was a high availability proposition. It was never implemented in any database design that we worked on together, but he was making a point.

Thanks Dan,

I hope IBM solution is going to make it a lot easier..

Yeh I think Richard was making a point about concurrency/availability problems when locking a DBD and logging large DBD change records, when he suggested one TS per Database. Even if tongue in cheek, I thought it was a good idea. I included it as a bit of a joke. Grouping Tablespaces into a database has always been a bit arbitary in my view. A Database seems to be in practice, a somewhat meaningless collection of objects.

I think approx. DB2 V5, Jim Teng was presenting on IBM trying to make DDL changes more concurrent with application operation. Improved availability. More types of ALTERS became possible.

Conversion of multi-table tablespaces is also an availability issue. How to do with it with minimum disruption. With significant outage, it becomes an easy task.

In early days it always surprised me that people wanted tables to share a Segmented tablespace (just to save some space). I could think of table join performance benefits, if the child and parent rows were in the same page (simple TS), if that was organised nicely by a Reorg, but I don't recall anyone really trying it.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Pete Woodman

RE: Who is afraid of FL504?
(in response to Michael Hannan)

In the "early days", it made a lot of sense if you had many small RI related tables to put them all in a single Segmented Tablespace.

It made Copy/Recovery etc a lot simpler.

Pete Woodman

RE: Who is afraid of FL504?
(in response to Daniel Luksetich)

Do you (or anyone) have any idea what IBMs solution will be?

Daniel Luksetich

Who is afraid of FL504?
(in response to Pete Woodman)
It appears to be utility based…most likely REORG. Not entirely sure, but should be a utility solution in the least.



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Pete Woodman <[login to unmask email]>
Sent: Tuesday, September 10, 2019 9:29 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?



Do you (or anyone) have any idea what IBMs solution will be?



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

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

David Simpson

Who is afraid of FL504?
(in response to Daniel Luksetich)
My understanding is that it will be utility based. I'm envisioning something like the reorgss that split up the catalog a few releases ago.

One tablespace goes in and many come out.

On Sep 10, 2019 9:59 AM, Daniel L Luksetich <[login to unmask email]> wrote:
It appears to be utility based…most likely REORG. Not entirely sure, but should be a utility solution in the least.

+--------------------------------------+-----------------------------------------------------------+
| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |
| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |
| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |
| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |
| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |
+--------------------------------------+-----------------------------------------------------------+
[cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email][cid:[login to unmask email]

From: Pete Woodman <[login to unmask email]>
Sent: Tuesday, September 10, 2019 9:29 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?


Do you (or anyone) have any idea what IBMs solution will be?

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

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

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Kim May

Who is afraid of FL504?
(in response to Daniel Luksetich)
Hi Dan! I am working with Surekha to get some promotional content together for the V12 migration webinar you are doing with Golds October 9th (hello world – registration coming soon!)



For the short speakers list your company is listed as “Independent”. Can I (correct) this to DanL Database Consulting?



Sorry to share this with the world…the Baltimore Gas and Electric Company’s decided to do much needed repairs in the middle of a workday so I’m sitting in a bagel shop trying to work!!



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Tuesday, September 10, 2019 11:01 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?



It appears to be utility based…most likely REORG. Not entirely sure, but should be a utility solution in the least.



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Pete Woodman <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Tuesday, September 10, 2019 9:29 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Who is afraid of FL504?



Do you (or anyone) have any idea what IBMs solution will be?



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



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

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Peter Vanroose

RE: Who is afraid of FL504?
(in response to Jorg Lueke)

Actually, even when in FL504, there's no need to get rid of segmented tablespaces! Keep them around until Db2 let you convert them in an easy way.

Essentially, the only thing FL504 does is: disallowing, *by default*, to create new segmented tablespaces.
Which is a good thing, I believe: this potentially avoids, in an early stage, future (expensive) tablespace conversions: viz. when you (by accident) would choose the wrong create tablespace parameters.

But even when in FL504, you *can* still create segmented tablespaces (as was already pointed out): just (temporarily) set the application level of your current SQL session to e.g. V12R1M503.

In Reply to Jorg Lueke:

I've heard several users express fears about the deprecation of non-UTS tablespaces. I think the segmented ts conversions have been lagging behind the simple ones.

Who else is afraid of FL504?

Who has gone ahead and were all the non-UTS TS converted?

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/wnew/src/tpc/db2z_fl_v12r1m504.html

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://abis.be/

Roy Boxwell

Who is afraid of FL504?
(in response to Daniel Luksetich)
I work with companies where doing an ALTER is *not* possible due to just two tables in one database... they really need to go down the road of one TB in one TS in one DB – Not for *all* spaces but the several hundred that kill you when trying to do changes these days...the good old problem of open dataset limit being reached is getting harder to reach these days!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Tuesday, September 10, 2019 1:31 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?



An IBM solution to multi-table table spaces conversion to UTS is expected late 2020. When Richard advocated one TS per DB it was indeed one table per TS per DB. It was a high availability proposition. It was never implemented in any database design that we worked on together, but he was making a point.

Cheers,

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Michael Hannan <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Tuesday, September 10, 2019 4:01 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Who is afraid of FL504?



In Reply to Sam Baugh:

All new tables creations are UTS, and currently working on converting
everything else to UTS. Hoping there will be some help for converting
tablespaces with multiple tables soon (by REORG). We have a few of these.

So far, the biggest pain has been having to REORG the entire tablespace
where many physical tablespace and indexspace datasets exists. The
extended amount of unavailable resource time begins during the LOG phase
and lasts through the UTILTERM phase (even though the DISPLAY DB shows
UTRW). It has been taking about 5 seconds per dataset and the UTILTERM
does these serially, and its just painful to watch. Would be nice if these
happened in parallel. Some of tables had 200+ datasets, so excluding the
log and switch phase, and the UTILTERM phase was about 17 minutes by
itself.

We are still Db2 11 z/OS.

Sam,

Yes I see this a pain in the ass. You mean't Switch phase perhaps for delays to objects switching, or Drain ALL part (before last Log Apply iteration). I am not really familiar if UTILTERM phase has a that much work to do.

I worry about the design if running a Reorg has become very impractical. I hope all the tables sharing a TS are small and number of tables is not too ridiculously large. Then it comes down to how much outage is feasible, what method to use. If all applications are shut down I imagine the last phases of the Reorg become much quicker. I assume originally most sites shared a TS to save space for lots of small tables and cut down the total objects to be allocated (only a little since Indexes still needed). Would have made little sense for any large tables. Would be extra silly if the TS had a small Segsize, stuffing up Prefetch. I like 64 myself except for very small objects (64 could waste a lot of space and make Image Copies run longer)

If there is a feasible time to have a short complete application outage, I think that can make the conversion task easier. Create new tables and new indexes, copy data across, etc. (while application still running), use triggers to capture all further application Inserts/Updates/Deletes since copy data, apply them to new tables and check if in sync (to make sure it works). Final application outage required for last apply of changes (just like Reorg would do log apply), and finally rename tables to effect the Switch, REBIND the Packages using the tables. In another env pre-check access paths, so access paths will be pretty much known. Smaller TS could mean choice of undesirable TS scan. Indexes, Tables, Columns can have pretty much the same stats as before. I don't think Tablespace NACTIVEF is really used very much for access path selection, however is noted as an optimizer used column. NPAGESF for tables is more important.

This is how I go if an application outage was possible for a small time. If that is not feasible, then It becomes so much harder as you describe.

I suppose it is feasible not to convert all tables in the shared TS at the one time, to reduce an outage, but makes for much more long term effort.

If was me, I am not sure I would be waiting too long for relief from IBM unless we know that is coming for certain and soon.

If sites have very active high Insert volume tables in shared tablespaces, that sounds pretty ugly to me. Not sure why a site would have chosen that.

When Richard Yevich suggested one TS per Database years ago (at V2.3 I think), I don't think he was thinking of lots of tables in that one TS.

In the end, what sort of application outage is possible against the problem TS and what can be achieved in such an outage if any. Would have been nice if Cloned tables were allowed different underlying physical structures, and a bit more flexible to help effect such conversions.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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



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

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)
  • smime.p7s (5.1k)

J&#248;rn Thyssen

RE: AW: Who is afraid of FL504?
(in response to David Simpson)

Hi David,

If the vendor happens to be IBM feel free to send me an off-list email.
I can either provide you the APAR number or let you know the status.

In most cases there is an APAR that allows new installs to happen on FL504. A few of the IBM Db2 tools supply migration scripts already, but the majority of the tools will wait for the official engine based solution for migration.

In Reply to David Simpson:

Yes, it should be the task of the vendor. They haven't all updated their install jobs yet.

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2019 IBM Champion.

Views are personal. 

Jorg Lueke

RE: Who is afraid of FL504?
(in response to Jack Campbell)

Thanks Jack, that is a good link

In Reply to Jack Campbell:

Jorg,

I'm in the same 'state' as Sam, all new tables are UTS-PBG / PBR. Segmented to UTS-PBG conversion in-progress using semi-automated REXX scripts / utilities.

You may want to check out the article below from IBM, which reviews deprecated objects at FL504 - and how to get around it:

https://www.ibm.com/developerworks/community/blogs/897a7c98-57af-4523-9cfa-07ebc3f996b4/entry/You_can_prevent_the_creation_of_new_deprecated_objects_in_Db2_12_function_level_504?lang=en

 

HTH

Jack

Jorg Lueke

RE: Who is afraid of FL504?
(in response to David Simpson)

I checked one system recently for deprecated tablespaces with Daniel L's query from this list and saw I was the creator. Well that was ISVs as well creating basic segmented tablespaces. I suppose one could manually add a MAXPARTITIONS keyword into the DDL if one thinks about it.

In Reply to David Simpson:

One issue I've encountered is the installation of third party (and sometimes IBM) products that assume the availability of multi-table segmented tablespaces. I've had to change some scripts to add SET CURRENT APPLICATION COMPATIBILITY in order to get them to run. Also had to override APPLCOMPAT on some bind cards.

Roy Boxwell

Who is afraid of FL504?
(in response to Jorg Lueke)
We changed all of our product tablespaces to be PBGs many years ago... a little ahead of the curve I guess!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Jorg Lueke [mailto:[login to unmask email]
Sent: Thursday, September 12, 2019 12:45 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?



I checked one system recently for deprecated tablespaces with Daniel L's query from this list and saw I was the creator. Well that was ISVs as well creating basic segmented tablespaces. I suppose one could manually add a MAXPARTITIONS keyword into the DDL if one thinks about it.

In Reply to David Simpson:

One issue I've encountered is the installation of third party (and sometimes IBM) products that assume the availability of multi-table segmented tablespaces. I've had to change some scripts to add SET CURRENT APPLICATION COMPATIBILITY in order to get them to run. Also had to override APPLCOMPAT on some bind cards.



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

Attachments

  • smime.p7s (5.1k)

ihillside Wendy

RE: Who is afraid of FL504?
(in response to Pete Woodman)



在回复皮特伍德曼时:

在“早期阶段”,如果你有许多小的RI相关表将它们全部放在一个分段表空间中,那就很有意义了。

它使复制/恢复等更简单。


It’s really easier

https://www.custom-necklace.com

Edited By:
ihillside Wendy[Organization Members] @ Sep 12, 2019 - 04:01 AM (America/Central)

Aurora Emanuela Dellanno

RE: Who is afraid of FL504?
(in response to Daniel Luksetich)

at a recent Db2 user group, a user pointed out that their reason for not going to FL504 was because they had no way to take huge tablespaces offline to convert/migrate them, and they could not do an alter table in one of these tablespaces - they were waiting for the IBM solution that Dan mentions.

Daniel Luksetich

Who is afraid of FL504?
(in response to Aurora Emanuela Dellanno)
The solution I mentioned was for multi-table table spaces. If you have a huge table space then that is a real challenge because you have to REORG the entire table space in a single utility execution. This is a challenge we are facing at one of my clients location.

Cheers,

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Aurora Emanuela Dellanno <[login to unmask email]>
Sent: Friday, September 13, 2019 6:50 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?



at a recent Db2 user group, a user pointed out that their reason for not going to FL504 was because they had no way to take huge tablespaces offline to convert/migrate them, and they could not do an alter table in one of these tablespaces - they were waiting for the IBM solution that Dan mentions.



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

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

David Simpson

Who is afraid of FL504?
(in response to Aurora Emanuela Dellanno)
I think we should be clear about this… Activating FL504 does NOT require you to migrate your legacy objects to PBG or PBR tablespaces. It doesn’t even prevent you from creating them. It just makes it slightly harder to create new legacy tablespaces. I don’t think it should prevent anyone from moving forward. You just need to be aware of the consequences and make sure your 3rd party products are current so they can deal with the change in behavior.

From: Aurora Emanuela Dellanno <[login to unmask email]>
Sent: Friday, September 13, 2019 6:50 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?


at a recent Db2 user group, a user pointed out that their reason for not going to FL504 was because they had no way to take huge tablespaces offline to convert/migrate them, and they could not do an alter table in one of these tablespaces - they were waiting for the IBM solution that Dan mentions.

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

Aurora Emanuela Dellanno

RE: Who is afraid of FL504?
(in response to David Simpson)

David,

 

while "Activating FL504 does NOT require you to migrate your legacy objects to PBG or PBR tablespaces. It doesn’t even prevent you from creating them", it is not correct that "It just makes it slightly harder to create new legacy tablespaces" - it does not just do that; the main problem is any alter required to existing no-longer-supported legacy TBSs - like very large and multi-table TBS.

 

Many users just cannot afford the down time and that is just a huge stumbling block.

J&#248;rn Thyssen

RE: Who is afraid of FL504?
(in response to Aurora Emanuela Dellanno)

Hi,

From the Db2 manual: "Function level 504 introduces capability to prevent the creation of certain deprecated objects in your Db2 for z/OS® environments"

The deprecated objects are still supported. Even simple table spaces that was deprecated more than 10 years ago are still supported, although you can no longer created them. Of course, lots of new functionality only works with UTS  (e.g., temporal tables or certain online changes), but that has been the case since at least Db2 V10.


In Reply to Aurora Emanuela Dellanno:

David,

 

while "Activating FL504 does NOT require you to migrate your legacy objects to PBG or PBR tablespaces. It doesn’t even prevent you from creating them", it is not correct that "It just makes it slightly harder to create new legacy tablespaces" - it does not just do that; the main problem is any alter required to existing no-longer-supported legacy TBSs - like very large and multi-table TBS.

 

Many users just cannot afford the down time and that is just a huge stumbling block.



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2019 IBM Champion.

Views are personal. 

David Simpson

Who is afraid of FL504?
(in response to Aurora Emanuela Dellanno)
I don't understand your response.

I understand that you are worried about the outage required to convert your legacy object.

I do not understand why you think you have to.

On Sep 16, 2019 6:41 AM, Aurora Emanuela Dellanno <[login to unmask email]> wrote:

David,



while "Activating FL504 does NOT require you to migrate your legacy objects to PBG or PBR tablespaces. It doesn’t even prevent you from creating them", it is not correct that "It just makes it slightly harder to create new legacy tablespaces" - it does not just do that; the main problem is any alter required to existing no-longer-supported legacy TBSs - like very large and multi-table TBS.



Many users just cannot afford the down time and that is just a huge stumbling block.

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

Aurora Emanuela Dellanno

RE: Who is afraid of FL504?
(in response to David Simpson)

Jorn and David,

 

if you need to alter any tables or indices and they reside in a deprecated TBS, you will not be able to - this is the problem that some early users have found, and the reason why they are currently unable to implement FL504.

 

Aurora

J&#248;rn Thyssen

RE: Who is afraid of FL504?
(in response to Aurora Emanuela Dellanno)

Not sure I understand.

I'm running FL505 and I can ALTER TABLE and ALTER INDEX on a segmented tablespace without issues.

Same for a table in a multi-table tablespace.

In Reply to Aurora Emanuela Dellanno:

Jorn and David,

 

if you need to alter any tables or indices and they reside in a deprecated TBS, you will not be able to - this is the problem that some early users have found, and the reason why they are currently unable to implement FL504.

 

Aurora



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2019 IBM Champion.

Views are personal. 

David Simpson

Who is afraid of FL504?
(in response to Jørn Thyssen)
Jørn,

Is there an open APAR on this issue?

From: Jørn Thyssen <[login to unmask email]>
Sent: Tuesday, September 17, 2019 4:56 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?


Not sure I understand.

I'm running FL505 and I can ALTER TABLE and ALTER INDEX on a segmented tablespace without issues.

Same for a table in a multi-table tablespace.

In Reply to Aurora Emanuela Dellanno:

Jorn and David,



if you need to alter any tables or indices and they reside in a deprecated TBS, you will not be able to - this is the problem that some early users have found, and the reason why they are currently unable to implement FL504.



Aurora





Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com http://www.rocketsoftware.com

2019 IBM Champion.

Views are personal.

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

David Simpson

Who is afraid of FL504?
(in response to Jørn Thyssen)
Sorry… I meant to ask Aurora.

From: Jørn Thyssen <[login to unmask email]>
Sent: Tuesday, September 17, 2019 1:00 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?


Hi David,

I do NOT have problems, but I don't run a production system.

Jørn

In Reply to David Simpson:
Jørn,

Is there an open APAR on this issue?

From: Jørn Thyssen
Sent: Tuesday, September 17, 2019 4:56 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?


Not sure I understand.

I'm running FL505 and I can ALTER TABLE and ALTER INDEX on a segmented tablespace without issues.

Same for a table in a multi-table tablespace.

In Reply to Aurora Emanuela Dellanno:

Jorn and David,



if you need to alter any tables or indices and they reside in a deprecated TBS, you will not be able to - this is the problem that some early users have found, and the reason why they are currently unable to implement FL504.



Aurora





Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e> • W: www.rocketsoftware.com http://www.rocketsoftware.com

2019 IBM Champion.

Views are personal.

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





Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com

2019 IBM Champion.

Views are personal.

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

J&#248;rn Thyssen

RE: Who is afraid of FL504?
(in response to David Simpson)

Hi David,

I do NOT have problems, but I don't run a production system.

Jørn

In Reply to David Simpson:

Jørn,

Is there an open APAR on this issue?

From: Jørn Thyssen <[login to unmask email]>
Sent: Tuesday, September 17, 2019 4:56 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Who is afraid of FL504?


Not sure I understand.

I'm running FL505 and I can ALTER TABLE and ALTER INDEX on a segmented tablespace without issues.

Same for a table in a multi-table tablespace.

In Reply to Aurora Emanuela Dellanno:

Jorn and David,



if you need to alter any tables or indices and they reside in a deprecated TBS, you will not be able to - this is the problem that some early users have found, and the reason why they are currently unable to implement FL504.



Aurora





Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com http://www.rocketsoftware.com

2019 IBM Champion.

Views are personal.

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



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2019 IBM Champion.

Views are personal. 

Aurora Emanuela Dellanno

RE: Who is afraid of FL504?
(in response to David Simpson)

David and Jorn,

 

I assume so - it is not one of our problems, we are not on Db2 12 yet at all - however at a recent user group meeting in Germany there was an interesting presentation with discussion with one of the ESP sites, shortly after the announcement for FL504, since this was one of the issues they had faced through their testing and they recommended to take care.

 

I will see them probably again next week and point out that other users say they can alter objects, and ask them to give me more detail, if you are interested.

 

Thanks.

 

Aurora