Identity Column Restart and need for reorg

Frederick Moor

Identity Column Restart and need for reorg
Having a hard time finding a answer to this question: if I do an alter table to
restart an identity column, do I need to do a reorg if I don't want to change
any of the values that are already in the database.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Steen Rasmussen

Re: Identity Column Restart and need for reorg
(in response to Frederick Moor)
NO reorg is needed - this command just tells DB2 where to start the next INSERT from.
You are good to go :-)

Steen Rasmussen
CA
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified Database Administrator - DB2 9 DBA for z/OS



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Frederick Moor
Sent: Friday, December 04, 2009 3:19 PM
To: [login to unmask email]
Subject: [DB2-L] Identity Column Restart and need for reorg

Having a hard time finding a answer to this question: if I do an alter table to
restart an identity column, do I need to do a reorg if I don't want to change
any of the values that are already in the database.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Myron Miller

Re: Identity Column Restart and need for reorg
(in response to Steen Rasmussen)
Nope.  Just alter and go.  Nothing else is needed at all.

Myron




________________________________
From: Frederick Moor <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, December 4, 2009 4:19:00 PM
Subject: [DB2-L] Identity Column Restart and need for reorg

Having a hard time finding a answer to this question: if I do an alter table to
restart an identity column, do I need to do a reorg if I don't want to change
any of the values that are already in the database.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *  http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html -  home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

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

Suresh Sane

Re: Identity Column Restart and need for reorg
(in response to Myron Miller)

As Steen and Myron have pointed out, no REORG is needed.



However, if you have static SQL, you should be aware of this also:



All dependent packages are invalidated (why? I do not know and have never heard a satisfactory answer from IBM).



Assuming autobind, next execution, you will be fine. If you are paranoid (like we are) about auto rebind failing, making the package inoperative and causing serious outages, even this seemingly simple alter may cause trouble. Unlikely but possible. BEWARE!



Thx

Suresh



> Date: Fri, 4 Dec 2009 21:54:54 -0500
> From: [login to unmask email]
> To: [login to unmask email]
> Subject: Re: [DB2-L] Identity Column Restart and need for reorg
>
> NO reorg is needed - this command just tells DB2 where to start the next INSERT from.
> You are good to go :-)
>
> Steen Rasmussen
> CA
> Sr Engineering Services Architect
> IBM Certified Database Associate - DB2 9 Fundamentals
> IBM Certified Database Administrator - DB2 9 DBA for z/OS
>
>
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Frederick Moor
> Sent: Friday, December 04, 2009 3:19 PM
> To: [login to unmask email]
> Subject: [DB2-L] Identity Column Restart and need for reorg
>
> Having a hard time finding a answer to this question: if I do an alter table to
> restart an identity column, do I need to do a reorg if I don't want to change
> any of the values that are already in the database.
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
> Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
> magazine now on-line!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
>

_________________________________________________________________
Get gifts for them and cashback for you. Try Bing now.
http://www.bing.com/shopping/search?q=xbox+games&scope=cashback&form=MSHYCB&publ=WLHMTAG&crea=TEXT_MSHYCB_Shopping_Giftsforthem_cashback_1x1
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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

Myron Miller

Re: Identity Column Restart and need for reorg
(in response to Suresh Sane)
Suresh brings up an excellent point.  Autorebinds can be real issues.  If you have an active package, the probability that the autorebind will fail approaches unity.  Need to manually rebind everything and check all binds as they finish.  The issue will be contention on the system resource SYSIBM.SYSDATABASE.  I see anytime I manually rebind more than about 10-20 packages consecutively, resource failures on this.  And of course, I've seen tons of autorebind failures.  Not pretty.

Myron




________________________________
From: Suresh Sane <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, December 7, 2009 2:19:10 PM
Subject: Re: [DB2-L] Identity Column Restart and need for reorg

As Steen and Myron have pointed out, no REORG is needed. 
 
However, if you have static SQL, you should be aware of this also:
 
All dependent packages are invalidated (why? I do not know and have never heard a satisfactory answer from IBM). 
 
Assuming autobind, next execution, you will be fine. If you are paranoid (like we are) about auto rebind failing, making the package inoperative and causing serious outages, even this seemingly simple alter may cause trouble.  Unlikely but possible.  BEWARE!
 
Thx
Suresh

 
> Date: Fri, 4 Dec 2009 21:54:54 -0500
> From: [login to unmask email]
> To: [login to unmask email]
> Subject: Re: [DB2-L] Identity Column Restart and need for reorg
>
> NO reorg is needed - this command just tells DB2 where to start the next INSERT from.
> You are good to go :-)
>
> Steen Rasmussen
> CA
> Sr Engineering Services Architect
> IBM Certified Database Associate - DB2 9 Fundamentals
> IBM Certified Database Administrator - DB2 9 DBA for z/OS
>
>
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Frederick Moor
> Sent: Friday, December 04, 2009 3:19 PM
> To: [login to unmask email]
> Subject: [DB2-L] Identity Column Restart and need for reorg
>
> Having a hard time finding a answer to this question: if I do an alter table to
> restart an identity column, do I need to do a reorg if I don't want to change
> any of the values that are already in the database.
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
> Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
> magazine now on-line!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
>

________________________________
Get gifts for them and cashback for you. Try Bing now.
________________________________

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 * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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