Sybase System 11 Technical Overview - Index

Accent sensitivity xvii

Adaptable architecture xv

Adding engines 3-8

Adding logins 10-5

Affinity, process 2-4

Aggregate function 5-2

out of cache 3-7

procedure cache 3-6

Aliases, creating 10-7

Allocating memory 2-11

alter database command

and allocating storage space 9-6

Alternate character sets xvii

Alternate date xvii

ANSI/ISO SQL `89 xvi

ANSI/ISO SQL `92 xvi

append_log semaphore 3-9

client/server 1-6

design, DSS and OLTP 3-5

development 1-4

international xvii

named data caches for 3-5

Application-level protocol 4-10

Architecture 2-1 to 2-11

multithreaded 2-1

SQL Server SMP 2-4 to 2-8

@@identity 6-12

at isolation clause 3-12

@@max_connections 6-11

@@nestlevel 6-11

@@rowcount 6-12

@@servername 6-12

@@sqlstatus 6-12

@@textsize 6-12

@@total_read 6-12

@@total_write 6-12

@@trancount 6-12

Auditing 10-12 to 10-16

of client requests 1-21

queue, size of 10-14

sybsecurity database 10-13

system procedures for 10-13

Audit trail 7-3

Automated database managers 6-7

Automatic recovery 11-2

Backup and recovery parameters 6-14

incremental 11-7

Backup Server utility 11-4

Banyan StreetTalk 1-22

Batches vs. stored procedures 5-4

bcp (bulk copy utility)

large I/O for 4-8

begin transaction command 11-2

Benchmark, TPC-C xvi

Benefits of client/server 1-7

Binary sort order xvii

transaction logs 3-5

Bitwise indexing 1-4

Bitwise operators 5-10

Book, overview xvii

Buffer Cache Manager. See Logical Memory Manager (LMM)

Buffer washing 3-6

Built-in functions 5-9

Bulk copy command 4-10

Business, global 1-1

Business rules 1-12, 5-4

aging out of 3-7

architecture 3-3

configuring multiple 3-4

named 3-2 to 3-5

and optimizer 4-3

Cache, data 3-2 to 3-5

hot spots bound to 3-4

spinlocks on 3-4

tempdb bound to own 3-5

transaction log bound to own 3-5

Cache, procedure

query plans in 3-6

Cache management 3-2 to 3-7

and contention 3-1

Cache manager parameters 6-14

Case sensitivity xvii

Catalog stored procedures

chained on|off 6-10

configuration parameters 6-2

Changing log I/O size 3-8

Chapter overviews xvii

Character sets xvii

Cheap direct updates 4-4, 4-5

Check constraints 8-2, 8-4 to 8-5, 8-7

error messages 8-5

Checkpoint xv, 3-6

housekeeper task and 3-6

checkpoint command 4-8

Class C2 requirements xvi

Class F-B1/E3 xvii

application 1-6

implementation 2-2

service request 1-9, 2-9

implementation 1-7

interaction 1-6

model 1-6 to 1-13

relative roles 1-8

and Sybase products 1-1

Client connections 2-1

Client functionality 1-8

Client-Library 1-4, 1-19

Client service request 2-10

Client-side API 1-19

Client task 2-2, 2-3

close on endtran on|off 6-10

Clustered indexes, effect on SQL Server performance 7-7

sizes of (list) 7-8 to 7-9

commit transaction command 11-2

interprocess 4-2

shared memory 2-7

Companion Server 9-9

Complex restrictions 5-6

Compliance, with standards xvi

Component model 2-1

Components 1-2, 1-11

Compressing data 1-18

compute clause 5-2

sample query 5-2

client/server 1-6

mainframe 1-5 to 1-6, 1-7

Concurrent inserts xv

Conditional triggers 8-7

Configuration parameters 6-9, 6-14

sp_sysmon and 6-2

Configurations 1-20

three-tier 1-20

Configuration tools 6-1

multiple caches 3-4

named caches 3-3

number of locks 3-10

maximum user number 6-11

memory consumption 2-2

simultaneous 2-6

Connections, multiple 2-2

Consistency checker 8-8

Constraints 5-5

primary key 8-3

referential 8-4

system procedures for managing 7-11

and user-defined messages 8-5

and cache management 3-1

log, eliminating 3-8

and logging 3-1

on the log semaphore xv

Context switching 2-3

Control-of-flow language 5-3

Cost-based optimizer 4-3

CPU utilization 2-6

housekeeper task and 3-6

create database command 7-6

create database command

and allocating storage space 9-6

permissions 10-10

create index command 9-4

create rule command 8-5

create table command 9-4

database objects 9-4

cursor rows n 6-10

Cycle, execution 2-5

encryption 1-21

entry rules 8-4

massive amounts 1-4

presentation 1-5

replication 1-15

structures 3-2, 4-2

transparency 1-22

warehousing 1-14

DataArchitect 1-5

assigning to database devices 9-6

client and server 1-9

default size 9-6

online information 9-11

procedures for managing 7-12

security 10-1 to 10-11

space allocation 9-1 to 9-11

standalone 1-18

system 7-1 to 7-3

system procedures for managing 7-12

user types 10-4 to 10-5

Database Consistency Checker 8-8

Database devices 9-5 to 9-11

information 9-11

initialization 9-5

online information 9-5

space allocation 9-14

Database layer 1-3, 1-12

Database management system 1-9

Database object owner 10-4 to 10-5

permissions of 10-10

Database objects

system procedures for managing 7-11

Data cache 2-7, 2-8

hot spots bound to 3-4

and large I/O 4-7

spinlocks on 3-4

tempdb bound to own 3-5

transaction log bound to own 3-5

Data integrity 8-6 to 8-7

in SQL Server 10-1

properties 7-10

summary of 7-8 to 7-9

text and image 5-9

user-defined 7-10

dbcc (Database Consistency Checker) 8-8

large I/O for 4-8

dbcc checkdb 9-16

dbcc checktable 9-16

and kernel 4-1, 4-2

dbo use only 6-11

ddl in tran 6-11

Debugging services 1-21

Decentralization 1-1

Decision support system (DSS) 1-14, 3-6

named data caches for 3-5

Default devices, designating 9-5

in data entry 5-5

settings and permissions 7-2

system procedures for managing 7-11

Deferred updates 4-4, 4-6

new clauses 3-5, 6-9

initialization 9-5

online information 9-5

system procedures for managing 7-13

Dictionary sort order xvii

Directory services 1-22

Direct updates 4-4

to system tables 6-17

Disk devices, mirroring 9-6 to 9-9

Disk I/O 2-8, 4-7

parameters 6-15

performing 2-10

disk init command 9-4

disk mirror command 9-4

Disk mirroring 9-6 to 9-9

disk mirror command 9-4

Distributed computing environment (DCE) 1-22

Document purpose xvii

Domain constraints 8-1

Dumb terminal 1-5

online information 9-5

Dump striping 11-4

dump transaction 11-3

Efficient use of OS 4-1

Eliminating log contention 3-8

comunication 2-7

functions and scheduling 2-5 to 2-8

system tables entries for 7-5

Enhancements to SQL 5-1 to 8-8

Enterprise client/server 1-12

Error handling 5-8

checking global variables for 6-11

Escalating locks 3-10

Pass-through server 1-20

Replication Server 1-16

SMP processing 2-8

transparent server 1-21

Exclusive table lock 3-10

Execution cycle 2-5, 2-10

Expensive direct updates 4-4, 4-5

Extending business 1-2

Extensions, other 5-9

Family of servers 1-2

Fault tolerance 9-9

Fault tolerance in SQL Server 11-1

First tier 1-12

Foreign language xvii

Formats, alternate xvii

Freelock list 3-11

Frequently used options 6-11

Functional compensation 1-22

General information parameters 6-15

Global business 1-1

Global distributed transaction 1-12

Global resources 3-2

Global stored procedures 1-22

Global variables 6-11 to 6-12

Graphical user interface (GUI) 1-7, 1-9

Hardware independence 2-1

Hash tables 3-10

Heap tables 9-15

Heterogeneous environment 1-7

Hewlett Packard xvi

High performance products 1-2

holdlock keyword 11-2

Hot pages 3-1, 3-4

binding caches to 3-4

Housekeeper task

free writes and 3-6

network 2-6, 2-10

update operations and 4-5

Identifying users 10-2

@@identity 6-12

image datatype 5-9

large I/O for 4-8

Client/server nodes 1-7

Improving efficiency 1-1

statistics 4-6, 6-9, 6-13

system procedures for managing 7-11

Industry standards xvi

Infrastructure 1-2

client/server 1-12

Innovative solutions xvi

In-place updates 4-4, 4-5

new clauses 3-5, 6-9

Integrating the mainframe 1-13

Intelligent analysis 1-5

International applications xvii

International Information Technology Security Evaluation Criteria (ITSEC) xvii

International Standards Organization (ISO) 1-7

Interprocess communication 4-2

Invoking a stored procedure 5-3

Isolation level 0 3-12

Isolation levels 3-12

Kernel 2-7, 4-1 to 4-10

architecture 4-1

and scheduling 2-5

automatically generated 5-3

Last page contention xv

database 1-2, 1-3, 1-12

middleware 1-2, 1-12

tools 1-2, 1-4, 1-12

Leaf levels of indexes

large I/O and 4-8

Lightweight process 2-2

catalog stored procedures 7-17

configuration parameters 6-14 to 6-19

system procedures 7-10 to 7-16

system tables 7-4 to 7-6

Location transparency 1-22

Lock management 3-10 to 3-12

Lock manger parameters 6-16

Lock promotion threshold 3-10

synchronization point 3-8

write ahead 3-7

Log, transaction 11-3

Log contetion, eliminating 3-8

and contention 3-1

data changes 3-8

Log I/O size, changing 3-8

Logical Memory Manager (LMM)

Logins 2-6, 2-9

account locking 10-2

adding to servers 10-5

dropping from servers 10-6

locking accounts 10-7

system procedures for managing 7-16

Log semaphore xv, 3-8, 3-9

Mainframe, integrating 1-13

Mainframe computing 1-5 to 1-6, 1-7

of SQL Server 11-1

cache 3-2 to 3-7

lock 3-10 to 3-12

transaction 3-7 to 3-9

Market requirements 1-1

Mass deployment 1-18

Massively parallel processing 1-16

master database 6-12

system tables 7-5 to 7-6

Matrix, product 1-2

@@max_connections 6-11

max_rows_per_page 3-11

max engines freelocks configuration parameter 3-11

Mechanism, logging 3-9

allocation 2-11

shared 2-4, 2-7

Message command 4-10

Messages, user-defined

system procedures for managing 7-15

Middle tier 1-12

Middleware layer 1-2, 1-4, 1-12

Mirroring. SeeDisk mirroring.

Mobile devices 1-18

client/server 1-6 to 1-13

component 1-11, 2-1

computing 1-1, 1-5

peer-to-peer 1-13

process 2-1 to 2-4

three-tier 1-11 to 1-21

two-tier 1-10 to 1-11

Model database 7-2

Monitor Historical Server 6-4

Monitoring performance (system) 6-2 to 6-11

Monitoring tools 6-1

MRU/LRU chain 3-6

Multifile dumps 11-4

Multiple connections 2-2

Multiple data caches xv

Multiple network engines xv, 2-6, 4-9

Multiple sites 1-4

Multiprocessing Architecture, SQL Server SMP 2-4 to 2-10

architecture 2-1

environment 4-1

Named caches 3-2 to 3-5

and performance 3-4

Naming services 1-22

National Computer Security Center (NCSC) xvi

National Institute of Standards and Technology xvi

Navigation Server 1-17

Nested triggers 8-7

Nesting levels 6-11

@@nestlevel 6-11

architecture 4-9

bottlenecks 2-6

communication parameters 6-16

Networked systems 1-7

Network engines 2-6

Network I/O 2-6, 2-8

performing 2-10

Network manager engine 2-9

New paradigms 1-1

no chkpt on recovery 6-11

nocount 6-10

on a network 1-8

noexec 6-10

Nomadic devices 1-18

Novell NetWare 1-22

Number (quantity of)

nesting levels 6-11

simultaneous SQL connections 6-11

user connections (@@max_connections) 6-11

disk reads 6-12

disk writes 6-12

page locks 3-10

Numbers, error message 6-11

Object allocation map (OAM) 3-12

ObjectCycle 1-5

Object-oriented C++ 1-5

communication 2-7

ODBC procedures 7-17

Off-peak data writer xv

Oldest active transactions 11-3

OmniCONNECT 1-4, 1-12, 1-13, 1-19, 1-21

On-line transaction processing (OLTP) 1-13 to 1-14, 3-5

Open architecture 2-1

Open Client 1-4, 1-19

Open Client Client Library 2-2

Open Database Connectivity 7-17

Open Data Connectivity 1-19

Open Server 1-4, 1-12, 1-13, 1-19, 1-19 to 1-21

configurations 1-20

Operating system, use of 4-1

Operating system process 2-2

Operating system resources parameters 6-17

Optima++ 1-5, 1-12, 2-2

deferred updates 4-6

single process 2-3

Overview of chapters xvii

Package commands 4-10

Page allocation 3-12

page utilization percent parameter 3-12

Parallel loads xv

Parallel processing 1-16

parseonly 6-10

Partitioning tables 9-15

Pass-through server 1-20

encryption 10-2

expiration 10-2

Peer-to-peer model 1-13

Performance 3-1 to 3-12

and disk usage 9-10

in single-process architecture 2-3

monitoring 6-2 to 6-11

and named cache 3-4

space allocation issues 9-2

and threads 2-3

Performing disk I/O 2-10

Performing network I/O 2-10

Permissions 10-8 to 10-10

aliases and 10-7

command/object 10-9 to 10-10

of database object owner 10-10

model database 7-2

and stored procedures 10-11

System Administrator 10-3

System Security Officer 10-3

and views 10-11

Physical memory parameters 6-17

Platform independence 2-1

PLC, see ULC xv

Polling sockets 4-9

PowerBuilder 1-4, 1-12

Predicting trends 1-2

Prefetch xv, 4-3

Private log cache xv

Private log cache (PLC). See User log cache

Problems, solving xvi

Procedure cache 2-7, 2-8

procedure cache percent configuration parameter and 2-7

query plans in 3-6

identifier (PID) 2-2

in run queue 2-2

lightweight 2-2

model 2-1 to 2-4

operating system 2-2

versus threads 2-2

ProcessAnalyst 1-5

Processors parameters 6-17

Product matrix 1-2, 1-10

Products, high performance 1-2

Protocol diagnostics 1-21

Purpose, this document xvii

Quality, ISO 9001 xvi

Quality certification xvi

Query optimization xv, 4-3

factors affecting 4-6

optimizer and 4-3

procedure cache storage 3-6

Query processing

large I/O for 4-8

Queued tasks 2-5

read only 6-11

audit records 10-14

Recovery 11-2 to 11-3

in SQL Server 11-1

housekeeper task and 3-6

checkpoint time xv

Reference Model, ISO 1-7

Referential integrity 5-6, 8-6 to 8-7

Relative roles, client/server 1-8

System procedures for managing 7-14

Remote procedure calls

System procedures for managing 7-14

Replication Server 1-4, 1-15

Request, client 1-9, 2-9

Requiremtents, market 1-1

Resource managers 3-2

Response time 3-1

Returning data 4-9

System Administrator 10-2 to 10-3

System Security Officer 10-3

rollback transaction command 11-2

Row aggregate function 5-2

@@rowcount 6-12

rowcount 6-10

update operations and 4-4

Row-level locking 3-10

Rows per page 3-11

Rows returned 6-12

for data entry 5-5, 8-4

and data integrity 8-2

system procedures for managing 7-11

Running business 1-2

Run queue 2-2, 2-3, 2-8, 2-10

Sample database 7-3

Scalability xv, 3-1

large I/O for 4-8

Scenario, SMP 2-8

Second tier 1-12

database 7-3, 10-1 to 10-11

enhancements 1-21

Segments 9-10 to 9-11

commands for using 9-11

partitions and 9-16

and performance 9-10, 9-14 to 9-15

select command

new clauses 3-5, 3-12, 6-9

select into/bulkcopy 6-11

select into/bulkcopy database option

model database and 7-2

select into command

large I/O for 4-8

Semaphore 3-8, 4-3

Sequential prefetch xv, 3-6, 4-3

application 1-6

Server functionality 1-8

@@servername 6-12

Server operations

System procedures for managing 7-14

adding new logins to 10-5

monitoring activity of 6-2 to 6-11

monitoring performance 6-2

Server-side API 1-19

Server structures 2-7, 2-8, 4-2

Server toolkit 1-4

sample options 6-10

set forceplan 3-5, 6-9

set prefetch 3-5, 6-9

set table count 3-5, 6-9

Shared data buffers 4-8

Shared memory 2-7

Shared procedure 4-8

showplan 6-10

Single-process overhead 2-3

Sites, multiple 1-4

Sleep queue 2-5, 2-8

SMP machines 1-17

SMP subsystems 2-8

SMP systems 4-9

architecture 2-4 to 2-8

Software independence 2-1

Solutions, innovative xvi

Solving problems xvi

Sort orders xvii

sp_adduser system procedure 7-2

sp_cachestrategy 3-5, 6-9

sp_configure 6-7

address lock spinlock ratio 3-11

page lock spinlock ratio 3-11

table lock spinlock ratio 3-11

user log cache spinlock ratio 3-9

sp_helpartition 9-16

sp_monitor 6-5

sp_sysmon 6-5

sp_volchanged 11-5

Space allocation

database devices 9-14

issues 9-1 to 9-4

cheap direct updates 4-5

deferred updates 4-6

direct updates 4-4

expensive direct updates 4-5

in-place updates 4-4

spinlock ratio parameter 3-9

Spinlocks 2-2, 2-7, 3-9, 3-10, 4-3

data cache and 3-4

Transact-SQL extensions 5-9

SQL Access Group 1-19

SQL Anywhere 1-4, 1-12, 1-16, 1-18

SQL Central 1-4

SQL enhancements 5-1 to 8-8

SQL Remote 1-16

activities monitor 6-2 to 6-11

adding users to 10-5

administration parameters 6-17

users information 10-8

SQL Server Manager 1-4, 6-7

SQL Server Monitor 1-4

@@sqlstatus 6-12

Standalone databases 1-18

Standard cache architecture 3-3

Standards compliance xvi

State of the art features xv

index 6-9, 6-13

statistics io 6-10

statistics time 6-10

system procedures for managing 7-13

Storage management 9-1 to 9-11

and system tables 9-2 to 9-3

Stored procedure ID 6-11

Stored procedures 7-10 to 7-16

effect on SQL Server performance 5-3

and optimizer 4-4

and permissions 10-11

system procedures for managing 7-15

System 11 1-1 to 1-13

Structures 2-8, 3-2, 4-2

of this book xvii

Sybase IQ 1-4, 1-12, 1-18

Sybase MPP 1-3, 1-12, 1-17

Sybase product matrix 1-10

Sybase products

client/server 1-1

Sybase Replication Server 1-15

sybsecurity database 10-13

Symmetric multiprocessing 2-4 to 2-10

Syntax database 7-3

sysindexes table 9-3

syslogshold system table 11-3

uniprocessor 2-2

System 11 strategy 1-1 to 1-13

System Administration

System Administrator 10-2 to 10-3

permissions 10-3

System databases 7-1 to 7-3

System datatypes 7-8

System procedures 7-10 to 7-17

for adding users 10-6

list of 7-10 to 7-16

sp_volchanged 11-5

System Security Officer 10-3

permissions 10-3

System tables 7-3 to 7-6

master database 7-5 to 7-6

and storage management 9-2 to 9-3

table count 6-10

Table locks 3-10

partitioning xv, 9-15

system 7-3 to 7-6

system procedures for managing 7-11

large I/O for 4-8

Tabular Data Stream 4-10

Telecommute 1-1

tempdb database

named caches and 3-5

text datatype 5-9

large I/O for 4-8

@@textsize 6-12

Third party tools 1-5

Third tier 1-12

Threads 2-2, 2-2 to 2-4

switching context 2-3

versus processes 2-2

Three-by-three matrix 1-2

Three-tier model 1-11 to 1-21

system procedures for managing 7-13

Tools layer 1-4, 1-12

Topologies 1-20

@@total_read 6-12

@@total_write 6-12

TPC-C benchmark xvi

Tracking cursor actions 6-12

Tracking transactions 3-7

@@trancount 6-12

Transaction logs 3-7, 11-3

database device placement 9-6

named cache binding 3-5

Transaction management 3-7 to 3-9, 11-2

Transactions 3-7

and consistency 11-2

performance and 6-2

and recovery 11-2

Transact-SQL debugger 5-9

Transparent server

Triggers 1-12, 5-5, 8-6 to 8-7

conditional 8-7

and multiple row data modifications 8-7

and optimizer 4-4

Truncation points 11-3

trunc log on chkpt 6-11

Trusted Computer System Evaluation Criteria (TCSEC) xvi

Tunable block I/O xv, 4-7

monitoring performance 6-2

Two-tier model 1-10, 1-11

ULC. See User log cache (ULC)

Uniprocessor system 2-2

Updates xv, 4-4

cheap direct 4-5

expensive direct 4-5

update statistics command 6-13

update statistics command

large I/O for 4-8

master database control of 7-1

User-defined caches. See Named caches

User-defined datatypes 7-10

system procedures for managing 7-15

User environment parameters 6-19

User log cache (ULC) xv, 3-9

dropping from servers 10-6

system procedures for managing 7-16

User statistics

system procedures for managing 7-16

configuration parameters 6-14 to 6-19

VAX OpenVMS 9-9

and database security 10-11

permissions for 10-11

Virtual Server Architecture (VSA) 2-4

Windows NT 1-22

Work models 1-1

Write-ahead log 3-7, 11-3

[Top] [Prev] [Next] [Bottom]