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 Isabelle DASTUGUE <is...@sela-light.com> on 2013/03/19 14:10:21 UTC
Database Size
Hello,
I have a database which is currently 272 MB If I created a second with the same structure and the same data is important, it does more than 2MB
I attempted to reduce the first with:
CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE (),
CALL SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE (1),
CALL and SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE (?, 1)
But that changes nothing.
Is there something else to do?
Why is there so much difference between the two?
Cordially
Isabelle DASTUGUE
------------------------------------------------------------------
Bonjour,
J'ai une base de données qui fait actuellement 272 Mo, Si j'en créé une deuxième avec la même structure et importe les mêmes données, cette dernière ne fait plus que 2 Mo.
J'ai tentée de réduire la première avec :
CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE(),
CALL SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1),
Et CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(?, 1)
Mais cela ne change rien.
Y a t'il autre chose à faire ?
Pourquoi y a-t-il autant de différence entre les 2 ?
Cordialement
Isabelle DASTUGUE
RE: Database Size
Posted by Isabelle DASTUGUE <is...@sela-light.com>.
Hello,
Here is my java code, but I gain nothing ...
try
{
PreparedStatement stmt = conn.prepareStatement("SELECT tablename as TableName, " +
"SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) AS OK " +
"FROM sys.sysschemas s, sys.systables t WHERE s.schemaid = t.schemaid and t.tabletype = 'T'");
ResultSet results = stmt.executeQuery ();
String maTable = null;
SimpleDateFormat sdf = new SimpleDateFormat( "yyyyMMdd" );
String backupdirectory ="C:/Essai/backup" + nom + sdf.format(new Date( System.currentTimeMillis()));
CallableStatement cst = conn.prepareCall
("CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE()");
cst.execute();
cst.close();
cst = conn.prepareCall
("CALL SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(?)");
cst.setInt(1, 1);
cst.execute();
cst.close();
while(results.next())
{
maTable = results.getString("TABLENAME");
System.out.println("ma table : " + maTable);
PreparedStatement stmt1 = conn.prepareStatement("SELECT * FROM TABLE(SYSCS_DIAG.SPACE_TABLE(?, ?)) AS T");
stmt1.setString(1, monschema);
stmt1.setString(2, maTable);
System.out.println("stmt: " + stmt1.toString());
ResultSet results1 = stmt1.executeQuery ();
if (results1.next())
{
System.out.println("ESTIMSPACESAVING : " + results1.getString("ESTIMSPACESAVING"));
System.out.println("NUMFREEPAGES : " + results1.getString("NUMFREEPAGES"));
}
cst = conn.prepareCall
("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
cst.setString(1, monschema);
cst.setString(2, maTable);
cst.setShort(3, (short) 1);
cst.execute();
System.out.println("Fin compression table " + maTable );
}
results.close();
conn.commit();
cst = conn.prepareCall
("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(?, ?)");
cst.setString(1, backupdirectory);
cst.setInt(2, 1);
cst.execute();
cst.close();
System.out.println("backed up database to "+backupdirectory);
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}
Isabelle DASTUGUE
-----Message d'origine-----
De : Knut Anders Hatlen [mailto:knut.hatlen@oracle.com]
Envoyé : mardi 19 mars 2013 14:34
À : Derby Discussion
Objet : Re: Database Size
Isabelle DASTUGUE <is...@sela-light.com> writes:
> Hello,
> I have a database which is currently 272 MB If I created a second with
> the same structure and the same data is important, it does more than
> 2MB I attempted to reduce the first with:
> CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE (), CALL
> SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE (1), CALL and
> SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE
> (?, 1)
> But that changes nothing.
> Is there something else to do?
Hi Isabelle,
The following chapter in the Derby Server and Administration Guide explains why tables and indexes may take more disk space than expected, and how to reclaim the unused space:
https://db.apache.org/derby/docs/10.9/adminguide/cadminspace21579.html
Hope this helps,
--
Knut Anders
Re: Database Size
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Isabelle DASTUGUE <is...@sela-light.com> writes:
> Hello,
> I have a database which is currently 272 MB If I created a second with
> the same structure and the same data is important, it does more than
> 2MB
> I attempted to reduce the first with:
> CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE (),
> CALL SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE (1),
> CALL and SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE
> (?, 1)
> But that changes nothing.
> Is there something else to do?
Hi Isabelle,
The following chapter in the Derby Server and Administration Guide
explains why tables and indexes may take more disk space than expected,
and how to reclaim the unused space:
https://db.apache.org/derby/docs/10.9/adminguide/cadminspace21579.html
Hope this helps,
--
Knut Anders
Re: Database Size
Posted by Katherine Marsden <km...@sbcglobal.net>.
On 3/19/2013 6:10 AM, Isabelle DASTUGUE wrote:
> Hello,
> I have a database which is currently 272 MB If I created a second with
> the same structure and the same data is important, it does more than 2MB
> I attempted to reduce the first with:
> CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE (),
> CALL SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE (1),
> CALL and SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE
> (?, 1)
> But that changes nothing.
> Is there something else to do?
> Why is there so much difference between the two?
It should help to compress the tables. See:
http://db.apache.org/derby/docs/10.8/ref/rrefaltertablecompress.html
> Cordially
> Isabelle DASTUGUE
> ------------------------------------------------------------------
> Bonjour,
> J'ai une base de données qui fait actuellement 272 Mo, Si j'en créé
> une deuxième avec la même structure et importe les mêmes données,
> cette dernière ne fait plus que 2 Mo.
> J'ai tentée de réduire la première avec :
> CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE(),
> CALL SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1),
> Et CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(?, 1)
> Mais cela ne change rien.
> Y a t'il autre chose à faire ?
> Pourquoi y a-t-il autant de différence entre les 2 ?
> Cordialement
> Isabelle DASTUGUE