Title: Arial 28pt.

1
BID212 Rapid Prototyping with Sybase IQ
Sid Sipes Principal System Consultant Sipes_at_sybase
.com August 15-19, 2004
2
The Enterprise. Unwired.
3
The Enterprise. Unwired.
Industry and Cross Platform Solutions
Unwire People
Unwire Information
Manage Information

• Adaptive Server Enterprise
• Adaptive Server Anywhere
• Sybase IQ
• Dynamic Archive
• Dynamic ODS
• Replication Server
• OpenSwitch
• Mirror Activator
• PowerDesigner
• Connectivity Options
• EAServer
• Industry Warehouse Studio

• Unwired Accelerator
• Unwired Orchestrator
• Unwired Toolkit
• Enterprise Portal
• Real Time Data Services

• SQL Anywhere Studio
• M-Business Anywhere
• Pylon Family (Mobile Email)
• Mobile Sales
• XcelleNet Frontline Solutions
• PocketBuilder
• PowerBuilder Family
• AvantGo

Sybase Workspace
4
Target Audience

• Information Technology Management
• Project Management
• Database Personnel
• All Levels
• System Administrators
• All Levels

5
For This Presentation

• Lets Keep It Interactive
• I will entertain questions on a subject
• Would be happy to speak to you offline, if
  desired
• No Question is too Basic
• Chances are others may have the same question
• Open Question Forum at the end
• Time Permitting

6
Agenda

• In this Session, we will be discussing
• What is Rapid Prototyping?
• Why should I consider using Sybase IQ for a Rapid
  Prototype?
• How To Get Started
• How to Optimally
• Select Hardware and Software for my Prototype
• Setup the Hardware for the Prototype
• Setup and Configure The Sybase IQ Database
• Summary
• Question and Answer

7
Rapid Prototyping with Sybase IQ

• In today's hectic business environment, rapid
  prototyping may be the best solution for quick
  implementation of a required functionality, or
  the rapid proving of a new and potentially
  helpful technology.
• Sybase IQ is a technology that can help you
  rapidly meet your sometimes unrealistic goals,
  and provide your users with unparalleled
  performance gains.

8
Rapid Prototyping with Sybase IQ

• This presentation will focus on how Sybase IQ can
  be used within a rapid prototype or
  proof-of-concept project.
• Topics covered will include project planning,
  limitations of scope, prevention of scope-creep.
  In addition, all technical aspects of utilizing
  Sybase IQ, including software installation,
  design methodology, and IQ system performance
  tuning will be fully covered.
• A complete exploration of Sybase IQ tips and
  tricks for rapid implementation and high
  performance is included

9
Rapid Prototyping - Definition

• What is a Rapid Prototype?
• Think of a Rapid Prototype as a rapidly developed
  first version of a new application.
• The Rapid Prototyping Methodology is a
  short-circuit to the traditional software
  development methodology.

10
Rapid Prototyping - Definition

• What is a Rapid Prototype?
• Traditionally, software is developed in distinct
  phases, with each phase concluding with approval,
  before the next phase. These phases include
• Requirements Development
• Requirements Analysis
• System Requirements Specification
• Preliminary Design
• Detailed Design
• Implementation
• Test
• Quality Assurance
• Deployment
• Training.

11
Rapid Prototyping - Definition

• As you probably have experienced, this is a long
  effort, and doesnt adequately address
  changing/evolving user requirements.
• The Rapid Prototype Methodology effectively rolls
  all the previously mentioned phases into a
  single, quick effort. Some characteristics of
  Rapid Prototyping include
• Limited Requirements
• On-The-Fly changes/modifications
• Rapid Response to Changing User Requests
• Rapid Deployment.

12
Rapid Prototyping - Definition

• Different Types of Rapid Prototypes
• Straw-Model Non-Operational
• Limited Model Limited Requirements
  Implementation
• Full Model Full system Implementation.
• For the purposes of this discussion, we will be
  considering only the Limited Model

13
Why Rapid Prototype?

• Rapid Prototyping Offers Many Advantages
• Quickly Prove Business Value
• Demonstrate value more effectively than
  talk/slide show
• Realistic Implementation
• Allow users rapid visualization of requirements
• Reduce (or confirm) Implementation Risks
• Resulting in
• Concept Proved
• Value Shown
• Money Saved

14
Why Rapid Prototype?

• Any Disadvantages?
• Usually No Documentation produced
• Not all Requirements Implemented
• Implementation may be Sloppy
• Easy to Lose Resources, especially people to
  Real projects
• You should be able to avoid or control any of the
  disadvantages.

15
Why Rapid Prototype?

• Now, Lets take a closer look at each of the
  advantages of Rapid Prototyping.
• At the conclusion of this section, well start
  diving deeper into the technical aspects,
  including IQ Performance, of implementing in a
  Rapid Prototype Mode.

16
Why Rapid Prototype?

• Prove Business Value
• Seeing is believing
• Asking forgiveness is easier than asking for
  large money/budget
• Early/Quick Testing of concept

17
Why Rapid Prototype?

• Demonstrate value more effectively
• Presentations and Slide Shows not always
  representative of the finished product
• Many are Truly boring
• Miscommunication often occurs
• Presentations are Usually Heavy on the Technical
  Side
• Light on User Requirements

18
Why Rapid Prototype?

• Realistic Implementation
• This is not a static mockup
• What you see Is What you get
• Actual User Requirements are Implemented
• Possible Evolutionary Basis for Final System

19
Why Rapid Prototype?

• Allow users rapid visualization of requirements
• Users are Involved Early and Continuously
• Users Feel Valued, and Empowered
• Early and Immediate Feedback is Available
• Developers can make Immediate changes/corrections

20
Why Rapid Prototype?

• Reduce (or confirm) Implementation Risks
• Seen by management as smaller risk
• Easier Management signoff on small budget

21
Why Rapid Prototype?

• Easier To Get Management Sign On
• Rapid Prototypes Seen by management as smaller
  risk
• Smaller Budget usually easier to Get
• Requires Smaller Hardware Investment

22
Why Rapid Prototype?

• Rapid Confirmation
• Rapidly Confirm Concept
• Quickly Change Direction when Problems Occur
• Early Confirmation/Discovery of Problems
• Developmental
• User Interface
• Database
• Performance

23
Why Rapid Prototype?

• To Summarize,
• Rapid Prototyping Offers Many Advantages
• Quickly Prove Business Value
• Demonstrate value more effectively than
  talk/slide show
• Realistic Implementation
• Allow users rapid visualization of requirements
• Reduce (or confirm) Implementation Risks
• Resulting in
• Concept Proved
• Value Shown
• Money Saved

24
Rapid Prototyping Getting Started

• In this section, we will discuss how to get a
  Rapid Prototype Project successfully started.

25
Getting Started

• Pick a Project
• One that Has Value to your Organization
• One that Users are Requesting
• Perhaps one that may seem impossible due to
  size/cost

26
Getting Started

• Once you have identified a Project
• Develop a Set of Requirements
• Based on User needs
• Document exactly what the Prototype will perform
• Review Requirements with Users, I.e.
• Conduct an informal requirements review
• Dont Let Scope Creep Stick to Original
  Vision/Plan

27
Getting Started

• For Requirements Analysis, Consider Following
• Audience / The Users
• Involve Users from the Start
• Implement what they Want, not what is Cool
• Do they have any budget/funding to share?
• What do they want
• Data Warehouse?
• Report Server?
• OLTP Server?

28
Getting Started - Guaranteeing Success

• There are no Guarantees of Success. However, you
  can maximize your chances of producing a useful
  system by
• Implementing your prototype in careful,
  methodical, structured manner.
• Developing a plan
• Sticking to the plan
• Dont allow scope creep
• Dont allow requirements creep

29
Getting Started - Guaranteeing Success

• Dont rush implementation
• And MOST IMPORTANTLY
• INVOLVE THE USERS AT ALL PHASES

30
Getting Started - Guaranteeing Success

• The goal is to produce a system that has
  considerable value to your USERS
• If USERS dont like the system, or if it is too
  difficult and/or cumbersome for them
• They wont use it
• The system will fail
• The system will be dismantled
• Your Career will be dismantled

31
Getting Started - Guaranteeing Success

• Lets take a look at the steps you can take to
  maximize your chances of success
• Get to Know the Users
• Conduct a Thorough Requirements Analysis
• Develop a Data Analysis
• Develop an Implementation Plan
• Secure sufficient resources
• Personnel
• Hardware/Software
• Time

32
Getting Started - Guaranteeing Success

• Develop a Set of System Requirements
• Dont get into requirements Analysis Paralysis
• Determine what you can do
• Lock into those requirements, discard others for
  future phases
• Make sure you brief users on what you can
  implement
• You May not be able to satisfy all users, but it
  is better to successfully build a subset of
  needs, than to try to build a super-system that
  fails and delivers nothing.

33
Getting Started - Guaranteeing Success

• Realistically Cost/Schedule The Effort
• Based on Requirements Analysis
• Determine resources required to fulfill
  requirements
• People
• Time/Schedule
• Hardware/Software
• Dont Estimate Optimistically
• Problems will occur
• Nothing ever goes according to plan
• What can go wrong Will go wrong!

34
Getting Started - Guaranteeing Success

• Conduct a Detailed Data Analysis
• Based on User needs and reports
• Locate Legacy/Existing data to support reports
• Acquire any/all documentation on existing data
• Acquire access to all data sources
• Determine if extract procedures are required

35
Getting Started - Guaranteeing Success

• Conduct a Detailed Data Analysis
• Determine Required Transformations
• Data Cleanup?
• Lookups/Validations?
• Combinations?
• Will you automate the process?
• Extract, Transformation, Load (ETL) Too

36
Getting Started - Guaranteeing Success

• Now What?
• The Requirements Analysis, and Data Analysis will
  form the basis and foundation for your detailed
  system design.
• Remember
• A Weak foundation will only support a weak and
  limited design
• A Strong foundation will form the basis for a
  strong and expandable design.

37
Getting Started - Guaranteeing Success

• Finally, Remember that
• It is better to Under Promise, then Over-Deliver
• Than Over-Promise, and Disappoint.

38
Why Sybase IQ?

• If your project includes a Database Component,
  Sybase IQ may be the best choice for your
  database implementation.
• In this section we will discuss how and why
  Sybase IQ will help ensure your project success

39
Why Sybase IQ?

• Lets first make sure that Sybase IQ is
  appropriate for your implementation.
• If you are implementing an OLTP system
• SYBASE IQ IS NOT THE APPROPRIATE CHOICE
• Otherwise, if your system falls into one of the
  following categories, then Sybase IQ will be the
  best choice
• Data Warehouse
• Data Mart
• Query Server
• Online Data Archive

40
Why Sybase IQ?

• Why Use Sybase IQ?
• Heres just a few of the reasons
• Quicker to Implement
• Easier to setup
• Requires Less Hardware
• Easier to Manage
• More Tolerant of Mistakes
• Faster Loads and Queries
• Leading to
• A Much Higher Probability of Success

41
Why Sybase IQ?

• Easier Hardware setup
• Optimal I/O Throughput is easy to setup
• More Tolerant of setup errors/inefficiencies
• Dont need lots of 2 Gigabyte Devices
• Dont need lots of I/O controllers
• Much Easier for System Administrator to Set up
• IQ Performance is Not Dependant on exact disk
  layout/setup
• IQ Can use File System Files or Raw Devices
• System Administrator will spend a Fraction of
  time perparing for an IQ implementation

42
Why Sybase IQ?

• A Sybase IQ Implementation Requires Less Hardware
• IQ Implementation will utilize much less disk
  hardware
• IQ Achieves 10-50 compression from Raw Data Size
• Fully Indexed, IQ will use approximately 70 Less
  Disk Space than any other RDBMS
• Disk might be getting cheaper, but chances are
  your organization will not have an abundance of
  disk space for you to use

43
Why Sybase IQ?

• A Sybase IQ Implementation Requires Less Hardware
• IQ is CPU Bound
• IQ Takes advantage of all available CPU Cycles
• CPUs are never idle, waiting on I/O
• An IQ Implementation will usually require 50
  fewer CPUs than any other RDBMS implementation

44
Why Sybase IQ?

• A Sybase IQ Implementation Requires Less Hardware
• Give the fact that IQ is CPU Bound and IQ
  Performance is directly related to CPU Speed
• Consider using Wintel CPUs
• Consider Using Linux
• IQ on 3 GHz Intel CPU outperforms major Unix
  Flavors
• If choosing Wintel/Linux route
• Remember 2Gigabyte Memory Limitation
• 64 Bit Linux coming soon.

45
Why Sybase IQ?

• An IQ based system is Easier to Manage
• Requires average of 75 less DBA intervention
• No DBCCs to run
• No Index Maintenance
• No Index Unload/Load Required

46
Why Sybase IQ?

• Sybase IQ More tolerant of Mistakes
• Performance is Not Schema Dependant
• Spend much less time designing and Tuning schema
• You can eliminate expensive Database Architects
• Easy to redesign/re-implement
• Speed of Load and Speed of Indexing allow quick
  changes to be made to schema (even after all data
  is loaded)
• Ability to quickly add and drop columns
• Speed of change and load/reload allow you to
  react to users change requests with
  unprecedented speed.

47
Why Sybase IQ?

• IQ is much Faster
• IQ will run queries 5x to 1000x faster
• With very little tuning
• Very little need to dump and analyze query plans
• IQ will load data 10x to 100x Faster
• High Speed loads directly into indexes
• No need to load, then add indexes
• This speed means that your system will be
  available to the users sooner.

48
Why Sybase IQ?

• To Summarize, An IQ system is
• Easier to Setup
• Easier to Manage
• Requires Much Less Resources
• Quicker Delivery
• Faster Response
• Which adds up to
• LOWER COST
• Quicker Delivery
• Increased Probability of Success

49
Physical Implementation with Sybase IQ

• In this section, we will take a look at the
  process of selecting the appropriate Hardware and
  Software for a successful IQ prototype system.
  We will discuss
• Disks
• Layout
• Controllers
• Computer CPU configuration
• Computer Memory configuration
• Optimal IQ Version for your Prototype
• Selection of User Interface/Visualization Tool

50
Physical Implementation with Sybase IQ

• Disk
• How Much disk Space Will you Need?
• Estimate Raw Data Size
• Size Main DB to same size as Raw Data
• We dont know how much compression yet, so be
  safe, estimate pessimistically
• Add 15 more for Temp DB
• Dont Forget TEMPDB
• Dont forget to Estimate Space for Staging Raw
  Data

51
Physical Implementation with Sybase IQ

• Type of Disk
• SAN is Best
• Mandatory if Planning Multiplex Prototype
• If Possible, Eliminate usage of any Volume
  Managers
• 15 Performance Loss with volume manager

52
Physical Implementation with Sybase IQ

• Disk Controller Optimal Configuration
• 1 fiber controller per 5 write CPUs
• 1 fiber controller per 10 read CPUs
• In mixed mode operation use the 51 ratio
• Recommend a minimum of 2 controllers

53
Physical Implementation with Sybase IQ

• Disk Layout
• 4-5 Devices (Total) for Main Database
• Raw is Better
• Can use File System files
• 4-5 Additional Devices (Total) for Temp DB
• Raw is Better
• Can use File System Files

54
Physical Implementation with Sybase IQ

• Disk Layout (cont).
• If using Cooked File System Files
• Same Rules for Disk Controllers Applies
• Make Sure that all Devices are Separate to
  Eliminate I/O Contention
• Plan on Using Raid-5 for Disk Redundan

55
Physical Implementation with Sybase IQ

• Disk Recommendations
• Storage Area Network (SAN) is best
• Mandatory for Multiplex Implementation
• Minimum 2 I/O controllers
• 4-5 Raw Devices for Main Storage
• 4-5 Raw Devices for Temp Storage
• Raid-5 for Disk Redundancy
• No Volume Management Software

56
Physical Implementation with Sybase IQ

• CPUs
• Estimate How Many Concurrent Users will your
  Prototype have/support
• Optimal CPU configuration
• Queries 1 CPU per active query
• more if queries are complex and can be run in
  parallel
• Loads 1 CPU per HG index plus 1 CPU per 2-5
  columns being loaded
• Suitable CPU Configuration
• Queries 3-5 Concurrent Queries Per CPU
• Loads Estimate Number of CPUs Based On 2
  Gigabytes Per Hour Per CPU Load Rate

57
Physical Implementation with Sybase IQ

• Memory
• How Much RAM for IQ
• As much as possible!
• Most systems should have at least 2 GB
• Rough rule of thumb is 2 GB per CPU
• Dont forget additional RAM for each load being
  performed

58
Physical Implementation with Sybase IQ

• Memory Allocation
• Start by allocating memory to caches
• 40 IQ Main Cache
• 60 IQ Temporary Cache

59
Physical Implementation with Sybase IQ

• IQ Software Selection
• Which IQ Version do you Need?
• If you are doing a Wintel Implementation, you
  will have a 32 Bit version
• Memory Limited to 2 Gigabytes
• Linux
• Currently 32 Bit
• 64 Bit Version Coming soon
• HP-UX/Solaris/AIX
• All Currently 64 Bit

60
Physical Implementation with Sybase IQ

• IQ Software Selection
• Wintel/Linux Prototypes usually easier
• Most Organizations have excess Wintel Hardware
• In Theory, could even use old desktop systems
• Dont do anything with less than 2 CPUs
• Wintel Systems are Often Easier to Justify to
  Management
• Seen as SMALL Systems
• Lower Cost
• Less Risk

61
Physical Implementation with Sybase IQ

• User Interface /Visualization,
• Using off the Shelf Visualization Software
• Business Objects/Cognos/Crystal/Brio, etc.
• More Expensive
• Easier Implementation
• Quicker to Prototype and deliver
• Greater Flexibility
• Standard Look and Feel
• Less Risk
• Possible less cost, due to reduced
  implementation/test/fix time required

62
Physical Implementation with Sybase IQ

• User Interface /Visualization,
• Custom Implementation
• Often more difficult to Implement
• Less Flexibility to meet User Change Requests
• Simplicity
• Less Functionality
• Higher Risk
• Possible Higher Cost due to Increased
  Implementation/test/fix cycle.

63
IQ Database Implementation

• In This section, we will discuss how to implement
  an IQ database, with particular attention to
  quick setup for high performance. In particular
  we will cover
• Schema Design
• IQ Database Options

64
IQ Database Implementation

• Schema Design
• Design Schema to Prototype Requirements
• Dont try to build too much
• Dont try to incorporate entire Enterprise
• Dont worry about future expansion/expandability
• Remember, you are building a prototype, not a
  final system
• IQs flexibility will allow for quick and easy
  changes to schema to meet future and/or evolving
  requirements

65
IQ Database Implementation

• Schema Design,
• Consider the following when Designing Schema
• Dont get too caught up in design details
• Remember you are trying to show something,
  however Imperfect
• Are you copying/modifying and existing Design?
• Do you need a New Design?
• Remember that with IQ Performance is Schema
  Independent
• What type of Schema Do you need?
• Star
• Relational
• Flat

66
IQ Database Implementation

• Database Options
• Consider Always setting the following options
• FORCE_NO_SCROLL_CURSORS
• Should always be set to ON
• Very few applications require this to be OFF
• Can improve query performance
• Append_Load
• Can be used to improve load performance
• Will not reuse Row IDs or the space occupied by
  those Row IDs
• Great for systems where large, contiguous chunks
  of data are deleted
• May not be good if random rows are deleted as it
  can lead to fragmentation and allocated, but
  unused space

67
IQ Database Implementation

• Database Options
• Consider Always setting the following options
• Disk_Striping
• If ON, IQ will stripe writes to all available
  devices
• If OFF, the first device must be full before the
  next is used
• Disk_Striping_Packed
• If ON, it forces better space usage and less
  fragmentation
• Fragmentation is indicated when out of space
  messages are returned but the main dbspace used
  is less than 100 full
• Allows Adaptive Server IQ to better utilize small
  pieces of unused space that remain after
  compression

68
IQ Database Implementation

• Database Options
• Consider Always setting the following options
• LOAD_MEMORY_MB
• Set to 0 (default) on systems with enough RAM
• Set to lt 500 on systems where RAM is tight or
  there are simultaneous loads taking place
• Rough rule of thumb for load memory requirements
• Run select tablewidth( tablename )
• Divide that value by 2
• New number is rough calculation of MB needed to
  load that table
• Remember that each load requires separate memory

69
IQ Database Implementation

• Database Options
• Consider Always setting the following options
• MINIMIZE_STORAGE
• This will place an IQ UNIQUE(255) on every column
  for every table created and removes the need to
  use IQ UNIQUE
• If the cardinality is lt 255 then IQ will place a
  1-byte FP index on the column 1 byte of storage
  per row
• If the cardinality is gt 255 but lt 65536 then IQ
  will place a 2-byte FP index on the column 2
  bytes of storage per row
• May slightly hinder data loads, but improve query
  speeds
• May incur onetime slight load slowdown while
  2-byte FP is converted to flat FP, but this
  usually happens during the first load

70
Lets Summarize

• Why should you consider Rapid Prototyping?
• Rapid Prototyping Offers Many Advantages
• Quickly Prove Business Value
• Demonstrate value more effectively than
  talk/slide show
• Realistic Implementation
• Allow users rapid visualization of requirements
• Reduce (or confirm) Implementation Risks
• Resulting in
• Concept Proved
• Value Shown
• Reduced Cost and Money Saved

71
Lets Summarize

• Why should you consider Sybase IQ for your Rapid
  Prototype?
• Heres just a few of the reasons
• Quicker to Implement
• Easier to setup
• Requires Less Hardware
• Easier to Manage
• More Tolerant of Mistakes
• Faster Loads and Queries
• Leading to
• A Much Higher Probability of Success
• Lower Risk
• Reduced Cost and Money Saved

72
Thats It

• Questions?

73
Thats It

• Go Forth and Safely Compute
• See Ya Next Year
• Sid

74
BID212 Rapid Prototyping with Sybase IQ
Sid Sipes Principal System Consultant Sipes_at_sybase
.com August 16, 2004