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, HPUX)
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
Memory SHEAPTHRES
Databases
Buffers
MGM
Databases
Tables
Tables
Indexes
DB2 System
Instances
Indexes
Buffers
Buffers
Tblspaces
Tblspaces
Tables
Tables
Indexes
Indexes
Warren’s Setup Order
From Informix
To DB2
1. Layout Disk (create space 1. Layout Disk (create space
for binaries - minimum)
for binaries and DB at minimum)
2. Install Informix
2. Install DB2
3. Setup onconfig
3. Start Instance
4. Start instance
4. Setup DBM CFG
5. Create database
5. Create database
6. Create dbspaces
6. Setup DB CFG
7. Update / Run DDL
7. Create memory
structures
8. Load Data
8. Create tablespaces
9. Update / Run DDL
10. Load Data
Warren’s DB2 Migration Order
From Informix To DB2
1. Pick DB2 installation: Single or Multi Partition
2. Get Informix DDL – Convert to DB2
3. Analyze DDL for Tablespace Structure
4. Create DDL for Tablespaces
5. Analyze Tablespaces for Memory Structure
6. Create Memory DDL
7. Create Instance
8. Create Database
9. Update / Run DDL
10. Load Data
11. 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 roundrobin, 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
 With
 With
 With


4KB pages–
8KB pages–
16KB pages
32KB pages
64GB
128GB
– 256GB
– 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
Name
Type
Contents
State
Detailed explanation:
Normal
Total pages
Useable pages
Used pages
Free pages
High water mark (pages)
Page size (bytes)
Extent size (pages)
Prefetch size (pages)
Number of containers
=
=
=
=
=
5
BIGSPACE_1
Database managed space
Any data
0x0000
=
=
=
=
=
=
=
=
=
4587520
4587120
3137520
1449600
4474560
8192
240
240
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
Name
Type
Contents
State
Detailed explanation:
Normal
Total pages
Useable pages
Used pages
Free pages
High water mark (pages)
Page size (bytes)
Extent size (pages)
Prefetch size (pages)
Number of containers
= 9
=
=
=
=
TTMP_8K
System managed space
System Temporary data
0x0000
=
=
=
=
=
=
=
=
=
18689
18689
18689
Not applicable
Not applicable
8192
240
240
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
dbserver2
0
4
dbserver2
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 <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
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 <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
BUFFPAGE – default bufferpool
 SORTHEAP –
 AVG_APPLS
 LOGFILSIZE, LOGPRIMARY,
LOGSECOND and NEWLOGPATH
 DFT_QUERYOPT – 1-9
 LOGBUFSZ

Extra Step for MultiPartition 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
Do you do a lot sorting in this
instance?…this database?
2. 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
1.
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 <Appl. Handle number>”
for all information about that query

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 <dbname> -i <filename>
-o <output file>
Example:
db2advis –d testdb –i trouble.sql –o fix.out

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 <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??
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 <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 –
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 <command>
Example: 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 <schemaname>;

Get the layout of a table:
Describe table <schemaname>.<tablename>;
Calling from ksh Script
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…


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 things…

Don’t use double quotes in DB2 !
Select * from tabname where name =
‘Bob’

DB2 does not support Directives
Datatypes

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

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

DB2 Outer join syntax is different
than Informix

DB2 is reportedly ANSI standard
and Informix is not
Join Syntax
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

Can’t use “number” syntax
Group by 1,2,3….

Forced to make case statements,
etc redundant
Group by - INFORMIX
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
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
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 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

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 Tables

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

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;
Utilities

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..”
Utilities

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
Utilities





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
Utilities


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

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)




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!