db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "ListFileNamesOfTables" by StanleyBradbury
Date Wed, 09 May 2012 22:58:47 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The "ListFileNamesOfTables" page has been changed by StanleyBradbury:
http://wiki.apache.org/db-derby/ListFileNamesOfTables

New page:
## Submitted May 09, 2012
== Listing file names for Derby tables (and a java function example) ==

Ever found a multi-gigabyte Derby data file and wonder what table you needed to compress to
shrink it?  I couldn't find anything out of the box that would do this so am documenting how
you can set this up for yourself.  Others have found it helpful, hope you do too.

The system table SYSCONGLOMERATES references the physical files associated with the data objects
in a Derby database. The filename is constructed as a hexadecimal string representation of
the conglomerate number. To list the file names for all user tables in the tables in a database
using an SQL statement create the bigintToHexString function shown below and issue the following
query:

{{{
select  CAST(TABLENAME as varchar(32)) as DB_table, CAST('C' ||  bigintToHexString
(CONGLOMERATENUMBER)  ||  '.dat'as varchar(12)) as file , isindex
 from SYS.SYSCONGLOMERATES a, SYS.SYSTABLES b
 where a.TABLEID = b.TABLEID AND b.TABLETYPE <> 'S' 
and isindex = 'false'
order by file;
}}}

=== Creating the java function 'bigintToHexString' within the database ===

1. Create and compile the java method {{{
// Class supporting Derby Java Stored Procedures and Functions
import java.sql.*;

public class derbyJavaUtils
{
// bigintToHexString: converts a BIGINT value to a Hexadecimal String
public static String bigintToHexString(long myBigint)
    {
        return Long.toHexString(myBigint);
    }
}
}}}

2. Put it in a JAR that can be loaded into Derby {{{
FORMAT: jar cf jar-file input-file(s)
EXAMPLE:  jar cf derbyJavaUtils.jar derbyJavaUtils.class
}}}

3. Install the JAR File (assumes you are connected to the DB using IJ)  {{{
FORMAT: CALL SQLJ.install_jar( 'jarFilePath', 'qualifiedJarName', 0)
  EXAMPLE: CALL sqlj.install_jar(  'derbyJavaUtils.jar','APP.derbyJavaUtils',0 ) 

    NOTE: In this example the file 'derbyJavaUtils.jar' is in the Derby default directory,
derby.system.home.  
       The filepath will needed to be specified if the jarfile is not located in derby.system.home.
  
}}}

4. Include the JAR in the database classpath (still in IJ) {{{
FORMAT:  Call SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('KEY','VALUE')
  EXAMPLE:  CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY (
             'derby.database.classpath', 'APP.derbyJavaUtils')
}}}

5. Define the function in Derby (yep, still in IJ) {{{
  EXAMPLE:  CREATE FUNCTION bigintToHexString(hexString bigint)
     RETURNS VARCHAR(16)
        PARAMETER STYLE JAVA NO SQL
     LANGUAGE JAVA 
     EXTERNAL NAME 'derbyJavaUtils.bigintToHexString'
}}}
6.  It's ready to go {{{
select  CAST(TABLENAME as varchar(32)) as DB_table, CAST('C' ||  bigintToHexString
(CONGLOMERATENUMBER)  ||  '.dat'as varchar(12)) as file , isindex
 from SYS.SYSCONGLOMERATES a, SYS.SYSTABLES b
 where a.TABLEID = b.TABLEID AND b.TABLETYPE <> 'S' 
and isindex = 'false'
order by file;
}}}

is reported you are done, all is well.  If an exception is thrown the query aborts and there
are tables that have not been checked.  Note the name of the table listed in the exception
and rerun the query excluding the problem table. {{{
SELECT schemaname || '.' || 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'
   and tablename not in (<tableX>,<tableY>,<table...>);
}}}

Mime
View raw message