Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
From: Apache Wiki <wikidiffs@apache.org>
To: Apache Wiki <wikidiffs@apache.org>
Date: Wed, 09 May 2012 22:58:47 -0000
Message-ID: <20120509225847.91761.45796@eos.apache.org>
Subject: 
 =?utf-8?q?=5BDb-derby_Wiki=5D_Update_of_=22ListFileNamesOfTables=22_by_St?=
 =?utf-8?q?anleyBradbury?=
Auto-Submitted: auto-generated

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
=3D=3D Listing file names for Derby tables (and a java function example) =
=3D=3D

Ever found a multi-gigabyte Derby data file and wonder what table you neede=
d to compress to shrink it?  I couldn't find anything out of the box that w=
ould do this so am documenting how you can set this up for yourself.  Other=
s 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 sta=
tement create the bigintToHexString function shown below and issue the foll=
owing query:

{{{
select  CAST(TABLENAME as varchar(32)) as DB_table, CAST('C' ||  bigintToHe=
xString
(CONGLOMERATENUMBER)  ||  '.dat'as varchar(12)) as file , isindex
 from SYS.SYSCONGLOMERATES a, SYS.SYSTABLES b
 where a.TABLEID =3D b.TABLEID AND b.TABLETYPE <> 'S' =

and isindex =3D 'false'
order by file;
}}}

=3D=3D=3D Creating the java function 'bigintToHexString' within the databas=
e =3D=3D=3D

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 def=
ault directory, derby.system.home.  =

       The filepath will needed to be specified if the jarfile is not locat=
ed 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' ||  bigintToHe=
xString
(CONGLOMERATENUMBER)  ||  '.dat'as varchar(12)) as file , isindex
 from SYS.SYSCONGLOMERATES a, SYS.SYSTABLES b
 where a.TABLEID =3D b.TABLEID AND b.TABLETYPE <> 'S' =

and isindex =3D '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 =3D t.schemaid
   and t.tabletype =3D 'T'
   and tablename not in (<tableX>,<tableY>,<table...>);
}}}