E120 Whats New in ASE 12'5 and 12'5'0'3

1
E120Whats New in ASE 12.5 and 12.5.0.3

• Umesh Panchaksharaiah
• Staff Software Engineer II
• Enterprise Solutions Division
• panchaks_at_sybase.com

2
ASE 12.5 The Database Server that is
Portal-Ready
3
Database for the Enterprise

• Union in Views

4
What are Unions in Views?

• Support for the union /union all operators in the
  create view statement. Example
• create view uv as
• select from t1 union
• select from t2
• Key Benefit
• A large table can be split into subtables with
  data partitioned possibly based on ranges of data
  values in one of the solumns. This partitioning
  helps in concurrency and maintenance.
• Applications that want to view the complete data
  can then use union in views.
• ANSI SQL99 Core compliance

5
Restrictions/Limitations

• Union views are not updateable. Cant insert,
  delete or update using the view
• with check option is not allowed. Syntax error
  is raised at view creation time
• These restrictions conform with the core subset
  of ANSI SQL 99
• Number of tables in the view is limited to 256.
  Same limitations for union queries

6
CIS and Union In Views
7
Database for the Enterprise

• Fine Grain Access Control (FGAC)

8
Benefits of Fine Grain Access Control

• Key Benefits
• Provide additional and more granular data
  security
• Can be used as an automatic data filter Only
  see data for your group, application, and/or role
  
• Consolidate application/data security control
• Reduce developers hard-coding in the where
  clause
• Reduce developers responsibility of coding with
  specific user profiles and application profiles
  in mind
• Increase flexibility If users privileges
  change, administrator can change users groups,
  roles, or profiles to affect his/her data access
  accordingly
• More flexible than views

9
Motivation Examples
Multiple applications may have different access
privileges
10
Security Services in ASE (FGAC)

• Row Level Security (FGAC)
• Access Rules (AR) defined to control access to
  data at the levels of individual users, groups or
  specific roles
• Application Context Facility (ACF) useful
  application profiles or user profiles for
  developing tightly secured applications
• Login Trigger (LT) stored procedure executed at
  login time
• They can be used alone or together

11
Access Rules

• Enforced on SELECT, UPDATE, and DELETE operations
• Transformed into a where clause and attached to
  the user query
• Can be bound to columns / user-defined data types
• Condition expressions in the rules can be
  built-ins or java methods
• No performance degradation because the query is
  compiled and optimized after the access rules are
  attached

12
Access Rules (cont.)

• AND / OR access rules are used to specify the
  interaction between the rules in the same table
• When Access Rule is dropped or rebound, ASE 12.5
  will automatically renormalize and recompile the
  stored procedures that reference tables with the
  access rule
• Can filter foreign data through Component
  Integration Services (CIS) using Access Rules

13
Access Rules Example

• sp_addtype typeA, int
• create table X (A int, B typeA, )
• create access rule ruleA as
• _at_col suser_id()
• sp_bindrule ruleA, typeA
• select from X
• (is transformed to)
• select from X_id()
• Returns only those rows where value of column B
  matches suser_id()

14
Example (cont.) using the previous AR
15
Application Context Facility

• An Application Context consists of
• CONTEXT_NAME, (ATTRIBUTE, VALUE)
• Users can define various CONTEXTs and various
  correlating ATTRIBUTE/VALUE pairs for each
  CONTEXT
• CONTEXT is specific to a session, and not
  persistent across sessions

16
ACF Built-ins

• New built-ins are created to define, retrieve,
  and delete contexts
• The privilege to use these built-ins can be
  granted and revoked
• Example
• select set_appcontext(CONTEXT1, ATTR1,
  VALUE1)
• select set_appcontext(CONTEXT1, ATTR2,
  VALUE2)
• select get_appcontext(CONTEXT1, ATTR1)

17
Login Trigger

• A regular stored procedure which is initiated at
  user login time
• Can be used to set up an application contexts
  together with ACF
• Another usage
• If the stored procedure returns (-4), the user
  login will be aborted
• Can be used to block logins from certain users

18
Login Trigger (cont.)

• SA/DBA/SSO can set up login triggers for users
• Users can register login triggers for their
  logins
• Bind to a login by sp_modifylogin
• sp_modifylogin ltlogin namegt, login script,
  ltlogin trigger procgt

19
LT ACF AR
Provides powerful framework to implement
security policies.
20
Examples of Issues FGAC Addresses
21
Examples of Solutions to Prior Issues
select from X activates Access Rule on
column X.R which allows him to see only rows
where the Corporation (R) corporation_name
22
CIS supports FGAC

• Enable access rules with columns in proxy tables
• Push rules as predicates to remote site

• create rule emp_access_rule as _at_empno 10
• sp_bindrule emp_access_rule, employees.id
• select lastname, firstname from employees

Remote DBMS
employee
client
ASE/ Omni
select lastname, firstname from employees where
id 10
23
Database for the Enterprise

• CIS, External File System Support

24
CIS Support for External File Systems

• Support for external files in ASE will ensure an
• easy and cost-effective way of managing data that
• is used in e-commerce and typically stored in
  files.
• Key Benefits
• Ensures and easy and cost-effective way of
  managing data that is used in e-commerce and
  typically stored in external files.
• Can be integrated with Enhanced Full Text Search
  Engine.
• Can query XML files saved in external file system
  using Sybase XQL queries.

25
Support for External File SystemsArchitecture
26
External Directory AccessSyntax

• SQL access to file system directories is enabled
  via proxy tables
• create proxy_table D1 external directory at
  /work/project
• Optionally, CIS can recurse through all
  sub-directories (with R extension)
• create proxy_table D1 external directory at
  /work/projectsR

27
Proxy Tables for Directory Access

• The proxy table for the directory contains the
  following columns

28
Proxy Tables for Directory Access(cont.)

• Each directory entry is visible as a row in the
  proxy table
• Each entry is defined by its attributes (name,
  type, etc.), plus contents
• Attributes of directory entries are converted to
  SQL data types
• Attributes derived from system functions like
  readdir(), fstat()
• Contents of regular files stored via content
  column
• Always treated as an image datatype
• Content column is null for non-regular files or
  ASE doesnt have access privileges to the files

29
Proxy Tables for Directory AccessOperations

• Insert causes new file to be created in directory
• Update allows file name changes
• Delete causes file to be deleted
• Select returns columns as required
• Readtext / writetext access and modify the
  contents of the files

30
External File AccessSyntax

• create proxy_table f1 external file at
  /work/projects/myfile.dat
• Results in one column, named record,
  varchar(255) null
• create existing table f1 (name
  varchar(30),hiredate datetime,empno int)
  external file at /work/projects/myfile.dat
• Any data type can be used except text, image or
  Java ADT
• If the file doesnt exist, it is created if
  privileges allow

31
Proxy Tables for File AccessOperations

• Insert
• Each row is written to the file as a line
  terminated by a new line character
• Internal data types are converted to character
  strings
• Each column is separated by tab
• Rows are appended to file
• Select
• Each line in the file is extracted into a row
• Character strings are converted into column data
  types
• Lines wider than the row length will be truncated
• Truncate clears the file
• Update and Delete not allowed

32
Proxy Tables for File AccessUsages

• Transfer data between tables and external files
• sp_addobjectdef, f1, /work/projects/myfile.txt,
  file
• select name, id, hiredate into f1 from Employees
• Useful for exporting ASE data to other
  applications that cannot otherwise be accessed
  via CIS

33
Database for the Enterprise

• Compressed Dump/Load

34
Compressed Backup

• Provides compression support to dump archives
• Both database and transaction log dumps can use
  this facility
• Compression level is specified at dump time
• The optional compression_level is a value between
  0 (no compression) to 9 (best compression)
• Default compression level is set to 1 considering
  beta customers feedback

35
Backup Server API (compression module)
36
Compressed Backup Syntax

• dump database ltdbnamegt tocompressltcompression
  _levelgtfilename
• dump tran ltdbnamegt tocompressltcompression_lev
  elgtfilename
• load database ltdbnamegt from compressfilename
• load tran ltdbnamegt from compressfilename

37
Database for the Enterprise

• Continuous Availability

38
Enhancements to HA Solution

• Sybase supports an unique solution to reduce ASE
  downtime.
• The solution is extended to other disk vendors.
• Key Benefits
• Reduce server downtime
• Provide full support for storage vendors like EMC
  and Net Appliance who provide snapshot facilities

39
Feature Description

• The quiesce database command was introduced in
  12.0 to allow use of disk mirroring as a warm
  standby backup technique
• dump database and load database, sequential
  operations which can take hours each, can be
  bypassed through this single operation which
  takes much less time

40
Customer Usage ASE 12.0

• Repetitive use enabled in 12.0.0.1 ESD 1

41
Customer Usage ASE 12.5

• sequence of load transactions enabled in 12.5

42
Customer Usage (cont.)

• 12.5 adds a -q command line flag to ASE for the
  benefit of recovery. When set
• Each of the user databases copied under quiesce
  database hold will be recovered as for load
  database
• No undo pass will be performed generation of
  compensation log records is thus avoided
• Databases will be left off line
• When the -q command line flag is not set
• All databases are recovered in normal boot-time
  style
• Undo pass will be performed
• Databases will be brought online

43
Database for the Enterprise

• TempDB Resource Limits

44
TEMP DB Resource Limits

• tempdb_space is a new resource limits can be
  set by SA/DBA via sp_add_resource_limit
• to limit total space used by a given session in
  TEMPDB
• for parallel query, the limit is evenly
  distributed among the parallel threads
• Control run-away tasks/applications that consume
  tons of space in TEMPDB (e.g. multiple
  SELECT-INTO, or large work tables, etc.)
• If the limit is exceeded, based on the action
  defined, the session will be terminated, or the
  batch or transaction will be aborted

45
Database for the Enterprise

• SQL Debugger

46
SQL Debugging Facility

• ASE provides SQL debugging infrastructures to
  debug stored procedures and triggers
• This SQL debugging support can be integrated by
  vendors like Embarcadero Technologies and BMC
  into their IDE
• sqldbgr is a command-line utility provided by
  Sybase that also utilizes the SQL debugging
  facility

47
SQL Debugger (sqldbgr)

• One can attach sqldbgr to a task
• Or run a stored procedure from sqldbgr
• Set, delete, enable, disable and show
  breakpoints.
• Step through a task one at a time
• Step into and out of procedures
• Show and set variables
• Detach sqldbgr from stored procedures or triggers
  once the debugging is complete

48
SQL Debugger (sqldbgr) example

• SYBASE/ASE-12_5/bin/sqldbgr U sa -P S
  MERCURY16896
• (sqldbg) stop in sp_who
• Breakpoint moved to line 20
• (sqldbg) run sp_who
• (sp_who20)if _at__at_trancount 0
• (sqldbg) next
• (sp_who22) set chained off
• (sqldbg) cont
• fid spid status loginame origname hostname
  blk_spid dbname cmd block_xloid
• 0 2 sleeping NULL NULL 0 master
  NETWORK HANDLER 0
• 0 11 sleeping sa sa .
• (sqldbg) show breakpoints
• 1 stop in sp_who

49
Database for the Enterprise

• LDAP, SSL

50
Directory Services in ASE

• Using Lightweight Directory Access Protocol
  (LDAP) for integrated directory services.
• Key Benefit
• Server information that is spread in multiple
  interfaces files can be available from a single
  LDAP server

51
LDAP in ASE 12.5 Advantages

• Serves existing enterprise customers who want to
  use the lightweight industry-standard LDAP
  protocol
• Enables enterprise/portal administrators to
  provide a directory service for the components in
  the enterprise or portal
• Supports LDAP V3 version

52
LDAP in ASE 12.5 Usage

• ASE 12.5 and Open Client / Open Server can be
  configured to use LDAP services to define server
  connection information
• Make sure that the LDAP server is set up,
  configured, and running
• Add entries for Sybase servers to the LDAP
  directory using existing tools such as dsedit,
  dscp
• Change libtcl.cfg file to instruct the Sybase
  clients and servers to use LDAP services

53
Usage of LDAP services
54
Security Services in ASE (SSL)

• Wire level security in ASE will ensure secure
  data transmission over the internet.
• Key Benefits
• Wire level security using Secure Socket Layer
  (SSL) protocol
• Using Public Key Infrastructure (PKI) and digital
  certificates for authentication, encryption and
  end-to-end security
• Support digital certificates from Certificate
  Authorities (CA) like Entrust and Verisign
• SSL v 3.0.7 integrated with CTLIB and ASE 12.5

55
SSL in ASE Configuration

• ASE 12.5 provides a static configuration
  parameter to let the user optionally use SSL to
  establish secure connections enable ssl
• By default, the service is turned off
• To enable it
• sp_configure enable ssl, 1

56
Secure Connections with SSL in ASE 12.5
57
Secure Connections with SSL in ASE 12.5
58
Database for the Enterprise

• Dynamic Configuration

Enterprise Support
59
Dynamic Configuration

• Dynamic Configuration will reduce server downtime
  and will save server maintenance costs.
• Key Benefits
• Significantly reduces the server downtime caused
  due to configuration changes and increases the
  server availability
• Provides infrastructure for an external tuning
  agent that can automatically adjust configuration
  parameters to deal with server load changes
• Dynamic Configuration will continue to evolve.
  One of the enhancements in progress is dynamic
  data caches.

60
Configuration Parameters

• What is a configuration parameter ?
• A mechanism to alter system resource sizes,
  system behavior to provide the ability to tune
  the system for performance, resources, etc.
• There are two types of config parameters
• Static - requires a server reboot for any change
  in config parameter to take effect.
• Dynamic - change in config parameter takes effect
  immediately without requiring a reboot.

61
Config parameters - changes summary

• A total of 42 config parameters converted from
  static to dynamic (see appendix for complete
  list) and some dynamic configure parameters are
  added to replace deleted static ones. Some
  examples
• procedure cache size
• number of locks
• number of open indexes
• number of open objects
• number of user connections
• number of worker processes etc.
• 8 configure parameters deleted.
• 5 new dynamic related configure parameters.
• In 12.0, we have 96 static and 89 dynamic config
  parameters.
• In 12.5, we have 50 static and 142 dynamic config
  parameters.

62
In pre-125 ASE, size of shared memory allocated
during boot time was fixed and never grew during
run-time.
63
Memory related configure parameter changesMemory
allocation in pre-12.5 ASE

• Pre-12.5 ASE allocated no additional shared
  memory once it had been booted.
• Changing the value of the configuration
  parameters that consumed memory or adding a new
  cache will result in change in the size of
  procedure and data caches in the next reboot.
• If the SA wanted to set the procedure cache size
  to some number of kilo/mega bytes, it was hard to
  figure out what the equivalent of that value
  should be for procedure cache percent

64
Memory related configure parameter changes
Memory allocation in 12.5 ASE
Code size is removed as a factor in 12.5.0.3

• A - value of config parameter max memory
• B total logical memory for current
  configuration. Run value of total logical
  memory
• C - total physical memory. Currently allocated
  memory.
• D - allocated memory
• E - memory available for allocation
• F - allocated during boot time
• G - additional segment allocated during run time
• H - yet another additional segment allocated
  during run-time
• I - This is the size available for additional
  shared memory segment(s) allocation.

65
Memory related configure parameter changes
total memory and max memory

• Configure parameter total memory was deleted in
  12.5
• Dynamic configure parameter max memory was
  added
• Indicate Max shared memory ASE is allowed to
  allocate. It is a logical value and does not
  indicate size of shared memory actually
  allocated. This behavior can be changed by using
  allocate max shared memory. Total Logical
  memory is always verified against max memory
  during re-configuration.
• 2 new read-only configure parameters were added
• total logical memory - Memory required for the
  current configuration. Run value of configure
  parameter total logical memory indicates this.
  This value increases or decreases due to
  reconfiguration.
• total physical memory New read-only configure
  parameter total physical memory, provides the
  size of shared memory allocated to ASE at any
  instant of time.

66
Memory related configure parameter changes
allocate max shared memory

• By default, ASE 12.5 Allocate shared memory on
  demand. If customers do not want to allocate
  shared memory during run time, and prefer to
  allocate all the shared memory they need during
  boot time. This behavior can be controlled
  through the new dynamic configure parameter
  'allocate max shared memory'

67
Memory related configure parameter changes
allocate max shared memory

• 'allocate max shared memory'
• Default is 0
• Allocate shared memory on demand, based on the
  algorithm provided earlier.
• When set to 1
• During boot-time, allocate shared memory based on
  max memory and not the possibly smaller size of
  actually needed memory for boot.
• During run-time, if the setting is changed from
  0 to 1 allocate shared memory up to max
  memory. Note that changing the setting from 1
  to 0 will not release any shared memory.
• Useful for customers who want to ensure that all
  the shared memory needed is mapped to ASE and/or
  dont want to incur any performance penalty
  during run-time. Note that the latter is not a
  frequent occurrence.

68
Memory related configure parameter changes
dynamic allocation on demand

• ASE 12.5 not only provides customers the ability
  to control shared memory allocation behavior, it
  also provides ability to control the behavior of
  dynamic grow of the various memory pools related
  to a configure parameter.

69
Memory related configure parameter changes
dynamic allocation on demand

• This ability to turn on/off dynamic allocation is
  provided by the new dynamic configure parameter
  'dynamic allocation on demand
• Default is 1 (on)
• ASE will not allocate the resources to 100
  percent of the configured value. Instead, the
  allocation will occur on demand when requests
  coming in and there is not enough resource
  available.
• When set to 0
• By turning it off, you can grow all the pools
  instantly to their configured size.

70
Memory related config parameter changes
Procedure cache size

• In ASE 12.5, procedure cache size can be
  increased dynamically and will not change with
  configuration changes of other parameters or data
  caches.
• procedure cache percent - is deleted in 12.5.
• New config parameter procedure cache size
  specifies size of procedure cache in absolute
  value.
• Size can be increased dynamically.
• Size of procedure cache will the same after
  upgrade as it was before upgrade.

71
Memory related configure parameter changes
Default data cache size

• ASE 12.5 makes configuration of default data
  cache simple
• default data cache size is now an absolute
  value. It is no longer the left over memory as in
  pre-12.5 ASE. Changing other memory related /
  memory consuming configure parameters would not
  affect the size of default data cache.
• DEFAULT in the configure file indicates a
  factory setting of 8MB and not left over memory
  as in pre-12.5 ASE.
• Creation of new caches does not reduce default
  data cache size.
• Size of default data cache will the same after
  upgrade as it was before upgrade.

72
Engine configure parameter changes

• Some engine related configure parameters have
  been changed to provide customers ability to
  start with a small number of engines and
  online/offline engines as they need.
• Each additional engine consumes some amount of
  memory as many engine specific data structures
  are pre-allocated during boot-time. The size of
  the memory pre-allocated per engine is roughly
  between 1 and 1.5 MB per engine.
• Customers can start with small number of engines
  and online up to max online engines and offline
  all engines except engine 0.

73
Engine configure parameter changes (Contd.)

• min online engines has been deleted.
• New configure parameter number of engines at
  startup has the same role as max online
  engines in pre-12.5 ASE to specify the number of
  engines ASE should online during boot time.
• max online engines still exists. It specifies
  the maximum number of engines ASE or SA can
  online. It can be configured up to the maximum
  number of engines that can be onlined in ASE,
  which is 128.
• New stored procedure sp_engine replaces dbcc
  engine() and can be used to online/offline
  engines.
• sp_engine "online"
• sp_engine "offline", ltengine numbergt

74
Configure manager enhancements

• sp_configure supports common unit-specifiers
• sp_configure max memory, 0, 2G
• sp_configure number of locks, 0, 4K
• Simplifies user interfaces
• sp_configure reports base unit for each option
  and static/dynamic/read-only
• status
• 1gt sp_configure "procedure cache size"
• 2gt go
• Parameter Name Default Memory
  Used Config Value
• Run Value Unit Type
• ------------------------------ -----------
  ----------- ------------
• ----------- -------------------- ----------
• procedure cache size 3271
  6914 3271
• 3271 memory pages(2k) dynamic

75
Database for the Enterprise

• Extensible Server Limits

Enterprise Support
76
Extended Server Limits

• New extensible limits in ASE will enable
  enterprise application development, particularly
  ERP systems used worldwide.
• Key Benefits
• Larger page size, server pagesize can be 2K, 4K,
  8K or 16K.
• Increased row, column, procedure argument and
  index-key sizes
• Row, column and index size dependent on page size
  
• Increased number of columns per table and views
  (1024)
• Increased number of stored procedure arguments
  (2048)
• Unlimited(2x 109) logins per server, users per
  database

77
Large Data Pages
78
Large Text Pages
79
Large Data Pages Buildmaster change in the Server

• Master devices are built by server binary
• Buildmaster utility does not exist any more. The
  server binary will build a new master device
• Support for multiple page sizes with single
  binary
• Build mode invoked by b ltmaster dev sizegt (i.e.,
  build) argument
• Page size specified by z 2k4k8k16k
  argument. (k or K)
• Dataserver binary used to re-write / re-build
  corrupt master, model databases
• Page size reported in the error log
• _at__at_maxpagesize reports servers logical page size

80
Large Data Pages Buffer and Cache Manager change

• Buffer and Cache size are related to server
  pagesize.
• Buffers are sized as logical page size
• Think of buffer pools as 1 logical page, 2
  logical pages
• Pool continues to hold 1, 2, 4, 8 buffers i.e.
  for 16K logical page size pool sizes are 16K,
  32K, 64K, 128K
• Large I/O on 16K page server is 128K
• Table scans enormously benefited by larger page
  sizes
• Existing configure files may have to be re-done
  to specify larger sized buffer pools
• Memory specified for pool of buffer size less
  than the logical page size is folded into memory
  for buffer pool of logical page size.

81
Wider Row and Wider Column

• Max row size and column size are increased to
  close to
• server pagesize
• Wide row and wide column

82
Wide Indexes

• Index size has been increased to about one third
  of the logical server pagesize.
• Index size is calculated according to the
  following factors
• The logical pagesize
• The minimum number of index rows required on an
  index page
• Minimum 3 index rows per page for APL tables
• Minimum 2 index rows per page for DOL tables
• The index page format, the overhead from page
  header and timestamp
• The index row format, the overhead from various
  control structures

83
Wide Index - Index Size
Wide Indexes
84
Wider literals

• varchar and varbinary literals up to 16K are
  accepted during parsing of SQL queries
• Pre 12.5 scheme
• If literal exceeds 255 bytes, treat it as TEXT or
  IMAGE and read into chained constant nodes, each
  having 450 bytes of data
• 12.5 scheme
• Same as above for length gt 16K literals, plus
• If length lt 16K copy literal to contiguous
  memory and identify as STRING or BINARY token.
  Otherwise identify it as TEXT or IMAGE token.
• Advantage of wider literal over
  text/imageText/image types are not first-class
  datatypes in ASE -- cannot be used with many
  builtins and operators

85
Wider literals (contd.)

• Stored procedure argument length and _at_variable
  length can now be up to 16K
• Partial solution to support text/image arguments
  to stored procedures
• Narrow text/image column data can be passed by
  value to stored procedures by copying to
  _at_variable
• Column length in various catalogs (e.g.
  Syscolumns.length) is now 4 bytes

86
More Columns

• Maximum number of columns in a table/view is now
  1024.
• Select/DML grant-revoke permissions can be
  defined and indexes constraints can be built on
  all 1024 columns
• Syscolumns.colid and other column ids in various
  catalogs is now 2 bytes. User applications,
  procedures retrieving this data will have to
  change. (E.g., sp_help etc have been changed for
  12.5.)

87
More Arguments to Stored Procedures

• Maximum number of parameters to stored procedures
  is 2048
• Limit of ? arguments to Dynamic SQL is also
  2048
• Number of arguments to SQL-J procedures and/or
  functions is 31
• Syscolumns has in/out/inout status for
  procedure/function arguments for newly created
  procedures/functions. Useful for tools that
  search through system catalogs.

88
More Users/Logins in ASE

• Max number of logins per server 2147516416
• Max number of users per database 2146484223
• Added new system globals to bind new limits to
  internal mnemonics. E.g. _at__at_maxuserid,
  _at__at_maxgroupid etc.
• System procedures (sp_addlogin, sp_adduser) etc.
  to use these globals, and cover the number space
  appropriately

89
Inter-version compatibility

• Pre-12.5 clients continue to work with ASE 12.5
  for old limits
• Need to upgrade clients version string to use
  new limits
• Data truncation will occur if wide data is
  transmitted to client which has not enabled
  CS_WIDETABLES, i.e., older client talking to
  newer ASE will not be able to send/receive wide
  data.

90
DDL Changes

• Support size specifiers via k, m, g (and
  upper case)
• Example
• disk init namedisk1 size512M
• disk init namedisk2 size300m
• create database db1 on disk10.5g
• log on disk2 300M
• Mix-and-match units. Support float notation as
  well
• No units defaults to pre-12.5 style
• Created to support -b, -z arguments for
  buildmaster
• Also applies to disk init/reinit, create/alter
  database

91
Statistics

• Histogram statistics maintained on first 255
  bytes of column
• Works for most common cases of unique data in
  first 255 bytes
• Indexes on wide columns result in histograms for
  first 255 bytes
• Note BYTES not characters affects unichar
  columns
• Table schema supports non-indexable columns (e.g.
  cannot create index on varchar(1000) on 2K
  server)
• But, Can generate statistics on such non-index
  columns

92
Statistics and optdiag

• 12.5 optdiag enhanced to handle more and wider
  columns
• Need to use 12.5 optdiag for 12.5 ASE
• Forced consistent versions of ASE and optdiag to
  ensure accurate interpretation of data
• Cannot use 12.5 optdiag with pre-12.5 ASE

93
Extensible Server LimitsSolution Page Dependent
Limits
See dbcc serverlimits output in the appendix
for other limits.
94
Dump/Load Support

• Dump / load of databases supported for all page
  sizes
• Cross page size dump / load is not supported,
  instead migration tools should be used
• For example, loading of dumps with 8K bytes
  logical page size into server running with 4K
  bytes logical page size, or vice-versa, is not
  supported
• Dumps from older releases can only be loaded into
  2K server because older releases only have 2K
  logical page size
• No syntax changes to the existing dump / load
  commands to support larger pages. (See new syntax
  for dump compression.)

95
Dump/Load Support - Compatibility

• Forward compatibility
• Loading pre-12.5/12.5 dump by 12.5 ASE and 12.5
  Backup Server of later version is supported
• 12.5 BS can talk to 12.0 ASE to load older dumps
• Backward compatibility
• Loading dump from pre-12.5 is supported. Upgrade
  will happen at online database time
• 12.5 BS is able to accept RPC calls sent by 12.0
  ASE, and when it finds no logical page size in
  the parameter list, it will interpret it as 2KB
  page size
• 12.5 ASE does not talk to 12.0 or earlier version
  backup server, because earlier version backup
  server does not know how to interpret the extra
  parameter of page size in the RPC calls

96
Upgrade Paths to ASE 12.5

• 11.9.3 onwards, ASE supports loading 32-bit dumps
  into 64-bit server
• Older dumps upgraded as part of online database
• Pre-12.5 32-bit databases can be directly
  upgraded to 32-bit or 64-bit 12.5
• Pre-12.5 32-bit database and transaction dumps
  can be loaded and upgraded directly into 64-bit
  12.5 server
• Changing page sizes as part of upgrade not
  supported

97
Performance Results for Large Pages

• Performance tests for some Common DBA/maintenance
  operations were conducted on ASE 12.5 servers of
  various pagesizes
• ASE 12.5 configuration
• Max Online engine 6
• Total memory 700M
• Default Data cache 450M
• 16k, 32k, 64k, 128k 100M
• Sort Buffers - 2000
• Data information Lineitem Table
• 12,000,000 rows
• space reserved for data 1493 Mb
• space reserved for index 768 Mb
• No. of partitions - 6

98
Performance Results for Large Pages

• Performance tests conducted
• serial/parallel table scan
• insertion
• create clustered/non-clustered indexes
• create database
• disk init
• select into
• alter table
• reorg rebuild
• dbcc checkstorage/checkalloc
• Results (details are attached in the appendix)
• For all tests, larger pagesize server performed
  better than the smaller pagesize server.
  Generally the average performance improved about
  3 - 5 times on a 16K server over the 2k server.

99
Database for the Enterprise

• Unicode (UTF-16) support

Enterprise Support
100
Support for UTF-16 (UCS-2)

• Support for UTF-16 will enable ASE to process
  character data in English and foreign languages
  more efficiently thereby facilitating global
  commerce.
• Key Benefits
• Universal and efficient representation of all
  languages in 2 byte encoding
• Improved performance by avoiding conversions
• Match UTF-16 support for characters in Java

101
Unicode Overview

• Universal character set
• Assigns scalar values to abstract characters
• Defines attributes (isAlphabetic, isDecimalDigit,
  )
• Unicode 3.0 defines 49,194 characters more to
  come
• Representations, Transformation formats
• UCS-2 Fixed two bytes per scalar value
• UCS-4 Fixed four bytes per scalar value
• UTF-8 Variable width, 1 4 bytes per scalar
  value
• ASCII is ASCII, no embedded null bytes
• UTF-16 Two or four bytes per scalar value
• Equivalent to UCS-2 with addition of Surrogate
  Pairs

102
Support for UTF-16 in ASE 12.5

• New data type UNICHAR to be used anywhere CHAR
  can be used similarly for UNIVARCHAR
• Full integration with other data types
• implicit conversions so that functions and
  sub-queries returning UNICHAR can participate in
  mixed-mode expressions
• conversions between Java and UNICHAR types
• builtins modified to accept UNICHAR arguments
• joins between columns of UNICHAR and other types

103
Feature Description

• Two new data types unichar and univarchar
• Co-exist with char and varchar
• Same semantics to the maximum extent possible
• Storage is always Unicode UTF-16
• 2 bytes per character mostly
• Conversions between all SQL types, Java types
• Supported by 12.5 CT-Lib and jConnect
• Several new configuration parameters
• default unicode sortorder, enable unicode
  normalization
• enable surrogate processing
• Unitext support planned for future release

104
Schema Migration to use Unicode

• Goal is to be as painless as possible

id int name char(30)
t1
Alter table t1 modify name unichar(30)
id int name unichar(30)
t1
Select upper(name) from t1 where name like Mac
105
UTF-8 Restriction

• To use unichar the servers default charset must
  be UTF-8!
• Why?
• Same syntax, same semantics implies no quoted
  literals of type unichar neither Ustring, nor
  \u03a3 exist
• With the servers default charset as UTF-8, we
  gain a free Unicode parser!
• Conversion between UTF-16 and UTF-8 is fast, no
  external table lookup.

Select postcode from unicities where name ????
106
Migrating from iso_1 to UTF-8

• Bcp out, bcp in
• For changing page sizes, too!
• Rep server - No support for unichar. Upcoming
  release of RS will fully support all 12.5
  features
• Unidb migration tool
• In-place data conversion
• Paucity of UTF-8 sort orders
• To be improved shortly

107
Isql/Bcp and unichar

• When client charset is UTF-8
• Isql/Bcp translates unichar (UTF-16) to UTF-8
• When client charset is other than UTF-8
• Isql/Bcp represents unichar as hex ascii

gt Select unicolumn from unitable Unicolumn ABC
gt Select unicolumn from unitable Unicolumn
0x004100420043
108
Endian Awareness

• When inputting unichar as binary
• Server doesnt know enough to byte-swap!
• Should present binary data in servers byte order
• insert unitable values (0x004100420043)
• inserts ABC on a big-endian machine (Sun)
• inserts 3 Chinese characters on a little-endian
  machine (i386)
• When outputting unichar as binary
• Server knows its unichar
• Will send to client in clients native byte order

109
e-Business Support in ASE 12.5

• Flexible XML Server

e-Business Support in ASE
110
ASE is a flexible XML Server

• Support for Storing and Querying XML documents
• Key benefits
• Store and Query parsed XML documents in ASE
• Simple Java classes to extract data are provided
• General extraction can be generalized
• Optimized new XML query engine in ASE that is
  optimized for store once, query many times
• Tight integration with SQL language

111
ASE 12.5 - Design of the XQL Engine
112
Support for XML QueriesGeneric

• String result Xql.query(/bookstore,
  ltxmlgtlt/xmlgt)
• URL xmlURL new URL(http//doc.xml)
• String result Xql.query(/bookstore,
  xmlURL.openStream())
• JXml xDoc new JXml (ltxmlgt .. lt/xmlgt)
• String result Xql.query(/bookstore,
  xDoc)

• Platform is Java
• Generic solutions can be run on any VM
• Various Usage facilities
• EJB Component
• JDBC app (storage can be ASE)
• Standalone Client
• result is a well-formed XML document

113
Support for XML QueriesASE Value-Add

• SybXmlStream xmlStream
  Xql.parse(ltxmlgt..lt/xmlgt)
• String result Xql.query(/bookstore,
  xmlStream)
• insert XMLtable(xmlcol) values
  (Xql.parse(ltxml..lt/xmlgt)
• select Xql.query(, xmlcol) from XMLtable
  where...

• SybXmlStream benefits
• parsed document can be stored in memory or in a
  file for fast access
• suitable for store once, query many times
  (caching)
• parser will validate against supplied DTD .
• integration with SQL
• parsed output stored in text/image cols
• Inserts, updates, deletes supported

114
Support for XML QueriesLeveraging ASE 12.0
Capabilities

• The query() method of Xql class can be run
  against ASE 12.0s JXml class objects
• Columns storing raw XML docs in ASE 12.0 can be
  converted to parsed docs for use by Xql
• create table XMLtable(id, .., xmlcol text) in
  12.0
• alter table XMLtable add xmldoc IMAGE null
• update XMLtable
• set xmldoc Xql.parse(xmlcol)

115
e-Business Support in ASE 12.5

• EJB Server

e-Business Support in ASE
116
EJB Server in ASE

• EJB Server in ASE ensures faster development
  and deployment of applications using component
  model, in a performance optimized and secure
  environment.
• Key Benefits
• Completely secure its in Java!. Additionally,
  execution is within ASE environment
• Faster Access to Database objects.
• EJB is a popular component model
• Leverages ASEs open architecture
• PowerJ support to deploy EJB
• J2EE certified.

117
ASE EJB Architecture
118
EJB Server Architecture

• ASE Component
• Creates the Shared memory segments
• Starts and Stops external engines
• Services TDS requests from clients
• Performs its own thread management
• Performs its own memory management

• EJB Component
• Services EJB requests from the clients
• Low level protocol is IIOP
• Threads managed by the OS
• Memory management done by OS

119
Architecture Shared Memory driver

• High Speed Interconnect between EJB Server and
  ASE
• For example in an EJB component
• String _jdbcString "jdbcsybaseshmnull0
• SybDriver sybDriver
• (SybDriver)Class.forName("com.sybase.newInstanc
  e()
• DriverManager.registerDriver((Driver)sybDriver)
  
• _con DriverManager.getConnection(_jdbcString,
  "sa", "")
• Maps jdbc calls to TDS calls
• First client thread running in the context of the
  EJB Server trying to connect to the database
  attaches to ASEs shared memory.
• Facilitates faster data transfer through shared
  memory.

120
Performance Comparison
121
EJB Server Management

• Perform system management activities for the EJB
  Server such as
• Starting the EJB Server
• Stopping the EJB Server
• Determining the status of the EJB Server
• through system stored procedures.
• sp_extengine SYB_EJB, start
• sp_extengine SYB_EJB, stop
• sp_extengine SYB_EJB, status

122
Managing through Sybase Central
123
e-Business Support in ASE 12.5

• SQLJ Support

e-Business Support in ASE
124
SQLJ Support

• Provides Java methods as T-SQL Stored Procedures
  and Functions.
• Key Benefits
• Transform Java methods to true SQL objects. i.e
  SQL meta data, SQL data types, SQL security, etc
  
• Ability to create more complex SQL procedures in
  Java (in addition to T-SQL) that can return
  output params and result sets
• Ability to create complex SQL functions in Java.
  
• ANSI standard on using Java methods within SQL
  servers. (portability)
• Improved Performance over the ASE12.0 Java UDF
  support.
• Move App Logic into the database
• SQLJ Part 1 Complaint

125
SQLJ Overview

• ANSI Standard SQLJ Routines
• SQLJ procedure
• Using Java static methods as SQL stored
  procedures
• Output parameters result sets return status.
  status is an ASE extension to the SQLJ standard
• SQLJ function
• Using Java static methods as SQL user defined
  function

126
SQLJ Procedure

• Can return result sets and/or output parameters
  to the client
• Behave exactly as Transact-SQL stored procedures
  when executed
• Can be called from the client using ODBC, isql,
  or JDBC
• Can be called within the server from other
  stored procedures or native Adaptive Server JDBC

127
SQLJ Procedure

• SQLJ Procedure Example
• Creating the procedure
• create procedure ranked_emps(region integer)
• dynamic result sets 1
• language java parameter style java
• external name Routine.orderedEmps
• Calling the procedure
• java.sql.CallableStatement stmt
  conn.prepareCall("call ranked_Emps(?)")
• stmt.setInt(1, 3)
• ResultSet rs stmt.executeQuery()
• Or
• exec rangked_emps 3

128
SQLJ Functions

• Lets you define User Defined Function which
  invokes static java methods.
• Can return values.
• Can be called just like any built-in functions.
• Can be invoked by a remote server through CIS
  services.

129
SQLJ Functions

• For Example
• create function region_of(state char(20))
• returns integer
• language java parameter style java
• external name 'SQLJExamples.region
  (java.lang.String)
• Calling
• select name, region_of(state) as region
• from sales_emps
• where region_of(state)3

130
e-Business Support in ASE 12.5

• Java.net support

e-Business Support in ASE
131
java.net support in ASE

• java.net support in ASE will enhance the
  usability of Java in ASE by providing
  connectivity to other servers through http or
  sockets etc..
• Key Benefit
• ASE can participate in a e-Business transaction.
• In future ASE can be the coordinator of a
  distributed e-Business transaction
• Useful feature for XML engine to get DTD info
  about the XML document

132
java.net sample usage

• There are many possibilities for using java.net
  in ASE 12.5.
• The following are just a few examples
• Get a document from any URL address on the
  internet
• Access an external XML document using the XQL
  query function inside ASE 12.5
• Send an email from inside the server
• jConnect into the database and access other
  servers through JDBC
• Connect to an external server youve created and
  perform some function, say, save a document
• Datagrams , Multicast and server sockets are not
  supported in ASE 12.5

133
Database for the Enterprise

• DirectConnect for Oracle 2PC Support

Enterprise Support
134
DirectConnect for Oracle 2PC Support

• One of the oldest requests in the Sybase
  Heterogeneous Data Access story has been the
  ability for our DirectConnects to support 2
  Phased Commit.
• Complex problem to solve as not all database
  vendors work the same.
• The DirectConnect for Oracle 12.5 has been
  designed to support the Adaptive Server
  Transaction Coordinator (ASTC). This will enable
  ASE to coordinate distributed transactions across
  other ASEs and Oracle.
• Similar to ASTC, other transaction
  coordinators(Encina, Tuxedo) can include the
  DirectConnect for Oracle in a distributed
  transaction if they use Sybase/XA Library.

135
DirectConnect for Oracle 2PC Support

• The DirectConnect for Oracle 12.5 accepts RPCs
  from XA library or ASE and makes the appropriate
  XA library calls to Oracle.
• Oracle XA library is linked into the
  DirectConnect for Oracle.
• No setup required in Oracle other than having
  privileges to rollback all transactions.

136
More Features in ASE 12.5

• Sybase Central Java Edition available on all
  platforms
• ASE JVM compliant with JDK 1.2 (Java 2)
• JDBC 2.0 support in ASE

137
Features in ASE 12.5.0.1

• ASE 12.5.0.1

138
Database for the Enterprise

• Grantable DBCC

Enterprise Support
139
Grantable dbcc commands

• The goal of this feature is to make certain DBCC
  commands operate under DAC (Discretionary Access
  Control)
• Customers have expressed their desire to
  grant/revoke access to DBCC commands such as
  Checkalloc, Checkstorage, etc.
• Each customer has different requirements being
  almost impossible to satisfy all with hard coded
  permission checks.
• Permissions needed to execute DBCC commands have
  constantly changed depending on customer needs.
  Also the set of grantable DBCC commands may vary
  with each customer.
• This feature (Grantable DBCC commands) allows
  each customer to address their particular needs.

140
Grantable dbcc - Feature Overview

• The DBCC command is an internal Sybase command.
• This feature uses the existing grant/revoke
  command to give permission to execute DBCC
  commands to users that would normally not have
  access to execute these commands.
• Examples
• grant dbcc checkalloc on test to joe
• Grant the execution of dbcc checkalloc on
  database test to user joe
• revoke dbcc tablealloc on test from joe
• Revoke privileges from user joe to execute dbcc
  tablealloc on database test

141
GRANT/REVOKE DBCC usage

• DBCC access control introduces a new syntax to
  grant/revoke command
• GRANT DBCC dbcc_command on all ltdatabasegt
  
• , dbcc_command on all ltdatabasegt ,
  ...
• TO user_list role_list
• REVOKE DBCC dbcc_command on all ltdatabasegt
  
• , dbcc_command on all ltdatabasegt ,
  ...
• FROM user_list role_list

142
Database for the Enterprise

• Disk Resize

Enterprise Support
143
Disk Resize

• Functionality
• A command that can increase the size of an ASE
  device dynamically.
• Purpose
• disk resize can be used to grow ASE devices to
  the maximum allowed size.
• Increasing the size of master device, log device
  and other data devices is now possible.

144
Disk Resize

• Syntax
• disk resize name ltlogical device namegt,
  size ltincremental sizegt
• Example
• disk resize name employee.dev, size
  100M
• alter database emp.db on employee.dev100

145
e-Business Support in ASE

• XML Result Set

e-Business Support in ASE
146
What does a customer do with results?

• Customers tend to use the XML Query Engine in the
  following environment
• Shredding XML documents to the lowest level
• Extract base types like INT, CHAR etc from xml
  documents.
• Creating JAVA objects to represents the XML
  fragments
• The most preferred object form is the Document
  Object Model (DOM).
• Iterating over the tags in the XML document.
• Users create an object out of the XML fragment
  and then write their own iterators.

147
ASE12.5 XQL Query, Index and Results
148
Before ASE 12.5.0.1, How does customer process
results?

• To get the object
• User parses the results.
• Creates a DOM around the results.
• To get the base types
• User parses the results.
• Creates a DOM around the results.
• Writes code to hunt for the base type at the leaf
  level.
• To iterate over the xml result.
• User parses the results.
• Writes code for each result to iterate over the
  result.
• APIs for different results may not be
  standardised.

149
How can we simplify customers usage?

• Encapsulate the results in an object which
  provides the following functionality
• PROCESS REQUESTS WITHOUT PARSING THE RESULT
  AGAIN.
• Get the result as java.lang.String
• Get the result as a DOM
• Iterate over the result (a la JDBC)
• Extract the current item as a basetype like INT,
  CHAR, DOUBLE, FLOAT.

150
ASE12.5.0.1 XQL Query, Index and Results
151
Benefits of XML result Sets

• Its very useful to customers in three scenarios
• When they extract results from ASE SQL queries as
  XML, they can iterate through these results,
  create objects and write event managers.
• When they extract XML fragments, they would like
  to iterate through the XML documents one item at
  a time.
• When they need to decompose XML into SQL data for
  insertion into RDBMS tables.
• The customer will have to write LESS CODE!

152
e-Business Support in ASE

• XML Internationlization

e-Business Support in ASE
153
XML Internationalization

• I18N is compulsory for a product like the ASE
  engine which is extensively used throughout the
  world.
• Background
• The XQL engine is fully developed in JAVA.
• All internal character operations are done in
  java.lang.String
• Therefore, the internal processing is UNICODE
  compliant.
• All user interaction needs to be
  internationalized.

154
ASE12.5 XQL - Character Processing
XML DOCUMENT
java.lang.String
Query
java.lang.String
java.io.InputStream
Query Parser
XML Parser
java.lang.String (/bookstore/book)
java.lang.String (i.e. xml tags and text)
Event
Query
Results
Query Processing Layer
Indexing Layer
java.lang.String (xml tags and text)
Stream of bytesd (xml tags and text)
SybXMLStream (indexes data)
155
XQL I18N 4 Important factors

• Setting the character encoding (tell me the
  language!)
• User should have some interface to set character
  set encoding.
• Parse
• XQL engine should understand the encoding
  declaration in XML documents.
• Indexing engine should generate offsets and
  character strings in unicode compliant manner
  thus maintaining a single standard and avoiding a
  conversion nightmare.
• Queries
• User should be able to enter queries encoded in a
  character set.
• Results
• Results should be in UNICODE compliant manner.
• User should be able to convert them to desired
  character set.

156
XQL I18N Missing Pieces

• Setting the character encoding
• User has no way to set encoding.