REORG sharelevel NONE

dusan pospisil

REORG sharelevel NONE
Hi all,
My environment is DB2 v.7 z/OS
I have a compressed TS, 12 partitions, 8GB each, but last one is now about 11GM and growing. In test environment I have tried to change PART 1 VALUES(...) and to run REORG sharelevel NONE part 1:2, but reorg crashed on sysrec (my estimation of appropriate sysrec is about 100GM). When I used SORTDATA and NOSYSREC, reorg worked.
My first question: do the rows go to the reload phase in some small amounts? (jcl region was 8M)
The second question: do you think that the method of changing one value of index limit n and reorg part n:n+1 (probably starting with 11, then 10, and then 1,2,... to avoid sql -636) is possible?

Thanks in advance for any advice,
dp

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Edward Long

Re: REORG sharelevel NONE
(in response to dusan pospisil)
I suspect that SYSREC in this context would compress very nicely using DFSMS compression.
Ask your storage admin or MVS system programmer if they can make it available to you.
The idea is to reduce the size of the intermediate datasets.
I get 3 or 4 to one compression on typical sequential datasets. Your 100gb file should be at least cut in half if not a lot better.

Pospí¹il Du¹an <[login to unmask email]> wrote:
Hi all,
My environment is DB2 v.7 z/OS
I have a compressed TS, 12 partitions, 8GB each, but last one is now about 11GM and growing. In test environment I have tried to change PART 1 VALUES(...) and to run REORG sharelevel NONE part 1:2, but reorg crashed on sysrec (my estimation of appropriate sysrec is about 100GM). When I used SORTDATA and NOSYSREC, reorg worked.
My first question: do the rows go to the reload phase in some small amounts? (jcl region was 8M)
The second question: do you think that the method of changing one value of index limit n and reorg part n:n+1 (probably starting with 11, then 10, and then 1,2,... to avoid sql -636) is possible?

Thanks in advance for any advice,
dp

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



Edward Long

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

dusan pospisil

Re: REORG sharelevel NONE
(in response to Edward Long)
Thank you for your answer. However I looked at failed sysrec and all records was in full length (more then 600B each row). I thought that it was due to that every partition had its own dictionary. Probably you mean some other kind of compression (DFSMS?) which I know nothing about.
Regards,
dp


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: Monday, January 15, 2007 5:17 PM
To: [login to unmask email]
Subject: Re: [DB2-L] REORG sharelevel NONE


I suspect that SYSREC in this context would compress very nicely using DFSMS compression.
Ask your storage admin or MVS system programmer if they can make it available to you.
The idea is to reduce the size of the intermediate datasets.
I get 3 or 4 to one compression on typical sequential datasets. Your 100gb file should be at least cut in half if not a lot better.

Pospí¹il Du¹an <[login to unmask email]> wrote:

Hi all,
My environment is DB2 v.7 z/OS
I have a compressed TS, 12 partitions, 8GB each, but last one is now about 11GM and growing. In test environment I have tried to change PART 1 VALUES(...) and to run REORG sharelevel NONE part 1:2, but reorg crashed on sysrec (my estimation of appropriate sysrec is about 100GM). When I used SORTDATA and NOSYSREC, reorg worked.
My first question: do the rows go to the reload phase in some small amounts? (jcl region was 8M)
The second question: do you think that the method of changing one value of index limit n and reorg part n:n+1 (probably starting with 11, then 10, and then 1,2,... to avoid sql -636) is possible?

Thanks in advance for any advice,
dp

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm





Edward Long --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Edward Long

Re: REORG sharelevel NONE
(in response to dusan pospisil)
That is exactly the compression I am alluding to. Check with your storage administrator or system programmer.
With SMS compression assigned, which can be done with jcl, the LRECL remains the same but the actual space used is reduced.

Pospíšil Dušan <[login to unmask email]> wrote:
Thank you for your answer. However I looked at failed sysrec and all records was in full length (more then 600B each row). I thought that it was due to that every partition had its own dictionary. Probably you mean some other kind of compression (DFSMS?) which I know nothing about.
Regards,
dp



---------------------------------
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: Monday, January 15, 2007 5:17 PM
To: [login to unmask email]
Subject: Re: [DB2-L] REORG sharelevel NONE



I suspect that SYSREC in this context would compress very nicely using DFSMS compression.
Ask your storage admin or MVS system programmer if they can make it available to you.
The idea is to reduce the size of the intermediate datasets.
I get 3 or 4 to one compression on typical sequential datasets. Your 100gb file should be at least cut in half if not a lot better.

Pospí¹il Du¹an <[login to unmask email]> wrote:
Hi all,
My environment is DB2 v.7 z/OS
I have a compressed TS, 12 partitions, 8GB each, but last one is now about 11GM and growing. In test environment I have tried to change PART 1 VALUES(...) and to run REORG sharelevel NONE part 1:2, but reorg crashed on sysrec (my estimation of appropriate sysrec is about 100GM). When I used SORTDATA and NOSYSREC, reorg worked.
My first question: do the rows go to the reload phase in some small amounts? (jcl region was 8M)
The second question: do you think that the method of changing one value of index limit n and reorg part n:n+1 (probably starting with 11, then 10, and then 1,2,... to avoid sql -636) is possible?

Thanks in advance for any advice,
dp

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



Edward Long --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


Edward Long

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

dusan pospisil

Re: REORG sharelevel NONE
(in response to Edward Long)
Thank you very much


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: Monday, January 15, 2007 6:35 PM
To: [login to unmask email]
Subject: Re: [DB2-L] REORG sharelevel NONE


That is exactly the compression I am alluding to. Check with your storage administrator or system programmer.
With SMS compression assigned, which can be done with jcl, the LRECL remains the same but the actual space used is reduced.

Pospíšil Dušan <[login to unmask email]> wrote:

Thank you for your answer. However I looked at failed sysrec and all records was in full length (more then 600B each row). I thought that it was due to that every partition had its own dictionary. Probably you mean some other kind of compression (DFSMS?) which I know nothing about.
Regards,
dp


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: Monday, January 15, 2007 5:17 PM
To: [login to unmask email]
Subject: Re: [DB2-L] REORG sharelevel NONE


I suspect that SYSREC in this context would compress very nicely using DFSMS compression.
Ask your storage admin or MVS system programmer if they can make it available to you.
The idea is to reduce the size of the intermediate datasets.
I get 3 or 4 to one compression on typical sequential datasets. Your 100gb file should be at least cut in half if not a lot better.

Pospí¹il Du¹an <[login to unmask email]> wrote:

Hi all,
My environment is DB2 v.7 z/OS
I have a compressed TS, 12 partitions, 8GB each, but last one is now about 11GM and growing. In test environment I have tried to change PART 1 VALUES(...) and to run REORG sharelevel NONE part 1:2, but reorg crashed on sysrec (my estimation of appropriate sysrec is about 100GM). When I used SORTDATA and NOSYSREC, reorg worked.
My first question: do the rows go to the reload phase in some small amounts? (jcl region was 8M)
The second question: do you think that the method of changing one value of index limit n and reorg part n:n+1 (probably starting with 11, then 10, and then 1,2,... to avoid sql -636) is possible?

Thanks in advance for any advice,
dp

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm





Edward Long --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm




Edward Long --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm