Everything you ever wanted to know about migrating from informix to db2 but were afraid to ask
Download
1 / 99

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


  • 39 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Everything You Ever Wanted to Know About Migrating From Informix to DB2 * But were afraid to ask' - raja-mcgee


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Everything you ever wanted to know about migrating from informix to db2 but were afraid to ask

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
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
About SAIC Informix to DB2

  • 42,000 Employees

  • Headquarters in San Diego

  • Largest Site is Washington, DC Area

  • Majority of Work is Federal

  • www.saic.com


About us
About Us Informix to DB2

  • 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
About Our Project Informix to DB2

  • 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
What is in this Session? Informix to DB2

  • 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 session1
What is in this Session? Informix to DB2

  • Will cover:

    • Converting DDL

    • Creating disk / tablespace structures

    • Creating Memory Structures

    • Basic Configuration / Tuning and,

    • Migration of SQL Code


Similarities of informix and db2
Similarities of Informix and DB2 Informix to DB2

  • Relational Databases

  • Both owned by IBM

  • Both available for most platforms

  • Connect to a wide variety of front ends


Product differences

INFORMIX Informix to DB2

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


Definitions some db2 speak
Definitions – Some DB2 Speak Informix to DB2

  • 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
What’s a Logical Partition? Informix to DB2

  • 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 vs. DB2 Structure Informix to DB2

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


Warren s setup order

From Informix Informix to DB2

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


Warren s db2 migration order

From Informix To DB2 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


Migrating ddl
Migrating DDL Informix to DB2

  • 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 Informix to DB2

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


Creating tables
Creating Tables Informix to DB2

  • 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
Informix Create Table Statement Informix to DB2

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
DB2 Create Table Statement Informix to DB2

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
About Partitioning Keys Informix to DB2

  • 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
Creating Indexes Informix to DB2

  • 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
Sample DB2 Create Index Statement Informix to DB2

CREATE INDEX NHL.PLAY_ID ON NHL.PLAYERS (ID_NUMBER ASC)

PCTFREE 5 ALLOW REVERSE SCANS;


With tables ready time to setup tablespaces
With Tables Ready… Informix to DB2Time 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 Informix to DB2

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


Initial disk layout
Initial Disk Layout Informix to DB2

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
Maximum Tablespace Sizes Informix to DB2

  • 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
Choosing a Tablespace type Informix to DB2

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
Creating a simple DMS Raw Tablespace Informix to DB2

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
Creating a simple DMS Cooked Tablespace Informix to DB2

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
DMS Tablespaces: Informix to DB2Things 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
Monitoring DMS Tablespaces Informix to DB2

  • 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
Creating an SMS Tablespace Informix to DB2

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
SMS Tablespaces: Informix to DB2Things 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
Monitoring SMS Tablespaces Informix to DB2

  • 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 Critical Notes on Tablespaces Informix to DB2

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
From Tablespaces to Memory Informix to DB2

  • 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 Informix to DB2

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


Db2 bufferpool basics
DB2 Bufferpool Basics Informix to DB2

  • 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
Creating Bufferpools Informix to DB2

  • 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
Some BUFFERPOOL Recommendations Informix to DB2

  • 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
Let’s git it on! Informix to DB2

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
Layout Disk for Binaries and System Catalogs Informix to DB2

  • 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
Layout Disk For Tablespaces Informix to DB2

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 Informix to DB2

Onconfig

Sqlhosts

Informix.rc

DB2

Database Manager Configuration (DBM Config)

Database Config (DB Config)

.rhosts

Db2set

Differences in Install and Initial Configuration


Install db2 binaries
Install DB2 Binaries Informix to DB2

  • 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
Example db2nodes.cfg file Informix to DB2

  • 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


Starting db2
Starting DB2 Informix to DB2

As the database owner, just run:

db2start


Setting up the dbm config
Setting up the DBM Config Informix to DB2

  • 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


Dbm config parameters
DBM Config Parameters Informix to DB2

  • 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 Informix to DB2

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


Create database script
Create Database Script Informix to DB2

create database nhl_mart on /db2ins07/db2ese

catalog tablespace managed by system using ('/nhl_mart/syscat/nhl_sys');


Db config
DB Config Informix to DB2

  • One per database

  • Cannot change with editor

  • db2 get db cfg for <dbname>

  • To update on a single partition server:

    • db2 update db cfg for <dbname> using <param> <value>

  • To update on a multi partition server:

    • db2_all db2 update db cfg for <dbname> using <param> <value>


Db config parameters
DB Config Parameters Informix to DB2

  • BUFFPAGE – default bufferpool

  • SORTHEAP –

  • AVG_APPLS

  • LOGFILSIZE, LOGPRIMARY, LOGSECOND and NEWLOGPATH

  • DFT_QUERYOPT – 1-9

  • LOGBUFSZ


Extra step for multi partition setup
Extra Step for Multi-Partition Setup Informix to DB2

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
Setting Up Logs Informix to DB2

  • 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
SHEAPTHRES: Major DB2 Memory Considerations Informix to DB2

  • Do you do a lot sorting in this instance?…this database?

  • Do you anticipate (or observe) large overflows to tempspace?

  • 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
What is SORT HEAP? Informix to DB2

  • 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
SHEAPTHRES / SORT HEAP recommendations Informix to DB2

  • 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
Some Basic Monitoring Informix to DB2

  • 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 <Appl. Handle number>” for all information about that query


Some get snapshot output
Some Get Snapshot Output Informix to DB2

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
Some Cool Tools Informix to DB2

  • Materialized Query Tables: MQTs

  • Index Advisor: db2advis

  • Db2shema - db2look

  • GETDISTRIB –Checking your Data Distribution


What s an mqt
What’s an MQT Informix to DB2

  • 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
Example MQT Informix to DB2

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
Defining Indexes with Informix to DB2DB2 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 <dbname> -i <filename> -o <output file>

    Example:

    db2advis –d testdb –i trouble.sql –o fix.out


Db2 advis output
DB2 Advis Output Informix to DB2

  • 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
DB2LOOK Informix to DB2

  • 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
GETDISTRIB – Check distribution of a table Informix to DB2

Use the GETDISTRIB from the db2 prompt to output the distribution of the data in a table

Syntax: getdistrib <tablename> <field>

Example: getdistrib employee empno

Returns: 1) Partition Number 2) Rows

1 2

----------- -----------

1 151967

2 138988

3 193551

4 162090


What are we going to do about all this code
What are We Going to Do About All This Code?? Informix to DB2

  • 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 code1
What are We Going to Do About All This Code?? Informix to DB2

  • 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
Some Things We Learned - Support Informix to DB2

  • 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
Some Things We Learned – Documentation Informix to DB2

  • 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
Some Things We Learned – Monitoring Informix to DB2

  • 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 monitoring1
Some Things We Learned – Monitoring Informix to DB2

  • 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 <PID>

    • DB2: get snapshot for application agentid <PID>

  • Kill a process:

    • INFORMIX: onmode –z <PID>

    • DB2: force application ‘(PID)’ or force application all


Some things we learned monitoring2
Some Things We Learned – Monitoring Informix to DB2

  • 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
Interactive Access Informix to DB2

  • 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
DB2CLP Informix to DB2

  • Several ways to execute commands

    • db2 <command>

    • Example: db2 connect to mydb

  • You can also use interactive mode

    db2 –t

    Connect to mydb;

    Select col1, col2

    From mytable;

    Quit;


Db2clp1
DB2CLP Informix to DB2

  • 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 <schemaname>;

  • Get the layout of a table:

    Describe table <schemaname>.<tablename>;


Calling from ksh script
Calling from ksh Script Informix to DB2

  • Dbaccess [dbname] <<EOF > stdout 2>stderror

    Select bla bla bla;

    EOF

  • Db2 –tvl <logfilename> <<EOF >

    Connect to [dbname];

    Select bla bla bla

    EOF


A few little things
A few little things… Informix to DB2

  • Default Permissions

    • Informix: Public has permissions by default

    • DB2: public does not

  • Updating Statistics (different syntax)

    Runstats on <schema>.<table> with distribution

    And indexes all shrlevel change;

  • Code Comments

    • DB2 does support the dash dash for comments

    • However, they need to start in column #1 of a line

      -- This works as a comment

      somecol char(3) -- this does not


A few little things1
A few little things… Informix to DB2

  • Don’t use double quotes in DB2 !

    Select * from tabname where name = ‘Bob’

  • DB2 does not support Directives


Datatypes
Datatypes Informix to DB2

  • DB2 does not support implicit casting

  • Explicitly cast all data types in expressions

  • Example:

    Create table bob.tabname (col1 integer,col2 char(10),col3 char(3))…

    Insert into tabname values (null, ‘bob’, null) --informix

    Insert into tabname values (cast(null as integer), ‘bob’, cast(null as char))


Limiting number of rows returned optimize for number of rows
Limiting Number of Rows Returned/Optimize for Informix to DB2Number of Rows

  • Informix: Select first 100 ssn from people;

    DB2:Select ssn from people

    Fetch first 100 rows only;

  • Optimize for a particular number of rows (db2 only)

    Db2:Select ssn from people

    Optimize for 20 rows;


Join syntax
Join Syntax Informix to DB2

  • DB2 Outer join syntax is different than Informix

  • DB2 is reportedly ANSI standard and Informix is not


Join syntax1
Join Syntax Informix to DB2

INFORMIX:

Select a.name, a.employ_num, b.program, c.ed_level From employee a, training b, OUTER education c Where a.employ_num = b.employ_num and a.employ_num = c.employ_num and b.program = ‘DB2101’

DB2:

Select a.name, a.employ_num, b.program, c.ed_level From employee a INNER JOIN training b on a.employ_num = b.employ_num LEFT OUTER JOIN education c on a.employ_num = c.employ_num

Where b.program = ‘DB2101’


Group by
Group by Informix to DB2

  • Can’t use “number” syntax

    Group by 1,2,3….

  • Forced to make case statements, etc redundant


Group by informix
Group by - INFORMIX Informix to DB2

Select gender, state_of_birth,

Case when age > 19 and age < 31 then ‘Young’

when age > 30 and age < 46 then ‘middle aged’

when age > 46 then ‘Up there’

End category

From employee

Group by 1,2,3


Group by db2
Group by – DB2 Informix to DB2

Select gender, state_of_birth,

Case when age > 19 and age < 31 then ‘Young’

when age > 30 and age < 46 then ‘middle aged’

when age > 46 then ‘Up there’

End case

From employee

Group by gender, state_of_birth,

Case when age > 19 and age < 31 then ‘Young’

when age > 30 and age < 46 then ‘middle aged’

when age > 46 then ‘Up there’

End case


Having
Having Informix to DB2

  • Syntax available in DB2 and not Informix

  • Look for duplicate keys

    select * from people_table where ssn in

    (select ssn from people_table

    group by ssn having count(*) > 1 );


Alter statements
Alter Statements Informix to DB2

  • Alter capabilities are limited in DB2

    • Can’t drop a column

    • Can’t change a datatype for a column

  • We of course used the alter – drop in our Informix Code!


Unlogged tables
UnLogged Tables Informix to DB2

  • Using Unlogged databases in Informix is straight forward

  • Using Unlogged tables in db2 version 7.2 is

    • Awkward

    • Temporary

    • Dangerous

    • Still Possible

  • Db2 version 8.1 is less disastrous

  • Basic problem is auto rollback makes table permanently unavailable, must recreate or restore


Unlogged tables1
UnLogged Tables Informix to DB2

  • When creating a table must specify that logging can be turned off

    Create table bob.xyz

    (Col1 char(2))

    In tablespace123 index in indexspace456

    Not logged initially;

  • Must alter the table to temporarily turn logging off

    Update command options using c off;

    Alter table bob.xyz activate not logged initially;

    Insert into bob.xyz …

    Commit;

  • If anything goes wrong, boom no useable table!


Utilities
Utilities Informix to DB2

  • DB2 has import, export, load utilities

    • Load is fastest way to get data into table

    • Load can handle various delimiters or no delimiters

    • You can replace or insert (append)

    • Terminate or restart

    • Example:

      Load from /pathname/filename

      Of del modified by coldel| keepblanks anyorder

      Messages messagefile.msg

      Temp files path /large_directory

      Replace into bob.xyz;


Utilities1
Utilities Informix to DB2

  • Another load example (using cursor):

    Declare cursor mycursor

    select …

    load from mycursor of cursor

    METHOD P (1,2,3,4,5…)

    replace INTO bob.xyz NONRECOVERABLE;

  • Approx 25% faster than using “insert into tablename select from..”


Utilities2
Utilities Informix to DB2

  • Another load example (mapping cols):

    load from strip.txt OF ASC

    METHOD L (1 7,9 43,45 54,56 90,92 126,128 145,

    147 148,150 160,268 277,336 336)

    messages messagefile.msg

    tempfiles path $WORKDIR

    replace INTO bob.xyz NONRECOVERABLE;

  • Import is slow


Utilities3
Utilities Informix to DB2

  • Export has several differences from dbexport

  • By default numbers have a + and leading zeros

  • Character data is enclosed by double quotes

  • Character data is padded to full length

  • Example:

    Export to filename.out

    Of del modified by coldel| decplusblank

    Select date_provided, rtrim(record_id) from tabname;

  • Used sed to strip out quotes and leading zeros

  • New parameters nochardel and stripLzeros


Utilities4
Utilities Informix to DB2

  • Getting the ddl

  • Informix: dbschema

    Dbschema –d databasename outputfilename.out

  • DB2: db2look

    Db2look –d databasename –e > outputfilename.out

  • Both have many options

  • Both have usage built in, just type command


Error messages
Error Messages Informix to DB2

  • Both databases provide error messages from the command line

    • INFORMIX: finderr –217

      -217 Column column-name not found in any table in the query

      (or SLV is undefined).

      The name appears in the select list or WHERE clause of this query but is…

    • DB2: db2 ? SQL0203

      SQL0203NA reference to column "<name>" is ambiguous.

      Explanation: The column "<name>" is used in the statement …


Informix xps version 8 x
INFORMIX XPS (Version 8.x) Informix to DB2

  • DB2 does not have the external table feature, must up import, export and load utilities

  • DB2 requires explicit indexes to perform adequately

  • DB2 does not have the join update/batch update feature (a subselect must be used)

  • DB2 does not support truncate command


Summary

Yes, you too can migrate to DB2! Informix to DB2

Summary


ad