You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Lars Vogel <la...@googlemail.com> on 2007/08/20 20:46:51 UTC

Create Copy of Database with the necessary files

Hello,

is there a way to create a database copy with only the necessary files?

Background: I did copy "my" database back and forth between a server and my
PC. I always copied the whole directory and did paste it into the existing
target directory in the assumption that all files would be overwritten.

I realized later that as new files are created the file set is not always
the same. Now I want to identify the files which are still used as the
directory is unnecessary large for my small dataset.

The CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE seems to copy all database files
even if they are not in use (see below for the jave coding). Is there a ways
to identify and copy only the used files?

Best regards, Lars


        CallableStatement cs = con
        .prepareCall("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)");
        cs.setString(1, "c:/temp/backupdir3");
        cs.execute();
        cs.close();

Re: Create Copy of Database with the necessary files

Posted by Lars Vogel <la...@googlemail.com>.
Hi Oystein,

thank you for your reply. By unnecessary files I mean file I did accidently
create via cut and paste.

I did manually delete the target directory before the copy.

I'm using Database Version 10.2.2.0 - (485682shipped with Java 6.0.

Currently the backup copies everything. I did test this the following ways:
I copied a file "c21431.dat" to a similar file "c21432.dat" and did the
backup. "c21432.dat" was also copied.

If you believe that this is a bug I can open a bug report for this.

Best regards, Lars

2007/8/21, Oystein Grovlen - Sun Norway <Oy...@sun.com>:
>
> Lars Vogel wrote:
> > Hello,
> >
> > is there a way to create a database copy with only the necessary files?
>
> I am not quite sure I understand what you mean by the necessary files.
>
> >
> > Background: I did copy "my" database back and forth between a server and
> > my PC. I always copied the whole directory and did paste it into the
> > existing target directory in the assumption that all files would be
> > overwritten.
> >
> > I realized later that as new files are created the file set is not
> > always the same. Now I want to identify the files which are still used
> > as the directory is unnecessary large for my small dataset.
>
> Creating and dropping tables and indexes will create and remove the
> corresponding files, respectively.  Compressing a table may also create
> a new file for it.
>
> Note that many of the files represents system tables that are necessary
> even if you do not access them directly.
>
> Is it not an option to recreate the target directory before you do the
> copy?  That way, there should be no unecessary files.
>
> >
> > The CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE seems to copy all database
> > files even if they are not in use (see below for the jave coding). Is
> > there a ways to identify and copy only the used files?
> >
> > Best regards, Lars
> >
> >
> >         CallableStatement cs = con
> >         .prepareCall("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)");
> >         cs.setString(1, "c:/temp/backupdir3");
> >         cs.execute();
> >         cs.close();
>
> Which version are you using?  As far as I know, the non-blocking backup
> mechanism introduced in 10.2, does only backup the tables that are
> defined in the current database dictionary.
>
> --
> Øystein
>

Re: Create Copy of Database with the necessary files

Posted by Oystein Grovlen - Sun Norway <Oy...@Sun.COM>.
Lars Vogel wrote:
> Hello,
> 
> is there a way to create a database copy with only the necessary files?

I am not quite sure I understand what you mean by the necessary files.

> 
> Background: I did copy "my" database back and forth between a server and 
> my PC. I always copied the whole directory and did paste it into the 
> existing target directory in the assumption that all files would be 
> overwritten.
> 
> I realized later that as new files are created the file set is not 
> always the same. Now I want to identify the files which are still used 
> as the directory is unnecessary large for my small dataset.

Creating and dropping tables and indexes will create and remove the 
corresponding files, respectively.  Compressing a table may also create 
a new file for it.

Note that many of the files represents system tables that are necessary 
even if you do not access them directly.

Is it not an option to recreate the target directory before you do the 
copy?  That way, there should be no unecessary files.

> 
> The CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE seems to copy all database 
> files even if they are not in use (see below for the jave coding). Is 
> there a ways to identify and copy only the used files?
> 
> Best regards, Lars
> 
> 
>         CallableStatement cs = con
>         .prepareCall("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)");
>         cs.setString(1, "c:/temp/backupdir3");
>         cs.execute();
>         cs.close();

Which version are you using?  As far as I know, the non-blocking backup 
mechanism introduced in 10.2, does only backup the tables that are 
defined in the current database dictionary.

-- 
Øystein

Re: Create Copy of Database with the necessary files

Posted by Lars Vogel <la...@googlemail.com>.
Hi Bryan,

thank you, I was hoping for an easier way but I will try your suggestion.

Thank you again.

Best regards, Lars

2007/8/20, Bryan Pendleton <bp...@amberpoint.com>:
>
> > always the same. Now I want to identify the files which are still used
>
> In the database folder, each file corresponds to a "conglomerate". Each
> user table,
> system table, and each index on a table is a conglomerate. You can
> figure out how conglomerates map to underlying operating system files
> by using the information in SYSCONGLOMERATES:
>
> http://db.apache.org/derby/docs/dev/ref/rrefsistabs39391.html
>
> Here's how to use that to find the file info:
>
> http://www.ibm.com/developerworks/db2/library/techarticle/dm-0408bradbury/#cs_map_filename
>
> thanks,
>
> bryan
>
>

Re: Create Copy of Database with the necessary files

Posted by Bryan Pendleton <bp...@amberpoint.com>.
> always the same. Now I want to identify the files which are still used 

In the database folder, each file corresponds to a "conglomerate". Each user table,
system table, and each index on a table is a conglomerate. You can
figure out how conglomerates map to underlying operating system files
by using the information in SYSCONGLOMERATES:

http://db.apache.org/derby/docs/dev/ref/rrefsistabs39391.html

Here's how to use that to find the file info:
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0408bradbury/#cs_map_filename

thanks,

bryan