RE: moving database tables from one dbspace to ano
Larry (IDS 11.70.FC1 on RHEL 5) asked for the easiest way to move thousands of tables from one dbspace to another, and whether ontape could do it. Art Kagel said ontape can't; the right approach is ALTER FRAGMENT ON TABLE <tab> INIT IN <dbspace>, scripted for many tables using his dbscript.ec utility from the utils2_ak package. After Larry hit download/gunzip errors (Art mailed him a copy) and confused dbscript with dbstruct, Art gave a one-liner: dbscript -d <database> -c 'alter fragment on table %s init in <new dbspace>;' | dbaccess -e <database> -.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.70.FC1
RHEL 5
What is the easiest and most straight forward way that people have used to
move 1000s of tables from one dbspace to another?
Can ontape be used to do this (moving the entire database) to the new dbspace
for the data tables and leaving the indexes, logs, etc. in the existing
dbspaces?
Suggestions?
Larry
> To: ids@iiug.org
> From: lsorensen25@msn.com
> Subject: dbspace will not allow new tables to be created [34744]
> Date: Mon, 2 Mar 2015 09:41:33 -0500
>
> IDS 11.70.FC1
> RHEL 5
>
> We have a server that is erroring out with new table creation, including
> temporary tables. Many tables will not allow us to insert new data. There is
> space in the dbspace. We tried to create a table in another dbspace, and it
> worked; but the primary data dbspace will not. The database is not
excessively
> large, but there does appear to be a table or two that has reached the page
> limit and will need to be fragmented or moved to another dbspace with a
> non-default pagesize.
>
> My question is, does a dbspace have a page limit as well; or what other
> situations could cause this?
>
> Thanks.
>
> Larry
>
> > To: ids@iiug.org
> > From: mpruet@us.ibm.com
> > Subject: Re: Replicate [34742]
> > Date: Sat, 28 Feb 2015 09:54:44 -0500
> >
> > You can still use 'cdr check' with a grid environment.
> >
> > From: "Jorge Valenzuela" <jorgervt@gmail.com>
> > To: ids@iiug.org
> > Date: 02/28/2015 08:51 AM
> > Subject: Re: Replicate [34741]
> > Sent by: ids-bounces@iiug.org
> >
> > We used the grid in this replication is there a way to validate if all =
> > the
> > information its the same on both sides?
> >
> > Enviado desde mi iPhone
> >
> > > El 27/02/2015, a las 12:59, Art Kagel <art.kagel@gmail.com> escribi=F3=
> > :
> > >
> > > To replicate between different platforms you have to either use
> > Enterprise
> > > Replication (ER) or a third party replication system like DbMoto. Bot=
> > h
> > > have features that will let you validate the current state of the
> > > replicates.
> > >
> > > 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 opini=
> > ons
> > > 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 themselve=
> > s.
> > >
> > > On Fri, Feb 27, 2015 at 3:48 PM, Jorge Valenzuela <jorgervt@gmail.com=
> > >
> > > wrote:
> > >
> > >> Hi,
> > >> We set up an replication from aix to suse.
> > >> How can I know if all the data are replicating correctly ?
> > >> Thanks in advance.
> > >>
> > >> Enviado desde mi iPhone
> > >
> > ***********************************************************************=
> > ********
> >
> > >> Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > > --001a113ecaac6d589f0510182415
> > >
> > >
> > >
> > ***********************************************************************=
> > ********
> >
> > > 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.
>
No. You cannot use ontape for that. Probably using the ALTER FRAGMENT ON
TABLE tablename INIT IN dbspacename; command is your best option. To
automate creating a script for a large number of tables look at using my
dbscript.ec utility in the utils2_ak package.
Art
On Mar 3, 2015 9:53 AM, "LARRY SORENSEN" <lsorensen25@msn.com> wrote:
> IDS 11.70.FC1
> RHEL 5
>
> What is the easiest and most straight forward way that people have used to
> move 1000s of tables from one dbspace to another?
> Can ontape be used to do this (moving the entire database) to the new
> dbspace
> for the data tables and leaving the indexes, logs, etc. in the existing
> dbspaces?
>
> Suggestions?
>
> Larry
> > To: ids@iiug.org
> > From: lsorensen25@msn.com
> > Subject: dbspace will not allow new tables to be created [34744]
> > Date: Mon, 2 Mar 2015 09:41:33 -0500
> >
> > IDS 11.70.FC1
> > RHEL 5
> >
> > We have a server that is erroring out with new table creation, including
> > temporary tables. Many tables will not allow us to insert new data.
> There is
> > space in the dbspace. We tried to create a table in another dbspace, and
> it
> > worked; but the primary data dbspace will not. The database is not
> excessively
> > large, but there does appear to be a table or two that has reached the
> page
> > limit and will need to be fragmented or moved to another dbspace with a
> > non-default pagesize.
> >
> > My question is, does a dbspace have a page limit as well; or what other
> > situations could cause this?
> >
> > Thanks.
> >
> > Larry
> >
> > > To: ids@iiug.org
> > > From: mpruet@us.ibm.com
> > > Subject: Re: Replicate [34742]
> > > Date: Sat, 28 Feb 2015 09:54:44 -0500
> > >
> > > You can still use 'cdr check' with a grid environment.
> > >
> > > From: "Jorge Valenzuela" <jorgervt@gmail.com>
> > > To: ids@iiug.org
> > > Date: 02/28/2015 08:51 AM
> > > Subject: Re: Replicate [34741]
> > > Sent by: ids-bounces@iiug.org
> > >
> > > We used the grid in this replication is there a way to validate if all
> =
> > > the
> > > information its the same on both sides?
> > >
> > > Enviado desde mi iPhone
> > >
> > > > El 27/02/2015, a las 12:59, Art Kagel <art.kagel@gmail.com>
> escribi=F3=
> > > :
> > > >
> > > > To replicate between different platforms you have to either use
> > > Enterprise
> > > > Replication (ER) or a third party replication system like DbMoto.
> Bot=
> > > h
> > > > have features that will let you validate the current state of the
> > > > replicates.
> > > >
> > > > 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
> opini=
> > > ons
> > > > 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
> themselve=
> > > s.
> > > >
> > > > On Fri, Feb 27, 2015 at 3:48 PM, Jorge Valenzuela <
> jorgervt@gmail.com=
> > > >
> > > > wrote:
> > > >
> > > >> Hi,
> > > >> We set up an replication from aix to suse.
> > > >> How can I know if all the data are replicating correctly ?
> > > >> Thanks in advance.
> > > >>
> > > >> Enviado desde mi iPhone
> > > >
> > >
> ***********************************************************************=
> > > ********
> > >
> > > >> Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > > --001a113ecaac6d589f0510182415
> > > >
> > > >
> > > >
> > >
> ***********************************************************************=
> > > ********
> > >
> > > > 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.
>
>
--001a11c3d23271cd520510644687
I am trying to download the package from IIUG, and I keep getting errors. Is
anyone else having problems?
Larry
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: RE: moving database tables from one dbspace to.... [34755]
> Date: Tue, 3 Mar 2015 10:49:20 -0500
>
> No. You cannot use ontape for that. Probably using the ALTER FRAGMENT ON
> TABLE tablename INIT IN dbspacename; command is your best option. To
> automate creating a script for a large number of tables look at using my
> dbscript.ec utility in the utils2_ak package.
>
> Art
> On Mar 3, 2015 9:53 AM, "LARRY SORENSEN" <lsorensen25@msn.com> wrote:
>
> > IDS 11.70.FC1
> > RHEL 5
> >
> > What is the easiest and most straight forward way that people have used to
> > move 1000s of tables from one dbspace to another?
> > Can ontape be used to do this (moving the entire database) to the new
> > dbspace
> > for the data tables and leaving the indexes, logs, etc. in the existing
> > dbspaces?
> >
> > Suggestions?
> >
> > Larry
> > > To: ids@iiug.org
> > > From: lsorensen25@msn.com
> > > Subject: dbspace will not allow new tables to be created [34744]
> > > Date: Mon, 2 Mar 2015 09:41:33 -0500
> > >
> > > IDS 11.70.FC1
> > > RHEL 5
> > >
> > > We have a server that is erroring out with new table creation, including
> > > temporary tables. Many tables will not allow us to insert new data.
> > There is
> > > space in the dbspace. We tried to create a table in another dbspace, and
> > it
> > > worked; but the primary data dbspace will not. The database is not
> > excessively
> > > large, but there does appear to be a table or two that has reached the
> > page
> > > limit and will need to be fragmented or moved to another dbspace with a
> > > non-default pagesize.
> > >
> > > My question is, does a dbspace have a page limit as well; or what other
> > > situations could cause this?
> > >
> > > Thanks.
> > >
> > > Larry
> > >
> > > > To: ids@iiug.org
> > > > From: mpruet@us.ibm.com
> > > > Subject: Re: Replicate [34742]
> > > > Date: Sat, 28 Feb 2015 09:54:44 -0500
> > > >
> > > > You can still use 'cdr check' with a grid environment.
> > > >
> > > > From: "Jorge Valenzuela" <jorgervt@gmail.com>
> > > > To: ids@iiug.org
> > > > Date: 02/28/2015 08:51 AM
> > > > Subject: Re: Replicate [34741]
> > > > Sent by: ids-bounces@iiug.org
> > > >
> > > > We used the grid in this replication is there a way to validate if all
> > =
> > > > the
> > > > information its the same on both sides?
> > > >
> > > > Enviado desde mi iPhone
> > > >
> > > > > El 27/02/2015, a las 12:59, Art Kagel <art.kagel@gmail.com>
> > escribi=F3=
> > > > :
> > > > >
> > > > > To replicate between different platforms you have to either use
> > > > Enterprise
> > > > > Replication (ER) or a third party replication system like DbMoto.
> > Bot=
> > > > h
> > > > > have features that will let you validate the current state of the
> > > > > replicates.
> > > > >
> > > > > 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
> > opini=
> > > > ons
> > > > > 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
> > themselve=
> > > > s.
> > > > >
> > > > > On Fri, Feb 27, 2015 at 3:48 PM, Jorge Valenzuela <
> > jorgervt@gmail.com=
> > > > >
> > > > > wrote:
> > > > >
> > > > >> Hi,
> > > > >> We set up an replication from aix to suse.
> > > > >> How can I know if all the data are replicating correctly ?
> > > > >> Thanks in advance.
> > > > >>
> > > > >> Enviado desde mi iPhone
> > > > >
> > > >
> > ***********************************************************************=
> > > > ********
> > > >
> > > > >> Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > > --001a113ecaac6d589f0510182415
> > > > >
> > > > >
> > > > >
> > > >
> > ***********************************************************************=
> > > > ********
> > > >
> > > > > 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.
> >
> >
>
> --001a11c3d23271cd520510644687
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Sent Larry the latest release of utils2_ak. Anyone else who's having
trouble getting the package, please let me know and I'll check on the IIUG
Repository later today.
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, Mar 3, 2015 at 12:02 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> I am trying to download the package from IIUG, and I keep getting errors.
> Is
> anyone else having problems?
>
> Larry
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: RE: moving database tables from one dbspace to.... [34755]
> > Date: Tue, 3 Mar 2015 10:49:20 -0500
> >
> > No. You cannot use ontape for that. Probably using the ALTER FRAGMENT ON
> > TABLE tablename INIT IN dbspacename; command is your best option. To
> > automate creating a script for a large number of tables look at using my
> > dbscript.ec utility in the utils2_ak package.
> >
> > Art
> > On Mar 3, 2015 9:53 AM, "LARRY SORENSEN" <lsorensen25@msn.com> wrote:
> >
> > > IDS 11.70.FC1
> > > RHEL 5
> > >
> > > What is the easiest and most straight forward way that people have
> used to
> > > move 1000s of tables from one dbspace to another?
> > > Can ontape be used to do this (moving the entire database) to the new
> > > dbspace
> > > for the data tables and leaving the indexes, logs, etc. in the existing
> > > dbspaces?
> > >
> > > Suggestions?
> > >
> > > Larry
> > > > To: ids@iiug.org
> > > > From: lsorensen25@msn.com
> > > > Subject: dbspace will not allow new tables to be created [34744]
> > > > Date: Mon, 2 Mar 2015 09:41:33 -0500
> > > >
> > > > IDS 11.70.FC1
> > > > RHEL 5
> > > >
> > > > We have a server that is erroring out with new table creation,
> including
> > > > temporary tables. Many tables will not allow us to insert new data.
> > > There is
> > > > space in the dbspace. We tried to create a table in another dbspace,
> and
> > > it
> > > > worked; but the primary data dbspace will not. The database is not
> > > excessively
> > > > large, but there does appear to be a table or two that has reached
> the
> > > page
> > > > limit and will need to be fragmented or moved to another dbspace
> with a
> > > > non-default pagesize.
> > > >
> > > > My question is, does a dbspace have a page limit as well; or what
> other
> > > > situations could cause this?
> > > >
> > > > Thanks.
> > > >
> > > > Larry
> > > >
> > > > > To: ids@iiug.org
> > > > > From: mpruet@us.ibm.com
> > > > > Subject: Re: Replicate [34742]
> > > > > Date: Sat, 28 Feb 2015 09:54:44 -0500
> > > > >
> > > > > You can still use 'cdr check' with a grid environment.
> > > > >
> > > > > From: "Jorge Valenzuela" <jorgervt@gmail.com>
> > > > > To: ids@iiug.org
> > > > > Date: 02/28/2015 08:51 AM
> > > > > Subject: Re: Replicate [34741]
> > > > > Sent by: ids-bounces@iiug.org
> > > > >
> > > > > We used the grid in this replication is there a way to validate if
> all
> > > =
> > > > > the
> > > > > information its the same on both sides?
> > > > >
> > > > > Enviado desde mi iPhone
> > > > >
> > > > > > El 27/02/2015, a las 12:59, Art Kagel <art.kagel@gmail.com>
> > > escribi=F3=
> > > > > :
> > > > > >
> > > > > > To replicate between different platforms you have to either use
> > > > > Enterprise
> > > > > > Replication (ER) or a third party replication system like DbMoto.
> > > Bot=
> > > > > h
> > > > > > have features that will let you validate the current state of the
> > > > > > replicates.
> > > > > >
> > > > > > 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
> > > opini=
> > > > > ons
> > > > > > 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
> > > themselve=
> > > > > s.
> > > > > >
> > > > > > On Fri, Feb 27, 2015 at 3:48 PM, Jorge Valenzuela <
> > > jorgervt@gmail.com=
> > > > > >
> > > > > > wrote:
> > > > > >
> > > > > >> Hi,
> > > > > >> We set up an replication from aix to suse.
> > > > > >> How can I know if all the data are replicating correctly ?
> > > > > >> Thanks in advance.
> > > > > >>
> > > > > >> Enviado desde mi iPhone
> > > > > >
> > > > >
> > >
> ***********************************************************************=
> > > > > ********
> > > > >
> > > > > >> Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > > >
> > > > > > --001a113ecaac6d589f0510182415
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > >
> ***********************************************************************=
> > > > > ********
> > > > >
> > > > > > 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.
> > >
> > >
> >
> > --001a11c3d23271cd520510644687
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
******************************************************
The problem I am having now is that when I use gunzip, I receive
gzip: utils2_ak.gz: unexpected end of file
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: moving database tables from one dbspace to.... [34758]
> Date: Tue, 3 Mar 2015 12:08:17 -0500
>
> Sent Larry the latest release of utils2_ak. Anyone else who's having
> trouble getting the package, please let me know and I'll check on the IIUG
> Repository later today.
>
> 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, Mar 3, 2015 at 12:02 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > I am trying to download the package from IIUG, and I keep getting errors.
> > Is
> > anyone else having problems?
> >
> > Larry
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: RE: moving database tables from one dbspace to.... [34755]
> > > Date: Tue, 3 Mar 2015 10:49:20 -0500
> > >
> > > No. You cannot use ontape for that. Probably using the ALTER FRAGMENT ON
> > > TABLE tablename INIT IN dbspacename; command is your best option. To
> > > automate creating a script for a large number of tables look at using my
> > > dbscript.ec utility in the utils2_ak package.
> > >
> > > Art
> > > On Mar 3, 2015 9:53 AM, "LARRY SORENSEN" <lsorensen25@msn.com> wrote:
> > >
> > > > IDS 11.70.FC1
> > > > RHEL 5
> > > >
> > > > What is the easiest and most straight forward way that people have
> > used to
> > > > move 1000s of tables from one dbspace to another?
> > > > Can ontape be used to do this (moving the entire database) to the new
> > > > dbspace
> > > > for the data tables and leaving the indexes, logs, etc. in the existing
> > > > dbspaces?
> > > >
> > > > Suggestions?
> > > >
> > > > Larry
> > > > > To: ids@iiug.org
> > > > > From: lsorensen25@msn.com
> > > > > Subject: dbspace will not allow new tables to be created [34744]
> > > > > Date: Mon, 2 Mar 2015 09:41:33 -0500
> > > > >
> > > > > IDS 11.70.FC1
> > > > > RHEL 5
> > > > >
> > > > > We have a server that is erroring out with new table creation,
> > including
> > > > > temporary tables. Many tables will not allow us to insert new data.
> > > > There is
> > > > > space in the dbspace. We tried to create a table in another dbspace,
> > and
> > > > it
> > > > > worked; but the primary data dbspace will not. The database is not
> > > > excessively
> > > > > large, but there does appear to be a table or two that has reached
> > the
> > > > page
> > > > > limit and will need to be fragmented or moved to another dbspace
> > with a
> > > > > non-default pagesize.
> > > > >
> > > > > My question is, does a dbspace have a page limit as well; or what
> > other
> > > > > situations could cause this?
> > > > >
> > > > > Thanks.
> > > > >
> > > > > Larry
> > > > >
> > > > > > To: ids@iiug.org
> > > > > > From: mpruet@us.ibm.com
> > > > > > Subject: Re: Replicate [34742]
> > > > > > Date: Sat, 28 Feb 2015 09:54:44 -0500
> > > > > >
> > > > > > You can still use 'cdr check' with a grid environment.
> > > > > >
> > > > > > From: "Jorge Valenzuela" <jorgervt@gmail.com>
> > > > > > To: ids@iiug.org
> > > > > > Date: 02/28/2015 08:51 AM
> > > > > > Subject: Re: Replicate [34741]
> > > > > > Sent by: ids-bounces@iiug.org
> > > > > >
> > > > > > We used the grid in this replication is there a way to validate if
> > all
> > > > =
> > > > > > the
> > > > > > information its the same on both sides?
> > > > > >
> > > > > > Enviado desde mi iPhone
> > > > > >
> > > > > > > El 27/02/2015, a las 12:59, Art Kagel <art.kagel@gmail.com>
> > > > escribi=F3=
> > > > > > :
> > > > > > >
> > > > > > > To replicate between different platforms you have to either use
> > > > > > Enterprise
> > > > > > > Replication (ER) or a third party replication system like DbMoto.
> > > > Bot=
> > > > > > h
> > > > > > > have features that will let you validate the current state of the
> > > > > > > replicates.
> > > > > > >
> > > > > > > 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
> > > > opini=
> > > > > > ons
> > > > > > > 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
> > > > themselve=
> > > > > > s.
> > > > > > >
> > > > > > > On Fri, Feb 27, 2015 at 3:48 PM, Jorge Valenzuela <
> > > > jorgervt@gmail.com=
> > > > > > >
> > > > > > > wrote:
> > > > > > >
> > > > > > >> Hi,
> > > > > > >> We set up an replication from aix to suse.
> > > > > > >> How can I know if all the data are replicating correctly ?
> > > > > > >> Thanks in advance.
> > > > > > >>
> > > > > > >> Enviado desde mi iPhone
> > > > > > >
> > > > > >
> > > >
> > ***********************************************************************=
> > > > > > ********
> > > > > >
> > > > > > >> Forum Note: Use "Reply" to post a response in the discussion
> > forum.
> > > > > > >
> > > > > > > --001a113ecaac6d589f0510182415
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > >
> > ***********************************************************************=
> > > > > > ********
> > > > > >
> > > > > > > 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.
> > > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
>
****************************************************************************
Do you have a little blerb on how to use dbscript.ec to create an ALTER
FRAGMENT....INIT.. on every table in a database? I didn't quite understand
what is required with
Usage: dbstruct [-F] [-h hostname] -d databasename [-t tablename] [filename]
Larry
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: RE: moving database tables from one dbspace to.... [34755]
> Date: Tue, 3 Mar 2015 10:49:20 -0500
>
> No. You cannot use ontape for that. Probably using the ALTER FRAGMENT ON
> TABLE tablename INIT IN dbspacename; command is your best option. To
> automate creating a script for a large number of tables look at using my
> dbscript.ec utility in the utils2_ak package.
>
> Art
> On Mar 3, 2015 9:53 AM, "LARRY SORENSEN" <lsorensen25@msn.com> wrote:
>
> > IDS 11.70.FC1
> > RHEL 5
> >
> > What is the easiest and most straight forward way that people have used to
> > move 1000s of tables from one dbspace to another?
> > Can ontape be used to do this (moving the entire database) to the new
> > dbspace
> > for the data tables and leaving the indexes, logs, etc. in the existing
> > dbspaces?
> >
> > Suggestions?
> >
> > Larry
> > > To: ids@iiug.org
> > > From: lsorensen25@msn.com
> > > Subject: dbspace will not allow new tables to be created [34744]
> > > Date: Mon, 2 Mar 2015 09:41:33 -0500
> > >
> > > IDS 11.70.FC1
> > > RHEL 5
> > >
> > > We have a server that is erroring out with new table creation, including
> > > temporary tables. Many tables will not allow us to insert new data.
> > There is
> > > space in the dbspace. We tried to create a table in another dbspace, and
> > it
> > > worked; but the primary data dbspace will not. The database is not
> > excessively
> > > large, but there does appear to be a table or two that has reached the
> > page
> > > limit and will need to be fragmented or moved to another dbspace with a
> > > non-default pagesize.
> > >
> > > My question is, does a dbspace have a page limit as well; or what other
> > > situations could cause this?
> > >
> > > Thanks.
> > >
> > > Larry
> > >
> > > > To: ids@iiug.org
> > > > From: mpruet@us.ibm.com
> > > > Subject: Re: Replicate [34742]
> > > > Date: Sat, 28 Feb 2015 09:54:44 -0500
> > > >
> > > > You can still use 'cdr check' with a grid environment.
> > > >
> > > > From: "Jorge Valenzuela" <jorgervt@gmail.com>
> > > > To: ids@iiug.org
> > > > Date: 02/28/2015 08:51 AM
> > > > Subject: Re: Replicate [34741]
> > > > Sent by: ids-bounces@iiug.org
> > > >
> > > > We used the grid in this replication is there a way to validate if all
> > =
> > > > the
> > > > information its the same on both sides?
> > > >
> > > > Enviado desde mi iPhone
> > > >
> > > > > El 27/02/2015, a las 12:59, Art Kagel <art.kagel@gmail.com>
> > escribi=F3=
> > > > :
> > > > >
> > > > > To replicate between different platforms you have to either use
> > > > Enterprise
> > > > > Replication (ER) or a third party replication system like DbMoto.
> > Bot=
> > > > h
> > > > > have features that will let you validate the current state of the
> > > > > replicates.
> > > > >
> > > > > 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
> > opini=
> > > > ons
> > > > > 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
> > themselve=
> > > > s.
> > > > >
> > > > > On Fri, Feb 27, 2015 at 3:48 PM, Jorge Valenzuela <
> > jorgervt@gmail.com=
> > > > >
> > > > > wrote:
> > > > >
> > > > >> Hi,
> > > > >> We set up an replication from aix to suse.
> > > > >> How can I know if all the data are replicating correctly ?
> > > > >> Thanks in advance.
> > > > >>
> > > > >> Enviado desde mi iPhone
> > > > >
> > > >
> > ***********************************************************************=
> > > > ********
> > > >
> > > > >> Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > > --001a113ecaac6d589f0510182415
> > > > >
> > > > >
> > > > >
> > > >
> > ***********************************************************************=
> > > > ********
> > > >
> > > > > 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.
> >
> >
>
> --001a11c3d23271cd520510644687
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
dbscript, not dbstruct. Dbstruct will print out a header file for the
tables (or for the whole database) in C, ESQL/C, structured FORTRAN, or 4GL
format to make develops' jobs easier.
For dbscript:
In one step you can do:
dbscript -d <database> -c 'alter fragment on table %s init in <new
dbspaces;' | dbaccess -e <database> -
If you only want to process some tables the include
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, Mar 3, 2015 at 1:02 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> Do you have a little blerb on how to use dbscript.ec to create an ALTER
> FRAGMENT....INIT.. on every table in a database? I didn't quite understand
> what is required with
>
> Usage: dbstruct [-F] [-h hostname] -d databasename [-t tablename]
> [filename]
> Larry
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: RE: moving database tables from one dbspace to.... [34755]
> > Date: Tue, 3 Mar 2015 10:49:20 -0500
> >
> > No. You cannot use ontape for that. Probably using the ALTER FRAGMENT ON
> > TABLE tablename INIT IN dbspacename; command is your best option. To
> > automate creating a script for a large number of tables look at using my
> > dbscript.ec utility in the utils2_ak package.
> >
> > Art
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e01176c7f08624605106651a4
The reason that I put dbstruct is that was the Usage statement inside of the
dbscript.ec file.
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: moving database tables from one dbspace to.... [34761]
> Date: Tue, 3 Mar 2015 13:15:26 -0500
>
> dbscript, not dbstruct. Dbstruct will print out a header file for the
> tables (or for the whole database) in C, ESQL/C, structured FORTRAN, or 4GL
> format to make develops' jobs easier.
>
> For dbscript:
>
> In one step you can do:
>
> dbscript -d <database> -c 'alter fragment on table %s init in <new
> dbspaces;' | dbaccess -e <database> -
>
> If you only want to process some tables the include
> 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, Mar 3, 2015 at 1:02 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > Do you have a little blerb on how to use dbscript.ec to create an ALTER
> > FRAGMENT....INIT.. on every table in a database? I didn't quite understand
> > what is required with
> >
> > Usage: dbstruct [-F] [-h hostname] -d databasename [-t tablename]
> > [filename]
> > Larry
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: RE: moving database tables from one dbspace to.... [34755]
> > > Date: Tue, 3 Mar 2015 10:49:20 -0500
> > >
> > > No. You cannot use ontape for that. Probably using the ALTER FRAGMENT ON
> > > TABLE tablename INIT IN dbspacename; command is your best option. To
> > > automate creating a script for a large number of tables look at using my
> > > dbscript.ec utility in the utils2_ak package.
> > >
> > > Art
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --089e01176c7f08624605106651a4
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Ahh, a left over comment from cloning the dbstruct code to create
dbscript. I'll fix that, thanks. You can get the actual Usage from
dbscritp or any of my utilities by just running it with no arguments or
with -?
I think that listdb & listdb7 will do something reasonable without args, so
you need - for them. The others all print out usage by default.
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, Mar 3, 2015 at 1:18 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> The reason that I put dbstruct is that was the Usage statement inside of
> the
> dbscript.ec file.
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: moving database tables from one dbspace to.... [34761]
> > Date: Tue, 3 Mar 2015 13:15:26 -0500
> >
> > dbscript, not dbstruct. Dbstruct will print out a header file for the
> > tables (or for the whole database) in C, ESQL/C, structured FORTRAN, or
> 4GL
> > format to make develops' jobs easier.
> >
> > For dbscript:
> >
> > In one step you can do:
> >
> > dbscript -d <database> -c 'alter fragment on table %s init in <new
> > dbspaces;' | dbaccess -e <database> -
> >
> > If you only want to process some tables the include
> > 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, Mar 3, 2015 at 1:02 PM, LARRY SORENSEN <lsorensen25@msn.com>
> wrote:
> >
> > > Do you have a little blerb on how to use dbscript.ec to create an
> ALTER
> > > FRAGMENT....INIT.. on every table in a database? I didn't quite
> understand
> > > what is required with
> > >
> > > Usage: dbstruct [-F] [-h hostname] -d databasename [-t tablename]
> > > [filename]
> > > Larry
> > >
> > > > To: ids@iiug.org
> > > > From: art.kagel@gmail.com
> > > > Subject: RE: moving database tables from one dbspace to.... [34755]
> > > > Date: Tue, 3 Mar 2015 10:49:20 -0500
> > > >
> > > > No. You cannot use ontape for that. Probably using the ALTER
> FRAGMENT ON
> > > > TABLE tablename INIT IN dbspacename; command is your best option. To
> > > > automate creating a script for a large number of tables look at
> using my
> > > > dbscript.ec utility in the utils2_ak package.
> > > >
> > > > Art
> > >
> > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --089e01176c7f08624605106651a4
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a1142dc0cd25e96051066bce4
Another related question. Is the best way to move detached indexes from one
index dbspace to one with a larger pagesize just to drop and recreate the
indexes? We are running into an issues with that dbspace nearing the page
limit as well.
Larry
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: moving database tables from one dbspace to.... [34763]
> Date: Tue, 3 Mar 2015 13:45:35 -0500
>
> Ahh, a left over comment from cloning the dbstruct code to create
> dbscript. I'll fix that, thanks. You can get the actual Usage from
> dbscritp or any of my utilities by just running it with no arguments or
> with -?
>
> I think that listdb & listdb7 will do something reasonable without args, so
> you need - for them. The others all print out usage by default.
>
> 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, Mar 3, 2015 at 1:18 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > The reason that I put dbstruct is that was the Usage statement inside of
> > the
> > dbscript.ec file.
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: moving database tables from one dbspace to.... [34761]
> > > Date: Tue, 3 Mar 2015 13:15:26 -0500
> > >
> > > dbscript, not dbstruct. Dbstruct will print out a header file for the
> > > tables (or for the whole database) in C, ESQL/C, structured FORTRAN, or
> > 4GL
> > > format to make develops' jobs easier.
> > >
> > > For dbscript:
> > >
> > > In one step you can do:
> > >
> > > dbscript -d <database> -c 'alter fragment on table %s init in <new
> > > dbspaces;' | dbaccess -e <database> -
> > >
> > > If you only want to process some tables the include
> > > 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, Mar 3, 2015 at 1:02 PM, LARRY SORENSEN <lsorensen25@msn.com>
> > wrote:
> > >
> > > > Do you have a little blerb on how to use dbscript.ec to create an
> > ALTER
> > > > FRAGMENT....INIT.. on every table in a database? I didn't quite
> > understand
> > > > what is required with
> > > >
> > > > Usage: dbstruct [-F] [-h hostname] -d databasename [-t tablename]
> > > > [filename]
> > > > Larry
> > > >
> > > > > To: ids@iiug.org
> > > > > From: art.kagel@gmail.com
> > > > > Subject: RE: moving database tables from one dbspace to.... [34755]
> > > > > Date: Tue, 3 Mar 2015 10:49:20 -0500
> > > > >
> > > > > No. You cannot use ontape for that. Probably using the ALTER
> > FRAGMENT ON
> > > > > TABLE tablename INIT IN dbspacename; command is your best option. To
> > > > > automate creating a script for a large number of tables look at
> > using my
> > > > > dbscript.ec utility in the utils2_ak package.
> > > > >
> > > > > Art
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --089e01176c7f08624605106651a4
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a1142dc0cd25e96051066bce4
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Another question:
If you do an onunload of a database with detached indexes, and load that into
another database, does it still do detached indexes and are they still in the
original dbspace?
Larry
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: moving database tables from one dbspace to.... [34763]
> Date: Tue, 3 Mar 2015 13:45:35 -0500
>
> Ahh, a left over comment from cloning the dbstruct code to create
> dbscript. I'll fix that, thanks. You can get the actual Usage from
> dbscritp or any of my utilities by just running it with no arguments or
> with -?
>
> I think that listdb & listdb7 will do something reasonable without args, so
> you need - for them. The others all print out usage by default.
>
> 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, Mar 3, 2015 at 1:18 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > The reason that I put dbstruct is that was the Usage statement inside of
> > the
> > dbscript.ec file.
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: moving database tables from one dbspace to.... [34761]
> > > Date: Tue, 3 Mar 2015 13:15:26 -0500
> > >
> > > dbscript, not dbstruct. Dbstruct will print out a header file for the
> > > tables (or for the whole database) in C, ESQL/C, structured FORTRAN, or
> > 4GL
> > > format to make develops' jobs easier.
> > >
> > > For dbscript:
> > >
> > > In one step you can do:
> > >
> > > dbscript -d <database> -c 'alter fragment on table %s init in <new
> > > dbspaces;' | dbaccess -e <database> -
> > >
> > > If you only want to process some tables the include
> > > 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, Mar 3, 2015 at 1:02 PM, LARRY SORENSEN <lsorensen25@msn.com>
> > wrote:
> > >
> > > > Do you have a little blerb on how to use dbscript.ec to create an
> > ALTER
> > > > FRAGMENT....INIT.. on every table in a database? I didn't quite
> > understand
> > > > what is required with
> > > >
> > > > Usage: dbstruct [-F] [-h hostname] -d databasename [-t tablename]
> > > > [filename]
> > > > Larry
> > > >
> > > > > To: ids@iiug.org
> > > > > From: art.kagel@gmail.com
> > > > > Subject: RE: moving database tables from one dbspace to.... [34755]
> > > > > Date: Tue, 3 Mar 2015 10:49:20 -0500
> > > > >
> > > > > No. You cannot use ontape for that. Probably using the ALTER
> > FRAGMENT ON
> > > > > TABLE tablename INIT IN dbspacename; command is your best option. To
> > > > > automate creating a script for a large number of tables look at
> > using my
> > > > > dbscript.ec utility in the utils2_ak package.
> > > > >
> > > > > Art
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --089e01176c7f08624605106651a4
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a1142dc0cd25e96051066bce4
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
I've you're using ALTER FRAGMENT ... INIT to move the tables, dropping the indexes first can help reduce the duration of moving the tables. ROWIDS in the btree will have to be modified (odds are very good) since data will land on new home pages. It sounds like (after reading messages further in this thread) that you're considering moving the indexes anyway. Utilize the standard PDQ settings for the index rebuild to make them as fast as possible. Mark Scranton The Mark Scranton Group www.markscranton.com mark@markscranton.com
So is ALTER FRAGMENT the method of choice for moving all the tables in the database, or is there a faster way? There are around 1000 tables. > To: ids@iiug.org > From: mark@markscranton.com > Subject: Re: RE: moving database tables from one dbspace to [34766] > Date: Tue, 3 Mar 2015 17:26:00 -0500 > > I've you're using ALTER FRAGMENT ... INIT to move the tables, dropping the > indexes first can help reduce the duration of moving the tables. ROWIDS in the > btree will have to be modified (odds are very good) since data will land on > new home pages. It sounds like (after reading messages further in this thread) > that you're considering moving the indexes anyway. Utilize the standard PDQ > settings for the index rebuild to make them as fast as possible. > > Mark Scranton > The Mark Scranton Group > www.markscranton.com > mark@markscranton.com > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
FYI, with a few small tweaks you could probably clone dbscript to create a
new utility to process index names instead of table names. I'd tackle that
just for giggles but I'm swamped right now.
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, Mar 3, 2015 at 5:29 PM, Art Kagel <art.kagel@gmail.com> wrote:
> Indexes always perform best on 16K pages with the sole exception being
> tiny indexes (ie narrow keys on tables with few rows) which are not
> affected by pagesize either way. I would rebuild indexes on active tables
> just to get a clean btree out of the deal and move indexes on mostly static
> tables (like lookup tables) just because it's easier.
>
> 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, Mar 3, 2015 at 3:53 PM, LARRY SORENSEN <lsorensen25@msn.com>
> wrote:
>
>> Another related question. Is the best way to move detached indexes from
>> one
>> index dbspace to one with a larger pagesize just to drop and recreate the
>> indexes? We are running into an issues with that dbspace nearing the page
>> limit as well.
>>
>> Larry
>>
>> > To: ids@iiug.org
>> > From: art.kagel@gmail.com
>> > Subject: Re: moving database tables from one dbspace to.... [34763]
>> > Date: Tue, 3 Mar 2015 13:45:35 -0500
>> >
>> > Ahh, a left over comment from cloning the dbstruct code to create
>> > dbscript. I'll fix that, thanks. You can get the actual Usage from
>> > dbscritp or any of my utilities by just running it with no arguments or
>> > with -?
>> >
>> > I think that listdb & listdb7 will do something reasonable without
>> args, so
>> > you need - for them. The others all print out usage by default.
>> >
>> > 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, Mar 3, 2015 at 1:18 PM, LARRY SORENSEN <lsorensen25@msn.com>
>> wrote:
>> >
>> > > The reason that I put dbstruct is that was the Usage statement inside
>> of
>> > > the
>> > > dbscript.ec file.
>> > >
>> > > > To: ids@iiug.org
>> > > > From: art.kagel@gmail.com
>> > > > Subject: Re: moving database tables from one dbspace to.... [34761]
>> > > > Date: Tue, 3 Mar 2015 13:15:26 -0500
>> > > >
>> > > > dbscript, not dbstruct. Dbstruct will print out a header file for
>> the
>> > > > tables (or for the whole database) in C, ESQL/C, structured
>> FORTRAN, or
>> > > 4GL
>> > > > format to make develops' jobs easier.
>> > > >
>> > > > For dbscript:
>> > > >
>> > > > In one step you can do:
>> > > >
>> > > > dbscript -d <database> -c 'alter fragment on table %s init in <new
>> > > > dbspaces;' | dbaccess -e <database> -
>> > > >
>> > > > If you only want to process some tables the include
>> > > > 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, Mar 3, 2015 at 1:02 PM, LARRY SORENSEN <lsorensen25@msn.com
>> >
>> > > wrote:
>> > > >
>> > > > > Do you have a little blerb on how to use dbscript.ec to create an
>> > > ALTER
>> > > > > FRAGMENT....INIT.. on every table in a database? I didn't quite
>> > > understand
>> > > > > what is required with
>> > > > >
>> > > > > Usage: dbstruct [-F] [-h hostname] -d databasename [-t tablename]
>> > > > > [filename]
>> > > > > Larry
>> > > > >
>> > > > > > To: ids@iiug.org
>> > > > > > From: art.kagel@gmail.com
>> > > > > > Subject: RE: moving database tables from one dbspace to....
>> [34755]
>> > > > > > Date: Tue, 3 Mar 2015 10:49:20 -0500
>> > > > > >
>> > > > > > No. You cannot use ontape for that. Probably using the ALTER
>> > > FRAGMENT ON
>> > > > > > TABLE tablename INIT IN dbspacename; command is your best
>> option. To
>> > > > > > automate creating a script for a large number of tables look at
>> > > using my
>> > > > > > dbscript.ec utility in the utils2_ak package.
>> > > > > >
>> > > > > > Art
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > >
>> > >
>> > >
>> >
>>
>>
*******************************************************************************
>> > > > > Forum Note: Use "Reply" to post a response in the discussion
>> forum.
>> > > > >
>> > > > >
>> > > >
>> > > > --089e01176c7f08624605106651a4
>> > > >
>> > > >
>> > > >
>> > >
>> > >
>> >
>>
>>
*******************************************************************************
>> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
>> > > >
>> > >
>> > >
>> > >
>> > >
>> >
>>
>>
*******************************************************************************
>> > > Forum Note: Use "Reply" to post a response in the discussion forum.
>> > >
>> > >
>> >
>> > --001a1142dc0cd25e96051066bce4
>> >
>> >
>> >
>>
>>
*******************************************************************************
>> > Forum Note: Use "Reply" to post a response in the discussion forum.
>> >
>>
>>
>>
>>
*******************************************************************************
>> Forum Note: U
Don't know, honestly. I avoid using onunload myself. I just don't trust
it and external tables and the high performance loader before them were
just as fast.
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, Mar 3, 2015 at 4:37 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> Another question:
>
> If you do an onunload of a database with detached indexes, and load that
> into
> another database, does it still do detached indexes and are they still in
> the
> original dbspace?
>
> Larry
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: moving database tables from one dbspace to.... [34763]
> > Date: Tue, 3 Mar 2015 13:45:35 -0500
> >
> > Ahh, a left over comment from cloning the dbstruct code to create
> > dbscript. I'll fix that, thanks. You can get the actual Usage from
> > dbscritp or any of my utilities by just running it with no arguments or
> > with -?
> >
> > I think that listdb & listdb7 will do something reasonable without args,
> so
> > you need - for them. The others all print out usage by default.
> >
> > 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, Mar 3, 2015 at 1:18 PM, LARRY SORENSEN <lsorensen25@msn.com>
> wrote:
> >
> > > The reason that I put dbstruct is that was the Usage statement inside
> of
> > > the
> > > dbscript.ec file.
> > >
> > > > To: ids@iiug.org
> > > > From: art.kagel@gmail.com
> > > > Subject: Re: moving database tables from one dbspace to.... [34761]
> > > > Date: Tue, 3 Mar 2015 13:15:26 -0500
> > > >
> > > > dbscript, not dbstruct. Dbstruct will print out a header file for the
> > > > tables (or for the whole database) in C, ESQL/C, structured FORTRAN,
> or
> > > 4GL
> > > > format to make develops' jobs easier.
> > > >
> > > > For dbscript:
> > > >
> > > > In one step you can do:
> > > >
> > > > dbscript -d <database> -c 'alter fragment on table %s init in <new
> > > > dbspaces;' | dbaccess -e <database> -
> > > >
> > > > If you only want to process some tables the include
> > > > 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, Mar 3, 2015 at 1:02 PM, LARRY SORENSEN <lsorensen25@msn.com>
> > > wrote:
> > > >
> > > > > Do you have a little blerb on how to use dbscript.ec to create an
> > > ALTER
> > > > > FRAGMENT....INIT.. on every table in a database? I didn't quite
> > > understand
> > > > > what is required with
> > > > >
> > > > > Usage: dbstruct [-F] [-h hostname] -d databasename [-t tablename]
> > > > > [filename]
> > > > > Larry
> > > > >
> > > > > > To: ids@iiug.org
> > > > > > From: art.kagel@gmail.com
> > > > > > Subject: RE: moving database tables from one dbspace to....
> [34755]
> > > > > > Date: Tue, 3 Mar 2015 10:49:20 -0500
> > > > > >
> > > > > > No. You cannot use ontape for that. Probably using the ALTER
> > > FRAGMENT ON
> > > > > > TABLE tablename INIT IN dbspacename; command is your best
> option. To
> > > > > > automate creating a script for a large number of tables look at
> > > using my
> > > > > > dbscript.ec utility in the utils2_ak package.
> > > > > >
> > > > > > Art
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > >
> > > > --089e01176c7f08624605106651a4
> > > >
> > > >
> > > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > >
> > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001a1142dc0cd25e96051066bce4
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a1141bcce82778f051069fb2e
I agree Mark. 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, Mar 3, 2015 at 5:26 PM, MARK SCRANTON <mark@markscranton.com> wrote: > I've you're using ALTER FRAGMENT ... INIT to move the tables, dropping the > indexes first can help reduce the duration of moving the tables. ROWIDS in > the > btree will have to be modified (odds are very good) since data will land on > new home pages. It sounds like (after reading messages further in this > thread) > that you're considering moving the indexes anyway. Utilize the standard PDQ > settings for the index rebuild to make them as fast as possible. > > Mark Scranton > The Mark Scranton Group > www.markscranton.com > mark@markscranton.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bdc1438897469051069ff71
Sorry about all the questions. Where do I find the standard PDQ settings for index rebuilds? I haven't really done one in bulk for a while. Larry > To: ids@iiug.org > From: mark@markscranton.com > Subject: Re: RE: moving database tables from one dbspace to [34766] > Date: Tue, 3 Mar 2015 17:26:00 -0500 > > I've you're using ALTER FRAGMENT ... INIT to move the tables, dropping the > indexes first can help reduce the duration of moving the tables. ROWIDS in the > btree will have to be modified (odds are very good) since data will land on > new home pages. It sounds like (after reading messages further in this thread) > that you're considering moving the indexes anyway. Utilize the standard PDQ > settings for the index rebuild to make them as fast as possible. > > Mark Scranton > The Mark Scranton Group > www.markscranton.com > mark@markscranton.com > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Assuming you are using Enterprise Edition and have access to PDQPRIORITY, this is the recommended environment for fast index builds: export PDQPRIORITY=100 export PSORT_NPROCS=<#CPU VPS * 2> If you want to build more than one index at a time in parallel, then reduce PDQPRIORITY so that the total of all index build sessions doesn't exceed 100%. 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, Mar 3, 2015 at 5:54 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > Sorry about all the questions. Where do I find the standard PDQ settings > for > index rebuilds? I haven't really done one in bulk for a while. > > Larry > > > To: ids@iiug.org > > From: mark@markscranton.com > > Subject: Re: RE: moving database tables from one dbspace to [34766] > > Date: Tue, 3 Mar 2015 17:26:00 -0500 > > > > I've you're using ALTER FRAGMENT ... INIT to move the tables, dropping > the > > indexes first can help reduce the duration of moving the tables. ROWIDS > in > the > > btree will have to be modified (odds are very good) since data will land > on > > new home pages. It sounds like (after reading messages further in this > thread) > > that you're considering moving the indexes anyway. Utilize the standard > PDQ > > settings for the index rebuild to make them as fast as possible. > > > > Mark Scranton > > The Mark Scranton Group > > www.markscranton.com > > mark@markscranton.com > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bdc07aeb94f4205106a85c3
Thank you again. > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: moving database tables from one dbspace to [34772] > Date: Tue, 3 Mar 2015 18:16:30 -0500 > > Assuming you are using Enterprise Edition and have access to PDQPRIORITY, > this is the recommended environment for fast index builds: > > export PDQPRIORITY=100 > export PSORT_NPROCS=<#CPU VPS * 2> > > If you want to build more than one index at a time in parallel, then reduce > PDQPRIORITY so that the total of all index build sessions doesn't exceed > 100%. > > 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, Mar 3, 2015 at 5:54 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > > > Sorry about all the questions. Where do I find the standard PDQ settings > > for > > index rebuilds? I haven't really done one in bulk for a while. > > > > Larry > > > > > To: ids@iiug.org > > > From: mark@markscranton.com > > > Subject: Re: RE: moving database tables from one dbspace to [34766] > > > Date: Tue, 3 Mar 2015 17:26:00 -0500 > > > > > > I've you're using ALTER FRAGMENT ... INIT to move the tables, dropping > > the > > > indexes first can help reduce the duration of moving the tables. ROWIDS > > in > > the > > > btree will have to be modified (odds are very good) since data will land > > on > > > new home pages. It sounds like (after reading messages further in this > > thread) > > > that you're considering moving the indexes anyway. Utilize the standard > > PDQ > > > settings for the index rebuild to make them as fast as possible. > > > > > > Mark Scranton > > > The Mark Scranton Group > > > www.markscranton.com > > > mark@markscranton.com > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --047d7bdc07aeb94f4205106a85c3 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
To add to Art's recommendations ... DS_TOTAL_MEMORY is a very important
setting for maximizing use of PDQPRIORITY. It's the memory allocated for PDQ
queries out of the virtual portion and guideline is 90% of SHMVIRTSIZE. The
biggest benefactor of this is sort memory. Use SET EXPLAIN to see the plan
showing sort memory allocated (and all the normal stuff). DS_TOTAL_MEMORY can
also be modified on the fly with "onmode -wf DS_TOTAL_MEMORY=<value in KB>" to
modify it and write to the onconfig or "onmode -wm DS_TOTAL_MEMORY=<value in
KB>" to modify in the engine but not the onconfig. IF there is 1 or more PDQ
queries running, any mod to the PDQ environment like this will be staged until
the running PDQ queries are complete. That can be awhile considering a query
is usually PDQ'd because it is a long/expensive one. You see any new PDQ jobs
"gated" in onstat -g mgm until current ones have finished, the onmode change
is put into effect. The "Init" gate will have a counter for gated queries.
Mark Scranton
The Mark Scranton Group
www.markscranton.com
mark@markscranton.com
Thank you for the additional information.
Larry
> To: ids@iiug.org
> From: mark@markscranton.com
> Subject: Re: moving database tables from one dbspace to [34776]
> Date: Wed, 4 Mar 2015 13:23:36 -0500
>
> To add to Art's recommendations ... DS_TOTAL_MEMORY is a very important
> setting for maximizing use of PDQPRIORITY. It's the memory allocated for PDQ
> queries out of the virtual portion and guideline is 90% of SHMVIRTSIZE. The
> biggest benefactor of this is sort memory. Use SET EXPLAIN to see the plan
> showing sort memory allocated (and all the normal stuff). DS_TOTAL_MEMORY can
> also be modified on the fly with "onmode -wf DS_TOTAL_MEMORY=<value in KB>"
to
> modify it and write to the onconfig or "onmode -wm DS_TOTAL_MEMORY=<value in
> KB>" to modify in the engine but not the onconfig. IF there is 1 or more PDQ
> queries running, any mod to the PDQ environment like this will be staged
until
> the running PDQ queries are complete. That can be awhile considering a query
> is usually PDQ'd because it is a long/expensive one. You see any new PDQ jobs
> "gated" in onstat -g mgm until current ones have finished, the onmode change
> is put into effect. The "Init" gate will have a counter for gated queries.
>
> Mark Scranton
> The Mark Scranton Group
> www.markscranton.com
> mark@markscranton.com
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>