Index

• Access plan, description of
• Accessibility
• Admission policy, used by RGM for queries
• Aggregate functions, key-only scans for
• ALTER DBSPACE STATIC command
• ALTER FRAGMENT statement
  • reclaiming extent space
  • with INIT clause to change distribution scheme
• ALTER TABLE
  • data manipulation effects of
  • effect on later actions
  • locks during
  • performance implications of in-place alter
  • to reclaim extent space
  • when in-place alter not used
• ALTER TABLE statement
  • MODIFY NEXT SIZE clause example
  • restrictions of
  • to change extent sizes
• ANSI isolation levels.
  • See also Isolation levels.
• Applications
  • See also DSS applications and OLTP applications.

• B-tree index
  • btree cleaner activity
  • description of
• Background processes, managing
• Backup and restore
  • performance issues
  • table fragmentation for efficient
• Bitmap indexes
  • CREATE INDEX statement example
  • estimating efficiency
  • estimating size
  • estimating size of
  • using
  • when to create
• blobs.
  • See also Simple large objects.
• Boldface type
• Bottlenecks, intercoserver, monitoring for
• Branch instances
• Branches
• Buffer size
  • for logical log
  • for physical log
  • for TCP/IP connections specified in sqlhosts
• BUFFERS configuration parameter
  • buffers for data caching
  • fast-recovery requirements
  • read-cache rate improvements
• BYTE data type, estimating column size

• Cache
  • data buffer
• Cardinality
  • and useful filters
  • definition of
• Cartesian Joins
• Cartesian Product alarm
• CASE statement, example
• Central Processing Unit (CPU)
  • number per coserver, optimal
  • planning for maximal use
  • tuning management parameters for
• Checkpoints
  • adjusting time interval
  • effect of LRUS on
  • effect on performance
  • full
  • fuzzy
  • interval between, setting
  • monitoring
• Chunks
  • associate disk partitions with
  • maximum allowed
  • monitoring free space in
  • monitoring I/O in
  • monitoring size and contents
  • monitoring writes to
• CKPTINTVL configuration parameter, maximum interval between checkpoints
• CLEANERS configuration parameter
  • specifying number of page cleaners
• CLIENT_TZ environment variable (1), (2)
• Clustering index, when to create
• Code, sample, conventions for
• Collocated join
  • advantage for temporary tables
  • creating dbslices for
  • definition of
  • for performance advantage
  • hash fragmentation for
• Command-line conventions
  • how to read
  • sample diagram
• Commands, UNIX
  • iostat
  • ps
  • sar
  • vmstat
• Compliance
  • with industry standards
• Concurrency
  • increasing during index checks
  • OLTP applications, improving for
• Configuration parameters
  • See also individual parameters.
  • affecting sequential I/O
  • BUFFERS
  • CLEANERS
  • DATASKIP
  • DD_HASHMAX
  • DD_HASHSIZE
  • DS_MAX_QUERIES (1), (2)
  • DS_TOTAL_MEMORY
    • description of
    • estimating for DSS
    • for OLTP
  • IDX_RA_PAGES
  • IDX_RA_THRESHOLD
  • LOGBUFF
  • LOGFILES
  • LOGSIZE
  • LRU_MAX_DIRTY
  • LRU_MIN_DIRTY
  • LRUS
  • LTXEWHM
  • LTXWHM
  • MAX_PDQPRIORITY, how used
  • NOAGE
  • NUMAIOVPS
  • NUMFIFOVPS
  • OFF_RECVRY_THREADS
  • ON_RECVRY_THREADS
  • PDQPRIORITY
  • PHYSBUFF
  • PHYSFILE
  • RA_PAGES
  • RA_THRESHOLD
  • SENDEPDS
  • SHMADD
  • SHMVIRTSIZE
  • USEOSTIME
• Connection coserver
  • obtaining session statistics with onstat
  • sqlexec thread
• Contact information
• Contention
  • fragmentation to reduce I/O
  • table fragmentation to reduce
• Contiguous extents, performance advantage of
• Conventions
  • documentation
  • syntax diagrams
  • syntax notation
• Correlated subquery
  • definition of
  • parallel execution of
• Coserver
  • monitoring interconnect activity
  • optimal number of CPUs for each
  • threads on
• Coservers
  • how to use specific-purpose
• CPU.
  • See also Central Processing Unit.
• CPU utilization.
  • See also Formula, service time.
• CPU virtual processor
  • limiting to one per operator per coserver for queries
  • optimal number of table fragments for
• CREATE DBSLICE statement
  • example
  • example for collocated joins
  • for temporary files
• CREATE INDEX statement
  • example for bitmap index
  • parallel index builds
• CREATE TABLE statement
  • example with extent sizes
  • SCRATCH
  • specifying extent size
  • TEMP
  • TEMP...WITH NO LOG
• cron
  • querying SMI tables
  • UNIX operating-system scheduling facility
• Cursor Stability
  • definition of
  • row locking during

• Data availability, table fragmentation for
• Data-dictionary cache, monitoring
• Data distribution statistics
  • examining for table (1), (2)
  • generating with UPDATE STATISTICS
  • guidelines for creating
  • to determine fragmentation column
• Data granularity
  • designing dbslices to increase
  • hybrid expression-hash fragmentation for improving
  • hybrid range fragmentation for
• Data loading
  • table fragmentation for
• Data mart, defined
• Data skew
  • automatic skew handling for sessions
  • dynamic, defined
  • in hash joins, reason for
  • monitoring queries for
• Data types
  • See also individual data types.
  • BYTE column size
  • NVARCHAR
  • TEXT
  • VARCHAR
• Data update, table fragmentation to improve speed
• Data warehouse, defined
• DATASKIP
  • setting configuration parameter
• DB-Access utility
• dbschema utility, examining data distribution statistics with (1), (2)
• Dbslices
  • cogroup all, predefined for creating dbslices
  • example of creating (1), (2)
  • for temporary files
  • fragmenting table in
  • granularity increase with
  • maximum allowed
  • purpose of
  • specifying logging slices for DBSPACETEMP
• Dbspaces
  • maximum allowed
  • monitoring I/O in
  • read-only
  • static
• DBSPACETEMP configuration parameter
  • specifying list of spaces for
  • specifying logging dbspaces for
  • using the TEMP argument
• Deadlock
  • definition of
  • methods of reducing
  • removing session to fix
• Debugging sysdbopen() routines
• Decision-support queries (DSS)
  • bitmap indexes for
  • composite indexes for
  • disabling process priority aging for
  • GK indexes for
  • managing memory for
  • monitoring resources allocated for
  • monitoring threads for (1), (2)
  • primary thread for
• Default locale
• DELETE USING statement, to delete rows based on a table join
• Demonstration databases
• Dependencies, software
• Detached index
  • definition of
  • global, description of use
  • local, definition of
• Directives (1), (2)
  • See also Optimizer directives., Optimizer directives.
• Disk access
  • spiral-write advantage
• Disk arrays, described
• Disks
  • associate partitions with chunks
  • overuse of (saturation)
• DISTINCT keyword, in index creation
• Distributed queries (1), (2)
• Distribution schemes
  • changing
  • data-distribution patterns for planning
  • expression-based
  • hash
  • hybrid expression and hash
  • planning
  • range
  • round-robin
  • table-access patterns for planning
• Distribution statistics
  • See also Data distribution statistics.
• Documentation, types of
  • machine notes
• Documentation Notes
• DS_MAX_QUERIES parameter
  • for OLTP
  • maximum queries
• DS_TOTAL_MEMORY parameter
  • for OLTP
  • shared memory available for DSS queries
• DS_TOTAL_TMPSPACE configuration parameter, specifying temp space for a single query
• DSS applications
  • described
  • operational data store
  • requirements for
  • running ad hoc queries
  • running planned queries
• Duplicate keys
  • avoiding in indexes
  • bitmap indexes for
• Duplication of small tables across coservers

• en_us.8859-1 locale
• Environment variables
  • determining mutability properties
  • IFMX_OPT_FACT_TABS
  • IFMX_OPT_NON_DIM_TABS
  • PDQPRIORITY
    • Setting mutability property
  • Setting the mutability of (1), (2)
• Error messages
• Exchange, in parallel processing
• Exchanges
• EXPLAIN output.
  • See also SET EXPLAIN.
• Expression-based distribution schemes
  • described
• EXTENT SIZE clause, in CREATE TABLE statement
• Extents
  • changing next-extent size
  • checking layout
  • definition of (1), (2)
  • interleaved
    • checking for
    • defined
  • limits
  • next-extent size
  • no more extents error
  • performance implications of contiguity
  • reclaiming empty space in
  • size calculation for index

• Fast recovery
  • size of physical log for
• Features of this product, new
• Fill factor, setting for indexes
• Filter
  • definition of
  • WHERE clause example
• Fixed and Known Defects File
• Forced residency, description of
• Foreground write
  • caused by memory-resident tables
  • defined
  • implications of
  • monitoring for
• Formula
  • bitmap index size
  • connections per poll thread
  • decision-support total memory (1), (2)
  • distance between duplicate rowids for bitmap indexes
  • extents, upper limit for specific table
  • hash-table size estimate
  • index-page estimate
  • initial stack size for threads
  • LOGSIZE estimate
  • memory for single query, minimum and maximum
  • number of remainder pages
  • partial remainder pages
  • RA_PAGES
  • RA_THRESHOLD
  • RGM FAIR policy
  • rows per table page
  • simple-large-object pages
  • size of physical log
  • table-page estimate
• Fragment elimination
  • limitations of function expressions in
  • table fragmentation for
  • when possible
• Fragmentation
  • advantages of schemes compared
  • contention-reducing advantage of
  • data-distribution statistics to design
  • fragment-elimination advantages of
  • information in system catalog tables (1), (2)
  • monitoring
  • monitoring disk usage with system catalog tables
  • monitoring I/O requests for fragments
  • parallel-processing advantage of
  • performance advantage of
  • response-time advantages of
  • using striped RAID system
• Fragmentation schemes
  • planning storage spaces to support them
• FRAGMENTS clause
  • using for permanent spaces
• Fuzzy checkpoints, described

• Generating
  • statistics
    • table on a
• GK indexes
  • join indexes
    • description of
    • example of use
  • limitations of
  • selective index, description of
  • types of
  • virtual-column index
• Global Language Support (GLS)
• Globally Detached Index alarm
• Globally detached indexes
  • monitoring creation of (1), (2)
• GROUP BY clause
  • memory grants for
  • use of index for
• Guidelines
  • amount of temporary space required
  • disk configuration
  • for determining performance goals
  • for filter evaluation
  • for LOCKS estimate
  • for quick OLTP transaction response
  • for shared memory configuration parameters
  • for UPDATE STATISTICS
  • index fragmentation
  • table-type advantages

• Hash distribution scheme
  • advantages
  • fragmenting on serial column
  • serial values as fragmentation columns
• Hash join
  • data skew caused by duplicate join keys
  • encouraging push-down
  • memory grants for
  • preventing push-down
  • push-down, example
  • push-down described
  • read-ahead buffers for overflow
  • zigzag example
• Help
• Hybrid-hash distribution scheme
  • advantages of
  • described
  • example
• Hybrid range distribution scheme, described

• I/O resources
  • disk layouts for optimal use
  • optimizing use
  • planning use of
• I-Spy
  • uses for
• IDX_RA_PAGES parameter, described
• IDX_RA_THRESHOLD parameter, described
• IFMX_CART_ALRM environment variable
• In-place ALTER TABLE
  • when used
• Indexes
  • attached
    • effect on extents
    • fragmentation effects
    • uses of
  • B-tree cleaner
  • bitmap
    • description of
    • estimating efficiency
    • estimating size
  • build performance, improving
  • checking consistency
  • clustering
  • COARSE lock mode for
  • columns
    • choosing
    • ordering in composite index
  • composite
    • for OLTP
    • optimal order of columns
  • detached
    • advantages and disadvantages
    • fragmentation effect
    • local or global, advantages of
  • disk space used by
  • duplicate keys, avoiding
  • estimating
    • B-tree index size
    • bitmap index size
    • extent size
  • fill factor specified
  • free space in index page
  • GK
    • join
    • limitations of
    • selective
    • virtual column
    • when to use
  • integrity checks
  • isolation level for multiple-index scans
  • join methods to replace
  • key-only scan
    • for aggregate functions
    • query examples
    • when used
  • lock mode for
  • low-selectivity columns in
  • managing
  • modification-time cost of
  • multiple-index scans
  • performance guidelines for
  • queries that use multiple
  • rebuild, when to
  • subquery use of
  • trigger restrictions
  • when used by optimizer (1), (2)
• Industry standards, compliance with
• INFORMIXDIR/bin directory
• Inner table, directives for
• Input-output
  • for tables, configuring
  • monitoring chunk reads and writes
  • monitoring queues
  • overuse of disk (saturation)
  • table fragmentation to reduce contention
• Installation Guides
• iostat command
• ISO 8859-1 code set
• ISO_CURLOCKS parameter, set for cursor stability
• Isolation levels
  • for multiple-index scans
  • multiple-index scan requirements
  • relation to query join method
  • review for queries and transactions
  • when light scans permitted
• Iterator
  • definition
  • model

• Join
  • collocated
    • across dbslice
  • effect of isolation level
  • hash join
  • left-deep example
  • nested-loop, described
  • order
  • plan, description of
• Join index (GK), description of

• Kernel asynchronous I/O (KAIO), AIO virtual processors needed with
• Key-only index scans
  • multiple-index scan requirements
  • query examples of
  • when used
• Keywords
  • in syntax diagrams

• Left-deep hash join, example
• Light append
  • monitoring for
  • OPERATIONAL table with trigger
  • table types for
  • when permitted
  • with RAW tables and express load
• Light scan
  • effect of isolation level
  • for indexes
  • monitoring for
  • when it occurs
  • when permitted
• Load jobs
  • external tables that contain simple large objects
  • light append with RAW tables
  • monitoring time for
  • without indexes
• LOAD statement
  • parallel inserts with
  • performance with index
• Locale
• Locks
  • COARSE level for indexes
  • during ALTER TABLE operations
  • indexes, lock mode for
  • locking more than one row
  • maximum number of simultaneous
  • monitoring sessions for
  • removing session for deadlock problem
  • shared for index validity checks
  • specifying table lock mode
• Log buffers, monitoring size and activity
• LOGBUFF configuration parameter, specify memory for logical-log buffer
• LOGFILES parameter, effect on checkpoints
• Logical log
  • buffer size
  • estimating size
• LOGSIZE configuration parameter, size of each logical log file
• LOGSIZE parameter
  • formula for estimating
• Long transactions
  • LTXWHM and LTXEWHM parameters to set high-water marks
  • managing
• LRU_MAX_DIRTY parameter, LRU queue behavior
• LRU_MIN_DIRTY parameter, LRU queue behavior
• LRU queues
  • tuning to improve write-cache rates
• LRU queues, monitoring
• LRUS parameter
  • effect on checkpoints
  • specifying number of LRU queue pairs
• LTXEWHM configuration parameter, how used
• LTXWHM configuration parameter, how used

• Machine notes
• MAX_PDQPRIORITY
  • description of
  • limiting PDQPRIORITY
  • query memory limited by
• Memory
  • automatic allocation for queries estimated by optimizer
  • bounding automatic memory allocation
  • estimate for sorting
  • estimating for DSS queries
  • freeing unused segments
  • granted by RGM
  • monitoring automatic allocation for queries
  • monitoring current shared memory segments
  • overflow to disk, preventing
  • planning resource use
  • proportional allocation among query operators
  • specifying increment for shared memory
• Memory-resident tables
  • foreground writes caused by
  • monitoring with onstat -P
  • setting
• MIDDLE clause, SQL statement
• MODIFY NEXT SIZE clause, for table
• Monitoring
  • buffer read-cache rate
  • checkpoints
  • chunks on specific coserver
  • command-line utilities for
  • data-dictionary cache use
  • free space in chunks
  • I/O in chunks
  • I/O in dbspaces
  • I/O queues
  • I/O write types
  • light appends
  • light scans
  • locks, onstat -u example
  • memory-resident tables
  • percent of dirty pages in LRU queues
  • query-processing balance
  • query segments and SQL operators
  • session resources
  • table fragment information
  • temporary space usage (onstat -g tmp)
  • threads on specific coserver
• Monitoring Cartesian Joins
• Multiple-index scans
  • example
  • isolation levels for
  • key-only scan requirements
  • when used
• Mutability property (1), (2)
  • setting for all variables
  • Using sysdbopen() stored procedure

• Named pipes, FIF virtual processors for
• Network
  • as performance bottleneck
  • connection configuration setting
• Network poll threads, creating virtual processors for
• New features of this product
• NOAGE parameter, disabling priority aging
• NUMAIOVPS parameter
• NUMFIFOVPS parameter
• NVARCHAR data type
  • calculating size of column

• OFF_RECVRY_THREADS configuration parameter, number of threads for cold restore
• OLTP applications
  • characteristics described
  • maximizing memory for
  • operational data store
  • performance
    • composite indexes for
    • isolation level and multiple index use
  • planning for
• ON_RECVRY_THREADS configuration parameter
• Online help
• Online manuals
• Online notes
• Online Notes
• onmode utility
  • -F option to free unused memory segments
  • -p option
    • adding AIO VPs
    • adding FIF VPs
  • -z option to kill sessions
• Onmode utility
  • onmode -q
    • Changing mutability property in an active session
• onstat -g mut command
• onstat -g rqm command
• onstat -g xit command
• onstat -g xmp command
• onstat -g xqp comman
• onstat -g xqs command
• onstat utility
  • -d
    • for information about dbspaces
    • output example
    • to monitor free space in chunks
  • -D to monitor chunk reads and writes
  • -F example
  • -g ath option
    • description of
    • example
    • purpose
  • -g dic, monitoring data-dictionary cache
  • -g iof to monitor disk reads and writes
  • -g ioq option, monitoring I/O queues
  • -g iov, to monitor VPs
  • -g rgm option
    • description of
    • queues and memory allocation displayed
  • -g rgm option, monitoring all queries
  • -g scn, monitoring light scans
  • -g seg option for memory segments
  • -g ses option
    • basic session information
    • description of
    • information displayed
    • monitoring session resources
    • output example
  • -g sql option
    • description of
    • information displayed
    • SQL statement information
  • -g xmp option
    • description of
    • information by SQL operator
    • output sample (1), (2)
  • -g xqp option
    • description of (1), (2)
    • example
    • query segment information
  • -g xqs option
    • description of (1), (2)
    • for single query
    • query-segment information
  • -k option
    • for lock-owner list
  • -P option
    • monitoring memory-resident tables
    • monitoring read ahead
  • -t option to monitor tblspaces
  • -u option
    • description of
  • description of
  • monitoring
    • buffer and cache rates
    • chunk I/O requests
    • dbspace I/O requests
    • free space in chunks
    • general memory use
    • LRU queues
    • query segments (1), (2)
    • threads on specific coserver
  • options
    • -g tmp
  • RGM wait-queue sample output
• Onstat utility
  • onstat -g mut
    • Monitoring mutability property
• onutil utility
  • and index size
  • check index integrity
  • CHECK INDEX option
  • CHECK SPACE option
  • CHECK TABLE option
  • CREATE example for collocated joins
  • determining free space in index
  • DISPLAY TABLE option
  • monitoring chunks and extents
  • monitoring growth of tables
• Operational data store, described
• OPERATIONAL table type, advantages and disadvantages of
• Optimizer
  • and hash join
  • automatic memory allocation managed by
  • bounding automatic memory allocation
• Optimizer directives
  • analyzing need for
  • AVOID_FULL
  • AVOID_HASH
  • AVOID_INDEX
  • effect on views (1), (2)
  • FULL (1), (2)
  • INDEX
  • INDEX_ALL
  • listed and described
  • NESTED
  • ORDERED (1), (2), (3)
  • USE_HASH
  • USE_NL
• ORDER BY clause, in query

• Page cleaning
  • number of threads at checkpoints
• Parallel
  • inserts, effect of indexes on
• Parallel database query
  • and correlated subqueries
  • monitoring resources allocated for
  • on multiple coservers
  • on single coserver
  • when not used
• Parallel processing
  • factors that affect
  • table fragmentation for
• Participating coserver
  • x_exec thread
• Partitioning.
  • See also Fragmentation.
• PDQPRIORITY
  • adjusting to prevent memory overflow
  • description of
  • environment variable syntax
  • limited by MAX_PDQPRIORITY
  • minimum if not set
  • query admission policy, used in
  • resetting for specific session
  • session-specific settings
  • setting in SPL routines
  • SQL statement syntax
• Performance
  • effect of
    • contiguous extents
    • correlated subquery
    • duplicate index keys
    • index modification
    • indexes
    • temporary tables
  • improving table update
  • network bottleneck problems
  • problems caused by system jobs
  • problems related to backup and restore
• Performance history
  • monitoring I/O queue length
  • monitoring read-cache rate
  • shared memory data
• PHYSBUFF configuration parameter, setting size of physical log buffer
• PHYSFILE parameter
• Physical log
  • buffer size
  • estimating size of
  • size related to checkpoints
  • specifying size of
• Pipes, monitoring AIO read/write
• Poll threads, network, creating virtual processors for
• Printed manuals
• Priority aging, disabling
• Probe table, directives

• Queries
  • assessing filters in
  • behavior for unavailable data
  • distributed (1), (2)
  • limiting CPU VP use
  • limiting temporary space for operator overflow
  • monitoring all concurrent
  • monitoring for data skew
  • monitoring resource use of
  • proportional memory allocation among operators
  • stopping for specific session
• Query plan
  • analyzing
  • displaying statistics for
  • for a distributed query
  • for distributed query
  • interpreting SET EXPLAIN output
  • limiting to one CPU VP per coserver per operator

• RA_PAGES parameter
  • described
  • monitor and adjust
• RA_THRESHOLD parameter
• RAID systems
  • chunk defined
  • configuring for database server use
  • described
  • stripe defined
• Range distribution scheme
  • example
  • hybrid (1), (2)
  • when to use
• RAW table type, advantages and disadvantages of
• Read-ahead
  • definition of
  • tuning and monitoring
• Read-cache rate, tuning BUFFERS to improve
• Read-only dbspaces
  • planning to optimize backup performance
• Rebuilding index, reasons for
• Release Notes
• Resizing table to reclaim empty space
• Resource use
  • temporary changes to limits
• Rewritten subquery, SET EXPLAIN output
• RGM (Resource Grant Manager)
  • admission policy levels
  • memory-grant factors
  • monitoring resources managed by
  • PDQPRIORITY and query admission
  • query admission policies
• Round-robin distribution scheme
  • described
  • limits to usefulness

• sales_demo database
• Sample-code conventions
• sar command
  • for memory management
  • to display resource statistics
• Saturation, definition of
• Scan threads, specifying number of
• Scans
  • light, advantages of
  • sequential, read-ahead for
• SCHLEVEL configuration parameter
  • resetting for specific session
• SCRATCH temporary table, creating
• Segments
• Selective index (GK), description of
• Selectivity
  • and indexed columns
  • definition of
  • of filter, description of
• Semaphores, required
• SENDEPDS configuration parameter, adjusting for interconnect resends
• Serial column values, as hash distribution columns
• Serial plans
  • table fragmentation for
• Session
  • adjusting resource use by
  • ID in query-monitoring output
  • kill specific
  • monitoring
  • resetting PDQPRIORITY for
  • resetting schedule level for
  • set initial environment for
  • specifying resource use by
  • stopping queries run by
• SET ALL_MUTABLES statement
• SET ENVIRONMENT statement
  • BOUND_IMPL_PDQ option
  • Changing mutability property
  • COMPUTE_QUOTA option
  • IMPLICIT_PDQ option
  • MAXSCAN option
  • option interactions listed
  • session management with
  • TMPSPACE_LIMIT option
• SET EXPLAIN
  • AVOID_EXECUTE
• SET EXPLAIN statement
  • appending new data
  • output example
  • query statistics in output, example of
  • rewritten subquery in output
  • with AVOID_EXECUTE directive
• SET LOCK MODE TO WAIT statement, effect of
• Shared memory
  • adjusting size of virtual portion
  • amount required by each lock
  • estimating amount for DSS queries
  • estimating amount for sorting
  • limiting for DSS queries
  • monitoring current segments
  • parameters
    • SHMADD
    • SHMVIRTSIZE
  • required for sorting in index build
• SHMADD parameter, specifying memory increments
• SHMVIRTSIZE parameter, specifying initial size of shared memory
• Simple large objects
  • estimating dbspace pages for
  • estimating tblspace pages for
  • loading and unloading from external tables
• Size, estimating
  • data page contents
  • of NVARCHAR
  • of pages for simple large objects
  • of tables
  • of TEXT data columns
  • of VARCHAR
  • table with variable-length rows
• Skew.
  • See also Data skew.
• Skip scans, reported in onstat -g scn
• Small table broadcast
  • explicit local table copies
• SMI (system-monitoring interface) tables
  • description of (1), (2)
• Software dependencies
• Sort memory
  • estimating for index builds
• Sorting
  • avoiding repeated
  • effect on performance
  • estimating temporary space for
  • memory estimate for
  • using temporary table to avoid
  • when parallel
• SPL routines
  • PDQPRIORITY settings in
  • sysdbclose() described
  • sysdbopen() described
• sqexplain.out file
  • example
    • query statistics
• SQL code
• SQL extensions
  • CASE statement
  • MIDDLE clause
• SQL operators
  • description of
  • listed and described
• SQLWARN array, for data skipping
• STANDARD table type, advantages and disadvantages of
• Static dbspaces
  • planning to optimize backup performance
• STATIC table type, advantages and disadvantages of
• Statistics
  • generating on a table
• Stored procedure.
  • See also SPL routine.
• stores_demo database
• Structured Query Language (SQL)
  • ALTER FRAGMENT statement
  • ALTER TABLE statement (1), (2)
  • CREATE TABLE statement
  • DELETE USING statement
  • DISTINCT keyword
  • EXTENT SIZE clause
  • GROUP BY clause
  • LOAD and UNLOAD statements
  • MODIFY NEXT SIZE clause (1), (2)
  • NEXT SIZE clause for extents
  • ORDER BY clause
  • TRUNCATE TABLE statement
  • UPDATE STATISTICS statement
  • WHERE clause
• Subqueries
  • nested, and parallel processing
  • uncorrelated, and parallel processing
• Syntax diagrams
  • conventions for
  • keywords in
  • variables in
• Syntax segment
• sysdbclose() routine, how used
• sysdbclose() stored procedure
• sysdbopen() routine
  • debugging
  • example
  • how used
• sysdbopen() stored procedure (1), (2)
• sysfragments table, columns described
• sysptprof SMI table, monitoring tblspace activity
• System catalog tables
  • fragmentation-monitoring information
  • monitoring fragmentation (1), (2)
  • sysdistrib, data distributions stored
• System maintenance, conflicts with database server use
• System requirements
  • database
  • software

• Table-access patterns, for planning distribution schemes
• Table types
  • chart of
  • OPERATIONAL
  • RAW
  • SCRATCH
  • STANDARD
  • STATIC
  • TEMP
• Tables
  • backup of nonlogging type before conversion
  • configuring I/O for
  • deleting all rows with TRUNCATE TABLE statement
  • dummy update to force conversion
  • duplicating small
  • estimating
    • data pages
    • index pages
    • simple large objects in dbspace
    • size with fixed-length rows
    • size with variable-length rows
  • extent management
  • external, simple large objects in
  • fragmentation, performance advantage of
  • fragmentation scheme, changing
  • loading and unloading
  • memory-resident
  • monitoring I/O for fragments
  • specifying lock mode
  • types.
    • See also Table types.
  • using SMI tables to monitor activity
• Tblspace
  • definition of
• tblspaces, monitoring
• TCP/IP buffers, specifying
• TEMP temporary table, creating
• Temporary changes to resource limits
• Temporary space
  • creating
  • guidelines for creating
  • limiting use for query-operator overflow
  • required for index builds
• Temporary tables
  • created by the database server
  • created WITH NO LOG
  • how used
  • reducing sorting scope
  • SCRATCH (1), (2)
  • TEMP
• TEXT data type
  • estimating size
• Threads
  • DEFUNCT status of
  • monitoring activity
  • monitoring for session
  • monitoring with onstat
• TOC Notes
• Transactions
  • See also OLTP.
• Trigger
  • restriction for table with globally detached index
  • restrictions for OPERATIONAL table
• TRUNCATE TABLE statement, to remove all rows from a table

• Unavailable data, behavior for
• Uncorrelated subquery, definition of
• UNIX commands
  • iostat
  • ps
  • sar
  • vmstat
• UPDATE statement, dummy to force table conversion
• UPDATE STATISTICS statement
  • for indexed columns
  • guidelines for running
  • MEDIUM mode, adjusting for efficiency
  • parallel execution
  • RESOLUTION clause, using to tune
  • setting BUFFERS to improve performance
• USEOSTIME configuration parameter, performance issues
• Users, types of
• Utilities
  • See also individual utilities.
  • xctl.
    • See also xctl utility.

• VARCHAR data type
  • calculating size
• Variable-length rows, estimating table size
• Variables, in syntax diagrams
• View, effect of optimizer directives (1), (2)
• Virtual-column index (GK), description of
• Virtual portion of shared memory
  • defined
  • initial size estimates
  • monitoring
• Virtual processors (VPs)
  • AIO
    • adding in Online mode
    • determining optimal number
  • FIF
    • adding in Online mode
    • determining optimal number
  • for network poll threads
• vmstat command
  • displaying virtual-memory statistics
  • for memory management

• Write-cache rates, tuning LRU queues to improve

• xctl utility
  • displaying all executing threads
  • displaying query plan across coservers
  • displaying threads for session
  • monitoring SQL operators
  • purpose of
  • with onlog
• xplan, defined
• Xplans (1), (2), (3)