Ase 137 what s new in ase 12 5 1 l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 91

ASE 137: What’s new in ASE 12.5.1! PowerPoint PPT Presentation


  • 149 Views
  • Uploaded on
  • Presentation posted in: General

ASE 137: What’s new in ASE 12.5.1!. Kannan Ananthanarayanan 925.236.4535 [email protected] ASE 12.5.1 Release Theme. REDUCE TOTAL COST OF OWNERSHIP ENABLE NEW APPLICATIONS. Operational Scalability & Performance Ease-of-Use/Self-Management High Availability

Download Presentation

ASE 137: What’s new in ASE 12.5.1!

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Ase 137 what s new in ase 12 5 1 l.jpg

ASE 137: What’s new in ASE 12.5.1!

Kannan Ananthanarayanan

925.236.4535

[email protected]


Ase 12 5 1 release theme l.jpg

ASE 12.5.1 Release Theme

REDUCE TOTAL COST OF OWNERSHIP

ENABLE NEW APPLICATIONS

  • Operational Scalability & Performance

  • Ease-of-Use/Self-Management

  • High Availability

  • SQL compatibility & Internationalization

  • Information Integration

  • Security


Ase 12 5 1 operational scalability performance l.jpg

ASE 12.5.1 – Operational Scalability & Performance

  • Dynamic Data Caches

  • Statement Caches

  • Dynamic Network Listeners

  • Parallel Checkpoints


Ase 12 5 1 high availability l.jpg

ASE 12.5.1 – High Availability

  • Fast (Parallel) Recovery

  • Mount/Unmount of Databases

  • Veritas Cluster Support


Ase 12 5 1 ease of use self management l.jpg

ASE 12.5.1 – Ease of Use / Self Management

  • Job Scheduler

  • Automatic Database Extension

  • Management Tools improvements

    • ASE Plug-in Usability improvements

    • Unified Installation using Installshield


Ase 12 5 1 sql compatibility internationalization l.jpg

ASE 12.5.1 – SQL compatibility & Internationalization

  • Date and Time Data Type

  • Derived Tables

  • SQL standards compatibility

  • Unicode enhancements


Ase 12 5 1 security l.jpg

ASE 12.5.1 – Security

  • LDAP User Authentication


Ase 12 5 1 information integration l.jpg

ASE 12.5.1 – Information Integration

  • Native XML support

  • Web Services Support


Ase 12 5 1 feature details l.jpg

ASE 12.5.1 Feature Details

Dynamic Data Caches


Dynamic data caches l.jpg

Dynamic Data Caches

  • Cache configuration changes take effect at run-time

  • Benefits

    • Creating new caches and binding “hot” objects without a server reboot

    • Adding more memory to caches (including the default data cache) having low hit% without a server reboot

    • Deleting unused caches online, giving the ability to re-use the same memory


Dynamic data caches usages l.jpg

Dynamic Data Caches: Usages

  • No additional configuration parameters – No changes to sp_cacheconfig

sp_cacheconfig 'transaction_cache', '10M’

go

The change is completed. The option is dynamic and the SQL Server

need not be rebooted for the change to take effect.

sp_bindcache 'transaction_cache', orders_db, trans_tab

go

sp_cacheconfig 'transaction_cache', ‘20M’

go

10240 Kilobytes added successfully to named cache (transaction_cache).

Change takes effect immediately.

sp_cacheconfig ‘unused_cache', ‘0’

go

The change is completed. The option is dynamic and the SQL Server

need not be rebooted for the change to take effect.


Dynamic data cache limitations l.jpg

Dynamic Data Cache: Limitations

  • Limitations

    • Shrinking a cache is not supported

    • Changing cache partitions is static

    • Changing replacement policy is static

    • To delete a cache, there should be no objects bound to it

      NOTE: For all caches other than default data cache, in order to change the static parameters, the cache can be deleted and re-created with the required parameter (shrinking, cache partitions, replacement policy)


Dynamic data caches cache wizard l.jpg

Dynamic Data Caches: cache wizard

  • A new option to sp_sysmon “cache wizard” helps in

    • Identifying hot objects in a cache

    • Evaluating effectiveness of Large buffer pools

    • Sizing data caches

    • Evaluating effectiveness of APF


Ase 12 5 1 operational scalability performance14 l.jpg

ASE 12.5.1 – Operational Scalability & Performance

STATEMENT CACHE


Ase 12 5 1 statement cache l.jpg

ASE 12.5.1 - Statement Cache

Description

An ASE performance improvement for applications that

  • Use standard SQL (not stored procedures)

  • Execute the same statements more than once

    • Within one application

    • Across instances of the same application

    • Across different applications


Ase 12 5 1 statement cache16 l.jpg

ASE 12.5.1 - Statement Cache

Qualifying Statements

  • Adaptive Server caches the following statements:

    • SELECT statements with at least one table

    • UPDATE and DELETE statements

    • INSERT statements with a sub SELECT instead of a VALUES clause

  • Adaptive Server does not cache the following:

    • Statements that reference temporary tables

    • SELECT INTO statements

    • Cursor SELECT statements

    • All other statements outside the above list of cachable statements


Ase 12 5 1 statement cache17 l.jpg

SQL statement to

querytree

Optimize and compile queryplan

of ltwt procedure

Execute queryplan

Cache Text. Normalize qrytree. Create ltwt proc

Transform statement to EXEC ltwt proc

ASE 12.5.1 - Statement Cache

Caching a New Statement

Parse

Normalize

Compile

Execute

SQL in Cache? No


Slide18 l.jpg

SQL statement to

querytree

SQL in Cache? Yes

Execute queryplan

Transform statement to EXEC ltwt proc

ASE 12.5.1 - Statement Cache

Executing a Cached Statement

Parse

Normalize

Compile

Execute


Ase 12 5 1 statement cache19 l.jpg

ASE 12.5.1 - Statement Cache

Using the cache

  • The statement cache is off by default

  • The SA turns on caching with the server-wide configuration option “enable statement cache”

  • When the statement cache is enabled in Adaptive Server, individual sessions control its use by:

    • set statement_cache {on|off}

  • New monitor counters track cache hits and misses

    • Reported under “Procedure Cache Management” section of sp_sysmon output

  • New DBCC commands allow SA to

    • View contents of cache – dbcc prsqlcache

    • Purge cache – dbcc purgesqlcache


Ase 12 5 1 statement cache20 l.jpg

ASE 12.5.1 - Statement Cache

Memory & Performance Impact

  • The statement cache uses the Procedure Cache memory pool

  • Each statement consumes as much memory as needed for the SQL text plus a small overhead for other identifying information

  • Adaptive Server saves the queryplan for a cached statement in the procedure cache, (same as for stored procedures)

  • For two concurrent uses of the same statement, there will exist one entry in the statement cache although there may be more than one queryplan in the procedure cache

  • When the queryplan for a cached statement ages out of the procedure cache, then if there remain no other copies of the queryplan in cache, Adaptive Server will drop the SQL text and descriptor from the statement cache and free its memory


Ase 12 5 1 statement cache21 l.jpg

ASE 12.5.1 - Statement Cache

Details

  • The text of two statements must match exactly to share the same queryplan. In a future release Adaptive Server will

    • Ignore white space differences

    • Automatically parameterize constant values, e.g., when used as search arguments, for more extensive queryplan sharing

  • The Statement Cache will consume object descriptors and procedure cache memory

    • May need to configure more of these resources

  • Performance Improvements will vary depending on

    • The length of compilation time for a cached statement

    • The ratio of compilation to execution time

    • The ratio of cache hits to misses. Cache misses bear (small) overhead of creating the lightweight procedure


Ase 12 5 1 operational scalability performance22 l.jpg

ASE 12.5.1 – Operational Scalability & Performance

DYNAMIC NETWORK LISTENERS


Ase 12 5 1 dynamic network listener l.jpg

ASE 12.5.1 – Dynamic Network Listener

Description & Benefits

DESCRIPTION

  • Allows flexible management of network listeners

    • Ability to START, STOP, SUSPEND and RESUME network listeners

    • Ability to get the status of the listeners

      BENEFITS

  • Help improve the performance by (de)activating the network listeners

  • Improve the availability (from application standpoint)

    DETAILS

  • Ability to start and stop network listeners at run time

  • Supports up to 32 network listeners

  • Every listener consumes one user connection

  • Each listener can service multiple engine


Ase 12 5 1 dynamic network listener24 l.jpg

ASE 12.5.1 – Dynamic Network Listener

Configuration & Examples

  • Sp_listener command can be used to manage the listeners

    • Sp_listener “command”, “server_name”, engine | remaining

    • Sp_listener “command”, “[protocol:]machine:port”, engine | remaining

  • STOP

    • Stop accepting connections; Closes the listener port

    • Does not remove the entries from the interfaces file

  • SUSPEND

    • Stop accepting new connections; Does NOT close the listener port

  • REMAINING OPTION

    • Applies the change only where applicable

    • Example

      • Sp_listener “start”, “ASE1251”, “3-6”, “remaining”

        • Even if one of the engines is unavailable, command will SUCCEED


Ase 12 5 1 operational scalability performance25 l.jpg

ASE 12.5.1 – Operational Scalability & Performance

MULTIPLE CHECKPOINT TASKS


Ase 12 5 1 multiple parallel checkpoint tasks l.jpg

ASE 12.5.1 – Multiple (Parallel) Checkpoint Tasks

DESCRIPTION

  • A pool of checkpoint tasks work on the list of active databases

    BENEFITS

  • Improves the service interval of checkpoint for different databases

  • Helps speed up recovery and improve availability

    CONFIGURATION

  • Use the following to adjust the number of checkpoint tasks you need

    • Sp_configure “number of checkpoint tasks”, #

    • Default value is 1

    • Maximum is 8


Ase 12 5 1 high availability27 l.jpg

ASE 12.5.1 – High Availability

  • Fast (Parallel) Recovery

  • Mount/Unmount of Databases

  • Veritas Cluster Support


Ase 12 5 1 feature details28 l.jpg

ASE 12.5.1 Feature Details

FAST RECOVERY


Ase 12 5 1 fast recovery l.jpg

ASE 12.5.1 – Fast Recovery

PRIMARY BENEFITS

  • Improves the availability after a shutdown, crash or HA Failover

    DESCRIPTION

  • Enhances the performance of a single database recovery

  • Recovers multiple databases in parallel

    • Currently applies only for user databases

    • Take into account the database recovery order, if any specified

      CONFIGURATION

  • Sp_configure “max concurrently recovered db”

    • Determines the maximum number of databases to be recovered in parallel

    • Default value is 0 (self tuning based on I/O subsystem effectiveness)

    • Minimum value is 1 (ie., no parallelism – pre 12.5.1 behavior)


Ase 12 5 1 feature details30 l.jpg

ASE 12.5.1 Feature Details

MOUNT/UNMOUNT


Ase 12 5 1 mount unmount l.jpg

ASE 12.5.1 – Mount/Unmount

DESCRIPTION

  • Means to move and copy databases between ASE installations

  • Used to move databases by operation at device level

  • A file (know as manifest) is used to describe the mapping for Mount/Unmount

    BENEFITS

  • Improves the availability and helps in disaster recovery

  • Fastest way to create copy of a database for distribution

  • Fastest way to transport/distribute databases between ASE servers


Ase 12 5 1 mount unmount32 l.jpg

ASE 12.5.1 – Mount/Unmount

EXAMPLE/SYNTAX

UNMOUNT database <database name list> to <manifest file>

UNMOUNT database pubs2 to “/work/pubs2.mfs”

MOUNT database all from <manifest file> [with listonly | using <name> = <value>]

MOUNT database all from “/work/pub2.mfs

DETAILS

  • SUID between source and target ASE must match

  • Mapping of path names is possible at mount time

  • System databases cannot be made portable (except sybsystemprocs)

  • Mount/Unmount commands are not allowed in a transaction

  • Mount database command is not allowed in a server configured for HA


Ase 12 5 1 mount unmount33 l.jpg

ASE 12.5.1 – Mount/Unmount

COPYING A DATABASE

  • Quiesce the database with manifest option

  • Copy the database devices and manifest file

  • Mount the database

  • Load transaction

  • Online the database

    MOVING A DATABASE

  • Unmount the database

  • Mount the database

  • Online the database


Ase 12 5 1 feature details34 l.jpg

ASE 12.5.1 Feature Details

VCS 3.5 support


Ase 12 5 1 veritas cluster agent l.jpg

ASE 12.5.1 – Veritas Cluster Agent

BENEFITS

  • Improves the availability

  • Reduces the cost of ownership (HA setup)

  • Eliminates the need to write custom scripts

  • Makes it easy to certify with new VCS releases


Ase 12 5 1 veritas cluster agent36 l.jpg

ASE 12.5.1 – Veritas Cluster Agent

DESCRIPTION

  • Provides a standalone agent to support Active/Active cluster support

  • Standalone agent will run with Veritas VCS 3.5

  • Eliminate the need for Veritas Database Edition for HA

  • Manages resources of type HA ASE associated with ASE on Veritas Cluster Server (VCS)

  • It takes a resource online, brings a resource offline and monitors a resource to determine its state

  • Components

    • Resource definition file

    • Agent binary executable file including monitor function

    • Agent scripts

    • Agent installation tool


Ase 12 5 1 veritas cluster agent37 l.jpg

ASE 12.5.1 – Veritas Cluster Agent

CONFIGURATION

  • Two homogenous, networked system with similar configurations in terms of system resources like CPU, memory, network connection

  • The two systems should be installed with Solaris 8 and VCS 3.5

  • The two systems should be installed with Veritas Volume Manager 3.1 or later version to manage disks and create resources like DiskGroup and Volume

  • The two systems must have access to shared multi-host disks which store the databases for Adaptive Server

  • Use third-party vendor mirroring for media failure protection

  • Create a service group on each system. The service group for Adaptive Server should include such resources as DiskGroup, Volume, Mount, IP, NIC and HA ASE (new resource type for Adaptive Server). A figure describing a sample configuration is in the product manual


Ase 12 5 1 veritas cluster agent38 l.jpg

ASE 12.5.1 – Veritas Cluster Agent

LIMITATIONS

  • Only support active-active HA configuration

  • Only available for ASE on Solaris. (The support for ASE on Linux is planned for future release)

  • Customized monitoring SQL script is not supported

  • Only provide HA support for Adaptive Server, no HA support for auxiliary servers such as backup server


Ase 12 5 1 ease of use self management39 l.jpg

ASE 12.5.1 – Ease of Use / Self Management

  • Job Scheduler

  • Automatic Database Extension

  • Management Tools improvements

    • ASE Plug-in Usability improvements

    • Unified Installation using Installshield


Ase 12 5 1 feature details40 l.jpg

ASE 12.5.1 Feature Details

Job Scheduler


Ase 12 5 1 job scheduler l.jpg

ASE 12.5.1 – Job Scheduler

Description

  • Principle Components

    • Internal ASE task

    • External process called the Job Scheduler (JS) Agent

    • sybmgmtdb database and stored procedures

    • Graphical user interface

      Benefits

  • Ability to schedule the activities that otherwise require manual interaction

  • Relieve DBA to focus on more important tasks


Ase 12 5 1 job scheduler components l.jpg

ASE 12.5.1 – Job Scheduler - Components

  • Internal Task

    • Determines when scheduled jobs should run

    • Starts the JS Agent and feeds JS Agent the necessary information

      • user name, password, and the id of the scheduled job

    • Creates historical record of jobs that are run

  • Job Scheduler Agent

    • Retrieves the job information from the sybmgmtdb database

    • Issues the job commands

    • Logs any result to sybmgmtdb database

  • Sybmgmtdb

    • Stores all the job, schedule and scheduled job information

    • Most access to Sybmgmtdb is through Stored Procedure

    • JS Task is the only one which access this database directly


Ase 12 5 1 job scheduler components43 l.jpg

ASE 12.5.1 – Job Scheduler - Components

  • Graphical User Interface

    • Assists user in creating and scheduling jobs

    • Assists in viewing job status and job history

    • Allows turning on and off of the ASE internal task

  • Templates

    • Tool to define parameterized tasks

    • Implemented as batch T-SQL commands for which parameter values can be provided.

    • Used for generating jobs, which can be scheduled to run at desired times.


Ase 12 5 1 job scheduler components44 l.jpg

Client GUI

Command Line

Stored Procedures

Tables

JS Agent

Job Scheduler

Internal Task

Target ASE

ASE Server Process

ASE 12.5.1 – Job Scheduler - Components


Ase 12 5 1 job scheduler catalogs l.jpg

Command Line

Client GUI

Stored Procedures

ASE task wake-up

JS_HISTORY

JS_SCHEDULES

JS_SCHEDULED JOBS

JS_OUTPUT

JS_CALLOUTS

JS_JOBS

ASE 12.5.1 – Job Scheduler - Catalogs


Ase 12 5 1 feature details46 l.jpg

ASE 12.5.1 Feature Details

Automatic Database Extension


Ase 12 5 1 automatic database extension l.jpg

ASE 12.5.1 – Automatic Database Extension

DESCRIPTION

  • Out-of-the-box threshold action procedure to manage database spaces

  • Automatically add database devices on-demand

  • Automatically extend the database upon hitting predefined thresholds

    BENEFITS

  • Flexible database space management for DBAs

  • Improve the availability (from application standpoint) by automatically extending the space, instead of blocking on transactions


Ase 12 5 1 feature details48 l.jpg

ASE 12.5.1 Feature Details

Usability Enhancements

  • Sybase Central/ASE Plug-in

  • Installer Enhancements


Ase 12 5 1 sybase central ase plug in l.jpg

ASE 12.5.1 – Sybase Central/ASE PLUG-IN

  • LDAP Directory Support

  • Quiesce Database Support


Ase 12 5 1 sybase central ase plug in50 l.jpg

ASE 12.5.1 – Sybase Central/ASE PLUG-IN

  • Support for Mount/Unmount functionality


Ase 12 5 1 sybase central ase plug in51 l.jpg

ASE 12.5.1 – Sybase Central/ASE PLUG-IN

  • Support for Multiple Temporary Databases


Ase 12 5 1 sybase central ase plug in52 l.jpg

ASE 12.5.1 – Sybase Central/ASE PLUG-IN

  • Support for CIS/Proxy Tables


Ase 12 5 1 sybase central ase plug in53 l.jpg

ASE 12.5.1 – Sybase Central/ASE PLUG-IN

  • SQL Command Log


Ase 12 5 1 sybase central ase plug in54 l.jpg

ASE 12.5.1 – Sybase Central/ASE PLUG-IN

  • Table Data Display & Enhanced Table Editor


Ase 12 5 1 installer l.jpg

ASE 12.5.1 – Installer

BENEFITS

  • Better installation interface

  • Single/Unified Installation interface for all platforms

  • Better (seamless) uninstall

    DESCRIPTION

  • Based on Installshield (as opposed to Studio Installer)

  • All platforms including Linux has same look and feel for Installation

  • Number of usability enhancements have been made


Ase 12 5 1 sql compatibility internationalization56 l.jpg

ASE 12.5.1 – SQL compatibility & Internationalization

  • Date and Time Data Type

  • Derived Tables

  • MS-SQL syntax compatibility

  • Unicode enhancements


Ase 12 5 1 feature details57 l.jpg

ASE 12.5.1 Feature Details

DATE & TIME DATA TYPE


Ase 12 5 1 date time data types l.jpg

ASE 12.5.1 – Date & Time Data Types

  • Most often requested data types – Date & Time

-- Add one day to a time;

-- time remains the same

declare @a time

select @a = "14:20:00"

select DATEADD(dd, 1, @a)

--------------------------

2:20PM

declare @a date, @b date

select @a = "apr 1, 1999"

select @b = "apr 2, 1999"

select DATEDIFF(hh, @a, @b)

-----------

24


Ase 12 5 1 date time data types59 l.jpg

ASE 12.5.1 – Date & Time Data Types

Details

  • Functions are enhanced to take new data types

    • DATEADD, DATEPART, DATENAME, DATEDIFF

  • Allowable Implicit Conversions:

    • DATE  VARCHAR, CHAR, BINARY, VARBINARY

    • TIME  VARCHAR, CHAR, BINARY, VARBINARY

  • Allowable Explicit Conversions:

    • DATE DATETIME, SMALLDATETIME, UNICHAR, UNIVARCHAR

    • TIME DATETIME, SMALLDATETIME, UNICHAR, UNIVARCHAR


Ase 12 5 1 date time data types60 l.jpg

ASE 12.5.1 – Date & Time Data Types

Migration/Compatibility

  • Moving from pre-12.5.1 to 12.5.1

    • The new DATE and TIME types will be added to systypes

    • Use alter table to modify the column data type to new ones

  • Falling back from 12.5.1 to pre-12.5.1 release

    • No impact if new DATE or TIME datatypes are used

    • If tables use DATE/TIME columns

      • Drop the tables

      • Change column data type using alter table

    • If stored proc. uses DATE/TIME – Drop before falling back


Ase 12 5 1 feature details61 l.jpg

ASE 12.5.1 Feature Details

SQL DERIVED TABLES


Sql derived tables l.jpg

SQL Derived Tables

Description

SQL derived tables consist of two main parts

  • the outer query having the correlation name and an optional derived column list

  • the inner query expression which does the SELECT from the base tables

    Where can SQL Derived tables be used?

  • SQL derived tables can be used wherever a view can be used

  • SQL derived tables can be used in the FROM list of SELECT, SELECT INTO, CREATE VIEW and INSERT statements

  • SQL derived tables can be used in the FROM list of subqueries

  • SQL derived tables can be used wherever subqueries can be used like HAVING clause, CASE statement etc.,

    Example:

    select * from (select * from (select * from t1)d1(c1)) d2(c2)


Sql derived tables63 l.jpg

SQL Derived Tables

Semantics

  • SQL derived tables only exist for that query and are unlike temp tables which exist for the session

  • Joins between SQL derived tables and SQL derived tables and tables/views are allowed

  • There are no insertion into system catalogs for SQL derived tables as they are not persistent

  • SQL derived tables can be nested with maximum nesting level of 25

  • Correlated SQL derived tables are not allowed

  • PARALLEL, INDEX and PREFETCH options are not allowed

  • Unions are supported in SQL derived tables

  • Unions in SQL derived tables are treated like union views


Sql derived tables64 l.jpg

SQL Derived Tables

Key Details

  • Performance Implications

    • For ad-hoc queries, the performance should be better than views as there are no insertion into catalogs

    • For views which are used repeatedly, the performance should be equivalent as the query tree for views is cached

  • Permission Checking

    • No permission checking is done on SQL derived tables as they are non-existent

    • Permission checking is done on the base tables in the query expression

    • Permission checking for views with SQL derived tables in them follows the same rules as views without SQL derived tables


Ase 12 5 1 feature details65 l.jpg

ASE 12.5.1 Feature Details

Unicode Enhancements


Ase 12 5 1 unicode enhancements l.jpg

ASE 12.5.1 – Unicode Enhancements

DESCRIPTION

  • No longer need UTF-8 as the default charset for using unichar & univarchar

    • Unichar data type (UTF-16) are now available regardless of server’s character set

  • Support for Unicode string literals through implementation of U& syntax

    • ANSI SQL 2002 standard

    • New syntax is available for specifying Unicode in character literals

  • Extended (non-binary) the sort orders available for UTF-8 characters

    BENEFITS

  • Easier for ISV to port their application to Sybase

  • Encourage customer to migrate applications to Unicode


Unicode enhancements in 12 5 1 l.jpg

Unicode Enhancements in 12.5.1

  • Unichar data type (UTF-16) are now available regardless of server’s character set

  • Character literals in queries are promoted to Unichar when necessary

  • New syntax is available for specifying Unicode in character literals

  • Non-binary sort orders are available when server’s character set is UTF-8


Unichar data type availability l.jpg

Unichar data type availability

  • Unichar (UTF-16) data type

    • Permits Unicode data even in server with “legacy” character set (e.g. ISO-1)

    • Permits migration of existing applications to Unicode via incremental schema change. ex:

      • alter table t1 modify col1 univarchar(100)

    • No syntax changes required to application code


Character literal promotion l.jpg

Character literal promotion

  • Character literals in queries are promoted to unichar data type when they cannot be represented in the server’s charset

SJIS client

iso_1

server

select * from t1 where col1 = ‘ ’


Ase 12 5 1 feature details70 l.jpg

ASE 12.5.1 Feature Details

ASE 12.5.1 – SQL enhancements


Ase 12 5 1 sql enhancements l.jpg

ASE 12.5.1 – SQL enhancements

DESCRIPTION

  • Set of features to be more compatible with Microsoft SQL extensions

    • Use of DEFAULT in INSERT statement

    • Use of SET for variable/value assignment

    • Use of Square Brackets in Identifier Names

      BENEFITS

  • Easier for ISV to port their application to Sybase

  • Easier migration from MS-SQL server to Sybase ASE


Ase 12 5 1 sql enhancements72 l.jpg

ASE 12.5.1 – SQL enhancements

EXAMPLES

create table test_table (col1 tinyint , col2 tinyint default 10, col3 tinyint)

Without support for Default:

insert test_table (col1, col3) values ( 11, 43)

With support for Default:

insert test_table (col1, col2, col3) values (11, default, 43)

insert test_table values (11, default, 43)

Without set command: declare @a tinyint ; select @a = 10

With set command : declare @a tinyint ; set @a = 10

Set quoted_identified on

With square bracket support :

create table [toto2] (a tinyint)

Without square bracket support:

create table "toto2" (a tinyint)


Ase 12 5 1 security73 l.jpg

ASE 12.5.1 – Security

LDAP User Authentication


Ase 12 5 1 ldap user authentication l.jpg

ASE 12.5.1 – LDAP User Authentication

DESCRIPTION

  • ASE authenticates clients with data from LDAP

    • User authenticates with password stored in LDAP as opposed to syslogins

  • Multiple ASE can share user login data stored on the LDAP

  • Roles, group and database user information is still managed on ASE

    BENEFITS

  • Centralized password security policies in one authority

  • Simplified creation of new users or deletion of users

  • Simplified user password for both the operating system and application

  • Reduced overall cost of ownership.


Ase 12 5 1 ldap user authentication75 l.jpg

ASE 12.5.1 – LDAP User Authentication

CONFIGURATION

  • Need ASE_DIRS license

  • Enable the functionality using

    sp_configure “enable ldap user auth’, [0|1|2]

    DETAILS

  • Allows user authentication using ASE only, LDAP only or a flexible mode

  • The flexible mode tries to authenticates using LDAP and if that fails uses ASE

  • Flexible mode is very for during migration of authentication from ASE to LDAP

  • sp_ldapadmin stored procedure allows setting LDAP access information (URL)

  • If a user profile is deleted from LDAP, it still remains in ASE

    • SA/SSO must delete it explicitly

  • Failover support

    • Users can be authenticated using a primary and secondary LDAP

    • DBA must configure it with URL for primary and secondary LDAP


Ase 12 5 1 information integration76 l.jpg

ASE 12.5.1 – Information Integration

  • Integrated XML support

  • Web Services Support


Ase 12 5 1 feature details77 l.jpg

ASE 12.5.1 Feature Details

INTEGRATED XML MANAGEMENT


Ase 12 5 1 native xml management l.jpg

ASE 12.5.1 – Native XML Management

Description

  • Native XML storage, indexing and Query support

  • SQL/XML duality – With support for XPATH/XQUERY and SQLX

  • Rich SQL  XML and XML  SQL transformation

  • Details

    • Eliminates the need to have JAVA in ASE (compared to 12.5.0.3)

    • Use sp_configure “enable xml” turn this functionality ON/OFF

    • Licensed Option – Available for FREE in Developer Edition


  • Ase 12 5 1 native xml management benefits l.jpg

    ASE 12.5.1 – Native XML Management - Benefits

    • NATIVE XML STORAGE & INDEXING

      • Store and Retrieve well formed XML data

      • Schema independent storage

      • Fast XML Indexes for high query performance

      • Self defined indexes – No user input necessary

    • SQL/XML DUALITY

      • XPATH/XQUERY and SQLX support

      • Complete SQL/XML interoperability at language and storage level

      • Granular results – Returns documents, document fragments, elements

      • Wild card support for complex queries

    • XML TRANSFORMATION & MAPPING

      • Allows XML transformations on stored XML document and the XML View

      • Maps SQL data as XML, and XML as SQL data


    Ase 12 5 1 xml engine architecture l.jpg

    Path Processor

    ASE 12.5.1 - XML Engine Architecture

    ASE

    Native XML Engine

    XML Query Engine

    Table

    XML Query

    Applications

    XML Store Engine

    XML Data

    XML Parser

    I/O Streaming

    File

    CIS


    Xml storage and indexing l.jpg

    CUSTOMER

    TRADES

    DETAIL

    DETAIL

    CID

    TID

    XML

    indexes

    XML Storage and Indexing

    • Store and Retrieve XML natively

    • Schema independent and can be dynamic

    • Shred XML and store as Relational data

    • Fast XML Indexes (like as B-Tree for relation data)

    • Self defined indexes – No user input necessary

    • Provides high performance

    • Support XQUERY,XPATH and SQLX

    • Wildcards and functions for complex querying

    XMLDocument

    SHRED

    SELECT …

    FOR XML

    STORE AS TEXT

    XMLDocument

    PARSE

    PARSE

    XML

    PARSER

    ASE


    Sql and xml duality l.jpg

    CUSTOMER

    RESULTS

    TRADES

    DETAIL

    DETAIL

    DETAIL

    RID

    TID

    CID

    XML

    indexes

    SQL and XML Duality

    • Interoperability between XML and SQL at the language and storage level

    • Support XPATH/XQUERY and SQLX

    • select …. from … FORXML to generate results in XML

    • select …. Xmlparse (doc) to parse xml document seamlessly

    • Functions to map SQL results to XML and to map XML docs to SQL

    SELECT …

    FOR XML

    SQL RESULT

    SQL + XPATH QUERY

    XMLDocument

    XMLDocument

    XMLDocument

    XPATH QUERY

    XMLRESULT

    ASE


    Ase 12 5 1 feature details83 l.jpg

    ASE 12.5.1 Feature Details

    ASE Web Services


    Web services support in ase 12 5 1 l.jpg

    Web Services Support in ASE 12.5.1

    Description

    • ASE as a Web Service Provider

      • Expose logic inside ASE as a Web Service

    • ASE as a Web Service Requestor

      • Use ASE to access a Web Service and use the result with other data

        Configuration

        Lightweight, Standalone binary

        Need ASE_WEBSERVICES license

        To enable this, use sp_configure “enable webservices”, 1


    Web services support in ase 12 5 185 l.jpg

    Web Services Support in ASE 12.5.1

    Details

    • Access T-SQL (stored procedures, functions) as web methods

    • Generate dynamic WSDL

    • Model Web Service as a proxy table

    • Persistent database connections via sessions

    • Support SSL

    • RPC/encoded web services support

    • document/literal web services support


    Ase web services provider l.jpg

    ASE Web Services Provider


    Example l.jpg

    Example

    • Execute select @@version through Web Services

    <ws xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <row>

    <C1>Adaptive Server Enterprise/12.5.1/XXXXX/P/Sun_svr4/OS 5.8/main/1778/32-bit/FBO/Wed Jun 25 05:15:25 2003</C1>

    </row>

    </ws>


    Ase web services consumer l.jpg

    ASE Web Services Consumer

    • Notify ASE of existence of ASE Web Services.

      • sp_addserver webservices, sds, webservices

      • Executed once per installation, typically at install time.

    • Import WSDL definition.

      • webservice...gen_sproc_from_wsdl "WSDL File URL", "ASE host name", ASE port number

      • Executed once for each web service to invoke.

    • Execute Web Service

      • select RRETURN from GETPRICE where _ISBN = '0694003611‘

      • Executed as needed.


    Ase web services requestor consumer l.jpg

    ASE Web Services Requestor/Consumer


    Xml web services integration l.jpg

    Full Text

    Search

    Web Service

    Web Service

    ASE - Web Services

    xml

    xml

    ASE - Web Services

    ODBC

    ORACLE,MSFT, IBM-DB2

    ODBC, Mainframe

    App

    logic

    ASE

    xml

    objects

    Ct-Lib

    Direct Connect

    sql-xml

    file

    system

    Applications

    XML & Web Services Integration


    Slide91 l.jpg

    Q & A


  • Login