RE: dbimport/dbexport
Larry accidentally deleted the .sql schema file produced by dbexport and asked whether dbschema could regenerate a dbimport-compatible version. Art Kagel explained it can't: dbimport needs per-table comments naming the .unl file and row counts, update statistics commands, and indexes/constraints grouped at the end. He suggested his myschema utility (utils2_ak, -l/-u/-U options), noting generated .unl filenames may not match so manual editing is needed. Ping Tang suggested using any .out file, or recreating an empty database from dbschema output, running dbexport on it to get a fresh .sql, then editing row counts to match the existing .unl files. Larry's follow-up question about editing the .sql to put indexes in a different dbspace got no reply.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
(I guess that I should have the proper subject so here is the message again)I
have a question on dbexport. I accidentally deleted the .sql script that goes
with the dbexport. Is it possible to recreate it with dbschema or some other
tool or am I just SOL? I know that the dbschema utility will create a script
to recreate the database structure, but does the dbexport put extra data
specific information in the script for import? Larry
Dbschema will not create a dbimport compatible schema file. My dbschema
replacement utility, myschema, can do so, but there's no guarantee that it
will come up with the same filenames for the .unl files, so you will have to
edit the schema file and either rename the files to match the schema or
modify the filesnames contained in the schema file. If there are many
similar table names your will have several files with the same first few
letters in their names followed by different numbers. Again, dbexport may
have assigned different names, and here it isn't obvious so you may have to
look at the data in the files and guess which table is in which file.
Dbexport/dbimport support is enabled with the -l option.
Myexport is contained in the package utils2_ak which you can download from
the Oninit web site (www.oninit.com/utils) or from the IIUG Software
Repository.
Art
On Thu, Oct 2, 2008 at 12:36 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> (I guess that I should have the proper subject so here is the message
> again)I
> have a question on dbexport. I accidentally deleted the .sql script that
> goes
> with the dbexport. Is it possible to recreate it with dbschema or some
> other
> tool or am I just SOL? I know that the dbschema utility will create a
> script
> to recreate the database structure, but does the dbexport put extra data
> specific information in the script for import? Larry
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
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.
Hi Larry
Do you happen to have a copy of output file (*.out) when you run dbexport?
The *.out file would be the best replacement for your *.sql file.
Since dbexport and dbschema shared some internal functions, for most of simple
database, you should be able to use the sql generated by dbschema.
You need edit the file to remove dbschema header line to avoid syntax error.
However, this may not work if your database have complicated objects.
Regards
-Ping
--- On Thu, 10/2/08, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> From: LARRY SORENSEN <lsorensen25@msn.com>
> Subject: RE: dbimport/dbexport [13556]
> To: ids@iiug.org
> Date: Thursday, October 2, 2008, 11:36 AM
> (I guess that I should have the proper subject so here is
> the message again)I
> have a question on dbexport. I accidentally deleted the
> .sql script that goes
> with the dbexport. Is it possible to recreate it with
> dbschema or some other
> tool or am I just SOL? I know that the dbschema utility
> will create a script
> to recreate the database structure, but does the dbexport
> put extra data
> specific information in the script for import? Larry
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in
> the discussion forum.
Using dbschema output won't work with dbimport for several reasons:
- Dbimport expects to see an additional comment per table containing the
unload file name and row count to validate the load (row count validation is
not automatic, you would have to do that manually, so the actual count is
not critical).
- Dbexport schema contain update statistics commands to replicate the
existing level of data distributions and dbschema has no option to generate
these.
- Dbexport places all of the CREATE INDEX and constraint alters at the
end of the schema. Dbschema mixes them in with the CREATE TABLE
statements.
The myschema -l option takes care of the first item - inserting the unload
file name comments (although as I mentioned in my last post - the file names
are not guaranteed to match dbexport's generated file names). Currently
myschema doesn't take care of the last two items for you automatically, but
you can do this manually. Myschema has the -u option to optionally generate
the update statistics commands to the end of the schema file (or with -U to
a separate script file) and if you pass two filenames on the commandline
myschema will write the index and constraint commands (and the optional
update stats if you used -u) to the second file which you can concatenate to
the first file to replicate the behavior of dbexport.
Art
On Thu, Oct 2, 2008 at 1:19 PM, PING TANG <ptang83@yahoo.com> wrote:
> Hi Larry
>
> Do you happen to have a copy of output file (*.out) when you run dbexport?
> The *.out file would be the best replacement for your *.sql file.
>
> Since dbexport and dbschema shared some internal functions, for most of
> simple
> database, you should be able to use the sql generated by dbschema.
> You need edit the file to remove dbschema header line to avoid syntax
> error.
>
> However, this may not work if your database have complicated objects.
>
> Regards
>
> -Ping
>
> --- On Thu, 10/2/08, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > From: LARRY SORENSEN <lsorensen25@msn.com>
> > Subject: RE: dbimport/dbexport [13556]
> > To: ids@iiug.org
> > Date: Thursday, October 2, 2008, 11:36 AM
> > (I guess that I should have the proper subject so here is
> > the message again)I
> > have a question on dbexport. I accidentally deleted the
> > .sql script that goes
> > with the dbexport. Is it possible to recreate it with
> > dbschema or some other
> > tool or am I just SOL? I know that the dbschema utility
> > will create a script
> > to recreate the database structure, but does the dbexport
> > put extra data
> > specific information in the script for import? Larry
> >
> >
> >
>
>
*******************************************************************************
> >
> > Forum Note: Use "Reply" to post a response in
> > the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
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.
Thank you for the responses.
Another question. Has anyone tried to run dbimport specifying a default
dbspace, but then edit the .sql file above where it starts creating the
indexes to specify a different default dbspace for the indexes so that each
create index statement does not have to be edited?
LArry> To: ids@iiug.org> From: art.kagel@gmail.com> Subject: Re:
dbimport/dbexport [13561]> Date: Thu, 2 Oct 2008 13:50:29 -0400> > Using
dbschema output won't work with dbimport for several reasons: > > - Dbimport
expects to see an additional comment per table containing the > > unload file
name and row count to validate the load (row count validation is > > not
automatic, you would have to do that manually, so the actual count is > > not
critical). > > - Dbexport schema contain update statistics commands to
replicate the > > existing level of data distributions and dbschema has no
option to generate > > these. > > - Dbexport places all of the CREATE INDEX
and constraint alters at the > > end of the schema. Dbschema mixes them in
with the CREATE TABLE > > statements. > > The myschema -l option takes care of
the first item - inserting the unload > file name comments (although as I
mentioned in my last post - the file names > are not guaranteed to match
dbexport's generated file names). Currently > myschema doesn't take care of
the last two items for you automatically, but > you can do this manually.
Myschema has the -u option to optionally generate > the update statistics
commands to the end of the schema file (or with -U to > a separate script
file) and if you pass two filenames on the commandline > myschema will write
the index and constraint commands (and the optional > update stats if you used
-u) to the second file which you can concatenate to > the first file to
replicate the behavior of dbexport. > > Art > > On Thu, Oct 2, 2008 at 1:19
PM, PING TANG <ptang83@yahoo.com> wrote: > > > Hi Larry > > > > Do you happen
to have a copy of output file (*.out) when you run dbexport? > > The *.out
file would be the best replacement for your *.sql file. > > > > Since dbexport
and dbschema shared some internal functions, for most of > > simple > >
database, you should be able to use the sql generated by dbschema. > > You
need edit the file to remove dbschema header line to avoid syntax > > error. >
> > > However, this may not work if your database have complicated objects. >
> > > Regards > > > > -Ping > > > > --- On Thu, 10/2/08, LARRY SORENSEN
<lsorensen25@msn.com> wrote: > > > > > From: LARRY SORENSEN
<lsorensen25@msn.com> > > > Subject: RE: dbimport/dbexport [13556] > > > To:
ids@iiug.org > > > Date: Thursday, October 2, 2008, 11:36 AM > > > (I guess
that I should have the proper subject so here is > > > the message again)I > >
> have a question on dbexport. I accidentally deleted the > > > .sql script
that goes > > > with the dbexport. Is it possible to recreate it with > > >
dbschema or some other > > > tool or am I just SOL? I know that the dbschema
utility > > > will create a script > > > to recreate the database structure,
but does the dbexport > > > put extra data > > > specific information in the
script for import? Larry > > > > > > > > > > > > > >
*******************************************************************************
> > > > > > Forum Note: Use "Reply" to post a response in > > > the discussion
forum. > > > > > > > > >
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum. > > >
> > > -- > 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. > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >
Hi Larry
Do you mean you have accidentally deleted the *.sql and the database was
dropped too? The only solution I can think of is do the following.
1. use your dbschema output to recreate empty database with old schema
2. dbexport your empty database and generate a new *.sql file
3. edit *.sql file so the "rows" field match up with your *.unl files's row
number.
4. copy the new *.sql file back to *.exp directory.
5. run dbimport
Hope this help.
Regards,
-Ping
--- On Thu, 10/2/08, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> From: LARRY SORENSEN <lsorensen25@msn.com>
> Subject: RE: dbimport/dbexport [13556]
> To: ids@iiug.org
> Date: Thursday, October 2, 2008, 11:36 AM
> (I guess that I should have the proper subject so here is
> the message again)I
> have a question on dbexport. I accidentally deleted the
> .sql script that goes
> with the dbexport. Is it possible to recreate it with
> dbschema or some other
> tool or am I just SOL? I know that the dbschema utility
> will create a script
> to recreate the database structure, but does the dbexport
> put extra data
> specific information in the script for import? Larry
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in
> the discussion forum.