Feed aggregator

Incremental ETL – The last refresh date

Dylan's BI Notes - Wed, 2017-10-11 15:31
There are multiple ways to model the last refresh date. In OBIA, DAC and Informatica based ETL, the last refresh date is maintained within DAC. It is maintained at the level of the source tables that populates the data. Oracle BI DAC User Guide > About Refresh Dates and DAC’s Incremental Load Strategy In OBIA […]
Categories: BI & Warehousing

Oracle OpenWorld 2017 Keynotes and Presentations

Richard Byrom - Wed, 2017-10-11 13:00

If you missed out on Oracle OpenWorld 2017 or simply want to catch up on the plethora of keynotes and sessions that were available then the session catalog is a good place to start as you can download presentations there. You can search by keyword or apply any of the following filters to view pertinent content: -

  • Day
  • Emerging Technologies for Business
  • IT Infrastructure
  • Intelligent Cloud Applications
  • Integrated Cloud Platform
  • SMB (Small to Medium Business)
  • Session Type

Oracle OpenWorld Session Catalog Presentations

Furthermore, there’s a pretty good library of on-demand keynotes where you can watch highlights or full length replays.

Oracle OpenWorld Keynote Larry Ellison 2017

Personally, I’m going to start making my way through this content with a view to highlighting some of the best picks here.

Java 9 – First baby steps with Modules and jlink

Amis Blog - Wed, 2017-10-11 12:00

In a recent article, I created an isolated Docker Container as Java 9 R&D environment: https://technology.amis.nl/2017/10/11/quick-and-clean-start-with-java-9-running-docker-container-in-virtualbox-vm-on-windows-10-courtesy-of-vagrant/. In this article, I will use that environment to take few small steps with Java 9 – in particular with modules. Note:this story does not end well. I wanted to conclude with using jlink to create a stand alone runtime that contained both the required JDK modules and my own module – and demonstrate how small that runtime was. Unfortunately, the Link step failed for me. More news on that in a later article.

Create Custom Module

Start a container based on the openjdk:9 image, exposing its port 80 on the docker host machine and mapping folder /vagrant (mapped from my Windows host to the Docker Host VirtualBox Ubuntu image) to /var/www inside the container:

docker run -it -p -v /vagrant:/var/www openjdk:9 /bin/sh

Create Java application with custom module:  I create a single Module (nl.amis.j9demo) and a single class nl.amis.j9demo.MyDemo. The module depends directly on one JDK module (httpserver) and indirectly on several more.

imageThe root directory for the module has the same fully qualified name as the module: nl.amis.j9demo.

This directory contains the module-info.java file. This file specifies:

  • which modules this module depends on
  • which packages it exports (for other modules to create dependencies on)

In my example, the file is very simple – only specifying a dependency on jdk.httpserver:


The Java Class MyDemo has a number of imports. Many are for base classes from the java.base module. Note: every Java module has a implicit dependency on java.base, so we do not need to include it in the modue-info.java file.


This code create an instance of HttpServer – an object that listens for HTTP Requests at the specified port (80 in this case) and then always returns the same response (the string “This is the response”). As meaningless as that is – the notion of receiving and replying to HTTP Requests in just few lines of Java Code (running in the OpenJDK!) is quite powerful.

package nl.amis.j9demo;
import java.io.*;
import java.net.*;
import java.util.*;
import java.util.concurrent.*;
import java.util.stream.*;
import com.sun.net.httpserver.*;

import static java.lang.System.out;
import static java.net.HttpURLConnection.*;

public class MyDemo{
  private static final int DEFAULT_PORT = 80;
  private static URI ROOT_PATH = URI.create("/"); 

private static class MyHandler implements HttpHandler {
       public void handle(HttpExchange t) throws IOException {
           URI tu = t.getRequestURI();
           InputStream is = t.getRequestBody();
           // .. read the request body
           String response = "This is the response";
           t.sendResponseHeaders(200, response.length());
           OutputStream os = t.getResponseBody();

  public static void main(String[] args) throws IOException {
    HttpServer server = HttpServer.create(new InetSocketAddress(DEFAULT_PORT), 0);
    server.createContext("/apps ", new MyHandler());
    server.setExecutor(null); // creates a default executor
    out.println("HttpServer is started, listening at port "+DEFAULT_PORT);


Compile, Build and Run

Compile the custom module:

javac -d mods –module-source-path src -m nl.amis.j9demo


Create destination directory for JAR file

mkdir -p lib

Create the JAR for the module:

jar –create –file lib/nl-amis-j9demo.jar –main-class nl.amis.j9demo.MyDemo -C mods/nl.amis.j9demo .


Inspect the JAR file:

jar tvf lib/nl-amis-j9demo.jar


To run the Java application- with a reference to the module:

java –p lib/ -m nl.amis.j9demo


the traditional equivalent with a classpath for the JAR file(s) would be:

java -classpath lib/nl-amis-j9demo.jar nl.amis.j9demo.MyDemo

Because port 80 in the container was exposed and mapped to port 8080 on the Docker Host, we can access the Java application from the Docker Host, using wget:



The response from the Java application is hardly meaningful However, the fact that we get a response at all is quite something: the ‘remote’  container based on openjdk:9 has published an HTTP server from our custom module that we can access from the Docker Host with a simple HTTP request.


I tried to use jlink – to create a special runtime for my demo app, consisting of required parts of JDK and my own module. I expect this runtime to be really small.

The JVM modules by the way on my Docker Container are in /docker-java-home/jmods


The command for this:

jlink –output mydemo-runtime –module-path lib:/docker-java-home/jmods –limit-modules nl.amis.j9demo –add-modules nl.amis.j9demo –launcher demorun=nl.amis.j9demo –compress=2 –no-header-files –strip-debug

Unfortunately, on my OpenJDK:9 Docker Image, linking failed with this error:


Error: java.io.UncheckedIOException: java.nio.file.FileSystemException: mydemo-runtime/legal/jdk.httpserver/ASSEMBLY_EXCEPTION: Protocol error


Documentation for jlink – https://docs.oracle.com/javase/9/tools/jlink.htm

JavaDoc for HttpServer package – https://docs.oracle.com/javase/9/docs/api/com/sun/net/httpserver/package-summary.html#

Java9 Modularity Part 1 (article on Medium by Chandrakala) – https://medium.com/@chandra25ms/java9-modularity-part1-a102d85e9676

JavaOne 2017 Keynote – Mark Reynolds demoing jlink – https://youtu.be/UNg9lmk60sg?t=1h35m43s

Exploring Java 9 Modularity – https://www.polidea.com/blog/Exploring-Java-9-Java-Platform-Module-System/

The post Java 9 – First baby steps with Modules and jlink appeared first on AMIS Oracle and Java Blog.

Convert row to matrix and save in table

Tom Kyte - Wed, 2017-10-11 10:26
Good Day I want to ask for your valuable help on a problem that I found, there is a table that stores the information in a single column in Clob format and is copied to another table of a second Oracle Schema, which separates the information with ...
Categories: DBA Blogs

ORA-12560: TNS:protocol adapter error (when running rman backup from scheduler on windows Oracle database)

Tom Kyte - Wed, 2017-10-11 10:26
Hi, I trying to setup a scheduler job on my windows 64bit Oracle database version 12C Below is the script i used where i created OS authentication and create a scheduler to run the backup_script. -- Create a credential so script is run as the co...
Categories: DBA Blogs

Numeric Overflow

Tom Kyte - Wed, 2017-10-11 10:26
I am attempting to Solve some mathematical problems using PL/SQL and I am facing a problem with huge numbers. One such question involves calculating 2 power 1000 (2 power 3 = 2*2*2 => 8). I tried assigning the values to multiple datatypes that I knew...
Categories: DBA Blogs

oracle on linux

Tom Kyte - Wed, 2017-10-11 10:26
i am not well verserd with linux ..i know that we have to write "sqlplus" on the comand prompt in linux to open sql*plus application...is there any place from where i can know how to open other (oracle) applications in linux like "oralce enterprise...
Categories: DBA Blogs

bind variables/ define confusion

Tom Kyte - Wed, 2017-10-11 10:26
Tom, Could you please clarify the DEFINE command in the example below. I am having trouble using the DEFINE command. INPUT: define naming = 'Ivan' insert into emp2 (fname) values (:naming); OUTPUT: Bind variable "NAMING" not declare...
Categories: DBA Blogs

SQLPLUS query output to *.csv or *.txt format

Tom Kyte - Wed, 2017-10-11 10:26
Hello Tom is there anyway to do a query in sql*plus, then have the result output to a file in *.csv or .*.txt format without invoking UTL_FILE, using only sql*plus command. I'm not allowed to creat any procedure. PS: this link "http://asktom.ora...
Categories: DBA Blogs

Grant DBA to yourself - exploit or not?

Pete Finnigan - Wed, 2017-10-11 10:26
Yesterday Peter from the Master of Disaster Blog sent me an email to ask if I had seen the issue in his post before and whether it was a new exploit. I looked at the post and immediately recognised that....[Read More]

Posted by Pete On 11/10/17 At 12:06 PM

Categories: Security Blogs

Quick and clean start with Java 9–running Docker container in VirtualBox VM on Windows 10 courtesy of Vagrant

Amis Blog - Wed, 2017-10-11 08:25

The messages from JavaOne 2017 were loud and clear. Some of these:

  • Java 9 is here,
  • the OpenJDK has all previously exclusive commercial features from the Oracle (fka SUN) JDK – this includes the Java Flight Recorder for real time monitoring/metrics gathering and analysis,
  • Java 9 will be succeeded by Java 18.3, 18.9 and so on (a six month cadence) with much quicker evolution with continued quality and stability
  • JigSaw is finally here; it powers the coming evolution of Java and the platform and it allows us to create fine tuned, tailor more Java runtime environments that may take less than 10-20% of the full blown JRE
  • Java 9 has many cool and valuable features besides the Modularity of JigSaw – features that make programming easier, more elegant more fun more lightweight etc.
  • One of the objectives is “Java First, Java Always” (instead of: when web companies mature, then they switch to Java) (having Java enabled for cloud, microsevice and serverless is an important step in this)

    Note: during the JavaOne Keynote, Spotify presented a great example of this pattern: they have a microservices architecture (from before it was called microservice); most were originally created in Python, with the exception of the search capability; due to scalability challenges, all Python based microservices have been migrated to Java over the years. The original search service is still around. Java not only scales very well and has the largest pool of developers to draw from, it also provides great run time insight into what is going on in the JVM

I have played around a little with Java 9 but now that is out in the open (and I have started working on a fresh new laptop – Windows 10) I thought I should give it another try. In this article I will describe the steps I took from a non Java enabled Windows environment to playing with Java 9 in jshell – in an isolated container, created and started without any programming, installation or configuration. I used Vagrant and VirtualBox – both were installed on my laptop prior to the exercise described in this article. Vagrant in turn used Docker and downloaded the OpenJDK Docker image for Java 9 on top of Alpine Linux. All of that was hidden from view.

The steps:

0. Preparation – install VirtualBox and Vagrant

1. Create Vagrant file – configured to provide a VirtualBox image (based on Ubuntu Linux) and provision the Docker host on that image as well as a Docker Container with OpenJDK 9

2. Run Vagrant for that Vagrant file to have it spin up the VirtualBox, install Docker into it, pull the OpenJDK image and run the container

3. Connect into VirtualBox Docker Host and Docker Container

4. Run jshell command line and try out some Java 9 statements

In more detail:

1. Create Vagrant file

In a new directory, create a file called Vagrantfile – no extension. The file has the following content:

It is configured to provide a VirtualBox image (based on Ubuntu Linux) and provision the Docker host on that VB image as well as a Docker Container based on the OpenJDK:9 image.


Vagrant.configure("2") do |config|
config.vm.provision "docker" do |d|
    d.run "j9",
      image: "openjdk:9",
      cmd: "/bin/sh",
      args: "-v '/vagrant:/var/www'"
    d.remains_running = true  
# The following line terminates all ssh connections. Therefore Vagrant will be forced to reconnect.
# That's a workaround to have the docker command in the PATH
# Command: "docker" "ps" "-a" "-q" "--no-trunc"
# without it, I run into this error:
# Stderr: Get http:///var/run/docker.sock/v1.19/containers/json?all=1: dial unix /var/run/docker.sock: permission denied.
# Are you trying to connect to a TLS-enabled daemon without TLS?
config.vm.provision "shell", inline:
"ps aux | grep 'sshd:' | awk '{print $2}' | xargs kill"
config.vm.define "dockerhostvm"
config.vm.box = "ubuntu/trusty64"
config.vm.network "private_network", ip: ""
config.vm.provider :virtualbox do |vb|
  vb.name = "dockerhostvm"
  vb.memory = 4096
  vb.cpus = 2
  vb.customize ["modifyvm", :id, "--natdnshostresolver1", "on"]
  vb.customize ["modifyvm", :id, "--natdnsproxy1", "on"]

# to get into running container: 
# vagrant ssh
# docker run -it  -v /vagrant:/var/www openjdk:9 /bin/sh
2. Run Vagrant for that Vagrant file

And have it spin up the VirtualBox, install Docker into it, pull the OpenJDK image and run the container:


3. Connect into VirtualBox Docker Host and Docker Container


vagrant ssh

to connect into the VirtualBox Ubuntu Host and

docker run –it openjdk:9 /bin/sh

to run a container and connect into the shell command line, we get to the environment primed for running Java 9:


At this point, I should also be able to use docker exec to get into the container that started by the Vagrant Docker provisioning configuration. However, I had some unresolved issues with that – the container kept restarting. I will attempt to resolve that issue.

4. Run jshell command line and try out some Java 9 statements

JShell is the new Java command line tool that allows REPL style exploration – somewhat similar to for example Python and JavaScript (and even SQL*Plus).

Here is an example of some JShell interaction:


I tried to use the new simple syntax for creating collections from static data. Here I got the syntax right:


It took me a little time to find out the exit strategy. Turns out that /exit does that trick:


In summary: spinning up a clean, isolated environment in which to try out Java is not hard at all. On Linux – with Docker running natively – it is even simpler, although even then using Vagrant may be beneficial. On Windows it is also quite straightforward – no complex sys admin stuff required and hardly any command line things either. And that is something we developers should start to master – if we do not do so already.

Issue with Docker Provider in Vagrant

Note: I did not succeed in using the Docker provider (instead of the provisioner) with Vagrant. Attempting that (cleaner) approach failed with “Bringing machine ‘j9’ up with ‘docker’ provider…
The executable ‘docker’ Vagrant is trying to run was not
found in the %PATH% variable. This is an error. Please verify
this software is installed and on the path.” I have looked across the internet, found similar reports but did not find a solutio that worked for me.


The provider is documented here: https://www.vagrantup.com/docs/docker/

The Vagrantfile I tried to use originally – but was unable to get to work:


(based on my own previous article: https://technology.amis.nl/2015/08/22/first-steps-with-provisioning-of-docker-containers-using-vagrant-as-provider/)

The post Quick and clean start with Java 9–running Docker container in VirtualBox VM on Windows 10 courtesy of Vagrant appeared first on AMIS Oracle and Java Blog.

In memoriam – 3

Jonathan Lewis - Wed, 2017-10-11 07:30

My father-in-law died a few weeks ago, aged 95. This is the story that he wrote for his children and grandchildren a few years ago describing his experiences as a Naval engineer on the aircraft carrier HMS Indefatigable during the second world war.


When war broke out on 3rd September 1939 I wanted to join the Navy, and a few days later I saw a  new recruiting office near Southend Pier so I went in and asked how I would be able to join. A Petty Officer looked at me and said “Well, sonny, you will have to wait until you are 18”. I was then only 17 so I continued with my plan to become an engineer. In those days parents either had to pay the full cost of university education or rely on their children gaining scholarships. In my case scholarships were essential. So, concentrating on mathematics, I took Higher School Certificate (A-Levels) in July 1938 and July 1939, but did not gain any scholarships. At that time I was Head Boy at Lindisfarne College and in late September the school was evacuated to North Wales from the Southend area because of fears of bombing and invasion but here the buildings were not well equipped and there was no laboratory. However, the Southend High School remained at Southend and arrangements were made to transfer me there.

In December 1939 I was awarded a Scholarship at Queens’ College, Cambridge. Then in May 1940 when the German blitzkrieg started the High School was evacuated to Mansfield in the Midlands, but there I took the HSC again and as a result gained a State Scholarship and a Southend Borough Major Scholarship, which in total was enough to see me through Cambridge. There I made friends with Denis Campbell, Stuart Glass and Edward Higham. In addition to lectures we went regularly to tutorials with a great character called (Professor) Archie Browne. He had additional duties as Steward of the College, and was responsible for obtaining food supplies and coal for heating, which was very difficult in wartime.

The course was completed in two years and, with blackouts, air raid precautions and other restrictions, social life was limited. I joined the Naval Section of the Cadet Corps and the Home Guard which took up one or two afternoons each week. I remember one exercise where we had to make a mock attack by night on an airfield some ten miles north of Cambridge. The defenders somehow knew that we would attack the SE corner and mustered there, but we made a mistake and went for the NE corner which was undefended, so we theoretically captured that bit of airfield! We had to march there and back, and the blisters lasted for weeks. On another exercise Cambridge was attacked by the Welch Fusiliers, I remember being knocked on the head and falling into a ditch half full of water. I was considered a casualty and allowed to return to college for a hot bath.

July – September 1942         I applied to join the Royal Navy as an engineer officer and had interviews at the Admiralty including medical examinations. As a result I was accepted and appointed a Probationary Temporary Acting Sub.Lieutenant (E) RNVR, and the next step was to purchase my uniform at Gieves in London, including the purple stripe denoting engineering.

October 1942         I reported to Portsmouth Barracks for four weeks training. I wore my uniform for the first time at Warminster in Wiltshire where we were living, and traveled to Portsmouth without any knowledge of how to make or receive naval salutes in public! This was soon rectified at Portsmouth where I joined twenty other trainees for the course which included instruction in naval customs and traditions, rules and regulations, security, and the all-embracing Kings Regulations and Admiralty Instructions. We also had training in small arms firing and endless square bashing under the eagle eye of Chief Petty Officer Sims, who was as tough as old boots.

November 1942 – November 1943         I was posted to John Brown’s Engineering Works at Clydebank with Donald Townend and Ian Richardson for practical marine engineering training. John Brown’s was a huge organisation which built engines as well as ships, and just after we arrived Indefatigable was launched. This was an amazing sight, seeing 30,000 tons of ship slide down the slipway into the river Clyde. Before the war the Queen Mary and the Queen Elizabeth were built on the same slipway.

The three of us were billeted with two or three other naval officers in lodgings at Glasgow where we three shared a room and were looked after by a homely landlady and her staff. Every morning we put on civilian clothes and caught a rickety old tram for a 30 minute journey to Clyde bank. There we worked successively in the Pattern-shop (making wood moulds), Foundry, Boiler-shop (being deafened by riveting), Machine-shop, Fitting-shop, Pipe-shop, Drawing Office and Dockyard. We did actually work, scraping bearings, operating lathes, casting metal, always under the supervision of an experienced workman. During lunch hours we used to climb over Indefat, deafened again by riveting, but we got to know the ship. At that time the yard was completing R class destroyers at the rate of about one every fortnight, and we used to take part in their initial sea trials so gaining experience of firing up boilers and operating turbine plant.

During the summer of 1943 we got to know the permanent RN engineer officers appointed to supervise the fitting out of the ship, including Peter Sandison who looked after the flight deck gear. We were seconded to help the checking of the installation and testing of all kinds of machinery, and in November I was chosen to be officially appointed to Indefat, while the other two went off to other ships.

December 1943 – February 1944         The ship was commissioned on 8th December and taken over by the RN from the yard. After dock trials we steamed down the Clyde and carried out various trials including full power of the 148,000 HP engines, the measured mile speed test (32 knots) off the Isle of Arran, and steering and going astern trials from full ahead. I remember on one occasion the steering gear locked solid at hard-a-starboard while doing full speed. We went round in circles flying two black balls showing we were out of control! Several weeks were spent commissioning and training the crew, taking on stores and ammunition, gunnery practice, testing of radar and flight deck equipment, while some time was spent at sea.

March – June 1944         The first aircraft flew in on 23rd March, and thereafter the squadrons began to arrive. We spent days at sea practising aircraft landings and by the end of June we had a complement of some 75 aircraft including Seafires, Fireflies and Hellcats. When at sea engineer officers kept watch for four hours at a time, the middle watch (midnight to 4 am) and the morning watch (4 am to 8 am) being the worst. During a watch we had to visit each engine room and boiler room, and altogether a total of seventeen machinery spaces where each involved climbing up and down three sets of ladders, as the only passage was via the main deck. The best visit was always to a boiler room, where the Chief Stoker would provide a mug of ‘kai’, a chocolate slab heated in hot water and steam.

In addition each officer had responsibility for a department which included the operation and maintenance of all the equipment in it, and the men carrying out this work. Over the years mine included seven steam generators supplying electricity to the ship, three emergency diesel generators, motor boats, steering gear and auxiliary machinery including the big evaporators for making the ship’s fresh water from seawater. Also every six months each officer took it in turn to run the ship’s laundry for 2000 crew!

At Action Stations if not on watch each engineer officer had a Damage Control section of the ship to look after. Mine was the midships section above one of the engine rooms, and my team consisted of about ten stokers and technicians. We might be stationed there all day with only sandwiches and ‘Spotted Dick’ for lunch!

July – October 1944         Indefat joined the Fleet at Scapa Flow surrounded by battleships, cruisers and destroyers, and spent much time at sea on Russian convoy escort duty going beyond the Arctic Circle. In July we made an attack on the largest German battleship Tirpitz, which was moored in a Norwegian fiord and was always a potential menace to Russian convoys. This operation was called MASCOT and with two other carriers the aircraft carrying out the attack included 44 Barracudas, 18 Hellcats and 12 Fireflies, supported by many Seafires as fighter escorts. The weather was not good with cloud and fog around and although the Tirpitz was damaged it was decided to make another attack in mid-August. Prior to that strikes were made against some installations on the Norwegian coast and then on 18th August we sailed for the second Tirpitz attack called operation GOODWOOD. At this time a valuable convoy was en route to Russia and our job was to protect it from the Tirpitz and Uboats. The convoy did arrive safely.

Indefat aircraft included 12 Barracudas, 12 Fireflies, 12 Hellcats and 32 Seafires, and the ship was accompanied by Formidable, Furious and two small escort carriers, together with destroyers. On the first day one escort carrier was torpedoed and badly damaged, and had to return to Scapa escorted by the second small carrier. Some time later a destroyer was torpedoed and sank, with few survivors. The operation lasted for seven days with the ship at Action Stations most of the time. At one point Indefat seemed to be under serious attack by Uboats, with the ship taking evasive action and shaken by exploding depth charges from nearby destroyers, while it was reported that one torpedo passed under Indefat. GOODWOOD was successful as Tirpitz was hit several times and had to be moved to the port of Tromso for repairs, where she was later sunk by the RAF with their 10 ton Tallboy bombs. Had she remained in the narrow fiord in the lee of the mountains protected by smokescreens they might never have hit her.

Above the Arctic Circle the sun at this time only went below the horizon for a short time, which meant that our ships could be continually kept under observation by German aircraft and Uboats. There were however some fascinating panoramas of sea and sky, and I remember that one evening the ship had to steam into the wind straight for the coast and the spectacular black rugged mountains of Norway loomed up ahead. I vowed that one day I would revisit the area, and so I did with Joan during our Norwegian cruise of 1987.

Our base was Scapa Flow where we returned every few days. Occasionally we went ashore and the main treat was a visit to the NAAFI canteen which provided a large dish of bacon and baked beans. Otherwise we spent time in the wardroom eating, drinking and playing shove ha’penny or bar skittles. One day we played hockey against a team of large and ruthless Wrens, who beat us using their sticks with wild abandon.

In July more engineer officers joined the ship and I knew that one of them would occupy the vacant berth in my double cabin. I anxiously watched them come aboard and liked the look of Brian, and was very glad when he was allocated to my cabin. Then began a friendship which has lasted all our lives.

October – November 1944         We returned to Clydebank in October and made preparations for going to the Far East. Then we steamed down to Portsmouth and went into dry dock for maintenance and cleaning the ship’s bottom. After this we were ready for sailing but before doing so on 16th November the King and the Royal Family came aboard to wish us Good Luck. We were all mustered in our divisions on the flight deck, the King inspected us and then asked for a cup of tea. This caused a flap as all the cooks and stewards were mustered, and it took the duty officer nearly half an hour to find some tea and make it!

December 1944         After leaving Portsmouth we sailed to Ceylon, passing through Gibraltar, the Mediterranean, the Suez Canal, and then across the Indian Ocean arriving at Colombo on 10th December. We stopped off Algiers where our Mess Secretary went ashore and triumphantly came back with a large load of Algerian wine, which turned out to be the most awful plonk! We had Admiral Vian, the fighting Admiral, on board and at Colombo he demanded to be ferried ashore immediately in his Admiral’s Barge. This motor boat arrived on board at Portsmouth just before we sailed and was stowed in one of the hangars, where the engine could not be tested. I was in charge of boats and I insisted that the boat should have a trial run before an official trip. The Admiral was furious and came storming down the Hight deck demanding an explanation, so I stood to attention quaking in my shoes and gave one. He looked me up and down and said “Right, I will give you ten minutes”. Luckily all went well. Strange how one remembers these things!

During the remainder of the month we spent time at Colombo or Trincomalee storing ship, or at sea exercising with other ships of the Fleet. Trincomalee was a beautiful harbour, and I remember Brian and I were thrilled to bring back a pineapple (which we hadn’t seen for years) to our cabin, but when with due ceremony we slit it open it was full of insects!

January 1945         On New Year’s Day we sailed in company with three other carriers, the battleship King George V, and several cruisers and destroyers for air strikes against the Japanese oil refineries at Palembang in Sumatra. The first strike took place on 4 January and about 100 aircraft took part plus 40 Seafires which provided fighter cover. The refineries were damaged but after returning to Trincomalee it w as decided that further strikes would be carried out and they took place on 24 and 29 January. These were major strikes carried out by 144 aircraft for the first and 128 for the second, plus the usual fighter cover. This time the Japanese were well prepared and on several occasions the Fleet came under attack by enemy aircraft. These were fought off by our guns and aircraft, two being shot down close to Indefat. There were many air battles and we lost 41 aircraft together with many of the aircrews. This included several aircraft that were damaged by enemy action and then crashed on deck landing. The worst event was the fate of nine aircrew survivors who had to force land in Sumatra, were made prisoners, taken to Singapore and then later beheaded. The strikes were successful as the refineries produced some 50 of Japanese oil requirements and they were reduced to a standstill, only increasing back to one third capacity by the end of March. After this we steamed south for Australia and crossed the line (the Equator) with King Neptune and his cohorts “coming aboard” on 1st February. I was duly ducked and scrubbed m a makeshift swimming pool.

February 1945         We called in at Fremantle and six days later arrived in Sydney and moored at Wooloomooloo near the Harbour Bridge. The Australians were very hospitable and Brian, Colin and I were “adopted” by the Murray-Jones family with two daughters, Judy and Annabel. They would invite us home for a meal or arrange some tennis or swimming, not that there was much time as we were busy with maintenance and storing for the Pacific. Towards the end of the month we steamed north with the British Pacific Fleet under Admiral Rawlings.

March 1945         After 11 days at sea we arrived at the island of Manus and then went on to Ulithi, another island. This had an enormous harbour and was full of American ships, a total of about 1,400 preparing for the invasion of Japan. Our Fleet then became Task Force 57 operating with the American 3rd Fleet under Admiral Spruance, and consisted of three other Fleet carriers, eleven destroyers and a number of support ships including sloops, frigates, minesweepers, oil tankers and hospital ships. Sailing from Ulithi our first strike took place on 26th March against some of the Japanese islands south of Okinawa where it was estimated that the Japanese had 10,000 aircraft, of which about 4,000 were suicide bombers called Kamikazes.

Then began a series of strike days, each being a long day’s activity for the Fleet, particularly for the ships’ companies of the aircraft carriers. We would go to Action Stations at 0600 and return to Defence Stations at 2000, and periodically a “Flash Red” warning would be broadcast when enemy aircraft approached. Several air battles took place and, throughout the day, the Fleet wheeled and turned in and out of the wind for the carriers to land on and fly off strikes and fighter escorts. When the last aircraft landed on at dusk the air engineering department worked all night to repair, re-arm, and refuel aircraft ready for the next day.

April 1945         On the morning of 1 April we were hit by a Kamikaze which exploded into the flight deck and bridge structure. Because the flight deck had 3″ armour plate the damage was not catastrophic but fourteen of the crew, including the ship’s doctor, were killed and there was a lot of damage to the flight deck barrier gear and bridge communications. I was Damage Control Officer for the area and my team had to remove the casualties and start repairing the damage. I remember the whole area was flooded with hot steam, as the steam-to-ships siren pipework was broken, until I managed to telephone Y boiler room to shut off the master valve.

Peter Sandison’s team did a good job to repair arrester wires and barriers, and the ship was flying off aircraft an hour later, much to the amazement of the American ships and Admirals. The American carriers with light steel decks were very vulnerable and many of their carriers were sunk or badly damaged due to Kamikazes. On 6 and 7 April the Japanese made massive attacks on allied ships with most of them concentrated on American ships to the north of our Fleet. These attacks were made by 600 aircraft, including 355 Kamikazes, and some 380 were shot down but six American ships were sunk and twenty-one damaged. At this time the giant Japanese battleship Yamato came out on a suicide mission and was sunk by American torpedo bombers with a loss of 2,100 men.

Operations continued until the last week of April when our Fleet returned to Leyte island for refitting and oiling, having been at sea continuously for 32 days. By this time sixty support ships had arrived to provide repair and maintenance facilities. During the month I was promoted to Temporary Lieutenant (E) RN and wore my second stripe.

May 1945         On 1st May the Fleet including the carriers Indefatigable, Implacable. Indomitable, Formidable and Victorious left Leyte to resume operations against the Japanese shipping and shore installations, with Action Stations every day except for the odd day when we retired for refuelling by waiting tankers. British ships were essentially designed for Atlantic operations, and consequently there was very little air conditioning to deal with the hot climate of the Pacific, Some of the machinery spaces reached temperatures of 1400 F and almost every day one’s boiler suit could be twice soaked with perspiration. After a few weeks one would suffer from prickly heat and would be painted with purple potassium permanganate, so looking like an Ancient Briton! Sleeping at night on the quarter-deck was the most comfortable time. Food was almost all dehydrated or tinned and a staple of the diet of dehydrated potato served in a variety of ways – mashed, cubed, boiled, roast or fried. There were also plenty of tins of egg powder and powdered milk!

On 4 May Formidable was hit by a Kamikaze which caused considerable damage and fires on the flight deck but the ship remained operational. Indomitable was nearly hit by another Kamikaze which was shot down and crashed some thirty feet off the starboard bow. A few days later Formidable was again hit and fires were started in the hangar, and nine aircraft were destroyed. All through this period the enemy pressed home their attacks with great skill and determination, making good use of cloud cover, decoys and variations of height. All five carriers were hit at least once by Kamikazes, but nevertheless our aircraft flew some 2500 sorties, dropped over 500 tons of bombs and destroyed about 60 aircraft, at a loss of 98 aircraft.

June 1945         At the beginning of June we returned to Sydney for vital boiler maintenance, aircraft repairs and other general refitting. This was a welcome relief after 100 days on the ship at sea and again the Murray Jones were very hospitable, so we enjoyed some tennis and swimming off Bondi Beach. Towards the end of June the Fleet sailed north again and resumed operations in co-operation with the American Third Fleet.

July – August 1945         We carried out strikes against the Japanese mainland for the first time, including airfields and installations in the Tokyo area. The routine developed of 4 or 5 days at Action Stations, then a day’s withdrawal for refuelling, and then back again for more strikes. It was a time of Action Stations, watch-keeping, eating and sleeping in a noisy, hot and tiring atmosphere, with some excitement when enemy aircraft appeared. The Flight Deck was again busy from dawn to dusk, sending off bombers and also fighters to protect the Fleet. Unfortunately many did not return, and several had accidents when landing back on. At this stage the whole of the Japanese mainland from north to south was under attack by allied ships, with the Americans concentrating on destroying the remnants of the Japanese navy. The British aircraft bombed industrial targets including shipping, oil storage tanks, railways and factories, and on two occasions the battleship King George V carried out extensive bombardment with her heavy guns.

On 4th August all ships were ordered to withdraw some 300 miles from Japan, and on the 6th the first atomic bomb was dropped on Hiroshima, and then the second on Nagasaki. Further strikes continued until the Japanese finally surrendered on 15th August. The Fleet remained at sea but on the 25th we were hit by a typhoon. The waves were awesome, I remember standing on the flight deck which was 70 ft above normal sea level, and watching waves much higher than this coming towards me. The ship was rolling 35° from one side to the other, but we survived. Three American destroyers capsized, and we saw one American carrier with a large part of its flight deck hanging over its bows, as though it had received a punch on the nose!

September 1945         The Japanese Surrender was signed on the USS Missouri in Tokyo Bay on 2nd September, much to our relief. We remained at sea, and with the American Fleet took part in an enormous “parade” of ships outside Tokyo Bay. Then we spent three days in the Bay, while some of our crew went ashore to find and collect prisoners of war and transport them to hospital ships. The famous Mount Fuji is usually covered in cloud but early one morning the tannoy broadcast that it was visible, and I remember a marvellous view of its snow-capped peak.

After this we steamed back to Sydney arriving towards the end of the month, ready for a respite after 73 days of sea time. It was time to reflect on past events, the worst being during July and August when the Fleet lost over 140 aircraft from all causes, by enemy action or deck landings. Since then there has been a lot of discussion about dropping the atomic bombs and their consequences, but to my mind the following reasons justified the decision.

  1. The Americans estimated that there would be around a half-million Allied casualties if the invasion of Japan had taken place later in the year. This did not happen.
  2. About 40,000 British and Allied prisoners of war were kept by the Japanese in horrendous conditions and most would probably not have survived another winter. They were rescued.
  3. The Japanese had some five thousand aircraft and pilots trained as Kamikazes to be used against an invasion fleet, and we would have been in the forefront of this.

October – December 1945         Indefat remained in Sydney and the crew were allowed a lot of shore leave. The Murray-Jones thoughtfully provided a flat where many of us could stay, including Brian, Colin, Peter Fanghanel and others. One highlight was when all the latter including me made up a party to go ski-ing for a week at Mount Kosciusco. We arrived at the snowline and were then told that the chalet was 12 miles away and could only be reached on skis. Some of us, including me, had not skied before but we were told “Oh, that’s OK, today is Tuesday, and there is a tractor going up on Thursday which could pick you up if you are stuck”!

This was a time of hard work and playas supplies were exhausted, the engines needed refitting, the ship needed cleaning and the typhoon had damaged part of the hull so the ship had to go into dry dock. Some of us were seconded to the dockyard to help out with various jobs and I enjoyed the use of a 500cc motor-bike.

We managed another five days on a sheep farm, again with Brian and Colin. The farm was enormous and the family relied on horses to get around. On the first day we were each provided with a horse, but I viewed this with trepidation. So evidently did the horse, as after 15 minutes he turned round and trotted home, and there was nothing I or anyone else could do to stop him! I decided that I would stick to something with a brake and throttle.

January – March 1946         On 20th January we left Sydney for the journey home. Three days later we arrived at Melbourne where we had a tremendous welcome, with a parade led by the Royal Marines hand marching through the streets to the City Hall where the Governor took the salute accompanied by Admiral Vian. We stayed a week and were well entertained, then steamed across the Australian Bight which was unpleasantly rough to call in at Fremantle for a few hours before setting off for Capetown.

We arrived at Capetown after 17 days at sea. Again we were well looked after with a reception at the Governor’s Residence and an expedition to Table Mountain. This was the highlight of the visit, we took the cable car to the top with marvellous views all round and then came all the way down on foot. On 24th February we left Capetown, arriving at Gibraltar on 11 th March. On the way we passed close to St. Helena and Ascension Island. The Duty Officer went ashore and paid his respects to the Governor, who presented him with a live turtle to make soup! The ship’s butcher did not think much of this so when we left the turtle was returned to the sea, and was last seen swimming happily to the shore.

This part of the trip was pleasant and not too hot, every day there were games of deck hockey on the flight deck using a rope grommet instead of a ball. At Gibraltar we stayed for one day and Peter Fanghanel was the only one of our group who managed to go ashore, he came back with a large case of Tio Pepe sherry.

Finally we arrived at Portsmouth on 16th March and berthed inside the harbour, with crowds lining the Southsea promenade and cheering as we went in. We engineers saw little of this, hut we looked forward to a pint at the St. Enoch’s Hotel and then some leave. I think I had about ten days at Westcliff with Mother and Brenda, it was good to see them again after nearly 2½years.

April – October 1946         The ship sailed again on 25th April with 130 “Bush Brides”, who were brides of Australian servicemen and were going to join their husbands to live in Australia. The voyage again was through the Mediterranean and then a brief stay at Aden. Brian, Colin and I went ashore and we asked some joker the way to the local Club for a drink. “Oh” he said “lt’s that white building up on the hill”. So we trudged up the hill, knocked on the front door which was opened by a smart servant who asked what we wanted. We said we would like a drink, to which he replied that this was the Consul’s Residence. Anyway, the Consul was very decent, gave us more than one drink and we went happily back to the ship.

We arrived back in Sydney on 25th May and left again on the 9th June with over 1,000 service personnel due to be demobilised, including some RAF. We also carried 65 tons of food for Britain and about 18,000 gift parcels of food. From Fremantle the engines worked at full power and lndefat made a record-breaking non-stop trip of 21 days to Portsmouth. Then on 29th July we sailed again to Colombo and repatriated another large number of service personnel. The highlight I remember was a visit to Kandy and the Temple of the Sacred Tooth, where we were guided by Buddhist priests in their saffron yellow robes.

The last major event was a parade by the ship’s company on 19th September through Holborn in London, the borough that had “adopted” us during the war. As one of the officers with the longest-serving time in Indefat I was placed in the front rank, and there is a photo in our album. After the march we were inspected by the Mayor and then had a luncheon in the Town Hall, where our Battle Ensign flown by Indefat during Action Stations was presented to be hung in the Council Chamber. The demobilisation process was slow, but I finally left the ship and the Navy on 1st October 1946, after a wardroom party the night before! I well remember going down the gangway, walking through Portsmouth Dockyard and then out through the Main Gate, ready to face a different kind of life and world.

Use Bit to represent groups

Dylan's BI Notes - Wed, 2017-10-11 03:17
Here I am providing an alternate approach of supporting group membership in MySQL. It is a common seen requirement that a group may have multiple members and a person may be added to multiple groups.  This many to many relationship is typically modeled in an intersection table. When the group membership is being used as […]
Categories: BI & Warehousing

Oracle Database Multilingual Engine (MLE)

Yann Neuhaus - Wed, 2017-10-11 01:35

My ODC appreciation blog post was about Javascript in the database running in the beta of the Oracle Database Multilingual Engine (MLE). Here I’ll detail my first test which is a comparison, in performance, between a package written in Javascript, running in the MLE, and one written and running in PL/SQL.

I’ve downloaded the 12GB .ova from OTN, installed the latest SQLcl, and I’m ready to load my first Javascript procedure. I want something simple that I can run a lot of times because I want to test my main concern when running code in a different engine: the context switch between the SQL engine and the procedural one.

My kid’s maths exercises were about GCD (greatest common divisor) this week-end so I grabbed the Euclid’s algorithm in Javascript. This algorithm was the first program I ever wrote long time ago, on ZX-81, in BASIC. Now in Javascript it can use recursion. So here is my gcd.js file:

module.exports.gcd = function (a, b) {
function gcd(a, b) {
if (b == 0)
{return a}
{return gcd(b, a % b)}
return gcd(a,b)

We need strong typing to be able to load it as a stored procedure, so here is the TypeScript definition in gcd.d.ts

export function gcd(a:number, b:number ) : number;

I load it with the dbjs utility, which I run in verbose mode:

[oracle@dbml MLE]$ dbjs deploy -vv gcd.js -u demo -p demo -c //localhost:1521/DBML
deploy: command called /media/sf_share/MLE/gcd.js oracle
Oracle backend: starting transpiler
gcd: processed function
Oracle backend: opening connection to database
gcd.js: retrieving functions
dropModule: called with gcd.js
loadModule: called with gcd.js
: generated PLSQL
+ gcd.js
└─┬ gcd

As it is mentioned in the verbose log, the Javascript code is transpiled. My guess is that the Javascript is parsed by the Oracle Truffle framework and compiled by Oracle GaalVM. More info in the One VM to Rule Them All paper.

This has loaded the package, the library and an ‘undefined’ object of type 144 (this MLE is in beta so not all dictionary views have been updated):

SQL> select * from dba_objects where owner='DEMO';
----- ----------- -------------- --------- -------------- ----------- ------- ------------- --------- ------ --------- --------- --------- --------- ------------ ------- ----------- -----------------
DEMO GCD 93427 PACKAGE 09-OCT-2017 15:29:33 09-OCT-2017 15:29:33 2017-10-09:15:29:33 VALID N N N 1 NONE Y N
DEMO gcd.js 93426 LIBRARY 09-OCT-2017 15:29:33 09-OCT-2017 15:29:33 2017-10-09:15:29:33 VALID N N N 1 NONE Y N
DEMO gcd.js 93425 UNDEFINED 09-OCT-2017 15:29:33 09-OCT-2017 15:29:33 2017-10-09:15:29:33 VALID N N N 129 NONE N
SQL> select * from sys.obj$ where obj# in (select object_id from dba_objects where owner='DEMO');
---- -------- ------ ---- --------- ------- ----- ----- ----- ----- ------ ----------- -------- ----- ---- ------ ------ ------ ------ ------ ------ --------- ------ ------ ------
93427 284 GCD 1 9 09-OCT-2017 15:29:33 09-OCT-2017 15:29:33 09-OCT-2017 15:29:33 1 0 6 65535 284 51713CBD7509C7BDA23B4805C3E662DF 0 0 0
93426 284 gcd.js 1 22 09-OCT-2017 15:29:33 09-OCT-2017 15:29:33 09-OCT-2017 15:29:33 1 0 6 65535 284 8ABC0DDB16E96DC9586A7738071548F0 0 0 0
93425 284 gcd.js 129 144 09-OCT-2017 15:29:33 09-OCT-2017 15:29:33 09-OCT-2017 15:29:33 1 0 6 65535 284 0 0 0

MLE Javascript

So, I’ve executed the function multiple times for each one of 10 millions rows:

SQL> select distinct gcd(rownum,rownum+1),gcd(rownum,rownum+2),gcd(rownum,rownum+3) from xmltable('1 to 10000000');
Elapsed: 00:00:17.64

The execution on 30 million took 17 seconds

PL/SQL function

In order to compare, I’ve created the same in PL/SQL:

SQL> create or replace function gcd_pl(a number, b number) return number as
2 function gcd(a number, b number) return number is
3 begin
4 if b = 0 then
5 return a;
6 else
7 return gcd_pl.gcd(b,mod(a,b));
8 end if;
9 end;
10 begin
11 return gcd_pl.gcd(a,b);
12 end;
13 /

Here is the execution:

SQL> select distinct gcd_pl(rownum,rownum+1),gcd_pl(rownum,rownum+2),gcd_pl(rownum,rownum+3) from xmltable('1 to 10000000');
Elapsed: 00:01:21.05

PL/SQL UDF function

In 12c we can declare a function with the pragma UDF so that it is optimized for calling from SQL

SQL> create or replace function gcd_pl_udf(a number, b number) return number as
2 pragma UDF;
3 function gcd(a number, b number) return number is
4 begin
5 if b = 0 then
6 return a;
7 else
8 return gcd_pl_udf.gcd(b,mod(a,b));
9 end if;
10 end;
11 begin
12 return gcd_pl_udf.gcd(a,b);
13 end;
14 /

Here is the execution:

SQL> select distinct gcd_pl_udf(rownum,rownum+1),gcd_pl_udf(rownum,rownum+2),gcd_pl_udf(rownum,rownum+3) from xmltable('1 to 10000000');
Elapsed: 00:00:51.85

Native compilation

We can also improve PL/SQL runtime by compiling it in native, rather than being interpreted on p-code

SQL> alter session set plsql_code_type=native;
Session altered.
SQL> alter function gcd_pl_udf compile;
Function altered.
SQL> alter function gcd_pl compile;
Function altered.

and here is the result:

SQL> select distinct gcd_pl_udf(rownum,rownum+1),gcd_pl_udf(rownum,rownum+2),gcd_pl_udf(rownum,rownum+3) from xmltable('1 to 10000000');
Elapsed: 00:01:10.31
SQL> select distinct gcd_pl_udf(rownum,rownum+1),gcd_pl_udf(rownum,rownum+2),gcd_pl_udf(rownum,rownum+3) from xmltable('1 to 10000000');
Elapsed: 00:00:45.54

Inline PL/SQL

Finally, similar to an UDF function, we can declare the function in the query, inlined in a WITH clause:

SQL> with function gcd_pl_in(a number, b number) return number as
2 function gcd(a number, b number) return number is
3 begin
4 if b = 0 then
5 return a;
6 else
7 return gcd(b,mod(a,b));
8 end if;
9 end;
10 begin
11 return gcd(a,b);
12 end;
13 select distinct gcd_pl_in(rownum,rownum+1),gcd_pl_in(rownum,rownum+2),gcd_pl_in(rownum,rownum+3) from xmltable('1 to 10000000')
14 /

And here is the result:

Elapsed: 00:00:48.92

Elapsed time summary

Here is a recap of the elapsed time:

Elapsed: 00:00:17.64 for MLE Javascript
Elapsed: 00:00:45.54 for PL/SQL UDF function (native)
Elapsed: 00:00:48.92 for Inline PL/SQL
Elapsed: 00:00:51.85 for PL/SQL UDF function (interpreted)
Elapsed: 00:01:10.31 for PL/SQL function (native)
Elapsed: 00:01:21.05 for PL/SQL function (interpreted)

The top winner is Javascript!

Perfstat Flame Graph

My tests were deliberately doing something we should avoid for performance and scalability: call a function for each row, because this involves a lot of time spent in switching the context between the SQL and the procedural engine. But this is however good for code maintainability. This overhead is not easy to measure from the database. We can look at the call stack to see what happens when the process is evaluating the operand (evaopn2) and switches to PL/SQL (evapls), and what happens besides running the PL/SQL itself (pfrrun). I have recorded perf-stat for the cases above to display the Flame Graph on the call stack. When looking for more information I remembered that Frits Hoogland already did that so I let you read Frits part1 and part2

You can download my Flame Graphs and here is a summary of .svg name and call stack from operand evaluation to PL/SQL run:

PL/SQL UDF function (native) perf-gcd_pl_UDF_native.svg evaopn2>evapls>peidxrex>penrun
Inline PL/SQL perf-gcd_pl_inline.svg evaopn2>evapls>kkxmss_speedy_stub>peidxrex>pfrrun>pfrrun_no_tool
PL/SQL UDF function (interpreted) perf-gcd_pl_UDF_interpreted.svg evaopn2>evapls>peidxexe>pfrrun>pfrrun_no_tool
PL/SQL function (native) perf-gcd_pl_native.svg evaopn2>evapls>kgmexec>kkxmpexe>kkxdexe>peidxexe>peidxr_run>plsql_run>penrun
PL/SQL function (interpreted) perf-gcd_pl_interpreted.svg evaopn2>evapls>kgmexec>kkxmpexe>kkxdexe>peidxexe>peidxr_run>plsql_run>pfrrun>pfrrun_no_tool

But more interesting is the Flame Graph for the JavaScript execution:

My interpretation on this is limited but I don’t see a stack of context switching function before calling the MLE engine, which is probably the reason why it is fast. Besides the ‘unknown’ which is probably the run of the JavaScript itself (the libwalnut.so library has no symbols) we can see that most of the time is in converting SQL data types into JavaScript types at call, and the opposite on return:

  • com.oracle.walnut.core.types.OraNumberUtil.doubleToNumber
  • com.oracle.walnut.core.types.OraNumberUtil.numberToDouble

This is the price to pay when running a different language, with different data types.

So what?

This MultiLingual Engine looks promising, both for functionalities (choose the language to run in the database) and performance (same address space than the SQL, and context switching is minimal). Of course, this is only in beta. There may be more things to implement, with more overhead. For example, we can imagine that if it goes to production there will be some instrumentation to measure time and record it in the Time Model. It may also be optimized further. You can test it (download from the MLE home and give feedback about it (on the MLE forum).

This post was about to measuring performance when switching from SQL to PL/SQL. In next post, I’ll look at callbacks when running SQL from MLE.


Cet article Oracle Database Multilingual Engine (MLE) est apparu en premier sur Blog dbi services.

Talking about APEX Reporting and AOP @ Montreal Oracle Dev Day 2017

Dimitri Gielis - Wed, 2017-10-11 01:00
For those in Montreal and the surrounding area I encourage you to come out to the Montreal Oracle Dev Day on October 25th (8:30-4:30 at Centre for Sustainable Development).

Here’s a summary agenda of the presentations with the full agenda here:
Aside from the presentations you will have plenty of opportunity to network and share your Oracle development experiences. All speakers will be available all day so feel free to bring your APEX questions!

You can register now online.

As I'm not that much in this part of the world it would be great to meet in person. I would love to hear your thoughts on APEX Office Print (AOP) too.  If you have any questions, feedback or just want to talk how to use AOP in your environment, don't hesitate to come up to me. I'm more than happy to talk to you :)

Categories: Development

Converting your XAI Services to IWS using scripting

Anthony Shorten - Tue, 2017-10-10 17:14

With the deprecation announcement surrounding XML Application Integration (XAI), it is possible to convert to using Inbound Web Services (IWS) manually or using a simple script. This article will outline the process of building a script to bulk transfer the definitions over from XAI to IWS.

Ideally, it is recommended that you migrate each XAI Inbound Service to Inbound Web Services manually so that you can take the opportunity to rationalize your services and reduce your maintenance costs but if you want to simply transfer over to the new facility in bulk this can be done via a service script to migrate the information.

This can be done using a number of techniques:

  • You can drive the migration via a query portal that can be called via a Business Service from a BPA or batch process.
  • You can use the Plug-In Batch to pump the services through a script to save time.

In this article I will outline the latter example to illustrate the migration as well as highlight how to build a Plug In Batch process using configuration alone.

Note: Code and Design in this article are provided for illustrative purposes and only cover the basic functionality needed for the article. Variations on this design are possible through the flexibility of the extensible of the product. These are not examined in any detail except to illustrate the basic process.

Note: The names of the objects in this article are just examples. Alternative values can be used, if desired.


The design for this is as follows:

  • Build a Service script that will take the XAI Inbound Service identifier to migrate and perform the following
    • Read the XAI Inbound Service definition to load the variables for the migration
    • Check that the XAI Inbound Service is valid to be migrated. This means it must be owned by Customer Modification and uses the Business Adaptor XAI Adapter.
    • Transfer the XAI Inbound Service definition to the relevant fields in the Inbound Web Service and add the service. Optionally activate the service ready for deployment. The deployment activity itself should not be part of the script as it is not a per service activity usually.
    • By default the following is transferred:
      • The Web Service name would be the Service Name on the XAI Inbound Service not the identifier as that is randomly generated.
      • Common attributes are transferred across from the existing definition
      • A single operation, with the same name as the Inbound Web Service, is created as a minimalist migration option.
  • Build a Plug In Batch definition to include the following:
    • The Select Record algorithm will identify the list of services to migrate. It should be noted that only services that are owned by the Customer Modification (CM) owner should be migrated as ownership should be respected.
    • The script for the above will be used in the Process Record algorithm.

The following diagram illustrates the overall process:

Plug In Development Process

The design of the Plug In Batch will only work for Oracle Utilities Application Framework V4. and above but the Service Script used for the conversion can be used with any implementation of Oracle Utilities Application Framework V4. and above. On older versions you can hook the script into another script such as BPA or drive it from a query zone.

Note: This process should ONLY be used to migrate XAI Inbound Services that are Customer Modifications. Services owned by the product itself should not be migrated to respect record ownership rules.

XAI Inbound Service Conversion Service Script

The first part of the process is to build a service script that establishes an Inbound Web Service for an XML Application Integration Inbound Service. To build the script the following process should be used:

  • Create Business Objects - Create a Business Object, using Business Object maintenance, based upon XAI SERVICE (XAI Inbound Service) and F1-IWSSVC (Inbound Web Service) to be used as Data Areas in your script. You can leave the schema's as generated with all the elements defined or remove the elements you do not need (as this is only a transient piece of functionality). I will assume that the schema will be as the default generation using the Schema generator in the Dashboard. Remember to allocate the Application Service for security purposes (I used F1-DFLTS as that is provided in the base meta data). The settings for the Business Objects are summarized as follows:
Setting XAI Inbound Service BO Values IWS Service BO Values Business Object CMXAIService CMIWSService Description XAI Service Conversion BO IWS Service Conversion BO Detailed Description Conversion BO for XML Application Integration Conversion BO for Inbound Web Services Maintenance Object XAI SERVICE F1-IWSSVC Application Service F1-DFLTS F1-DFLTS Instance Control Allow New Instances Allow New Instances
  • Build Script - Build a Service Script with the following attributes:
Setting Value Script CMConvertXAI Description Convert an XAI Service to IWS Service Detailed Description

Script that converts the passed in XAI Service Id into an Inbound Web Service.

- Reads the XAI Inbound Service definition
- Copies the relevant attributes to the Inbound Web Service
- Add the Inbound Web Service

Script Type Service Script Application Service F1-DFLTAPS Script Engine Version 3.0 Data Area CMIWSService - Data Area Name IWSService Data Area CMXAIService - Data Area Name XAIService Schema (this is the input value and some temporary variables)

  <xaiInboundService mdField="XAI_IN_SVC_ID"/>
  <operations type="group">

The Data Area section looks like this:

  • Add the following code to your script (this is in individual edit-data steps):

Note: The code below is very basic and there are optimizations that can be done to make it smaller and more efficient. This is just some sample code to illustrate the process.

10: edit data
     // Jump out if the inbound service Id is blank
     if ("string(parm/xaiInboundService) = $BLANK")
20: edit data
     // populate the key value from the input parameter
     move "parm/xaiInboundService" to "XAIService/xaiServiceId";
     // invoke the XAI Service BO to read the service definition
     invokeBO 'CMXAIService' using "XAIService" for read;
     // Check that the Service Name is populated at a minimum
     if ("XAIService/xaiInServiceName = $BLANK")
     // Check that the Service type is correct
     if ("XAIService/xaiAdapter != BusinessAdaptor")
     // Check that the owner flag is CM
     if ("XAIService/customizationOwner != CM")
30: edit data
     // Copy the key attributes from XAI to IWS
     move "XAIService/xaiInServiceName" to "IWSService/iwsName";
     move "XAIService/description" to "IWSService/description";
     move "XAIService/longDescription" to "IWSService/longDescription";
     move "XAIService/isTracing" to "IWSService/isTracing";
     move "XAIService/postError" to "IWSService/postError";
     move "XAIService/shouldDebug" to "IWSService/shouldDebug";
     move "XAIService/xaiInServiceName" to "IWSService/defaultOperation";
     // Assume the service will be Active (this can be altered)
     // For example, set this to false to allow for manual checking of the
     // setting. That way you can confirm the service is set correctly and then
     // manually set Active to true in the user interface.
     move 'true' to "IWSService/isActive";
     // Process the list for the operation to the temporary variables in the schema
     move "XAIService/xaiInServiceName" to "parm/operations/iwsName";
     move "XAIService/xaiInServiceName" to "parm/operations/operationName";
     move "XAIService/requestSchema" to "parm/operations/requestSchema";
     move "XAIService/responseSchema" to "parm/operations/responseSchema";
     move "XAIService/inputXSL" to "parm/operations/requestXSL";
     move "XAIService/responseXSL" to "parm/operations/responseXSL";
     move "XAIService/schemaName" to "parm/operations/schemaName";
     move "XAIService/schemaType" to "parm/operations/schemaType";
     // move "XAIService/transactionType" to "parm/operations/transactionType";
     move "XAI/searchType" to "parm/operations/searchType";
     // Add the parameters to the operation list object
     move "parm/operations" to "IWSService/+iwsServiceOperation";
40: edit data
     // Invoke BO for Add
     invokeBO 'CMIWSService' using "IWSService" for add;

Note: The code example above does not add annotations to the Inbound Web Service to attach policies for true backward compatibility. It is assumed that policies are set globally rather than on individual services. If you want to add annotation logic to the script it is recommended to add an annotations group to the script internal data area and add annotations list in logic in the script.

One thing to point out for XAI. To use the same payload for an XAI service in Inbound Web Services, a single operation must exist with the same name as the Service Name. This is the design pattern for a one to one conversion. It is possible to vary from that if you manually convert from XAI to IWS as it is possible to reduce the number of services in IWS using multiple operations. Refer to Migrating from XAI to IWS (Doc Id: 1644914.1) and Web Services Best Practices (Doc Id: 2214375.1) from My Oracle Support for a discussion of the various techniques available. The attribute mapping looks like this:

Mapping of objects

The Service Script has now been completed. All it needs is to pass the XAI Inbound Service Identifier (not the name) to parm/xaiInboundService structure.

Building The Plug In Batch Control

In past releases, the only way to build a Batch process that is controlled via a Batch Control was to use the Oracle Utilities SDK using Java. It is now possible to define what is termed a Plug In based Batch Control which allows you to use ConfigTools and some configuration to build your batch process. The fundamental principle is that batch is basically selecting a set of records to process and then passing those records into something to process them. In our case, we will provide an SQL statement to subset the services to convert from XAI to pass to the service we just built in the previous step.

Select Records Algorithm

The first part of the Plug In Batch process is to define the Select Records algorithm that defines the parameters for the Batch process, the commit strategy and the SQL used to pump the records into the process. The first step is to create a script to be used for the Algorithm Type of Select Records to define the parameters and the commit strategy. For this example I created a script with the following parameters:

Setting Value Script CMXAISEL Description XAI Select Record Script - Parameters Detailed Description This script is the driver for the Select Records algorithm for the XAI to IWS conversion Script Type Plug In Script Algorithm Entity Batch Control - Select Records Script Version 3.0 Script Step 10: edit data
 // Set strategy and key field
 // Strategy values are dictated by BATCH_STRATEGY_FLG lookup
 //  Set JOBS strategy as this is a single threaded process
 //  I could use THDS strategy but then would have to put in logic for
 // restart in the SQL. The current SQL has that logic already implied.
 move 'JOBS' to "parm/hard/batchStrategy";
 move 'XAI_IN_SVC_ID' to "parm/hard/keyField";

Note: I have NO parameters for this job. If you wish to add processing for parameters, take a look at some examples of this algorithm type to see the processing necessary for bind variables.

The next step is to create an algorithm type. This will be used by the algorithm itself to define the process. Typically, an algorithm type is the definition of the physical aspects of the algorithm and its parameters. For the select algorithm the following algorithm type was created:

Setting Value Algorithm Type CMXAISEL Description XAI Selection Algorithm Detailed Description This algorithm Type is a generic wrapper to set the job parameters Algorithm Entity Batch Control - Select Records Program Type Plug In Script Plug In Script CMXAISEL Parameter SQL (Sequence 1 - Required) - This is the SQL to pass into the process

The last step is to create the Algorithm to be used in the Batch Control. This will use the Algorithm Type created earlier. Create the algorithm definition as follows:

Setting Value Algorithm Code CMXAISEL Description XAI Conversion Selection Algorithm Type CMXAISEL Effective Date Any valid date in the past is acceptable SQL Parameter

SELECT xai_in_svc_id FROM ci_xai_in_svc
WHERE xai_adapter_id = 'BusinessAdaptor'
xai_in_svc_name NOT IN ( SELECT in_svc_name FROM f1_iws_svc)
owner_flg = 'CM'

You might notice the SQL used in the driver. It passes the XAI_IN_SVC_ID's for XAI Inbound Services that use the Business Adaptor, are not already converted (for restart) and are owned by Customer Modification.

Process Records Algorithm

The next step is to link the script created earlier to the Process Records algorithm. As with the Select Records algorithm, a script, an algorithm type and algorithm entries need to be created.

The first part of the process is to build a Plug-In Script to pass the data from the Select Records Algorithm to the Service Script that does the conversion. The parameters are as follows:

Setting Recommended Value Script CMXAIProcess Description Process XAI Records in Batch Detailed Description This script reads the parameters from the Select records and passes them to the XAI Conversion script Script Type Plug-In Script Algorithm Entity Batch Control - Process Record Script Version 3.0 Data Area Service Script - CMConvertXAI - Data Area Name ConvertXAI Script Step if ("parm/hard/selectedFields/Field[name='XAI_IN_SVC_ID']/value != $BLANK")
    move "parm/hard/selectedFields/Field[name='XAI_IN_SVC_ID']/value" to "ConvertXAI/xaiInboundService";
    invokeSS 'CMConvertXAI' using "ConvertXAI" ;

The script above basically takes the parameters passed to the algorithm and then passes them to the Service Script for processing

The next step is to define this script as an Algorithm Type:

Setting Value Algorithm Type CMXAIPROC Description XAI Conversion Algorithm Detailed Description This algorithm type links the algorithm to the service script to drive the process. Algorithm Entity Batch Control - Process Record Program Type Plug-In Script Plug-In Script CMXAIProcess

The last step in the algorithm process is to create the Algorithm entry itself:

Setting Value Algorithm Code CMXAIPROCESS Description XAI Conversion Process Record Algorithm Type CMXAIPROC Plug In Batch Control Configuration

The last part of the process is to bring all the configuration into a single place, the Batch Control. This will pull in the algorithms into a configuration ready for use.

Setting Value Batch Control CMXAICNV Description Convert XAI Services to IWS Detailed Description

This batch control converts the XAI Inbound Services to Inbound Web Services to aid in the mass migration of the meta data to the new facility.
This batch job only converts the following:

- XAI Services that are owned by Customer Modification to respect record ownership.
- XAI Services that use the Business Adaptor XAI Adapter. Other types are auto converted in IWS
- XAI Services that are not already defined as Inbound Web Services

Application Service F1-DFLTAPS Batch Control Type Not Timed Batch Category Adhoc Algorithm - Select Records CMXAISEL Algorithm - Process Records CMXAIPROCESS

The Plug-in batch process is now defined.


The conversion process can be summarized as follows:

  • A Service Script is required to transfer the data from the XAI Inbound Web Service to the Inbound Web Service definition. This converts only services owned by Customer Modification, have not been migrated already and use the Business Adaptor XAI Adapter. The script sets the same parameters as the XAI Service for backward compatibility and creates a SINGLE operation Web Service with the same payload as the original.
  • The Select Records Algorithm is defined which defines the subset of records to process with a script that defines the job properties, an algorithm entry to define the script to the framework and an algorithm, with the SQL to use, to link to the Batch Control.
  • The Process Records Algorithm is defined which defines the processing from the Select Records and links in the Service Script from the first step. As with any algorithm, the code is built, in this case in Plug-In Script to link the data to the script, an algorithm type entry defines the script and then an algorithm definition is created to link to the batch control.
  • The last step is to create the Batch Control that links the Select Records and Process Records algorithms.

Cloning Goldengate Integrated Capture and DB

Michael Dinh - Tue, 2017-10-10 17:10

Using DBMS_STREAMS_ADM To Cleanup GoldenGate

Let’s say you want to clone DB and Goldengate implementation from PROD to DEV, then you need to drop the capture that was registered with PROD database.

This is what happens when dependencies are introduced / created.

select capture_name from dba_capture;

nVision Performance Tuning: Introduction

David Kurtz - Tue, 2017-10-10 15:41
This blog post is the first in a series that discusses how to get good performance from nVision as used in General Ledger reporting.

PS/nVision is a PeopleTools technology that extracts data from the database and places it in an Excel spreadsheet (see PS/nVision Overview).  Although PS/nVision can be used with any PeopleSoft product, it is most commonly used in Financials General Ledger.

The SQL queries generated by nVision are, at least conceptually, similar to data warehouse queries. The ledger, ledger budget or summary ledger tables are the fact tables.

The ledger tables are analysed by their attribute columns. There are always literal conditions on the fiscal year and accounting period, there is usually a literal condition on currency code.  Then there are criteria on some of the other attributes.  I will take an example that analyses the ledger table in three dimensions: BUSINESS_UNIT, ACCOUNT and CHARTFIELD1, but there are many other attribute columns on the ledger tables.  These attributes are defined in lookup tables in the application, but their hierarchies are defined in trees.

nVision reports use the trees to determine which attribute values to report.  A report might report on a whole tree, or particular nodes, or branches of a tree.  nVision joins the tree definition to the attribute table and produces a list of attributes to be reported.  These are put into working storage tree selector tables (PSTREESELECT01 to 30).  The choice of selector table is controlled by the length of the attribute column.  BUSINESS_UNIT is a 5 character column so it goes into PSTREESELECT05. CHARTFIELD1 and ACCOUNT are 10 character columns so they use PSTREESELECT10.  These selector tables form the dimensions in the queries.

Here is an example of a SQL statement generated by nVision.  The tree selector 'dimension' tables are joined to the ledger 'fact' table.

This SQL looks simple enough, but there are various complexities
  • The tree selector tables are populated at runtime.  Many dimensions can be stored in each tree selector table, each keyed by a different SELECTOR_NUM.
  • Selectors can be static or dynamic.  In dynamic selectors, the data is only stored temporarily for the lifetime of the report and will be deleted when it completes.  So immediately, there is a challenge of keeping statistics up to date, and even then Oracle doesn't always manage to find an effective execution plan.
  • Different selectors will have different numbers of rows, so the statistics have to describe that skew.
  • Different nVision reports and even different parts of the same report generate different statements that can use different combinations of attribute columns.  The number of dimensions can vary, I have seen systems that use as many as five different trees in a single query.
  • Then the database needs to find the relevant rows on the ledger table for the dimensions specified as efficiently as possible.
This very quickly becomes a difficult and complex problem.  This series articles works through the various challenges and describe methods to overcome them.  Not all of them are applicable to all systems, in some cases, it will be necessary to choose between approaches depending on circumstances.


Subscribe to Oracle FAQ aggregator