Moving System Tables
A DBA on IDS 11.70.FC6 wanted to relocate system catalog tables (sysdistrib, syscolumns, sysprocedures, sysprocbody, sysfragments, etc.) out of the 6th chunk of a dbspace so that chunk could be dropped. Art Kagel replied that the only supported method is to unload/export the whole database, drop it, drop the chunk, recreate the database and reload the data. Paul Watson described an explicitly unsupported trick (create duplicate tables, copy rows, shut down the engine and swap the partnums), which he said works but carries no support. The poster, facing hundreds of GB, concluded he'd file an RFE asking IBM to allow moving catalog tables; no supported in-place fix was found.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, We are running Informix 11.70.FC6. Is there a way to rebuild some system tables in a database. Basically, looking to move some tables like sysdistrib, syscolumns, sysprocedures, sysprocbody, sysfragments etc... as they are residing in a chunk that I need to drop. It is not the 1st chunk of the DB, rather it ended up in the 6th chunk of a DB and would like to move those tables to the 1st chunk. Any Suggestions? --Dave --047d7b86ebce786a44051b62c80f
The ONLY way is to export the database, drop the database, drop the chunk, recreate the database, reload the data. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jul 21, 2015 at 9:39 AM, Informix DBA <in4mixdba@gmail.com> wrote: > Hi, > > We are running Informix 11.70.FC6. Is there a way to rebuild some system > tables in a database. Basically, looking to move some tables > like sysdistrib, syscolumns, sysprocedures, sysprocbody, sysfragments > etc... as they are residing in a chunk that I need to drop. It is not the > 1st chunk of the DB, rather it ended up in the 6th chunk of a DB and would > like to move those tables to the 1st chunk. Any Suggestions? > > --Dave > > --047d7b86ebce786a44051b62c80f > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113df88ed5f5a3051b636d19
This is correct, there is only one SUPPORTED way of doing. However ....
Note - this is NOT SUPPORTED
Create newtables exactly the same as systables
Insert into newtablesFind the partnums
Down the engine
Switch the partnums
Bring the engine back online
This works fine but it is 110% UNSUPPORTED, Note - this is NOT SUPPORTED
For more details look for the Advance SPL presentations I have made the
IIUG conferences over the years, the above is detailed in there. I would
send you a copy but the IIUG do not allow me to send my presentations
without their express permission.
Cheers
Paul
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Tuesday, July 21, 2015 9:26 AM
> To: ids@iiug.org
> Subject: Re: Moving System Tables [35492]
>
> The ONLY way is to export the database, drop the database, drop the chunk,
> recreate the database, reload the data.
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com
>
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on the IIUG, nor any other organization with which I am
> associated either explicitly, implicitly, or by inference. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity with which I am affiliated nor those of the entities themselves.
>
> On Tue, Jul 21, 2015 at 9:39 AM, Informix DBA <in4mixdba@gmail.com>
> wrote:
>
> > Hi,
> >
> > We are running Informix 11.70.FC6. Is there a way to rebuild some system
> > tables in a database. Basically, looking to move some tables
> > like sysdistrib, syscolumns, sysprocedures, sysprocbody, sysfragments
> > etc... as they are residing in a chunk that I need to drop. It is not
the
> > 1st chunk of the DB, rather it ended up in the 6th chunk of a DB and
would
> > like to move those tables to the 1st chunk. Any Suggestions?
> >
> > --Dave
> >
> > --047d7b86ebce786a44051b62c80f
> >
> >
> >
> >
> **********************************************************
> *********************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a113df88ed5f5a3051b636d19
>
>
> **********************************************************
> *********************
> Forum Note: Use "Reply" to post a response in the discussion forum.
Yes, but its not supported.
> On 21 Jul 2015, at 15:57, Paul Watson <paul@oninit.com> wrote:
>
> This is correct, there is only one SUPPORTED way of doing. However ....
>
> Note - this is NOT SUPPORTED
>
> Create newtables exactly the same as systables
> Insert into newtables> Find the partnums
> Down the engine
> Switch the partnums
> Bring the engine back online
>
> This works fine but it is 110% UNSUPPORTED, Note - this is NOT SUPPORTED
>
> For more details look for the Advance SPL presentations I have made the
> IIUG conferences over the years, the above is detailed in there. I would
> send you a copy but the IIUG do not allow me to send my presentations
> without their express permission.
>
> Cheers
> Paul
>
>> -----Original Message-----
>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
>> Kagel
>> Sent: Tuesday, July 21, 2015 9:26 AM
>> To: ids@iiug.org
>> Subject: Re: Moving System Tables [35492]
>>
>> The ONLY way is to export the database, drop the database, drop the chunk,
>> recreate the database, reload the data.
>>
>> Art
>>
>> Art S. Kagel, President and Principal Consultant
>> ASK Database Management
>> www.askdbmgt.com
>>
>> Blog: http://informix-myview.blogspot.com/
>>
>> Disclaimer: Please keep in mind that my own opinions are my own opinions
>> and do not reflect on the IIUG, nor any other organization with which I am
>> associated either explicitly, implicitly, or by inference. Neither do
>> those opinions reflect those of other individuals affiliated with any
>> entity with which I am affiliated nor those of the entities themselves.
>>
>> On Tue, Jul 21, 2015 at 9:39 AM, Informix DBA <in4mixdba@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> We are running Informix 11.70.FC6. Is there a way to rebuild some system
>>> tables in a database. Basically, looking to move some tables
>>> like sysdistrib, syscolumns, sysprocedures, sysprocbody, sysfragments
>>> etc... as they are residing in a chunk that I need to drop. It is not
> the
>>> 1st chunk of the DB, rather it ended up in the 6th chunk of a DB and
> would
>>> like to move those tables to the 1st chunk. Any Suggestions?
>>>
>>> --Dave
>>>
>>> --047d7b86ebce786a44051b62c80f
>>>
>>>
>>>
>>>
>> **********************************************************
>> *********************
>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>
>>>
>>
>> --001a113df88ed5f5a3051b636d19
>>
>>
>> **********************************************************
>> *********************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
No it is not supported but it does work.
We had a customer with a little over 1.5TB of data and they ran out of
extents on sysprocedures, export/import was not an option.
There was a RFE to allow moment of the systables .....
Cheers
Paul
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Spokey Wheeler
> Sent: Tuesday, July 21, 2015 10:22 AM
> To: ids@iiug.org
> Subject: Re: Moving System Tables [35497]
>
> Yes, but its not supported.
>
> > On 21 Jul 2015, at 15:57, Paul Watson <paul@oninit.com> wrote:
> >
> > This is correct, there is only one SUPPORTED way of doing. However ....
> >
> > Note - this is NOT SUPPORTED
> >
> > Create newtables exactly the same as systables
> > Insert into newtables> > Find the partnums
> > Down the engine
> > Switch the partnums
> > Bring the engine back online
> >
> > This works fine but it is 110% UNSUPPORTED, Note - this is NOT
> SUPPORTED
> >
> > For more details look for the Advance SPL presentations I have made the
> > IIUG conferences over the years, the above is detailed in there. I would
> > send you a copy but the IIUG do not allow me to send my presentations
> > without their express permission.
> >
> > Cheers
> > Paul
> >
> >> -----Original Message-----
> >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art
> >> Kagel
> >> Sent: Tuesday, July 21, 2015 9:26 AM
> >> To: ids@iiug.org
> >> Subject: Re: Moving System Tables [35492]
> >>
> >> The ONLY way is to export the database, drop the database, drop the
> chunk,
> >> recreate the database, reload the data.
> >>
> >> Art
> >>
> >> Art S. Kagel, President and Principal Consultant
> >> ASK Database Management
> >> www.askdbmgt.com
> >>
> >> Blog: http://informix-myview.blogspot.com/
> >>
> >> Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> >> and do not reflect on the IIUG, nor any other organization with which I
am
> >> associated either explicitly, implicitly, or by inference. Neither do
> >> those opinions reflect those of other individuals affiliated with any
> >> entity with which I am affiliated nor those of the entities themselves.
> >>
> >> On Tue, Jul 21, 2015 at 9:39 AM, Informix DBA <in4mixdba@gmail.com>
> >> wrote:
> >>
> >>> Hi,
> >>>
> >>> We are running Informix 11.70.FC6. Is there a way to rebuild some
> system
> >>> tables in a database. Basically, looking to move some tables
> >>> like sysdistrib, syscolumns, sysprocedures, sysprocbody, sysfragments
> >>> etc... as they are residing in a chunk that I need to drop. It is not
> > the
> >>> 1st chunk of the DB, rather it ended up in the 6th chunk of a DB and
> > would
> >>> like to move those tables to the 1st chunk. Any Suggestions?
> >>>
> >>> --Dave
> >>>
> >>> --047d7b86ebce786a44051b62c80f
> >>>
> >>>
> >>>
> >>>
> >>
> **********************************************************
> >> *********************
> >>> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>>
> >>>
> >>
> >> --001a113df88ed5f5a3051b636d19
> >>
> >>
> >>
> **********************************************************
> >> *********************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> **********************************************************
> *********************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> **********************************************************
> *********************
> Forum Note: Use "Reply" to post a response in the discussion forum.
Thank You Art. This DB has hundreds of GB's of data and would be a challenge to Export. Guess its RFE time since it would be really nice to be able to have the ability to move the system tables if necessary. Thank You, --Dave On Tue, Jul 21, 2015 at 10:25 AM, Art Kagel <art.kagel@gmail.com> wrote: > The ONLY way is to export the database, drop the database, drop the chunk, > recreate the database, reload the data. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Tue, Jul 21, 2015 at 9:39 AM, Informix DBA <in4mixdba@gmail.com> wrote: > > > Hi, > > > > We are running Informix 11.70.FC6. Is there a way to rebuild some system > > tables in a database. Basically, looking to move some tables > > like sysdistrib, syscolumns, sysprocedures, sysprocbody, sysfragments > > etc... as they are residing in a chunk that I need to drop. It is not the > > 1st chunk of the DB, rather it ended up in the 6th chunk of a DB and > would > > like to move those tables to the 1st chunk. Any Suggestions? > > > > --Dave > > > > --047d7b86ebce786a44051b62c80f > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a113df88ed5f5a3051b636d19 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bb03db294e8c6051b653dc4
Youve been across the pond too long, mate.
> On 21 Jul 2015, at 16:39, Paul Watson <paul@oninit.com> wrote:
>
> No it is not supported but it does work.
>
> We had a customer with a little over 1.5TB of data and they ran out of
> extents on sysprocedures, export/import was not an option.
>
> There was a RFE to allow moment of the systables .....
>
> Cheers
> Paul
>
>> -----Original Message-----
>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
>> Spokey Wheeler
>> Sent: Tuesday, July 21, 2015 10:22 AM
>> To: ids@iiug.org
>> Subject: Re: Moving System Tables [35497]
>>
>> Yes, but its not supported.
>>
>>> On 21 Jul 2015, at 15:57, Paul Watson <paul@oninit.com> wrote:
>>>
>>> This is correct, there is only one SUPPORTED way of doing. However ....
>>>
>>> Note - this is NOT SUPPORTED
>>>
>>> Create newtables exactly the same as systables
>>> Insert into newtables>>> Find the partnums
>>> Down the engine
>>> Switch the partnums
>>> Bring the engine back online
>>>
>>> This works fine but it is 110% UNSUPPORTED, Note - this is NOT
>> SUPPORTED
>>>
>>> For more details look for the Advance SPL presentations I have made the
>>> IIUG conferences over the years, the above is detailed in there. I would
>>> send you a copy but the IIUG do not allow me to send my presentations
>>> without their express permission.
>>>
>>> Cheers
>>> Paul
>>>
>>>> -----Original Message-----
>>>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
>> Art
>>>> Kagel
>>>> Sent: Tuesday, July 21, 2015 9:26 AM
>>>> To: ids@iiug.org
>>>> Subject: Re: Moving System Tables [35492]
>>>>
>>>> The ONLY way is to export the database, drop the database, drop the
>> chunk,
>>>> recreate the database, reload the data.
>>>>
>>>> Art
>>>>
>>>> Art S. Kagel, President and Principal Consultant
>>>> ASK Database Management
>>>> www.askdbmgt.com
>>>>
>>>> Blog: http://informix-myview.blogspot.com/
>>>>
>>>> Disclaimer: Please keep in mind that my own opinions are my own
>> opinions
>>>> and do not reflect on the IIUG, nor any other organization with which I
> am
>>>> associated either explicitly, implicitly, or by inference. Neither do
>>>> those opinions reflect those of other individuals affiliated with any
>>>> entity with which I am affiliated nor those of the entities themselves.
>>>>
>>>> On Tue, Jul 21, 2015 at 9:39 AM, Informix DBA <in4mixdba@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> We are running Informix 11.70.FC6. Is there a way to rebuild some
>> system
>>>>> tables in a database. Basically, looking to move some tables
>>>>> like sysdistrib, syscolumns, sysprocedures, sysprocbody, sysfragments
>>>>> etc... as they are residing in a chunk that I need to drop. It is not
>>> the
>>>>> 1st chunk of the DB, rather it ended up in the 6th chunk of a DB and
>>> would
>>>>> like to move those tables to the 1st chunk. Any Suggestions?
>>>>>
>>>>> --Dave
>>>>>
>>>>> --047d7b86ebce786a44051b62c80f
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>> **********************************************************
>>>> *********************
>>>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>>>
>>>>>
>>>>
>>>> --001a113df88ed5f5a3051b636d19
>>>>
>>>>
>>>>
>> **********************************************************
>>>> *********************
>>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>
>>>
>>>
>> **********************************************************
>> *********************
>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>
>>
>>
>> **********************************************************
>> *********************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
And here I always thought that Paul's distain of authority came from having
spent his formative years on your side of the pond!
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Tue, Jul 21, 2015 at 12:37 PM, Spokey Wheeler <spokey.wheeler@gmail.com>
wrote:
> Youve been across the pond too long, mate.
>
> > On 21 Jul 2015, at 16:39, Paul Watson <paul@oninit.com> wrote:
> >
> > No it is not supported but it does work.
> >
> > We had a customer with a little over 1.5TB of data and they ran out of
> > extents on sysprocedures, export/import was not an option.
> >
> > There was a RFE to allow moment of the systables .....
> >
> > Cheers
> > Paul
> >
> >> -----Original Message-----
> >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> >> Spokey Wheeler
> >> Sent: Tuesday, July 21, 2015 10:22 AM
> >> To: ids@iiug.org
> >> Subject: Re: Moving System Tables [35497]
> >>
> >> Yes, but its not supported.
> >>
> >>> On 21 Jul 2015, at 15:57, Paul Watson <paul@oninit.com> wrote:
> >>>
> >>> This is correct, there is only one SUPPORTED way of doing. However ....
> >>>
> >>> Note - this is NOT SUPPORTED
> >>>
> >>> Create newtables exactly the same as systables
> >>> Insert into newtables> >>> Find the partnums
> >>> Down the engine
> >>> Switch the partnums
> >>> Bring the engine back online
> >>>
> >>> This works fine but it is 110% UNSUPPORTED, Note - this is NOT
> >> SUPPORTED
> >>>
> >>> For more details look for the Advance SPL presentations I have made the
> >>> IIUG conferences over the years, the above is detailed in there. I
> would
> >>> send you a copy but the IIUG do not allow me to send my presentations
> >>> without their express permission.
> >>>
> >>> Cheers
> >>> Paul
> >>>
> >>>> -----Original Message-----
> >>>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> >> Art
> >>>> Kagel
> >>>> Sent: Tuesday, July 21, 2015 9:26 AM
> >>>> To: ids@iiug.org
> >>>> Subject: Re: Moving System Tables [35492]
> >>>>
> >>>> The ONLY way is to export the database, drop the database, drop the
> >> chunk,
> >>>> recreate the database, reload the data.
> >>>>
> >>>> Art
> >>>>
> >>>> Art S. Kagel, President and Principal Consultant
> >>>> ASK Database Management
> >>>> www.askdbmgt.com
> >>>>
> >>>> Blog: http://informix-myview.blogspot.com/
> >>>>
> >>>> Disclaimer: Please keep in mind that my own opinions are my own
> >> opinions
> >>>> and do not reflect on the IIUG, nor any other organization with which
> I
> > am
> >>>> associated either explicitly, implicitly, or by inference. Neither do
> >>>> those opinions reflect those of other individuals affiliated with any
> >>>> entity with which I am affiliated nor those of the entities
> themselves.
> >>>>
> >>>> On Tue, Jul 21, 2015 at 9:39 AM, Informix DBA <in4mixdba@gmail.com>
> >>>> wrote:
> >>>>
> >>>>> Hi,
> >>>>>
> >>>>> We are running Informix 11.70.FC6. Is there a way to rebuild some
> >> system
> >>>>> tables in a database. Basically, looking to move some tables
> >>>>> like sysdistrib, syscolumns, sysprocedures, sysprocbody, sysfragments
> >>>>> etc... as they are residing in a chunk that I need to drop. It is not
> >>> the
> >>>>> 1st chunk of the DB, rather it ended up in the 6th chunk of a DB and
> >>> would
> >>>>> like to move those tables to the 1st chunk. Any Suggestions?
> >>>>>
> >>>>> --Dave
> >>>>>
> >>>>> --047d7b86ebce786a44051b62c80f
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>
> >> **********************************************************
> >>>> *********************
> >>>>> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>>>>
> >>>>>
> >>>>
> >>>> --001a113df88ed5f5a3051b636d19
> >>>>
> >>>>
> >>>>
> >> **********************************************************
> >>>> *********************
> >>>> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>>
> >>>
> >>>
> >> **********************************************************
> >> *********************
> >>> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>>
> >>
> >>
> >> **********************************************************
> >> *********************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a1140c18ae3e52a051b6581ae