IBM Informix Dynamic Server 10.x Utilities Quick Reference Guide
compliments of
An IBM Advanced Partner Advanced DataTools Corporation 4216 Evergreen Lane, Suite 136 Annandale, VA 22003 (800) 807-6732 (703) 256-0267
www.advancedatatools.com
Advanced DataTools is dedicated to providing the best database tools, web technologies, consulting ® services and training to companies using IBM Informix database software. We have years of relational database experience in designing and implementing systems. We have successfully implemented web-enabled data warehouses and OLTP systems for our customers. We offer advanced Informix training, and provide support for all aspects of a systems life cycle, from planning and development, to performance tuning and maintenance. Our goal is to boost the performance and reliability of your database systems, and enable your staff to effectively utilize Informix database systems to meet your business requirements.
In 1995, after years of trying to find a manual to check the command line syntax of a utility, or scribbling notes to myself on scraps of paper with the command line options of the Informix Dynamic Server utilities, I decided to make myself a quick reference guide. Several friends asked for copies, which resulted in the 5.X and then the 7.3x and 9.X versions. This guide is based on 10.X, and is meant to jog your memory. Some of the commands are very powerful so please use them with care. I hope this new version helps! Regards - Lester Knutsen --lester@advancedatatools.com
IBM Informix Dynamic Server 10.x Utilities Quick Reference Guide From Advanced DataTools Corporation www.advancedatatools.com Page 1
ONCHECK Usage: oncheck {-cCheckOptions | -pPrintOptions} [-y | -n] [-q] [ { database[:[owner.]table[,fragdbs|#index]] | TBLspace number | Chunk number } { rowid | page number } ] [# pgs] [-h] -c CheckOptions r Reserved pages R Reserved pages including logical and physical logs e Extents c Database catalogs [database] i Table indexes database[:[owner.]table[#index]] I Table indexes and rowids in index database[:[owner.]table[#index]] x Place share lock on table during index check d TBLspace data rows including bitmaps database[:[owner.]table[,fragdbs]] D TBLspace data rows including bitmaps, remainder pages and BLOBs database[:[owner.]table[,fragdbs]] s SBLOBspace metadata partitions S SBLOBspace metadata partitions and LO extents PrintOptions r Reserved pages (-cr) R Reserved pages including logical and physical logs (-cR) e Extents report (-ce) c Catalog report (-cc) [database] k Keys in index (-ci) database[:[owner.]table[#index]] K Keys and rowids in index (-cI) database[:[owner.]table[#index]] l Leaf node keys only (-ci) database[:[owner.]table[#index]] L Leaf node keys and rowids (-cI) database[:[owner.]table[#index]] x Place share lock on table during index check d TBLspace data rows (-cd) database[:[owner.]table[,fragdbs]] [rowid] D TBLspace data rows including bitmaps, remainder pages and BLOBs (-cD) database[:[owner.]table[,fragdbs]] [page number] t TBLspace report database[:[owner.]table[,fragdbs]] T TBLspace disk utilization report database[:[owner.]table[,fragdbs]] p Dump page for the given [table[,fragdbs] and rowid | TBLspace and page number] {[# pgs] [-h]} P Dump page for the given chunk number and page number [chunk num and page number] {[# pgs] [-h]} B BLOBspace utilization for given table(s) database[:[owner.]table[,fragdbs]] s SBLOBspace metadata partitions S SBLOBspace metadata partitions and LO extents Quiet mode - print only error messages Answer NO to all questions Answer YES to all questions
-p
-q -n -y
ONDBLOG Usage: ondblog
[-f ] [] new mode: buf Change database to buffered mode. unbuf Change database to unbuffered mode. nolog Change database to no logging. ansi Change database to be ANSI-compliant cancel Cancel logging request. -f File containing list of databases for logging change. List of databases for logging change.
IBM Informix Dynamic Server 10.x Utilities Quick Reference Guide From Advanced DataTools Corporation www.advancedatatools.com Page 2
ONINIT Usage: oninit -[ijpsy] -i Initialize disk space and shared memory, leave in on-line mode. (Note: This will destroy all data on any existing dbspaces) -j Initialize shared memory, leave in single-user mode. -p Do not reclaim temporary tables. -s Initialize shared memory, leave in quiescent mode. -y Respond yes to all prompts -V Initialize in verbose mode displaying extra debugging messages ONLOG Usage: onlog [-l] [-q] [-b] [-d ] [-n < starting log unique identifier->] [-u ] [-t ] [-x ] -l Display maximum information about each log record -q Do not display program header -b Display information about logged BLOB pages (-d option only) -d Read from tape device -n Display the specified log(s) -u Display the specified user(s) -t Display the specified TBLspace(s) -x Display the specified transaction(s) ONMODE usage: onmode [-abCcDdFklMmnOpQRrSsuyZz] | [-wf =] | [-wm =] -a Increase shared memory segment size. -b Revert Dynamic Server disk structures. -BC [1|2] Change server large chunk mode -c [block | unblock] Do Checkpoint. Block or unblock server. -C {start #|stop #|high|low|threshold} Tune Btree scanner resources. -D -d {standard|{primary|secondary }} set DR server type On DR secondary only: {idxauto {on|off}} set DR automatic index repair mode {index :[owner.]#} DR repair index -F Free unused memory segments -j Change to single-user mode -k Shutdown completely -e {on|off|enable|flush} configure or flush shared statement cache. -l Force to next logical log -M -m Go to multi-user on-line -n Set shared memory buffer cache to non-resident -O Override space down blocking a checkpoint -p <+-#> Start up or remove virtual processors of a specific class -Q -R Rebuild the /INFORMIXDIR/etc/.infos.DBSERVERNAME file -r Set shared memory buffer cache to resident -S -s Change to quiescent mode -u Change to quiescent mode and kill all attached sessions -W {STMT_CACHE_NOLIMIT {0|1} | STMT_CACHE_HITS <#>} Sets SQL cache parameters. -wf = update the value for the variable in the onconfig file. -wm = update the value for the variable in memory. -y Do not require confirmation -Y [0|1] Set or unset dynamic explain -Z heuristically complete specified transaction -z Kill specified session id
IBM Informix Dynamic Server 10.x Utilities Quick Reference Guide From Advanced DataTools Corporation www.advancedatatools.com Page 3
ONPARAMS Usage: onparams -a -d [-s ] [-i] | -b -g [-n ][-r ] [-x ] [-m ] } | -d -l [-y] | -p -s [-d ] [-y] -a -b -i -d -p -y Add a logical log file Add a buffer pool Insert after current log Drop a logical log file Change physical log size and location Automatically responds "yes" to all prompts
ONSPACES Usage: onspaces -a -p -o -s [-m ] { { [-Mo ] [-Ms ] } | -U } | -c -d [-k ] [-t] -p -o -s [-m ] | -c -d [-k ] -p -o -s [-m ] [-ef ] [-en ] | -c -b -g -p -o -s [-m ] | -c -S [-t] -p -o -s [-m ] [-Mo ] [-Ms ] [-Df ] | -c -x -l | -d [-p -o ] [-f] [-y] | -f[y] off [] | on [] | -m {-p -o -m [-y] | -f } | -r [-y] | -s -p -o {-O | -D} [-y] | -ch -Df | -cl | -ren -n -a -c -d -f -m -r -s -ch -cl -ren Add a chunk to a DBspace, BLOBspace or SBLOBspace Create a DBspace, BLOBspace, SBLOBspace or Extspace Drop a DBspace, BLOBspace, SBLOBspace, Extspace, or chunk Change dataskip default for specified DBspaces Add mirroring to an existing DBspace, BLOBspace or SBLOBspace Turn mirroring off for a DBspace, BLOBspace or SBLOBspace Change the status of a chunk Change default list for smart large object space garbage collect smart large objects that are not referenced default-list = {[LOGGING = {ON|OFF}] [,ACCESSTIME = {ON|OFF}] [,AVG_LO_SIZE = {1 - 2097152}] } Rename a DBspace, BLOBspace, SBLOBspace or Extspace
IBM Informix Dynamic Server 10.x Utilities Quick Reference Guide From Advanced DataTools Corporation www.advancedatatools.com Page 4
ONSTAT usage: onstat [ -abcdfghklmpstuxzBCDFRX ] [ -i ] [ -r [] ] [ -o [] ] [ ] -a Print all info -b Print buffers -c Print configuration file -d Print spaces and chunks -d [update] update - Ask server to update BLOB chunk statistics -f Print dataskip status -g Print MT subcommand (default: all) -i Interactive mode -h Print buffer hash chain info -j Print interactive status of the active onpload process -k Print locks -l Print logging -m Print message log -p Print profile -s Print latches -t Print TBLspaces -u Print user threads -x Print transactions -z Zero profile counts -B Print all buffers -C Print btree cleaner requests -D Print spaces and detailed chunk stats -F Print page flushers -G Print global transaction ids. -P Print partition buffer summary -R Print LRU queues -T Print tablespace information -X Print entire list of sharers and waiters for buffers -r Repeat options every seconds (default: 5) -o Put shared memory into specified file (default: onstat.out) Read shared memory information from specified dump file ONSTAT MT COMMANDS: (onstat –g command) all Print all MT information ath Print all threads wai Print waiting threads act Print active threads rea Print ready threads sle Print all sleeping threads spi Print spin locks with long spins sch Print VP scheduler statistics lmx Print all locked mutexes lsc Print Light Scan information wmx Print all mutexes with waiters con Print conditions with waiters stk Dump the stack of a specified thread glo Print MT global information mem [|] Print pool statistics. seg Print memory segment statistics rbm Print block map for resident segment nbm Print block map for non-resident segments afr Print allocated pool fragments ffr Print free pool fragments ufr Print pool usage breakdown iov Print disk IO statistics by vp iof Print disk IO statistics by chunk/file iog Print AIO global information iob Print big buffer usage by IO VP class
IBM Informix Dynamic Server 10.x Utilities Quick Reference Guide From Advanced DataTools Corporation www.advancedatatools.com Page 5
ONSTAT MT COMMANDS - continued lsc Print light scans ppf [ | 0] Print partition profiles tpf [ | 0] Print thread profiles ntu Print net user thread profile information ntt Print net user thread access times ntm Print net message information ntd Print net dispatch information nss [] Print net shared memory status nsc [] Print net shared memory status nsd Print net shared memory data sts Print max and current stack sizes dic Print dictionary cache information opn [] Print open tables qst Print queue statistics wst Print thread wait statistics rwm Print Read/Write Mutex lists ses [] Print session information sql [] Print SQL information stq [] Print stream queue information smb Print smart-large-object usage dri Print data replication information pos Print /INFORMIXDIR/etc/.infos.DBSERVERNAME file mgm Print Memory Grant Manager information lap Print light append information ddr Print DDR log post processing information env [ all | [] ] [[,...]] Display environment variable settings. dmp Dump bytes of shared memory starting at src Search memory for , where ==(memory&) dll Print dynamic library statistics ssc [pool|all] Prints ssc pool summary, or statement cache summary and entries stm [] Prints all prepared statements approximate memory usage in a session buf Print profile information related to buffer pools. ONTAPE usage: ontape -a | -c |-l | -p [-e] | -S | -r [-rename {-f | -p -o -n -o ...}] [-D DBspace_list] | -s [[-L archive_level][-F]] [-A database_list] [-B database_list] [-N database_list] [-U database_list] } -a Automatic backup of logical logs -c Continuous backup of logical logs -l Logical restore -p Physical restore for HDR -e Physical restore for HDR from external restore -r Full restore DBspaces/BLOBspaces as listed -s Archive full system -A Set the following database(s) to ansi logging -B Set the following database(s) to buffered logging -N Set the following database(s) to no logging -U Set the following database(s) to unbuffered logging -F Do not record the physical archive -S Salvage logical logs only -rename Rename chunks during cold restore with -rename options: -f pathname of file containing list of mapped chunk pathnames -p old pathname of chunk -o old offset of chunk -n new pathname of chunk -o new offset of chunk
IBM Informix Dynamic Server 10.x Utilities Quick Reference Guide From Advanced DataTools Corporation www.advancedatatools.com Page 6