EDU 213 Replication Server: Heterogeneous Replication Bill Zhang, Sr. Product Manager, Sybase Inc. Javier Cuerva, Sr. Software Architect, Sybase Inc. Mark Mumy, Principal Consultant, Sybase Inc. Slide title goes here and can be one line or two. Arial 30 pts. First level text and bullet Arial 25 pts • Second level text and bullet Arial 22 pts – Third level text and bullet Arial 20 pts – Please use green to highlight important text. Agenda Section # Time Topic Format Presenter 1 1:00 pm – 2:00 pm Overview of Replication Server and Replication Server Oracle Option Presentation Bill Zhang 2 2:00 pm – 3:15 pm Step-by-step demonstration of Oracle to Oracle, Oracle to Sybase replication and Replication Server Application design with Sybase PowerDesigner Presentation/Demo Javier Cuerva 3:15 pm – 3:30 pm Break 3 3:30 pm – 5:00 pm Replication into Sybase IQ from heterogeneous data sources Presentation/Demo Mark Mumy 4 5:00 pm – 6:00 pm Real Time Data Services: heterogeneous database events replication with Messaging Bus Presentation/Demo Javier Cuerva Section #1 Replication Server 15 Overview Replication Server Oracle Option 12.6 Overview Replication Server Roadmap Replication Server 15.0 Data Movement Sybase Replication server performs realtime data movement while providing: • • • • Replicate Targets Non-intrusive transaction capture Flexible transformation of data Efficient routing across networks Real-time synchronization across heterogeneous databases • ASE • SQl Anywhere • Sybase IQ • • • • • Primary Sites Data Sources • ASE • SQL Anywhere • Oracle • Microsoft SQL Server • DB2 UDB (Unix, NT, OS/390) • OS/390 (IMS, VSAM) Replication Agent Replication Server RTDS Oracle Microsoft DB2 AS/400 ODBC • TIBCO • JMS • MQSeries Replication Server High Availability Disaster Recovery Customer Data Synchronization Data Consolidation Data Distribution Legacy Data Migration and Synchronization New Application Deployment Real-time Data Mart/Warehouse Population Reporting Servers Product Usage: Disaster Recovery OpenSwitch Site 1: SF (Order Entry) DR Site: Sacramento WAN OLTP Application ASE Rep Server • Disaster Recovery • Business Continuity ASE Standby Product Usage: Data Distribution Site 2: New York (Sales) Rep Server Site 1: San Francisco (Order Entry) Oracle Sales Decision Support Site 3: San Francisco (Finance) WAN OLTP Application • • • • ASE Rep Server Real time Data Mart Reporting Servers Application Deployment Legacy Data Migration ASE Finance Reporting Site 3: Dallas (Manufacturing) Rep Server DB2 Decision Support Product Usage: Data Consolidation Site 2: Seoul (Production) Seoul Application Oracle Rep Server Site 1: Frankfurt (Production) Frankfurt Application ASE Rep Server Site 4: Boston (HQ) Boston Reporting Application Rep Server ASE Site 3: Mexico City (Production) Mexico Application DB2 Rep Server • Reporting Servers • Real time Data Warehouse • Application Deployment Product Usage: Data Synchronization Site 1: New York (Customer Service System) CSS Application Oracle Rep Server Rep Server Site 2: Dallas (Web Site) Web Application Oracle Site 2: Dublin (Engineering Bug Tracking System) Rep Server DB2 Bug Tracking Application • Customer Data Synchronization • Distributed geographic apps • Distributed App over multiple systems Benefits of Replication Generic Benefits Sybase Strengths Replication to secondary can be done, regardless of network outages (due to local storage of transactions) High Performance Data Distribution and Consolidation (via batch tuning, SMP) Independent of Central Server downtime – as exact replica of primary is available on secondary Central management of distributed data Flexible Transformations of primary data, before being applied to secondary Centralized Administration of complex deployments Distributed Architecture with guaranteed delivery Bi-directional synchronization across heterogeneous data sources Platform and Database independent Ability to compare data values across replicate sites RS 15 Features REQUIREMEN T NEW FEATURE ADVANTAGE Integrated from Top to Bottom PowerDesign er Modeling Configuration integrated with PowerDesigner Systems Management Full Functionality through Sybase Central Java Plug-in Admin & Troubleshooting Assistance Health Monitoring Monitor the availability of the servers and the state of the connections and routes RS 15 Features • PowerDesigner Modeling RS 15 Features • Sybase Central Java Plug-in RS 15 Features General Tab Health View RS 15 Features Inbound Tab Health View RS 15 Features REQUIREME NT NEW FEATURE ADVANTAGE Support of ASE 15.0 Features Computed Columns Support replication of materialized computed columns as source and target Data Partitioning Support of DDL and DML replication of ASE partitioned tables Long Identifiers Support for increased identifier max length from 30 bytes to 255 bytes New Datatypes Support for Unitext and Big and Unsigned Integers RS 15 Features REQUIREME NEW FEATURE NT ADVANTAGE Maintain Performance Batch Serialization Optimal Transaction Delivery Solution Replication Flexibility ERSSD Routing Support Routes from a RepServer with ERSSD to a RepServer with ERSSD Routes from a RepServer with ERSSD to a RepServer with ASE RSSD System Data Replication User and Role Replication Logins automatically synchronized between primary and replicate RS 15 Features REQUIREME NEW NT FEATURE ADVANTAGE Security EAL2 Common Criteria EAL2 Certification FIPS Support Certified AES Algorithm using 128-bit Encryption Key Expanded Queue Size Large disk can be used as one partition Simplified Administratio n Customer Success Story: Major Credit Card Issuer Oracle Customer Care Application WAN ASE DB2 Rep Server ASE Rep Server PR Site • Call Center has to support over 40 millions credit card customers Customers seek immediate assistance when: requesting new cards, increasing credit limits, and applying for loans Database availability required for service rep to handle request Sybase Replication Server synchronizes customer data • • DR Site Call center needs database availability • • Standby ASE Collects data from heterogeneous sources Stores customer info in Sybase ASE. Warm standby for HA Achieved 99.95% database availability • • • Which contributed to customer acquisition and retention The warm standby ensures fast failover, limits latency, and enables geographic separation The ROI of replicating customer data across customer-facing applications has become clear in terms of customer satisfaction and retention Customer Success: GSA Advantage OpenSwitch e-Commerce Customers WAN ASE Sterling, VA Rep Server Standby ASE Chicago, IL • Hosting the world’s largest e-commerce site, GSA requires non-stop operations, servicing customers around the globe. Stats: • 11 million products and services offered • 2,500 orders/day @ $724/order • 500,000 registered users • Sybase solution: • ASE, Rep Server, Open Switch • Primary site in Sterling, VA – Secondary Site in Chicago, IL • Results: • Fallover to second site transparent to users – occurs within seconds Competitive Advantages of RS 15 Replication Server: Sybase heterogeneous replication supports sophisticated enterprise-level distributed environments with highperformance, low overhead, scalable solutions Known as a cost-effective technology solution with WallStreet class performance and reliability Replication capabilities span DR, HA, reporting, data consolidation, data synchronization and data integration Broad set of tools, value-adds for replicated environment (event delivery, archiving, auditing, etc.) Strong replication customer base Replication Server Oracle Option 12.6 Heterogeneous Replication • Sybase Replication Server supports multiple databases (Oracle, DB2, SQL Server) – with large numbers of customers in production • Customers prefer one vendor for all their replication needs • Same product can be deployed for replication for ASE as well as other databases • Oracle • Log Based • Supports Oracle 9i, with 10g/RAC on roadmap DB2 • Log Based • Supports V7, with upcoming support for V8 SQL Server • Trigger based. Log based solution on roadmap, which will support SQL Server 2005 • Supports SQL Server 2000 Heterogeneous Replication New releases for heterogeneous replication: 12.6 Log-based Sybase Replication Server Oracle Option Log-based Mirror Activator for Oracle • Extends Sybase well-known replication strength and performance capabilities for mixed environments • High demand from market for reliable, heterogeneous replication solutions • Fundamental element of Data Services solutions Sybase Replication Server Oracle Option 12.6 Sybase has supported Oracle replication for over 10 years • Has been trigger-based Replication Server Oracle Option 12.6 is log-based • Better performance • Reduce impact on primary Supports Oracle 9i (9.0.1 and 9.2.0) on: • • • • • Solaris 2.8, 2.9 Windows 2000, 2003 AIX 5.1, 5.2, 5.3 HP-UX 11i RedHat Linux Enterprise 2.1, 3.0 Major Features, Sybase Replication Server Oracle Option 12.6 Supports Oracle 9i • 10g and RAC in product roadmap Supports Stored Procedure Replication • Key differentiator for solving performance issues Supported by PowerDesigner for Integrated Modeling • Integrated modeling of database and replication environments Supports DDL replication Heterogeneous Replication Success Story A Major Travel Service Provider Travel Franchisee Logistics Applications Central Reporting Applications Oracle Oracle Replication Server Primary DB2 on Mainframe Replicate Travel service provider needs data integration to improve customer satisfaction • Legacy implementation was a manual script-driven batch process. Logistics information updated local agencies weren’t captured and delivered to Central site. Hence, customers weren’t able to get accurate information on time. Sybase Replication Server synchronizes logistics data in heterogeneous environment • Collects changed data from heterogeneous sources • Replicate in real-time to other heterogeneous targets Achieved real-time data integration • This solution enables this customer to provide better services and improve customer satisfaction, which contributed to customer acquisition and retention One Architecture For Data Movement and Disaster Recovery Increased requirement to support multiple databases: Mergers, acquisitions, 3rd party applications, legacy applications Need to staff DBA skills for all databases in environment Data movement and disaster recovery efforts require a different set of DBA skills If data movement or disaster recovery solutions are the same across databases then only one set of skills is required Numerous sites currently use Replication Server and have these skills Replication Server skills are easily extended to replicating other databases Replication Server 15 Packaging • Base – Replication to and from Sybase ASE databases Replication Server Base Package • Heterogeneous Replication Options – Each option enables replication to and from the corresponding database Replication Server Options Package Replication Server Base Server License • Replication Server Manager • Sybase Central Plug-in for Replication Server • Advanced Security (SSL) Option for Oracle Option for Microsoft Option for DB2/UDB RA RA RA DC DC DC Summary • Sybase Replication Server is used by ASE and non-ASE customers in mixed database environments in production – with thousands of customers worldwide • Typical usage of Sybase Replication Server is in financial services (for trade processing), healthcare, telecom and public sector • Replication Server provides capabilities for: • • • • • High Availability Disaster Recovery Reporting Data Distribution Master Data Management • Sybase is continually enhancing replication server to provide enhanced capabilities: Support for all current versions of major databases Continuing to make management and performance in large environments easier Supporting use of replication in data integration Working with third party development and management tools vendors, including in-house development products like PowerDesigner • Enhance data comparison capabilities in mixed database environments • Provide continuous data protection capabilities • • • • Replication Server Roadmap Replication Server Roadmap Replication Agent UDB 15.0 UDB V8 (Q4, 2006) Replication Server 15.0 RA MSSQLServer 15.0 (2007) (Q1, 2006) •Replication Modeling •Monitoring & Management Tools •ASE 15.0 Support for Partitions, Data Types, Long Ids, Rep Agent Oracle 15.0 10g (Q4, 2006) Replication Server 15.0.1 Replication Server 15.1 Manage at logical level and ASE support (2007) Performance (Q3, 2006) Rep Agent Oracle 15.1 RAC (Q2, 2007) Q1 ’06 Q3 ’06 Q1 ’07 Oracle Replication Sybase Confidential Proprietary. RAO 12.6 Sybase has supported Oracle replication for over 10 years • Has been trigger-based Rep Agent for Oracle 12.6 is log-based • Better performance • Reduce impact on primary • Basis for Mirror Activator for Oracle RAO 12.6 Supports Oracle 9i (9.0.1 and 9.2.0) – Oracle RAC and 10g in product roadmap available in RAO 15 Supports Stored Procedure Replication – Key differentiator for solving performance issues – Unique in the market to do so Supports DDL replication Supports BLOB replication Supports UDD replication Supports Oracle Sequence replication ( latest ESDs ) RAO 12.6 at a glance Log Based • No need for a “virtual” log based on tables • No need for triggers to populate those tables All JAVA written, requires JRE 1.4.2 – JRE bundled in RAO – Requires Oracle JDBC Driver 9.2.0.5 for JDK 1.4 Not shipped with the product ! Embedded ASA, known RASD Compatible with: – RS 12.5, RS 12.6 and RS 15.0 Prior RS 12.5 will truncate LOB data-types to 2G and CHAR, VARCHAR2, RAW to 255 characters Prior RS 12.5 will replicate NCHAR and NVARCHAR as CHAR and VARCHAR (ASCII not Unicode) – jConnect/jTDS version 6.0 EBF 12723 – ECDA 12.6.1 (for LOB replication) RAO 12.6 at a glance, connectivity RASD Not Displayed: Each RAO instance has a configuration file RAO 12.6 at a glance, RASD RASD = Replication Agent System Database Started as a personal server ( dbeng8.exe ) • • ASA name same has the instance name Started at instance name port +1 Initially empty until the rep agent is initialized (pdb_xlog init) Holds the metadata about the primary database • • • Database schema Objects marked for Replication (pdb_setrepproc, pdb_setreptable, pdb_setrepcol, pdb_setrepseq) Contains versions when a DDL command modifies the database schema, to replay transactions that involved objects before the change made by the DDL command Size of the RASD depends on the number of objects marked for replication Every instance of the RAO has its own RASD Information stored in ASA not relevant outside the Rep Agent Internal ASA schema subject to change for BETA / GA / EBF • Building a new RAO instance is required, do not reuse the .db/.log files Administration command via RAO • rasd_backup, rasd_restore, ra_truncatearticles, ra_truncateusers RAO 12.6 at a glance Read directly from Oracle’s redo log files Replication Agent must be installed on a machine from which it can directly access the redo log files (same host or host with shared access) • This the only requirement RAO will perform the log archiving as data in redo logs is replicated. RAO 12.6 at a glance, components and flow LTI: generates LTL for Replication Server LTM: manages all the components and interactions Log Reader: Reads the transaction log in the primary database to retrieve txs RAO 12.6, Oracle Configuration, part 1 Oracle configuration needed to work with RAO Oracle Archiving log must be on: • Run: alter database ARCHIVELOG ; • Verify: select log_mode from v$database; Automatic Archiving must be disabled: • Run: alter system set log_archive_start=false scope=spfile; • Verify: show parameter log_archive_start; Restart the Oracle Server RAO truncates the ORACLE log • • • • Depends on ra_config truncation_type interval | command Automatic: ra_config truncation_interval, a_value_in_minutes( 0..720) Manual: pdb_truncate_xlog Sends the Oracle command: alter system archive log RAO 12.6, Oracle Configuration, part 2 Tell Oracle to log primary keys • Run: alter database add SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS; • Verify: select SUPPLEMENTAL_LOG_DATA_PK from V$DATABASE; RAO 12.6, Oracle Configuration, part 3 RAO User Add an Oracle user for the RAO to connect to the primary DB: • create session • select_catalog_role • alter system • alter any procedure, create procedure • create public synonym, drop public synonym • create table • execute on DBMS_FLASHBACK • select on SYS.OBJ$ • select on SYS.LOB$ • select on SYS.COLLECTION$ • select on SYS.COL$ RAO 12.6, Creating a RAO instance A RAO server is called an instance N instances can be created in a machine: • Instance name must be different • Instance port must be different One instance has one type (here Oracle, could be also UDB, MSSQL, Informix) An instance is created via: • Command line – Ex: ra_admin –c <instance_name> -p 10000 –t oracle • GUI – Run the administrator command in the RAX\bin directory RAO 12.6 DDL Replication Only available for Oracle, the other RepAgents do not replicate DDL Must use Oracle as the replicate database, cannot replicate DDL from ORACLE to NON-ORACLE DB Must define a new Oracle user • Not the maintenance user / Not the Replication Agent user • Has to be created in the replicated database • Identified in RAO with ddl_username and ddl_password – Has to have the following permissions: ALTER SESSION, RAO sends this commands as the first command for every DDL transaction ALTER SESSION SET CURRENT_SCHEMA=user, where user is the user that generated the DDL command at the primary Permission on rs_info and rs_lastcommit Permission to create any DDL commands ( create / drop… ) RAO 12.6 DDL Replication To replicate DDL in oracle, use: pdb_setrepddl [enable | disable] Replication Server must have a database level replication definition with replicate DDL set in the definition. create database replication definition whatever with primary at rao_instance.ora_instance replicate DDL replicate tables replicate functions create subscription orardb_sub for database replication definition whatever with primary at rao_instance.ora_instance with replicate at DCO_ORACLE.orardb without materialization RAO 12.6 DDL Replication DDLs commands not replicated: • • • • • • • • • • • • • • • • • • • • create / alter / drop tablespace alter database create / drop database link alter session Create / alter / drop snapshot Create /alter / drop snapshot log alter create / drop / rollback segment alter system switch log create control file create pfile from spfile create schema authorization create spfile from pfile explain lock table rename set constraints set role set transaction analyze Audit / no audit RAO 12.6 DDL Replication Objects not replicated: • Any Object owned by SYS • Any objects owned by users defined in the list of non replicated users ( see pdb_ownerfilter ) RAO 12.6 Stored Procedure Replication Behaves similarly to the trigger based implementation in RAX 12.5. Proc-Active and shadow tables are created on the primary database to support stored procedure replication ( Oracle does not log stored procedure execution) Primary stored procedure is instrumented during marking of the procedure to be able to update the proc-active/shadow table. Use pdb_setrepproc to mark/enable or unmark/disable primary procedure for replication (caution DDL replication must be disabled before running the command) To be able to mark an Oracle procedure or function, it must be a standalone. The Oracle procedure or function cannot be part of a package. RAO 12.6 Stored Procedure Replication Stored Procedure Example ( this is what you type) CREATE PROCEDURE ADD_EMPLOYEE ( PEMP_ID NUMBER … ) AS BEGIN /* whatever Oracle SQL */ END ; In RAO run pdb_setrepproc ADD_EMPLOYEE, mark go pdb_setrepproc ADD_EMPLOYEE go Owner Name Marked Enabled Replicate Send Owner Shadow Table Shadow Row Proc Blob Shadow Table Blob Row Proc -------- ------------- ------- -------- ------------- ---------- ------------ --------------- ----------------- ------------ RA_USER ADD_EMPLOYEE marked enabled ADD_EMPLOYEE disabled RA_SH_K N/A N/A N/A RAO 12.6 Stored Procedure Replication Stored Procedure modified by RAO 12.6 CREATE PROCEDURE ADD_EMPLOYEE ( PEMP_ID …) AS /**** BEGIN SYBASE REPLICATION CODE ***/ RA_CSEQ__ NUMBER; RA_SPOBJID__ NUMBER; RA_SHOBJID__ NUMBER; BEGIN select RA_PCALL_A.NEXTVAL into RA_CSEQ__ from DUAL; select OBJECT_ID into RA_SPOBJID__ from ALL_OBJECTS where OWNER = 'RA_USER‘ and OBJECT_NAME = 'ADD_EMPLOYEE‘ and 'PROCEDURE'; select OBJECT_ID into RA_SHOBJID__ from ALL_OBJECTS where OWNER = 'RA_USER‘ and OBJECT_NAME = 'RA_SH_K‘ and OBJECT_TYPE = 'TABLE'; …to be continued OBJECT_TYPE = RAO 12.6 Stored Procedure Replication Stored Procedure modified by RAO 12.6 INSERT INTO RA_USER.RA_PROCACTIVE_A VALUES( RA_CSEQ_,USERENV('SESSIONID'), 'RA_USER','ADD_EMPLOYEE',RA_SPOBJID__,'RA_SH_K',RA_SHOBJID__,2 ); INSERT INTO RA_USER.RA_SH_K VALUES(PEMP_ID,PEMP_FIRSTNAME,PEMP_LASTNAME, PEMP_STATUS,PEMP_DEPTID, RA_CSEQ__ ); /**** END SYBASE REPLICATION CODE ***/ Your SP CODE /**** BEGIN SYBASE REPLICATION CODE ***/ END; DELETE FROM RA_USER.RA_SH_K where RA_CALLSEQ__ = RA_CSEQ__; DELETE FROM RA_USER.RA_PROCACTIVE_A where RA_CALLSEQ__ = RA_CSEQ__; EXCEPTION WHEN OTHERS THEN ROLLBACK; DELETE FROM RA_USER.RA_SH_K where RA_CALLSEQ__ = RA_CSEQ__; DELETE FROM RA_USER.RA_PROCACTIVE_A where RA_CALLSEQ__ = RA_CSEQ__; COMMIT; RAISE; END ; RAO 12.6 Binary Object Replication Oracle LOB datatype support: • Character: – LONG: 2G field Max, 1 per table (deprecated in Oracle 9/ for compatibility), not possible to partition the table (text) – CLOB: ASCII, max 4G (text) – NCLOB: UNICODE, max 4G (text) • Binary – LONG RAW: 2G field Max, 1 per table (deprecated in Oracle 9 / for compatibility), not possible to partition the table (image) – BLOB : Binary, max 4G (image) – BFILE : a pointer to a file stored outside the db (image) • Oracle replication cannot replicate LONG / LONG RAW data RAO 12.6 Binary Object Replication Mark the table containing the LOB columns for replication using pdb_setreptable table If pdb_dflt_column_repl is set to true then marking the table will mark all the columns for replication Use pdb_setrepcol to mark LOB columns for replication, if pdb_dflt_column_repl is set to false ( the default) pdb_setrepcol table, column, enable | disable LOB Replication Depending on the size and storage configuration of the LOB, the data is either logged in-row or off-row via the Oracle “disable storage in row” Data logged in-row is logged in the same log record as all other non-LOB column data. Data logged off-row is logged in a separate log record, and a pointer is logged in the row with the non-LOB column data LOB Replication LOB data may be changed without updating other column data • Updates to off-row LOBs via DBMS LOB package • No supplemental logging included for LOB updates in Oracle 9.0 (fixed in 9.2) • Example: – – – – Update T set reg_col2 = 2 where reg_col1 = 1 Reg_col1 and reg_col2 are recorded in the redo log Update T set lob_col = “something” where reg_col1 =1 Only lob_col is recorded in the redo log -> no way to reconstruct the where clause In these cases, RAO requires that a LOB update be directly preceded or followed by a non-LOB update to the same row (insert or update ) • Update T set reg_col2 = 2 where reg_col = 1 <- to execute after the update of the LOB column • Commit; • There is no way for RAO to force compliance LOB Replication CLOB, NCLOB and BLOB data logged off-row is logged prior to the in-row column data in separate log records RepServer requires that the in-row column data be sent first, so LogReader skips off-row LOB data until it sees the in-row column data, and then re-reads the off-row LOB data LOB Replication LONG and LONG RAW LOB types are stored in-row like non-lob data Only one column of type LONG is allowed per table Since LONG data is always logged as the last column, no matter its actual column position, the article definition is altered to reflect this behavior when it is stored in the repository Otherwise, LONG data is treated like any other in-row LOB LOB Replication BFILE data is stored external to the database and only a pointer to the file is logged, not the data itself Handled in a similar way as trigger-based solution, when a BFILE column is changed, RAO queries the primary database for the data • Selecting the BFILE data separate from other data in the redo log can provide a temporary out-of-sync condition if the BFILE contents are changed multiple times. RAO 12.6 UDD Replication User Defined Data types use ORACLE built-in data types and other user data types • CREATE TYPE customer_typ_demo AS OBJECT ( customer_id NUMBER(6) , cust_first_name VARCHAR2(20) , cust_last_name VARCHAR2(20)) UDD can also have methods associated to it (PL/SQL, C, JAVA) # like a class To replicate UDDs in Oracle: • A special datatype must be created in RS and that datatype must be used as the datatype for any UDD column defined in a replication definition • Choose an un-used datatype in rs_datatype table ( column is dtid ) • In RSSD: Insert into rs_datatype ( …. ‘rs_jc_dt’ …) //this is the name of the datatype • Create replication definition jc_repdef with with primary at ra_source_db.ra_source_ds with all tables named ‘ORACLE_TABLE‘ (PKEY int, PNAME rs_jc_dt) … Sequence Replication Replication Server does not directly replicate sequence type. To support sequence replication, you must: • Install the stored procedure rs_update_sequence on the standby site using the oracle_create_replicate_sequence_proc.sql script. • Install the function replication definition rep_update_sequence for that procedure in Replication Server using the oracle_create_rs_sequence_repdef.sql script. (Scripts reside under Mirror Replication Agent installation’s scripts directory - %SYBASE%\MRO-12_6\scripts) Use pdb_setrepseq command to mark/enable or unmark/disable a primary sequence for replication. RAO 12.6 Non supported Oracle data types Listed of non supported Oracle data types • ORACLE REF – A logical pointer to a row – The oracle way to reduce foreign keys – CREATE TYPE person AS OBJECT ( name VARCHAR2(30), manager REF person ); • ORACLE VARRAY – A Variable Array Each element as an index, All elements of the same type Can be used as a table column type CREATE TYPE prices AS VARRAY(10) OF NUMBER(12,2); • ORACLE NESTED TABLE – An oracle collection – A table column containing another table • ORACLE SUPPLIED TYPES – Oracle types to define other types Any / XML / Spatial / Media types RAO 12.6 Platforms Rep Agent for Oracle supports Oracle 9i on: – Solaris SPARC 2.8, 2.9 – Windows 2000, 2003 – AIX 5.1, 5.2, 5.3 – HP-UX 11 (PA-RISC) – RedHat Linux Enterprise x86 2.1 (32 bits) Kernel version 2.4.9-e.27 or later RPM version 4.0.4 or later – RedHat Linux Enterprise x86 3.0 (32 bits) Kernel version 2.4.21-27.0.2.EL RPM version 4.2.3 Questions ? Real Time Data Services 3.5 Sybase Confidential Proprietary. HOW ARE REAL-TIME DATA SERVICES DELIVERED TODAY Custom Applications -- Complex, Intrusive & Costly Custom Applications MESSAGE BUS Messaging Architectures Share Information Among Multiple Applications in real time Customer Relationship Management Real Time Fraud Detection • New code required to connect databases and applications to message bus • Polling is intrusive and slows down the database HOW WILL SYBASE REAL-TIME DATA SERVICES SOLUTION HELP Sybase RTDS – Simple, Non-intrusive and Code Free MESSAGE BUS SYBASE RTDS Messaging Architectures Share Information Among Multiple Applications in real time Customer Relationship Management Real Time Fraud Detection Legacy Applications No custom coding required to connect to message bus Eliminates need for polling. Proactive and non-intrusive RTDS 3.5 Components. JMS SYBASE RTDS RepConnector 15 EAS 5.2 Replication Server 15 •Replication Server 15 -- Non intrusively capture events from databases •Rep Connector 15 -- deliver events natively to MQSeries, Tibco, JMS •EAServer 5.2 – Runs the Rep Connector JCA. •Compatible with ASE 12.5.4 and ASE 15.0 ESD2 EMPOWERING NEW APPLICATIONS / ASE Messaging / Active Messaging ASE to / from TiBCO-MQ Natively JMS Capabilities SYBASE RTDS ASE 12.5.2 with messaging enabled •Leverage existing triggers •Push events from within client apps •Bi-directional Benefits •Use SQL to generate events •Applications developers don’t need to learn yet another API •High Performance message delivery and archiving ENABLING EXISTING APPLICATIONS / Rep Connector / Passive Messaging Any Database to Any Message Bus SYBASE SYBASE RTDS BILLING DB2 INVENTORY ORACLE Rep Server Rep Connector JMS, MQSERIES, TIBCO Capabilities ORDER ENTRY •Non-intrusive •Heterogeneous Sources •Heterogeneous Message Bus •No Custom Coding Benefits •Eliminate ‘polling’ applications, reduce cost •Be proactively notified when changes happen, without coding •Zero impact on existing applications •Enable multiple applications RTDS Active Messaging, Messaging capabilities in ASE Sybase Confidential Proprietary. RTDS Active Messaging Agenda Messaging Service Overview ASE Messaging Service Native Support New Features in RTDS 3.5 Setup and Configuration Examples Summary MESSAGING OVERVIEW Sybase Confidential Proprietary. Messaging and Queuing Messaging - programs communicate by sending data in messages rather than by calling each other directly. Queuing - messages are put on queues in storage, eliminating the need for programs to be logically connected. A messaging and queuing framework is inherently ASYNCHRONOUS! How Messaging & Queuing Works? Programs communicate by putting messages on queues. Here, program A puts a message on Queue1, which is read by program B. Communication can be one-way or two-way. Here, A sends to B on Queue1, and B responds to A on Queue2 Note: A and B need not be on the same machine! Messaging and Queuing Characteristics Three key facts about Messaging and Queuing differentiate it from other communication styles: 1) Communicating programs can run at different times. 2) There are no constraints on application structure. 3) Programs are insulated from environmental differences. MESSAGING STANDARD Messaging standard (specification) defines the framework and contract used by the applications Messaging standard, just like any, defines core API for use by applications Messaging standard is generally programming language neutral Popular messaging standards • MQI (Messaging and Queuing Interface) – Defined by IBM • JMS (Java Messaging Service) – Part of Java Standard – Derives number of concepts from IBM MQ MESSAGING MODELS Point To Point Any given message can only be read by one application These are known as queues Multiple receivers are allowed, but only one receiver will get the message Publish and Subscribe One to many In JMS these are known as topics In IBM MQ these are known as subjects Publishers posts messages pertaining specific subjects/topics Consumers establish subscriptions to register interest in messages sent to a topic What is JMS? Java Message Service Standardized messaging API • Developed by Sun in conjunction with TIBCO and others • Component of Java 2 Enterprise Edition (J2EE) • http://java.sun.com/products/jms/index.html API only • An API to which vendors can build implementations • No specification of wire format, namespace or access control, etc. • Message portability for bridge-building MESSAGE FORMAT - JMS Three parts to a JMS Message Message Header Message Body Message Properties Message Header Defined by JMS standard, and includes vendor specifics Example: Message-ID Message Body Application data (payload) JMS defines structured types (Stream, Map), and unstructured (Text, Bytes, Object) Message Properties User defined Typed and can be used by receivers to control which messages they are interested in Example: application_name, application_version TIBCO EMS Architecture Sender EMS Client API EMS Client API Receiver Topic1 QUEUE1 Topic2 QUEUE2 Topic3 QUEUE3 EMS Client API EMS Client API JMS message server (tibjmsd) Sender Receiver What is MQ Series? • MQSeries is a middleware product from IBM that runs on multiple platforms and enables applications to send messages to other applications • Basically, the sending application PUTs a message on a Queue, and the receiving application GETs the message from the Queue • The sending and receiving applications do not have to be on the same platform, and do not have to be executing at the same time • MQSeries takes care of all the storage, logging and communications details required to guarantee delivery of the message to the destination queue • In most cases, it will take care of translating the data when the source and destination use different character sets (EBCDIC on MVS vs. ASCII on NT or Unix) • All the applications have to do is know the name of the Queue and agree on the meaning of the message. MQ series Architecture Applications Programmatic API Queue Manager Queue Manager Message Queue Communications using Message Channels ASE MESSAGING NATIVE SUPPORT Sybase Confidential Proprietary. ASE Real Time Messaging Service (overview) Functionality Overview Brings the Messaging (JMS / MQ ) services into ASE using Simple SQL functions in ASE to publish and consume messages to and from a message bus Transform messages into XML format before publishing Supports Point-To-Point and Publish–Subscribe messaging paradigms Preserve transactional semantics across the database and message bus (e.g., if a transaction fails then message won’t be published) Graphical User interface is provided via Sybase Central/ASE Plug-in Only for adding/dropping of the message services providers and registration of subscriptions Workspace 1.5 has support for building the syntax to send and receive messages from both TIBCO and MQ-SERIES. ASE Real Time Messaging Service (Concepts) ASE_MESSAGING License for messaging functions sp_configure “enable real time messaging” Turns on the messaging feature sp_configure “messaging memory” Configures the amount of memory for messaging needs Messaging_role Must be granted to logins that will use the messaging function sp_msgadmin Messaging administration stored procedure Each variant has a command and an optional sub-command sp_msgadmin help will provide more details ASE Real Time Messaging Service (TSQL) Functions related to queues msgsend() – send a message to a queue msgrecv() – receive a message from a queue Functions related to topics msgpublish() – publish a message to a topic msgconsume() – consume a message from a topic msgsubscribe() – start a subscription msgunsubscribe() – stop a subscription Functions related to message properties msgproplist() – returns the properties and their values msgpropname() – returns the name of a property msgpropvalue() – returns the value of a property msgproptype() – returns the type of a property msgpropcount() – returns the number of properties ASE Real Time Messaging Service (Q engine) sp_configure “max online Q engines” (default = 0) • Configures maximum number of ASE engines that can be use for MQ Messaging sp_configure “number of Q engines at startup” (default = 0) • Configures the number of ASE engine that is used for MQ Messaging These are static configuration parameters, the ASE server must be rebooted after setting them. Transaction Support Transactional Behavior, Three Modes • Simple – Messages sent and received are part of the ASE transaction – Rollback in ASE will undo the work at the bus side – Errors on the bus do not rollback the ASE transaction • Full – Messages sent and received are part of the ASE transaction – Rollback in ASE will undo the work at the bus side – Errors on the bus will rollback the ASE transaction • None – Messages sent and received are not part of the ASE transaction – Rollback in ASE will not undo the work at the bus side – Errors in the bus will not rollback the ASE transaction Session Specific Control by the set transactional messaging command • Set transactional messaging full | simple | none What’s new in RTDS 3.5 Sybase Confidential Proprietary. Evolution and Roadmap of RTDS in ASE ASE 12.5.2 • TIBCO JMS 3.1 support ASE 12.5.3a (RTDS 3.0) • MQ series 5.3 support & TIBCO EMS 4.2 support ASE 15.0 ESD2 & ASE 12.5.4 (RTDS 3.5) • Separate licenses. • Separate dynamical libraries. FUTURE • • • • • Support JMS 1.1 standard MQ Alteruser functionality Support MQ series 6.0 I18N support HA for RTDS RTDS 3.5 Background • Previously tibco api was coupled tightly with ASE by including the header files directly within the codeline. • Reduce the tight coupline/dependency on tibco code: • Remove any license implications • Increase opportunities for the product • Header files were isolated into separate DLL's that get loaded dynamically on demand. • DLL's released only to customers that have purchased RTMS/RTDS licenses. New Feature Description • Isolate 3rd party messaging include files from core ASE into separate DLL's. • Separate ASE_MESSAGING into ASE_MESSAGING_TIBJMS and ASE_MESSAGING_IBMMQ New DLLs for each type of 3rd party messaging provider TIBCO EMS (JMS) =============== $SYBASE/$SYBASE_ASE/lib/libsybtibems.so $SYBASE/$SYBASE_ASE/lib/libsybtibems.sl $SYBASE/$SYBASE_ASE/bin/sybtibems.dll (solaris, linux, ibmaix64) (hppa64) (nt386) IBM MQ =============== $SYBASE/$SYBASE_ASE/lib/libsybibmmq.so $SYBASE/$SYBASE_ASE/lib/libsybibmmq.sl $SYBASE/$SYBASE_ASE/bin/sybibmmq.dll (solaris, linux, ibmaix64) (hppa64) (nt386) + The only places in ASE source code that will include 3rd party headers will be the sources for these DLL's. + 3rd party include files are no longer included by ASE core code. + ASE will load these DLL's if messaging is configured for that type of messaging provider. The path to the DLL's is hardcoded to the paths under $SYBASE. + The new DLL's have generated version and copyright strings, and have functions to return their version numbers. This is for future upward compatibility in case we need to change the interface between ASE and the DLL's. Code changes + There are no external functional changes that customers will notice. + Code changes are mainly in the following categories o Functions that load the new DLLs. o All calls to KRTMS worker functions are now through function pointers. o These are the KRTMS functions that make the actual 3rd party provider API call. The logic of how the 3rd party API calls are made or of the RTMS or higher KRTMS layers is not changed. o Build changes (see below) 3 Layers The implementation is divided into three layers a server layer (RTMS), a kernel layer (KRTMS), an interface DLL layer (libsybtibems.so or libsybibmmq.so). New Work Flow for 3 Layers RTDS 3.5 New Interface If you are using: • Both MQ and JMS, enter: sp_configure 'enable real time messaging', 1 •Only MQ, enter: sp_configure 'enable real time messaging', 1, 'ibm_mq' •Only JMS, enter: sp_configure 'enable real time messaging', 1, 'tibco_jms' RTDS 3.5 Licenses • Separate ASE_MESSAGING into ASE_MESSAGING_TIBJMS and ASE_MESSAGING_IBMMQ. • This is a follow up to split out the messaging provider API calls into separate dll's. • This gives us more flexibility to package RTDS w/o incurring licensing liabilities from the 3rd party messaging providers. Start of ASE 00:00000:00000:2006/02/20 10:05:03.14 kernel libsybibmmq Sybase IBM MQ interface/12.5.4/EBF 12150/P/NT (IX86)/OS 4.0/ase1254dev/1/32-bit/DEBUG/Sun Feb 05 14: 44:21 200600:00000:00000:2006/02/20 10:05:03.14 kernel Confidential property of Sybase, Inc.Copyright 1987, 2006Sybase, Inc. All rights reserved.Unpublished rights reserved under U.S. copyright laws. This software contains confidential and trade secret information of Sybase,Inc. Use, duplication or disclosure of the software and documentation bythe U.S. Government is subject to restrictions set forth in a license agreement between the Government and Sybase, Inc. or other written agreement 00:00000:00000:2006/02/20 10:05:03.14 kernel IBM MQ dynamic libraries successfully loaded. Start of ASE 00:00000:00000:2006/02/20 10:05:03.14 kernel libsybtibems Sybase TIBCO/EMS interface/12.5.4/EBF 12150/P/NT (IX86)/OS 4.0/ase1254dev/1/32-bit/DEBUG/Fri Feb 17 15:46:33 2006 00:00000:00000:2006/02/20 10:05:03.14 kernel Confidential property of Sybase, Inc.Copyright 1987, 2006Sybase, Inc. All rights reserved.Unpublished rights reserved under U.S. copyright laws. This software contains confidential and trade secret information of Sybase,Inc. Use, duplication or disclosure of the software and documentation bythe U.S. Government is subject to restrictions set forth in a licenseagreement between the Government and Sybase, Inc. or other writtenagreement 00:00000:00000:2006/02/20 10:05:03.18 kernel TIBCO JMS dynamic libraries successfully loaded. Setup and Configuration Examples Sybase Confidential Proprietary. Setup and Configuration Examples Setup RTDS 3.5 InstallShield. License of Sysam 2.0 . Configure 1. Install messaging services isql –i $SYBASE/$SYBASE_ASE/scripts/installmsgsvss 2. Add local server for transactional messaging (required) 3. sp_addserver <local server name>, local <reboot> Grant messaging roles to logins grant role messaging_role to <login> 4. Configure real time messaging services sp_configure ‘enable real time messaging’, 1, ‘tibco_jms’ sp_configure ‘messaging memory’, <# of pages> JMS Msgsend Examples Sending messages to a queue 1. select msgsend(‘hello world’, ‘tibco_jms:tcp://my_host_name:18541?queue=queue.sample’) 2. select msgsend((select * from pubs2..publishers FOR XML), ‘tibco_jms:tcp://my_host_name:18541?queue=queue.sample’) 3. select msgsend(“this is a red message”, ‘tibco_jms:tcp://my_host_name:18541?queue=queue.sample’ MESSAGE PROPERTY “color=‘red’”) JMS Msgrecv Examples Receive messages from a queue 1. select msgrecv( ‘tibco_jms:tcp://my_host_name:18541?queue=queue.sample’) 2. select msgrecv( ‘tibco_jms:tcp://my_host_name:18541?queue=queue.sample’ MESSAGE SELECTOR “color=‘red’”) JMS Msgpublish Examples Register the topic 1. sp_msgadmin ‘register’, ‘subscription’, ‘sub1’, ‘tibco_jms:tcp://my_host_name:18541?topic=topic.sample’) 2. sp_msgadmin ‘register’, ‘subscription’, ‘sub2’, ‘tibco_jms:tcp://my_host_name:18541?topic=topic.sample’, “color=‘blue’” Publish messages to a topic 1. select msgpublish(‘hello world’, ‘sub1’) 2. select msgpublish((select * from pubs2..publishers FOR XML), ‘sub1’) 3. select msgpublish(“this is a blue message”, ‘sub2’ MESSAGE PROPERTY “color=‘blue’”) JMS Msgconsume Examples Consume messages from a topic 1. select msgconsume(‘sub1’) MQ Msgsend - Examples 1. select msgsend(‘hello world’, ‘ibm_mq:chan1/tcp/localhost(1234)?queue=default.queue’) 2. select msgsend((select * from pubs2..publishers FOR XML), ‘ibm_mq:chan1/tcp/localhost(1234)? qmgr=QM,queue=default.queue’) 3. select msgsend(“something about nothing”, ‘ibm_mq:chan1/tcp/localhost(1234)? qmgr=QM,queue=queue.sample’ MESSAGE HEADER “priority=8”) 4. select msgsend(‘hello world’, ‘ibm_mq:chan1/tcp/localhost(1234)? qmgr=QM,queue=default.queue’ OPTION ‘msgType=datagram’) MQ Msgsend – Flexible Messaging Option MESSAGE HEADER is used to specify optional element for the message being sent Few examples of MESSAGE HEADER values are mode = default | persistent | non-persistent expiry = <timespec> priority = number correlationId = <string or hex number> msgId = <string or hex number> formatName = string groupId = <string or hex number> sequenceId = number offset = number ordering = logical | physical MQ Msgrecv - Examples declare @Q varchar(50) select @Q = ‘ibm_mq:channel1/TCP/localhost(1234)?qmgr=QM1,queue=Q1’ select msgrecv(@Q) 1. select msgrecv(@Q OPTION ‘timeout=30ss’) 2. select msgrecv(@Q OPTION ‘timeout=30ss,correlationId=‘je987nh’’ 3. select msgrecv(@Q OPTOIN ‘timeout=30ss’ RETURNS varbinary(100)) MQ publish/subscribe - Examples declare @BROKER declare @STREAM declare @SUBQ declare @QM varchar(100) varchar(100) varchar(100) varchar(100) select @QM select @BROKER select @STREAM select @SUBQ = “ibm_mq:chnl1/tcp/host1(9876)?qmgr=QM” = “SYSTEM.BROKER.CONTROL.QUEUE” = “ANIMALS” = “MY_ANIMALS” 1. Publisher registers to send publications to ANIMALS with topics on fish. 2. Subscriber registers to receive publications published to ANIMALS with topics on fish. The subscriber will get the publications on MY_ANIMALS. select msgsend(NULL, @QM + ‘,queue=’ + @BROKER OPTION ‘rfhCommand=registerPublisher’ MESSAGE HEADER ‘topics=fish,streamName=’ + @STREAM) select msgsend(NULL, @QM + ‘,queue=’ + @BROKER OPTION ‘rfhCommand=registerSubscriber’ MESSAGE HEADER ‘topics=fish’ + ‘,streamName=’ + @STREAM + ‘,queueName=’ + @SUBQ’) MQ publish/subscribe - Examples 3. 4. Publisher publishes publication to ANIMALS about fish. The MQ pub/sub broker will automatically forward the publication to MY_ANIMALS. select msgsend(‘something about fish’, @QM + ‘,queue=’ + @STREAM OPTION ‘rfhCommand=publish’ MESSAGE HEADER ‘topics=fish’) Subscriber reads the forwarded message from MY_ANIMALS. select msgrecv(@QM + ‘,queue=’ + @SUBQ OPTION ‘timeout=30ss’) PLATFORM-SPECIFIC ISSUES RTDS 3.5 • Active Messaging using ASE (using ASE 12.5.4) – JMS Support: Solaris 32/64 bit, HPPA RISC 64 bit, IBM AIX 64 bit, Windows 32 bit, Linux 32 bit – IBM MQ support: Solaris 32/64 bit, HPPA RISC 64 bit, IBM AIX 64 bit, Windows 32 bit, Linux 32 bit RTDS Passive Messaging, RepConnector 15 Sybase Confidential Proprietary. Quick Review Database to Messaging Application Server RepServer Replication Event Capture Transform XML or custom Message Event Sender pre-defined or custom Messaging System RepConnector RepConnector Customization RepConn allows user customization Message Format Customization Ability to transform or format the message Implementing the RepTransactionFormatter java interface Transport Customization Ability to send the message anywhere ( file, disk, email …) Implementing the RepraClient java interface Quick Review Messaging to Database Application Server Messaging System RepConnector Message Event Capture Transform Database Event Sender Product and Platform Compatibilities • Platform Support – – – – Windows 2000, 2003 Solaris 2.8, 2.9, 2.10 AIX 5.2, 5.3 Linux 2.1, 3.0 • Application Server Support, because it is a JCA connector – Sybase EAServer 5.2 – BEA WebLogic Server 8.1 • Database/Replication Server Support – Sybase Replication Server 12.6, 15.0 – Sybase ASE 12.5.x, 15.0 – Oracle Dataserver 9i, 10g, for events to database flow Product and Platform Compatibilities • Messaging System Support – J2EE JMS ( EAS / BEA ) – Tibco EMS 4.2.0 – Tibco Rendezvous 7.4.1 – IBM Websphere MQ/MQJMS 5.3 – Sonic MQ 6.1 New Features in RepConnector 15 • Supports new ASE 15.0 datatypes – New integer types – Unitext – Computed columns – Encrypted columns – Large Identifiers • Supports Eclipse 3.0.1 for RepConnector Manager New Features in RepConnector 15 (cont) • Supports non-default ports for IBMMQ • Supports JDBC 3.0 • No longer supports trimming white spaces from replication events SUMMARY Messaging architectures enable sharing information among multiple applications But databases today store information until applications ask for it With Sybase Real Time Data Services (RTDS) database events are pushed to applications the moment change occurs Sybase RTDS enables information flows that are faster, more relevant, and actionable Questions ?
© Copyright 2024