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@sybase.co.za & jimmy@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 “What’s new in ASE” manual…. (With thanks to Rudie Rudie@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 executed:select 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 Sample:select 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.(Addressing 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 class:select poc.dti.ase.URLReader.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 class:instjava -Usa -P -f DTIAse.jar -update

35. Addendum – Code extract Mixing Replication with Java in the Database /* * URLReader.java * * Created on April 8, 2002, 1:05 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. * * @author Jimmy Myerscough */ public class URLReader { /** Creates a new instance of URLReader */ public URLReader() { } /* public static String checkIdSSL(String url, String idNo) throws Exception { System.out.println("net Security Debug(old)=" + System.setProperty("javax.net.debug","all")); System.out.println("Security Debug(old)=" + System.setProperty("java.security.debug","all")); System.out.println("Original handler = " + System.setProperty("java.protocol.handler.pkgs","com.sun.net.ssl.internal.www.protocol")); java.security.Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider()); System.out.println("SecureRandom="+ new java.security.SecureRandom()); return checkId(url, idNo); } */

36. Addendum – Code extract Mixing Replication with Java in the Database /** Uses a proxy servlet to connect to an HTTPS site. * @param secureUrl Full URL to secure site. * Include all parameters except the IDNo itself. e.g. https://securesite.com:443/DTI/IDChecker?IDNo=@param idNo ID No to check. * This is appended to the secureUrl. @param proxyUrl Full url to the proxy servlet. * Include all parameters, except the remote URL. * e.g. http://localhost:8089/DTI/Proxy?RemoteURL= @throws Exception An error occurred @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. * @param url Full URL to site. * Include all parameters except the IDNo itself. * e.g. http://remotesite.com:8080/DTI/IDChecker?IDNo= @param idNo ID No to check. * This is appended to the secureUrl. @throws Exception An error occurred @return The string returned by the remote URL. * May contain error messages in stead */ public static String checkId(String url, String idNo) throws Exception{ URL u = null; Reader in = null; try { u = new URL(url + idNo); in = new InputStreamReader(u.openStream()); } catch (Exception e) { e.printStackTrace(); return "BAD_URL : " + e.getMessage() + e; }

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://jimmy:8080/DTI/IDChecker?IDNo= * @param idNo ID No to check. * This is appended to the secureUrl. * @throws Exception An error occured * @return The string returned by the remote URL. * May contain error messages in stead */ public static String checkId(String idNo) throws Exception { return checkId("http://jimmy:8080/DTI/IDChecker?IDNo=", idNo); } /** Encodes a string for submission in a URL. * @param url String to encode. * @return URL encoded string. */ public static String encodeUrl(String url) { return java.net.URLEncoder.encode(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