Transcript Sybasedit2

ACCESS - TEAM
Austin, Sandra
Grant, Anthony
Pham, Tran
Samuel, Bhanu
Stewart, Doretha
Agenda
•
•
•
•
•
Goals
Infrastructure of Sybase
Management
DBMS Selection by choice
Cost
Mission Statement
• Sybase databases are developed to accommodate
business needs regardless of size. The main focus of
the database is Web based to allow portability,
integration, manageability, and availability of
applications as their dedication to businesses success.
Mission Objective
1. Provide high performance, scalable products that are
compatible and deliverable anywhere.
2. Provide the most comprehensive solutions for
processing of critical information globally.
History of Sybase
• Corporation located in Emeryville, Ca.
• Company founded in 1984.
• Sybase is primarily a UNIX vendor and is recognized as
one the largest providers of database software.
• Introduced the SQL server in 1987 as a client server
solution that was as designed to separate the client and
server functionality.
Tran Pham
Infrastructure of Sybase
ASE 12.5 includes:
1. Adaptive Server 12.5
2. Adaptive Server Enterprise Monitor™ Server 12.5
3. Monitor Client Library 12.5
4. Historical Server 12.5
5. Open Client™ 12.5 SDK
6. Embedded SQL™/C 12.5 SDK
7. SQL Remote™ 7.0.2
8. Language Modules 12.5
9. jConnect for JDBC 4.5
10.jConnect for JDBC 5.5 .
11.Java utilities:
» JISQL 2.0
» Ribo 2.0
12.Sybase Central 3.2
ASE Environment
Common Database Architecture
Database Architecture
•
•
•
•
•
•
Mixed and Varying Workloads
Leverage Legacy Data
Enterprise connect
Replication Server
Internet-Architecture Support
Low and High-end Scalability
Data Type
• Having standard data type SQL2. However, there are
quite a few exceptions.
SQL Statements vs. SQL*Plus Commands
SQL:
• Is a language
• Is ANSI standard
• Keywords cannot be
abbreviated
• Statements manipulate data
and table definitions in the
database
• SQL statements SQL
Buffer
SQL* Plus
• Is an environment
• Is Oracle propriety
• Keywords can be abbreviated
• Commands do not allow
manipulation of values in the
database
• SQL*Plus commands  NO
SQL*Plus buffer
Basic SQL
• Data Retrieval
• Data Manipulation Language (DML)
SELECT
• Data Definition Language (DDL)
CREATE
INSERT
UPDATE
DELETE
ALTER
DROP
RENAME
TRUNCATE
• Transaction control
COMMIT
ROLLBACK
SAVEPOINT
• Data control language (DCL)
GRANT
REVOKE
DDL - Create table
• CREATE TABLE EMPLOYEE
(FNAME
VARCHAR(15)
NOT NULL.
MINT
CHAR,
LNAME
VARCHAR(15)
NOT NULL,
SSN
CHAR(9)
NOT NULL,
BDATE
DATE,
ADDRESS
VARCHAR(30)
SEX
CHAR,
SALARYDECIMAL(10,2),
DNO
INT
NOT NULL,
PRIMARY KEY (SSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER));
go
DML - Insert data into table
• INSERT INTO EMPLOYEE
VALUES
(‘Rich’,’k’, ‘Martin’,’123456789’,’196205-09’, ‘123 Main St, Baltimore, MD’, ‘M’, 37000,
15);
go
DATA RETRIEVAL - SQL
SELECT
FROM
WHERE
DNAME, COUNT(*)
DEPARTMENT, EMPLOYEE
DNUMBER=DNO AND SALARY >
50000
GROUP BY DNAME
HAVING COUNT (*) > 5;
Grant, Anthony
Management
Data Management
• Information Management (Advanced E-Business Data Management)
– Extensive Markup Language (XML)
– Universal format for structured documents and data on the
Internet
– Embedded query and storage engine
– Extensive Query Language (XQL)
– Content generation from HML documents
– Interprets XQL queries
– Generates in memory access structures
– Management of External Non-Relational Content
– Proxy tables – full access to all information in the operating
systems directory
Dynamic Performance Tuning
• Permits the use of hybrid transaction processing/query environments to
perform the following functions
–
–
–
–
–
–
Adjust to cache of Index pages and data
Significant reduction in I/O costs
User priorities are provided
CPU level resources can be controlled
Resources for a query or batch are limited
DBA’s can reconfigure the system without rebooting
Security
• Row-level Storage Security
– Solves the problem of information filtering
– By incorporating an application called Application Context Facility
(ACF)
– DBA can set attribute values for application facilitated by the
creation of triggers that run each time a user logs in
• Secure Socket Layer Support
– Sybase supports Secure Socket Layer
– Allowing for the use of encryption between database clients
– Protects data from unauthorized access
– Sybase utilizes digital certificates to authenticate servers and
username/passwords
Mission Critical Computing
• Compressed Backups
– Reduction of archival cost
• SQL debugging
– Assist in application development
• Lightweight Directory Access Protocol (LDAP)
– Support for easier system management
• Component Integration
– Remote query optimization
– Heterogeneous data access
– Heterogeneous transaction support
Samuel, Bhanu
RDBMS Selection
SYBASE PRODUCTS
•Sybase Adaptive Server Enterprise: It is a powerful data
management platform for mission - critical transactions, it is
known for its flexibility, open standards and effective use of
computing power.
•iAnywhere Solutions: It is a comprehensive package that
provides data management and enterprise synchronization to
enable the rapid deployment of distributed e-business solutions.
•Adaptive Server IQ : It is a high performance relational database
to meet the needs of business intelligence.
MERITS OF SYBASE
• When it comes to your business - Sybase understands one
size does not fit all.
• Sybase competitive advantage are based on the company’s 3
strategic initiatives:
• e-Business
• m-Business
• Vertical markets.
SYBASE - ORACLE
• Basic Sybase infrastructure includes- 2 processes, data server and
back up server.
• Oracle includes: 5 processes - process monitor, db writer,
checkpoint and log writer.
• Backup Capabilities: Sybase can perform a dump database
operation when the database is online or offline. In oracle
previous to Oracle 8, the only way to back up the database was
through a cold backup.
• Recovery Attributes: In Sybase the load database command
loads the designated database. In Oracle recovery is achieved by
restoring the data files.
SYBASE - ORACLE
• Security Procedures: The initial id shipped with Sybase is
System Administrator (SA). User logins are added at the
server level, and then granted access to each database.
Oracle is shipped with several built in accounts system.
• Data Types: In Sybase the supported data types include
integer, decimal, float, money, char, varchar, and text types.
Oracle data types include number, char,varchar2, date, long
raw, clob, and blob.
• Storage: In Sybase tables are stored in segments, In Oracle
tables are stored in table spaces.
SYBASE - ORACLE
• Performance and Scalability: Sybase continues to break TPC
benchmark records. Oracle has always been known for its speed and
performance. Oracle 8 supports 15,000 active user connections.
• Pricing - In Sybase price per seat is average. Support is achieved by
opening cases with the support team. In oracle price per seat is
relatively higher.
SYBASE vs. ORACLE
•
•
•
•
•
Better tech support.
Cheaper to administer.
Requires fewer system resources.
Better documentation
Owns 90% of the financial industry which speaks of its reliability
and stability
• Use of transaction logs - has roll back and recovery transaction
log features.
• Sybase can backup on the fly without taking table spaces.
SYBASE - CERTIFICATIONS
• Sybase Certified Associate
• Sybase Certified professional Adaptive Server
Administration.
Austin, Sandra
Cost Analysis
Sybase ASE: Performance
Company
Product
Hardware
TPC-C
# of CPUs
Sybase
ASE 12.0
HP9000/2500
102,023
32
Oracle
Oracle 8i 8.1.5.1.1
HP9000/2500
92,832
32
Difference
$/TPC-C
Total Cost:
$63.21
$6,448,894
$87.71
$8,142,782
$24.50
$1,693,888
9,191
Less Demand on System Resources:
Memory Requirements
Company
Product
Memory Per User
Sybase
ASE 12
72 KB
System Memory Requirements
250 Users
18 MB
500 Users
36 MB
1,000 Users
72 MB
5,000 Users
360 MB
Oracle
Oracle 8i
250 KB
62 MB
132 MB
264 MB
1.25 GB
Difference
(347% )
178 KB
44 MB
96 MB
192 MB
890 MB
Cost and Efficiency
• Sybase has a much more efficient database engine
• On a typical 300 GB database with 500 users, ASE can save
customer $1.4 million (hardware and software)
• We use about half as much disk space for same amount of data
• Our SMP records prove the efficiency of ASE. Oracle relies on
lots of hardware for high performance
• Using Oracle would cost the customer more even if the
software was free
Disk Requirements
• Independent study comparing disk usage:
– # of rows:
2 billion
– Row width: 42 bytes
Company
Product
Disk Used
Cost (at $1/MB)
Sybase
ASE 11.5
511.3 GB
$511,000
Oracle
Oracle 8
1,052 GB
$1,052,000
Difference
>107%
540 GB
$541,000
Total Cost of Ownerships
Oracle
Sybase
Advantage
Raw throughput
57,053
52,117
Oracle 9%
$TPM-C
$147.40
$81.17
Sybase 82%
Processors
96
16
Sybase 600%
Total cost
$8.4 Mil
$4.2 mil
Sybase 100%
• Oracle had to spend two times as much to get only 9% more
performance
• Oracle ran on six times as many processors (96 vs. 16) to get only
9% more performance
• If you want to spend more on hardware & maintenance buy
Oracle. Sybase is the performance and scalability leader
Conclusion
•
•
•
•
•
•
A True Relational Database
Follows the SQL2 standards
Rank 3nd in RDBMS
Easier to use and learn than Oracle
Similar features and structures as Microsoft SQL 2000
Lower price in comparing to other DBMS