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' 
order by file;

This will list all the non-system tables and indexes. If you want to exclude indexes, add the predicate and isindex = 'false' .

Creating the java function 'bigintToHexString' within the database (using an existing Java API method)

In this case we can take advantage of the fact there is already a suitable method in the Java API:

CREATE FUNCTION bigintToHexString(hexString bigint)
     RETURNS VARCHAR(16)
     PARAMETER STYLE JAVA NO SQL
     LANGUAGE JAVA 
     EXTERNAL NAME 'java.lang.Long.toHexString';

You're done and can run the query :)

Creating the java function 'bigintToHexString' within the database (using your own method)

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;

ListFileNamesOfTables (last edited 2013-10-22 22:25:35 by MyrnavanLunteren)