DB2 Reorg Pending automation

Rahul Singh

DB2 Reorg Pending automation

Hi Experts

I have a situation where I am being asked to automate execution of DB2 REORG if table is in "Reorg Pending" status. I can easily do this using shell script or windows batch file , however I don't have that option. Only option for me is to automate this using SQL

I am basically from Oracle background and not very well versed with DB2 UDB much. I tried creating a stored procedure but not sure how can I loop through each row,

 

SQL :-

select TABSCHEMA, TABNAME from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y'

Can someone please help ?

 

thanks a lot in advance

 

 

 

Edited By:
Rahul Singh[Organization Members] @ Jan 18, 2018 - 11:28 PM (Asia/Calcutta)
Rahul Singh[Organization Members] @ Jan 18, 2018 - 11:29 PM (Asia/Calcutta)
Rahul Singh[Organization Members] @ Jan 18, 2018 - 11:39 PM (Asia/Calcutta)

Ian Bjorhovde

RE: DB2 Reorg Pending automation
(in response to Rahul Singh)
You can do this in 2 ways:
1) Declare Variables and a cursor for your SQL statement and, using a while loop, fetch from the cursor into your variables:
  declare SQLSTATE char(5) default '00000';
  declare vTabSchema varchar(128);
  declare vTabName varchar(128);
declare c1 cursor for select tabschema, tabname
from syscat.tables
where reorg_pending = 'Y';

open c1;
-- fetch first row
fetch from c1 into vTabSchema, vTabName;

-- Loop through result set
while (SQLSTATE = '00000') do

-- issue reorg using vTabSchema, vTabName


-- fetch next row
fetch from c1 into vTabSchema, vTabName;
end while;
2)  Use a FOR loop:
    for v as c1 cursor for select tabschema, tabname
from syscat.tables
where reorg_pending = 'Y'

-- results are available in v.tabschema and v.tabname


end for;

 

 

 

Ember Crooks

DB2 Reorg Pending automation
(in response to Rahul Singh)
Won't ADMIN_REVALIDATE_DB_OBJECTS do this for you?

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0053626.html

Ember Crooks

IBM Champion, Information Management, 2013 - 2018
IBM Gold Consultant, 2014 - 2017

On Thu, Jan 18, 2018 at 10:57 AM, Rahul Singh <[login to unmask email]> wrote:

> Hi Experts
>
> I have a situation where I am being asked to automate execution of DB2
> REORG if table is in "Reorg Pending" status. I can easily do this using
> shell script of windows batch file , however I don't have that option. Only
> option for me is to automate this using SQL
>
> I am basically from Oracle background and not very well versed with DB2
> much. I tried creating a stored procedure but not sure how can I loop
> through each row,
>
> select TABSCHEMA, TABNAME from SYSIBMADM.ADMINTABINFO where REORG_PENDING
> = 'Y'
>
> Can someone please help ?
>
>
>
> thanks a lot in advance
>
>
>
>
>
>
>
> -----End Original Message-----
>

Rahul Singh

RE: DB2 Reorg Pending automation
(in response to Ember Crooks)

Thanks a lot for providing SQL codes ,   ADMIN_REVALIDATE_DB_OBJECTS seems to be doing the magic here.

Is there any list or documentation mentioning list of the commands which can put table in REORG PENDING STATUS ?.  I know changing DATATYPE does it but I am unable to find list of all such operations causing REORG PENDING STATUS anywhere in documentation

 

 

 

Ember Crooks

DB2 Reorg Pending automation
(in response to Rahul Singh)
This is a decent, but not fully complete answer to the question:
https://dba.stackexchange.com/questions/127848/db2-reorg-recommended-commands

Ember Crooks

IBM Champion, Information Management, 2013 - 2018
IBM Gold Consultant, 2014 - 2017

On Fri, Jan 19, 2018 at 12:01 AM, Rahul Singh <[login to unmask email]> wrote:

> Thanks a lot for providing SQL codes , ADMIN_REVALIDATE_DB_OBJECTS seems
> to be doing the magic here.
>
> Is there any list or documentation mentioning list of the commands which
> can put table in REORG PENDING STATUS ?. I know changing DATATYPE does it
> but I am unable to find list of all such operations causing REORG PENDING
> STATUS anywhere in documentation
>
>
>
>
>
>
>
> -----End Original Message-----
>

Charles Brown

DB2 Reorg Pending automation
(in response to Ember Crooks)
Hello Rahul,
Just like you, I came from the ORA (oracle) world where a simple oracle export followed by an import is sufficient for a REORG. Whereas in db2, the concept of REORG is pushed to another level. Such that even if you ALTER a table and add column. Bam!! The table goes into a reorg pending state. Really! Oh! Yes.

For all it worth, here is what I am using to address similar concerns / issues you have. I automated (cron job) these for my TEST systems. Supporting twenty developers, there’s a REORG pending state somewhere every hr of the day. Try it and see if it does the trick for you.

Hope this helps
Thx!
Chas
NZ dba

[login to unmask email] ~]$ db2 "select 'REORG TABLE ' || trim(tabschema) ||'.\"'|| trim( tabname) || '\";' from sysibmadm.admintabinfo where REORG_PENDING = 'Y'"

[[login to unmask email] ~]$ db2 "select 'REORG TABLE ' || trim(tabschema) ||'.\"'|| trim( tabname) || '\";' from sysibmadm.admintabinfo where REORG_PENDING = 'Y'"

Sent from my iPhone

> On Jan 19, 2018, at 8:21 AM, Ember Crooks <[login to unmask email]> wrote:
>
> This is a decent, but not fully complete answer to the question: https://dba.stackexchange.com/questions/127848/db2-reorg-recommended-commands
>
> Ember Crooks
>
> IBM Champion, Information Management, 2013 - 2018
> IBM Gold Consultant, 2014 - 2017
>
>> On Fri, Jan 19, 2018 at 12:01 AM, Rahul Singh <[login to unmask email]> wrote:
>> Thanks a lot for providing SQL codes , ADMIN_REVALIDATE_DB_OBJECTS seems to be doing the magic here.
>>
>> Is there any list or documentation mentioning list of the commands which can put table in REORG PENDING STATUS ?. I know changing DATATYPE does it but I am unable to find list of all such operations causing REORG PENDING STATUS anywhere in documentation
>>
>>
>>
>>
>>
>>
>>
>>
>> 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
>>
>
>
> 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
>

Rahul Singh

RE: DB2 Reorg Pending automation
(in response to Ember Crooks)

Thanks Ember, that really helps, however I am bit surprised to see that even ALTER TABLE ADD COLUMN cause table to go in reorg pending status. I tried adding few columns with different data type but none of them seems to cause table to go into reorg, any insight will be appreciated

Edited By:
Rahul Singh[Organization Members] @ Jan 19, 2018 - 10:21 PM (Asia/Calcutta)
Rahul Singh[Organization Members] @ Jan 19, 2018 - 10:23 PM (Asia/Calcutta)

Ember Crooks

DB2 Reorg Pending automation
(in response to Rahul Singh)
I think it is required if you add a not null column, but not for every
column addition. I'm not happy with the documentation in this area, and
always try an operation I think might be reorg-required or
reorg-recommended in a sandbox and a test environment before doing it in
production to see what happens.

To make things even more confusing there are also reorg-recommended
operations where you get three before the table is actually placed into a
reorg pending state, and several commands will not work against a table
after a reorg-recommended operation.

The best details are in the ALTER TABLE reference in the KC
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html ,
but it can take a bit of reading and experimentation to understand.

Ember Crooks

IBM Champion, Information Management, 2013 - 2018
IBM Gold Consultant, 2014 - 2017

On Fri, Jan 19, 2018 at 9:51 AM, Rahul Singh <[login to unmask email]> wrote:

> Thanks Amber, that really helps, however I am bit surprised to see that
> even ALTER TABLE ADD COLUMN cause table to go in reorg pending status. I
> tried adding few columns with different data type but none of them seems to
> cause table to go into reorg, any insight will be appriciated
>
> -----End Original Message-----
>

Jeff Goss

RE: DB2 Reorg Pending automation
(in response to Ember Crooks)

I think the phrasing may throw you off a bit but the list should be fairly clear (I worked with ID to get them to put all the operations in one note rather than having to search the entire reorg page), ie. from your link above:

  • The following is the full list of REORG-recommended ALTER statements that will cause a version change and place the table into a REORG-pending state:
    • DROP COLUMN
    • ALTER COLUMN SET NOT NULL
    • ALTER COLUMN DROP NOT NULL
    • ALTER COLUMN SET DATA TYPE, except in the following situations:
      • Increasing the length of a VARCHAR or VARGRAPHIC column
      • Decreasing the length of a VARCHAR or VARGRAPHIC column without truncating trailing blanks from existing data, when no indexes exist on the column

So, no, adding a column should not be an issue.  Internally we use the same version, and any rows that don't contain that column on disk have a value materialized depending on your alter statement - could be a default value, NULL, etc.  If you update a row after an alter table add column has been done then it will be written back to disk with all columns even if the 'new' columns were not modified - note this could cause pointer/overflow creation depending on the free space available on the page.  As far as using export/import instead of reorg you are also free to do that if you like, or use Online Table Move which can handle the deleting columns, altering data types, etc.

I think the logic of saying 'reorg-recommended' is that you can still access the table for operations listed in the bullet below that in the note, but the fact that it is read only and just for table scans means its not realistically much use after a reorg-recommended action has placed it in reorg pending state.

Rahul Singh

RE: DB2 Reorg Pending automation
(in response to Jeff Goss)

 

 

That's really a good insight Jeff thanks.  One dumb question  ,   if we already know that performing certain actions can put table in 'Reorg Pending',  why not let the code handle it and make  operations like Drop Column, change data type  etc take care of  changing 'Reorg Pending'  internally  instead of forcing end user to execute "reorg" explicitly

May be there is a feature request to do so already ?

Steen Rasmussen

DB2 Reorg Pending automation
(in response to Rahul Singh)
Its not really REORG PENDING when you perform schema changes but ADVISORY REORG PENDING in order to get the changes materialized. Especially for column attribute changes you are paying a performance penalty while operating on the data since Db2 has to reformat the data,

Steen

From: Rahul Singh [mailto:[login to unmask email]
Sent: Monday, January 22, 2018 10:49 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Reorg Pending automation

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.





That's really a good insight Jeff thanks. One dumb question , if we already know that performing certain actions can put table in 'Reorg Pending', why not let the code handle it and make operations like Drop Column, change data type etc take care of changing 'Reorg Pending' internally instead of forcing end user to execute "reorg" explicitly

May be there is a feature request to do so already ?

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

Rahul Singh

RE: DB2 Reorg Pending automation
(in response to Steen Rasmussen)

oh ok thanks for letting me know that. My point is shouldn't the code take care of such things instead of letting end user run 'reorg pending' manually ? most RDBMS doesn't force end user to run any command after column is altered

Nevertheless, this discussion was very useful , thanks all who took time to respond , I highly appreciate this

 

 

 

James Campbell

DB2 Reorg Pending automation
(in response to Steen Rasmussen)
If I read the original post correctly, it referred to a Db2 (LUW) environment - even though
the subject didn't say so.

If I read some of the responses correctly, they refer to Db2 for z/OS environments.

VERY different environments for reorgs.

James Campbell

Rahul Singh

RE: DB2 Reorg Pending automation -DB2 LUW
(in response to James Campbell)

Hi James, that's right,  the question I asked was particular to DB2 LUW -  I have now modified the subject, thanks for pointing that out

Jeff Goss

RE: DB2 Reorg Pending automation
(in response to Rahul Singh)



In Reply to Rahul Singh:

oh ok thanks for letting me know that. My point is shouldn't the code take care of such things instead of letting end user run 'reorg pending' manually ? most RDBMS doesn't force end user to run any command after column is altered

Nevertheless, this discussion was very useful , thanks all who took time to respond , I highly appreciate this

 

 

 

Right I believe Steen is referring to z/OS.  For LUW, we allow you to make up to 3 version altering changes before you are forced to do a CLASSIC reorg.  At one point this was per ALTER command with a version altering (reorg recommended) change but was changed at some point during v9 to allow all ALTERs in a single UOW behave like a single version altering change.  I don't remember why we added that particular enhancement but I'm sure it was driven by customer and/or third party developer requirements.  in LUW reorg is different from SQL in that it drives a utility - does commits at various points, has it's own version of undo etc.  You are of course free to issue a reorg directly after any ALTER you please.

The infrastructure for this is actually set up to be quite flexible, if the requirement rose up the list it could be extended to handle full table R/W access for up to three  reorg-recommended LUWs, and maybe even use INPLACE (aka online) REORG to bring old row versions up to current  and never force a CLASSIC reorg just for schema changes.