OAM large object online reorg

Gopal Tripathi

OAM large object online reorg
Good morning,

 

I was trying to perform an online reorg on large object table OAM in production environment having total of 49 partitions and close to 2 million rows on DB2 Z Os


The table is a part by size table and TS is UTS PBG. with max parts 200 specified. I was trying 5 partitions each and while doing 31:35th partition I received dfsort error.

Later I tried with 35th partition alone still getting the same error.

Tablespace ddl:

CREATE TABLESPACE xyz32
IN dbname
USING STOGROUP xyz001
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 0 FOR UPDATE 0
GBPCACHE CHANGED
TRACKMOD YES
MAXPARTITIONS 200
LOGGED
DSSIZE 2 G
NUMPARTS 49
SEGSIZE 64
BUFFERPOOL BP32K2
LOCKSIZE ANY
LOCKMAX SYSTEM

Card:
//SORTWK44 DD UNIT=SYSDA,SPACE=(CYL,(300,300))
//SORTWK45 DD UNIT=SYSDA,SPACE=(CYL,(300,300))
//SYSIN DD *
OPTIONS EVENT(ITEMERROR,SKIP)
LISTDEF LISTREORG
INCLUDE TABLESPACES TABLESPACE dbname.tsname PARTLEVEL 35
TEMPLATE BACK1 DSN 'HLQ.BKUP.&TS..P&PA..D&MO.&DA..T &TI.'
DISP(NEW,CATLG,DELETE) UNIT(VTAPE) RETPD 30 STACK YES
REORG TABLESPACE LIST LISTREORG LOG NO COPYDDN(BACK1)
SHRLEVEL CHANGE MAPPINGTABLE QUALIFIER.TABLENAME
STATISTICS TABLE(ALL) INDEX(ALL) REPORT YES SAMPLE 15
SORTDEVT DISK
/*

Error:
Sysprint:
UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'00E40347'
Utprint message:
ICE185A 0 AN S04E ABEND WAS ISSUED BY DFSORT, ANOTHER PROGRAM OR AN EXIT (PHASE S 3)

I have also updated stats before running the reorg, allocated around 45 sortwork datasets as above in the reorg.

Is there any probable space issue that I need to address for the index or table. I see the index is well placed with 70 percent free space.

Since it is a big table am trying to free up some sms space by doing part level reorg and was able to achieve some, i have completed 34 partitions but problem is same with 35th partition now. Full table space reorg is something am avoiding as of now due to sort work issues.

Am just trying to figure out if my approach is correct. Do i need to do full reorg later??

Appreciate your help.Thanks!!

Lizette Koehler

OAM large object online reorg
(in response to Gopal Tripathi)
So what was the ICE message ICExxxxA



If you could provide the full ICE messages that could help



Lizette





From: Gopal Tripathi [mailto:[login to unmask email]
Sent: Friday, December 08, 2017 12:08 PM
To: [login to unmask email]
Subject: [DB2-L] - OAM large object online reorg



Good morning,



I was trying to perform an online reorg on large object table OAM in production environment having total of 49 partitions and close to 2 million rows on DB2 Z Os


The table is a part by size table and TS is UTS PBG. with max parts 200 specified. I was trying 5 partitions each and while doing 31:35th partition I received dfsort error.

Later I tried with 35th partition alone still getting the same error.

Tablespace ddl:

CREATE TABLESPACE xyz32
IN dbname
USING STOGROUP xyz001
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 0 FOR UPDATE 0
GBPCACHE CHANGED
TRACKMOD YES
MAXPARTITIONS 200
LOGGED
DSSIZE 2 G
NUMPARTS 49
SEGSIZE 64
BUFFERPOOL BP32K2
LOCKSIZE ANY
LOCKMAX SYSTEM

Card:
//SORTWK44 DD UNIT=SYSDA,SPACE=(CYL,(300,300))
//SORTWK45 DD UNIT=SYSDA,SPACE=(CYL,(300,300))
//SYSIN DD *
OPTIONS EVENT(ITEMERROR,SKIP)
LISTDEF LISTREORG
INCLUDE TABLESPACES TABLESPACE dbname.tsname PARTLEVEL 35
TEMPLATE BACK1 DSN 'HLQ.BKUP.&TS..P&PA..D&MO.&DA..T &TI.'
DISP(NEW,CATLG,DELETE) UNIT(VTAPE) RETPD 30 STACK YES
REORG TABLESPACE LIST LISTREORG LOG NO COPYDDN(BACK1)
SHRLEVEL CHANGE MAPPINGTABLE QUALIFIER.TABLENAME
STATISTICS TABLE(ALL) INDEX(ALL) REPORT YES SAMPLE 15
SORTDEVT DISK
/*

Error:
Sysprint:
UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'00E40347'
Utprint message:
ICE185A 0 AN S04E ABEND WAS ISSUED BY DFSORT, ANOTHER PROGRAM OR AN EXIT (PHASE S 3)

I have also updated stats before running the reorg, allocated around 45 sortwork datasets as above in the reorg.

Is there any probable space issue that I need to address for the index or table. I see the index is well placed with 70 percent free space.

Since it is a big table am trying to free up some sms space by doing part level reorg and was able to achieve some, i have completed 34 partitions but problem is same with 35th partition now. Full table space reorg is something am avoiding as of now due to sort work issues.

Am just trying to figure out if my approach is correct. Do i need to do full reorg later??


Appreciate your help.Thanks!!



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

Gopal Tripathi

RE: OAM large object online reorg
(in response to Lizette Koehler)

There is only this message in the UTPRINT  and nothing else. there is a SORTSNAP though but nothing in there too.

 

Thanks,

Gopal Tripathi

RE: OAM large object online reorg
(in response to Gopal Tripathi)

The only message is see is ICE185A

AN S04E  ABEND WAS ISSUED BY DFSORT, ANOTHER PROGRAM OR AN EXIT

Lizette Koehler

OAM large object online reorg
(in response to Gopal Tripathi)
I think there should be a sysprint for sort. You may need to ask you Zoe dysprosium what they set for msgddn

Lizette


Sent from EarthLink Mobile mail
On 12/8/17, 1:05 PM, Gopal Tripathi <[login to unmask email]> wrote:

From: Gopal Tripathi <[login to unmask email]>
To: [login to unmask email]
Subject: [DB2-L] - RE: OAM large object online reorg
Date: December 8, 2017 at 1:05:00 PM MST
There is only this message in the UTPRINT  and nothing else. there is a SORTSNAP though but nothing in there too.
 
Thanks,


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Lizette Koehler

OAM large object online reorg
(in response to Lizette Koehler)
Sorry about that. iPhones do not spell well ;-D

you Zoe dysprosium what they set for msgddn

Should read

You should ask your z/OS system programmer what they set for MSGDDN for DFSORT. Default is usually SYSPRINT

Lizette


> -----Original Message-----
> From: Lizette Koehler [mailto:[login to unmask email]
> Sent: Friday, December 08, 2017 2:58 PM
> To: Gopal Tripathi <[login to unmask email]>
> Subject: [DB2-L] - RE: OAM large object online reorg
>
> I think there should be a sysprint for sort. You may need to ask you Zoe
> dysprosium what they set for msgddn
>
> Lizette
>
>
> Sent from EarthLink Mobile mail
> On 12/8/17, 1:05 PM, Gopal Tripathi <[login to unmask email]> wrote:
>
> From: Gopal Tripathi <[login to unmask email]>
> To: [login to unmask email]
> Subject: [DB2-L] - RE: OAM large object online reorg
> Date: December 8, 2017 at 1:05:00 PM MST There is only this message in the
> UTPRINT and nothing else. there is a SORTSNAP though but nothing in there
> too.
>
> Thanks,
>
>
> Site Links: View post online View mailing list online Start new thread
> via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email] Setup a data refresh
> task in less time than it takes to make a cup of coffee + save up to 90% in
> CPU ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make
> you a hero to users. See http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
>
> -----End Original Message-----

Gopal Tripathi

RE: OAM large object online reorg
(in response to Lizette Koehler)

:)

Never mind.

Yes it is SYSPRINT only.  

Thanks!!