Transcript Migrating Informix to DB2
Everything You Ever Wanted to Know About Migrating From Informix to DB2 * But were afraid to ask
J.Warren Donovan Bob Carts
Everything You Ever Wanted to Know About Migrating from Informix to DB2
Bob Carts Senior Data Engineer, SAIC [email protected]
J. Warren Donovan Senior Software Engineer, SAIC [email protected]
About SAIC
42,000 Employees Headquarters in San Diego Largest Site is Washington, DC Area Majority of Work is Federal www.saic.com
About Us
Certified Informix DBAs Certified DB2 DBAs WAIUG Board of Directors Windows and UNIX (Solaris, IBM AIX, HP UX) IDS 7.31, 9.21, 9.3, XPS 8.31, DB2 8.1
Data Warehouse and OLTP Applications
About Our Project
Data Warehouse Multi-Node 1800+ Aggressive Users 600+ DSS Queries per day Converted from Informix XPS version 8.31 to DB2 version 8.1
900 GB of data 684 pieces of ETL code ETL code SQL, KSH, PERL
What is in this Session?
Goal: To provide basic information on differences between Informix and DB2 SQL to help you get started in evaluating, planning or executing a conversion Assumption:
You are familiar with Informix
Included:HOW to migrate Not Included:WHY …or why not
What is in this Session?
Will cover:
Converting DDL Creating disk / tablespace structures Creating Memory Structures Basic Configuration / Tuning and, Migration of SQL Code
Similarities of Informix and DB2
Relational Databases Both owned by IBM Both available for most platforms Connect to a wide variety of front ends
Product Differences
INFORMIX Different products for different uses Simple configuration Simple performance tuning DB2 “One product fits all” Complex configuration options Advanced and robust performance tuning toolset Will still exist in 10 years
Definitions – Some DB2 Speak
DBSpaces = Tablespaces Chunks = Containers Coservers = Logical Partitions Logical Partitions are the biggest difference between Informix 7.x / Informix 9.x and DB2.
What’s a Logical Partition?
A virtual Database server A DB2 Database uses Logical Partitions (or LPs) to maximize parallel processing by spreading data across I/O and CPUs LPs can be used to spread data across multiple physical servers Can be used to overcome tablespace size limitations Can be used to overcome 2GB Memory Limitations of 32-bit installs Before you do anything, decide if you will be using a Single or Multiple LP install!
Informix vs. DB2 Structure
Informix System Instances Memory DBspaces DB2 System Instances Memory SHEAPTHRES Buffers MGM Databases Buffers Buffers Databases Tables Tables Tblspaces Tables Tblspaces Tables Indexes Indexes Indexes Indexes
Warren’s Setup Order
From Informix 1.
Layout Disk ( create space for binaries - minimum ) 2.
3.
4.
5.
Install Informix Setup onconfig Start instance Create database 6.
7.
8.
Create dbspaces Update / Run DDL Load Data To DB2 1.
Layout Disk ( create space for binaries and DB at minimum ) 2.
3.
4.
5.
Install DB2 Start Instance Setup DBM CFG Create database 6.
7.
8.
9.
10.
Setup DB CFG Create memory structures Create tablespaces Update / Run DDL Load Data
Warren ’ s DB2 Migration Order
From Informix To DB2 1.
Pick DB2 installation: Single or Multi Partition 2.
3.
Get Informix DDL – Convert to DB2 Analyze DDL for Tablespace Structure 4.
5.
6.
Create DDL for Tablespaces Analyze Tablespaces for Memory Structure Create Memory DDL 7.
8.
9.
10.
11.
Create Instance Create Database Update / Run DDL Load Data Monitor and Tune Database
Migrating DDL
The first step is to rewrite Informix DDL to DB2 Get Informix DDL using dbschema Data types, Primary and Secondary keys remarkably similar Extent sizes, Indexes, Fragmentation/Partitioning are not.
Know what tables you want together, and if you will install a Single or Multiple Partition DB2 instance
Creating Tables Comparison
INFORMIX Can set initial and next extent sizes Can fragment across dbspaces Fragment by round robin, expression + hash Can create indexes later in any dbspace DB2 Extent size set by tablespace 1 Table to 1 Tablespace Hash fragment in multi-partition, round-robin automatic in a tablespace with multiple containers Set index location in create table statement
Creating Tables
Basically, all the same data types One table – one tablespace Must specify index location during create table statement If you’ll ever need to do unlogged updates or inserts, use the “not logged initially” option A Partitioning Key is a good idea, especially if creating the table in a tablespace with containers that span multiple Logical Partitions
Informix Create Table Statement
CREATE TABLE NHL.PLAYERS ( NAME CHAR(20) , TEAM VARCHAR (20) GOALS INTEGER , ASSISTS INTEGER , ID_NUMBER INTEGER ) FRAGMENT BY ROUND ROBIN IN bigspace EXTENT SIZE 10000 NEXT SIZE 1000;
DB2 Create Table Statement
CREATE TABLE NHL.PLAYERS ( NAME CHAR(20) , TEAM VARCHAR (20) GOALS INTEGER , ASSISTS INTEGER , ID_NUMBER INTEGER ) PARTITIONING KEY (ID_NUMBER) IN BIGSPACE_1 INDEX IN BIG_INDEX_1 NOT LOGGED INITIALLY ;
About Partitioning Keys
The more diverse the data in a field, and the more it is used in joins, the better Defaults to PK (first column if no PK) Greatest performance boost is from co-located joins: when it can join to another table on the same key, and can therefore ignore whole containers
Creating Indexes
You WILL need indexes Location is determined during table definition Be sure to use the ALLOW REVERSE SCANS parameter Can use the db2advis tool to recommend indexes
Sample DB2 Create Index Statement
CREATE INDEX NHL.PLAY_ID ON NHL.PLAYERS (ID_NUMBER ASC) PCTFREE 5 ALLOW REVERSE SCANS;
With Tables Ready… Time to Setup Tablespaces
Once table DDL is complete, analyze it for tablespaces One table fits into one and ONLY one tablespace Tablespaces can hold multiple tables Tablespaces must have one, and only one, memory buffer pool
Informix Dbspaces vs DB2 Tablespaces
DBspaces Raw Devices, Cooked Can add chunks Writes consecutively to chunks Tables can be fragmented across DBSpaces Extents set at TABLE creation time Can offset in raw devices Can mirror at DB level Tablespaces DMS Raw,DMS Cooked,and SMS Can add containers Automatically balances data across containers 1 Table to 1 Tablespace Extents set at tablespace level Cannot offset in raw devices No DB mirroring
Initial Disk Layout
DB2 has 3 types of tablespaces System Managed Space (for database, tempspace and blob/clobs) Raw Database Managed Space (DMS Raw) “Cooked” Database Managed Space (DMS Cooked) Know when to use which, and why! DB2 has no internal DB mirroring: use OS mirroring.
DB2 cannot set offsets on raw devices: one container to one raw device
Maximum Tablespace Sizes
True for all tablespace types Max size per logical partition the tablespace spans: With 4KB pages– 64GB With 8KB pages– 128GB With 16KB pages – 256GB With 32KB pages – 512GB Max of 255 rows per page
Choosing a Tablespace type
DMS Fastest Can add containers Cannot contain LOBs SMS Very flexible, very easy to set up Cannot add containers Can contain LOBs
Creating a simple DMS Raw Tablespace
CREATE REGULAR TABLESPACE REFERENCE IN DATABASE PARTITION GROUP REF_GRP PAGESIZE 8192 MANAGED BY DATABASE USING (DEVICE '/dev/reference_part1'131072) ON DBPARTITIONNUMS (1) EXTENTSIZE 240 PREFETCHSIZE 240 BUFFERPOOL REF_8K OVERHEAD 12.500000
TRANSFERRATE 0.300000;
Creating a simple DMS Cooked Tablespace
CREATE REGULAR TABLESPACE REF2 IN DATABASE PARTITION GROUP REF_GRP PAGESIZE 8192 MANAGED BY DATABASE USING (FILE '/dev/ref2_part1.dat'131072) ON DBPARTITIONNUMS (1) EXTENTSIZE 240 PREFETCHSIZE 240 BUFFERPOOL REF_8K OVERHEAD 12.500000
TRANSFERRATE 0.300000;
DMS Tablespaces: Things to keep in mind
Never use them for system catalogs For RAW: No offsets available: set your raw device to exactly the size you need No DB mirroring: mirror disk at OS level Cooked slightly more flexible, slightly slower When using multiple containers, make your container sizes the same for load and data balancing!
Monitor with the “db2 list tablespaces show detail command”
Monitoring DMS Tablespaces
db2 list tablespaces show detail Tablespace ID = 5 Name = BIGSPACE_1 Type = Database managed space Contents = Any data State = 0x0000 Detailed explanation: Normal Total pages = 4587520 Useable pages = 4587120 Used pages = 3137520 Free pages = 1449600 High water mark (pages) = 4474560 Page size (bytes) = 8192 Extent size (pages) = 240 Prefetch size (pages) = 240 Number of containers = 1
Creating an SMS Tablespace
CREATE temporary TABLESPACE TEMP2_8K IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 8192 MANAGED BY SYSTEM USING ('/temp1_jfs2/tmp1_1') on nodes(1) USING ('/temp2_jfs2/tmp2_1') on nodes(2) USING ('/temp3_jfs2/tmp3_1') on nodes(3) USING ('/temp4_jfs2/tmp4_1') on nodes(4) EXTENTSIZE 24 PREFETCHSIZE 72 BUFFERPOOL TEMP_8K OVERHEAD 12.500000 TRANSFERRATE 0.300000
SMS Tablespaces: Things to keep in mind
Slowest Ideal for system catalogs Ideal for tempspace Easy to set up, minimal planning as they Expand and Contract as required Cannot expand or add containers Monitor by watching the filespace fill and empty
Monitoring SMS Tablespaces
db2 list tablespaces show detail – always listed as full, so watch file system too Tablespace ID = 9 Name = TTMP_8K Type = System managed space Contents = System Temporary data State = 0x0000 Detailed explanation: Normal Total pages = 18689 Useable pages = 18689 Used pages = 18689 Free pages = Not applicable High water mark (pages) = Not applicable Page size (bytes) = 8192 Extent size (pages) = 240 Prefetch size (pages) = 240 Number of containers = 1
Some Critical Notes on Tablespaces
Some parameters not seen in Informix: EXTENT SIZE – The extent size for all tables in this tablespace PREFETCH SIZE – Pages grabbed at a time BUFFERPOOL – name of the bufferpool the tablespace will use. Must exist before table can be created, can be changed OVERHEAD, TRANSFERRATE – Indicators of the speed of the disk the tablespace uses. Affects the optimizer
From Tablespaces to Memory
Before you can create tablespaces, you will need bufferpools with the same page size to dedicate them to Can just use the default: IBMDEFAULTBP Can create specific buffers later, and change with the “alter tablespace” command Can never change the page size of an existing tablespace
Differences in Memory
INFORMIX BUFFERS SHMVIRTSIZE SHMTOTAL DS_TOTAL_MEMORY Log, backup buffers, etc.
(Resident, Virtual and Message) DB2 BUFFPAGE Custom Bufferpools SHEAPTHRES SORTHEAP Lots of log, backup and other little buffers
DB2 Bufferpool Basics
Comes with a default IBMDEFAULTBP Can create different buffer pools, with different page sizes, for different tablespaces and data IBM doesn’t recommend using more than 2 different page sizes Created in the database you are currently connected to
Creating Bufferpools
This creates an 8K Bufferpool CREATE BUFFERPOOL M2_8K SIZE 175000 PAGESIZE 8192 NOT EXTENDED STORAGE; In a Multi-Partition install, it is created across all partitions Total size will be: SIZE * ( number of LPs)
Some BUFFERPOOL Recommendations
Expand the default BUFFER with the alter bufferpool command First try 1 big buffer for all your tablespaces…this will probably be fine Experiment with the following: Create small bufferpools for reference tables If you have a small number of large, busy tables, create bufferpools for each Try creating a separate bufferpool for tempspace
Let’s git it on!
Now that we know what Bufferpools, Tablespaces and Tables we need…let’s install and configure DB2!
But there’s a few things we’ll want to do first…
Layout Disk for Binaries and System Catalogs
Create a filesystem directory structure to install the binaries Create a filesystem to contain the system catalogs (the database) Keep them separate from disk you plan to put data on DB2 has no internal DB mirroring: use OS mirroring
Layout Disk For Tablespaces
DB2 has 3 types of tablespaces, with different disk requirements System Managed Space – Requires a filesystem directory structure for each path DMS Raw – Requires raw devices for each container DMS Cooked – Requires a filesystem directory structure to create containers in Know when to use which, and why!
Differences in Install and Initial Configuration
INFORMIX Onconfig Sqlhosts Informix.rc
DB2 Database Manager Configuration (DBM Config) Database Config (DB Config) .rhosts
Db2set
Install DB2 Binaries
Very similar to Informix install: RTFM! (Read the fine Manual) For DB2 Multi-Partition Installs: requires that binaries be installed on each PHYSICAL node Multi-Partition also requires creation of the db2nodes.cfg file before startup
Example db2nodes.cfg file
Located in $db2home/sqllib Per P690 Red Book: 1 LP per CPU – I think this is excessive.
3 Columns: Absolute LP number, Name of Physical Node, Number on Physical Node 1 dbserver1 0 2 dbserver1 1 3 4 dbserver2 dbserver2 0 1
Starting DB2
As the database owner, just run: db2start
Setting up the DBM Config
1 per instance db2 get dbm cfg (for current settings) Cannot change with editor To Update: db2 update dbm cfg using
DBM Config Parameters
DFTDBPATH INTRA_PARALLEL SHEAPTHRES MONITOR SWITCHES (Buffer Pool, Lock, Sort, Statement,Table, Timestamp, Unit of Work and the killer Health Monitor) SVCENAME Backup, restore and audit buffers
Creating a Database
INFORMIX Location defaults to rootspace (defined in config) Put in dbspace Dbspace must exist Raw space for best performance DB2 Location defaults to DFLTDBPATH in DBM CFG Put in OS path Path must exist System Managed Space for best performance
Create Database Script
create database nhl_mart on /db2ins07/db2ese catalog tablespace managed by system using ('/nhl_mart/syscat/nhl_sys');
DB Config
One per database Cannot change with editor db2 get db cfg for
DB Config Parameters
BUFFPAGE – default bufferpool SORTHEAP – AVG_APPLS LOGFILSIZE, LOGPRIMARY, LOGSECOND and NEWLOGPATH DFT_QUERYOPT – 1-9 LOGBUFSZ
Extra Step for Multi Partition Setup
If doing a multi-partition install, you will want to setup Partition Groups…since you probably don’t need all your data spread across every node!
CREATE DATABASE PARTITION GROUP "BIG_PART" ON DBPARTITIONNUMS (1,2,3,4); CREATE DATABASE PARTITION GROUP "REF_PART" ON DBPARTITIONNUMS (1);
Setting Up Logs
For performance, recommend setting up all logs as LOGPRIMARY Try to place logs on disk not used for other activities. Set a new path with the NEWLOGPATH param, then bounce engine Circular logging is a new feature
SHEAPTHRES: Major DB2 Memory Considerations
1.
2.
Do you do a lot sorting in this instance?…this database?
Do you anticipate (or observe) large overflows to tempspace?
3.
Are you frequently reading large volumes of data from 2 or more tables?
If you answer YES to these questions, you may need SHEAPTHRES: SORT MEMORY
What is SORT HEAP?
Individual rows are written to BUFFERS in each database All sorts are done in a memory pool called the Sort Heap SHEAPTHRES -a DBM parameter used to set an instance wide max sort heap SORTHEAP – A DB parameter used by each database to determines how much Sort Heap a single query can use within that database
SHEAPTHRES / SORT HEAP recommendations
Make sure SHEAPTHRES + BUFFERPOOLS is less than system memory Start small – adding to SHEAPTHRES will require a reduction of BUFFERPOOLS Capture SQL and monitor queries to gauge SORT OVERFLOWS Set the SORTHEAP to fit evenly into SHEAPTHRES When the SORTHEAP overflows to temp buffers, it writes the entire SORTHEAP. So, a large SORTHEAP may actually hurt performance!
Some Basic Monitoring
To get any useful information, you must turn on the Monitor Switches in the DBM CFG Use “db2 list applications” to get the Appl. Handle number Use “db2 get snapshot for application
Some Get Snapshot Output
The output is much too extensive to review entirely, but here’s some interesting stuff that’s in it: Dynamic SQL statement text Sort Overflows Rows Read, Rows Written Bufferpool Data Logical Reads
Some Cool Tools
Materialized Query Tables: MQTs Index Advisor: db2advis Db2shema - db2look GETDISTRIB –Checking your Data Distribution
What’s an MQT
A Materialized Query Table is like a summary table that is automatically referenced Can be costly to build in terms of processing time and disk If designed properly, can significantly reduce processing time on many reports by effectively having the results already processed.
Example MQT
CREATE TABLE CORP.MQT_SALES AS ( SELECT STORE_NO, EMPNO, CUSTNO, SUM(SALE_PRICE_RAW) as sale_price_raw, SUM(COMMISSIONS) as commissions_paid_tot, SUM(COST_OF_GOODS) as cost_of_goods_raw, SUM(SALES_TAX) as sales_tax_tot FROM CORP.SALES GROUP BY STORE_NO, EMPNO, CUSTNO) data initially deferred refresh immediate ENABLE QUERY OPTIMIZATION MAINTAINED BY SYSTEM partitioning key (store_no) in bigspace_2 not logged initially; commit; update command options using c off; alter table CORP.MQT_SALES activate not logged initially; REFRESH TABLE M2ADM.MQT_SALES NOT INCREMENTAL;
Defining Indexes with DB2 Advis
Use the DB2 Advis utility to analyze SQL statements for indexes First capture a SQL statement that is exhibiting poor performance Write it to file, say trouble.sql
Run db2advis –d
DB2 Advis Output
Will output an estimate (in timerons) to run the query with and without the recommended indexes Will output indexes (if any will help) Remember to add ALLOW REVERSE SCANS to the create index statements!
DB2LOOK
Use the DB2LOOK tool to output a schema, or physical layout To get all options: db2look ?
The following would output all the DDL needed to recreate the test database to a file called test.ddl
db2look –d test –e –l –o test.ddl
GETDISTRIB – Check distribution of a table
Use the GETDISTRIB from the db2 prompt to output the distribution of the data in a table Syntax: getdistrib
What are We Going to Do About All This Code??
Application code can be converted to DB2 The question is: how to convert how long will it take how will performance be after conversion
What are We Going to Do About All This Code??
You will hear: SQL is SQL Just Point the Application at DB2 Just run the code through the conversion tool To estimate: Id all the code that will need to change (joins, group by, external table, truncate, etc) build an estimate, then at least double it !
Some Things We Learned Support
Make friends with DB2 Developers in Toronto because the DB2 help desk does not answer SQL questions DB2 SQL assistance is available for $ Informix Help Desk Does answer SQL questions
Some Things We Learned – Documentation
DB2 documentation is on par or better than Informix documentation (and Informix documentation is pretty good!) Improvements to the documentation are in the works (adding examples) Look at IBM.com, DB2 Technical Support, Product Manuals The manuals we use most: SQL reference Volumes 1 and 2 Data Movement Utilities Guide and Reference
Some Things We Learned – Monitoring
Informix “onstat” commands make for easy monitoring While monitoring tools are available in DB2, they can be awkward Onstat type monitoring commands are on the list to be added to DB2 in a future release
Some Things We Learned – Monitoring
Determine which processes are running: INFORMIX: Onstat –g ses/sql/act/ath DB2: list applications show detail View a specific process: INFORMIX: onstat –g ses
Some Things We Learned – Monitoring
View the database configuration: INFORMIX: onstat –c DB2: get database configuration and/or get database managers configuration (get db cfg / get db mgr cfg) View available tablespaces: INFORMIX: onstat –d/-D/-t/-T DB2: list tablespace show detail
Interactive Access
DBACCESS – Psuedo GUI, Menu bar driven DB2 CLP (command line processor) – A little clumsy, but adequate. More like sybase or oracle interface Getting Help Help dbaccess cntl-w Help ? CLP Command Connecting Db2 initially requires an explicit connect Informix implicitly connects when using dbaccess
DB2CLP
Several ways to execute commands db2
db2 connect to mydb
You can also use interactive mode
db2 –t Connect to mydb; Select col1, col2 From mytable; Quit;
DB2CLP
You can execute OS commands within DB2 CLP ! Cp file1 file2 Get a list of databases: List active databases; Get a list of columns: List tables [for schema
Calling from ksh Script
Dbaccess [dbname] <
A few little things…
Default Permissions Informix: Public has permissions by default DB2: public does not Updating Statistics (different syntax) Runstats on