11.5FC8W2 on Linux

I'm doing a 9.40 upgrade to 11.5 on a smallish database, around 100g. I
want to unload and reload the data too, as the dbspace layout is dreadful.

timings so far:
dbexport 2h 20m
dbimport 10h

So I thought I'd try onunload.

onunload -t filename -s 204800000 -b 512 databasename took 0h 42m

The onload is still going, 15 hours later!! By the looks of it (the
onunload file size was 62g in size) it still has about 6g to go ...

onstat -D ....
67bf1028 9 9 0 24275898 24274725
/opt/informix/dbspaces3/appdbs_1
67bf1218 10 9 0 4784051 4700042
/opt/informix/dbspaces3/appdbs_2

Is onunload supposed to be this slow? Was my blocksize choice (512) wrong?
Why is it doing so many page *reads" (just given myself an idea here;
perhaps it's a free page list thing and if I reinitialise the db server it
wouldn't do the reads ...?).

!5 hours and counting for a 62g database is crazy. This is all on fairly
slow internal disk, but even a dbimport was much faster than this.

Any ideas? (myexport is on my list for sure, but I'd like to know what's
wrong with onload).

Thanks
Neil

Hi Neil,

AFAIK you cannot use onunload/onload for differ IFX versions...
Probably the natural differ of struct of the data pages generate this problem of slowness...

My suggestion: Go to HPL ... much faster of dbimport/dbexport* . But the control of the creation of objects must be manually...

check the command:
onpladm create project <prj> -D <database> ....

The easy / dummy way to do this is:
On the source server:
- Generate a dbschema full
- Export with HPL (using the project or table-by-table)
On the target server:
- Create this dbschema on the target (all objects)
- Disable all FK constraints and triggers
- Load all data with HPL
- Enable all FKs and triggers.
- Update statistics...

And exists several ways to tunning this process:
- using gzip to unload all data (avoiding write I/O or network I/O)
- Writing all data from table to several files in parallel
- parameter of the HPL (plconfig.std)
- PDQ configuration (for indexes creation)

And for the load, alternatively* you can use "external tables' ... if youdon't want use the HPL


Copied from v11.70 Information Center:
http://publib.boulder.ibm.com/infoce...ds_mig_192.htm


You can use only onunload and onload if your answer
to each of the following questions is yes. If your answer is no,
you cannot use onunload and onload.
Use onunload and onload Only If Your Answer To
Each Question Is Yes


Is the target database server on the same hardware
platform?

Do you want to move to another database server
of the same version?

Do you want to keep the existing database schema
without modifying it?

Do you want to move an entire database or an entire
table?

Are the page images compatible?

Are the numeric representations the same?

--- Em ter, 15/3/11, Neil Truby <neil.truby (AT) ardenta (DOT) com> escreveu:

De: Neil Truby <neil.truby (AT) ardenta (DOT) com>
Assunto: Onload, the "fast" alternative
Para: informix-list (AT) iiug (DOT) org
Data: Terça-feira, 15 de Março de 2011, 11:23

11.5FC8W2 on Linux

I'm doing a 9.40 upgrade to 11.5 on a smallish database, around 100g.* I
want to unload and reload the data too, as the dbspace layout is dreadful.

timings so far:
dbexport 2h 20m
dbimport 10h

So I thought I'd try onunload.

onunload -t filename -s 204800000 -b 512 databasename took 0h 42m

The onload is still going, 15 hours later!!* By the looks of it (the
onunload file size was 62g in size) it still has about 6g to go ...

onstat -D ....
67bf1028* * * ***9* * * 9* * * 0* * * * *24275898 24274725
/opt/informix/dbspaces3/appdbs_1
67bf1218* * * ***10* ***9* * * 0* * * * * 4784051* 4700042
/opt/informix/dbspaces3/appdbs_2

Is onunload supposed to be this slow?* Was my blocksize choice (512) wrong?
Why is it doing so many page *reads" (just given myself an idea here;
perhaps it's a free page list thing and if I reinitialise the db server it
wouldn't do the reads ...?).

!5 hours and counting for a 62g database is crazy.* This is all on fairly
slow internal disk, but even a dbimport was much faster than this.

Any ideas?* (myexport is on my list for sure, but I'd like to know what's
wrong with onload).

Thanks
Neil

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

Myexport/myimport using EXTERNAL TABLES in express mode in parallel will
FLY! It should be faster then HPLoader even.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Tue, Mar 15, 2011 at 10:23 AM, Neil Truby <neil.truby (AT) ardenta (DOT) com> wrote:

On Mar 15, 8:37*am, Cesar Inacio Martins
<cesar_inacio_mart... (AT) yahoo (DOT) com.br> wrote:
onstat -g ath
onstat -g stk for the onload threads

??

Hello Neil,

If my memory is correct, to minimize I/O and translation (informix
internal to ASCII), I like to SELECT from source server table and
INSERT into target server table all in 1 SQL. Plus I try to get the
best network connection between the 2 servers (even if I have to run a
new cable directly between them). On the source server setup
LIGHT_SCANS, etc. On the target server initially make the table RAW
(then STANDARD when done), etc. I would insert tables in parallel (if
the lesser computer has 4 cpu's I would do 3 tables in parallel).

I did this years ago with more data than you in less time.

Good luck,
LS

"Art Kagel" <art.kagel (AT) gmail (DOT) com> wrote

Thanks. It looks good. However, I got 14 of this messag:
SQL -23103: Code-set conversion function failed due to illegal sequence or
invalid value

... which I assume means that the unloads of the relvant tables has failed.

Back to the original question, does anyone know why onload is so slow? It
eventually loaded the 100g-odd in SEVENTEEN hours!

Thanks

My dbcopy utility does this faster when copying between two servers (even a
bit faster, oddly, if you run it on a 3rd machine).

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Thu, Mar 17, 2011 at 3:29 PM, LIGHT SCANS <light_scans (AT) yahoo (DOT) com> wrote:

Hello Neil,

Did you check if stuff is physical logged ???

onstat -l during the load and maybe onstat -g iof etc.

Superboer.

On 17 mrt, 21:23, "Neil Truby" <neil.tr... (AT) ardenta (DOT) com> wrote:

OOOppss i missed

AFAIK you cannot use onunload/onload for differ IFX versions...

from Cesar and he is right!!!

you can not mix.

Superboer.

On 18 mrt, 14:06, Superboer <superbo... (AT) t-online (DOT) de> wrote:

Yeah, I'm not trying to. This is 11.5FC8W2GE -> 11.5FC8W2GE

Thanks

"Superboer" <superboer7 (AT) t-online (DOT) de> wrote

OOOppss i missed

AFAIK you cannot use onunload/onload for differ IFX versions...

from Cesar and he is right!!!

you can not mix.

Superboer.

On 18 mrt, 14:06, Superboer <superbo... (AT) t-online (DOT) de> wrote: