Kuassi Mensah
Oracle Database Integration with Java, JavaScript, Hadoop, Spark
I - Java in the database, JDBC, UCP, DRCP, Application Continuity, Transaction Guard
II - Oracle Datasource for Hadoop (OD4H), In-Database Container for
Hadoop, Orale Datasource for Spark
III - JavaScript Stored Procedures using Nashorn
All topics discussed here represent my own opinions and speculations.Kuassi Mensahhttp://www.blogger.com/profile/17181349306854451284noreply@blogger.comBlogger108125
Updated: 3 hours 17 min ago
Java Development with Autonomous Transaction Processing Dedicated (ATP-D)
The Oracle Autonomous Transaction Processing Dedicated (ATP-D) is a database Cloud service which allows implementing a private database Cloud service running on dedicated Exadata Infrastructure within the Oracle Public Cloud. The goal of this blog article is to help you, Java developer or architect, build and deploy fast, scalable, and reliable Java applications with ATP-D, using plain Java, Java Servlets, or Java Microservices with WebLogic, Helidon, WebSphere, Liberty, Tomcat, WildFly (JBoss), Spring, and so on.
Please read the full blog post @ https://medium.com/oracledevs/java-development-with-autonomous-transaction-processing-dedicated-atp-d-f0355a2f9abd
Please read the full blog post @ https://medium.com/oracledevs/java-development-with-autonomous-transaction-processing-dedicated-atp-d-f0355a2f9abd
Oracle JDBC drivers on Maven Central
At last!
Yes, you asked for it, and with some delay (better late than ..), we did it!
Maven Central becomes a distribution center for the Oracle JDBC drivers. We started with the latest release 19.3.0.0 but will soon add previous and supported releases.
Read the full post @ https://medium.com/oracledevs/oracle-jdbc-drivers-on-maven-central-64fcf724d8b
Maven Central becomes a distribution center for the Oracle JDBC drivers. We started with the latest release 19.3.0.0 but will soon add previous and supported releases.
Read the full post @ https://medium.com/oracledevs/oracle-jdbc-drivers-on-maven-central-64fcf724d8b
OracleCode One 2019 - My Sessions Pick
OracleCode One 2019 is few weeks ahead and like many, you have not yet made up your mind on which sessions to attend or why attend?
Here is a selection of sessions that I highly recommend (full disclosure, I am involved in these sessions).
Here is a selection of sessions that I highly recommend (full disclosure, I am involved in these sessions).
- Performance and Scalability for Java Applications with an RDBMS: What’s New
Monday, Sep 16, 4:00pm - 4:45pm
- Zero Downtime for Java Applications: What's New, Use Cases, and SolutionsMonday, September 16, 06:00 PM - 06:45 PM
- Reactive Streams Processing with a New Java Library, Helidon, and Microservices Tuesday, September 17, 05:00 PM - 05:45 PM
- Java/JDBC Scalability and Asynchrony: Reactive Extension and Fibers Wednesday, September 18, 12:30 PM - 01:15 PM
- Developing and Deploying Oracle Database Applications in Kubernetes
Wednesday, September 18, 05:00 PM - 05:45 PM - Developing and Deploying Oracle Database Applications in Kubernetes
Wednesday, September 18, 05:00 PM - 05:45 PM - Microservices Essentials: Kubernetes & Ecosystem, Data, and Transaction Patterns
Thursday, Sep 19, 9:00am - 9:45am - A Database Proxy for Transparent HA, Performance, Routing, and Security
Thursday, September 19, 12:15 PM - 01:00 PM - Exploring the Multicloud: Working with Azure and Oracle Autonomous Database
Thursday September 19, 1:15 PM - 2:00 PM
And you don't want to miss this exciting hands-on lab
- Building Microservices Using Oracle Autonomous Database
- Monday, September 16, 01:00 PM - 02:00 PM
Thursday, September 19, 01:30 PM - 02:30 PM (Repeat)
Optimizing the Performance & Scalability of Java Applications that use an RDBMS
PreambleThere is an abundant literature on Java performance (books, articles, blogs, websites, and so on); a Google search returns more than 5 millions hits. To name a few, the Effective Java programming language guide, Java Performance the definitive guide, Java performance tuning newsletter and associated http://www.javaperformancetuning.com website. This is not the purpose of this post.
The goal of this post is to revisit the known best practices for speeding up and scaling database operations for Java applications then discuss database proxy and the upcoming standard Java API for asynchronous database access (ADBA).
Even those familiar with Java optimization techniques will learn new tips!
Speeding up Java applications that use an RDBMS
Optimizing Network TrafficNetwork Data Compression
The ability to compress data transmitted between the Java applications and the RDBMS over LAN or WAN reduces the volume of data, the transfert time and the number of roundtrips.
// Enabling Network Compression
prop.setProperty("oracle.net.networkCompression","on");
// Optional configuration for setting the client compression threshold.
prop.setProperty("oracle.net.networkCompressionThreshold","1024"); ds.setConnectionProperties(prop);
ds.setURL(url);
Connection conn = ds.getConnection();
...
Sessions Multiplexing
The Oracle database Connection Manager a.k.a. CMAN, furnishes the ability to funnel multiple database connections over a single network connection thereby saving OS resources.
In-Place ProcessingAs we have seen earlier, SQL statements execution involves a number of roundtrips between a database client i.e., Java mid-tier/web-server and the RDBMS; this is the rationales for using stored procedures. Even modern data processing such as Hadoop or Spark, collocate the processing and data for low latency.
All RDBMSes furnish stored procedures in various languages including proprietary procedural language such as Oracles PL/SQL but also Java, JavaScript, even PHP, Perl, Python, and TCL.
I discussed the pros and cons of stored procedures in chapter 1 of my book.
I'd add that in a modern Micro-services based architecture, stored procedures are perfect for designing data-bound services.
The Oracle database furnishes Java and PL/SQL stored procedures. Java in the database is one of the best Oracle database gem; see some code samples on GitHub.
Scaling Out Java Applications that use an RDBMS
The goal of this post is to revisit the known best practices for speeding up and scaling database operations for Java applications then discuss database proxy and the upcoming standard Java API for asynchronous database access (ADBA).
Even those familiar with Java optimization techniques will learn new tips!
Speeding up Java applications that use an RDBMS
Optimizing database operations for Java applications includes: speeding up database connectivity, speeding up SQL statements processing, optimizing network traffic, and in-place processing.
Speeding up Database ConnectivityConnection establishment is the most expensive database operation; the obvious optimization that
Java developers have been using for ages is connection pooling which avoids creating connections at
runtime.
Client-side Connection Pools
Java connection pools such as the Apache Commons DBCP, C3P0, as well as the Oracle
Universal Connection Pool (UCP) and many others, run along the JDBC libraries either stand-alone
within the JDK/JRE or as part of Java EE containers datasources (e.g., Tomcat, Weblogic, WebSphere
and others). Java EE containers usually furnish their own connection pools but they also allow
replacing theirs with 3rd party pools (see using UCP with Tomcat, UCP with Weblogic).
Most Java developers use these client-side or mid-tier connection pools for sustaining small and
medium workloads however, these connection pools are confined to the JRE/JDK instance
(i.e., can't be shared beyond the boundary of the JRE/JDK) and unpractical when deploying
thens of thousands of mid-tiers or Web servers. Even with very small pool size each, the RDBMS
server is overwhelmed by thens of thousands of pre-allocated connections that are predominantly
idle (more than 90%).
Proxy Connection Pools
Proxy connection pools such as MySQL Router, Oracle Database Connection Manager in Traffic
Director Mode (CMAN-TDM), and others, are part of proxy servers that sit between the database
clients (i.e., Java apps) and the RDBMS. These allow thousands of database clients to share a
common connection pool. I will discuss this a bit more, near the end of this post.
The Oracle database also furnishes database-side connection pools such as the Shared Servers,
and the Database Resident Connection Pool (DRCP). We will not discuss those in this post.
Other connection optimization features include: deferring connection health check and the
de-prioritization of failed nodes.
Java developers have been using for ages is connection pooling which avoids creating connections at
runtime.
Client-side Connection Pools
Java connection pools such as the Apache Commons DBCP, C3P0, as well as the Oracle
Universal Connection Pool (UCP) and many others, run along the JDBC libraries either stand-alone
within the JDK/JRE or as part of Java EE containers datasources (e.g., Tomcat, Weblogic, WebSphere
and others). Java EE containers usually furnish their own connection pools but they also allow
replacing theirs with 3rd party pools (see using UCP with Tomcat, UCP with Weblogic).
Most Java developers use these client-side or mid-tier connection pools for sustaining small and
medium workloads however, these connection pools are confined to the JRE/JDK instance
(i.e., can't be shared beyond the boundary of the JRE/JDK) and unpractical when deploying
thens of thousands of mid-tiers or Web servers. Even with very small pool size each, the RDBMS
server is overwhelmed by thens of thousands of pre-allocated connections that are predominantly
idle (more than 90%).
Proxy Connection Pools
Proxy connection pools such as MySQL Router, Oracle Database Connection Manager in Traffic
Director Mode (CMAN-TDM), and others, are part of proxy servers that sit between the database
clients (i.e., Java apps) and the RDBMS. These allow thousands of database clients to share a
common connection pool. I will discuss this a bit more, near the end of this post.
The Oracle database also furnishes database-side connection pools such as the Shared Servers,
and the Database Resident Connection Pool (DRCP). We will not discuss those in this post.
Other connection optimization features include: deferring connection health check and the
de-prioritization of failed nodes.
Deferring Connection Health Check
The ability of a connection pool such as Oracle's Universal Connection Pool (UCP) to avoid
checking the health of connections for a defined period of time, improves the latency of
connection check-out (i.e., getConnection() returns faster).
De-prioritization of Failed Nodes
The ability of a connection pool such as Oracle's Universal Connection Pool (UCP) to avoid
checking the health of connections for a defined period of time, improves the latency of
connection check-out (i.e., getConnection() returns faster).
De-prioritization of Failed Nodes
In a multi-instances clustered database environment such as Oracle RAC, this JDBC feature assigns
a low priority to a failed instance for a user-defined period of time thereby reducing the connection
a low priority to a failed instance for a user-defined period of time thereby reducing the connection
establishment latency (iow, avoid attempting to get connections from the failed instance).
Optimizing Statements ProcessingThe default COMMIT mode with JDBC is Auto-COMMIT; unless this corresponds to your desire,
you should explicitly disable Auto-COMMIT on the connection object.
conn.setAutoCommit(false);
Processing a SQL statement requires several steps including: parsing, binding variables, executing,
fetching resultSets (if a query), and COMMITting or ROLLBACKing the transaction (if a DML
i.e., Insert, Update, or Delete).
Java developers have several options for optimizing SQL statements processing including:
Prepared Statements, Statements Caching, ResultSets caching with change notification.
Prepared Statements
Parsing (i.e., hard parsing) is the most expensive operation during the processing of a SQL statement.
The best practices consists in avoiding parsing by using Prepared Statements which are parsed only
once then reused on subsequent invocations, after binding variables. A security byproduct of
Prepared Statements is to prevent SQL injection.
https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
you should explicitly disable Auto-COMMIT on the connection object.
conn.setAutoCommit(false);
fetching resultSets (if a query), and COMMITting or ROLLBACKing the transaction (if a DML
i.e., Insert, Update, or Delete).
Java developers have several options for optimizing SQL statements processing including:
Prepared Statements, Statements Caching, ResultSets caching with change notification.
Prepared Statements
Parsing (i.e., hard parsing) is the most expensive operation during the processing of a SQL statement.
The best practices consists in avoiding parsing by using Prepared Statements which are parsed only
once then reused on subsequent invocations, after binding variables. A security byproduct of
Prepared Statements is to prevent SQL injection.
https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
Statements Caching
Statement caching significantly improves performance. The JDBC driver caches the SQL statements
(PreparedStatements and CallableStatements) on close, using an LRU algorithm then refers
the RDBMS to the parsed form in its library cache (i.e., "use statement #2), during subsequent
invocations of the same statement. Enabled by setting Implicit statement caching to true and
allocating a statement cache in the driver memory (i.e., an array per physical connection).
OracleDataSource ods = new OracleDataSource();
...
ods.setImplicitCachingEnabled( true );
ods.setStmtCacheSize(nn);
...
ResultSets Caching with Change Notification - the Hard Way (JDBC-OCI)
Caching JDBC result sets avoids re-executing the corresponding SQL query, resulting in dramatic
Java applications performance. RDBMSes allow caching ResultSet at the server side but the
applications needs a roundtrip to the database to get these. Optimizing further, these result set can be
pushed to the drivers (JDBC, C/C++, PHP, C#, and so on) and grabbed by the applications without
database roundtrips.
What if the ResultSets become stale, out of sync with the actual RDBMS data? RDBMSes
furnish mechanisms to maintain the ResultSets, up to date. For example, the Oracle database Query
Change Notifications allows registering a SQL query with the RDBMS and receiving notifications
when committed DMLs from other threads render the ResultSets out of sync.
Java applications may explicitly implement ResultSet caching with change notification through the
following steps:
Prerequisite: grant CHANGE NOTIFICATION to the schema (i.e., database user);
grant change notification to HR; // might need your DBA's help.
1) Create a registration
2) Associate a query with the registration
Statement stmt = conn.createStatement();
// associating the query with the registration
((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
// any query that will be executed with the 'stmt' object will be associated with
// the registration 'dcr' until 'stmt' is closed or
// '((OracleStatement)stmt).setDatabaseChangeRegistration(null);' is executed.
...
3) Listen to the notification
...
// Attach the listener to the registration.
// Note: DCNListener is a custom listener and not a predefined or standard
// listener
DCNListener list = new DCNListener(); dcr.addListener(list);
...
catch(SQLException ex) {
// if an exception occurs, we need to close the registration in order
// to interrupt the thread otherwise it will be hanging around.
if(conn != null)
conn.unregisterDatabaseChangeNotification(dcr);
throw ex;
}
ResultSets Caching with Change Notification - the Easy Way (JDBC-Thin with DB 18c)
You may also enable ResultSet caching with invalidation, in a much easier way, using the following
steps (once JDBC-Thin in Oracle database 18c is available on-premise).
1) Set the following database parameters in the database configuration file also known as INIT.ORA
CLIENT_RESULT_CACHE_SIZE=100M // e.g., maximum cache size, in bytes
CLIENT_RESULT_CACHE_LAG=1000 // maximum delay for refreshing the cache (msec)
2) Set the JDBC connection property oracle.jdbc.enableQueryResultCache to true (the default).
3) add the following hint to the SQL query string "/*+ RESULT_CACHE */"
Example "SELECT /*+ RESULT_CACHE */ product_name, unit_price
FROM PRODUCTS WHERE unit_price > 100"
If changing the Java/JDBC source code to add the SQL hint is not an option, you can instruct the
RDBMS to cache the ResultSets of all queries related to a specific table, either at table creation
(default mode) or later (force mode); this is known as Table annotation.
Examples
CREATE TABLE products (...) RESULT_CACHE (MODE DEFAULT);
ALTER TABLE products RESULT_CACHE (MODE FORCE);
The RDBMS furnishes views such as the V$RESULT_CACHE_STATISTICS and
a CLIENT_RESULT_CACHE_STATS$ table for monitoring the effectiveness of ResultSet caching.
See section 15 in the performance tuning guide for more details on configuring the server-side result
set cache
Array Fetch
Array fetching is an absolute necessity when retrieving a large number of rows from a ResultSet.
The fetch size can be specified on Statement, PreparedStatement, CallableStatement, and
ResultSet objects.
Example: pstmt.setFetchSize(20);
When using the Oracle database, this array size is capped by the RDBMS's internal buffer known as
Session Data Unit (SDU). The SDU buffer is used for transferring data from the tables to the client,
over the network. The size of this buffer, in bytes, can be specified in JDBC URL
jdbc:oracle:thin:@(DESCRIPTION=(SDU=10240)
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost-vip)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=myorcldbservicename)))
or at the service level in Net Services configuration files sqlnet.ora and tnsnames.ora.
There is a hard limit depending on the RDBMS release: 2MB with DB 12c, 64K with DB 11.2,
and 32K with DB pre-11.2.
In summary, even if you set the array fetch to a large number, it cannot retrieve more data than the
SDU permits.
Array DML (Update Batch)
The JDBC specification defines array operations as sending a batch of the same DML operations
(i.e., array INSERTs, array UPDATEs, array DELETE) for sequential execution at the server, thereby
reducing network round-trips.
Update Batching consists in explicitly invoking the addBatch methods which adds a statement to
an array operation then explicitly calling executeBatch method.
...
PreparedStatement pstmt =
conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");
pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();
pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();
int[] updateCounts = pstmt.executeBatch();
...
Statement caching significantly improves performance. The JDBC driver caches the SQL statements
(PreparedStatements and CallableStatements) on close, using an LRU algorithm then refers
the RDBMS to the parsed form in its library cache (i.e., "use statement #2), during subsequent
invocations of the same statement. Enabled by setting Implicit statement caching to true and
allocating a statement cache in the driver memory (i.e., an array per physical connection).
OracleDataSource ods = new OracleDataSource();
...
ods.setImplicitCachingEnabled( true );
ods.setStmtCacheSize(nn);
Caching JDBC result sets avoids re-executing the corresponding SQL query, resulting in dramatic
Java applications performance. RDBMSes allow caching ResultSet at the server side but the
applications needs a roundtrip to the database to get these. Optimizing further, these result set can be
pushed to the drivers (JDBC, C/C++, PHP, C#, and so on) and grabbed by the applications without
database roundtrips.
What if the ResultSets become stale, out of sync with the actual RDBMS data? RDBMSes
furnish mechanisms to maintain the ResultSets, up to date. For example, the Oracle database Query
Change Notifications allows registering a SQL query with the RDBMS and receiving notifications
when committed DMLs from other threads render the ResultSets out of sync.
Java applications may explicitly implement ResultSet caching with change notification through the
following steps:
grant change notification to HR; // might need your DBA's help.
1) Create a registration
OracleConnection conn = ods.getConnection();
Properties prop = new Properties();
prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
prop.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION,"true");
...
DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotifictaion(prop);
...
2) Associate a query with the registration
Statement stmt = conn.createStatement();
// associating the query with the registration
((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
// any query that will be executed with the 'stmt' object will be associated with
// the registration 'dcr' until 'stmt' is closed or
// '((OracleStatement)stmt).setDatabaseChangeRegistration(null);' is executed.
...
3) Listen to the notification
...
// Attach the listener to the registration.
// Note: DCNListener is a custom listener and not a predefined or standard
// listener
DCNListener list = new DCNListener(); dcr.addListener(list);
...
catch(SQLException ex) {
// if an exception occurs, we need to close the registration in order
// to interrupt the thread otherwise it will be hanging around.
if(conn != null)
conn.unregisterDatabaseChangeNotification(dcr);
throw ex;
}
ResultSets Caching with Change Notification - the Easy Way (JDBC-Thin with DB 18c)
You may also enable ResultSet caching with invalidation, in a much easier way, using the following
steps (once JDBC-Thin in Oracle database 18c is available on-premise).
1) Set the following database parameters in the database configuration file also known as INIT.ORA
CLIENT_RESULT_CACHE_SIZE=100M // e.g., maximum cache size, in bytes
CLIENT_RESULT_CACHE_LAG=1000 // maximum delay for refreshing the cache (msec)
2) Set the JDBC connection property oracle.jdbc.enableQueryResultCache to true (the default).
3) add the following hint to the SQL query string "/*+ RESULT_CACHE */"
Example "SELECT /*+ RESULT_CACHE */ product_name, unit_price
FROM PRODUCTS WHERE unit_price > 100"
If changing the Java/JDBC source code to add the SQL hint is not an option, you can instruct the
RDBMS to cache the ResultSets of all queries related to a specific table, either at table creation
(default mode) or later (force mode); this is known as Table annotation.
Examples
CREATE TABLE products (...) RESULT_CACHE (MODE DEFAULT);
ALTER TABLE products RESULT_CACHE (MODE FORCE);
The RDBMS furnishes views such as the V$RESULT_CACHE_STATISTICS and
a CLIENT_RESULT_CACHE_STATS$ table for monitoring the effectiveness of ResultSet caching.
See section 15 in the performance tuning guide for more details on configuring the server-side result
set cache
Array Fetch
Array fetching is an absolute necessity when retrieving a large number of rows from a ResultSet.
The fetch size can be specified on Statement, PreparedStatement, CallableStatement, and
ResultSet objects.
Example: pstmt.setFetchSize(20);
When using the Oracle database, this array size is capped by the RDBMS's internal buffer known as
Session Data Unit (SDU). The SDU buffer is used for transferring data from the tables to the client,
over the network. The size of this buffer, in bytes, can be specified in JDBC URL
jdbc:oracle:thin:@(DESCRIPTION=(SDU=10240)
(ADDRESS=(PROTOCOL=tcp)(HOST=myhost-vip)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=myorcldbservicename)))
or at the service level in Net Services configuration files sqlnet.ora and tnsnames.ora.
There is a hard limit depending on the RDBMS release: 2MB with DB 12c, 64K with DB 11.2,
and 32K with DB pre-11.2.
In summary, even if you set the array fetch to a large number, it cannot retrieve more data than the
SDU permits.
Array DML (Update Batch)
The JDBC specification defines array operations as sending a batch of the same DML operations
(i.e., array INSERTs, array UPDATEs, array DELETE) for sequential execution at the server, thereby
reducing network round-trips.
Update Batching consists in explicitly invoking the addBatch methods which adds a statement to
an array operation then explicitly calling executeBatch method.
...
PreparedStatement pstmt =
conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");
pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();
pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();
int[] updateCounts = pstmt.executeBatch();
...
Optimizing Network TrafficNetwork Data Compression
The ability to compress data transmitted between the Java applications and the RDBMS over LAN or WAN reduces the volume of data, the transfert time and the number of roundtrips.
// Enabling Network Compression
prop.setProperty("oracle.net.networkCompression","on");
// Optional configuration for setting the client compression threshold.
prop.setProperty("oracle.net.networkCompressionThreshold","1024"); ds.setConnectionProperties(prop);
ds.setURL(url);
Connection conn = ds.getConnection();
...
Sessions Multiplexing
The Oracle database Connection Manager a.k.a. CMAN, furnishes the ability to funnel multiple database connections over a single network connection thereby saving OS resources.
In-Place ProcessingAs we have seen earlier, SQL statements execution involves a number of roundtrips between a database client i.e., Java mid-tier/web-server and the RDBMS; this is the rationales for using stored procedures. Even modern data processing such as Hadoop or Spark, collocate the processing and data for low latency.
All RDBMSes furnish stored procedures in various languages including proprietary procedural language such as Oracles PL/SQL but also Java, JavaScript, even PHP, Perl, Python, and TCL.
I discussed the pros and cons of stored procedures in chapter 1 of my book.
I'd add that in a modern Micro-services based architecture, stored procedures are perfect for designing data-bound services.
The Oracle database furnishes Java and PL/SQL stored procedures. Java in the database is one of the best Oracle database gem; see some code samples on GitHub.
Scaling Out Java Applications that use an RDBMS
In this section, I will discuss scaling Java applications using Sharded databases, Multitenant databases, database proxy and the upcoming asynchronous Java database access API.
Horizontal Scaling of Java applications with Sharded DatabasesSharded database have been around for a while; think of shards as horizontal partitioning of tables across several databases (iow, partitions on steroids!).
The main impact for developers is that Java application must be Shard-aware; iow, the requirement to: (i) define which fields serve as sharding key, (ii) set the binding values and build the sharding key (and optionally, the super sharding key) before requesting a connection to the datasource. RDBMS vendors are actively working on a routing capability which will remove shard-awareness (see database proxy, later in this post).
Java SE 9 furnishes the standard APIs for building the sharding and supersharding keys.
Depending on the RDBMS implementation, the map of shards keys across databases also know as shard topology is maintained by an external mechanism known as the "Shard Director" (in Oracle database implementation). Without further optimization, all connection requests (with a mandatory sharding key) go to the Shard Director which finds the corresponding shard then a connection is established with that shard.
A Shared Pool for Sharded DBs
The Oracle Universal Connection Pool (UCP) furnishes a shared single pool for all shards.
UCP has been enhanced to transparently suck the shard map (i.e., all the keys that map to a specific shard), from the Shard Director, during the first connection to a specific shard. Once UCP gets the keys range, it no longer needs to go to the Shard Director for subsequent connections requests related to that shard. After a little while, assuming your Java application randomly accesses all shards, UCP will get the entire shard topology from the Shard Director. A high availability byproduct of UCP acting as the Shard Director is that shard-aware Java applications can work even if the Shard Director is down.
The main impact for developers is that Java application must be Shard-aware; iow, the requirement to: (i) define which fields serve as sharding key, (ii) set the binding values and build the sharding key (and optionally, the super sharding key) before requesting a connection to the datasource. RDBMS vendors are actively working on a routing capability which will remove shard-awareness (see database proxy, later in this post).
Java SE 9 furnishes the standard APIs for building the sharding and supersharding keys.
DataSource ds = new MyDataSource();
ShardingKey shardingKey = ds.createShardingKeyBuilder()
.subkey("abc", JDBCType.VARCHAR)
.subkey(94002, JDBCType.INTEGER)
.build(); ...
Connection con = ds.createConnectionBuilder()
.shardingKey(shardingKey)
.build();
Depending on the RDBMS implementation, the map of shards keys across databases also know as shard topology is maintained by an external mechanism known as the "Shard Director" (in Oracle database implementation). Without further optimization, all connection requests (with a mandatory sharding key) go to the Shard Director which finds the corresponding shard then a connection is established with that shard.
A Shared Pool for Sharded DBs
The Oracle Universal Connection Pool (UCP) furnishes a shared single pool for all shards.
UCP has been enhanced to transparently suck the shard map (i.e., all the keys that map to a specific shard), from the Shard Director, during the first connection to a specific shard. Once UCP gets the keys range, it no longer needs to go to the Shard Director for subsequent connections requests related to that shard. After a little while, assuming your Java application randomly accesses all shards, UCP will get the entire shard topology from the Shard Director. A high availability byproduct of UCP acting as the Shard Director is that shard-aware Java applications can work even if the Shard Director is down.
Scaling Java Applications with Multi-Tenant DatabasesMulti-tenancy is a key business requirement for enterprise Java applications. It could be simulated at the application level but true Multi-tenancy requires a Multi-tenant RDBMS where each tenant has it's own database.
Multi-tenant RDBMS scale by managing thousands of databases with one of very few database instances (an instance being the set of processes and memory structures necessary for managing a database), thereby reducing drastically the required computing resources.
How would Java applications scale with Multi-Tenant RDBMS?
A non Multi-tenant aware connection pool would allocate a pool per database, defeating the purpose. UCP has been enhanced to use a single shared pool for all pluggable databases -- a.k.a. PDB (a PDB is the tenant specific database in Oracle's Multi-tenant architecture).
Upon a connection request to a specific PDB, if there is no free/available connection attached to that tenant database, UCP transparently repurposes an idle connection in the pool, which was attached to another PDB to be re-attached to this one, thereby allowing to use a small set of pooled connections to service all tenants while avoiding new connection creation (remember, this is very expensive!) and preserving system resources.
See the UCP doc for more details on using one datasource per tenant or a single datasource for all tenants.
Database proxy
See more details in the CMAN landing page and the Net Services documentions linked from the landing page.Asynchronous Java Database Access API (ADBA)
Multi-tenant RDBMS scale by managing thousands of databases with one of very few database instances (an instance being the set of processes and memory structures necessary for managing a database), thereby reducing drastically the required computing resources.
How would Java applications scale with Multi-Tenant RDBMS?
A non Multi-tenant aware connection pool would allocate a pool per database, defeating the purpose. UCP has been enhanced to use a single shared pool for all pluggable databases -- a.k.a. PDB (a PDB is the tenant specific database in Oracle's Multi-tenant architecture).
Upon a connection request to a specific PDB, if there is no free/available connection attached to that tenant database, UCP transparently repurposes an idle connection in the pool, which was attached to another PDB to be re-attached to this one, thereby allowing to use a small set of pooled connections to service all tenants while avoiding new connection creation (remember, this is very expensive!) and preserving system resources.
See the UCP doc for more details on using one datasource per tenant or a single datasource for all tenants.
Database proxy
Proxies are man-in-the-middle software running between the database and its clients e.g., Java applications. There are several proxy offerings on the market; to name a few: MySQL Router, the Oracle Database Connection Manager in Traffic Director Mode (CMAN-TDM), ProxySQL, and so on.
The Oracle CMAN-TDM is new in Oracle database 18c; it is an extension of the existing Oracle Connection Manager a.k.a. CMAN and furnishes these new following capabilities
The Oracle CMAN-TDM is new in Oracle database 18c; it is an extension of the existing Oracle Connection Manager a.k.a. CMAN and furnishes these new following capabilities
- Fully transparent to applications
- Routes database traffic to right instance (planned)
- Hides database planned and unplanned outages to support zero application downtime
- Optimizes database session usage and application performance
- Enhances database security
CMAN-TDM is client agnostic, iow, it supports all database clients applications including: Java, C, C++, DotNET, Node.js, Python, Ruby, R.
Java applications would connect to CMAN-TDM which, in its turn, connects to the database using the latest driver and libraries then transparently furnish the Quality of Service that the application would get only if it was using the latest driver and APIs.
The existing JDBC API leads to blocked threads, threads scheduling, and contention; it is not suitable for reactive applications or high throughput and large-scale deployments. There exist non-standard asynchronous Java database access APIs but the Java community needs a standard one where user threads never block. User threads submit database operations and return; the API implementation takes care of executing the operations, independently of user threads.
This new API proposal is not intended to be an extension to, or a replacement for, JDBC but, rather, an entirely separate API that provides completely nonblocking access to the same databases as JDBC.
I would encourage all the readers of this blog to review the API and get involved in the discussion.
In order to help the community get a feel of ADBA, an alpha version of it that runs over the vanilla/synchronous JDBC -- that we are calling AoJ for ADBA over JDBC -- will be posted soon, along with a couple of working examples.
I will announce it, when available, on my social media streams including @kmensah, http://db360.blogspot.com/, https://www.linkedin.com/in/kmensah.
The new API proposal relies on the java.util.concurrent.CompletionStage interface; it is available for download from the OpenJDK sandbox @ http://tinyurl.com/java-async-db.
You can sed some examples in the latest JavaOne presentation @ http://bit.ly/2wi948k.There was a suggestion on the mailing list http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/ to rather base he API on the Reactive Streams class java.util.concurrent.Flow; you can follow that discussion in the mailing list.
I would encourage all the readers of this blog to review the API and get involved in the discussion.
In order to help the community get a feel of ADBA, an alpha version of it that runs over the vanilla/synchronous JDBC -- that we are calling AoJ for ADBA over JDBC -- will be posted soon, along with a couple of working examples.
I will announce it, when available, on my social media streams including @kmensah, http://db360.blogspot.com/, https://www.linkedin.com/in/kmensah.
Resources
Slides of my JavaOne and OOW 2017 sessions
The slides of my recommended JavaOne 2017 and OOW2017 sessions are available here.
My Sessions Recommendations for JavaOne and Oracle Open World 2017
Good Day,
JavaOne, and Oracle Open World are 4 exactly weeks away.
Here are some sessions related to Java, JDBC, OJVM (database embedded JVM), JavaScript Nashorn (JavaSCript data access, JavaScript stored procedures), Apache Spark, Apache Hadoop, Apache Flink, Apache Beam that I'd warmly recommend.
JavaOne 2017 Sessions Recommendations
JavaOne, and Oracle Open World are 4 exactly weeks away.
Here are some sessions related to Java, JDBC, OJVM (database embedded JVM), JavaScript Nashorn (JavaSCript data access, JavaScript stored procedures), Apache Spark, Apache Hadoop, Apache Flink, Apache Beam that I'd warmly recommend.
JavaOne 2017 Sessions Recommendations
- JDBC Next: A New Asynchronous API for Connecting to a Database
Tuesday, Oct 03, 11:00 a.m. - 11:45 a.m. | Moscone West - Room 2020 - A RESTful Java framework for Asynchronous High Speed Ingest
Thursday, Oct 05, 10:45 a.m. - 11:30 a.m. | Marriott Marquis (Yerba Buena Level) - Nob Hill A/B - Security Policy File Best Practices for your Java/JDBC Modules
Monday, Oct 02, 12:15 p.m. - 1:00 p.m. | Moscone West - Room 2022 - Portable Database Access for JavaScript Applications, using Java 8 Nashorn
Wednesday, Oct 04, 8:30 a.m. - 9:15 a.m. | Moscone West - Room 2024 - Turning Relational Database Tables into Spark Data Sources
Wednesday, Oct 04, 9:30 a.m. - 10:15 a.m. | Moscone West - Room 2005 - No More Duplicate Transactions: a Java API for At Most Once COMMIT
Monday, Oct 02, 6:30 p.m. - 7:15 p.m. | Moscone West - Room 2024
- Java Best Practices for Developing and Deploying Against Databases in the Cloud
Thursday, Oct 05, 12:45 pm - 1:30pm | Marriott Marquis - Golden Gate C3 - Best Practices for Speeding and Scaling Java Applications
Monday, Oct 02, 5:45 p.m. - 6:30 p.m. | Moscone West - Room 3010 - Multitenancy and Continuous Availability for Java Applications
Tuesday, Oct 03, 5:45 p.m. - 6:30 p.m. | Moscone West - Room 3010 - Hadoop, Spark, Flink, and Beam Explained to Oracle DBAs: Why They Should Care
Wednesday, Oct 04, 4:30 p.m. - 5:15 p.m. | Moscone West - Room 3010 - JavaScript Programming with Oracle Database 12c Using Nashorn Across Tiers
Wednesday, Oct 04, 5:30 p.m. - 6:15 p.m. | Moscone West - Room 3010
Carbonated Java & JavaScript Stored Procedures
Carbonated Java Stored ProceduresFor accessing JSON Collections and documents without any knowledge of SQL, Oracle furnishes the SODA for Java API. It allows a convenient access and navigation using the dot notation.
How to use SODA for Java in Java Stored Procedures? I have posted the steps, the code samples and scripts on GitHub.
Carbonated JavaScript Stored Procedures Nashorn allows interoperability between Java and javaScript. By leveraging such interoperability, I've bee able to reuse SODA for Java with JavaScript Stored Procedures.
How to use SODA for Java in JavaScript Stored Procedures? I have posted the steps, the code samples and scripts on GitHub.
Enjoy!
How to use SODA for Java in Java Stored Procedures? I have posted the steps, the code samples and scripts on GitHub.
Carbonated JavaScript Stored Procedures Nashorn allows interoperability between Java and javaScript. By leveraging such interoperability, I've bee able to reuse SODA for Java with JavaScript Stored Procedures.
How to use SODA for Java in JavaScript Stored Procedures? I have posted the steps, the code samples and scripts on GitHub.
Enjoy!
What's in Oracle Database 12c Release 2 for Java & JavaScript Developers?
Here is the summary of New Java & JavaScript Features in Oracle Database 12c Release 2 on Cloud and on-Premise
- Java 8: Java 8 in JDBC/UCP and OJVM; JDBC 4.2
- JavaScript with Nashorn: JDBC/UCP, OJVM
- Performance: JIT (OJVM), Network Compression over WAN (JDBC), Configurable connection health check frequency (UCP), PL/SQL Callback interface (JDBC)
- Scalability: Shared Pool for Multi-Tenant Database (UCP), Shared Pool for Sharded database (UCP), Sharding Key APIs (JDBC, UCP), DRCP Proxy session sharing, DRCP support for multiple labels
- High-Availability: Java APIs for FAN events (SimpleFan.jar), Planned Maintenance in the driver (JDBC), Application Continuity for XA Datasources, Transaction Guard for XA Datasource
- Security: SSL v1.2 / TLS v 1.2 (JDBC)
- Manageability: XMLconfiguration (UCP), Enable/disable/suspend/resume feature level logging (JDBC), MAX_THINK_TIME for Transactions in progress (DRCP), new statistics view and AWR reports
- Ease of Use : Web Services Callout (OJCM), Long Identifiers (OJVM), PL/SQL Boolean (JDBC), Debugger for OJVM (Java Debug Wire Protocol)
My Talks/Sessions at UKOUG Tech16
Hi guys,
If you plan to attend UKOUG Tech16, please check out my talks.sessions
If you plan to attend UKOUG Tech16, please check out my talks.sessions
- Sunday 12/04 16:10 New JDBC & UCP Perf, Scalability & HA features in Oracle Database 12cR2 @ http://bit.ly/2f9m0ZA
- Monday 12/05 17:55 A RESTful MicroService for JSON Processing in Oracle Database 12c R2 @ bit.ly/2gqmfgB
- Wednesday 12/07 8:50 12Hadoop, Spark & Flink Explained to Oracle DBAs & why They Should Care @ https://t.co/7CGLchAYAF
- Wednesday 12/07 15:10 Integrate BigData with Master Data: Oracle database table as Hadoop Datasource @ http://bit.ly/2gzwU8w
REST Enable Java or JavaScript in the Database
REST Enable Java or JavaScript in the Database
The Oracle REST Data Service (ORDS) allows you to turn Java stored procedures or JavaScript stored procedures into REST Web Services that you may publish these in the Oracle REST Data Service.
See more details @ http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html
Download and Configure ORDS1) First step: download the latest ORDS @ http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html
2) Extract the zip file in a directory say ../ords.3.0.8
3) Configure and Install
$ ls
docs logs params examples ords.war readme.html
Navigate into
Modify the
$vi params/ords_params.properties
Navigate into
params directory and edit the ords_params.properties file. Modify the
standalone.http.port value to a desired port # (e.g., 8090) and save the file.$vi params/ords_params.properties
#Tue Jul 26 05:23:16 UTC 2016
db.hostname=
db.port=
db.servicename=
db.sid=
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
rest.services.apex.add=
rest.services.ords.add=true
schema.tablespace.default=SYSAUX
schema.tablespace.temp=TEMP
standalone.http.port=8090
standalone.static.images=
user.tablespace.default=USERS
user.tablespace.temp=TEMP
~
~
~
~
~ "params/ords_params.properties" 17L, 467C 1,1 All
$ java -jar ords.war
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts
Enter the location to store configuration data:/u01/oracle/ords.3.0.6/ordsi
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:pdb1
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.
Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for SYS:Welcome1
Confirm password:Welcome1
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Sep 07, 2016 3:53:19 AM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults, apex_pu
Installing Oracle REST Data Services version 3.0.6.176.08.46
... Log file written to /u01/oracle/ords.3.0.6/logs/ords_install_core_2016-09-07_035319_00534.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /u01/oracle/ords.3.0.6/logs/ords_install_datamodel_2016-09-07_035342_00050.log
Completed installation for Oracle REST Data Services version 3.0.6.176.08.46. Elapsed time: 00:00:23.840
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Enter 1 if using HTTP or 2 if using HTTPS [1]:1
2016-09-07 03:54:28.867:INFO::main: Logging initialized @158428ms
Sep 07, 2016 3:54:29 AM oracle.dbtools.standalone.StandaloneJetty setupDocRoot
INFO: Disabling document root because the specified folder does not exist: /u01/oracle/ords.3.0.6/ordsi/ords/standalone/doc_root
2016-09-07 03:54:29.525:INFO:oejs.Server:main: jetty-9.2.z-SNAPSHOT
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: No encryption key found in configuration, generating key
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: No mac key found in configuration, generating key
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: Updated configuration with generated keys
2016-09-07 03:54:29.793:INFO:/ords:main: INFO: Using configuration folder: /u01/oracle/ords.3.0.6/ordsi/ords
2016-09-07 03:54:29.793:INFO:/ords:main: FINEST: |ApplicationContext [configurationFolder=/u01/oracle/ords.3.0.6/ordsi/ords, services=Application Scope]|
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: |apex|pu|
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: |apex|pu| is correctly configured
config.dir
2016-09-07 03:54:30.298:INFO:/ords:main: INFO: Oracle REST Data Services initialized|Oracle REST Data Services version : 3.0.6.176.08.46|Oracle REST Data Services server info: jetty/9.2.z-SNAPSHOT|
2016-09-07 03:54:30.305:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@429bd883{/ords,null,AVAILABLE}
2016-09-07 03:54:30.346:INFO:oejs.ServerConnector:main: Started ServerConnector@b7f23d9{HTTP/1.1}{0.0.0.0:8090}
2016-09-07 03:54:30.348:INFO:oejs.Server:main:
Started @159913ms
4) Allow your schema to use ORDS
SQL> exec ords.enable_schema;
SQL> COMMIT;
Define and Configure Your ORDS Service
Let's use the JavaScript procedure defined in an earlier blog post @
http://db360.blogspot.in/2016/11/javascript-in-oracle-database-12c.htmlRem Create a procedure based on the select.js and it's javax.script wrapper (see the previous blog post)
CREATE OR REPLACE PROCEDURE selectproc(id IN varchar2)
IS
output varchar2(10000);
BEGIN
SELECT invokeScriptEval(id) INTO output from dual;
htp.prn(output);
END;
/
SHOW ERRORS;
-- delete load.routes module
begin
ords_services.delete_module(
p_name => 'load.routes');
commit;
end;
/
SHOW ERRORS;
-- External JS select query
-- URL: load/routes/nashorn/select
-- procedure: selectproc
begin
ords.create_service(
p_module_name => 'load.routes' ,
p_base_path => '/load/routes/',
p_pattern => 'nashorn/selectbyid/:id',
p_source_type => 'plsql/block',
p_source => 'begin selectproc(:id); end;'
);
commit;
end;
/
SHOW ERRORS;
Let's use the JavaScript procedure defined in an earlier blog post
Open your web browser and navigate to http://localhost:8090/ords/ordstest/load/routes/nashorn/selectbyid/100.
You must see the JSON document of the employee with empid as 100 displayed.
That's it! You have just created your first ORDS service
The same process can be used tor Java stored procedures or other JavaScript procedures in the database.
My sessions recommendations for JavaOne '15 and OOW '15
JavaOne SF 2015 Session recommendations
Java Connection Pool Performance and Scalability with Wait-Free Programming [CON2158]
Wednesday, Oct 28, 4:30 p.m. | Hilton—Continental Ballroom 1/2/3
OOW SF 2015 - Session recommendations
Java Virtual Machine Cookbook [UGF2720]
Sunday, Oct 25, 9:00 a.m. | Moscone West—3011
Next-Generation Database: Implement Cloud Data Services with Java 8 Nashorn [CON8461]
Monday, Oct 26, 5:15 p.m. | Moscone South—308
Next-Generation Database: Java Connection Pool for Multitenant and Sharded Databases [CON8460]
Monday, Oct 26, 2:45 p.m. | Moscone South—308
Integrate Master Data with Big Data on Hadoop and Spark [CON8459]
Wednesday, Oct 28, 3:00 p.m. | Moscone South—308
Market Basket Analysis Using Oracle In-Database Container for Hadoop [CON8462]
Thursday, Oct 29, 12:00 p.m. | Moscone South—307
Next-Gen Database Enhancements for Java Application Performance and Scalability [CON10310]
Thursday, Oct 29, 2:30 p.m. | Moscone South—307
Dialog with the Oracle Database Java Developers and Architects [MTE9501]
Tuesday, Oct 27, 7:15 p.m. | Moscone South—305
High Availability with Java EE Containers, JDBC, and Java Connection Pools [BOF7732]
Monday, Oct 26, 8:00 p.m. | Parc 55—Mission
Monday, Oct 26, 8:00 p.m. | Parc 55—Mission
Implement Cloud Data Services with Java 8 Nashorn [CON4405]
Java Connection Pool Performance and Scalability with Wait-Free Programming [CON2158]
Wednesday, Oct 28, 4:30 p.m. | Hilton—Continental Ballroom 1/2/3
OOW SF 2015 - Session recommendations
Java Virtual Machine Cookbook [UGF2720]
Sunday, Oct 25, 9:00 a.m. | Moscone West—3011
Next-Generation Database: Implement Cloud Data Services with Java 8 Nashorn [CON8461]
Monday, Oct 26, 5:15 p.m. | Moscone South—308
Next-Generation Database: Java Connection Pool for Multitenant and Sharded Databases [CON8460]
Monday, Oct 26, 2:45 p.m. | Moscone South—308
Integrate Master Data with Big Data on Hadoop and Spark [CON8459]
Wednesday, Oct 28, 3:00 p.m. | Moscone South—308
Market Basket Analysis Using Oracle In-Database Container for Hadoop [CON8462]
Thursday, Oct 29, 12:00 p.m. | Moscone South—307
Next-Gen Database Enhancements for Java Application Performance and Scalability [CON10310]
Thursday, Oct 29, 2:30 p.m. | Moscone South—307
Dialog with the Oracle Database Java Developers and Architects [MTE9501]
Tuesday, Oct 27, 7:15 p.m. | Moscone South—305
JavaScript stored procedures as Cloud data services.
Find out how to implement JavaScript Stored Procedures with Oracle Database 12c and how to invoke these through RESTful Web Services.
https://blogs.oracle.com/java/entry/nashorn_and_stored_procedures
https://blogs.oracle.com/java/entry/nashorn_and_stored_procedures
JavaScript Stored Procedures and Node.js Applications with Oracle Database 12c
Kuassi Mensah
db360.blogspot.com | @kmensah | https://www.linkedin.com/in/kmensah
Node.js and server-side JavaScript are hot and trendy; per the latest “RedMonk Programming Languages Rankings”[1], JavaScript and Java are the top two programming languages. For most developers building modern Web, mobile, and cloud based applications, the ability to use the same language across all tiers (client, middle, and database) feels like Nirvana but the IT landscape is not a green field; enterprises have invested a lot in Java (or other platforms for that matter) therefore, the integration of JavaScript with it becomes imperative. WebSockets and RESTful services enable loose integration however, the advent of JavaScript engines on the JVM (Rhino, Nashorn, DynJS), and Node.js APIs on the JVM (Avatar.js, Nodyn, Trireme), make possible and very tempting to co-locate Java and Node applications on the same JVM.
This paper describes the steps for running JavaScript stored procedures[2]directly on the embedded JVM in Oracle database 12c and the steps for running Node.js applications on the JVM against Orace database 12c, using Avatar.js, JDBC and UCP.
JavaScript and the Evolution of Web Applications Architecture This paper describes the steps for running JavaScript stored procedures[2]directly on the embedded JVM in Oracle database 12c and the steps for running Node.js applications on the JVM against Orace database 12c, using Avatar.js, JDBC and UCP.
At the beginning, once upon a time, long time ago, JavaScript was a browser-only thing while business logic, back-end services and even presentations where handled/produced in middle-tiers using Java or other platforms and frameworks. Then JavaScript engines (Google’s V8, Rhino) leave the browsers and gave birth to server-side JavaScript frameworks and Node.js.
Node Programming ModelNode.js and similar frameworks bring ease of development rapid prototyping, event-driven, and non-blocking programming model[3]to JavaScript. This model is praised for its scalability and good enough performance however, unlike Java, Node lacks standardization in many areas such as database access i.e., JDBC equivalent, and may lead, without discipline, to the so called “callback hell[4]”.
Nonetheless, Node is popular and has a vibrant community and a large set of frameworks[5].
Node Impact on Web Applications ArchitectureNonetheless, Node is popular and has a vibrant community and a large set of frameworks[5].
With the advent of Node, REST and Web Sockets, the architecture of Web applications has evolved into
(i) plain JavaScript on browsers (mobiles, tablets, and desktops);
(ii) server-side JavaScript modules (i.e., Node.js, ORM frameworks) interacting with Java business logic and databases.
The new proposal for Web applications architecture is the integration of Node.js and Java on the JVM. Let’s discuss the enabling technologies: JavaScript engine on the JVM and Node API on the JVM and describe typical use cases with Oracle database 12c.
JavaScript on the JVMWhy implement a JavaScript engine and run JavaScript on the JVM? For starters, i highly recommend Mark Swartz ‘s http://moduscreate.com/javascript-and-the-jvm/and Steve Yegge’s http://steve-yegge.blogspot.com/2008/06/rhinos-and-tigers.htmlblog posts.
In summary, the JVM brings (i) portability; (ii) manageability; (iii) Java tools; (iv) Java libraries/technologies such as JDBC, Hadoop; and (v) the preservation of investments in Java. There are several implementations/projects of Java based JavaScript engines including Rhino, DynJS and Nashorn.Rhino
First JavaScript engine entirely written in Java; started at NetScape in 1997 then, became an open-source Mozilla project[6]. Was for quite some time the default JavaScript engine in Java SE, now replaced by Nashorn in Java SE 8.
DynJSDynJS is another open-source JavaScript engine for the JVM. Here is the project homepage http://dynjs.org/.
Nashorn
Introduced in Java 7 but “production” in Java 8[7], the goal of project Nashorn (JEP 174), is to enhance the performance and security of the Rhino JavaScript engine on the JVM. It integrates with javax.script API (JSR 223) and allows seamless interaction between Java and JavaScript (i.e., invoking Nashorn from Java and invoking Java from Nashorn).
Introduced in Java 7 but “production” in Java 8[7], the goal of project Nashorn (JEP 174), is to enhance the performance and security of the Rhino JavaScript engine on the JVM. It integrates with javax.script API (JSR 223) and allows seamless interaction between Java and JavaScript (i.e., invoking Nashorn from Java and invoking Java from Nashorn).
To illustrate the reach of Nashorn on the JVM and the interaction between Java and JavaScript, let’s run some JavaScript directly on the database-embedded JVM in Oracle database 12c.
JavaScript Stored Procedures with Oracle database 12c Using NashornWhy would anyone run JavaScript in the database? For the same reasons you’d run Java in Oracle database. Then you might ask: why run Java in the database, in the first place? As discussed in my book[8], the primary motivations are:
(i) reuse skills and code, i.e., which programming languages are your new hire knowledgeable of or willing to learn;
(ii) avoid data shipping[9] i.e., in-place processing of billions of data/documents;
(iii) combine SQL with foreign libraries to achieve new database capability thereby extending SQL and the reach of the RDBMS, e.g., Web Services callout, in-database container for Hadoop[10].
Some developers/architects prefer a tight separation between the RDBMS and applications therefore, no programming language in the database[11]but there are many pragmatic developers/architects who run code near data, whenever it is more efficient than shipping data to external infrastructure.
Co-locating functions with data on the same compute engine is shared by many programming models such as Hadoop. With the surge and prevalence of Cloud computing, RESTful service based architecture is the new norm. Data-bound services can be secured and protected by the REST infrastructure, running outside the RDBMS. Typical use case: a JavaScript stored procedures service would process millions/billions of JSON documents in the Oracle database and would return the result sets to the service invoker.
To conclude, running Java, JRuby, Python, JavaScript, Scala, or other programming language on the JVM in the database is a sound architectural choice. The best practices consist in: (i) partitioning applications into data-bound and compute-bound modules or services; (ii) data-bound services are good candidates for running in the database; (iii) understand Oracle database DEFINER INVOKER rights [12]and grant only the necessary privilege(s) and/or permission(s).
The following steps allow implementing JavaScipt stored procedure running in Oracle database; these steps represent an enhancement from the ones presented at JavaOne and OOW 2014 -- which consisted in reading the JavaScript from the file system; such approach required granting extra privileges to the database schema for reading from RDBMS file system something not recommended from security perspective. Here is a safer approach:
1. Nashorn is part of Java 8 but early editions can be built for Java 7; the embedded JavaVM in Oracle database 12c supports Java 6 (the default) or Java 7. For this proof of concept, install Oracle database 12c with Java SE 7 [13]
2. Build a standard Nashorn.jar[14]; (ii) modify the Shell code to interpret the given script name as an OJVM resource; this consists mainly in invoking getResourceAsStream()on the current thread's context class loader ; (iii) rebuild Nashorn.jar with the modified Shell
3. Load the modified Nashorn jar into an Oracle database shema e.g., HR
loadjava -v -r -u hr/ nashorn.jar
loadjava -v -r -u hr/ nashorn.jar
4. Create a new dbms_javascript package for invoking Nashorn’s Shell with a script name as parameter
create or replace package dbms_javascript as
procedure run(script varchar2);
end;
/
create or replace package body dbms_javascript as
procedure run(script varchar2) as
language java name 'com.oracle.nashorn.tools.Shell.main(java.lang.String[])';
end;
/
Then call dbms_javascript,run(‘myscript.js’)from SQL which will invoke Nashorn Shell to execute the previously loaded myscript.js.
create or replace package dbms_javascript as
procedure run(script varchar2);
end;
/
create or replace package body dbms_javascript as
procedure run(script varchar2) as
language java name 'com.oracle.nashorn.tools.Shell.main(java.lang.String[])';
end;
/
Then call dbms_javascript,run(‘myscript.js’)from SQL which will invoke Nashorn Shell to execute the previously loaded myscript.js.
5. Create a custom role, we will name it NASHORN, as follows, connected as SYSTEM
SQL> create role nashorn;
SQL> call dbms_java.grant_permission('NASHORN', 'SYS:java.lang.RuntimePermission', 'createClassLoader', '' );
SQL> call dbms_java.grant_permission('NASHORN', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' );
SQL> call dbms_java.grant_permission('NASHORN', 'SYS:java.util.logging.LoggingPermission', 'control', '' );
SQL> create role nashorn;
SQL> call dbms_java.grant_permission('NASHORN', 'SYS:java.lang.RuntimePermission', 'createClassLoader', '' );
SQL> call dbms_java.grant_permission('NASHORN', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' );
SQL> call dbms_java.grant_permission('NASHORN', 'SYS:java.util.logging.LoggingPermission', 'control', '' );
Best practice: insert those statements in a nash-role.sqlfile and run the script as SYSTEM
6. Grant the NASHORN role created above to the HR schema as follows (connected as SYSTEM):
SQL> grant NASHORN to HR;
SQL> grant NASHORN to HR;
7. Insert the following JavaScript code in a file e.g., database.js stored on your client machine’s (i.e., a machine from which you will invoke loadjava as explained in the next step).
This script illustrates using JavaScript and Java as it uses the server-side JDBC driver to execute a PreparedStatement to retrieve the first and last names from the EMPLOYEES table.
This script illustrates using JavaScript and Java as it uses the server-side JDBC driver to execute a PreparedStatement to retrieve the first and last names from the EMPLOYEES table.
var Driver = Packages.oracle.jdbc.OracleDriver;
var oracleDriver = new Driver();
var url = "jdbc:default:connection:"; // server-side JDBC driver
var query ="SELECT first_name, last_name from employees";
// Establish a JDBC connection
var connection = oracleDriver.defaultConnection();
// Prepare statement
var preparedStatement = connection.prepareStatement(query);
// execute Query
var resultSet = preparedStatement.executeQuery();
// display results
while(resultSet.next()) {
print(resultSet.getString(1) + "== " + resultSet.getString(2) + " " );
}
// cleanup
resultSet.close();
preparedStatement.close();
connection.close();
var oracleDriver = new Driver();
var url = "jdbc:default:connection:"; // server-side JDBC driver
var query ="SELECT first_name, last_name from employees";
// Establish a JDBC connection
var connection = oracleDriver.defaultConnection();
// Prepare statement
var preparedStatement = connection.prepareStatement(query);
// execute Query
var resultSet = preparedStatement.executeQuery();
// display results
while(resultSet.next()) {
print(resultSet.getString(1) + "== " + resultSet.getString(2) + " " );
}
// cleanup
resultSet.close();
preparedStatement.close();
connection.close();
8. Load database.js in the database as a Java resource (not a vanilla class)
loadjava –v –r –u hr/ database.js
loadjava –v –r –u hr/ database.js
9. To run the loaded script
sqlplus hr/
SQL>set serveroutput on
SQL>call dbms_java.set_output(80000)
SQL>call dbms_javascript.run(‘database.js’);
SQL>set serveroutput on
SQL>call dbms_java.set_output(80000)
SQL>call dbms_javascript.run(‘database.js’);
The Nashorn Shell reads ‘database.js’ script stored as Java Resource from internal table; the JavaScript in its turn invokes JDBC to execute a PreparedStatement and the result set is displayed on the console. The message “ORA=29515: exit called from Java code with status 0” is due to the invocation of java.lang.Runtime.exitInternal; and status 0 means normal exit (i.e., no error). The fix is to remove that call from Nashorn.
As discussed earlier, Node.js is becoming the man-in-the-middle between Web applications front ends and back-end legacy components and since companies have invested a lot in Java, it is highly desirable to co-locate Node.js and Java components on the same JVM for better integration thereby eliminating the communication overhead. There are several projects re-implementing Node.js APIs on the JVM including: Avatar.js, Nodyn, and Trireme. This paper will only discuss Oracle’s Avatar.js.
Project Avatar.js[15]The goal of project Avatar.js is to furnish “Node.js on the JVM”; in other words, an implementation of Node.js APIs, which runs on top of Nashorn and enables the co-location of Node.js programs and Java components. It has been outsourced by Oracle under GPL license[16]. Many Node frameworks and/or applications have been certified to run unchanged or slightly patched, on Avatar.js.
There are binary distributions for Oracle Enterprise Linux, Windows and MacOS (64-bits). These builds can be downloaded from https://maven.java.net/index.html#welcome. Search for avatar-js.jar and platform specific libavatar-js libraries (.dll, .so, dylib). Get the latest and rename the jar and the specific native libary accordingly. For example: on Linux, rename the libary to avatar-js.so; on Windows, rename the dll to avatar-js.dll and add its location to your PATH (or use -Djava.library.path=).
RDBMSes in general and Oracle database in particular remain the most popular persistence engines and there are RDBMS specific Node drivers[17]as well as ORMs frameworks. However, as we will demonstrate in the following section, with Avatar.js, we can simply reuse existing Java APIs including JDBC and UCP for database access.
Node Programming with Oracle Database using Avatar.js, JDBC and UCP
The goal of this proof of concept is to illustrate the co-location of a Node.js application, the Avatar.js library, the Oracle JDBC driver and the Oracle Universal Connection Pool (UCP) on the same Java 8 VM.
The sample application consists in a Node.js application which performs the following actions:
(i) Request a JDBC-Thin connection from the Java pool (UCP)
(ii)Create a PreparedStatement object for “SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES”
(iii)Execute the statement and return the ResultSet in a callback
(iv)Retrieve the rows and display in browser on port 4000
(iii)Execute the statement and return the ResultSet in a callback
(iv)Retrieve the rows and display in browser on port 4000
(v) Perform all steps above in a non-blocking fashion – this is Node.js’s raison d’être. The demo also uses Apache ab load generator to simulate concurrent users running the same application in the same/single JVM instance.For the Node application to scale in the absence of asynchronous JDBC APIs, we need to turn synchronous calls into non-blocking ones and retrieve the result set via callback.
Turning Synchronous JDBC Calls into Non-Blocking CallsWe will use the following wrapper functions to turn any JDBC call into a non-blocking call i.e., put the JDBC call into a thread pool and free up the Node event loop thread.
var makeExecutecallback = function(userCallback) {
return function(name, args){
...
userCallback(undefined, args[1]);
}
}
return function(name, args){
...
userCallback(undefined, args[1]);
}
}
function submit(task, callback, msg) {
var handle = evtloop.acquire();
try { var ret = task();
evtloop.post(new EventType(msg, callback, null, ret)); {catch{}
var handle = evtloop.acquire();
try { var ret = task();
evtloop.post(new EventType(msg, callback, null, ret)); {catch{}
evtloop.submit(r);
}
}
Let’s apply these wrapper functions to executeQuery JDBC call, to illustrate the concept
exports.connect = function(userCallback) {..} // JDBC and UCP settings
Statement.prototype.executeQuery = function(query, userCallback) {
var statement = this._statement;
var task = function() {
return statement.executeQuery(query);
}
submit(task, makeExecutecallback(userCallback), "jdbc.executeQuery");
}
var statement = this._statement;
var task = function() {
return statement.executeQuery(query);
}
submit(task, makeExecutecallback(userCallback), "jdbc.executeQuery");
}
Similarly the same technique will be applied to other JDBC statement APIs.
Connection.prototype.getConnection = function() {…}
Connection.prototype.createStatement = function() {..}
Connection.prototype.prepareCall = function(storedprocedure) {..}
Statement.prototype.executeUpdate = function(query, userCallback) {..}
Returning Query ResultSet through a CallbackThe application code fragment hereafter shows how: for every HTTP request: (i) a connection is requested, (ii) the PreparedStatement is executed, and (iii) the result set printed on port 4000.
...
var ConnProvider = require('./connprovider').ConnProvider;
var connProvider = new ConnProvider(function(err, connection){.. });
var server = http.createServer(function(request, response) {
connProvider.getConn(function(name,data){..});
connProvider.prepStat(function(resultset) {
while (resultset.next()) {
response.write(resultset.getString(1) + " --" + resultset.getString(2));
response.write('
');
}
response.write('
');var connProvider = new ConnProvider(function(err, connection){.. });
var server = http.createServer(function(request, response) {
connProvider.getConn(function(name,data){..});
connProvider.prepStat(function(resultset) {
while (resultset.next()) {
response.write(resultset.getString(1) + " --" + resultset.getString(2));
response.write('
');
}
response.write('
response.end();
}
server.listen(4000, '127.0.0.1');
Using Apache AB, we were able to scale to hundreds of simultaneous invocations of the Node application. Each instance grabs a Java connection from The Universal Connection Pool (UCP), executes the SQL statements through JDBC then return the result set via a Callbak on port 4000.
ConclusionsThrough this paper, i discussed the rise of JavaScript for server-side programming and how Java is supporting such evolution; then – something we set out to demonstrate – furnished step by step details for implementing and running JavaScript stored procedures in Oracle database 12c using Nashorn as well as running Node.js applications using Avata.js, Oracle JDBC, UCP against Oracle database 12c.
As server-side JavaScript (typified by Node.js) gains in popularity it’ll have to integrate with existing components (COBOL is still alive!!). Developers, architects will have to look into co-locating JavaScript with Java, across middle and database tiers.
[1] http://redmonk.com/sogrady/2015/01/14/language-rankings-1-15/
[2] I’ll discuss the rationale for running programming languages in the database, later in this paper.
[3] Request for I/O and resource intensive components run in separate process then invoke a Callback in the main/single Node thread, when done.
[4] http://callbackhell.com/
[5] Search the web for “Node.js frameworks”
[6] https://developer.mozilla.org/en-US/docs/Mozilla/Projects/Rhino
[8] http://www.amazon.com/exec/obidos/ASIN/1555583296
[9] Rule of thumb: when processing more than ~20-25% of target data, do it in-place, where data resides (i.e., function shipping).
[10] In-database Container for Hadoop is not available, as of this writing.
[11] Other than database’s specific procedural language, e.g., Oracle’s PL/SQL
[12] I discuss this in chapter 2 of my book; see also Oracle database docs.
[13] See Multiple JDK Support in http://docs.oracle.com/database/121/JJDEV/E50793-03.pdf
[14] Oracle does not furnish a public download of Nashorn.jar for Java 7; search “Nashorn.jar for Java 7”.
[15] https://avatar-js.java.net/
[16] https://avatar-js.java.net/license.html
[17] The upcoming Oracle Node.js driver was presented at OOW 2014.
My DOAG session re:Server-side JavaScript
#DOAG Wed 19/11 17:00 rm HongKong Server-side #JavaScript (#NodeJS) progrm#OracleDB using #nashorn & Avatar.js --#db12c @OracleDBDev #java
Will post shortly ablog re: JavaScript Stored Procedures.
Will post shortly ablog re: JavaScript Stored Procedures.
Preview [video]: Apps HA Solutions for Planned & Unplanned DB Downtime w UCP
http://bit.ly/V0Dycf
My sessions at JavaOne and OOW 2014
My sessions at JavaOne 2014 San Francisco
CON1815 - JavaScript Across Tiers with Nashorn and Avatar.js
BOF1826 - MapReduce over an RDBMS: JDBC Storage Handler or In-Database Container for Hadoop?
CON1851 - Java Applications: At-Most-Once COMMIT Protocol and Capture/Replay Database Calls
Other JavaOne sessions from my teams
CON2152 - DistributableStream: A Java 8 Stream Computational Model for Big Data Processing
My sessions at Oracle OpenWorld 2014
CON1815 - JavaScript Across Tiers with Nashorn and Avatar.js
BOF1826 - MapReduce over an RDBMS: JDBC Storage Handler or In-Database Container for Hadoop?
CON1851 - Java Applications: At-Most-Once COMMIT Protocol and Capture/Replay Database Calls
Other JavaOne sessions from my teams
CON2152 - DistributableStream: A Java 8 Stream Computational Model for Big Data Processing
My sessions at Oracle OpenWorld 2014
Other OOW sessions from my teams
CON7785 - Performance/Scalability with JDBC, Oracle Universal Connection Pool, and Oracle Database 12c En route for OTN Yathra
En route for OTN Yathra (http://t.co/E0KUudWOLn) first stop Mumbai customers visits #java #db12c #mapreduce #hadoop #oracletechnet