Whats the best way to export an empty database?
The poster needed to replicate a 300+ table Informix 7 database on a development server with full structure (tables, indexes, triggers, views) but without the sensitive production data. Replies all pointed to dbschema: `dbschema -d dbname > file.sql` to capture the schema, with the `-ss` flag to include server-specific details such as dbspaces, extent sizes, fragmentation and lock level, and DELIMIDENT=1 if column names clash with keywords; the output is then run as an SQL script (dbspaces must exist first). For a sample of rows, suggestions were per-table UNLOAD TO ... SELECT with a WHERE clause and LOAD FROM on the target (script-generated), or the High Performance Loader.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
i've a live database running on UNIX Informix v7. And because of the sensitivity of the data, the client does not wish to allow us to export the data 1 to 1 to our development server. Whats the best practise for such cases? Where i need to have an exact duplicate of the live database with all the database structures, indexes, triggers, etc.. but not the records/data (or if possible a few records)? Btw, the database alone has about 300+ tables.. so to delete the records 1 by 1 will take a very long time.. is there any other more efficient way? Thanks
Michael wrote
> Sent: 26 March 2004 07:24
> To: ids@iiug.org
> Subject: Whats the best way to export an empty database? [2745]
>
>
> i've a live database running on UNIX Informix v7. And because
> of the sensitivity of the data, the client does not wish to
> allow us to export the data 1 to 1 to our development server.
>
> Whats the best practise for such cases? Where i need to have
> an exact duplicate of the live database with all the database
> structures, indexes, triggers, etc.. but not the records/data
> (or if possible a few records)? Btw, the database alone has
> about 300+ tables.. so to delete the records 1 by 1 will take
> a very long time.. is there any other more efficient way?
>
dbschema -ss -d database output.sqlgives an sql file that can be run easily.
Getting a few data records is a different ball game !
Colin Bull
c.bull@videonetworks.com
----LNX_Fri_Mar_26_2004_10:56:48_V3.33--
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
>Datum: 2004.03.26 10:39:34
>Sender: Michael <michael@mikeymall.com>
>
>i've a live database running on UNIX Informix v7. And because of the
>sensitivity of the data, the client does not wish to allow us to export =
the data 1 to 1
>to our development server.
>
>Whats the best practise for such cases? Where i need to have an exact
>duplicate of the live database with all the database structures, indexes=
, triggers,
>etc.. but not the records/data (or if possible a few records)?
=
You can use the command 'dbschema' to create the database table and i=
ndex
information, just run dbschema without parameters to get the syntax. With=
the
option -ss you get information about database layout, too (extentsize=
s and dbspaces) .
If you have column definitions with keywords (like max as column name) se=
tting
the environment variable DELIMIDENT=3D1 before running dbschema may be=
helpful.
=
Regards,
Andreas Kutsche
=
------------------------------------------------
SPAR Oesterreichische Warenhandels-AG
Hauptzentrale
Europastra=DFe 3
A-5015 Salzburg
=
Telefon : +43 662 4470 24423
E-Mail : Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
------------------------------------------------
=
----LNX_Fri_Mar_26_2004_10:56:48_V3.33----
Hi,
if you're interested mainly in the database schema,
the the tool of choice is "dbschema" - easy, isn't it ? :)
Use the option "-ss" to get all the Informix specific "settings"
as well.
It will also (in comments) tell you, how many rows there are in
each table and a few other things, but will contain no data.
You can run this in your instance as SQL script and it will
generate you a database of the same structure, indexes, views,
etc. all included.
Before that of course you've to create/setup all the dbspaces
with the same names as your customer has ...
For data, in this case I'd recommend using SQL statements
"UNLOAD TO 'file_name' select ..." on customer system.
The "select ..." is a normal select statement, with optional
"where"-clause
and everything. So the customer can kind of filter what data to unload.
You transfer the resulting unload files to your in-house system and do
"LOAD FROM 'file_name" insert into 'table_name' " ...
Unfortunately you've to do this on a per-table basis, but probably some
scripts for the unload/load statements can be generated. (I'd probably
use UNIX utilities grep, sed, awk extensively, but there are probably
other ways including SQL.)
Other possibilities would include High Performance Loader (HPL),
which also lets you define certain "rules" for unloading/loading ...
Check the manuals for keywords:
dbschema , load , unload , High Performance Loader.
Regards,
Martin
--
Martin Fuerderer
IBM Informix Development Munich
Data Management Solutions
"Michael" <michael@mikeymall.com>
Sent by: forum.subscriber@iiug.org
26.03.2004 08:24
To
ids@iiug.org
cc
Subject
Whats the best way to export an empty database? [2745]
i've a live database running on UNIX Informix v7. And because of the
sensitivity of the data, the client does not wish to allow us to export
the data 1 to 1 to our development server.
Whats the best practise for such cases? Where i need to have an exact
duplicate of the live database with all the database structures, indexes,
triggers, etc.. but not the records/data (or if possible a few records)?
Btw, the database alone has about 300+ tables.. so to delete the records 1
by 1 will take a very long time.. is there any other more efficient way?
Thanks
Genera
the dbschema of the db and direct to a file
dbschema -d DataBaseName > file
This create the struct of th db with all, except data
Wrote: Jorge garcia
----- Original Message -----
From: "Michael" <michael@mikeymall.com>
To: <ids@iiug.org>
Sent: Friday, March 26, 2004 1:24 AM
Subject: Whats the best way to export an empty database? [2745]
> i've a live database running on UNIX Informix v7. And because of the
sensitivity of the data, the client does not wish to allow us to export the
data 1 to 1 to our development server.
>
> Whats the best practise for such cases? Where i need to have an exact
duplicate of the live database with all the database structures, indexes,
triggers, etc.. but not the records/data (or if possible a few records)?
Btw, the database alone has about 300+ tables.. so to delete the records 1
by 1 will take a very long time.. is there any other more efficient way?
>
> Thanks
>
Jorge,
If you want the dbschema 'file' to include the physical structure
(disk layout, fragmentation, table lock level) add the '-ss' flag after the
'DataBaseName'. More options are available, check out the Informix
Migration Guide at http://publibfi.boulder.ibm.com/epubs/pdf/4372.pdf for a
complete explanation.
HTH
Russell J. Clancy
Senior Database Administrator
Rotech Systems Group
(321) 235-3100 x2017
rclancy@rotech.com
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf
Of Jorge Garcia
Sent: Friday, March 26, 2004 9:34 AM
To: ids@iiug.org
Subject: Re: Whats the best way to export an empty database? [2750]
Genera the dbschema of the db and direct to a file dbschema -d
DataBaseName > file
This create the struct of th db with all, except data
Wrote: Jorge garcia
----- Original Message -----
From: "Michael" <michael@mikeymall.com>
To: <ids@iiug.org>
Sent: Friday, March 26, 2004 1:24 AM
Subject: Whats the best way to export an empty database? [2745]
> i've a live database running on UNIX Informix v7. And because of the
sensitivity of the data, the client does not wish to allow us to export the
data 1 to 1 to our development server.
>
> Whats the best practise for such cases? Where i need to have an exact
duplicate of the live database with all the database structures, indexes,
triggers, etc.. but not the records/data (or if possible a few records)?
Btw, the database alone has about 300+ tables.. so to delete the records 1
by 1 will take a very long time.. is there any other more efficient way?
>
> Thanks
>