Weblog for the AMIS Technology corner
Archive for December, 2004
Just before X-mas
Dec 24th
On the 7th of December, I, a database administrator, had a seminar from Mr. Chris Date about “Relational Remodeled” (into the 21st century with the relational model: a technical presentation). It’s contents and implications still startles me. Not because the issues explained by Mr. Date were new, but that the issues are still, after so many years, very much alive. Chris told a small story during this seminar about a question, he once asked to his wife, when she tried to understand why he was so excited. Chris asked her “what’s the most essential thing about databases?“. She replied after a while: “I think, that you can trust the data, you have stored in the database…“. In my mind this is the most essential thing of all, regarding data in databases. Every question you “ask” the database, should give you a correct data, data you can trust. So this story really hit me. That was exactly (again) the thing bogging me for some weeks. Maybe in a different context (Oracle security alert 68), but nonetheless . In one of the more profound publications I got from Chris during this seminar, by him described as The Paper were it all started with, it’s stated as follows:
ABSTRACT: The large, integrated data banks of the future will contain many relations of various degrees in stored form. It will not be unusual for this set of stored relations to be redundant. Two types of redundancy are defined and discussed. One type may be employed to improve accessibility of certain kinds of information which happed to be in great demand. When either type of redundancy exists, those responsible for control of the data bank should know about it and have some means of detecting any “logical” inconsistencies in the total set of stored relations. Consistency checking might be helpful in tracking down unauthorized (and possible fraudulent) changes in the data bank contents.
Derivability, redundancy and consistency of relations stored in large data banks – E. F. Codd, August 19, 1969
Thirty-five years ago…
Most of the time I am, or I am one of the people, who “is responsible for control of the data” . Regarding to the issue “security”, this is one of our biggest challenges as (database) administrators. How do you get someone out and keep him or her out, where he or she was not allowed to get in, in the first place. How do you “track down unauthorized (and possible fraudulent) changes in the data bank contents“.
Normally I would start saying: “start gathering information (and evaluate this information!) – don’t forget to use your common sense” (don’t allow select any table privileges in your database – eg. “select password from sys.link$” etc. etc.). Mr. Date’s remark, that one of the biggest problems with SQL is that “SQL does not support the relational model” and / or “Databases do not support the relational model or the relational model is not correctly implemented” does also not really help in this context.
If you think in terms like optimizers and query re-write you even start more wondering if your result set is the correct one. The better the optimizer becomes, the more code was used to build it, the more bugs there will arise. It’s all a merry-go-round. One thing i believe in though – it will never be “secure”. There will be always someone smarter, than the person who build a better “security” scheme.
Mr. Date said during the seminar: “the abstraction level defines atomic“. As an example he tried to explain atomic to set this in the context of that there is an abstraction level when you look at atoms at an atomic level. Also the abstraction level in it’s place defines therefore atomic. He annotated that atoms exist out of electrons and neutrons and that it’s possible to split these again in quarks and strings. Each level containing his own correct abstraction level.
Lets do the merry-go-round in that perspective. We are all perfectionists in some or other way, let us be driven by the challenge and define the correct abstraction level, maybe it defines also the perfect relational model, database, ANSI SQL standard, etc.
In the meanwhile i will try to control the data bank the best i can, with the best means i can find or build.
(some) Links:
http://www.thethirdmanifesto.com/
http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci962949,00.html
http://www.infoworld.com/cgi-bin/displayStory.pl?/interviews/980213date.htm
UML version 2.0
Dec 23rd
Introduction
Although UML 2.0 is not completely new, I was not able to have a look at it until now. For people like me, and for other people who are interested, I tried to summarize the differences between UML 2.0 and UML 1.4. At the very bottom of this post, I added a link to a site where all the UML 2.0 diagrams are explained. I used the information described on the pages of this link and other information which is available on the internet, to complete this post. Read the rest of this entry »
XSL Transforming XML generated from SQL results using JSTL tags in JSP pages – putting JSTL to the test
Dec 22nd
While working on more advanced Statistics Reporting for our weblog, I did some investigations into JSTL and more specifically the XML and SQL tags. Yes, I know I have said one should never use the SQL tags in a real application. However…. this is read-only, prototpying, quick and dirty etc. And of course I am investigating the SQL tags so I have to use them.
The setup I created was the following: I invoke a JSP (running in Tomcat 4) from the browser. This JSP imports several other JSPs that return XML documents. These XML documents are constructed from data returned from a database (either Oracle or MySQL) using the JSTL SQL tags. The JSP passes some simple parameters to the imported data-generating-JSPs to influence the exact set of data returned from the database. In the main JSP, the XML documents are transformed using XSLT and XSL-stylesheets. Typically the transformation is to HTML but I also worked on transformations to plain text, PDF and SVG. Read the rest of this entry »
AMIS Query – Oracle BPEL Process Manager Dissected (Thursday 27th January)
Dec 21st
BPEL is emerging as the standard for assembling a set of discrete services into an end-to-end process flow, radically reducing the cost and complexity of process integration initiatives. The Oracle BPEL Process Manager offers a comprehensive and easy-to-use infrastructure for creating, deploying and managing BPEL business. Oracle BPEL PM has both a design-time and a run-time environment for design, generation and implementation, deployment, execution and management of BPEL based workflows or business processes. The run-time component or BPEL engine is integrated with Oracle 10g Application Server (runs in OC4J). The design-time is currently available as plugin for Eclipse and will be released shortly (Spring 2005?) as plugin for Oracle 10g JDeveloper.
Very interesting is the option offered by Oracle BPEL Process Manager to integrate into BPEL processes existing internal applications – implemented with for example Java or PL/SQL technology – without the full overhead of a formal WebService implementation. This allows us to also implement internal workflows in a standardized manner across different technology stacks with very efficient calls into existing Java and/or PL/SQL Modules. New instances of the predefined Business Processes are started, managed and monitored through the BPEL Process Manager runtime engine. There are various APIs to get hold of statistics on the process execution.
In this session, Sandor Nieuwenhuijs (Senior Principal Product Manager with Oracle) will demonstrate and comment on the latest developments around Oracle BPEL. Sandor will explain how Oracle BPEL fits in Oracle’s over-all product portfolio and how it relates to for example ProcessConnect and Workflow. He will address more specifically the efficient integration of PL/SQL and Java components in workflows. We also spend some time on the APIs in the run-time environment of Oracle BPEL Process Manager and the best ways to manage it. This session offers plenty of room for discussion and for getting hands-on experience with – for the first time in The Netherlands – the Oracle BPEL design-time plugin for Oracle 10g JDeveloper.
For more details on Oracle BPEL, see the homepage on OTN: Oracle BPEL Process Manager on OTN.
Agenda
15.30 Welcome
15.35 Overview of Oracle BPEL Process Manager and demonstrations, discussion etc.
18.00 Dinner
19.00 Workshop with the Oracle BPEL plugin for JDeveloper
20.30 or later…. The End
Target Audience
Senior Developers, Technical Architects and Technical Managers. The session assumes a background with WebServices, XML, Java and possibly PL/SQL.
To register as visitor for this session, please go to AMIS Activitities
Resources on Oracle BPEL Process Manager
For our previous posts on Oracle BPEL Process Manager, see JDeveloper BPEL plugin and BPEL is great – and so is Oracle BPEL. A very interesting post on invoking Java code directly (not through the full monty of WebServices wrappers) is found on OraBlogs: BPEL – More than Web Services by Antony Reynolds; it discusses WSIF and also embedding Java code directly in the BPEL document (comparable with Java scriptlets in JSP files). An even better post is this one Using Java from BPEL with WSIF also by Antony Reynolds
Enabling Role-based security management in Oracle Designer 6i/9i/10g through the Repository Object Browser
Dec 20th
Managing users and privileges on countless folders and application systems, configurations and workareas as is the job of the Repository Administrator can be quite a task. Each privilege on every container-object needs to be manually assigned and revoked. If a new developer enters the project, with exactly the same set of privileges as his or her neighbour, you still have to grant all privileges from scratch, there is no way to copy a user or apply a template of privileges. Well that is, unless you are using the Oracle Designer Web Assistant 6i or 9i (ODWA, part of the iDeveloper Accelerators Suite that also contains Headstart and RuleFrame) or you have the Repository Object Browser (ROB) installed
Oracle Designer product development adopted the Oracle Designer Web Assistant in 2002. It simply took over all ODWA source code from the Netherlands-based Center of Excellence for Custom Development (the team behind stuff like CDM, JHeadstart, RuleFrame, Headstart etc.). This code was somewhat – marginally – modified; centralization of boilerplate text in a single package was the most important modification. The support for Roles in Designer was temporarily disabled until product management could find the time to properly assess its impact. Although the Repository infrastructure is prepared for role – the Role PUBLIC is part of every Oracle Designer 6i/9i/10g Repository – it was never fully worked out in detail by Product Development itself. And even though dozens of customers had been using Oracle Designer Web Assistant with Role Management enabled for some time, a careful study was deemed in order. Unfortunately, Oracle seems to have forgotten about this functionality or at least its very low on the list of priorities. So here we are: we have the ROB, we have all the code for Role based privileges management but we cannot use it. Or can we? Read the rest of this entry »
Providing default-values for find-pages in JHeadstart applications (UIX/BC4J/Struts)
Dec 16th
An interesting problem we were working on some time ago is the following: a JHeadstart application – release 9.0.4.5 created with UIX, BC4J and Struts, JDeveloper 9.0.3 – has many Find pages. These pages contain many search-criteria. Our users would like to have their own default values provided for some/many/most of these search criteria. That means: when user A enter a Find page, a number of fields will already have specific values that limit the search results in a certain way. When user B enters the same Find page, again a number of fields – probably different fields – will have a default value – probably a different default. Each user will have a persistent user-profile, stored in the database, that contains these default values. This post describes how we implemented a solution for this request. Read the rest of this entry »
Book Review: Oracle Database 10g New Features – Oracle10g Reference for Advanced Tuning and Administration by Daniel Liu, Don Burleson, Madhu Tumma, Mike Ault
Dec 16th
Title: Oracle Database 10g New Features – Oracle10g Reference for Advanced Tuning and Administration
Authors: Daniel Liu, Don Burleson, Madhu Tumma, Mike Ault
Published by: Rampant Tech Press
ISBN: 0-9740716-0-9
Publication Date: 01 December, 2003
Details: 530 pages, $34.95; code depot at: http://rampant.cc/new.htm
SummaryIt is not a reference, it is a new features overview. It is an assorted, hardly edited or annotated copy from several Oracle Manuals and White Papers. Do not buy it. If you have copy, browse through it and have it pique your interest. Read the rest of this entry »
Pivoting in SQL using the 10g Model Clause
Dec 16th
Pivoting records means changing their format and typically either splitting or merging records. Usual examples include transactional records such as a list of daily transactions that need to be turned into a record structure, for example a weekly record. The inverse operation is also sometimes required. You can also use pivoting to get a nicer, more compact result from a SQL query without procedural logic.
Pivoting (both merging and splitting) can easily be implemented using Table Functions. See for example: Turning On Pivot Tables By Jonathan Gennick (Oracle Magazine 2002). Table Functions require you to create an object type (most of the times), a nested table type and a PL/SQL Function. For a simple (!) query, that may be a lot of trouble. And although Table Functions can be made quite efficient – they even allow parallel execution – they still can probably not compete with pure SQL based solutions.
You can “trick” merging records in SQL using in-line views and an aggregation statement (this works in early 8.0 versions of the database). This SQL trick seems to be invented by Tom Kyte (see his Expert One-on-One Oracle book) – and can now be seen in many articles. For an example, see: Pivot Query on AskTom. This solution requires SQL that is somewhat difficult to read, understand and maintain: the purpose of the aggregation to condense several rows into one is not immediately clear.
You can ususally make use of a UNION for splitting records. This is not very efficient and requires you to duplicate entire SQL statements which is far from ideal.
The new way to pivot records – merging and splitting using the 10g SQL MODEL
The SQL MODEL clause, introduced in Oracle 10g Release 1, gives new ways to approach pivoting, both merging and splitting records. Read the rest of this entry »
For our eyes only – Oracle EPB (Enterprise Planning & Budgeting)
Dec 15th
Last Friday we enjoyed one of the special privileges we have in our partnership with Oracle in The Netherlands – we have a private session on one of Oracle’s latest products Enterprise Planning and Budgeting (announced as early as 2002 as successor to OFA and OSA – Oracle Financials and Sales Analyzer – finally released in October 2004 in a controlled release; more general availability is expected after Q1 2005, say April or May (the first new piece of information we gathered). Note that this visit to De Meern to learn more about EPB was triggered by two of our customers with specific questions, such as I discussed in a previous post Oracle (finally) announced release of Enterprise Planning and Budgeting. Our delegation consisted of a commercial representative, an analyst/developer, a business consultant and a technical consultant/architect/developer (that’s me).
EPB is a tool that primarily supports – as the name says – planning and budgeting. It completely handles the workflow around budgeting as well as integrating actuals (available from the CPM that is fed from either Oracle Apps or some 3rd party ERP system or an operational Data Warehouse) into the planning and forecasting process. It should replace Excel as the primary tool for creating budgets and trying to compare budgets and planning to actuals. It has 10 predefined Dimensions – such as time and geography – and allows another 20 Dimensions to be user defined. In the demonstration of EPB, we saw how a business process around a particular type of budget was set up. Read the rest of this entry »
Parallelizing Table Functions (instead of paralysing)
Dec 12th
One of the many topics in our (AMIS’) Oracle 7Up (..to 10g) workshop is Table Functions. We discuss how PL/SQL functions can be turned into ‘table like’ data sources or row-generators. Table Functions have been around since Oracle 8i. Since Oracle 9i, we have two additional capabilities with Table Functions: pipelining and parallel execution. Pipelining basically means that the Table Function will return rows as soon as they are ready, instead of sitting on all of them until the entire set is ready and then returning them. When multiple table functions are used in a series, this means that processing of the first record by the second table function (‘transformer’) can start as soon as it has been piped out by the first table function. That first record may be returned to the client – e.g. SQL*Plus – even before the first Table Function has managed to produce its second record. It should be clear that this pipelining gives tremendous improvement even of overall throughput time. The first record is returned must faster, but since now the first, second and subsequent table functions can work at the same time – in parallel as it were – the processing is complete much sooner than otherwise would have been the case.
However, this is not what is meant by parallel execution of Table Functions. This is what is: Read the rest of this entry »
