ASE 107: Mixing Replication with Java in the Database

1
ASE 107 Mixing Replication with Java in the
Database
Johan McDonald Jimmy MyerscoughEnterprise
Systems - Sybase S.A.johanm_at_sybase.co.za
jimmy_at_sybase.co.za 27 83 379 1394 / 2711 804
3740August 2003
2
Agenda
Mixing Replication with Java in the Database

• The Business and IT Problem
• The Possible Technical Solutions
• Technologies we Chose
• Sybase Replication.
• Java in the Database.
• Sybase Natural Interface (SNI)
• The Final Solution
• Other uses for this Technology
• Conclusion

3
The Business problem
Mixing Replication with Java in the Database

• Companies Registrar
• Government agency for the statutory registration
  of public/private Companies
• Ensuring orderly registration which complies with
  the law.
• Part of the mechanisms aimed at improving
  Corporate Governance.
• Streamlined business process, to make it a fast
  easy to business locally.
• Some of the more problematic checks, time-wise.
• Are the Directors eligible to be a Director,
  orDoes the to-be Directors have a criminal
  record ? Maximum details.
• Are the Directors who they say they are ?
• Are any of the to-be Directors a wanted person,
  locally, or internationally.
• Time constraints on the process, currently
  measured in days
• Perform the same check in a automated way.
• Hopefully faster,
• AND cheaper

4
The IT problem
Mixing Replication with Java in the Database

• Distributed nature of the Information needed.
• The information needed resides
• in different locations
• In different databases (Informix, Oracle, Adabas)
• Running on different technologies (Unix,
  Mainframe etc)
• In different departments.
• Different owners for the relevant information.
• Companies registrar (for now, this is us)
• Home affairs
• Police services

5
The IT problem
Mixing Replication with Java in the Database

• Specifics for each of the mentioned checks
• Are the Directors eligible to be a Director ?
• Internal to the registrar, part of the existing
  workflow.
• Information stored in Informix, on Unix
• Are the Directors who they say they are ?
• Check internal data.
• Check with Home affairs, to match the name with
  the ID-number.
• Check for work permits for foreign citizens, if
  applicable,
• Information stored in Adabas
• Are any of the to-be Directors a wanted person,
  locally, or internationally.
• Check with Police Services, in their database.
• Information stored in Oracle on Mainframe

6
The IT problem
Mixing Replication with Java in the Database

• Specifications for a proposed solution.
• Legal compliance regarding Information that may
  be shared between departments.
• Security concerns regarding access. Who queries
  what. Needs to be controlled limited to what is
  needed.
• Security concerns regarding how secure that data
  will be transferred. Currently no access to
  Police database from outside their red network.
• Dealing with elegantly with the occasional
  un-availability of information from one, or more
  of the sources.
• Automated as far as possible.
• Cost effective solution

7
Agenda
Mixing Replication with Java in the Database

• The Business and IT Problem
• The Possible Technical Solutions
• Technologies we Chose
• Sybase Replication.
• Java in the Database.
• Sybase Natural Interface (SNI)
• The Final Solution
• Other uses for this Technology
• Conclusion

8
Possible Technical Solutions
Mixing Replication with Java in the Database

• The other Potential solutions we considered
• ETL tools
• Extract from Adabas, (and optionally Oracle), to
  central look-up pointKilled on security/access
  control concerns
• Extract from Source, to the two external
  reference points, perform lookup local to the
  two external parties, return status.Killed on
  secure access, estimated cost, and complexity of
  installations (who hosts what).Batch nature was
  also a problem.

9
Possible Technical Solutions
Mixing Replication with Java in the Database

• The other Potential solutions we considered
• Replication with Direct Connect Middleware
• Replicating from Oracle to a central look-up
  point.Killed on data-ownership issues, as well
  as security/access control concerns
• From Informix to outside parties, do the lookups,
  via Direct connect to Oracle, and Adabas
  (through SNI) and replicate back.Killed on
  security concerns, and secure network access
  issues,also based on hosting and ownership
  issues.Ownership access to the red network.

10
Possible Technical Solutions
Mixing Replication with Java in the Database

• The other Potential solutions we considered
• Replication from Adabas
• Special Mainframe Replication Agent
  (assembler-based) - built in South Africa.
• Hooks seamlessly into Replication Server, like
  any other replication agent.
• Allows us to keep a separate copy of the data,
  for access Killed on data-ownership, and legal
  issues,

11
Possible Technical Solutions
Mixing Replication with Java in the Database

• The other Potential solutions we considered
• Process integration - New Era
• Define Processes spanning different systems, and
  have a central distributed process defined to
  access data.
• Could use web-services as part of the
  work-flow.Security concerns addressed. A bit of
  an overkill for the simplicity of the
  problem.This was a definite option, though.

12
Possible Technical Solutions
Mixing Replication with Java in the Database

• The other Potential solutions we considered
• Messaging integration outside the firewall
• Web-based, secure technology, aimed at
  mass-deployment to business partners.
• Easy to use, and deploy to a large client/partner
  network.
• Handles fast-changing environments well, version
  control etc.
• Well-suited for the B2B space, and large value
  chains.This solution is aimed at three sites,
  which will remain fairly static.Security (once
  more) was not a problem, though.Community very
  small and these products aims at large
  trading-networks.

13
Agenda
Mixing Replication with Java in the Database

• The Business and IT Problem
• The Possible Technical Solutions
• Technologies we Chose
• Sybase Replication.
• Java in the Database.
• Sybase Natural Interface (SNI)
• The Final Solution
• Other uses for this Technology
• Conclusion

14
Technologies we chose
Mixing Replication with Java in the Database

• Sybase Replication Server
• Trusted, familiar technology
• Secure Access to External Data remained a
  headache.
• Replication Server can execute a custom
  queries(through a function string, which queries
  internal/external data)
• An insert in Informix can be transformed into an
  update, which, with a function-string lookup can
  update the very same row inserted into Informix.
• Problem remains on HOW to get to the remote data,
  given the hosting, cost, and security issues.

15
Technologies we chose
Mixing Replication with Java in the Database

• Replication agent for Informix
• Trusted technology, at the Problem-Owner. (No
  hosting problems, or ownership issues)
• Sniffs relevant data inserts which forms part of
  the registration process.
• Passed all changes to Replication Server.

16
Technologies we chose
Mixing Replication with Java in the Database

• Apache
• HTTPS web-site that queries Oracle via CGI at
  Police Services. (Apache hosts this from inside
  the red-network)
• Could do the same at the Adabas site.
• Need a certificate to make secure connection
  using HTTPS.
• Police, as an external agency, needs to host
  Apache along with the programs to be executed
  through CGI.

17
Technologies we chose
Mixing Replication with Java in the Database

• Tomcat
• Used Tomcat from outside world to connect to a
  secure web-site INSIDE the red network, using a
  certificate to authenticate.
• Tomcat is hosted at the Registrar of Companies,
  the Problem-Owner.
• Key exchange may needed to be agreed on.

18
Technologies we chose
Mixing Replication with Java in the Database

• Java in the database
• Read the Whats new in ASE manual. (With
  thanks to Rudie Rudie_at_sybase.co.za )
• With java.net ASE allows one to create
  client-side networking applications within the
  server.
• One can create a network java client
  application,that connects to any server from
  within ASE,enabling one to- Download documents
  from an URL.- Send e-mail messages from inside
  the server.- Connect to an external server to
  save or edit a document.- access documents using
  XML.
• Sybase supports certain java.net classes and
  provides examples that allows one to set up
  java.net as either a client, or a server process.

19
Technologies we chose
Mixing Replication with Java in the Database

• . Java in the database
• ASE supports Java .net (12.5 later)
• A Query causes a java class to be
  executedselect poc.dti.ase.URLReader.checkId("
  http//158.76.48.211/cgi-bin/t2.sh?", "5554")
• A Java class running in the ASE server-space.
• Makes a socket connection to the outside world,
• Needed Tomcat for Secure socketsUsed right next
  to ASE for secure communication to Apache
• Any user with permissions on this table can
  execute a java-query, passing parameters, and
  getting a status back.
• This enables any ASE user (including Replication
  Server) to effectively run web-queries through
  the database.

20
Technologies we chose
Mixing Replication with Java in the Database

• Meeting the Security Requirements
• Java Class inside ASE executes based on query,
• Calls another Java Class in Tomcat, (which makes
  a trusted connection to Apache inside the red
  network)
• Apache authenticates the request,
• Apache passes the parameters to CGI gateway
  which
• Calls an executable which Queries Oracle (on the
  Mainframe), returning a status.
• Code Sampleselect poc.dti.ase.URLReader.checkId
  ("http//158.76.48.211/cgi-bin/t2.sh?",
  "5554")The two text parameters are combined to
  create the complete URL http//158.76.48.211/cgi-
  bin/t2.sh?5554

21
Technologies we chose
Mixing Replication with Java in the Database

• SNI explained
• SNI is a framework, using Sybase technologies,
  which- Enables access to Mainframe Adabas,
  through Natural programs.- Presents data as if
  it originated from a Client-Server data source.-
  Allows us to present a Natural Program as a
  table.
• Client connects to the Database as normal.
• Client issues an RPC to Net Gateway or the MVS DC
• DC Invokes a CICS transaction
• CICS transaction has an Assembler stub behind
  it- invokes the Natural Nucleus - passes
  control to a Standard Natural Program that we
  provide.

22
Technologies we chose
Mixing Replication with Java in the Database

• . SNI explained
• Our Standard Natural Program retrieves a number
  of standard parameters- including the name of
  the user-specified Natural program as well as-
  any standard user parameters (e.g. a customer
  number etc).
• The User Natural program runs as normal,
  however- we provide standard output areas, as
  well as- Natural code that allow data to be
  returned simply easily.
• This is done by calling a COBOL stub, which
  contains all the Open Server calls.-
  Users/developers is unaware of this. (They
  simply make the calls, executing the Natural
  Code).

23
Agenda
Mixing Replication with Java in the Database

• The Business and IT Problem
• The Possible Technical Solutions
• Technologies we Chose
• Sybase Replication.
• Java in the Database.
• Sybase Natural Interface (SNI)
• The Final Solution
• Other uses for this Technology
• Conclusion

24
The Final Solution
Mixing Replication with Java in the Database
Police
Oracle Database(Mainframe)
Registrar of Companies
Informix Database
Co registration Application
Sybase Agent for Informix
Web Server Query, through CGI to SAPS Database
Sybase Replication server
Government network
Java .net
Sybase Adaptive Server Enterprise
25
The Final Solution
Mixing Replication with Java in the Database
Police
Oracle Database(Mainframe)
Registrar of Companies
Informix Database
Co registration Application
Government network
Sybase Agent for Informix
Web Server Query, through CGI to SAPS Database
Sybase Replication server
Java .net
Department of Home Affairs
Sybase Adaptive Server Enterprise
Sybase call to Adabas through Web-interface /
Sybase Natural Interface
Component Integration Services
AdabasDatabase
26
The Final Solution
Mixing Replication with Java in the Database

• Advantages of the proposed solution
• Meets the stringent security demands.
• Extremely Cost Effective.
• Low on hosting and maintenance demands.(Addressin
  g Cost of Ownership Concerns).
• Adequate performance for the expected volumes.
• Short Period of Time taken to implement a Proof
  of Concept.
• POC showed Client Concerns about scalability was
  unfounded.The proposed architecture can handle
  the volumes this agency can generate.
• Disadvantages of the proposed solution
• Would still have to install some software
  components at the external agencies.(One could
  argue this was inevitable)
• Cutting edge solution, not a familiar combination
  of technologies.

27
Agenda
Mixing Replication with Java in the Database

• The Business and IT Problem
• The Possible Technical Solutions
• Technologies we Chose
• Sybase Replication.
• Java in the Database.
• Sybase Natural Interface (SNI)
• The Final Solution
• Other uses for this Technology
• Conclusion

28
Other uses for this Technology
Mixing Replication with Java in the Database

• Access to non-Sybase databases.
• Load JDBC driver in ASE java engine.
• Adjust memory for Java Services (See addendum)
• Read-Access non-Sybase database-server achieved!
  Cheap Fast Simple
• Write-access can also be achieved, but beware,
  this process removes the transactional nature of
  external data-access. It can be done, though.
• Our performance testing was adequate to good.

29
Other uses for this Technology
Mixing Replication with Java in the Database

• Access to Flat Files.
• Load JDBC driver for flat-files in ASE java
  engine.
• Same as before.
• Beware performance
• Access to any other external Programmatic logic
• Nothing new, XP-server has been around for a
  while.
• This can now be done directly by loading the
  class,
• Or by making an external Class call from a loaded
  class.

30
Agenda
Mixing Replication with Java in the Database

• The Business and IT Problem
• The Possible Technical Solutions
• Technologies we Chose
• Sybase Replication.
• Java in the Database.
• Sybase Natural Interface (SNI)
• The Final Solution
• Other uses for this Technology
• Conclusion

31
Conclusion
Mixing Replication with Java in the Database

• With the openness of Sybase ASE, one can do
  things deemed to be very difficult / impossible.
• We can customize the database from inside, to
  perform externally specified logic.
• A simple select can now become - a
  parameterized execution call to external
  programs, - a web-page access, - file access,
  - or external database access.
• One can circumvent limitations placed by the JVM
  inside ASE, by calling external classes.
• This custom logic remains Multithreaded, so
  performance is pretty good.
• Do test, and use with care, to ensure performance
  is not compromised.

32
Conclusion
Mixing Replication with Java in the Database

• ASE, along with Replication Server has expanded
  horizons
• Unparalleled Flexibility, Productivity,
  Simplicity.
• We Could meet stringent Cost requirements, adding
  maximum value to clients
• The technology works as published
• This Open Architecture allows one to extend and
  customize your replication solution beyond the
  database, with virtually no boundaries.

33
Questions
Mixing Replication with Java in the Database
34
Addendum - Background
Mixing Replication with Java in the Database

• To query the classselect poc.dti.ase.URLReade
  r.checkId("http//158.76.48.211/cgi-bin/t2.sh?",
  "5554")/ Where the the two text parameters are
  combined to create the complete URL e.g.
  http//158.76.48.211/cgi-bin/t2.sh?5554 /
• To install the classinstjava -Usa -P -f
  DTIAse.jar -update

35
Addendum Code extract
Mixing Replication with Java in the Database

• /
• URLReader.java
• Created on April 8, 2002, 105 PM
• /
• package poc.dti.ase
• import java.net.
• import java.io.
• / Class for reading a remote URL.
• Intended for POC - to check ID for crimial
  record against remote web site.
• _at_author Jimmy Myerscough
• /
• public class URLReader
• / Creates a new instance of URLReader /
• public URLReader()

36
Addendum Code extract
Mixing Replication with Java in the Database

• / Uses a proxy servlet to connect to an HTTPS
  site.
• _at_param secureUrl Full URL to secure site.
• Include all parameters except the IDNo
  itself. e.g. https//securesite.com443/DTI/IDChe
  cker?IDNo_at_param idNo ID No to check.
• This is appended to the secureUrl. _at_param
  proxyUrl Full url to the proxy servlet.
• Include all parameters, except the remote
  URL.
• e.g. http//localhost8089/DTI/Proxy?Remote
  URL _at_throws Exception An error occurred _at_return
  The string returned by the secure URL.
• May contain error messages in stead
• /
• public static String checkId(String secureUrl,
  String idNo, String proxyUrl) throws Exception
• String url proxyUrl encodeUrl(secureUrl
  idNo)
• return checkId(url, "")
• / Reads content of a URL.
• _at_param url Full URL to site.
• Include all parameters except the IDNo
  itself.
• e.g. http//remotesite.com8080/DTI/IDChecke
  r?IDNo _at_param idNo ID No to check.
• This is appended to the secureUrl. _at_throws
  Exception An error occurred _at_return The string
  returned by the remote URL.

37
Addendum Code extract
Mixing Replication with Java in the Database

• StringBuffer sb new StringBuffer()
• int n 0
• n in.read()
• int i 0
• for (i 0 n ! -1 n in.read() )
• sb.append((char) n)
• String ret sb.toString()
• return ret
• / Reads content from http//jimmy8080/DTI/I
  DChecker?IDNo
• _at_param idNo ID No to check.
• This is appended to the secureUrl.
• _at_throws Exception An error occured
• _at_return The string returned by the remote
  URL.

38
Addendum Server Configuration
Mixing Replication with Java in the Database

• Selected Configuration parameters for ASE
• Java Services
• enable java 1
• size of process object heap 10000
• size of shared class heap 10000
• size of global fixed heap 10000
• number of java sockets 20
• enable enterprise java beans DEFAULT

39
The end.
Mixing Replication with Java in the Database