Everything You Ever Wanted to Know About Migrating From Informix to DB2 * But were afraid to ask

1. Everything You Ever Wanted to Know About Migrating From Informix to DB2*But were afraid to ask J.Warren Donovan Bob Carts

2. Everything You Ever Wanted to Know About Migrating from Informix to DB2 Bob Carts Senior Data Engineer, SAIC robert.m.carts@saic.com J. Warren Donovan Senior Software Engineer, SAIC warren.j.donovan@saic.com

3. About SAIC • 42,000 Employees • Headquarters in San Diego • Largest Site is Washington, DC Area • Majority of Work is Federal • www.saic.com

4. 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

5. 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

6. 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

7. What is in this Session? • Will cover: • Converting DDL • Creating disk / tablespace structures • Creating Memory Structures • Basic Configuration / Tuning and, • Migration of SQL Code

8. Similarities of Informix and DB2 • Relational Databases • Both owned by IBM • Both available for most platforms • Connect to a wide variety of front ends

9. 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 Product Differences

10. 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.

11. 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!

12. Informix vs. DB2 Structure DB2 System Informix System Instances Instances SHEAPTHRES Memory Memory DBspaces Databases Buffers MGM Buffers Buffers Databases Tblspaces Tblspaces Tables Tables Tables Tables Indexes Indexes Indexes Indexes

13. From Informix Layout Disk (create space for binaries - minimum) Install Informix Setup onconfig Start instance Create database Create dbspaces Update / Run DDL Load Data To DB2 Layout Disk (create space for binaries and DB at minimum) Install DB2 Start Instance Setup DBM CFG Create database Setup DB CFG Create memory structures Create tablespaces Update / Run DDL Load Data Warren’s Setup Order

14. From Informix To DB2 Pick DB2 installation: Single or Multi Partition Get Informix DDL – Convert to DB2 Analyze DDL for Tablespace Structure Create DDL for Tablespaces Analyze Tablespaces for Memory Structure Create Memory DDL Create Instance Create Database Update / Run DDL Load Data Monitor and Tune Database Warren’s DB2 Migration Order

15. 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

16. 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 Comparison

17. 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

18. 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;

19. 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 ;

20. 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

21. 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

22. Sample DB2 Create Index Statement CREATE INDEX NHL.PLAY_ID ON NHL.PLAYERS (ID_NUMBER ASC) PCTFREE 5 ALLOW REVERSE SCANS;

23. 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

24. 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 Informix Dbspaces vs DB2 Tablespaces

25. 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

26. 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

27. 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

28. 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;

29. 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;

30. 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”

31. 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

32. 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

33. 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

34. 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

35. 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

36. 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

37. 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 Differences in Memory

38. 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

39. 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)

40. 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

41. 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…

42. 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

43. 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!

44. INFORMIX Onconfig Sqlhosts Informix.rc DB2 Database Manager Configuration (DBM Config) Database Config (DB Config) .rhosts Db2set Differences in Install and Initial Configuration

45. 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

46. 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 dbserver2 0 4 dbserver2 1

47. Starting DB2 As the database owner, just run: db2start

48. 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 <param> <value> • Changes affects the instance, and therefore all databases in the instance • Some changes take effect immediately, most require a db2stop and db2start

49. 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

50. 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 Creating a Database