[DB2-L] DB2 for z/OS V8 - LOAD utility question

Walter Janißen

[DB2-L] DB2 for z/OS V8 - LOAD utility question
Hi

The ZPARM is RRF. If enabled, you get RRF as soon as you reorg or load replace a table(space). I am not sure, but I think, that the tablespace is put back to BRF, if you disable RRF.

Mit freundlichen Grüßen
Walter Janißen

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

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Satish Srikakulapu
Gesendet: Mittwoch, 15. Dezember 2010 16:29
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Steen,

This brings up a question, that might have been answered but I wanted to know. What is the ZPARM to control this behavior, and can we reconvert tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim - from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the management of these tables by physically move all VARCHAR columns to the end and then have a set of OFFSET's in front of these guys. This will happen at LOAD REPLACE / REORG time, and if you don't want to utilize this feature, there is a ZPARM parameter to control it. You can identify which partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
<mailto:[login to unmask email]>[file:///C:\Program%20Files\CA\GIS\CASig\CA_email.gif ] < http://www.ca.com/ >

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace attribute is being changed if any ?

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.
---------------------------------------------------------------------

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Walter Jani&#223;en

AW: [DB2-L] DB2 for z/OS V8 - LOAD utility question
(in response to Walter Janißen)
And to add: Some features are only possible with RRF: e.g. HASH ACCESS in V10. I am not sure, if RRF is also mandatory for clone tables.

Mit freundlichen Grüßen
Walter Janißen

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

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Myron Miller
Gesendet: Mittwoch, 15. Dezember 2010 17:01
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will eventually prove to be more efficient access to these tables. Not a lot, but some, so why degrade potentially efficiency improvements?

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Steen,

This brings up a question, that might have been answered but I wanted to know. What is the ZPARM to control this behavior, and can we reconvert tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim - from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the management of these tables by physically move all VARCHAR columns to the end and then have a set of OFFSET's in front of these guys. This will happen at LOAD REPLACE / REORG time, and if you don't want to utilize this feature, there is a ZPARM parameter to control it. You can identify which partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
<mailto:[login to unmask email]>< http://www.ca.com/ >

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace attribute is being changed if any ?

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.
---------------------------------------------------------------------

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Satish Srikakulapu

Re: DB2 for z/OS V8 - LOAD utility question
(in response to Walter Janißen)
Myron,

We refresh the data in our test environments by copying the data from production using MS/FTR product. As of now, the tables whose FORMAT is different between our production and test environments are failing to get copied. For MS/FTR product to work, we have to have the structure and FORMAT to be the same between production and test environments. To eliminate the need to reorg or load replace the target environment tables, I was wondering if turning off the ZPARM would be a good idea.

In our experience, not just the tables with VARCHAR columns are getting changed from BRF to RRF but all the tables (and partitions) that are either reorged or load replaced are changing from BRF to RRF. That is also another reason for wondering if we need to turn off this ZPARM.

Can you tell us how and when we might see the benefits of RRF? I know that this might be a very broad question but if you can point me to the documentation that I could read on this, it would be really great.

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, December 15, 2010 9:01 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will eventually prove to be more efficient access to these tables. Not a lot, but some, so why degrade potentially efficiency improvements?

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to know. What is the ZPARM to control this behavior, and can we reconvert tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim – from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the management of these tables by physically move all VARCHAR columns to the end and then have a set of OFFSET’s in front of these guys. This will happen at LOAD REPLACE / REORG time, and if you don’t want to utilize this feature, there is a ZPARM parameter to control it. You can identify which partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
<mailto:[login to unmask email]>Error! Filename not specified.< http://www.ca.com/ >

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace attribute is being changed if any ?

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.
---------------------------------------------------------------------

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Satish Srikakulapu

Re: DB2 for z/OS V8 - LOAD utility question
(in response to Satish Srikakulapu)
Walter,

Thank you for the information.

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Wednesday, December 15, 2010 9:02 AM
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Hi

The ZPARM is RRF. If enabled, you get RRF as soon as you reorg or load replace a table(space). I am not sure, but I think, that the tablespace is put back to BRF, if you disable RRF.

Mit freundlichen Grüßen
Walter Janißen

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

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Satish Srikakulapu
Gesendet: Mittwoch, 15. Dezember 2010 16:29
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to know. What is the ZPARM to control this behavior, and can we reconvert tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim - from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the management of these tables by physically move all VARCHAR columns to the end and then have a set of OFFSET's in front of these guys. This will happen at LOAD REPLACE / REORG time, and if you don't want to utilize this feature, there is a ZPARM parameter to control it. You can identify which partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
<mailto:[login to unmask email]>[file:///C:\Program%20Files\CA\GIS\CASig\CA_email.gif ] < http://www.ca.com/ >

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace attribute is being changed if any ?

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.
---------------------------------------------------------------------

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

James Campbell

Re: DB2 for z/OS V8 - LOAD utility question
(in response to Satish Srikakulapu)
Here is a link to the online documentation for RRF



http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.inst/db2z_summigrcmsteps.htm



Jim Campbell
Sr. Database Administrator
Administrative Office of the Courts

360-704-4015
[login to unmask email]<mailto:[login to unmask email]>

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Satish Srikakulapu
Sent: Wednesday, December 15, 2010 8:54 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Myron,

We refresh the data in our test environments by copying the data from production using MS/FTR product. As of now, the tables whose FORMAT is different between our production and test environments are failing to get copied. For MS/FTR product to work, we have to have the structure and FORMAT to be the same between production and test environments. To eliminate the need to reorg or load replace the target environment tables, I was wondering if turning off the ZPARM would be a good idea.

In our experience, not just the tables with VARCHAR columns are getting changed from BRF to RRF but all the tables (and partitions) that are either reorged or load replaced are changing from BRF to RRF. That is also another reason for wondering if we need to turn off this ZPARM.

Can you tell us how and when we might see the benefits of RRF? I know that this might be a very broad question but if you can point me to the documentation that I could read on this, it would be really great.

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, December 15, 2010 9:01 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will eventually prove to be more efficient access to these tables. Not a lot, but some, so why degrade potentially efficiency improvements?

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to know. What is the ZPARM to control this behavior, and can we reconvert tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim – from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the management of these tables by physically move all VARCHAR columns to the end and then have a set of OFFSET’s in front of these guys. This will happen at LOAD REPLACE / REORG time, and if you don’t want to utilize this feature, there is a ZPARM parameter to control it. You can identify which partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
<mailto:[login to unmask email]>Error! Filename not specified.< http://www.ca.com/ >

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace attribute is being changed if any ?

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.
---------------------------------------------------------------------

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Phil Grainger

Re: DB2 for z/OS V8 - LOAD utility question
(in response to James Campbell)
If you have a lot of very short variable columns, compressed RRF can actually take up more space than the equivalent BRF (due to the fact that column lengths are no longer adjacent to their column data)

This is why the conversion BACK to BRF was introduced

As part of this, support for BRF had to be retrofitted to universal table spaces, so I suspect things that USED TO require RRF at DB2 9 GA time no longer do so

Not sure about hash tables though……
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: 15 December 2010 16:01
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will eventually prove to be more efficient access to these tables. Not a lot, but some, so why degrade potentially efficiency improvements?

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to know. What is the ZPARM to control this behavior, and can we reconvert tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim – from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the management of these tables by physically move all VARCHAR columns to the end and then have a set of OFFSET’s in front of these guys. This will happen at LOAD REPLACE / REORG time, and if you don’t want to utilize this feature, there is a ZPARM parameter to control it. You can identify which partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
<mailto:[login to unmask email]>Error! Filename not specified.< http://www.ca.com/ >

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace attribute is being changed if any ?

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.
---------------------------------------------------------------------

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Myron Miller

Re: DB2 for z/OS V8 - LOAD utility question
(in response to Phil Grainger)
Here's the place to change it back and forth. 'Existing RRF table spaces are
never converted to BRF by the LOAD REPLACE or REORG TABLESPACE utility except
when requested explicitly with the ROWFORMAT keyword" This implies that you can
switch back and forth easily enough.

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.inst/db2z_migr2nfmimplications.htm


We also copy a lot of data into a shadow space for production and to other
environments using DSN1COPY. So we also have to have the FORMAT the same in
both. I've developed some SQL for our production environment copy to compare
the two and I regularly go thru and reorg the mismatches. For the production to
test environments, generally I only worry about those that have problems as we
try to keep them as identical as possible in every way (DSN1COPY as mentioned
has tons of issues if they are not identical).

From what I've seen lots of new features will be using RRF only. That's the
direction IBM has stated to be the future for all tablespaces. And I have a
strong feeling that at some point, that will be the only format supported. So
while I can get ahead of the game, I'm slowly converting all of our tablespaces
to RRF. In production it's a simple conversion. It's more of an effort in the
test environments as we don't have regular reorgs run there. But if I can get
everything converted, I figure that with the X release of DB2, this will be one
item that I don't have to convert. And I've found that by staying ahead of this
makes conversions from one DB2 version to the next much much simpler.

Myron




________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 11:53:35 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question


Myron,

We refresh the data in our test environments by copying the data from production
using MS/FTR product. As of now, the tables whose FORMAT is different between
our production and test environments are failing to get copied. For MS/FTR
product to work, we have to have the structure and FORMAT to be the same between
production and test environments. To eliminate the need to reorg or load replace
the target environment tables, I was wondering if turning off the ZPARM would be
a good idea.

In our experience, not just the tables with VARCHAR columns are getting changed
from BRF to RRF but all the tables (and partitions) that are either reorged or
load replaced are changing from BRF to RRF. That is also another reason for
wondering if we need to turn off this ZPARM.

Can you tell us how and when we might see the benefits of RRF? I know that this
might be a very broad question but if you can point me to the documentation that
I could read on this, it would be really great.

Thanks,
Satish Srikakulapu

From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, December 15, 2010 9:01 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will
eventually prove to be more efficient access to these tables. Not a lot, but
some, so why degrade potentially efficiency improvements?

Myron


________________________________

From:Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to know.
What is the ZPARM to control this behavior, and can we reconvert tablespaces
that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim – from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the
management of these tables by physically move all VARCHAR columns to the end and
then have a set of OFFSET’s in front of these guys. This will happen at LOAD
REPLACE / REORG time, and if you don’t want to utilize this feature, there is a
ZPARM parameter to control it. You can identify which partitions/tablespaces
having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
Error! Filename not specified.

From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much
info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE
CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace
attribute is being changed if any ?

Jim McAlpine


________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.



________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential,
privileged or proprietary information. If you have received it in error, please
notify the sender immediately and delete the original and any copy or printout.
Unintended recipients are prohibited from making any other use of this e-mail.
Although we have taken reasonable precautions to ensure no viruses are present
in this e-mail, we accept no liability for any loss or damage arising from the
use of this e-mail or attachments, or for any delay or errors or omissions in
the contents which result from e-mail transmission.

---------------------------------------------------------------------


________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.



________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Satish Srikakulapu

Re: DB2 for z/OS V8 - LOAD utility question
(in response to Myron Miller)
What you are saying is true. By having all the tablespaces converted to RRF, we would be having one less thing to worry about when the time for migration comes. Even, we think that we should be going in the direction of RRF but the failure with the MS/FTR product made me think twice about RRF.

My understanding of RRF was that if the RRF ZPARM is turned on, all the tablespaces with VARCHAR fields will be converted to RRF when “REORG” or “LOAD REPLACE” happens on that tablespace. But, we are seeing that all the tablespaces that are getting REORG’d are converting into RRF. Is my understanding correct?

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, December 15, 2010 1:12 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Here's the place to change it back and forth. 'Existing RRF table spaces are never converted to BRF by the LOAD REPLACE or REORG TABLESPACE utility except when requested explicitly with the ROWFORMAT keyword" This implies that you can switch back and forth easily enough.

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.inst/db2z_migr2nfmimplications.htm

We also copy a lot of data into a shadow space for production and to other environments using DSN1COPY. So we also have to have the FORMAT the same in both. I've developed some SQL for our production environment copy to compare the two and I regularly go thru and reorg the mismatches. For the production to test environments, generally I only worry about those that have problems as we try to keep them as identical as possible in every way (DSN1COPY as mentioned has tons of issues if they are not identical).

From what I've seen lots of new features will be using RRF only. That's the direction IBM has stated to be the future for all tablespaces. And I have a strong feeling that at some point, that will be the only format supported. So while I can get ahead of the game, I'm slowly converting all of our tablespaces to RRF. In production it's a simple conversion. It's more of an effort in the test environments as we don't have regular reorgs run there. But if I can get everything converted, I figure that with the X release of DB2, this will be one item that I don't have to convert. And I've found that by staying ahead of this makes conversions from one DB2 version to the next much much simpler.

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 11:53:35 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Myron,

We refresh the data in our test environments by copying the data from production using MS/FTR product. As of now, the tables whose FORMAT is different between our production and test environments are failing to get copied. For MS/FTR product to work, we have to have the structure and FORMAT to be the same between production and test environments. To eliminate the need to reorg or load replace the target environment tables, I was wondering if turning off the ZPARM would be a good idea.

In our experience, not just the tables with VARCHAR columns are getting changed from BRF to RRF but all the tables (and partitions) that are either reorged or load replaced are changing from BRF to RRF. That is also another reason for wondering if we need to turn off this ZPARM.

Can you tell us how and when we might see the benefits of RRF? I know that this might be a very broad question but if you can point me to the documentation that I could read on this, it would be really great.

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, December 15, 2010 9:01 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will eventually prove to be more efficient access to these tables. Not a lot, but some, so why degrade potentially efficiency improvements?

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to know. What is the ZPARM to control this behavior, and can we reconvert tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim – from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the management of these tables by physically move all VARCHAR columns to the end and then have a set of OFFSET’s in front of these guys. This will happen at LOAD REPLACE / REORG time, and if you don’t want to utilize this feature, there is a ZPARM parameter to control it. You can identify which partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
<mailto:[login to unmask email]>Error! Filename not specified.< http://www.ca.com/ >

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace attribute is being changed if any ?

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.
---------------------------------------------------------------------

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Walter Jani&#223;en

AW: [DB2-L] DB2 for z/OS V8 - LOAD utility question
(in response to Satish Srikakulapu)
Hello Phil

Not sure about hash tables though......

But I am sure, that RRF is a prerequisite for using hash access

Mit freundlichen Grüßen
Walter Janißen

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

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Phil Grainger
Gesendet: Mittwoch, 15. Dezember 2010 20:57
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

If you have a lot of very short variable columns, compressed RRF can actually take up more space than the equivalent BRF (due to the fact that column lengths are no longer adjacent to their column data)

This is why the conversion BACK to BRF was introduced

As part of this, support for BRF had to be retrofitted to universal table spaces, so I suspect things that USED TO require RRF at DB2 9 GA time no longer do so

Not sure about hash tables though......
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: 15 December 2010 16:01
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will eventually prove to be more efficient access to these tables. Not a lot, but some, so why degrade potentially efficiency improvements?

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to know. What is the ZPARM to control this behavior, and can we reconvert tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim - from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the management of these tables by physically move all VARCHAR columns to the end and then have a set of OFFSET's in front of these guys. This will happen at LOAD REPLACE / REORG time, and if you don't want to utilize this feature, there is a ZPARM parameter to control it. You can identify which partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
<mailto:[login to unmask email]>Error! Filename not specified.< http://www.ca.com/ >

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace attribute is being changed if any ?

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.
---------------------------------------------------------------------

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Phil Grainger

Re: DB2 for z/OS V8 - LOAD utility question
(in response to Walter Janißen)
Hmm - I feel some further testing coming on

In the DB2 10 utility manual, for ROWFORMAT, it says "This keyword affects only utility behavior in Version 9 NFM, and overrides the existing SPRMRRF setting when it is specified."

Which to me, reads like in DB2 10 you CANNOT choose between BRF and RRF any more

I'd not heard that restriction before
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: 16 December 2010 08:11
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Hello Phil

Not sure about hash tables though......

But I am sure, that RRF is a prerequisite for using hash access

Mit freundlichen Grüßen
Walter Janißen

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

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Phil Grainger
Gesendet: Mittwoch, 15. Dezember 2010 20:57
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
If you have a lot of very short variable columns, compressed RRF can actually take up more space than the equivalent BRF (due to the fact that column lengths are no longer adjacent to their column data)

This is why the conversion BACK to BRF was introduced

As part of this, support for BRF had to be retrofitted to universal table spaces, so I suspect things that USED TO require RRF at DB2 9 GA time no longer do so

Not sure about hash tables though......
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: 15 December 2010 16:01
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will eventually prove to be more efficient access to these tables. Not a lot, but some, so why degrade potentially efficiency improvements?

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to know. What is the ZPARM to control this behavior, and can we reconvert tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim - from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the management of these tables by physically move all VARCHAR columns to the end and then have a set of OFFSET's in front of these guys. This will happen at LOAD REPLACE / REORG time, and if you don't want to utilize this feature, there is a ZPARM parameter to control it. You can identify which partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
<mailto:[login to unmask email]>Error! Filename not specified.< http://www.ca.com/ >

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace attribute is being changed if any ?

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.
---------------------------------------------------------------------

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Myron Miller

Re: DB2 for z/OS V8 - LOAD utility question
(in response to Phil Grainger)
It's all tablespaces, regardless of whether they have VARCHAR.




________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 6:26:10 PM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question


What you are saying is true. By having all the tablespaces converted to RRF, we
would be having one less thing to worry about when the time for migration comes.
Even, we think that we should be going in the direction of RRF but the failure
with the MS/FTR product made me think twice about RRF.

My understanding of RRF was that if the RRF ZPARM is turned on, all the
tablespaces with VARCHAR fields will be converted to RRF when “REORG” or “LOAD
REPLACE” happens on that tablespace. But, we are seeing that all the tablespaces
that are getting REORG’d are converting into RRF. Is my understanding correct?

Thanks,
Satish Srikakulapu

From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, December 15, 2010 1:12 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Here's the place to change it back and forth. 'Existing RRF table spaces are
never converted to BRF by the LOAD REPLACE or REORG TABLESPACE utility except
when requested explicitly with the ROWFORMAT keyword" This implies that you can
switch back and forth easily enough.

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.inst/db2z_migr2nfmimplications.htm


We also copy a lot of data into a shadow space for production and to other
environments using DSN1COPY. So we also have to have the FORMAT the same in
both. I've developed some SQL for our production environment copy to compare
the two and I regularly go thru and reorg the mismatches. For the production to
test environments, generally I only worry about those that have problems as we
try to keep them as identical as possible in every way (DSN1COPY as mentioned
has tons of issues if they are not identical).

From what I've seen lots of new features will be using RRF only. That's the
direction IBM has stated to be the future for all tablespaces. And I have a
strong feeling that at some point, that will be the only format supported. So
while I can get ahead of the game, I'm slowly converting all of our tablespaces
to RRF. In production it's a simple conversion. It's more of an effort in the
test environments as we don't have regular reorgs run there. But if I can get
everything converted, I figure that with the X release of DB2, this will be one
item that I don't have to convert. And I've found that by staying ahead of this
makes conversions from one DB2 version to the next much much simpler.

Myron


________________________________

From:Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 11:53:35 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Myron,

We refresh the data in our test environments by copying the data from production
using MS/FTR product. As of now, the tables whose FORMAT is different between
our production and test environments are failing to get copied. For MS/FTR
product to work, we have to have the structure and FORMAT to be the same between
production and test environments. To eliminate the need to reorg or load replace
the target environment tables, I was wondering if turning off the ZPARM would be
a good idea.

In our experience, not just the tables with VARCHAR columns are getting changed
from BRF to RRF but all the tables (and partitions) that are either reorged or
load replaced are changing from BRF to RRF. That is also another reason for
wondering if we need to turn off this ZPARM.

Can you tell us how and when we might see the benefits of RRF? I know that this
might be a very broad question but if you can point me to the documentation that
I could read on this, it would be really great.

Thanks,
Satish Srikakulapu

From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, December 15, 2010 9:01 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will
eventually prove to be more efficient access to these tables. Not a lot, but
some, so why degrade potentially efficiency improvements?

Myron


________________________________

From:Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to know.
What is the ZPARM to control this behavior, and can we reconvert tablespaces
that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim – from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the
management of these tables by physically move all VARCHAR columns to the end and
then have a set of OFFSET’s in front of these guys. This will happen at LOAD
REPLACE / REORG time, and if you don’t want to utilize this feature, there is a
ZPARM parameter to control it. You can identify which partitions/tablespaces
having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
Error! Filename not specified.

From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much
info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE
CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace
attribute is being changed if any ?

Jim McAlpine


________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.



________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential,
privileged or proprietary information. If you have received it in error, please
notify the sender immediately and delete the original and any copy or printout.
Unintended recipients are prohibited from making any other use of this e-mail.
Although we have taken reasonable precautions to ensure no viruses are present
in this e-mail, we accept no liability for any loss or damage arising from the
use of this e-mail or attachments, or for any delay or errors or omissions in
the contents which result from e-mail transmission.

---------------------------------------------------------------------


________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.



________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.



________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.



________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.


_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Phil Grainger

Re: DB2 for z/OS V8 - LOAD utility question
(in response to Myron Miller)
But is there any difference between an RRF and BRF tablespace that only has fixed columns?

I’m not convinced there is but I’m willing (as ever) to be wrong
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: 16 December 2010 14:03
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

It's all tablespaces, regardless of whether they have VARCHAR.

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 6:26:10 PM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
What you are saying is true. By having all the tablespaces converted to RRF, we would be having one less thing to worry about when the time for migration comes. Even, we think that we should be going in the direction of RRF but the failure with the MS/FTR product made me think twice about RRF.

My understanding of RRF was that if the RRF ZPARM is turned on, all the tablespaces with VARCHAR fields will be converted to RRF when “REORG” or “LOAD REPLACE” happens on that tablespace. But, we are seeing that all the tablespaces that are getting REORG’d are converting into RRF. Is my understanding correct?

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, December 15, 2010 1:12 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Here's the place to change it back and forth. 'Existing RRF table spaces are never converted to BRF by the LOAD REPLACE or REORG TABLESPACE utility except when requested explicitly with the ROWFORMAT keyword" This implies that you can switch back and forth easily enough.

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.inst/db2z_migr2nfmimplications.htm

We also copy a lot of data into a shadow space for production and to other environments using DSN1COPY. So we also have to have the FORMAT the same in both. I've developed some SQL for our production environment copy to compare the two and I regularly go thru and reorg the mismatches. For the production to test environments, generally I only worry about those that have problems as we try to keep them as identical as possible in every way (DSN1COPY as mentioned has tons of issues if they are not identical).

From what I've seen lots of new features will be using RRF only. That's the direction IBM has stated to be the future for all tablespaces. And I have a strong feeling that at some point, that will be the only format supported. So while I can get ahead of the game, I'm slowly converting all of our tablespaces to RRF. In production it's a simple conversion. It's more of an effort in the test environments as we don't have regular reorgs run there. But if I can get everything converted, I figure that with the X release of DB2, this will be one item that I don't have to convert. And I've found that by staying ahead of this makes conversions from one DB2 version to the next much much simpler.

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 11:53:35 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Myron,

We refresh the data in our test environments by copying the data from production using MS/FTR product. As of now, the tables whose FORMAT is different between our production and test environments are failing to get copied. For MS/FTR product to work, we have to have the structure and FORMAT to be the same between production and test environments. To eliminate the need to reorg or load replace the target environment tables, I was wondering if turning off the ZPARM would be a good idea.

In our experience, not just the tables with VARCHAR columns are getting changed from BRF to RRF but all the tables (and partitions) that are either reorged or load replaced are changing from BRF to RRF. That is also another reason for wondering if we need to turn off this ZPARM.

Can you tell us how and when we might see the benefits of RRF? I know that this might be a very broad question but if you can point me to the documentation that I could read on this, it would be really great.

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, December 15, 2010 9:01 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will eventually prove to be more efficient access to these tables. Not a lot, but some, so why degrade potentially efficiency improvements?

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to know. What is the ZPARM to control this behavior, and can we reconvert tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim – from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the management of these tables by physically move all VARCHAR columns to the end and then have a set of OFFSET’s in front of these guys. This will happen at LOAD REPLACE / REORG time, and if you don’t want to utilize this feature, there is a ZPARM parameter to control it. You can identify which partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
<mailto:[login to unmask email]>Error! Filename not specified.< http://www.ca.com/ >

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace attribute is being changed if any ?

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.
---------------------------------------------------------------------

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Raymond Bell

Re: DB2 for z/OS V8 - LOAD utility question
(in response to Phil Grainger)
Phil, if all columns in a DB2 9 table are fixed length then, even though it’ll be in RRF format (cause it was created in 9), in this case I believe there is no underlying difference in the page layout. That’s me interpreting a nice presentation around, among other things, page formats given by one Ken McDonald. So the Directory will say it’s RRF but it looks, I believe, like BRF.

Actually there are 3 places the page format is stored. I dug into this awhile ago when looking into the implications of moving ICs in one format onto a TS in the other. The header page, the Directory... oh, and the Catalog too. D’oh.

Cheers,


Raymond
PS. If we’re wrong, we’ll buy each other a beer...

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: 16 December 2010 14:57
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

But is there any difference between an RRF and BRF tablespace that only has fixed columns?

I’m not convinced there is but I’m willing (as ever) to be wrong
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: 16 December 2010 14:03
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

It's all tablespaces, regardless of whether they have VARCHAR.

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 6:26:10 PM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
What you are saying is true. By having all the tablespaces converted to RRF, we would be having one less thing to worry about when the time for migration comes. Even, we think that we should be going in the direction of RRF but the failure with the MS/FTR product made me think twice about RRF.

My understanding of RRF was that if the RRF ZPARM is turned on, all the tablespaces with VARCHAR fields will be converted to RRF when “REORG” or “LOAD REPLACE” happens on that tablespace. But, we are seeing that all the tablespaces that are getting REORG’d are converting into RRF. Is my understanding correct?

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, December 15, 2010 1:12 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Here's the place to change it back and forth. 'Existing RRF table spaces are never converted to BRF by the LOAD REPLACE or REORG TABLESPACE utility except when requested explicitly with the ROWFORMAT keyword" This implies that you can switch back and forth easily enough.

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.inst/db2z_migr2nfmimplications.htm

We also copy a lot of data into a shadow space for production and to other environments using DSN1COPY. So we also have to have the FORMAT the same in both. I've developed some SQL for our production environment copy to compare the two and I regularly go thru and reorg the mismatches. For the production to test environments, generally I only worry about those that have problems as we try to keep them as identical as possible in every way (DSN1COPY as mentioned has tons of issues if they are not identical).

From what I've seen lots of new features will be using RRF only. That's the direction IBM has stated to be the future for all tablespaces. And I have a strong feeling that at some point, that will be the only format supported. So while I can get ahead of the game, I'm slowly converting all of our tablespaces to RRF. In production it's a simple conversion. It's more of an effort in the test environments as we don't have regular reorgs run there. But if I can get everything converted, I figure that with the X release of DB2, this will be one item that I don't have to convert. And I've found that by staying ahead of this makes conversions from one DB2 version to the next much much simpler.

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 11:53:35 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Myron,

We refresh the data in our test environments by copying the data from production using MS/FTR product. As of now, the tables whose FORMAT is different between our production and test environments are failing to get copied. For MS/FTR product to work, we have to have the structure and FORMAT to be the same between production and test environments. To eliminate the need to reorg or load replace the target environment tables, I was wondering if turning off the ZPARM would be a good idea.

In our experience, not just the tables with VARCHAR columns are getting changed from BRF to RRF but all the tables (and partitions) that are either reorged or load replaced are changing from BRF to RRF. That is also another reason for wondering if we need to turn off this ZPARM.

Can you tell us how and when we might see the benefits of RRF? I know that this might be a very broad question but if you can point me to the documentation that I could read on this, it would be really great.

Thanks,
Satish Srikakulapu

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, December 15, 2010 9:01 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will eventually prove to be more efficient access to these tables. Not a lot, but some, so why degrade potentially efficiency improvements?

Myron

________________________________
From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to know. What is the ZPARM to control this behavior, and can we reconvert tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim – from DB2 V8, for tables having VARCHAR columns, DB2 will optimize the management of these tables by physically move all VARCHAR columns to the end and then have a set of OFFSET’s in front of these guys. This will happen at LOAD REPLACE / REORG time, and if you don’t want to utilize this feature, there is a ZPARM parameter to control it. You can identify which partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
<mailto:[login to unmask email]>Error! Filename not specified.< http://www.ca.com/ >

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific tablespace attribute is being changed if any ?

Jim McAlpine

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain confidential, privileged or proprietary information. If you have received it in error, please notify the sender immediately and delete the original and any copy or printout. Unintended recipients are prohibited from making any other use of this e-mail. Although we have taken reasonable precautions to ensure no viruses are present in this e-mail, we accept no liability for any loss or damage arising from the use of this e-mail or attachments, or for any delay or errors or omissions in the contents which result from e-mail transmission.
---------------------------------------------------------------------

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Max Scarpa

Re: AW: [DB2-L] DB2 for z/OS V8 - LOAD utility question
(in response to Raymond Bell)
Hi

UTS and RRF are required (see DB2 10 new redbook or Shyam/Cornwell
presentation at IDUG EMEA) .

Anyway it seems there are many combinations for tablespaces now moving to
V9 (and V10 ?).... :-(.

Max Scarpa

Certifid confused DB2 sysprog





From: Walter Janißen <[login to unmask email]>
To: [login to unmask email]
Date: 16/12/2010 09.11
Subject: [DB2-L] AW: [DB2-L] DB2 for z/OS V8 - LOAD utility
question
Sent by: IDUG DB2-L <[login to unmask email]>



Hello Phil

Not sure about hash tables though……

But I am sure, that RRF is a prerequisite for using hash access
Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Phil Grainger
Gesendet: Mittwoch, 15. Dezember 2010 20:57
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

If you have a lot of very short variable columns, compressed RRF can
actually take up more space than the equivalent BRF (due to the fact that
column lengths are no longer adjacent to their column data)

This is why the conversion BACK to BRF was introduced

As part of this, support for BRF had to be retrofitted to universal table
spaces, so I suspect things that USED TO require RRF at DB2 9 GA time no
longer do so

Not sure about hash tables though……
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: 15 December 2010 16:01
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Why would you want to convert back? For varchar column tables, this will
eventually prove to be more efficient access to these tables. Not a lot,
but some, so why degrade potentially efficiency improvements?

Myron


From: Satish Srikakulapu <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, December 15, 2010 10:28:46 AM
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question
Steen,

This brings up a question, that might have been answered but I wanted to
know. What is the ZPARM to control this behavior, and can we reconvert
tablespaces that are in RRF as of now to BRF back?

Thanks,
Satish Srikakulapu


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen,
Steen
Sent: Tuesday, December 14, 2010 11:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS V8 - LOAD utility question

Jim – from DB2 V8, for tables having VARCHAR columns, DB2 will optimize
the management of these tables by physically move all VARCHAR columns to
the end and then have a set of OFFSET’s in front of these guys. This will
happen at LOAD REPLACE / REORG time, and if you don’t want to utilize this
feature, there is a ZPARM parameter to control it. You can identify which
partitions/tablespaces having RRF/BRF by looking at SYSTABLEPART.format.

Please be aware of DSN1COPY too.

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS
Tel: +1-630-505-6673
Mobile: +1-708-382-1363
[login to unmask email]
Error! Filename not specified.

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Tuesday, December 14, 2010 10:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS V8 - LOAD utility question

I'm getting the following when running LOAD and I've RTFM and I can't find
much info on it -

DSNU1173I * 348 17:18:02.06 DSNURWI - TABLE SPACE Z30DDA01.SAACNTRL WILL
BE CONVERTING FROM BASIC ROW FORMAT TO REORDERED ROW FORMAT

Can someone explain exactly what it is telling me. What specific
tablespace attribute is being changed if any ?

Jim McAlpine



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
Email Firewall made the following annotations
---------------------------------------------------------------------
--- NOTICE ---
This message is for the designated recipient only and may contain
confidential, privileged or proprietary information. If you have received
it in error, please notify the sender immediately and delete the original
and any copy or printout. Unintended recipients are prohibited from making
any other use of this e-mail. Although we have taken reasonable
precautions to ensure no viruses are present in this e-mail, we accept no
liability for any loss or damage arising from the use of this e-mail or
attachments, or for any delay or errors or omissions in the contents which
result from e-mail transmission.
---------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.


_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv