[Db-derby Wiki] Update of "ListFileNamesOfTables" by StanleyBradbury

[Apache Wiki <wi...@apache.org>]

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...>);
}}}