DBDELIMITER in dbexport/dbimport process
A user moving a 30GB database to a new dbspace layout hit trouble with dbexport/dbimport: the data contained the default '|' delimiter and, since it includes every ASCII character (ascii art), no single-character DBDELIMITER was safe; onunload also failed on encoding warnings. Suggestions included external tables in INFORMIX/FIXED rather than delimited format, and direct INSERT INTO ... SELECT copies. Art Kagel noted that if only dbspaces change, ALTER FRAGMENT ON <table> INIT IN <dbspace> moves data with no unload (except the system catalog, for which a new database plus his dbcopy/myschema utils4_ak scripts work). No confirmation from the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello,
First, i want to thanks all of you for the big job made here, i cannot count
the number of time that this board save me.
Now it's time for me to submit the problem i have.
I'm trying to make a dbexport/dbimport process on a big database (30go+) in
order to change the dbs structure.
After the first dbexport fail due to an incorrect encoding caracters, then
IFX_UNLOAD_EILSEQ_MODE save me and the export process was fine.
Then the dbimport fail because of presence of the default DBDELIMITER | in the
datas. After many checks the only way to is to set the DBDELIMITER at an ascii
extended caracter.
I found a old thread on this board showing a trick to set it like this in env
: DBDELIMITER=`echo -ne "\\\\007"` and that work pretty nice ... except that i
have ascii art inside datas ! All ascii caracters are presents even extended
ascii !!!
So the limitation to one caracter is now blocking me. The ways i'm seeking now
are :
- multi-delimiter like ^|^|^
- external table with an inter-process on the fly
- mounting replication and breaking it after a full sync.
If you have some other ways or clues for succesfully transfert datas between 2
differentes dbs structures i will be strongly interrest.
Thanks in advance
Marc
Marc,
How about using a dbload, the High Performance Loader, dbimport etc to do
this? Its a bit of extra work but nothing outrageous. It will also be quicker
than using dbimport for
big tables and get round your pipe sign problem. 30Gb is not a big database.
How many tables are there in this database? It may be easier to script your
unload and reload using alternative tools.
> To: ids@iiug.org
> From: m.saettel@greenivory.com
> Subject: DBDELIMITER in dbexport/dbimport process [25585]
> Date: Tue, 13 Dec 2011 04:55:25 -0500
>
> Hello,
>
> First, i want to thanks all of you for the big job made here, i cannot count
> the number of time that this board save me.
> Now it's time for me to submit the problem i have.
>
> I'm trying to make a dbexport/dbimport process on a big database (30go+) in
> order to change the dbs structure.
> After the first dbexport fail due to an incorrect encoding caracters, then
> IFX_UNLOAD_EILSEQ_MODE save me and the export process was fine.
> Then the dbimport fail because of presence of the default DBDELIMITER | in
the
> datas. After many checks the only way to is to set the DBDELIMITER at an
ascii
> extended caracter.
> I found a old thread on this board showing a trick to set it like this in env
> : DBDELIMITER=`echo -ne "\\\\007"` and that work pretty nice ... except that i
> have ascii art inside datas ! All ascii caracters are presents even extended
> ascii !!!
>
> So the limitation to one caracter is now blocking me. The ways i'm seeking
now
> are :
> - multi-delimiter like ^|^|^
> - external table with an inter-process on the fly
> - mounting replication and breaking it after a full sync.
>
> If you have some other ways or clues for succesfully transfert datas between
2
> differentes dbs structures i will be strongly interrest.
>
> Thanks in advance
>
> Marc
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Are the structure changes in the schema too complex to allow you to just
ALTER the tables in-place? How about just copying the data from one table
to the other directly using INSERT INTO ... SELECT .... or using my dbcopy
utility? There are sample scripts for that in my utils4_ak package.
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, Dec 13, 2011 at 4:55 AM, MARC SAETTEL <m.saettel@greenivory.com>wrote:
> Hello,
>
> First, i want to thanks all of you for the big job made here, i cannot
> count
> the number of time that this board save me.
> Now it's time for me to submit the problem i have.
>
> I'm trying to make a dbexport/dbimport process on a big database (30go+) in
> order to change the dbs structure.
> After the first dbexport fail due to an incorrect encoding caracters, then
> IFX_UNLOAD_EILSEQ_MODE save me and the export process was fine.
> Then the dbimport fail because of presence of the default DBDELIMITER | in
> the
> datas. After many checks the only way to is to set the DBDELIMITER at an
> ascii
> extended caracter.
> I found a old thread on this board showing a trick to set it like this in
> env
> : DBDELIMITER=`echo -ne "\\\\007"` and that work pretty nice ... except that i
> have ascii art inside datas ! All ascii caracters are presents even
> extended
> ascii !!!
>
> So the limitation to one caracter is now blocking me. The ways i'm seeking
> now
> are :
> - multi-delimiter like ^|^|^
> - external table with an inter-process on the fly
> - mounting replication and breaking it after a full sync.
>
> If you have some other ways or clues for succesfully transfert datas
> between 2
> differentes dbs structures i will be strongly interrest.
>
> Thanks in advance
>
> Marc
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae93411512a662804b3f70c80
Dbload will still suffer from the delimiter problem though, so no go. Hmm,
I like the original suggestion to use external tables. The OP would have
to create the external tables in INFORMIX mode rather than the default
delimited mode, however, but it would work and be rather faster than
dbexport/dbimport to boot.
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, Dec 13, 2011 at 5:08 AM, Andrew Grantham <agrantha@hotmail.com>wrote:
> Marc,
>
> How about using a dbload, the High Performance Loader, dbimport etc to do
> this? Its a bit of extra work but nothing outrageous. It will also be
> quicker
> than using dbimport for
> big tables and get round your pipe sign problem. 30Gb is not a big
> database.
> How many tables are there in this database? It may be easier to script your
> unload and reload using alternative tools.
>
> > To: ids@iiug.org
> > From: m.saettel@greenivory.com
> > Subject: DBDELIMITER in dbexport/dbimport process [25585]
> > Date: Tue, 13 Dec 2011 04:55:25 -0500
> >
> > Hello,
> >
> > First, i want to thanks all of you for the big job made here, i cannot
> count
> > the number of time that this board save me.
> > Now it's time for me to submit the problem i have.
> >
> > I'm trying to make a dbexport/dbimport process on a big database (30go+)
> in
> > order to change the dbs structure.
> > After the first dbexport fail due to an incorrect encoding caracters,
> then
> > IFX_UNLOAD_EILSEQ_MODE save me and the export process was fine.
> > Then the dbimport fail because of presence of the default DBDELIMITER |
> in
> the
> > datas. After many checks the only way to is to set the DBDELIMITER at an
> ascii
> > extended caracter.
> > I found a old thread on this board showing a trick to set it like this in
> env
> > : DBDELIMITER=`echo -ne "\\\\007"` and that work pretty nice ... except
> that i
> > have ascii art inside datas ! All ascii caracters are presents even
> extended
> > ascii !!!
> >
> > So the limitation to one caracter is now blocking me. The ways i'm
> seeking
> now
> > are :
> > - multi-delimiter like ^|^|^
> > - external table with an inter-process on the fly
> > - mounting replication and breaking it after a full sync.
> >
> > If you have some other ways or clues for succesfully transfert datas
> between
> 2
> > differentes dbs structures i will be strongly interrest.
> >
> > Thanks in advance
> >
> > Marc
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3b9d4d2e3d6504b3f74cf1
Thanks for all your suggestions, i'm looking on all this ways with rtfm and
running tests.
For having more accurate info, the structure must not change, i only want to
move to a different dbspace structure.
The first thing i can say is that onunload will not work because of warning
about encoding.
You can do that using ALTER FRAGMENT ON <tablename> INIT IN <dbspacename or
fragmentation expression>; You do not need to unload your data! This
works whether the table is currently fragmented or not!
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, Dec 13, 2011 at 8:49 AM, MARC SAETTEL <m.saettel@greenivory.com>wrote:
> Thanks for all your suggestions, i'm looking on all this ways with rtfm and
> running tests.
>
> For having more accurate info, the structure must not change, i only want
> to
> move to a different dbspace structure.
>
> The first thing i can say is that onunload will not work because of warning
> about encoding.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3b9d4d072cf604b3fa76b6
Hi Marc,
It seems that you want to copy from one server to another or from one
database to another. If this is the case, just create another table ( a
new one) at the destination with the same schema and using type RAW if
you source database uses logging. If the schema is not the same, make
sure to list the comumns needed.
After that just do a direct copy using : INSERT into <destination>
SELECT * from <source> for each table.
You can probably run several of these INSERTs in parallel to save time.
DO it gardually, since you might exhaust the load on your network. Do
not forget to set FET_BUF_SIZE to the highest available today.
What IDS version are you using?
You can probably use EXTERNAL TABLES. This is the fastest route to
unload or load but you might run into the same problem with the
delimiter if you use the the DELIMITED format. You might in this case
use the FIXED format.
Cordialement, Regards,
Khaled Bentebal
Directeur Général - ConsultiX
Président UGIF - User Group Informix France
IIUG - Board of Directors
Tél: 33 (0) 1 39 12 18 00
Fax: 33 (0) 1 39 12 18 18
Mobile: 33 (0) 6 07 78 41 97
Email: khaled.bentebal@consult-ix.fr
Site Web: www.consult-ix.fr
Le 13/12/11 10:55, MARC SAETTEL a écrit :
> Hello,
>
> First, i want to thanks all of you for the big job made here, i cannot count
> the number of time that this board save me.
> Now it's time for me to submit the problem i have.
>
> I'm trying to make a dbexport/dbimport process on a big database (30go+) in
> order to change the dbs structure.
> After the first dbexport fail due to an incorrect encoding caracters, then
> IFX_UNLOAD_EILSEQ_MODE save me and the export process was fine.
> Then the dbimport fail because of presence of the default DBDELIMITER | in
the
> datas. After many checks the only way to is to set the DBDELIMITER at an
ascii
> extended caracter.
> I found a old thread on this board showing a trick to set it like this in env
> : DBDELIMITER=`echo -ne "\\\\007"` and that work pretty nice ... except that i
> have ascii art inside datas ! All ascii caracters are presents even extended
> ascii !!!
>
> So the limitation to one caracter is now blocking me. The ways i'm seeking
now
> are :
> - multi-delimiter like ^|^|^
> - external table with an inter-process on the fly
> - mounting replication and breaking it after a full sync.
>
> If you have some other ways or clues for succesfully transfert datas between
2
> differentes dbs structures i will be strongly interrest.
>
> Thanks in advance
>
> Marc
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
FYI, the only problem with using ALTER FRAGMENT is that you cannot use that
to relocate the database's system catalog. If you also have to move the
catalog tables (systables, syscolumns, sysindices, etc.) then creating a
new database in different storage and copying the data somehow is the only
solution.
If that's the problem, get my utils4_ak package. One of the AWK scripts in
there, mkcopy.awk, processes dbschema or myschema output to produce a shell
script that copies all of the tables from one database to another using
INSERT INTO ... SELECT ... (you have to create the target database before
running the resulting script). Another AWK script, mkdbcopy.awk, does the
same but the generated shell script uses my dbcopy utility to actually move
the data (dbcopy is included in the package utils2_ak). Using dbcopy is
only a smidge faster than INSERT INTO...SELECT... if both the source and
target databases are in the same server - however, using dbcopy you do not
have to worry about long transactions as the copy is broken down into
partial commits (10,000 rows at a time by default - configurable).
So you would just:
dbschema -d mydatabase -ss >mydatabase.sql 2>/dev/null
awk -f mkdbcopy.awk mydatabase.sql >dbcopy_mydatabase.ksh<Edit the schema file to change dbspaces, create the new database as
mydatabase_copy, run the modified schema>
./dbcopy_mydatabase.ksh $INFORMIXSERVER $INFORMIXSERVER mydatabase
mydatabase_copy
If you use my dbschema replacement utility, myschema (also in the utils2_ak
package), instead of dbschema you can have the CREATE TABLE statements
separated from the CREATE INDEX and constraint DDL into two different files
and optionally include UPDATE STATISTICS (-u) commands to recreate the data
distributions as they were in the original database. Then you would run
the CREATE TABLE script before the dbcopy_mydatabase.ksh and the
index/constraint script after the data has been copied.
Oh, if you decide to just go the ALTER FRAGMENT route, the AWK scripts in
utils4_ak provide good templates for making your own script to
automatically generate the ALTER script from dbschema output.
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, Dec 13, 2011 at 8:49 AM, MARC SAETTEL <m.saettel@greenivory.com>wrote:
> Thanks for all your suggestions, i'm looking on all this ways with rtfm and
> running tests.
>
> For having more accurate info, the structure must not change, i only want
> to
> move to a different dbspace structure.
>
> The first thing i can say is that onunload will not work because of warning
> about encoding.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9341151a8434204b3fb1114