SEO

>From: Suppository Admin <suppositoryadmin@the-suppository.com>
>If the new database is not being used by anyone, you could certainly turn
>logging off on the receiver database, run the load, then turn logging back
>on.
>
>Same for the database with the data, if no one is in it, turn the logging
>off, run the unload, then turn the logging back on.
>

Err yes. Thought about that. But since I'm not a DBA, but an architect and
app designer, I wasn't sure how Informix would cope. Thought there was a new
type of table that didn't have logging introduced in 10. But then again, I
was falling asleep during that presentation. ;-)

But it goes back to my point.
Going directly from one table to another is easier.

>
>Ian Michael Gumby wrote:
> >
> >
> >
> >> From: "Keith Simmons" <smiley73@googlemail.com>
> >
> >> >
> >> Need to watch logging, both databases need to be the same so there may
> >> be a requirement for lots of log space on the target to avoid long tx,
> >> also the number of locks used may come into play if the tables have a
> >> large number of records. However that aside I agree this is the
> >> fastest method.
> >>
> >> Keith
> >
> > Locks?
> > Hmmm. I guess you haven't tried to lock the table so that there is only
> > 1 lock per table per database?
> >
> > Ok, so here's you other "problem".
> >
> > If you chose the simplest method, of a single insert statement with an
> > embedded select statement, yes, you will have an issue of a long query
> > in the IDS 10 database.
> > Considering that this a new box being built, there shouldn't be anyone
> > else on the box and you can control your log file issues. (Or use rowid
> > to control the select and insert size.... ;-)
> >
> > If you were going to write your own esql/c program, you would run in to
> > the standard librarian/reader mutex issue. One thread to perform the
> > select against IDS 7.x and n threads to read.
> >
> > Haven't played with HPL, but does it allow for a select statement as its
> > input or a named pipe?
> > While you'll have some parallelization, you'll also have additional
> > overhead to contend with.
> >
> > Now I know that there are other methods that could be "faster" like
> > using a combination of a shared memory queue and multiple writers and
> > readers for each table. (Note: you could create a system where you have
> > a shared memory queue for each table you are downloading. For each
> > shared memory queue, you have multiple readers where each reader opens a
> > TCP/IP socket to the other (IDS 10) machine, and on each of those
> > sockets, you have one reader and multiple writers, each being able to
> > control how many transactions they want per commit. (Which would flush
> > the logs.) This would be a fairly complex solution, however, how much
> > faster would it be? You're going to be limited by network bandwidth,
> > amount of memory, processor speed, and disk speed.)
> >
> > So, to wit,
> > The simplest and probably most efficient solution would be to account
> > for the table sizes with respect to logging, and then use an insert
> > statement with an embedded select.
> >
> > But hey! What do I know?
> > I'm talking in terms of theory. I'd take the lazy approach and do the
> > insert statement.
> > (And if necessary, use the rowid to help break down the table in to more
> > manageable transactions. ;-)
> >
> > -G
> >
> > __________________________________________________ _______________
> > Find sales, coupons, and free shipping, all in one place! MSN Shopping
> > Sales & Deals
> > http://shopping.msn.com/content/shp/...tnrdata=200639
> >
>
>_______________________________________________
>Informix-list mailing list
>Informix-list@iiug.org
>http://www.iiug.org/mailman/listinfo/informix-list

__________________________________________________ _______________
Your Hotmail address already works to sign into Windows Live Messenger! Get
it now
http://clk.atdmt.com/MSN/go/msnnkwme...enger/overview

Hate to tell you this, but MySQL has the feature to turn logging on/off per table. Very nice feature. You flip it into MyISAM to load/unload, then flip it back to InnoDB--or whatever other format it was as a logged table. Very easy, and great for lazy people like me. I hate having to think, although they do pay me for some of that.

Regarding Informix, it's the ondblog command to turn logging on/off. The biggest issue is that I believe you have run a level-0 backup, but hey, that's life. Certainly easier than all kinds of complicated scripts n shyte.

See Also: http://publib.boulder.ibm.com/infoce...c/admin464.htm

and you can also use ontape:

http://publib.boulder.ibm.com/infoce...c/admin464.htm

l8r

Ian Michael Gumby wrote:
>
>
>
>> From: Suppository Admin <suppositoryadmin@the-suppository.com>
>> If the new database is not being used by anyone, you could certainly
>> turn logging off on the receiver database, run the load, then turn
>> logging back on.
>>
>> Same for the database with the data, if no one is in it, turn the
>> logging off, run the unload, then turn the logging back on.
>>
>
> Err yes. Thought about that. But since I'm not a DBA, but an architect
> and app designer, I wasn't sure how Informix would cope. Thought there
> was a new type of table that didn't have logging introduced in 10. But
> then again, I was falling asleep during that presentation. ;-)
>
> But it goes back to my point.
> Going directly from one table to another is easier.
>
>>
>> Ian Michael Gumby wrote:
>> >
>> >
>> >
>> >> From: "Keith Simmons" <smiley73@googlemail.com>
>> >
>> >> >
>> >> Need to watch logging, both databases need to be the same so there may
>> >> be a requirement for lots of log space on the target to avoid long tx,
>> >> also the number of locks used may come into play if the tables have a
>> >> large number of records. However that aside I agree this is the
>> >> fastest method.
>> >>
>> >> Keith
>> >
>> > Locks?
>> > Hmmm. I guess you haven't tried to lock the table so that there is only
>> > 1 lock per table per database?
>> >
>> > Ok, so here's you other "problem".
>> >
>> > If you chose the simplest method, of a single insert statement with an
>> > embedded select statement, yes, you will have an issue of a long query
>> > in the IDS 10 database.
>> > Considering that this a new box being built, there shouldn't be anyone
>> > else on the box and you can control your log file issues. (Or use rowid
>> > to control the select and insert size.... ;-)
>> >
>> > If you were going to write your own esql/c program, you would run in to
>> > the standard librarian/reader mutex issue. One thread to perform the
>> > select against IDS 7.x and n threads to read.
>> >
>> > Haven't played with HPL, but does it allow for a select statement as
>> its
>> > input or a named pipe?
>> > While you'll have some parallelization, you'll also have additional
>> > overhead to contend with.
>> >
>> > Now I know that there are other methods that could be "faster" like
>> > using a combination of a shared memory queue and multiple writers and
>> > readers for each table. (Note: you could create a system where you
>> have
>> > a shared memory queue for each table you are downloading. For each
>> > shared memory queue, you have multiple readers where each reader
>> opens a
>> > TCP/IP socket to the other (IDS 10) machine, and on each of those
>> > sockets, you have one reader and multiple writers, each being able to
>> > control how many transactions they want per commit. (Which would flush
>> > the logs.) This would be a fairly complex solution, however, how much
>> > faster would it be? You're going to be limited by network bandwidth,
>> > amount of memory, processor speed, and disk speed.)
>> >
>> > So, to wit,
>> > The simplest and probably most efficient solution would be to account
>> > for the table sizes with respect to logging, and then use an insert
>> > statement with an embedded select.
>> >
>> > But hey! What do I know?
>> > I'm talking in terms of theory. I'd take the lazy approach and do the
>> > insert statement.
>> > (And if necessary, use the rowid to help break down the table in to
>> more
>> > manageable transactions. ;-)
>> >
>> > -G
>> >
>> > __________________________________________________ _______________
>> > Find sales, coupons, and free shipping, all in one place! MSN Shopping
>> > Sales & Deals
>> >
>> http://shopping.msn.com/content/shp/...tnrdata=200639
>> >
>>
>> _______________________________________________
>> Informix-list mailing list
>> Informix-list@iiug.org
>> http://www.iiug.org/mailman/listinfo/informix-list
>
> __________________________________________________ _______________
> Your Hotmail address already works to sign into Windows Live Messenger!
> Get it now
> http://clk.atdmt.com/MSN/go/msnnkwme...enger/overview
>
>

Ian Michael Gumby wrote:
>
>
>
>> From: Suppository Admin <suppositoryadmin@the-suppository.com>
>> If the new database is not being used by anyone, you could certainly
>> turn logging off on the receiver database, run the load, then turn
>> logging back on.
>>
>> Same for the database with the data, if no one is in it, turn the
>> logging off, run the unload, then turn the logging back on.
>>
>
> Err yes. Thought about that. But since I'm not a DBA, but an architect
> and app designer, I wasn't sure how Informix would cope. Thought there
> was a new type of table that didn't have logging introduced in 10. But
> then again, I was falling asleep during that presentation. ;-)
>
> But it goes back to my point.
> Going directly from one table to another is easier.

You could use my dbcopy utility from the utils2_ak package. It's nearly as
fast as insert into ... select ... from... and often faster when run with
the -F option, the -f<commit level> option prevents long transaction
problems as long as the logical logs are being actively backed up on the
target, and the -a option avoids lock table problems. If you can break up
the data and formulate a -s<SELECT ....> option for each you can run many
copies of dbcopy, especially if dbcopy is running on a separate machine from
the source and target servers, to take advantage of the extra CPU power.
Finally, since it uses separate connections to the source and target server
instances you can put the target database into NOLOG mode to increase the
insert speed even more (can't do that with insert into... select ... from...
without also dropping logging on the source).

I've copied a large table with 40 copies of dbcopy running in parallel with
-p 2 set (PDQPRIORITY=2). WHOOSH! 8^)

Art S. Kagel

>>
>> Ian Michael Gumby wrote:
<SNIP>