Data Storage And Migration

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]