Informix Data Storage and Data Movement Utilities - 2455 James Edmiston Quest Information Systems, Inc.
Commercial Break… WAIUG/SEIUG Forum 2006 – December 8 and 9 (Fri and Sat) in Wash DC (N. VA) – www.iiug.org/waiug
Join the IIUG – It’s free – www.iiug.org
IDUG (IDIG) 2007 – May 6-10, 2007 in San Jose, CA
Quotes… "I think there is a world market for maybe five computers." - Thomas Watson, chairman of IBM, 1943 "Computers in the future may weigh no more than 1.5 tons.“ - Popular Mechanics, 1949 "There is no reason anyone would want a computer in their home." - Ken Olson, president, chairman and founder of DEC "640K of memory ought to be enough for anybody.“ - Bill Gates, 1981 "Microsoft Products are Generally Bug Free" - Bill Gates "The Internet? We are not interested in it" - Bill Gates, 1993
Outline Part 1 Informix Data Storage Creating, Managing and Monitoring Observing Guidelines Part 2 Informix Data Movement tools and utilities Getting Data “In” and “Out”
Purpose What I found at a client site – – – – –
Lack of Critical and temp dbspace separation Little or no use of fragmentation (big tables) Lots of chunks in 1 data dbspace Inability to control specific disk placements Use of RAID 5 (dev and test)
RAID 5 – www.baarf.com – BAARF - Battle Against Any RAID Five, Four, Free (3)
Offer practical advice and recommendations
Example Dbspaces address c0000000a774fe60 c0000000a9c55e98 c0000000a9c6f9b8 c0000000a9c6fb38 c0000000a9c6fcb8 c0000000a9c6fe38 c0000000a9c70028 . . . Chunks address c0000000a7751028 c0000000a9c56028 c0000000a9c561c0 c0000000a9c56358 c0000000a9c564f0 c0000000a9c56688 c0000000a9c56820
number 1 2 3 4 5 6 7
chunk/dbs 1 1 2 2 3 3 4 4 5 5 6 6 7 7
flags 0x1 0x1 0x1 0x2001 0x2001 0x2001 0x2001
offset 0 50000 175000 0 256000 512000 768000
fchunk 1 2 3 4 5 6 7
size 50000 125000 849000 256000 256000 256000 256000
nchunks 1 1 3 2 2 2 2
flags N N N N T N T N T N T
free 43876 447 447 255381 255373 255373 255381
owner informix informix informix informix informix informix informix
bpages
name rootdbs physdbs logdbs tmpdbs1 tmpdbs2 tmpdbs3 tmpdbs4
flags PO-PO-PO-PO-PO-PO-PO--
pathname /dev/infx/ck_01_1 /dev/infx/ck_01_1 /dev/infx/ck_01_1 /dev/infx/ck_01_2 /dev/infx/ck_01_2 /dev/infx/ck_01_2 /dev/infx/ck_01_2
Example 2 (same installation…) Dbspaces address . . . c0000000a9c701a8 c0000000a9c70328 c0000000a9c704a8
Chunks address c0000000a9c569b8 c0000000a9c56b50 c0000000a9c56ce8 c0000000a9c56e80 c0000000a9c57028 c0000000a9c57b50 c0000000a9c57ce8 c0000000a9c57e80 c0000000a9c58028 c0000000a9c581c0 . . .
number
flags
fchunk
nchunks
flags
owner
8 9 10
0x1 0x1 0x1
8 13 157
107 45 12
N N N
informix pstable1 informix psindex1 informix pstable2
chunk/dbs 8 8 9 8 10 8 11 8 12 8 19 8 20 8 21 8 22 8 23 8
offset 0 0 0 0 0 0 0 0 0 0
size 1024000 1024000 1024000 1024000 1024000 1024000 1024000 1024000 1024000 1024000
free 17898 6482 16490 28314 372 372 67036 29389 19971 70158
bpages
name
flags PO-PO-PO-PO-PO-PO-PO-PO-PO-PO--
pathname /dev/infx/ck1 /dev/infx/ck2 /dev/infx/ck3 /dev/infx/ck4 /dev/infx/ck5 /dev/infx/ck6 /dev/infx/ck7 /dev/infx/ck8 /dev/infx/ck9 /dev/infx/ck10
SAN
Part 1 – Informix Data Storage
Physical Chunk – Largest unit of physical disk dedicated to database server data storage – Raw disk devices or regular file system files (cooked) – Raw disk chunks perform better due to less OS overhead – Maximum size prior to IDS 9.4 was 2GB – Maximum size in IDS 9.4 and higher is 4TB with use of large chunks (onmode –BC) – v10 eliminates 2GB max default and need to use onmode -BC
Physical (cont) Pages – Unit of disk storage read from and written to – 2k bytes on Unix and 4k on Windows – Configurable Page size introduced in IDS 10 • Multiple of platform page size up to 16k
Physical (cont) Extents – Physical unit of storage used to allocate storage space for a database table – Consists of a collection of contiguous pages – Initial Extent size and Next Extent size – Important to manage extent sizes and number of extents – Manage with EXTENT SIZE, NEXT SIZE – Default – 16k
Logical Dbspaces – Logical unit of storage – Can have between 1 and 32,767 chunks – Contains databases, tables, indexes, logical logs and physical log, temporary space – Separate • • • • •
Root Log spaces Temp Data Indexes
Dbspaces Root Dbspace – Initial dbspace containing reserved pages and internal tables – Default dbspace location for database creation – Configured in ONCONFIG: # Root Dbspace Configuration ROOTNAME ROOTPATH ROOTOFFSET ROOTSIZE
rootdbs /dev//informix/chunk1 0 2048000
# Root dbspace name # Path for device containing root dbspace # Offset of root dbspace into device (Kbytes) # Size of root dbspace (Kbytes)
# Disk Mirroring Configuration Parameters MIRROR MIRRORPATH MIRROROFFSET
1 # Mirroring flag (Yes = 1, No = 0) /dev/informix2/chunk1 # Path for device containing mirrored root 0 # Offset into mirrored device (Kbytes)
Dbspaces Logical Logs – Move out of root dbspace to a dbspace on a disk that is not shared by active tables or the physical log
Physical Log – Move out of root dbspace…
Temp Dbspaces – Place on different disks and list in DBSPACETEMP ONCONFIG parameter DBSPACETEMP
tmp1dbs,tmp2dbs,tmp3dbs,tmp4dbs,tmp5dbs,tmp6dbs,tmp7dbs,tmp8dbs
Data, Indexes
Creating, Managing and Monitoring Creating and Managing – onspaces - command line utility – On-Monitor – GUI utilities • Server Studio • ISA • IDSAdmin (Demo at IBM pedestal)
Monitoring – onstat – SMI queries – GUI utilities
Creating and Managing onspaces onspaces -c -d dbspce1 -p /dev/raw_dev1 -o 0 -s 2097150 -m /dev/raw_dev2 0
– Create, add, drop
onmonitor DBSPACES: Create BLOBSpace Mirror Drop Info Add_chunk datasKip Status Exit Create a new dbspace. -----------------------------On-Line------- Press CTRL-W for Help. --------
ISA, Server Studio
On-Monitor Press ESC to build a new DBspace. Press Interrupt to cancel the option and return to the Dbspaces menu. Press F2 or CTRL-F for field level help. CREATE DBSPACE Dbspace Name [ Dbspace Page Size [
]
Mirror [ ]
Temp [ ]
2] Kbytes
PRIMARY CHUNK INFORMATION: Full Pathname [ Offset [
] 0] Kbytes
Size [
0] Kbytes
MIRROR CHUNK INFORMATION: Full Pathname [ Offset [
] 0] Kbytes
Monitoring onstat –d (spaces and chunks showing size and free space) Dbspaces address number flags 0EACB7D8 1 0x20001 0EBDBE90 2 0x20001 0EACBAE0 3 0x28001 0EACBC30 4 0x20001 4 active, 2047 maximum
fchunk 1 2 3 4
Chunks address 0EACB928 0EBDBA28 0EBDBBA0
nchunks 1 1 1 1
chunk/dbs offset size 1 1 0 12800 2 2 0 12800 3 3 0 12800 Metadata 868 554 0EBDBD18 4 4 0 500000 4 active, 2047 maximum
flags N N N S N
free 5309 12697 11879 868 431989
owner informix informix informix informix
bpages
11879
name rootdbs ol_james sbspace dbs_data1
flags PO-PO-POS-
pathname \IFMX\rootdbs \IFMX\ol_james \IFMX\sbspace
PO--
\IFMX\dbs_data1
Expanded chunk capacity mode: disabled
Also: onstat –D (spaces and chunks, shows reads and writes) onstat -g iof (shows chunk reads and writes)
oncheck –pe = Database and table storage in a chunk > oncheck –pe ol_james DBspace Usage Report: ol_james Chunk Pathname 2 \IFMX\ol_james
Owner: informix
Created: 01/26/2006
Size 12800
Used 792
Free 12008
Description Offset Size ------------------------------------------------------------- -------- -------RESERVED PAGES 0 2 CHUNK FREELIST PAGE 2 1 ol_james:'informix'.TBLSpace 3 50 stores_demo:'informix'.systables 53 8 stores_demo:'informix'.syscolumns 61 16 stores_demo:'informix'.sysindices 77 24 stores_demo:'informix'.systabauth 101 8 stores_demo:'informix'.syscolauth 109 8 . . . stores_demo:'jgedmiston'.zip_ix 704 4 stores_demo:'jgedmiston'.catalog 708 8 stores_demo:'jgedmiston'.msgs 724 8 stores_demo:'jgedmiston'.idxmsgs_enus 732 5 stores_demo:'jgedmiston'.idxmsgs_frfr 737 5 FREE 742 103 ol_james:'informix'.TBLSpace 845 50 FREE 895 11905 Total Used: Total Free:
792 12008
Extents C:\demo>oncheck -pt stores_demo:customer TBLspace Report for stores_demo:jgedmiston.customer Physical Address Creation date TBLspace Flags Maximum row size Number of special columns Number of keys Number of extents Current serial value First extent size Next extent size Number of pages allocated Number of pages used Number of data pages Number of rows Partition partnum Partition lockid
2:52 08/20/2006 15:18:58 801 Page Locking TBLspace use 4 bit bit-maps 134 0 0 1 129 8 8 8 2 1 28 2097201 2097201
SMI (sysmaster) queries ------------------------------------------------------------------------------ Module: @(#)dbsfree.sql 1.4 Date: 97/07/18 -- Author: Lester B. Knutsen Email:
[email protected] -Advanced DataTools Corporation -- Discription: Displays free space in all dbspaces like Unix "df -k " command ------------------------------------------------------------------------------ Note:On some versions (e.g 7.3) need to use the 7.2 version of -this script to correctly display the truncated dbspace name. -If the dbspace name is blank - use the 72 version. database sysmaster; select
name[1,8] dbspace, -- name truncated to fit on one line sum(chksize) Pages_size, -- sum of all chuncks size pages sum(chksize) - sum(nfree) Pages_used, sum(nfree) Pages_free, -- sum of all chunks free pages round ((sum(nfree)) / (sum(chksize)) * 100, 2) percent_free sysdbspaces d, syschunks c d.dbsnum = c.dbsnum
from where group by 1 order by name[6,8], name[1,5]; --order by 1;
dbspace dbs_data rootdbs sbspace ol_james
pages_size
pages_used
pages_free
percent_free
500000 12800 12800 12800
68011 7499 12246 103
431989 5301 554 12697
86.40 41.41 4.33 99.20
Server Studio
ISA
ISA
Guidelines rootdbs - Keep it clean – Don’t allow any user databases to be created in root – Allow extra space for system table growth
Critical dbspaces – Move physical log and logical logs to their own dbspace (preferably on different disks)
Temp dbspaces – Must specify in ONCONFIG DBSPACETEMP or rootdbs will be used – Used for creation of temporary tables and sorting • ORDER BY, GROUP BY, UNIQUE, etc.
– Use multiple temp dbspaces and place on separate disks
Guidelines (cont) Minimize number of chunks per dbspace Use table fragmentation for large, high use tables Reduce number of table extents Take advantage of large chunks to minimize the number of chunks/dbspaces on larger drives Utilize the configurable pages size for tables with large row size Test
Part 2 – Data Movement Utilities
Comparison of data migration tools… dbexport/ dbimport
dbload
HPL
onunload/ onload
UNLOAD/ LOAD
Granularity of data
Database only
Partial or complete table
Partial or complete table
Table or database
Partial or complete table
Performance
Moderate
Slow
Fast
Fast
Moderate
Source of Data
Usually produced by dbexport
Any data in the format specified by the input file
Any ASCII or COBOL data, User can create custom capabilities
Must be produced by onunload
Any data in the specified format usually produced by UNLOAD
Database Schema
Can modify
Can modify
Can modify
Cannot modify
Can modify
Location of Data
Disk or tape
Disk only
Disk, tape, or pipe
Disk or tape
Disk only
Type of File
Text
Text
Text
Binary
Text
Logging Status
Logging optional
Logging optional
Logging optional
Logging must be turned off
Logging optional
Move Data Between OS?
Yes
Yes
Ys, or from a non Informix database
No
Yes
Ease of Use
Moderate
Moderate
Most difficult
More difficult
Easiest
unload/load Use of SQL Limit/filter with “where” clause Extraction of whatever data that can be queried in an SQL statement Delimited data – Default is a “|” (pipe, vertical bar) if DBDELIMITER has not been set to something different – Can use other delimiters if specified, except “/”, “0-9”, “a-z” and “A-Z”
ASCII as output (or input) Simple/quick demo
dbexport/dbimport dbexport unloads a database into ASCII files for later import into another database Creates a schema file of the database – database.sql Creates unload of each table to single file - table.unl dbimport creates and populates a database from data in ASCII files The schema file can be edited if necessary Use same environment variables If the database is a logging database, import with no logging, then use ontape to change logging status
dbexport/dbimport (cont) Use –X option to export binary data in char fields Can export to tape On disk, creates a directory – database.exp – Default location is from where the command is issued – Able to specify different location with -o parameter Usage: dbexport
[-X] [-c] [-q] [-d] [-ss] [{ -o | -t -b -s [-f <sql-command-file>] }] NOTE: arguments to dbexport are order independent.
Simple/quick demo
Summary Informix Data Storage – Follow recommended guidelines – Separate and spread I/O – Monitor and manage
Moving Data In and Out – Choose utility to meet your need – Flexible and easy to use
References Informix Administrator’s Guide – Chapter on Data Storage – Chapter on Managing Disk Space
Informix Migration Guide – Data Migration Tools – Data Movement Utilities
Informix Data Storage and Data Movement Utilities - 2455 James Edmiston Quest Information Systems, Inc. [email protected]