onmode -b 7.31 to revert back from 9.40
Larry temporarily migrated a 7.31 instance to 9.40 to dbexport large tables, but 'onmode -b 7.31' repeatedly refused to revert, naming tables needing dummy UPDATEs. Answer: those tables have unfinished in-place ALTERs, which must be completed (physically updating every row) ideally before upgrading; he should check the updates actually succeed (use nohup/log output). His updates then failed with 'no more locks' — fixed by LOCK TABLE ... IN EXCLUSIVE MODE — after which he hit long-transaction limits, with no further resolution recorded. Art Kagel also suggested his myexport/myimport utilities, and Jack Parker HPL unloads, to avoid the version switching entirely.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally convert to
9.40.UC8 (Solaris 5.6) so that I can export a database in preparation for a
future permanent migration. I have several tables that are very large and will
not export correctly otherwise due to file size limits. After my dbexport, I
perform an onmode -b 7.3 to convert it back and it always complains about
several tables that I need to run UPDATE table_name set table_name.field =
table_name.field...... Sometimes, that does not work and it complains about
the same tables again and again and appears not to be able to revert back.
Without anyone else touching the database while it is converted, I go through
the following steps:
IDS 7.31.UC9
onmode -s
onmode -l
onmode -c
ontape -a
onmode -yuk
oninit -s
onmode -yuk
I then bring up the instance with my after environment variables have been
changed to point to my IDS 9.40.UC8 installation (in-place migration).
I then export a database.
I then repeat the above steps as follows:
onmode -s
onmode -l
onmode -c
ontape -a
onmode -yuk
oninit -s
onmode -b 7.31
Can anyone se something that I am doing wrong to prevent some tables from
being able to revert back to IDS 7.31?
thanks
LARRY SORENSEN wrote:
> I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally convert to
> 9.40.UC8 (Solaris 5.6) so that I can export a database in preparation for a
> future permanent migration. I have several tables that are very large and
will
> not export correctly otherwise due to file size limits. After my dbexport, I
> perform an onmode -b 7.3 to convert it back and it always complains about
> several tables that I need to run UPDATE table_name set table_name.field =
> table_name.field...... Sometimes, that does not work and it complains about
> the same tables again and again and appears not to be able to revert back.
>
> Without anyone else touching the database while it is converted, I go through
> the following steps:
>
> IDS 7.31.UC9
>
> onmode -s>
> onmode -l>
> onmode -c>
> ontape -a>
> onmode -yuk>
> oninit -s>
> onmode -yuk>
> I then bring up the instance with my after environment variables have been
> changed to point to my IDS 9.40.UC8 installation (in-place migration).
>
> I then export a database.
>
> I then repeat the above steps as follows:
>
> onmode -s>
> onmode -l>
> onmode -c>
> ontape -a>
> onmode -yuk>
> oninit -s>
> onmode -b 7.31>
> Can anyone se something that I am doing wrong to prevent some tables from
> being able to revert back to IDS 7.31?
You have unfinished in-place alters. That's why you have to run the
updates, to complete the IPAs.
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
The issues that I have are:
1) No one is allowed to touch the box during the entire process.
2) I follow the steps laid out in the conversion documentation.
3) After I run the updates, I still run into the same problem. Why aren't
these in-place alters taken care of with the updates that I run?
thanks again
> To: ids@iiug.org
> From: obnoxio@serendipita.com
> Subject: Re: onmode -b 7.31 to revert back from 9.40 [16026]
> Date: Mon, 15 Jun 2009 11:17:41 -0400
>
> LARRY SORENSEN wrote:
> > I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally convert
to
> > 9.40.UC8 (Solaris 5.6) so that I can export a database in preparation for a
> > future permanent migration. I have several tables that are very large and
> will
> > not export correctly otherwise due to file size limits. After my dbexport,
I
> > perform an onmode -b 7.3 to convert it back and it always complains about
> > several tables that I need to run UPDATE table_name set table_name.field =
> > table_name.field...... Sometimes, that does not work and it complains about
> > the same tables again and again and appears not to be able to revert back.
> >
> > Without anyone else touching the database while it is converted, I go
> through
> > the following steps:
> >
> > IDS 7.31.UC9
> >
> > onmode -s> >
> > onmode -l> >
> > onmode -c> >
> > ontape -a> >
> > onmode -yuk> >
> > oninit -s> >
> > onmode -yuk> >
> > I then bring up the instance with my after environment variables have been
> > changed to point to my IDS 9.40.UC8 installation (in-place migration).
> >
> > I then export a database.
> >
> > I then repeat the above steps as follows:
> >
> > onmode -s> >
> > onmode -l> >
> > onmode -c> >
> > ontape -a> >
> > onmode -yuk> >
> > oninit -s> >
> > onmode -b 7.31> >
> > Can anyone se something that I am doing wrong to prevent some tables from
> > being able to revert back to IDS 7.31?
>
> You have unfinished in-place alters. That's why you have to run the
> updates, to complete the IPAs.
>
> --
> Cheers,
> Obnoxio The Clown
>
> http://obotheclown.blogspot.com
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
LARRY SORENSEN wrote: > The issues that I have are: > > 1) No one is allowed to touch the box during the entire process. > > 2) I follow the steps laid out in the conversion documentation. > > 3) After I run the updates, I still run into the same problem. Why aren't > these in-place alters taken care of with the updates that I run? Are the updates actually completing without errors? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
I am running the updates remotely using the "at" command. I am not seeing any
errors in the online.log. Is there another place to look at for at commands or
do I need to modify my script to print out messages to a file after each
update? I have 6 tables that I have to update and each takes about 30 minutes
to complete, so I have to use "at" in case I disconnect.
> To: ids@iiug.org
> From: obnoxio@serendipita.com
> Subject: Re: onmode -b 7.31 to revert back from 9.40 [16028]
> Date: Mon, 15 Jun 2009 12:25:08 -0400
>
> LARRY SORENSEN wrote:
> > The issues that I have are:
> >
> > 1) No one is allowed to touch the box during the entire process.
> >
> > 2) I follow the steps laid out in the conversion documentation.
> >
> > 3) After I run the updates, I still run into the same problem. Why aren't
> > these in-place alters taken care of with the updates that I run?
>
> Are the updates actually completing without errors?
>
> --
> Cheers,
> Obnoxio The Clown
>
> http://obotheclown.blogspot.com
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
LARRY SORENSEN wrote: > I am running the updates remotely using the "at" command. I am not seeing any > errors in the online.log. Is there another place to look at for at commands or > do I need to modify my script to print out messages to a file after each > update? I have 6 tables that I have to update and each takes about 30 minutes > to complete, so I have to use "at" in case I disconnect. Try nohup and check nohup.out ... ? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Why not use high performance loader (unloader in your case).=C2=A0
Spread t= he output across multiple files to get under the size
restrictions.=C2=A0 A= void all this unpleasantness of switching back
and forth.
j.
On Jun 15, 2009, LARRY SORENSEN <lsorensen25= @msn.com> wrote:
I = have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally
convert to =
9.40.UC8 (Solaris 5.6) so that I can export a database in
preparation= for a
future permanent migration. I have several tables that are ver= y
large and will
not export correctly otherwise due to file size limi= ts. After my
dbexport, I
perform an onmode -b 7.3 to convert it back = and it always
complains about
several tables that I need to run UPDAT= E table_name set
table_name.field =3D
table_name.field...... Sometime= s, that does not work and it
complains about
the same tables again an= d again and appears not to be able to
revert back.
Without anyo= ne else touching the database while it is converted,
I go through
the= following steps:
IDS 7.31.UC9
onmode -s
onmode -l
onmode -c
ontape -a
onmode = -yuk
oninit -s
onmode -yuk
I then bring= up the instance with my after environment variables
have been
change= d to point to my IDS 9.40.UC8 installation (in-place
migration).
I then export a database.
I then repeat the above steps as fo= llows:
onmode -s
onmode -l
onmode -c
ontape -a
onmode -yuk
oninit -s
onmode -b 7.31
Can anyone se something that I am doing wrong= to prevent some
tables from
being able to revert back to IDS 7.31? <= br />
thanks
***************************************=
****************************************
Forum Note: Use "Reply" to p= ost a response in the discussion
forum.
Can you use high performance unloader from IDS 7.31 to load into IDS 11.5?
> To: ids@iiug.org
> From: jack.parker4@verizon.net
> Subject: Re: onmode -b 7.31 to revert back from 9.40 [16032]
> Date: Mon, 15 Jun 2009 13:45:24 -0400
>
> Why not use high performance loader (unloader in your case).=C2=A0
>
> Spread t= he output across multiple files to get under the size
>
> restrictions.=C2=A0 A= void all this unpleasantness of switching back
>
> and forth.
>
> j.
>
> On Jun 15, 2009, LARRY SORENSEN <lsorensen25= @msn.com> wrote:
>
> I = have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally
>
> convert to =
>
> 9.40.UC8 (Solaris 5.6) so that I can export a database in
>
> preparation= for a
>
> future permanent migration. I have several tables that are ver= y
>
> large and will
>
> not export correctly otherwise due to file size limi= ts. After my
>
> dbexport, I
>
> perform an onmode -b 7.3 to convert it back = and it always
>
> complains about
>
> several tables that I need to run UPDAT= E table_name set
>
> table_name.field =3D
>
> table_name.field...... Sometime= s, that does not work and it
>
> complains about
>
> the same tables again an= d again and appears not to be able to
>
> revert back.
>
> Without anyo= ne else touching the database while it is converted,
>
> I go through
>
> the= following steps:
>
> IDS 7.31.UC9
>
> onmode -s>
> onmode -l>
> onmode -c>
> ontape -a>
> onmode = -yuk
>
> oninit -s>
> onmode -yuk>
> I then bring= up the instance with my after environment variables
>
> have been
>
> change= d to point to my IDS 9.40.UC8 installation (in-place
>
> migration).
>
> I then export a database.
>
> I then repeat the above steps as fo= llows:
>
> onmode -s>
> onmode -l>
> onmode -c>
> ontape -a>
> onmode -yuk>
> oninit -s>
> onmode -b 7.31>
> Can anyone se something that I am doing wrong= to prevent some
>
> tables from
>
> being able to revert back to IDS 7.31? <= br />
>
> thanks
>
> ***************************************=
>
> ****************************************
>
> Forum Note: Use "Reply" to p= ost a response in the discussion
>
> forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
I don't see why not.=C2=A0 It would probably not work with unconverted page=
s - since there is a difference between 7 and 11 pages, but if you ask it t=
o produce flat files and then either pump those to files or pipes the v11 H=
PL should be able to pick it up from there.
j.
On Jun 15, 2009, LARRY SORENSEN <lsorensen25@msn.com> wrote:=20
Can you use high performance unloader from IDS 7.31 to load into IDS 11.5?=
=20
> To: ids@iiug.org=20
> From: jack.parker4@verizon.net=20
> Subject: Re: onmode -b 7.31 to revert back from 9.40 [16032]=20
> Date: Mon, 15 Jun 2009 13:45:24 -0400=20
>=20
> Why not use high performance loader (unloader in your case).=3DC2=3DA0=20
>=20
> Spread t=3D he output across multiple files to get under the size=20
>=20
> restrictions.=3DC2=3DA0 A=3D void all this unpleasantness of switching ba=
ck=20
>=20
> and forth.=20
>=20
> j.=20
>=20
> On Jun 15, 2009, LARRY SORENSEN <lsorensen25=3D @msn.com> wrote:=20
>=20
> I =3D have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally=20
>=20
> convert to =3D=20
>=20
> 9.40.UC8 (Solaris 5.6) so that I can export a database in=20
>=20
> preparation=3D for a=20
>=20
> future permanent migration. I have several tables that are ver=3D y=20
>=20
> large and will=20
>=20
> not export correctly otherwise due to file size limi=3D ts. After my=20
>=20
> dbexport, I=20
>=20
> perform an onmode -b 7.3 to convert it back =3D and it always=20
>=20
> complains about=20
>=20
> several tables that I need to run UPDAT=3D E table_name set=20
>=20
> table_name.field =3D3D=20
>=20
> table_name.field...... Sometime=3D s, that does not work and it=20
>=20
> complains about=20
>=20
> the same tables again an=3D d again and appears not to be able to=20
>=20
> revert back.=20
>=20
> Without anyo=3D ne else touching the database while it is converted,=20
>=20
> I go through=20
>=20
> the=3D following steps:=20
>=20
> IDS 7.31.UC9=20
>=20
> onmode -s=20
>=20
> onmode -l=20
>=20
> onmode -c=20
>=20
> ontape -a=20
>=20
> onmode =3D -yuk=20
>=20
> oninit -s=20
>=20
> onmode -yuk=20
>=20> I then bring=3D up the instance with my after environment variables=20
>=20
> have been=20
>=20
> change=3D d to point to my IDS 9.40.UC8 installation (in-place=20
>=20
> migration).=20
>=20
> I then export a database.=20
>=20
> I then repeat the above steps as fo=3D llows:=20
>=20
> onmode -s=20
>=20
> onmode -l=20
>=20
> onmode -c=20
>=20
> ontape -a=20
>=20
> onmode -yuk=20
>=20
> oninit -s=20
>=20
> onmode -b 7.31=20
>=20> Can anyone se something that I am doing wrong=3D to prevent some=20
>=20
> tables from=20
>=20
> being able to revert back to IDS 7.31? <=3D br />=20
>=20
> thanks=20
>=20
> ***************************************=3D=20
>=20
> ****************************************=20
>=20
> Forum Note: Use "Reply" to p=3D ost a response in the discussion=20
>=20
> forum.=20
>=20
>=20
>=20
***************************************************************************=
****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
So, as far as everyone knows, if I update the tables that state needing to be
updated when trying the onmode -b 7.31, the revert back should work properly?
What if it still doesn't? I will try it all again and watch for errors, but I
am suspecting that there will not be any. I have done this all several times,
and it has never worked for me. I have ended up restoring from archives, which
is a real pain for something that should work!!
No changes have been made to the database while converted to 9.4. No users
were able to connect.
LARRY SORENSEN wrote:
> So, as far as everyone knows, if I update the tables that state needing to be
> updated when trying the onmode -b 7.31, the revert back should work properly?
> What if it still doesn't? I will try it all again and watch for errors, but I
> am suspecting that there will not be any. I have done this all several times,
> and it has never worked for me. I have ended up restoring from archives,
which
> is a real pain for something that should work!!
>
> No changes have been made to the database while converted to 9.4. No users
> were able to connect.
Can I ask a really, REALLY stupid question? Why are you upgrading to
9.40 when it's already dead?
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
It is a long story, but....
Our old server is an old Solaris 5.6 box (32-bit). We are running IDS
7.31.UD9. Since the OS is so old, 9.4.UD8 was the last release that I could
find that was certified on this OS. We purchased a new server that runs
Solaris 10 (64-bit) that we would like to migrate to, but we are still in the
testing phase. To get testing data to the new server, I can't just run a
dbexport due to the file size limits on the old Informix 7.31. I have,
therefore, loaded IDs 9.4 on the old server. We copy data from the production
instance to a separate instance. Convert the separate instance to IDS 9.4, and
then we are able to export the data without 2GB limitations. I then need to
revert the separate instance back to 7.31 because it also contains another
database that we use for testing, and the test data is currently in the middle
of a 30-day process test. We don't want to lose the testing data.
So, I need to revert back my test instance.
After that, if someone else can suggest an easier method to use for getting
the data from an old 32-bit IDS 7.31 database to a 64-bit 11.5 database, that
would be great.
> To: ids@iiug.org
> From: obnoxio@serendipita.com
> Subject: Re: onmode -b 7.31 to revert back from 9.40 [16039]
> Date: Mon, 15 Jun 2009 17:44:00 -0400
>
> LARRY SORENSEN wrote:
> > So, as far as everyone knows, if I update the tables that state needing to
> be
> > updated when trying the onmode -b 7.31, the revert back should work
> properly?
> > What if it still doesn't? I will try it all again and watch for errors, but
> I
> > am suspecting that there will not be any. I have done this all several
> times,
> > and it has never worked for me. I have ended up restoring from archives,
> which
> > is a real pain for something that should work!!
> >
> > No changes have been made to the database while converted to 9.4. No users
> > were able to connect.
>
> Can I ask a really, REALLY stupid question? Why are you upgrading to
> 9.40 when it's already dead?
>
> --
> Cheers,
> Obnoxio The Clown
>
> http://obotheclown.blogspot.com
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
You have tables which have been altered in-place. The in-place alters must
be completed (by physically updating every row) before the server can be
reverted successfully. You really should do the updates BEFORE upgrading to
9.40.
FYI, you can use my dbexport replacement utility, myexport, for the export
directly from 7.31 to a set of correctly exported files that you can import
into 9.40 or any later release using myexport's myimport utility or dbimport
(myexport and myimport are fully compatible with dbexport/dbimport).
Myexport can be downloaded from the Oninit web site (www.oninit.com/utils)
or from the IIUG Software Repository (www.iiug.org/software). You will also
need my dbschema replacement utility (dbschema is NOT compatible with
dbimport) from the package utils2_ak, Jonathan Leffler's sqlcmd utility, and
-optionally - Ravi Krishna's myonpload (if you want to have
myexport/myimport use the HPLoader for file IO - though the 7.31 HPLoader is
limited to 2GB files, so you can pass this option up).
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN <lsorensen25@msn.com>wrote:
> I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally convert
> to
> 9.40.UC8 (Solaris 5.6) so that I can export a database in preparation for a
> future permanent migration. I have several tables that are very large and
> will
> not export correctly otherwise due to file size limits. After my dbexport,
> I
> perform an onmode -b 7.3 to convert it back and it always complains about
> several tables that I need to run UPDATE table_name set table_name.field =
> table_name.field...... Sometimes, that does not work and it complains about
> the same tables again and again and appears not to be able to revert back.
>
> Without anyone else touching the database while it is converted, I go
> through
> the following steps:
>
> IDS 7.31.UC9
>
> onmode -s>
> onmode -l>
> onmode -c>
> ontape -a>
> onmode -yuk>
> oninit -s>
> onmode -yuk>
> I then bring up the instance with my after environment variables have been
> changed to point to my IDS 9.40.UC8 installation (in-place migration).
>
> I then export a database.
>
> I then repeat the above steps as follows:
>
> onmode -s>
> onmode -l>
> onmode -c>
> ontape -a>
> onmode -yuk>
> oninit -s>
> onmode -b 7.31>
> Can anyone se something that I am doing wrong to prevent some tables from
> being able to revert back to IDS 7.31?
>
> thanks
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e6d27c906bc8fd046c6adb2e
2 more questions:
1) I went through the steps listed in the documentation for migration. How
else do I know what in-place alters need to be done before migration?
2) I am receiving the following error while trying to do an in-place alter:
update claim_d set claim_d.h_msi = claim_d.h_msi
where 1=1;271: Could not insert new row into the table.
134: ISAM error: no more locksI have changed the LOCK MODE to PAGE from ROW and tried again without success.
I have my LOCKS set at 900000.
Any suggestions? The row that I used above is not indexed, but there are 2
referential constraints on the table. I believe that there are around 2.5
million rows in the table.
Thanks
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: onmode -b 7.31 to revert back from 9.40 [16042]
> Date: Mon, 15 Jun 2009 18:48:34 -0400
>
> You have tables which have been altered in-place. The in-place alters must
> be completed (by physically updating every row) before the server can be
> reverted successfully. You really should do the updates BEFORE upgrading to
> 9.40.
>
> FYI, you can use my dbexport replacement utility, myexport, for the export
> directly from 7.31 to a set of correctly exported files that you can import
> into 9.40 or any later release using myexport's myimport utility or dbimport
> (myexport and myimport are fully compatible with dbexport/dbimport).
> Myexport can be downloaded from the Oninit web site (www.oninit.com/utils)
> or from the IIUG Software Repository (www.iiug.org/software). You will also
> need my dbschema replacement utility (dbschema is NOT compatible with
> dbimport) from the package utils2_ak, Jonathan Leffler's sqlcmd utility, and
> -optionally - Ravi Krishna's myonpload (if you want to have
> myexport/myimport use the HPLoader for file IO - though the 7.31 HPLoader is
> limited to 2GB files, so you can pass this option up).
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. 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 Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN <lsorensen25@msn.com>wrote:
>
> > I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally convert
> > to
> > 9.40.UC8 (Solaris 5.6) so that I can export a database in preparation for a
> > future permanent migration. I have several tables that are very large and
> > will
> > not export correctly otherwise due to file size limits. After my dbexport,
> > I
> > perform an onmode -b 7.3 to convert it back and it always complains about
> > several tables that I need to run UPDATE table_name set table_name.field =
> > table_name.field...... Sometimes, that does not work and it complains about
> > the same tables again and again and appears not to be able to revert back.
> >
> > Without anyone else touching the database while it is converted, I go
> > through
> > the following steps:
> >
> > IDS 7.31.UC9
> >
> > onmode -s> >
> > onmode -l> >
> > onmode -c> >
> > ontape -a> >
> > onmode -yuk> >
> > oninit -s> >
> > onmode -yuk> >
> > I then bring up the instance with my after environment variables have been
> > changed to point to my IDS 9.40.UC8 installation (in-place migration).
> >
> > I then export a database.
> >
> > I then repeat the above steps as follows:
> >
> > onmode -s> >
> > onmode -l> >
> > onmode -c> >
> > ontape -a> >
> > onmode -yuk> >
> > oninit -s> >
> > onmode -b 7.31> >
> > Can anyone se something that I am doing wrong to prevent some tables from
> > being able to revert back to IDS 7.31?
> >
> > thanks
You could do the following ...
begin work;
lock table table_name in exclusive mode;
update table_name ....
commit work;
That will place a table level lock on the table ... You should make sure
you have enough transaction logs ... or you might hit the high water mark
...
Peter
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From:
"LARRY SORENSEN" <lsorensen25@msn.com>
To:
ids@iiug.org
Date:
06/16/2009 10:05 AM
Subject:
RE: onmode -b 7.31 to revert back from 9.40 [16046]
Sent by:
ids-bounces@iiug.org
2 more questions:
1) I went through the steps listed in the documentation for migration. How
else do I know what in-place alters need to be done before migration?
2) I am receiving the following error while trying to do an in-place
alter:
update claim_d set claim_d.h_msi = claim_d.h_msi
where 1=1;271: Could not insert new row into the table.
134: ISAM error: no more locksI have changed the LOCK MODE to PAGE from ROW and tried again without
success.
I have my LOCKS set at 900000.
Any suggestions? The row that I used above is not indexed, but there are 2
referential constraints on the table. I believe that there are around 2.5
million rows in the table.
Thanks
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: onmode -b 7.31 to revert back from 9.40 [16042]
> Date: Mon, 15 Jun 2009 18:48:34 -0400
>
> You have tables which have been altered in-place. The in-place alters
must
> be completed (by physically updating every row) before the server can be
> reverted successfully. You really should do the updates BEFORE upgrading
to
> 9.40.
>
> FYI, you can use my dbexport replacement utility, myexport, for the
export
> directly from 7.31 to a set of correctly exported files that you can
import
> into 9.40 or any later release using myexport's myimport utility or
dbimport> (myexport and myimport are fully compatible with dbexport/dbimport).
> Myexport can be downloaded from the Oninit web site (
www.oninit.com/utils)
> or from the IIUG Software Repository (www.iiug.org/software). You will
also
> need my dbschema replacement utility (dbschema is NOT compatible with
> dbimport) from the package utils2_ak, Jonathan Leffler's sqlcmd utility,
and
> -optionally - Ravi Krishna's myonpload (if you want to have
> myexport/myimport use the HPLoader for file IO - though the 7.31
HPLoader is
> limited to 2GB files, so you can pass this option up).
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
and
> do not reflect on my employer, Oninit, the IIUG, nor any other
organization
> with which I am associated either explicitly or implicitly. 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 Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN
<lsorensen25@msn.com>wrote:
>
> > I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally
convert
> > to
> > 9.40.UC8 (Solaris 5.6) so that I can export a database in preparation
for
a
> > future permanent migration. I have several tables that are very large
and
> > will
> > not export correctly otherwise due to file size limits. After my
dbexport,
> > I
> > perform an onmode -b 7.3 to convert it back and it always complains
about
> > several tables that I need to run UPDATE table_name set
table_name.field =
> > table_name.field...... Sometimes, that does not work and it complains
about
> > the same tables again and again and appears not to be able to revert
back.
> >
> > Without anyone else touching the database while it is converted, I go
> > through
> > the following steps:
> >
> > IDS 7.31.UC9
> >
> > onmode -s> >
> > onmode -l> >
> > onmode -c> >
> > ontape -a> >
> > onmode -yuk> >
> > oninit -s> >
> > onmode -yuk> >
> > I then bring up the instance with my after environment variables have
been
> > changed to point to my IDS 9.40.UC8 installation (in-place migration).
> >
> > I then export a database.
> >
> > I then repeat the above steps as follows:
> >
> > onmode -s> >
> > onmode -l> >
> > onmode -c> >
> > ontape -a> >
> > onmode -yuk> >
> > oninit -s> >
> > onmode -b 7.31> >
> > Can anyone se something that I am doing wrong to prevent some tables
from
> > being able to revert back to IDS 7.31?
> >
> > thanks
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks. Now that I have gotten past that, I am running into a long
transaction. Is the only trick to overcome that, adding more log files?
> To: ids@iiug.org
> From: Peter_Logan@spartanstores.com
> Subject: RE: onmode -b 7.31 to revert back from 9.40 [16047]
> Date: Tue, 16 Jun 2009 10:37:49 -0400
>
> You could do the following ...
>
> begin work;
>
> lock table table_name in exclusive mode;
> update table_name ....
> commit work;
>
> That will place a table level lock on the table ... You should make sure
> you have enough transaction logs ... or you might hit the high water mark
> ....
>
> Peter
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From:
> "LARRY SORENSEN" <lsorensen25@msn.com>
> To:
> ids@iiug.org
> Date:
> 06/16/2009 10:05 AM
> Subject:
> RE: onmode -b 7.31 to revert back from 9.40 [16046]
> Sent by:
> ids-bounces@iiug.org
>
> 2 more questions:
>
> 1) I went through the steps listed in the documentation for migration. How
>
> else do I know what in-place alters need to be done before migration?
>
> 2) I am receiving the following error while trying to do an in-place
> alter:
>
> update claim_d set claim_d.h_msi = claim_d.h_msi
> where 1=1;> 271: Could not insert new row into the table.>
> 134: ISAM error: no more locks> I have changed the LOCK MODE to PAGE from ROW and tried again without
> success.
> I have my LOCKS set at 900000.
>
> Any suggestions? The row that I used above is not indexed, but there are 2
>
> referential constraints on the table. I believe that there are around 2.5
> million rows in the table.
>
> Thanks
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: onmode -b 7.31 to revert back from 9.40 [16042]
> > Date: Mon, 15 Jun 2009 18:48:34 -0400
> >
> > You have tables which have been altered in-place. The in-place alters
> must
> > be completed (by physically updating every row) before the server can be
>
> > reverted successfully. You really should do the updates BEFORE upgrading
> to
> > 9.40.
> >
> > FYI, you can use my dbexport replacement utility, myexport, for the
> export
> > directly from 7.31 to a set of correctly exported files that you can
> import
> > into 9.40 or any later release using myexport's myimport utility or
> dbimport> > (myexport and myimport are fully compatible with dbexport/dbimport).
> > Myexport can be downloaded from the Oninit web site (
> www.oninit.com/utils)
> > or from the IIUG Software Repository (www.iiug.org/software). You will
> also
> > need my dbschema replacement utility (dbschema is NOT compatible with
> > dbimport) from the package utils2_ak, Jonathan Leffler's sqlcmd utility,
> and
> > -optionally - Ravi Krishna's myonpload (if you want to have
> > myexport/myimport use the HPLoader for file IO - though the 7.31
> HPLoader is
> > limited to 2GB files, so you can pass this option up).
> >
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> > with which I am associated either explicitly or implicitly. 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 Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN
> <lsorensen25@msn.com>wrote:
> >
> > > I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally
> convert
> > > to
> > > 9.40.UC8 (Solaris 5.6) so that I can export a database in preparation
> for
> a
> > > future permanent migration. I have several tables that are very large
> and
> > > will
> > > not export correctly otherwise due to file size limits. After my
> dbexport,
> > > I
> > > perform an onmode -b 7.3 to convert it back and it always complains
> about
> > > several tables that I need to run UPDATE table_name set
> table_name.field =
> > > table_name.field...... Sometimes, that does not work and it complains
> about
> > > the same tables again and again and appears not to be able to revert
> back.
> > >
> > > Without anyone else touching the database while it is converted, I go
> > > through
> > > the following steps:
> > >
> > > IDS 7.31.UC9
> > >
> > > onmode -s> > >
> > > onmode -l> > >
> > > onmode -c> > >
> > > ontape -a> > >
> > > onmode -yuk> > >
> > > oninit -s> > >
> > > onmode -yuk> > >
> > > I then bring up the instance with my after environment variables have
> been
> > > changed to point to my IDS 9.40.UC8 installation (in-place migration).
>
> > >
> > > I then export a database.
> > >
> > > I then repeat the above steps as follows:
> > >
> > > onmode -s> > >
> > > onmode -l> > >
> > > onmode -c> > >
> > > ontape -a> > >
> > > onmode -yuk> > >
> > > oninit -s> > >
> > > onmode -b 7.31> > >
> > > Can anyone se something that I am doing wrong to prevent some tables
> from
> > > being able to revert back to IDS 7.31?
> > >
> > > thanks
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
I think you can alter the table to raw in 7.31 ... alter table table_name
type (raw); Of course if it screws up you won't be able to revert ...I
believe in 7.31 you still could have indexes and such on the table ...
It's been a while ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From:
"LARRY SORENSEN" <lsorensen25@msn.com>
To:
ids@iiug.org
Date:
06/16/2009 01:25 PM
Subject:
RE: onmode -b 7.31 to revert back from 9.40 [16053]
Sent by:
ids-bounces@iiug.org
Thanks. Now that I have gotten past that, I am running into a long
transaction. Is the only trick to overcome that, adding more log files?
> To: ids@iiug.org
> From: Peter_Logan@spartanstores.com
> Subject: RE: onmode -b 7.31 to revert back from 9.40 [16047]
> Date: Tue, 16 Jun 2009 10:37:49 -0400
>
> You could do the following ...
>
> begin work;
>
> lock table table_name in exclusive mode;
> update table_name ....
> commit work;
>
> That will place a table level lock on the table ... You should make sure
> you have enough transaction logs ... or you might hit the high water
mark
> ....
>
> Peter
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From:
> "LARRY SORENSEN" <lsorensen25@msn.com>
> To:
> ids@iiug.org
> Date:
> 06/16/2009 10:05 AM
> Subject:
> RE: onmode -b 7.31 to revert back from 9.40 [16046]
> Sent by:
> ids-bounces@iiug.org
>
> 2 more questions:
>
> 1) I went through the steps listed in the documentation for migration.
How
>
> else do I know what in-place alters need to be done before migration?
>
> 2) I am receiving the following error while trying to do an in-place
> alter:
>
> update claim_d set claim_d.h_msi = claim_d.h_msi
> where 1=1;> 271: Could not insert new row into the table.>
> 134: ISAM error: no more locks> I have changed the LOCK MODE to PAGE from ROW and tried again without
> success.
> I have my LOCKS set at 900000.
>
> Any suggestions? The row that I used above is not indexed, but there are
2
>
> referential constraints on the table. I believe that there are around
2.5
> million rows in the table.
>
> Thanks
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: onmode -b 7.31 to revert back from 9.40 [16042]
> > Date: Mon, 15 Jun 2009 18:48:34 -0400
> >
> > You have tables which have been altered in-place. The in-place alters
> must
> > be completed (by physically updating every row) before the server can
be
>
> > reverted successfully. You really should do the updates BEFORE
upgrading
> to
> > 9.40.
> >
> > FYI, you can use my dbexport replacement utility, myexport, for the
> export
> > directly from 7.31 to a set of correctly exported files that you can
> import
> > into 9.40 or any later release using myexport's myimport utility or
> dbimport> > (myexport and myimport are fully compatible with dbexport/dbimport).
> > Myexport can be downloaded from the Oninit web site (
> www.oninit.com/utils)
> > or from the IIUG Software Repository (www.iiug.org/software). You will
> also
> > need my dbschema replacement utility (dbschema is NOT compatible with
> > dbimport) from the package utils2_ak, Jonathan Leffler's sqlcmd
utility,
> and
> > -optionally - Ravi Krishna's myonpload (if you want to have
> > myexport/myimport use the HPLoader for file IO - though the 7.31
> HPLoader is
> > limited to 2GB files, so you can pass this option up).
> >
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
opinions
> and
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> > with which I am associated either explicitly or implicitly. 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 Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN
> <lsorensen25@msn.com>wrote:
> >
> > > I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally
> convert
> > > to
> > > 9.40.UC8 (Solaris 5.6) so that I can export a database in
preparation
> for
> a
> > > future permanent migration. I have several tables that are very
large
> and
> > > will
> > > not export correctly otherwise due to file size limits. After my
> dbexport,
> > > I
> > > perform an onmode -b 7.3 to convert it back and it always complains
> about
> > > several tables that I need to run UPDATE table_name set
> table_name.field =
> > > table_name.field...... Sometimes, that does not work and it
complains
> about
> > > the same tables again and again and appears not to be able to revert
> back.
> > >
> > > Without anyone else touching the database while it is converted, I
go
> > > through
> > > the following steps:
> > >
> > > IDS 7.31.UC9
> > >
> > > onmode -s> > >
> > > onmode -l> > >
> > > onmode -c> > >
> > > ontape -a> > >
> > > onmode -yuk> > >
> > > oninit -s> > >
> > > onmode -yuk> > >
> > > I then bring up the instance with my after environment variables
have
> been
> > > changed to point to my IDS 9.40.UC8 installation (in-place
migration).
>
> > >
> > > I then export a database.
> > >
> > > I then repeat the above steps as follows:
> > >
> > > onmode -s> > >
> > > onmode -l> > >
> > > onmode -c> > >
> > > ontape -a> > >
> > > onmode -yuk> > >
> > > oninit -s> > >
> > > onmode -b 7.31> > >
> > > Can anyone se something that I am doing wrong to prevent some tables
> from
> > > being able to revert back to IDS 7.31?
> > >
> > > thanks
>
>
>
*******************************************************************************
>
> 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.
Alter the database to non logged, run your mass update, then alter it back ...
then back it up.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY
SORENSEN
Sent: Tuesday, June 16, 2009 12:25 PM
To: ids@iiug.org
Subject: RE: onmode -b 7.31 to revert back from 9.40 [16053]
Thanks. Now that I have gotten past that, I am running into a long
transaction. Is the only trick to overcome that, adding more log files?
> To: ids@iiug.org
> From: Peter_Logan@spartanstores.com
> Subject: RE: onmode -b 7.31 to revert back from 9.40 [16047]
> Date: Tue, 16 Jun 2009 10:37:49 -0400
>
> You could do the following ...
>
> begin work;
>
> lock table table_name in exclusive mode;
> update table_name ....
> commit work;
>
> That will place a table level lock on the table ... You should make sure
> you have enough transaction logs ... or you might hit the high water mark
> ....
>
> Peter
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From:
> "LARRY SORENSEN" <lsorensen25@msn.com>
> To:
> ids@iiug.org
> Date:
> 06/16/2009 10:05 AM
> Subject:
> RE: onmode -b 7.31 to revert back from 9.40 [16046]
> Sent by:
> ids-bounces@iiug.org
>
> 2 more questions:
>
> 1) I went through the steps listed in the documentation for migration. How
>
> else do I know what in-place alters need to be done before migration?
>
> 2) I am receiving the following error while trying to do an in-place
> alter:
>
> update claim_d set claim_d.h_msi = claim_d.h_msi
> where 1=1;> 271: Could not insert new row into the table.>
> 134: ISAM error: no more locks> I have changed the LOCK MODE to PAGE from ROW and tried again without
> success.
> I have my LOCKS set at 900000.
>
> Any suggestions? The row that I used above is not indexed, but there are 2
>
> referential constraints on the table. I believe that there are around 2.5
> million rows in the table.
>
> Thanks
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: onmode -b 7.31 to revert back from 9.40 [16042]
> > Date: Mon, 15 Jun 2009 18:48:34 -0400
> >
> > You have tables which have been altered in-place. The in-place alters
> must
> > be completed (by physically updating every row) before the server can be
>
> > reverted successfully. You really should do the updates BEFORE upgrading
> to
> > 9.40.
> >
> > FYI, you can use my dbexport replacement utility, myexport, for the
> export
> > directly from 7.31 to a set of correctly exported files that you can
> import
> > into 9.40 or any later release using myexport's myimport utility or
> dbimport> > (myexport and myimport are fully compatible with dbexport/dbimport).
> > Myexport can be downloaded from the Oninit web site (
> www.oninit.com/utils)
> > or from the IIUG Software Repository (www.iiug.org/software). You will
> also
> > need my dbschema replacement utility (dbschema is NOT compatible with
> > dbimport) from the package utils2_ak, Jonathan Leffler's sqlcmd utility,
> and
> > -optionally - Ravi Krishna's myonpload (if you want to have
> > myexport/myimport use the HPLoader for file IO - though the 7.31
> HPLoader is
> > limited to 2GB files, so you can pass this option up).
> >
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> > with which I am associated either explicitly or implicitly. 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 Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN
> <lsorensen25@msn.com>wrote:
> >
> > > I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally
> convert
> > > to
> > > 9.40.UC8 (Solaris 5.6) so that I can export a database in preparation
> for
> a
> > > future permanent migration. I have several tables that are very large
> and
> > > will
> > > not export correctly otherwise due to file size limits. After my
> dbexport,
> > > I
> > > perform an onmode -b 7.3 to convert it back and it always complains
> about
> > > several tables that I need to run UPDATE table_name set
> table_name.field =
> > > table_name.field...... Sometimes, that does not work and it complains
> about
> > > the same tables again and again and appears not to be able to revert
> back.
> > >
> > > Without anyone else touching the database while it is converted, I go
> > > through
> > > the following steps:
> > >
> > > IDS 7.31.UC9
> > >
> > > onmode -s> > >
> > > onmode -l> > >
> > > onmode -c> > >
> > > ontape -a> > >
> > > onmode -yuk> > >
> > > oninit -s> > >
> > > onmode -yuk> > >
> > > I then bring up the instance with my after environment variables have
> been
> > > changed to point to my IDS 9.40.UC8 installation (in-place migration).
>
> > >
> > > I then export a database.
> > >
> > > I then repeat the above steps as follows:
> > >
> > > onmode -s> > >
> > > onmode -l> > >
> > > onmode -c> > >
> > > ontape -a> > >
> > > onmode -yuk> > >
> > > oninit -s> > >
> > > onmode -b 7.31> > >
> > > Can anyone se something that I am doing wrong to prevent some tables
> from
> > > being able to revert back to IDS 7.31?
> > >
> > > thanks
>
>
>
*******************************************************************************
>
> 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.
I tried to change the status using both ontape and onmonitor. Both times I
selected non logged, it said that it was an invalid option.
> To: ids@iiug.org
> From: JRPlugge@west.com
> Subject: RE: onmode -b 7.31 to revert back from 9.40 [16056]
> Date: Tue, 16 Jun 2009 13:34:58 -0400
>
> Alter the database to non logged, run your mass update, then alter it back
...
> then back it up.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY
> SORENSEN
> Sent: Tuesday, June 16, 2009 12:25 PM
> To: ids@iiug.org
> Subject: RE: onmode -b 7.31 to revert back from 9.40 [16053]
>
> Thanks. Now that I have gotten past that, I am running into a long
> transaction. Is the only trick to overcome that, adding more log files?
>
> > To: ids@iiug.org
> > From: Peter_Logan@spartanstores.com
> > Subject: RE: onmode -b 7.31 to revert back from 9.40 [16047]
> > Date: Tue, 16 Jun 2009 10:37:49 -0400
> >
> > You could do the following ...
> >
> > begin work;
> >
> > lock table table_name in exclusive mode;
> > update table_name ....
> > commit work;
> >
> > That will place a table level lock on the table ... You should make sure
> > you have enough transaction logs ... or you might hit the high water mark
> > ....
> >
> > Peter
> >
> > Peter Logan
> > Senior Database Administrator
> > Phone: 616/878-8309
> >
> > From:
> > "LARRY SORENSEN" <lsorensen25@msn.com>
> > To:
> > ids@iiug.org
> > Date:
> > 06/16/2009 10:05 AM
> > Subject:
> > RE: onmode -b 7.31 to revert back from 9.40 [16046]
> > Sent by:
> > ids-bounces@iiug.org
> >
> > 2 more questions:
> >
> > 1) I went through the steps listed in the documentation for migration. How
> >
> > else do I know what in-place alters need to be done before migration?
> >
> > 2) I am receiving the following error while trying to do an in-place
> > alter:
> >
> > update claim_d set claim_d.h_msi = claim_d.h_msi
> > where 1=1;> > 271: Could not insert new row into the table.> >
> > 134: ISAM error: no more locks> > I have changed the LOCK MODE to PAGE from ROW and tried again without
> > success.
> > I have my LOCKS set at 900000.
> >
> > Any suggestions? The row that I used above is not indexed, but there are 2
> >
> > referential constraints on the table. I believe that there are around 2.5
> > million rows in the table.
> >
> > Thanks
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: onmode -b 7.31 to revert back from 9.40 [16042]
> > > Date: Mon, 15 Jun 2009 18:48:34 -0400
> > >
> > > You have tables which have been altered in-place. The in-place alters
> > must
> > > be completed (by physically updating every row) before the server can be
> >
> > > reverted successfully. You really should do the updates BEFORE upgrading
> > to
> > > 9.40.
> > >
> > > FYI, you can use my dbexport replacement utility, myexport, for the
> > export
> > > directly from 7.31 to a set of correctly exported files that you can
> > import
> > > into 9.40 or any later release using myexport's myimport utility or
> > dbimport> > > (myexport and myimport are fully compatible with dbexport/dbimport).
> > > Myexport can be downloaded from the Oninit web site (
> > www.oninit.com/utils)
> > > or from the IIUG Software Repository (www.iiug.org/software). You will
> > also
> > > need my dbschema replacement utility (dbschema is NOT compatible with
> > > dbimport) from the package utils2_ak, Jonathan Leffler's sqlcmd utility,
> > and
> > > -optionally - Ravi Krishna's myonpload (if you want to have
> > > myexport/myimport use the HPLoader for file IO - though the 7.31
> > HPLoader is
> > > limited to 2GB files, so you can pass this option up).
> > >
> > > Art S. Kagel
> > > Oninit (www.oninit.com)
> > > IIUG Board of Directors (art@iiug.org)
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and
> > > do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization
> > > with which I am associated either explicitly or implicitly. 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 Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN
> > <lsorensen25@msn.com>wrote:
> > >
> > > > I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally
> > convert
> > > > to
> > > > 9.40.UC8 (Solaris 5.6) so that I can export a database in preparation
> > for
> > a
> > > > future permanent migration. I have several tables that are very large
> > and
> > > > will
> > > > not export correctly otherwise due to file size limits. After my
> > dbexport,
> > > > I
> > > > perform an onmode -b 7.3 to convert it back and it always complains
> > about
> > > > several tables that I need to run UPDATE table_name set
> > table_name.field =
> > > > table_name.field...... Sometimes, that does not work and it complains
> > about
> > > > the same tables again and again and appears not to be able to revert
> > back.
> > > >
> > > > Without anyone else touching the database while it is converted, I go
> > > > through
> > > > the following steps:
> > > >
> > > > IDS 7.31.UC9
> > > >
> > > > onmode -s> > > >
> > > > onmode -l> > > >
> > > > onmode -c> > > >
> > > > ontape -a> > > >
> > > > onmode -yuk> > > >
> > > > oninit -s> > > >
> > > > onmode -yuk> > > >
> > > > I then bring up the instance with my after environment variables have
> > been
> > > > changed to point to my IDS 9.40.UC8 installation (in-place migration).
> >
> > > >
> > > > I then export a database.
> > > >
> > > > I then repeat the above steps as follows:
> > > >
> > > > onmode -s> > > >
> > > > onmode -l> > > >
> > > > onmode -c> > > >
> > > > ontape -a> > > >
> > > > onmode -yuk> > > >
> > > > oninit -s> > > >
> > > > onmode -b 7.31> > > >
> > > > Can anyone se something that I am doing wrong to prevent some tables
> > from
> > > > being able to revert back to IDS 7.31?
> > > >
> > > > thanks
> >
> >
> >
>
>
*******************************************************************************
> >
> > 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.
>
1 - 'ontape -s -N=C2=A0<database> -t NUL' may get around that.=C2=A0 =
DB does not like to change logging state without a backup.
2 - S= chema changes are always logged - even for raw tables.=C2=A0
IMLE, you need= 1x the size of the table in logfiles to support a
schema change.
j.
On Jun 16, 2009, LARRY SORENSEN <lso= rensen25@msn.com> wrote:
I tried to change the status using both ontape and onmonitor. Both
t= imes I
selected non logged, it said that it was an invalid option.
> To: [1]ids@iiug.org
> From: [2]JRPlugge@west.com =
> Subject: RE: onmode -b 7.31 to revert back from 9.40 [16056]
> Date: Tue, 16 Jun 2009 13:34:58 -0400
>
> Alter t= he database to non logged, run your mass update, then
alter it back
.= ...
> then back it up.
>
> -----Original Message= -----
> From: [3]ids-bounces@iiug.org [mailto:[4]ids-bounc= es@iiug.org]
On Behalf Of LARRY
> SORENSEN
> Sent: Tu= esday, June 16, 2009 12:25 PM
> To: [5]ids@iiug.org
> Subje= ct: RE: onmode -b 7.31 to revert back from 9.40 [16053]
>
&g= t; Thanks. Now that I have gotten past that, I am running into
a long
> transaction. Is the only trick to overcome that, adding more log
file= s?
>
> > To: [6]ids@iiug.org
> > From: Peter_Logan@spartanstores.com
> > Subject: RE: onm= ode -b 7.31 to revert back from 9.40 [16047]
> > Date: Tue, 16 = Jun 2009 10:37:49 -0400
> >
> > You could do the fo= llowing ...
> >
> > begin work;
> >
> > lock table table_name in exclusive mode;
> > updat= e table_name ....
> > commit work;
> >
> &= gt; That will place a table level lock on the table ... You
should make sur= e
> > you have enough transaction logs ... or you might hit the= high
water mark
> > ....
> >
> > Peter=
> >
> > Peter Logan
> > Senior Databas= e Administrator
> > Phone: 616/878-8309
> >
&= gt; > From:
> > "LARRY SORENSEN" <[7]lsorensen25@msn.com= >
> > To:
> > [8]ids@iiug.org
> > = Date:
> > 06/16/2009 10:05 AM
> > Subject:
&g= t; > RE: onmode -b 7.31 to revert back from 9.40 [16046]
> >= Sent by:
> > [9]ids-bounces@iiug.org
> >
> > 2 more questions:
> >
> > 1) I went th= rough the steps listed in the documentation for
migration. How
> &= gt;
> > else do I know what in-place alters need to be done bef= ore
migration?
> >
> > 2) I am receiving the follow= ing error while trying to do an
in-place
> > alter:
> = >
> > update claim_d set claim_d.h_msi =3D claim_d.h_msi
> > where 1=3D1;
> > 271: Could not insert new row int= o the table.
> >
> > 134: ISAM error: no more locks=
> > I have changed the LOCK MODE to PAGE from ROW and tried ag= ain
without
> > success.
> > I have my LOCKS set at= 900000.
> >
> > Any suggestions? The row that I us= ed above is not indexed,
but there are 2
> >
> > re= ferential constraints on the table. I believe that there
are around 2.5
> > million rows in the table.
> >
> > Tha= nks
> >
> > > To: [10]ids@iiug.org
> > = > From: [11]art.kagel@gmail.com
> > > Subject: Re: = onmode -b 7.31 to revert back from 9.40
[16042]
> > > Date: = Mon, 15 Jun 2009 18:48:34 -0400
> > >
> > > Y= ou have tables which have been altered in-place. The
in-place alters
= > > must
> > > be completed (by physically updating ev= ery row) before the
server can be
> >
> > > reve= rted successfully. You really should do the updates
BEFORE upgrading
= > > to
> > > 9.40.
> > >
> >= ; > FYI, you can use my dbexport replacement utility,
myexport, for the =
> > export
> > > directly from 7.31 to a set of = correctly exported files
that you can
> > import
> >= ; > into 9.40 or any later release using myexport's myimport
utility or =
> > dbimport
> > > (myexport and myimport are fu= lly compatible with
dbexport/dbimport).
> > > Myexport can b= e downloaded from the Oninit web site (
> > [12]www.oninit.com/= utils)
> > > or from the IIUG Software Repository (=
www.iiug.org/software). You will
> > also
> > &= gt; need my dbschema replacement utility (dbschema is NOT
compatible with <= br />> > > dbimport) from the package utils2_ak,
Jonathan Leffler'= s sqlcmd utility,
> > and
> > > -optionally - Ra= vi Krishna's myonpload (if you want to have
> > > myexport/m= yimport use the HPLoader for file IO - though the
7.31
> > HPLo= ader is
> > > limited to 2GB files, so you can pass this opt= ion up).
> > >
> > > Art S. Kagel
> = > > Oninit ([13]www.oninit.com)
> > > IIUG Board of Dire= ctors ([14]art@iiug.org)
> > >
> > > Disclaimer:= Please keep in mind that my own opinions are my
own opinions
> &g= t; and
> > > do not reflect on my employer, Oninit, the IIUG= , nor any
other
> > organization
> > > with whic= h I am associated either explicitly or implicitly.
Neither do
> &g= t; > those opinions reflect those of other individuals
affiliated with a= ny
> > entity
> > > with which I am affiliated n= or those of the entities
themselves.
> > >
> > &= gt; On Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN
> > <[15]lsorensen25@msn.com>wrote:
> > >
> > &g= t; > I have an IDS 7.31.UC9 (Solaris 5.6) instance that I
occasionally <= br />> > convert
> > > > to
> > > &g= t; 9.40.UC8 (Solaris 5.6) so that I can export a database
in preparation > > for
> > a
> > > > future perman= ent migration. I have several tables that
are very large
> > an= d
> > > > will
> > > > not export corre= ctly otherwise due to file size limits.
After my
> > dbexport, =
> > > > I
> > > > perform an onmode -b = 7.3 to convert it back and it always
complains
> > about
= > > > > several tables that I need to run UPDATE table_name set=
> > table_name.field =3D
> > > > table_name.= field...... Sometimes, that does not work and
it complains
> > = about
> > > > the same tables again and again and appears= not to be able
to revert
> > back.
> > > > <= br />> > > > Without anyone else touching the database
while it= is converted, I go
> > > > through
> > > = > the following steps:
@
ontape -s -L 0 -N database_name
Prior to doing that you might want to change the archive tape to /dev/null
... also, make sure there are no active threads in the database being
switched ....
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From:
"LARRY SORENSEN" <lsorensen25@msn.com>
To:
ids@iiug.org
Date:
06/16/2009 02:35 PM
Subject:
RE: onmode -b 7.31 to revert back from 9.40 [16058]
Sent by:
ids-bounces@iiug.org
I tried to change the status using both ontape and onmonitor. Both times I
selected non logged, it said that it was an invalid option.
> To: ids@iiug.org
> From: JRPlugge@west.com
> Subject: RE: onmode -b 7.31 to revert back from 9.40 [16056]
> Date: Tue, 16 Jun 2009 13:34:58 -0400
>
> Alter the database to non logged, run your mass update, then alter it
back
....
> then back it up.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
LARRY
> SORENSEN
> Sent: Tuesday, June 16, 2009 12:25 PM
> To: ids@iiug.org
> Subject: RE: onmode -b 7.31 to revert back from 9.40 [16053]
>
> Thanks. Now that I have gotten past that, I am running into a long
> transaction. Is the only trick to overcome that, adding more log files?
>
> > To: ids@iiug.org
> > From: Peter_Logan@spartanstores.com
> > Subject: RE: onmode -b 7.31 to revert back from 9.40 [16047]
> > Date: Tue, 16 Jun 2009 10:37:49 -0400
> >
> > You could do the following ...
> >
> > begin work;
> >
> > lock table table_name in exclusive mode;
> > update table_name ....
> > commit work;
> >
> > That will place a table level lock on the table ... You should make
sure
> > you have enough transaction logs ... or you might hit the high water
mark
> > ....
> >
> > Peter
> >
> > Peter Logan
> > Senior Database Administrator
> > Phone: 616/878-8309
> >
> > From:
> > "LARRY SORENSEN" <lsorensen25@msn.com>
> > To:
> > ids@iiug.org
> > Date:
> > 06/16/2009 10:05 AM
> > Subject:
> > RE: onmode -b 7.31 to revert back from 9.40 [16046]
> > Sent by:
> > ids-bounces@iiug.org
> >
> > 2 more questions:
> >
> > 1) I went through the steps listed in the documentation for migration.
How
> >
> > else do I know what in-place alters need to be done before migration?
> >
> > 2) I am receiving the following error while trying to do an in-place
> > alter:
> >
> > update claim_d set claim_d.h_msi = claim_d.h_msi
> > where 1=1;> > 271: Could not insert new row into the table.> >
> > 134: ISAM error: no more locks> > I have changed the LOCK MODE to PAGE from ROW and tried again without
> > success.
> > I have my LOCKS set at 900000.
> >
> > Any suggestions? The row that I used above is not indexed, but there
are 2
> >
> > referential constraints on the table. I believe that there are around
2.5
> > million rows in the table.
> >
> > Thanks
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: onmode -b 7.31 to revert back from 9.40 [16042]
> > > Date: Mon, 15 Jun 2009 18:48:34 -0400
> > >
> > > You have tables which have been altered in-place. The in-place
alters
> > must
> > > be completed (by physically updating every row) before the server
can be
> >
> > > reverted successfully. You really should do the updates BEFORE
upgrading
> > to
> > > 9.40.
> > >
> > > FYI, you can use my dbexport replacement utility, myexport, for the
> > export
> > > directly from 7.31 to a set of correctly exported files that you can
> > import
> > > into 9.40 or any later release using myexport's myimport utility or
> > dbimport> > > (myexport and myimport are fully compatible with dbexport/dbimport).
> > > Myexport can be downloaded from the Oninit web site (
> > www.oninit.com/utils)
> > > or from the IIUG Software Repository (www.iiug.org/software). You
will
> > also
> > > need my dbschema replacement utility (dbschema is NOT compatible
with
> > > dbimport) from the package utils2_ak, Jonathan Leffler's sqlcmd
utility,
> > and
> > > -optionally - Ravi Krishna's myonpload (if you want to have
> > > myexport/myimport use the HPLoader for file IO - though the 7.31
> > HPLoader is
> > > limited to 2GB files, so you can pass this option up).
> > >
> > > Art S. Kagel
> > > Oninit (www.oninit.com)
> > > IIUG Board of Directors (art@iiug.org)
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
opinions
> > and
> > > do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization
> > > with which I am associated either explicitly or implicitly. 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 Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN
> > <lsorensen25@msn.com>wrote:
> > >
> > > > I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally
> > convert
> > > > to
> > > > 9.40.UC8 (Solaris 5.6) so that I can export a database in
preparation
> > for
> > a
> > > > future permanent migration. I have several tables that are very
large
> > and
> > > > will
> > > > not export correctly otherwise due to file size limits. After my
> > dbexport,
> > > > I
> > > > perform an onmode -b 7.3 to convert it back and it always
complains
> > about
> > > > several tables that I need to run UPDATE table_name set
> > table_name.field =
> > > > table_name.field...... Sometimes, that does not work and it
complains
> > about
> > > > the same tables again and again and appears not to be able to
revert
> > back.
> > > >
> > > > Without anyone else touching the database while it is converted, I
go
> > > > through
> > > > the following steps:
> > > >
> > > > IDS 7.31.UC9
> > > >
> > > > onmode -s> > > >
> > > > onmode -l> > > >
> > > > onmode -c> > > >
> > > > ontape -a> > > >
> > > > onmode -yuk> > > >
> > > > oninit -s> > > >
> > > > onmode -yuk> > > >
> > > > I then bring up the instance with my after environment variables
have
> > been
> > > > changed to point to my IDS 9.40.UC8 installation (in-place
migration).
> >
> > > >
> > > > I then export a database.
> > > >
> > > > I then repeat the above steps as follows:
> > > >
> > > > onmode -s> > > >
> > > > onmode -l> > > >
> > > > onmode -c> > > >
> > > > ontape -a> > > >
> > > > onmode -yuk> > > >
> > > > oninit -s> > > >
> > > > onmode -b 7.31> > > >
> > > > Can anyone se something that I am doing wrong to prevent some
tables
> > from
> > > > being able to revert back to IDS 7.31?
> > > >
> > > > thanks
> >
> >
> >
>
>
*******************************************************************************
> >
> > Forum Note: Use "Reply" to post a response in the
Doh.=C2=A0 Two corrections/clarifications.
NUL is the Windows version of /dev/null - in other words backup to /dev/nul=
l while doing the logging change.
2 - if your LTXHWM is 60, and you have 10GB of logical logs, you have 6GB o=
f logical logs to work with to perform your schema change. IMLE that means=
you can handle a table up to 6GB in size. YMMV.
j.
On Jun 16, 2009, jack.parker4@verizon.net <jack.parker4@verizon.net> wrote:=
=20
1 - 'ontape -s -N=3DC2=3DA0<database> -t NUL' may get around that.=3DC2=3DA=
0 =3D=20
DB does not like to change logging state without a backup.=20
2 - S=3D chema changes are always logged - even for raw tables.=3DC2=3DA0=
=20
IMLE, you need=3D 1x the size of the table in logfiles to support a=20
schema change.=20
j.=20
On Jun 16, 2009, LARRY SORENSEN <lso=3D rensen25@msn.com> wrote:=20
I tried to change the status using both ontape and onmonitor. Both=20
t=3D imes I=20
selected non logged, it said that it was an invalid option.=20
> To: [1]ids@iiug.org=20
> From: [2]JRPlugge@west.com =3D=20
> Subject: RE: onmode -b 7.31 to revert back from 9.40 [16056]=20
> Date: Tue, 16 Jun 2009 13:34:58 -0400=20
>=20
> Alter t=3D he database to non logged, run your mass update, then=20
alter it back=20
..=3D ...=20
> then back it up.=20
>=20
> -----Original Message=3D -----=20
> From: [3]ids-bounces@iiug.org [mailto:[4]ids-bounc=3D es@iiug.org]=20
On Behalf Of LARRY=20
> SORENSEN=20
> Sent: Tu=3D esday, June 16, 2009 12:25 PM=20
> To: [5]ids@iiug.org=20
> Subje=3D ct: RE: onmode -b 7.31 to revert back from 9.40 [16053]=20
>=20
&g=3D t; Thanks. Now that I have gotten past that, I am running into=20
a long=20
> transaction. Is the only trick to overcome that, adding more log=20
file=3D s?=20
>=20
> > To: [6]ids@iiug.org=20
> > From: Peter_Logan@spartanstores.com=20
> > Subject: RE: onm=3D ode -b 7.31 to revert back from 9.40 [16047]=20
> > Date: Tue, 16 =3D Jun 2009 10:37:49 -0400=20
> >=20
> > You could do the fo=3D llowing ...=20
> >=20
> > begin work;=20
> >=20
> > lock table table_name in exclusive mode;=20
> > updat=3D e table_name ....=20
> > commit work;=20
> >=20
> &=3D gt; That will place a table level lock on the table ... You=20
should make sur=3D e=20
> > you have enough transaction logs ... or you might hit the=3D high=20
water mark=20
> > ....=20
> >=20
> > Peter=3D=20
> >=20
> > Peter Logan=20
> > Senior Databas=3D e Administrator=20
> > Phone: 616/878-8309=20
> >=20
&=3D gt; > From:=20
> > "LARRY SORENSEN" <[7]lsorensen25@msn.com=3D >=20
> > To:=20
> > [8]ids@iiug.org=20
> > =3D Date:=20
> > 06/16/2009 10:05 AM=20
> > Subject:=20
&g=3D t; > RE: onmode -b 7.31 to revert back from 9.40 [16046]=20
> >=3D Sent by:=20
> > [9]ids-bounces@iiug.org=20
> >=20
> > 2 more questions:=20
> >=20
> > 1) I went th=3D rough the steps listed in the documentation for=20
migration. How=20
> &=3D gt;=20
> > else do I know what in-place alters need to be done bef=3D ore=20
migration?=20
> >=20
> > 2) I am receiving the follow=3D ing error while trying to do an=20
in-place=20
> > alter:=20
> =3D >=20
> > update claim_d set claim_d.h_msi =3D3D claim_d.h_msi=20
> > where 1=3D3D1;=20
> > 271: Could not insert new row int=3D o the table.=20
> >=20
> > 134: ISAM error: no more locks=3D=20
> > I have changed the LOCK MODE to PAGE from ROW and tried ag=3D ain=20
without=20
> > success.=20
> > I have my LOCKS set at=3D 900000.=20
> >=20
> > Any suggestions? The row that I us=3D ed above is not indexed,=20
but there are 2=20
> >=20
> > re=3D ferential constraints on the table. I believe that there=20
are around 2.5=20
> > million rows in the table.=20
> >=20
> > Tha=3D nks=20
> >=20
> > > To: [10]ids@iiug.org=20
> > =3D > From: [11]art.kagel@gmail.com=20
> > > Subject: Re: =3D onmode -b 7.31 to revert back from 9.40=20
[16042]=20
> > > Date: =3D Mon, 15 Jun 2009 18:48:34 -0400=20
> > >=20
> > > Y=3D ou have tables which have been altered in-place. The=20
in-place alters=20
=3D > > must=20
> > > be completed (by physically updating ev=3D ery row) before the=20
server can be=20
> >=20
> > > reve=3D rted successfully. You really should do the updates=20
BEFORE upgrading=20
=3D > > to=20
> > > 9.40.=20
> > >=20
> >=3D ; > FYI, you can use my dbexport replacement utility,=20
myexport, for the =3D=20
> > export=20
> > > directly from 7.31 to a set of =3D correctly exported files=20
that you can=20
> > import=20
> >=3D ; > into 9.40 or any later release using myexport's myimport=20
utility or =3D=20
> > dbimport=20
> > > (myexport and myimport are fu=3D lly compatible with=20
dbexport/dbimport).=20
> > > Myexport can b=3D e downloaded from the Oninit web site (=20
> > [12]www.oninit.com/=3D utils)=20
> > > or from the IIUG Software Repository (=3D=20
www.iiug.org/software). You will=20
> > also=20
> > &=3D gt; need my dbschema replacement utility (dbschema is NOT=20
compatible with <=3D br />> > > dbimport) from the package utils2_ak,=20
Jonathan Leffler'=3D s sqlcmd utility,=20
> > and=20
> > > -optionally - Ra=3D vi Krishna's myonpload (if you want to have=20
> > > myexport/m=3D yimport use the HPLoader for file IO - though the=20
7.31=20
> > HPLo=3D ader is=20
> > > limited to 2GB files, so you can pass this opt=3D ion up).=20
> > >=20
> > > Art S. Kagel=20
> =3D > > Oninit ([13]www.oninit.com)=20
> > > IIUG Board of Dire=3D ctors ([14]art@iiug.org)=20
> > >=20
> > > Disclaimer:=3D Please keep in mind that my own opinions are my=20
own opinions=20
> &g=3D t; and=20
> > > do not reflect on my employer, Oninit, the IIUG=3D , nor any=20
other=20
> > organization=20
> > > with whic=3D h I am associated either explicitly or implicitly.=20
Neither do=20
> &g=3D t; > those opinions reflect those of other individuals=20
affiliated with a=3D ny=20
> > entity=20
> > > with which I am affiliated n=3D or those of the entities=20
themselves.=20
> > >=20
> > &=3D gt; On Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN=20
> > <[15]lsorensen25@msn.com>wrote:=20
> > >=20
> > &g=3D t; > I have an IDS 7.31.UC9 (Sol
Either that or break up the update into multiple updates. You DO have at
least two indexes, one for each of the referential constraints on the
table. So, you could use those keys to limit the updates to some of the
rows. You can check on whether a table still has any altered rows that are
not currently at the latest schema version by running oncheck -pT
<database>:<table>. That report will tell you what row versions are in the
table and how many rows are at each version level in the section headed
"Home Data Page Version Summary". If the table has no alters outstanding
then all rows will be at the single version level marked "(current)".
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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, Jun 16, 2009 at 1:24 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> Thanks. Now that I have gotten past that, I am running into a long
> transaction. Is the only trick to overcome that, adding more log files?
>
> > To: ids@iiug.org
> > From: Peter_Logan@spartanstores.com
> > Subject: RE: onmode -b 7.31 to revert back from 9.40 [16047]
> > Date: Tue, 16 Jun 2009 10:37:49 -0400
> >
> > You could do the following ...
> >
> > begin work;
> >
> > lock table table_name in exclusive mode;
> > update table_name ....
> > commit work;
> >
> > That will place a table level lock on the table ... You should make sure
> > you have enough transaction logs ... or you might hit the high water mark
> > ....
> >
> > Peter
> >
> > Peter Logan
> > Senior Database Administrator
> > Phone: 616/878-8309
> >
> > From:
> > "LARRY SORENSEN" <lsorensen25@msn.com>
> > To:
> > ids@iiug.org
> > Date:
> > 06/16/2009 10:05 AM
> > Subject:
> > RE: onmode -b 7.31 to revert back from 9.40 [16046]
> > Sent by:
> > ids-bounces@iiug.org
> >
> > 2 more questions:
> >
> > 1) I went through the steps listed in the documentation for migration.
> How
> >
> > else do I know what in-place alters need to be done before migration?
> >
> > 2) I am receiving the following error while trying to do an in-place
> > alter:
> >
> > update claim_d set claim_d.h_msi = claim_d.h_msi
> > where 1=1;> > 271: Could not insert new row into the table.> >
> > 134: ISAM error: no more locks> > I have changed the LOCK MODE to PAGE from ROW and tried again without
> > success.
> > I have my LOCKS set at 900000.
> >
> > Any suggestions? The row that I used above is not indexed, but there are
> 2
> >
> > referential constraints on the table. I believe that there are around 2.5
> > million rows in the table.
> >
> > Thanks
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: onmode -b 7.31 to revert back from 9.40 [16042]
> > > Date: Mon, 15 Jun 2009 18:48:34 -0400
> > >
> > > You have tables which have been altered in-place. The in-place alters
> > must
> > > be completed (by physically updating every row) before the server can
> be
> >
> > > reverted successfully. You really should do the updates BEFORE
> upgrading
> > to
> > > 9.40.
> > >
> > > FYI, you can use my dbexport replacement utility, myexport, for the
> > export
> > > directly from 7.31 to a set of correctly exported files that you can
> > import
> > > into 9.40 or any later release using myexport's myimport utility or
> > dbimport> > > (myexport and myimport are fully compatible with dbexport/dbimport).
> > > Myexport can be downloaded from the Oninit web site (
> > www.oninit.com/utils)
> > > or from the IIUG Software Repository (www.iiug.org/software). You will
> > also
> > > need my dbschema replacement utility (dbschema is NOT compatible with
> > > dbimport) from the package utils2_ak, Jonathan Leffler's sqlcmd
> utility,
> > and
> > > -optionally - Ravi Krishna's myonpload (if you want to have
> > > myexport/myimport use the HPLoader for file IO - though the 7.31
> > HPLoader is
> > > limited to 2GB files, so you can pass this option up).
> > >
> > > Art S. Kagel
> > > Oninit (www.oninit.com)
> > > IIUG Board of Directors (art@iiug.org)
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > and
> > > do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization
> > > with which I am associated either explicitly or implicitly. 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 Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN
> > <lsorensen25@msn.com>wrote:
> > >
> > > > I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally
> > convert
> > > > to
> > > > 9.40.UC8 (Solaris 5.6) so that I can export a database in preparation
> > for
> > a
> > > > future permanent migration. I have several tables that are very large
> > and
> > > > will
> > > > not export correctly otherwise due to file size limits. After my
> > dbexport,
> > > > I
> > > > perform an onmode -b 7.3 to convert it back and it always complains
> > about
> > > > several tables that I need to run UPDATE table_name set
> > table_name.field =
> > > > table_name.field...... Sometimes, that does not work and it complains
> > about
> > > > the same tables again and again and appears not to be able to revert
> > back.
> > > >
> > > > Without anyone else touching the database while it is converted, I go
> > > > through
> > > > the following steps:
> > > >
> > > > IDS 7.31.UC9
> > > >
> > > > onmode -s> > > >
> > > > onmode -l> > > >
> > > > onmode -c> > > >
> > > > ontape -a> > > >
> > > > onmode -yuk> > > >
> > > > oninit -s> > > >
> > > > onmode -yuk> > > >
> > > > I then bring up the instance with my after environment variables have
> > been
> > > > changed to point to my IDS 9.40.UC8 installation (in-place
> migration).
> >
> > > >
> > > > I then export a database.
> > > >
> > > > I then repeat the above steps as follows:
> > > >
> > > > onmode -s> > > >
> > > > onmode -l> > > >
> > > > onmode -c> > > >
> > > > ontape -a> > > >
> > > > onmode -yuk> > > >
> > > > oninit -s> > > >
> > > > onmode -b 7.31> > > >
> > > > Can anyone se something that I am doing wrong to prevent some tables
> > from
> > > > being able to revert back to IDS 7.31?
> > > >
> > > > thanks
> >
> >
> >
>
>
*******************************************************************************
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
>
>@@NL@
Thanks to all for the help. I first doubled the number of logical logs without
success. I then was able to change the two (2) databases to non-logging
temporarily (as suggested) while I did the updates and the conversion. Once I
brought the instance back up in IDS 7.31, I changed the two (2) databases back
to buffered logging.
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: onmode -b 7.31 to revert back from 9.40 [16068]
> Date: Wed, 17 Jun 2009 09:39:25 -0400
>
> Either that or break up the update into multiple updates. You DO have at
> least two indexes, one for each of the referential constraints on the
> table. So, you could use those keys to limit the updates to some of the
> rows. You can check on whether a table still has any altered rows that are
> not currently at the latest schema version by running oncheck -pT
> <database>:<table>. That report will tell you what row versions are in the
> table and how many rows are at each version level in the section headed
> "Home Data Page Version Summary". If the table has no alters outstanding
> then all rows will be at the single version level marked "(current)".
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. 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, Jun 16, 2009 at 1:24 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > Thanks. Now that I have gotten past that, I am running into a long
> > transaction. Is the only trick to overcome that, adding more log files?
> >
> > > To: ids@iiug.org
> > > From: Peter_Logan@spartanstores.com
> > > Subject: RE: onmode -b 7.31 to revert back from 9.40 [16047]
> > > Date: Tue, 16 Jun 2009 10:37:49 -0400
> > >
> > > You could do the following ...
> > >
> > > begin work;
> > >
> > > lock table table_name in exclusive mode;
> > > update table_name ....
> > > commit work;
> > >
> > > That will place a table level lock on the table ... You should make sure
> > > you have enough transaction logs ... or you might hit the high water mark
> > > ....
> > >
> > > Peter
> > >
> > > Peter Logan
> > > Senior Database Administrator
> > > Phone: 616/878-8309
> > >
> > > From:
> > > "LARRY SORENSEN" <lsorensen25@msn.com>
> > > To:
> > > ids@iiug.org
> > > Date:
> > > 06/16/2009 10:05 AM
> > > Subject:
> > > RE: onmode -b 7.31 to revert back from 9.40 [16046]
> > > Sent by:
> > > ids-bounces@iiug.org
> > >
> > > 2 more questions:
> > >
> > > 1) I went through the steps listed in the documentation for migration.
> > How
> > >
> > > else do I know what in-place alters need to be done before migration?
> > >
> > > 2) I am receiving the following error while trying to do an in-place
> > > alter:
> > >
> > > update claim_d set claim_d.h_msi = claim_d.h_msi
> > > where 1=1;> > > 271: Could not insert new row into the table.> > >
> > > 134: ISAM error: no more locks> > > I have changed the LOCK MODE to PAGE from ROW and tried again without
> > > success.
> > > I have my LOCKS set at 900000.
> > >
> > > Any suggestions? The row that I used above is not indexed, but there are
> > 2
> > >
> > > referential constraints on the table. I believe that there are around 2.5
> > > million rows in the table.
> > >
> > > Thanks
> > >
> > > > To: ids@iiug.org
> > > > From: art.kagel@gmail.com
> > > > Subject: Re: onmode -b 7.31 to revert back from 9.40 [16042]
> > > > Date: Mon, 15 Jun 2009 18:48:34 -0400
> > > >
> > > > You have tables which have been altered in-place. The in-place alters
> > > must
> > > > be completed (by physically updating every row) before the server can
> > be
> > >
> > > > reverted successfully. You really should do the updates BEFORE
> > upgrading
> > > to
> > > > 9.40.
> > > >
> > > > FYI, you can use my dbexport replacement utility, myexport, for the
> > > export
> > > > directly from 7.31 to a set of correctly exported files that you can
> > > import
> > > > into 9.40 or any later release using myexport's myimport utility or
> > > dbimport> > > > (myexport and myimport are fully compatible with dbexport/dbimport).
> > > > Myexport can be downloaded from the Oninit web site (
> > > www.oninit.com/utils)
> > > > or from the IIUG Software Repository (www.iiug.org/software). You will
> > > also
> > > > need my dbschema replacement utility (dbschema is NOT compatible with
> > > > dbimport) from the package utils2_ak, Jonathan Leffler's sqlcmd
> > utility,
> > > and
> > > > -optionally - Ravi Krishna's myonpload (if you want to have
> > > > myexport/myimport use the HPLoader for file IO - though the 7.31
> > > HPLoader is
> > > > limited to 2GB files, so you can pass this option up).
> > > >
> > > > Art S. Kagel
> > > > Oninit (www.oninit.com)
> > > > IIUG Board of Directors (art@iiug.org)
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions
> > > and
> > > > do not reflect on my employer, Oninit, the IIUG, nor any other
> > > organization
> > > > with which I am associated either explicitly or implicitly. 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 Mon, Jun 15, 2009 at 11:13 AM, LARRY SORENSEN
> > > <lsorensen25@msn.com>wrote:
> > > >
> > > > > I have an IDS 7.31.UC9 (Solaris 5.6) instance that I occasionally
> > > convert
> > > > > to
> > > > > 9.40.UC8 (Solaris 5.6) so that I can export a database in preparation
> > > for
> > > a
> > > > > future permanent migration. I have several tables that are very large
> > > and
> > > > > will
> > > > > not export correctly otherwise due to file size limits. After my
> > > dbexport,
> > > > > I
> > > > > perform an onmode -b 7.3 to convert it back and it always complains
> > > about
> > > > > several tables that I need to run UPDATE table_name set
> > > table_name.field =
> > > > > table_name.field...... Sometimes, that does not work and it complains
> > > about
> > > > > the same tables again and again and appears not to be able to revert
> > > back.
> > > > >
> > > > > Without anyone else touching the database while it is converted, I go
> > > > > through
> > > > > the following steps:
> > > > >
> > > > > IDS 7.31.UC9
> > > > >
> > > > > onmode -s> > > > >
> > > > > onmode -l> > > > >
> > > > > onmode -c> > > > >
> > > > > ontape -a> > > > >
> > > > > onmode -yuk> > > > >
> > > > > oninit -s> > > > >
> > > > > onmode -yuk> > > > >
> > > > > I then bring up the instance with my after environment variables have
> > > been
> > > > > changed to point to my IDS