Component Technology and Active Databases

1
Component Technology and Active Databases

• Sources
• Ken North Database Magic with Ken North,
  Prentice-Hall 1999
• Jim Melton, Andrew Eisenberg Understanding SQL
  and Java Together, Morgan Kaufmann 2000
• http//www.sqlj.org (under construction?)
• Informix Embedded SQJL User's Guide
  (www.informix.co.za)
• Oracle8i SQLJ Developer's Guide and Reference,
  Oracle 1999

Martti Laiho, 2001
2
Data Access Challenges ..
- Ken North

• Global access
• Multinational organisations/companies
• Virtual organisations
• e-Commerce C2B, B2B
• Mobile computing
• and possibility of rush hits on high-volume Web
  pages
• leads to high-quality demands in database
  programming
• database connections
• transaction programming and concurrency control
• query design, indexing technologies, ..

Martti Laiho, 2001
3
.. Challenges
- Ken North

• providing
• Rich data types
• multimedia, spatial data
• Internationalisation
• multiple languages (or English) and character
  sets
• in data, forms, error messages, help screens
• 24x7 availability and time zone management
• governmental laws/regulations on trade, privacy,
  etc
• Distributed transactions across heterogeneous
  databases
• Replications
• Security
• adhering Standards SQL-99, X/Open XA, ...

Martti Laiho, 2001
4
Requirements
- Ken North

• Reliability
• Security
• Performance
• Scalability
• Cost-effectiveness
• Based on de facto Standards

Martti Laiho, 2001
5
Reliability
- Ken North

• Fault-tolerant servers
• Cluster technology
• Replication
• Database Technology
• Strong Theory of Data Modelling
• Integrity rules and business logic
• Constraints Primary key, Foreign key, Unique,
  Check, Not null, Cascading rules
• Triggers, functions, procedures, packages
• Transaction Processing
• Security

Martti Laiho, 2001
6
Security Issues
- Ken North

• User account Authentication and Privileges
• passwords, domains, roles, permissions,
• access by stored procedures
• Firewalls, air-gap technologies
• Tunnelling, Secure sockets
• Virus protection
• Encryption
• Signatures, Certificates
• Security Support Provider interface
• Java security model

Martti Laiho, 2001
7
Performance and Scalability
- Ken North

• Server technologies
• Multi-Tier Architectures
• Buffering / Caching technologies
• Local processing avoiding network round trips
• Stored Procedures, Packages
• bulk processing
• batches
• Indexing technologies

Martti Laiho, 2001
8
Client/Server 2-Tier,.., Multi-Tier Architectures
Browsers
Power users GUI applications Office automation
Internet, Extranet
Global directories
Web servers
- JDBC, ODBC, OLE DB
HTML, Applets, ActiveX, ASP CGI, ISAPI,
Servlets XML
Intranet
Ethernet LAN
NOS, active directory messaging
Application servers
- Data validation - Business rules
Database servers
- persistent data - active SQL databases - stored
procedures
Transaction servers
- Data Access - JDBC, ODBC, OLE DB
Martti Laiho, 2001
9
Extensible Database Servers
- Ken North

• Universal Servers, Object-Relational Servers
• unifies SQL and complex data types, streams
• text retrieval and pattern recognition
• various indexing schemes B trees, hash
  indexes, AVL trees, r-trees, bit-mapped indexes
• Dedicated Servers
• Data Warehousing (DW)
• Online Analytical Processing (OLAP)
• Very Large Databases (VLDB)
• Extensible, Adaptive Servers
• Component integration, Universal Data Access
• Java, C, ..

Martti Laiho, 2001
10
Technologies
- Ken North

• RPC and Message Queues
• asynchronous RPC, transactional RFC
• Message-oriented middleware (MOM)
  andtransactional message queues
• DNA ? .NET
• COM, OLE Automation, OCX, ActiveX, COM
• ODBC/OLE DB, ADO, ASP
• Java and RMI
• Servlets, JavaBeans, EJB, JSP
• Transaction Servers

Martti Laiho, 2001
11
Tools
Thomas Carlyle "Man is a tool-using animal
Without tools he is nothing, with tools he is
all." H.G. "A fool with a tool - is still a fool"

• Visual tools
• Rapid application development (RAD) tools
• prototypes
• database-enabled Web pages
• "quick and dirty","As Soon As Possible"
• Professional precision tools
• Data Modelling tools, UML, design patterns
• Quality design for mission-critical applications
• Indexing tools
• Query Optimizing tools

Martti Laiho, 2001
12
Stored Procedures
- Ken North

• SQL/PSM
• ISO Standard 1996
• Implementations Mimer, DB2 V7.1
• Proprietary Implementations
• Oracle PL/SQL
• Informix SPL
• Sybase/Microsoft T-SQL
• External Procedures
• DB2 C, C, Cobol, Java
• Oracle PL/SQL hybrid procedures

Martti Laiho, 2001
13
Procedure Management

• GRANT CREATE PROCEDURE to ...
• CREATE PROCEDURE procedure () ...
• parse, validate objects and access permissions of
  the creator
• optimize, generate access plan
• CREATE OR REPLACE PROCEDURE
• ALTER PROCEDURE ...
• GRANT EXECUTE ON procedure
• EXECUTE procedure ()
• reoptimize by recompile
• DROP PROCEDURE procedure

Martti Laiho, 2001
14
Remote Procedures

• In-process (not fenced) procedures are efficient
• Out-of-process procedures (fenced) are safe
• Remote procedures require (user account) access
  permission to the remote server as well

Martti Laiho, 2001
15
Functions

• SQL Aggregate functions
• Numeric functions
• String functions
• Date and time functions
• System functions
• Conversion and casting functions
• Table functions
• User-defined functions (UDF)
• external functions written in 3GL languages

Martti Laiho, 2001
16
Packages
- Ken North

• Declaration
• Public and private objects
• Encapsulation
• Variables
• Functions and procedures
• Unit of security
• Unit of caching

Martti Laiho, 2001
17
Transaction Processing

• TP-Lite in SQL databases
• TP-Monitors
• IBM CICS, Transarc Encina, BEA Tuxedo
• Distributed transactions (2PC)
• Transaction Servers
• Microsoft MTS, Sybase Jaguar CTS, EJB Servers
• TP-monitor Transactional Components
• Single-user model extended scalable
• Thread management object locking
• Object pooling
• Connection pooling
• Role based security

Martti Laiho, 2001
18
Why Java in the database?
- Ken North

• Security
• No pointers, no memory leaks
• bytecode verification
• VM integrated in DBMS
• Solution to Abstract Data Types (ADT, UDT)
• Portability and platform independence
• Universal language for multi-tier programming
  including stored procedures
• Based on SQLJ or JDBC

Martti Laiho, 2001
19
Oracle 8i JServer architecture
- Oracle8i Java Component Programming
Morrisseau-Leroy, Solomon, and Basu, 2001
PL Engine
SQLJ translator
EJB
Aurora ORB
SQLJ runtime
JDBC
Java Core Class Libraries
Aurora Virtual Machine
SQL Engine
Database
Oracle RDBMS Libraries
Martti Laiho, 2002
20
"Hello" Java Stored Procedure
- Oracle example
public class Hello public static String
world () return "Hello world"
javac Hello.java loadjava -user scott/tiger
Hello.class create or replace function
HELLOWORLD return VARCHAR2 as language java
name 'Hello.world () return java.lang.String' /
SQLgt select HELLOWORLD from DUAL
HELLOWORLD -----------------------------------
Hello world SQLgt
21
SQLJ Technology
- Melton, Eisenberg

• ESQL for Java
• SQLJ Consortium Oracle, IBM, Sybase, Informix,
• Part 0 Embedded SQL in Java (ANSI SQL 10
  SQL/OLB)
• Object bindings
• sql
• translator from Oracle
• Part 1 SQL Routines using Java
• Static stored procedures and UDFs
• Part 2 SQLJ Data Types
• Pure Java classes as ADTs

22
SQLJ phases
Sqlj.org Oracle, IBM, etc
Running the application or from applet
java
Foo.sqlj
sqlj (translator)
Foo.java
javac
Foo.class
jartool
Foo.jar
jdbc
Registering/ Storing into database
_profile.ser
_profile.ser
_profile.ser
_profile.ser
loadjava
compiled Sql
Using as stored procedure
database
23
SQLJ files
import java.sql. import sqlj.runtime.ref.Defaul
tContext import oracle.sqlj.runtime.Oracle
sql iterator MyIter (String ename, int empno,
float sal) public class MyExample public
static void main (String args) throws
SQLException Oracle.connect
("jdbcoraclethin_at_oow115521sol2", "scott",
"tiger") sql insert into emp (ename,
empno, sal) values ('SALMAN', 32, 20000)
MyIter iter sql iter select ename,
empno, sal from emp while
(iter.next()) System.out.println
(iter.ename()" "iter.empno()" "iter.sal())

MyIter.sqlj
portable
portable
translator
javac
MyIter.java
MyIter.class
JVMSqlj runtime
MyIter _SJProfile0.ser MyIter _SJProfile1.ser
MyIter _SJProfile2.ser ...
24
SQLJ vs ISO ESQL/C ...
- Informix SQLJ User's Guide

• Flexibility of Connection context object
• No host variable definitions needed
• WHENEVER replaced by Java's professional
  exception handling
• Result-set iterator object acting as native part
  of the Java environment instead of the SQL cursor
  object
• Java allows NULL values - no need for Null
  indicators
• Only static SQL (- use JDBC for dynamic SQL)

25
JDBC - Interfaces / Methods
See JDBC tutorial
http//java.sun.com/products/jdbc/.
DatabaseMetaData
getTables()
Connection
ResultSet
ResultSetMetaData
Statement
getMetaData() setAutoCommit(b) setTransaction
Isolation(level) createStatement() prepareStateme
nt(sql) prepareCall(sql) commit() rollback() close
()
getMetaData() findColumn(name) next() getInt(col)
getShort(col) getNumeric(col) getDouble(col)
getFloat(col) getString(col) getDate(col) getTime
(col) getTimestamp(col) wasNull() setText(s) appen
d(s) close()
getColumnCount() getColumnName(i) getColumnLabel(i
) getColumnDisplaySize(i)
setCursorName(s) executeQuery(sql) executeUpdate(s
ql) cancel() close()
DriverManager Class
getConnection (url, user, psw)
PreparedStatement
setXxxx(n, hvar) clearParameters()
SQLException
CallableStatement
getSQLState() getErrorCode() getNextExcetion()
registerOutputParameter execute() ...
26
SQLJ or JDBC?

• SQLJ precompiler improves performance and
  reliablity by
• syntax checking
• type checking
• schema checking
• at compile time and
• precompiled optimized SQL plans (static SQL)
  stored in the database to be utilized
• at run time
• JDBC allows dynamic SQL