E189 Distributing Data Throughout the Enterprise Using Sybase

1
E189 Distributing Data Throughout the Enterprise
Using Sybases Data Movement and Data Access
Technologies

• By Kelvin Foster
• Senior Sybase DBA
• IT Services
• Kelvin_Foster_at_AIMFUNDS.COM

2
Agenda

• Introduction
• Case Study 1 - Contribution Attribution
  Performance Reporting
• Case Study 2 Portfolio Management System
• Case Study 3 Cash Management Trading System
  (CMTS)
• Future Opportunities Whats Next?
• Q A

3
Introduction

• About AIM
• When retired chairman Charles T. (Ted) Bauer,
  President and CEO Robert H. Graham and Director
  of Investments Gary T. Crum founded AIM in 1976,
  the company only had a table, two chairs, and a
  telephone. Through decades of planning and
  disciplined growth, AIM has earned its reputation
  as one of America's leading investment management
  companies. The Houston-based company now has more
  than 158 billion in assets under management and
  more than 2,500 employees.

4
Introduction cont.

• About AIM
• AIM is an indirect subsidiary of AMVESCAP PLC.
  AMVESCAP is a leading independent global
  investment manager dedicated to helping people
  worldwide build their financial security.
  Operating under the AIM and INVESCO brands,
  AMVESCAP strives to deliver outstanding
  investment performance and service through a
  comprehensive array of retail and institutional
  products for clients in more than 100 countries.
  The Company is listed on the London, New York,
  Paris, and Toronto stock exchanges with the
  symbol AVZ.

5
Introduction cont.

• About the Presenter
• Senior Sybase DBA
• 8 years of IT experience mostly working with
  Sybase products
• Former Sybase Professional Services consultant

6
Case Study 1 - Contribution Attribution
Performance Reporting

• Multi purpose trade based quantitative analytical
  tool
• Contribution Tool- designed to disaggregate
  performance of a portfolio to the lowest level
• Attribution Tool- allows one to assess the
  performance of a portfolio with respect to a
  pre-determined index or fund
• Scenario analysis allows one to perform What
  if scenarios for a particular fund

7
Case Study 1 - Contribution Attribution
Performance Reporting - cont.

• Business Challenge Provide near real-time
  Equity, Fixed-Income, Hedge fund data that is
  consist and accurate
• Solution Adaptive Server Enterprise, Sybase
  Replication Server, DirectConnect for Oracle
• Results The replication system developed
  provides timely data to this critical decision
  making application while maintaining data
  integrity and consistency.

8
Case Study 1 - Data Movement with Replication
Server and DirectConnect for Oracle

• Replication from Sybase ASE to Oracle using
  Component Integration Services and Direct Connect
  for Oracle

RDS
PDS
RS
DCO
RDB1
RDB2
RDB3
9
Case Study 1 Keys to a Successful Implementation

• Provide near real-time data that is consist and
  accurate
• Analysis and Testing
• What? (Databases, Tables, Stored Procedures,
  Columns)
• When? (Maximum amount of latency)
• Where? (Location of PDS, PRS, RDS, RRS)
• How Much? (Number of transactions and size of
  largest transaction)
• Error handling procedures
• Invalid data (NaN, Duplicate trans)
• Error actions (Log, Stop Replication)
• Exception reporting and procedures to handle
  exceptions (rs_exceptscmd, rs_exceptshdr,
  rs_systext)

10
Case Study 1 Keys to a Successful
Implementation cont.

• Monitoring, Monitoring, and More Monitoring
• Replication Server Manager
• Third-Party Monitoring tools
• UNIX shell scripts
• Backup and Recovery
• Clustering
• Atomic vs. Bulk Materialization
• rs_subcmp

11
Case Study 2 Portfolio Management System

• Portfolio Management System gives money managers
  an automated way of trading, analyzing, and
  managing assets associated with investment
  portfolios.

12
Case Study 2 - Portfolio Management System cont.

• Business Challenge Decision Support (DSS)
  activities affecting On-line Transaction
  Processing (OLTP). Provide separate environments
  to split OLTP and DSS functions by distributing
  transactions with minimal latency.
• Solution Adaptive Server Enterprise and Sybase
  Replication Server
• Results Implemented replication system providing
  real-time transaction from OLTP system to DSS
  system minimizing contention between two function
  while maintaining data integrity and consistency.

13
Case Study 2 - Data Movement with Replication
Server

• Replication from Sybase ASE to Sybase ASE

14
Case Study 2 Keys to a Successful Implementation

• Provide near real-time data that is consist and
  accurate
• Analysis and Testing
• What? (Databases, Tables, Stored Procedures,
  Columns)
• When? (Maximum amount of latency)
• Where? (Location of PDS, PRS, RDS, RRS)
• How Much? (Number of transactions and size of
  largest transaction)
• Error handling procedures
• Invalid data (NaN, Duplicate trans)
• Error actions (Log, Stop Replication)
• Exception reporting and procedures to handle
  exceptions (rs_exceptscmd, rs_exceptshdr,
  rs_systext)

15
Case Study 2 Keys to a Successful
Implementation cont.

• Monitoring, Monitoring, and More Monitoring
• Replication Server Manager
• Third-Party Monitoring tools
• UNIX shell scripts
• Backup and Recovery
• Clustering
• Atomic vs. Bulk Materialization
• Dump and Load
• rs_subcmp

16
Case Study 3 Cash Management Trading System
(CMTS)

• The Money Market desk trades approximately 25
  billion dollars a day in overnight securities
  through this system.
• This system allows portfolio managers to accept
  bids from money brokers, enter trades and flow
  data to back office systems automatically.
• Developers are currently working on the
  requirements gathering of Phase II which will
  include the Taxable and Tax-free Money Market
  Desk.

17
Case Study 3 Cash Management Trading System
(CMTS) - cont.

• Business Challenge Provide an interface between
  CMTS and Back Office application.
• Solution Adaptive Server Enterprise,
  DirectConnect for Microsoft SQL Server
• Results Provided seamless access to the CMTS
  data and eliminating the need for multiple batch
  processes providing stale data.

18
Case Study 3 - Data Access with DirectConnect for
MS SQL Server

• PA processes FIMM trades which are booked in the
  Portfolio Administration system.

PDS
Remote DS
MS SQL DBMS
19
Future Opportunities Whats Next?

• Warm-standby for Equity Trading System
• Warm-standby for Portfolio Administration
• More Data Access
• High-Availability

20
Q A - What would you like to know?