Last updated: 5/7/03

Sybase Version Differences; lists of modifications/features/changes by version

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Desired Features not yet planned (from discussions in Sybase-L, ASE-Linux, etc)
- "before" triggers: ala Oracle. 
- user definied functions
- RLL in system tables
- Disable log in tempdb
- Full disable of all internal security checks (for systems w/ only one user) 
- update statistics history per table: type, date last run, etc
- "index rebuild notification facility" command that tells user when an index
needs to be rebuilt.


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Note; most platforms are being EOL'd as of 1/98 for all versions of sybase 
previous to System 11.  See DocIDs 2698, 2767, 20363, 20406 at
http://my.sybase.com/detail?id=xxxx or http://www.sybase.com/support/techdocs

---
Future Releases (Code word Galaxy)

14.0 (skipping 13.0, much like buildings don't have 13th floors??)
Anything for this release is rumored...

Admin
- plan to put all Optimizer outputs in set commands.

---
12.5: (code name Everest): Early 2001 release date.  Beta 12/00.
Full details at http://www.sybase.com/detail?id=1010961
12.5.1: End of 2002 release tentatively, now 3Q03

Java
- SQL-J, Java Stored Procedures
- XML Support; pushed off from 12.0
- EJB support: Enterprise Java Beans

SQL
- Unions in views
- Relaxation of conventional sybase limitations.  You can now increase
from varchar(255) to larger sizes.  Max size dependent on the page size
configured... varchar(1960) on a 2k page install, 16k for a 16k page install.

- Server-level increase of page size (4k, 8k, 16k)!  Great.  This
increases the effective rowsize of tables from current 1962 bytes to 
a far greater length.  Caveat: you can only dump and load between servers
with identical page sizes.

- Unichar (utf-16)
- Allows concatenation of strings to be inserted into text fields (upto
16K worth).
- Command line SQL debugger included; sqldbgr
- Increases from 255 to 16384 the character string limit passable to exec function.

- Dynamic Views (aka Derived tables): 12.5.1

Admin
- Dynamic Reconfiguration
- Identity can now be configured for true sequential key generation; You
can set a "maximum" identity gap to be accepted.  There are performance
issues with this method, but it eliminates the need for home grown 
sequential key generation schemes. (is this in 12.0?)
- Backup server able to compress dumps on the fly (ala SQL Backtrack)
- Capability of restoring tables only from dumps (like SQL backtrack): very nice!
- Ships with Open Client 12.5
- Backup server increases 32 stripe limit; experiences with at least 80+
stripes on a backup
- tempdb resource limitations per user/application (multiple tempdb)
- Inclusion of indcrdate column in sysindexes to track when an index was
last created or re-built!  finally.
- Windows2000 supported
- grant/revoke on truncate table, update statistics commands (12.5.1)
- grant on DBCC 12.5.0.1
- 12.5.1: Job Scheduler; allows dba to run update stats commands based on
configurable condisions.


Rumored:
- Integration of Sybase IQ engine into Adaptive server product 
- Improvements in Sybase IQ to handle more conventional SQL server 
structures (stored procedures, temp tables, etc) 
- Scalable clusters 
- Star Schema options 
- Parallel insert/update/delete operations 
- Partition level operations 
- Event scheduling 
- Security: SSL, data encryption 
- Linux 2.4.x raw device and asynch i/o supported


---
12.0 (code word Avatar): Scheduled late-3Q/early 4Q 1999.  Relased Dec 1999. 
Originally scheduled to be 11.9.5; decided to jump straight to 12.0

Java Stuff:
- Java-based Sybase central
- Installation program re-written in Java (unpopular)
- Complaints about the licensing features (support for Java features
 is quite expensive)
- Java extensibility within the server: meaning a JavaSoft compliant
 Virtual Machine

Sql:
- Increase to 50 table joins (was 16)
- ANSI SQL outer join syntax 
- execute immediately (ie execute a string as SQL)
- Extensible data types
- distributed transactions: server side 2-phase commits
- Better distributed transaction support
- Abstract Query plans: Basically allows a user to pre-determine
the exact query plan, bypassing the Optimizer.
- Dynamic SQL.  
- sort merge joins.

Admin
- Big: different way of referencing Data devices, meaning that filesystem 
devices are now "safe" to use.  You have to set a "non i/o write 
buffering flag" but its there.  However; common practice has ruled against
this.
- Windows2000 supported
- Rearchitected optimizer
- TEXT/IMAGE processing enhancements
- HA: clusters: log replication between pairs of servers (Built-in failover)
- offline [database] (for use in HA pairs)
- Dynamic engine online/offline
- Security improvements: Kerberos and DCE integration, single logins
- Backup improvements: 1tb/hour
- Native XA Server
- ODBC connectivity via XA server to achieve MTS processing
- Increased emphasis on OAM
- Point in time backups/recovery (great).  Meaning, you can reload 
transaction logs to a date/time like Oracle.
- Table-level control over Identity burn factor
- Alter table drop column supported!  Nice.  You can also modify the
 column datatype, shrink a column, etc.
- Alter table add non-null columns (with specified default values).  Very nice.
- alter table change null to non-null too: nice.
- Alter table command to temporarily disable triggers; useful when loading
databases to save time.
- Fix 2gb file dump size (with dump or bcp) over UFS limitation...You must
be configured to use large files though.  See technote #1011375
- Encryption of Stored procedure code within database.
- Admin: must create a new system database to handle new features.
- 32gb per device limit.  256 devices max per database...equating 
with a theoretical database size limit of a little less than 4terabytes.
(though sources say 8TB theoretical limit for databases) (this has been in
place since 11.5 apparently)
- Fully 64 bit.
- Bug #220929: v12.0 on Solaris; must increase stack size 3x normal or
update statistics stack traces (Sun issue, not Sybase).  Not fixed as of
Mid Dec, 2000.
- Some reports of text/image corruption when moving to 12.0; just make sure
you're on the latest EBF.  Suggested workaround is to run dbcc fix_text
(tablename).  Update: Sybase has reported a serious issue after upgrading
to v12.0.  See http://www.sybase.com/detail?id=1012153
- Ships with Open Client 12.0
- 4/19/01: Known Solaris bug w/ ASE 12.0 involving floating point errors in 
high i/o environments.  Workaround is to disable optimized libc libc_psr.so.1
in /usr/platform//lib.
- 4/27/01: bug #217318; timestamp mismatches in text pages.  Still open,
fixable with dbcc rebuild_text (syscharsets) when it occurs.
- 6/7/01: issues running 12.0 on Solaris 2.6; see tech note 1012883 or
http://www.sybase.com/detail?id=1012883

---
11.9.3: GA 1/28/99: 64Bit release (only certified on 64-bit operating
systems: HP 11, Solaris 2.7, Aix 4.3, Digital Unix 4.0d).  EOL'd 1/31/01
for all platforms, since 12 is 64 bit as well.

---
11.9.2: first Publically available version: 6/98 target date, actual release
date Oct 1998. (make sure you're at least at Ebf8139)

SQL:
- adding dynamic sql capabilities? i.e., reading variable names in a 
SQL statement
- Added Ansi-2 level isolation: repeatable reads.  Now have Isolation levels
0,1,2,3

- Concurrency changes
- Incredibly accurate statistics.  Optimizer costing algorithim are 
amazingly accurate now.  
- Statistics stored in system table (as opposed to a distribution page) 
and thus are modifiable.  Useful to simulate larger tables than you 
actually have. A tool (Optdiag) comes with the distribution to
automatically put statistics pages into human-readable form, too.
AND, you can simulate statistics with optdiag to see how a query would
change its plan with more or less rows...very nice feature.
- Optimizer issues: correlated subquery problems reported (but fixed).  
- Fixed long standing "bug" of allowing cursors to remain open after 
a commit (has to do with chained mode and ISO standard cursor behavior. 
(See Bug #131674 in 11.0.3 and Doc_id #2901 for more details)
- Increases RI checks from 16 to 192 per query (pushed off from 11.5)
- Reverse index scan traversal (now bi-directional) for query optimization.
(pushed off from 11.5)
- Forced joins to match datatypes, forces parameters being passed into 
stored procedures to match datatypes (else table scan)

Admin
- Row Level Locking: Hell has frozen over.  Actually this version allows 
for 3 different locking strategies on a per table basis!  Allpages, Dataonly
and original Page level.
- Improved bcp; Parallel bcp (if the table is partitioned)
- Sysmon table-level reporting
- lock table command.  No more need to hack
- Just announced: REORG is a new built-in command in ASE 11.9.x, it is 
not a seperate utility program.  This command is a database optimization 
scheme that will migrate rows back together that have moved apart because 
of inherent Row Level Locking processing.
- Omniconnect now completely contained in the CIS code
- Dump/Load performance: 100-200% increases documented
- Dbcc performance enhanced (possible bug on >1.5gb table checkallocs?)
- eliminated lct_admin(unsuspend) function to clear a suspended 
transaction.  Now need to lct_admin(abort)
- Downside?: Clustered indexes are now treated intermally as non-clustered 
constructs: the leaf-level pages are no longer on the same pages as the 
data.
- sysprocsdev min size 45MB: defaults to 60MB.
- Total memory (shared memory) addressable seems to be maxed out at 
3.75gb...despite the fact that its a 32-bit program and should be able 
to see 4gb->32gb.  Other users report it maxes at 4gb-1 byte.
- First Sybase version ported to Linux, for free.
- dbcc checkstorage; new dbcc command that performs some checks not avail 
in pre-11.5 dbcc versions.  Also designed as a non-impact Dbcc that can 
be performed while a database is online! (pushed off from 11.5)
- 1/9/01: most stable EBF; 9218?

Upgrade Issues:
- Statistics: upgrading gotcha exists: after upgrading make sure to rebuild 
your indexes (and delete and re-update stats).  Some upgrades 
have noticed terrible performance w/o redoing all the index and stats.  
This is because the optimizer really costs things differently.  Recompile 
all your Stored Procs too.
- Some implicit datatype conversions are illegal now (e.g., where int=smallint)
but can be overridden with trace flag 29.
- Upgrade issue from 11.0->11.9.2; sp_configure "open indexes" higher.
- Deadlocking increases w/ upgrade; frequently caused by "allow backward scans"
option being turned on by default.  Can also be caused by differing
optimizer paths with new version (after upgrade) or just the engine's sheer
speed.  Also Recommended to increase lock escalation threshold if using
RLL (defaults to 200; which isnt' a lot of rows).

---
11.9.1:  Released 3/98 to limited partner software companies 
(PeopleSoft, BAAN, Lawson, SAP in 11.9.1).  Test release of first Row
Level Locking version of Sybase.  EOL'd 1/31/01.

---
11.5 (Adaptive Server, code word Gryphon): Released Sept 1997...Peter Thawley 
considers it the most stable release Sybase has ever done (todate, 16 bugs 
found..none at P1 or P2 level).  Next scheduled release: 2Q1998 11.5.1.
EOL'd 12/31/2000.

- Async Pre-fetch: the most important feature in 11.5 according to Thawley
- SQL additions; case (when..then); finally mimics the Oracle decode
construct, coalesce(val1, val2, ..., valn), nullif(condition).
- Logical Process Manager: allows for cpu affinity configuring, priorizitation 
of processes.
- DBA configurable governers, allowing limiting of queries based on row 
returns, cpu time, etc.
- Will support Unix commands from within sybase w/o the need for syb_sendmsg 
or an external open server!!  This will be done via an included open server 
and through extended stored procedures.  xp_cmd 

- Lots of parallelization of tasks (supports parallel query optimization, 
parallel index scans, index builds, selects, and parallel dbccs)

- Device size increases!!!! from 2gb -> 32gb per device (Operating system
dependent... depending on whether the OS can handle files larger than 2gb)

- unique auto_identity index dboption; automatically adds the identity column 
to new tables created in a database, and automatically creates an index on 
the column too.
- Integrated OmniConnect.  Now you can use ASE as a front-end to IQ (using 
the Omniconnect product as a pass-thru) and thus obtain the ability to use 
stored procedures and temp tables in your IQ queries!
- Open Client v 11.x
- Will ship w/ bcp 11.1, which can bcp out of a view (Must have version 
11.x of bcp to be able to bcp out of a view..just having the server version 
at 11.5 is not sufficeint).
- Will ship w/ isql 11.x, which makes "go" case sensitive (be careful!)

- Hooks to allow encrypting connections built into the Open client api.
- dbccdb: dbcc output collection database
- Point-in-time transaction recovery...allows recovering of all 
tranactions until a particular time...very nice.
- connect to: feature; log into another server from with in isql.
- cross server joins; though there is a decent amount of setup required.
Setup a remote server in sysservers, and map proxy tables to remote table
structures.
- clustered indexes on partitioned tables
- Sybinit "modify interfaces file" option replaces with dsedit (X-based) 
and/or dscp (command line)
- sysprocsdev min size 45MB: defaults to 60MB.
- 1.7gb is max Shared memory size supported. potential features discussed 
but not confirmed:
- dump on [condition] feature...
- Ansi isolation levels 0,1,3 available. (no changes from 11.0.x)
- 16 tables in a join.
- Y2K compliant.
- Known bug w/ 16K i/o buffer pools where Optimizer choses table scans
automatically over non-clustered indexes.

---
11.0.x (code word Cougar): First released Dec 1995.  Next scheduled release
is 11.03.3, in 3Q1998. EOL'd 6/30/2000 (except for OpenVMS platform, which 
is supported through 12/31/2003).

- No more buildmaster
- No more reconfigure, or reconfigure with override.  Needed before, but now 
sp_configure values that can change w/o a server reboot, will.
- All configuration values tunable via text file in $SYBASE/[server].cfg
- Async processing ability added
- Added sp_sysmon to collect monitoring information.  Nothing comparable in 10 
or 4.9.2
- introduced max_rows_per_page option on tables; eliminates need to hack a
"full" row to emulate row level locking.
- Added 70 configuration parameters over version 10.
- sysprocsdev min 17mb at initial levels; 22mb at 11.0.3
- Increased # of concurrent users to the 1000s
- Increased supported database size to 500gb->1tb
- Adds Private Log Cache (PLC) and Named caches
- Tunable Block i/o sizes
- Performance wise, processing of exists/not exists changed to be essentially
the same; no more need to be careful using one or the other.
- New DBA tool/SA companion; Sql Server Manager.
- Adds Isolation Level 0; "dirty reads."  Shared reads essentially.  Reduces
contention.  Now has Ansi isolation levels 0,1,3 available.


---
10.0.x: Released mid-1994.  Major public relations nightmare for Sybase due 
to technical problems.  Marketing pushed through an unfinished product.
EOL'd 10/1/98 on Solaris, 1/1/98 on most other Platforms

- Ansi 89 compliant
- Added sybinit to add servers, modify interfaces, add char sets, etc
- Added Backupserver to process backup requests.
- Added sp_thresholdaction functionality.  Not avail in 10.
- Identity value added.
- Changed deadlock victim selection strategy.  Was selected by connection
time pre 10; now selected by accumulated physical i/o time.
- No more dumping to "/dev/null" allowed.
- Device size max 2gb (driven by Operating systems, not sybase).
- Isolation Levels 1 and 3 only

- Open Client version 10.0.x (maxed out at 10.0.4).  10.0.4 EOL'd 12/31/00

---
4.9.x: 4.9.2 released mid-1993.  Was the leading RDBMS of its time.
EOL'd 7/1/98 on Solaris, most other Platforms.

- no sysloginroles: added in 10.  A slight hassle when programming disaster 
recover collection scripts.
- "user" not a reserved word: made reserved for 10...this is one of the biggest 
hassles during conversions.
- dump database, dump tran can only be done to existing devices.  No capability 
to dump database to "file".  Also, such devices must be added manually by 
altering sysdevices!
- No comments via "--" line during isql executions.  Fixed in 10.
- sybload (not sybinit) to create new SQL servers
- Much operations stuff forced into syncrynous operations; only one dbcc at 
a time, one backup at a time, etc.
- No sybsystemprocs; all system procedures in master.  Added in 10.
- Allows null passwords...fixed in 11
- Stored procedures "grow" badly in 4.9.2; due to a) query tree growing when 
sp_recompile used and b) temp tables not cleaned up properly after use. Still 
an issue in 10, supposedly fixed in 11.
- passwords stored in syslogins in CLEARTEXT!!!: fixed in 10
- no sa, sso, other roles: fixed in 10
- Multiple segmented databases load neatly into newly created databases, unlike 
11 where segments get mapped all over the place.

---
4.8: released ?, generally EOL'd 12/1/96
4.2; released ?, generally EOL'd 3/31/96; (4.2.1a; final stable release?)
4.0.x: released ?, generally EOL'd 6/30/93
3.x: released ?, generally EOL'd 10/31/92: VMS, Sun, OS/2 platforms: 
beginning of shared codebase w/ Microsoft SQL Server (which ended w/ 4.21).  
Sybase and Microsoft shared development til 1993.

Why did Microsoft co-develop SQL server?  To combat a push by IBM with OS/2
in the late 80s.  OS/2 was being marketed as an alternative PC OS, and it
had an "extended" version that included a database engine.  Microsoft wanted
to compete, and OEM'd SQL Server to be MS Window's "extended" version.

2.0: ?? release date.
Previous versions: generally EOL 10/31/92

First Release SQL Server 1.0; released 1987

Sybase Founded 1984 in Berkeley Hills (California, East Bay)

---

Structured Query Language (SQL) History: Originally developed for use in
mainframe database systems (IBM).  In the mid 1970's, the INGRES (Interactive
Graphics Retreival System) project at UC-Berkeley created a modified query 
language called "QUEL."  QUEL was superior to SQL in some ways, and had
its supporters (those who took it and eventually created the Ingres system).
In the meantime, Oracle embraced SQL and released an engine in the early
1980s (though it was SQL over top of QUEL).  

Eventually a group spun out of the INGRES project, started the Britton-Lee
group.  They eventually start Sybase.  Sybase's key differentiators at the
time was its capability to run on UNIX and use UNIX's facets to its advantage
(raw disk, a multi-threaded single process on the UNIX box).  They used
SQL but were QUEL activists.  And their performance was better by a factor
of Three at the time.