Dbexport

Hello all, IDS 9.21.FC4 HPUX11.0 The customer has requested the archive data we have currently within a dbspace be moved to a seperate database within the live instance. Any ideas....I was planning on using dbexport to tape but I have just read that a dbexport exports the whole database which is not feasable. So to recap I wish to create a new database and populate it with the excisting data from a dbspace.

On Mon, 24 Nov 2003 15:26:44 -0000, "Howard Jones" <howie_lfc@hotmail.com> wrote: >Hello all, > >IDS 9.21.FC4 >HPUX11.0 > >The customer has requested the archive data we have currently within a >dbspace be moved to a seperate database within the live instance. Any >ideas....I was planning on using dbexport to tape but I have just read that >a dbexport exports the whole database which is not feasable. >So to recap I wish to create a new database and populate it with the >excisting data from a dbspace. > Depending on the requirements for online accessibility, any referential integrity requirements, and table size, you could . . . 1. create new database. 2. create table structures in new database 3. "insert into new_database:table1 select * from old_database:table1" 4. Create indices and other table-level objects on tables in new database. Another means could substitute HPL in place of the 'insert into...select from' construct. JWC

I'd go for an alter table init dbspace approach, same you are not on a versionthat supports raw tables. Howard Jones wrote: > > Hello all, > > IDS 9.21.FC4 > HPUX11.0 > > The customer has requested the archive data we have currently within a > dbspace be moved to a seperate database within the live instance. Any > ideas....I was planning on using dbexport to tape but I have just read that > a dbexport exports the whole database which is not feasable. > So to recap I wish to create a new database and populate it with the > excisting data from a dbspace. -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

"Paul Watson" <paul@oninit.com> wrote in message news:3FC24D89.88E41052@oninit.com... > I'd go for an > > alter table init dbspace approach, same you are not on a version> that supports raw tables. How does that work then? How will you reassign the database that the tables are in? To answer the question: if you don't want to dbexport the whole database and import just the tables you want into the new one, I like John Carlson's suggestion(s). > Howard Jones wrote: > > > > Hello all, > > > > IDS 9.21.FC4 > > HPUX11.0 > > > > The customer has requested the archive data we have currently within a > > dbspace be moved to a seperate database within the live instance. Any > > ideas....I was planning on using dbexport to tape but I have just read that > > a dbexport exports the whole database which is not feasable. > > So to recap I wish to create a new database and populate it with the > > excisting data from a dbspace. > > -- > Paul Watson # > Oninit Ltd # Growing old is mandatory > Tel: +44 1436 672201 # Growing up is optional > Fax: +44 1436 678693 # > Mob: +44 7818 003457 # > www.oninit.com #

Howard Jones wrote: > So to recap I wish to create a new database and populate it with the > excisting data from a dbspace. Assuming you want to move entire tables, you have several options. The easiest one in my experience is to create the new tables in the new database and select remotely from the old tables. Then, you can delete the existing tables. Other options are unload and load (which is the same as dbexport and dbimport minus the schema portion and on a select statement basis), HPL (assuming you can get it to work), onunload and onload (which are binary instead of ASCII and faster). Since you are using the same instance, any of these should work. However, in order to select from one database into another, both databases must use the same logging mode. This can fill your logical logs, cause long transactions, etc. The solution, then, is RAW tables. Raw tables are non-logged tables. They can exist in a logged database, but, as they bypass transaction logging, they can be much faster. The insert is still part of a transaction, but the actual data load is not logged. A load job we used to do in 2-4 hours now takes us 45 minutes or less using raw tables, and we no longer wrap through a day's worth of logical logs twice during the process. A raw table can be altered to standard mode with an alter statement, but you should really follow up with a level 0 archive, or attempts to restore can be bad. The drawbacks to raw tables are that you cannot create indexes, constraints, etc., on the table, and you can still hit long transactions if something else fills up the log. Here's how it works: If you have a database prod with buffered log and a table TableA with and a primary key: Get the schema for TableA using the tool of your choice. CREATE DATABASE archive WITH BUFFERED LOG; CREATE RAW TABLE ArchiveTableA (<Same Columns as TableA>) in dbspaceA; INSERT INTO ArchiveTableA SELECT * FROM prod:TableA; ALTER TABLE ArchiveTableA TYPE STANDARD; ALTER TABLE ArchiveTableA ADD CONSTRAINT PRIMARY KEY (<column list>); Follow up with a level 0 archive. Raw tables were created for XPS and were backported into earlier versions. I know they are supported in 7.31.UD4 where I do what you describe on a regular basis. I assume they are in 9.21, but I am not certain what release. Check you release notes to be certain. In my experience, this is the fastest way to move data between databases in the same instance. It is even better on UNIX with a shared memory connection, but it works well across a network, too. Sincerely, Christopher Coleman Database Analyst Pharmacy Division Mediware Information Systems, Inc.