db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Isabelle DASTUGUE <isabelle.dastu...@sela-light.com>
Subject RE: Database Size
Date Tue, 26 Mar 2013 16:25:46 GMT
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 <isabelle.dastugue@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

Mime
View raw message