1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14.

Sunday, December 20, 2009

Review Of IBM WebSphere eXtreme Scale 6 by Packt Publishing

This is a quick few notes on a new book I've started reading through, as per the title of this blog post. The general prognosis is that this is a very respectable piece of material, specifically I like:-
  • The fact that the book has been reviewed by the chief technical architect behind WebSphere eXtreme scale, Billy Newport, this would suggest that technical content is pretty water tight.

  • The author has chosen to write the book in such a way that it is highly accessible to its audience. By this I mean that Anthony Chaves explains WebSphere eXtreme scale from first principles and provides coding examples throughout the book which do not mandate the use of the WebSphere application server.

  • The book contains chapters that represent all the main use case areas for the product culminating in a final "Putting it all together" chapter.
All in all this is a very good book, and I would think that anyone wishing to cover the same subject material would struggle to write something that is more comprehensive than this.

Monday, November 30, 2009

JEE Batch Process Design Part 1

A project I worked on recently used J2EE to implement batch processes. The architecture consisted of a WebSphere application server (version 6.1), Oracle database and a Java client to invoke the batch processes. On initiation, the client retrieves a list of jobs from the database via the application server. The list of jobs was then partitioned into equal sized 'Chunks', calls to a batch control session bean were invoked by threads fired off from the Java client, a chunk of jobs is attached to each thread. On receiving the jobs, the batch control bean invokes other beans to carry out the business logic of the application for each job within the chunk via a loop. In fact this architecture mirrored a batch process solution that can be found devx, to the extent I suspect that this is where it originated from in the first place.

Under this architecture scale out is achieved via IIOP load balancing, whereby the WebSphere workload manager distributes calls made to the batch control bean amongst the nodes in the cluster, or cell to use WebSphere parlance, in a round robin manner. If the CPU capacity of the servers that make up the cell are not uniform, a configurable load balance weighting can be used to redress this inbalance. Refer to this link from IBM for further details on load balance weighting. WebSphere extended deployment adds further intelligence into the process of distributing the workload via traffic shaping.

Whilst this design and architecture worked, it struck me that there were several ways in which this could be improved on:-
  • The client should be as light weight as possible, it should invoke the batch process and report on whether it suceeded or failed. Thus leveraging the qualities of the service the application server provides as much as possible. Specifically, the WebSphere launch client did not in itself provide the same qualities of service as the application server itself, namely workload management, fail over transaction support etc.

  • The application should only use transaction context within the application server.

  • The client prohibits end user application to Java.

  • Using RMI requires the application server RMI boot strap and RMI registry ports to be open.

  • A core design pattern should implement the business logic, such that a uniform design principle for followed for all batch jobs.

  • Threading should be handled within the application server.

  • Inversion of control should be used to decouple the business logic from the batch 'Framework'.

  • Although the software used JDBC batching, much better performance and scalability could be realised using oracle.sql.array for array processing.

  • Raw JDBC was used for persistance, a better design practise would be to externalise all SQL in the application and configuration.

  • For scale out, each node would acquire its configuration, storing the configuration in an LDAP server, a database, JDNI store etc would be much more cluster friendly.

  • An XML cache was used to store fixed domain configuration data, whilst this was more efficient than making frequent trips out to the database, a more light weight solution would have been preferable. When in full flight, 20% of each batch processes CPU usage was down to XPath calls associated with this cache.
If you are a sizeable WebSphere shop for which batch processes form a significant part of your IT infrastructure back bone, there is a compelling argument for looking at WebSphere Compute grid. If you are already a major Spring user, Spring batch may worth looking at. However, I'm not going to address the debate of which option is the best fit, suffice it to say both are options available for consideration. Whilst writing this posting, I couldn't help but feel that WebSphere Compute Grid answered a lot of the difficult questions that batch process design asked out of the box.

The salient features of my design are:-
  • Batch invocation via synchronous web service calls.

  • The web service would use JMS, by placing messages on a JMS queue, message driven beans on each node instigate the batch processing, this is the primary mechanism by which scale out is achieved.

  • In addition to the message driven beans facilitating scale out, they provide a means by which the batch process execution is detached from the client, therefore, anything adverse that happens to the client does not affect the batch, this was a conscious design decision.

  • Once the message driven had received a message to initiate the processing of the batch, high throughput on each node would be achieved through calls to methods annotated with the JEE6 @asynchronous annotation. I considered this to be the most light weight option available for this purpose, more light weight than a combination of using JMS and message driven beans.

  • I was considering using Spring, but as Glassfish 3 is fully JEE 6 compliant, and JEE 6 has virtually closed the gap with Spring in terms of Inversion of Control capabilities. I therefore elected to forego Spring in favour of using JEE 6. Also, at the time of writing this posting , Glassfish 3 was the only JEE 6 compliant application server available.

  • Implementation of the business logic using the strategy pattern.

  • Use of the command pattern in order to determine which strategy is required to implement the strategy.

  • Solidity and maturity of a JEE application for transaction management via JTA.

  • The use of JEE6 singleton beans annotated for initialisation on application startup for caching fixed domain data.

  • iBatis and oracle array processing using the oracle.sql.array class. To achieve raw performance, many people may consider a caching grid style solution at first. However, conventional caching wisdom does not apply to true batch processing, in that the same data item is rarely used more than once during the processing of a batch. Therefore any type of cache, regardless of where it is used in the technology stack will be of limited benefit. If any benefit at all is derived from caching, it will be through write behind and the caching of fixed domain data. iBatis is a "Half way house" solution, in that it is not a full blown object relational mapping framework nor is it as free form as using raw JDBC.
Firstly the strategy pattern. I will not steal anyone's thunder on this, as both Snehal Antani of IBM and a colleague and the Java technical architect of the team in which I work have both proposed this. Below is the essence of the strategy pattern in UML class form. By the way, if anyone want a free UML modelling tool, I can strongly recommend Eclipse Galileo with the modelling bundle, which I used to produce this. Double click on the image to obtain a larger version:-Here is a UML class diagram to represent the command pattern:-
The software architecture consists of the following components:-

1. A web service client and service generated using Axis 2 and Eclipse.

2. On the application server, the skeleton for the web service will instigate the BatchControl session bean, the batch delegate. A stand alone java client could invoke this directly.

3. It will be assumed that only one batch process of a given type will run at the same time, therefore the polling port type only needs the batch process name and not a handle.

4. The batch execution strategy will comprise of three steps:-
  • Get workload step, this will obtain a partition ('Chunk') of jobs to be processed.
  • Process workload step, this will use asynchronous method calls to process the jobs obtained from the get workload step.
  • Output the processed results step.
6. Whilst the batch is running, the client will poll the batch control bean at a frequency determined by a configurable polling period. The polling operation will return the number of jobs processed and the total number of jobs that make up the workload. Another as yet specified web service will allow the last execution of a batch process to be reported on in terms the number of individual jobs that failed and were processed successfully within the batch.

The concept of processing jobs in 'Chunks' will be retained, the three steps of get workload, process workload and output results will be iterated around in 'Chunk' size partitions of the the workload until the total workload is processed.

This is illustrated in the UML sequence diagram below.

1. Execute batch process.
2. Place the name of the batch process on the execute batch topic of the batch message queue.
3. Execute the get workload element of the batch strategy.
4. Execute the process workload element of the batch strategy.
5. Execute the output results element of the batch strategy.
6. Poll the web service for batch completion.
7. Call the batch delegate to establish whether or not the batch process is still running.8. Return the number of jobs processed and to process to the web service skeleton.
8. Return the number of jobs processed and to process to the web service skeleton.
9. Return the number of jobs processed and to process to the web service stub.

I am yet to established how to track the progress of the batch processes, the problem being around the ability to store state globally across the cluster representing the total number of jobs that the batch is going to process and the total number of jobs processed. The obvious way to do this is to store this in the database, however, for performance reasons I would like to avoid making trips out to the database unless absolutely necessary. A nice solution would be if Glassfish supported something similar to WebSphere's DistributedMap, then as each node in the cluster completes a 'Chunk' of jobs, it will put to this to the hash map, from which the batch delegate (batch control bean) can work out how much of the workload has been processed.

The UML class diagram below illustrates the fleshed out design, click on it to enlarge.
In future postings I will elaborate on the architecture and design with the intention of providing a code skeleton for the prototype.

Friday, November 13, 2009

Eclipse Findbugs Plugin & Double Checked Locking

This code sample below is for the creation of a singleton object:-

public static SqlConfiguration getSqlConfig(String fileName) {
if (sqlConfigSingleton == null) {
synchronized(SqlConfiguration.class) {
if (sqlConfigSingleton == null) {
try {
parseXmlConfigFile(fileName);
} catch (JDOMException e) {
log.severe(e.getMessage());
} catch (IOException e) {
log.severe(e.getMessage());
}
sqlConfigSingleton = new SqlConfiguration();
}
}
}

There are many reasons as to why the code is wrong, the most obvious one being that creation of the SqlConfiguration object and write to sqlConfigSingleton can be performed out of order. But this is not what impressed me, what impressed me was the fact that findbugs picked this up:-

Bug: Possible doublecheck on com.sqlstress.test.SqlConfiguration.sqlConfigSingleton
Pattern id: DC_DOUBLECHECK, type: DC, category: MT_CORRECTNESS

This method may contain an instance of double-checked locking. This idiom is not correct according to the semantics of the Java memory model. For more information, see the web page http://www.cs.umd.edu/~pugh/java/memoryModel/DoubleCheckedLocking.html.

Impressive right down to the fact a link is proivded to an artcile that discusses this in detail.

Sunday, October 11, 2009

XML Based Database Stress Test Tool

If you refer to this link, you will see an xml file containing example configuration information for a little tool I have been writing in Java. The aim of this is to stress test databases by executing SQL statements concurrently using Java threads. The xml file may seem vaguely reminiscent of an iBatis xml SQL maps, however my tool's configuration document contains information specific to tuning. Connections to the database are provided by a connection factory class, they can be: via a thin or thick JDBC driver (with or without use of the Oracle database resident connection pool) or use connection pooling, facilitated by the Oracle Universal Connection Pool. The code is largely complete, however, before I put it into the public domain, I want it to be pristine in terms of good coding practise, design and documentation. One of my colleagues at work has reviewed it , and its not in bad shape, the only problem being that my class model for storing SQL objects is not very OO, at present as I use one SQL statement class containing logic to work out how the execute method should be performed, i.e.:-
  • A stored procedure, function or package call will require the use of JDBC callable statement.
  • A select statement will return a result set, where as a DML statement will not.
The xml file allows the user to specify whether dml statements are to be run using JDBC batching or whether oracle.sql.ARRAY is to be used to perform array processing. Also, in order t to simulate the effect of hard parsing, the code can force this by putting randomly generated numbers in query hint text, dependant on whether value binding is set to soft or hard.

Hence, I'm in the process of modifying the code to use a Java interface for the SQL Statement class, with separate implementation classes for callable statements, DML statements and select statements, these will be created by a SQL statement factory class depending on what the xml file contains. Once this work is complete, I will update my blog and publish the source code via google doc links.

"Larry Ellison - What The Hell Is Cloud Computing?"

I found this amusing posting on YouTube in which Larry Ellison states that IT is the only industry that is more fashion driver than women's fashion retail, when canvassed for his views on "The cloud". Not only do we work in an industry more fashion driven than women's fashion retail, we work in an industry in which staple technologies such as the RDBMS and J2EE are pronounced as being dead on a regular basis. Recently there was an article on the theserverside.com proclaiming that programming was dead, needless to say it was treated with great derision. This thing about a particular technology being pronounced as dead reminds me of a quote from the late comedian George Burns:-

"Every morning I read the orbituary column. If I don't see my name there I go ahead and have breakfast"

In summary Larry, you are not an idiot.

IBM and Oracle Performance Wars

I see from www.spec.org that Oracle have posted some impressive SPECjAppServer2004 results for the second quarter of 2009. To summarise, 28463.30 JOPS on 17 nodes, 136 cores and 68 chips, putting into the shade IBM's result of 22643.13 JOPS on 16 nodes, 128 cores and 32 chips, posted here. I'm sure that this has not escaped the attention of Andrew Spyker and his team at IBM.

On the same theme, IBM are rumoured to be bringing out a competitor to Exadata 2 called DB2 Pure Scale as reported by The Register. On some of The Register articles, IBMers have jumped on the comments thread to extol the virtues of DB2, and whilst I'm not for a second criticising it as a product, who actually uses it ?. I live and work in the Uk, if I go to www.jobswatch.co.uk and look for DB2 dba jobs, there have been seventeen job adverts for DB2 DBAs in the last three months, versus three hundred and sixty nine for Oracle DBAs, if this web site is to be believed that is.

Friday, September 25, 2009

A DBA Utilities Package Using Java Stored Procedures

I wanted to blog on Java stored procedures, the problem being that all I could come up with was something really contrived such as the infamous ATM machine example that always gets trotted out in training course material. Fortunately I came up with the requirement for a general purpose DBA utilities package, the requirements of which included:-
  • A "Poor mans" data guard using data pump

  • The ability to rotate alert logs etc

  • The ability to produce data pump exports, compress them and maintain an archive, i.e. if you wanted seven days worth of dmp files, when you reached seventh file, as soon as the newest one is produced, the oldest one would be purged.
My main motivation for writing this was to develop a utility package that was portable across Windows and Unix, hence saving the effort in having to maintain multiple scripts.

The second two requirements is where Java stored procedures came in. Although a full desemination of Java stored procedures is beyond the scope of a simple blog posting, I'll bullet point some of more salient points:-
  • The ability to run Java within the database was introduced with Oracle 8i.

  • 10g supports code compiled with the J2SE 1.4 JDK and 11g supports code compiled with the J2SE 1.5 JDK.

  • All Java methods to be wrapped by PL/SQL need to be declared as public (obviously !) and static.

  • The embedded JVM run time uses a different security model compared to the conventional JVM outside the database. Permissions are granted via certain database roles such as JAVASYSPRIV, more fine grained permissions can be granted using the DBMS_JAVA PL/SQL package.

  • Java in the database scales using sessions rather than threads.

  • SQL is run via an embedded JDBC driver.

  • The Oracle 11g JVM furnishes a JIT compiler, prior to this, Java had to be compiled ahead of time using NCOMP.

  • System classes are shared between sessions so as to reduce memory and general resource overheads.
Here is my Java class, much of the code comes courtesy of the Java almanac. I've omitted the use of featues such as annotations, the string builder class and the Java foreach loop, so as to make the code portable between 10 and 11g. Also, note that I've had to remove the original dump file after it has been compressed as the Oracle JVM does not remove the compressed file after the zip stream is closed.


import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.zip.GZIPInputStream;
import java.util.zip.GZIPOutputStream;

public class FileUtils {
/**
*
* This is utility code to be used by the dbutilspkg PL/SQL package, it will purge
* and files from the specified by directory that have:-
*
* 1) Are suffixed by bak.zip
* 2) If there are more than NumFilesToKeep files in the directory with the bak.zip
* extension, it will purge the oldest files, such that only NumFilesToKeep #
* of files remain.
*
* @param Directory directory containing the compressed dump
* files to scan.
* @param NumFilesToKeep number of compressed dump files to keep
*
*/
public static void PurgeDmpFiles(String Directory, long NumFilesToKeep) throws Exception {
int DumpFiles = 0;
int StrLen;

File files[] = new File(Directory).listFiles();

Arrays.sort( files, new Comparator()
{
public int compare(final Object o1, final Object o2) {
return new Long(((File)o1).lastModified()).compareTo
(new Long(((File) o2).lastModified()));
}
} );

for (int i = 0; i < files.length; i++) {
StrLen = files[i].getName().length();

if (StrLen > 7) {
if ( files[i].getName().substring(StrLen - 7, StrLen).equals("bak.zip") ) {
DumpFiles++;
System.out.println("Found a gzipped dump file");
System.out.println("<<" + files[i].getName() + ">>");

if ( DumpFiles > NumFilesToKeep ) {
System.out.println("Deleting a dump file");
files[i].delete();
}
}
}
}
}
/**
*
* Java code to gzip a file based on code from the Java Almanac, note that because
* the JVM embedded in the Oracle RDBMS does not remove the original file after the
* zip stream has been closed, this has had to be coded for.
*
* @param fileName absolue path of file to zip
*
*/
public static void GzipFile(String fileName) {
try {
// Create the GZIP output stream
String outFilename = fileName + ".Z";
GZIPOutputStream out = new GZIPOutputStream(new FileOutputStream(outFilename));

// Open the input file
FileInputStream in = new FileInputStream(fileName);

// Transfer bytes from the input file to the GZIP output stream
byte[] buf = new byte[1024];
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
in.close();

// Complete the GZIP file
out.finish();
out.close();

File finalFile = new File(fileName + "bak.zip");
File tempFile = new File(fileName + ".Z");

tempFile.renameTo(finalFile);

boolean success = (new File(fileName).delete());
if (!success) {
System.err.println("Unable to delete uncompressed dmp file: " + fileName);
}
} catch (IOException e) {
System.err.println(e.getMessage());
}
}
/**
*
* Java code to un-gzip a file.
*
* @param fileName absolue path of file to zip
*
*/
public static void UnGzipFile(String fileName) {
try {
// Open the compressed file
GZIPInputStream in = new GZIPInputStream(new FileInputStream(fileName));

// Open the output file
String outFilename = "outfile";
OutputStream out = new FileOutputStream(outFilename);

// Transfer bytes from the compressed file to the output file
byte[] buf = new byte[1024];
int len;

while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}

// Close the file and stream
in.close();
out.close();
} catch (IOException e) {
System.err.println(e.getMessage());
}
}
/**
*
* Java code to rotate a file if its length exceeded the specified
* rotation size (bytes).
*
* @param fileName absolue path of file to zip
* @param RotateSize size threshhold for file rotation
*
*/
public static void RotateFile(String FileName, long RotateSize) {
File file = new File(FileName);
if ( file.exists() ) {
if ( file.length() >= RotateSize ) {
SimpleDateFormat dateformatMMDDYYYYHHmm = new SimpleDateFormat("MMddyyyyHHmm");
StringBuffer nowMMDDYYYYHHmm = new
StringBuffer( dateformatMMDDYYYYHHmm.format( new Date() ) );
File rotatedFile = new File(FileName + "." + nowMMDDYYYYHHmm);
// Rename file (or directory)
file.renameTo(rotatedFile);
System.out.println("File rotated");
}
else {
System.out.println("File below rotation size");
}
}
else
{
System.out.println("File not rotated");
}
}
}

Below is my PL/SQL package that uses the FileUtils class, if you read the comment banner at the top of package, instruction is provided for compiling and loading the Java class. You can create the source in SQLPLUS via the CREATE JAVA SOURCE command, however my preference is to always write the source in Eclipse, compile it and then load it into the database via loadjava. My apologies for anyone reading this using Google Chrome, for some reason the formatting of the PL/SQL goes awry, Firefox, however, has no such problem.

CREATE OR REPLACE PACKAGE BODY DbUtilsPkg AS
/*==========================================================================*/
/* */
/* Name: DbUtilsPkg (BODY) */
/* */
/* Version: 1.0 */
/* */
/* Description: This package includes useful DBA utilities, namely:- */
/* */
/* 1) A procedure, pump_backup_db, to data pump export */
/* full databases, gzip the dmp files on successful */
/* completion and then remove any obsolete dmp files */
/* , depending on the number of dump file to retain */
/* parameter value specified. */
/* */
/* 2) Rotate log files once they have exceeded the */
/* specified file rotation size. */
/* */
/* Special Notes */
/* ~~~~~~~~~~~~~ */
/* The "DpCopySchema" procedure is intended to function as a poor mans */
/* data guard, as such the destination schemas objects are dropped. I */
/* have elected against dropping the schema, as this would require */
/* the password of the destination schema to be reset afterwards, for */
/* security purposes this package does not handle or manage any */
/* passwords. */
/* */
/* The source code for this, with the exception of the procedures to */
/* drop schema objects comes coutesy of Dani Rey from Trivadis AG, */
/* refer to: http://danirey.wordpress.com . */
/* */
/* Dependencies */
/* ~~~~~~~~~~~~ */
/* This package is dependant upon the FileUtils java class, the source */
/* code for this should be compiled using the version 5 JDK as follows */
/* :- */
/* */
/* javac FileUtils.java */
/* */
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* !!! NOTE !!! Use the J2SE 1.4 JDK javac for Oracle 10g */
/* Use the J2SE 1.5 JDK javac for Oracle 11g *
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* */
/* This will result in to java classes being produced: FileUtils$.class */
/* and FileUtils$1.class, these then need to be loaded into the */
/* database as follows:- */
/* */
/* loadjava r -f username/password FileUtils.class FileUtils$1.class */
/* */
/* Should you wish to debug the java, messages displayed via */
/* System.out.println will appear on the SQPLUS command line, if the */
/* Java is wrapped with PL/SQL after DBMS_JAVA.SET_OUTPUT(<bufferSize>) */
/* and set serveroutput on are issued/ */
/* */
/* Note that the security model for executing Java within the database */
/* differs slightly from that used by PL/SQL, therefore, in order to be */
/* able to write to files, DBMS_JAVA must be used to grant the pre- */
/* requisite permissions to the relevant users:- */
/* */
/* EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', */
/* 'java.io.FilePermission', */
/* '<<ALL FILES>>', */
/* 'read ,write, execute, delete'); */
/* */
/* Grant JAVASYSPRIVS to the relevant user will grant blanket file CRUD */
/* permissions. */
/* */
/* Additionally, the following object permissions are required, unless */
/* this package is run as SYS or SYSTEM or a user with he DBA role */
/* privilege:- */
/* */
/* 1) SELECT on V$DATABASE */
/* 2) EXECUTE on DBMS_DATAPUMP */
/* 3) SELECT privilege on DBA_USERS */
/* 4) execute privilege on DBMS_FLASHBACK */
/* 5) EXP_FULL_DATABASE sys privilege */
/* Not to be confused with EXPORT FULL DATABASE. */
/* 6) CREATE TABLE privilege */
/* 7) SELECT privilege on DBA_DIRECTORIES. */
/* 8) SELECT privilege on V$PARAMETER. */
/* 9) For the schema copy utility:- */
/* i) A database link connecting the destination to the source */
/* schema must exist. */
/* ii) The destination schema must have IMP_FULL_DATABASE and */
/* EXP_FULL_DATABASE privileges. */
/* iii) The source schema must have IMP_FULL_DATABASE and */
/* EXP_FULL_DATABASE privileges. */
/* */
/* History */
/* ~~~~~~~ */
/* */
/* Author Date Change Description */
/* =========== ========== ========================================= */
/* C. J. Adkin 21/09/2009 Initial version created */
/* */
/*==========================================================================*/
PROCEDURE PumpBackupDb (PumpDirectory IN VARCHAR2,
DmpFilesToRetain IN NUMBER DEFAULT 7) IS
DirectoryExists NUMBER;
DpHandle NUMBER;
DoP NUMBER;
DbName VARCHAR2(32);
JobState VARCHAR2(32);
DmpFileAbsolutePath VARCHAR2(100);
DmpFileName VARCHAR2(100);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('Start time : '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
--
BEGIN
SELECT 1
INTO DirectoryExists
FROM dba_directories
WHERE directory_name = PumpDirectory;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Data pump directory '||PumpDirectory||' does not exist');
RAISE;
END;
--
SELECT name
INTO DbName
FROM v$database;
--
DmpFileName := DbName||'_FULL_'||TO_CHAR(SYSDATE, 'DDMMYYYYHH24MI');
--
DBMS_OUTPUT.PUT_LINE('Openning data pump job');
--
DpHandle := DBMS_DATAPUMP.OPEN (operation => 'EXPORT' ,
job_mode => 'FULL' );
--
DBMS_OUTPUT.PUT_LINE('Adding data pump dmp file');
--
DBMS_DATAPUMP.ADD_FILE (handle => DpHandle ,
filename => DmpFileName||'.dmp',
directory => PumpDirectory);
DBMS_OUTPUT.PUT_LINE('Adding data pump log file');
--
DBMS_DATAPUMP.ADD_FILE (handle => DpHandle ,
filename => DmpFileName||'.log',
directory => PumpDirectory,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
--
SELECT value
INTO DoP
FROM v$parameter
WHERE name = 'cpu_count';
--
IF DoP > 1 THEN
DBMS_DATAPUMP.SET_PARALLEL(handle => DpHandle,
degree => DoP);
END IF;
--
DBMS_DATAPUMP.START_JOB(DpHandle);
--
DBMS_DATAPUMP.WAIT_FOR_JOB(DpHandle, JobState);
--
DBMS_DATAPUMP.DETACH(DpHandle);
--
SELECT directory_path
INTO DmpFileAbsolutePath
FROM dba_directories
WHERE directory_name = PumpDirectory;
--
DBMS_OUTPUT.PUT_LINE(DmpFileAbsolutePath);
--
GzipFile(DmpFileAbsolutePath||'\'||DmpFileName||'.dmp');
--
PurgeDumpFiles(DmpFileAbsolutePath, DmpFilesToRetain);
--
DBMS_OUTPUT.PUT_LINE('End time : '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

EXCEPTION
WHEN OTHERS THEN
--dbms_datapump.stop_job(DpHandle);
RAISE;
END;
-------------------------------------------------------------------------------
PROCEDURE PumpCopySchema(sourceSchema IN VARCHAR2,
destinationSchema IN VARCHAR2,
databaseLink IN VARCHAR2,
debug IN BOOLEAN := FALSE) IS
handle NUMBER;
message VARCHAR2(2000);
flashbackSCN NUMBER;
logfileName VARCHAR2(200);
destinationSchemaExists NUMBER;
jobState user_datapump_jobs.state%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start time : '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('verify whether destination schema '||destinationSchema||' already exists');

SELECT COUNT(username)
INTO destinationSchemaExists
FROM dba_users
WHERE username = destinationSchema
AND username NOT IN ('SYS','SYSTEM','DBSNMP','DIP','EXFSYS','OUTLN','TSMSYS');
--
IF ( destinationSchemaExists = 0 ) THEN
DBMS_OUTPUT.PUT_LINE ('---> !!! Source schema does not exist !!!');
ELSE
DBMS_OUTPUT.PUT_LINE ('open datapump job over database link '||databaseLink);
--
handle := dbms_datapump.open (operation => 'IMPORT',
job_mode => 'SCHEMA',
remote_link => databaseLink);
--
IF debug = TRUE THEN
DBMS_OUTPUT.PUT_LINE('set logfile parameter');
logfileName := 'datapump_'||to_char(SYSDATE,'YYYYMMDD')||'.log';
--
DBMS_DATAPUMP.ADD_FILE (handle => handle,
filename => logfileName,
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file);
END IF;
--
DBMS_OUTPUT.PUT_LINE('set FLASHBACK_SCN parameter');
--
DBMS_DATAPUMP.SET_PARAMETER (handle => handle,
name => 'FLASHBACK_SCN',
value => dbms_flashback.get_system_change_number);
--
DBMS_DATAPUMP.SET_PARAMETER (handle => handle,
name => 'TABLE_EXISTS_ACTION',
value => 'TRUNCATE' );
--
DBMS_OUTPUT.PUT_LINE ('define SCHEMA_LIST');
--
DBMS_DATAPUMP.METADATA_FILTER (handle => handle,
name => 'SCHEMA_LIST',
value => ''''||sourceSchema||'''');
--
DBMS_OUTPUT.PUT_LINE ('define schema remapping');
--
DBMS_DATAPUMP.METADATA_REMAP (handle => handle,
name => 'REMAP_SCHEMA',
old_value => sourceSchema,
value => destinationSchema);
--
DBMS_OUTPUT.PUT_LINE('start datapump job');
--
DBMS_DATAPUMP.START_JOB (handle);
--
DBMS_OUTPUT.PUT_LINE ('wait for job to finish');
DBMS_DATAPUMP.WAIT_FOR_JOB (handle, jobState);
END IF;
--
DBMS_OUTPUT.PUT_LINE('End time : '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
--
EXCEPTION
WHEN OTHERS THEN
IF sqlcode = 31631 THEN
DBMS_OUTPUT.PUT_LINE ('Caught ORA-31631, ensure that both source and destination ' ||
'schemas have IMP_FULL_DATABASE and EXP_FULL_DATABASE ' ||
'privileges');
ELSE
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END IF;
END;
----------------------------------------------------------------------------
PROCEDURE PurgeDmpFiles (Directory VARCHAR2,
NumFilesToKeep NUMBER)
IS LANGUAGE JAVA
NAME 'FileUtils.PurgeDmpFiles(java.language.String, long)';
----------------------------------------------------------------------------
PROCEDURE GzipFile (FileName VARCHAR2)
IS LANGUAGE JAVA
NAME 'FileUtils.GzipFile(java.language.String)';
----------------------------------------------------------------------------
PROCEDURE RotateFile (FileName VARCHAR2,
RotateSize NUMBER)
IS LANGUAGE JAVA
NAME 'FileUtils.RotateFile(java.language.String, long)';
END DbUtilsPkg;

Use cases for Java stored procudures include:-
  • The need to integrate the database with other systems and middle ware components which have APIs accessible from Java.

  • "A better PL/SQL", I mainly use PL/SQL as a wrapper for SQL in order to leverage the flow control constructs that PL/SQL gives you. PL/SQL does have object extensions, however, I find these 'clunky' compared to those in Java. Java also supports some basic language features that PL/SQL does not, such as enumeration.

  • Moving some of the business logic out of the middle tier and closer to the data in order to improve performance and scalability by reducing network round trips between the middle tier and the database.

  • A way to leverage skills of Java programmers within the database.

  • Accessing operating system commands from within the database.
There is a wealth of material available on OTN regarding Java stored procedures which goes significantly beyond the simple example presentation here, including sample code and case studies, for further reading refer to:-
  1. OTN: Samples Corner: Java Stored Procedures
  2. OTN: Oracle JVM and Java Stored Procedures
  3. OTN: Oracle JVM Forum
  4. 360 Degree Programming Blog

 
1. 2.