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

Loading...

PPT – Everything You Ever Wanted to Know About Migrating From Informix to DB2 *But were afraid to ask PowerPoint presentation | free to download - id: 13d44a-YzdmN



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

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

Description:

Bob Carts. Everything You Ever Wanted to Know About Migrating from Informix to DB2. Bob Carts ... robert.m.carts_at_saic.com. J. Warren Donovan. Senior Software ... – PowerPoint PPT presentation

Number of Views:270
Avg rating:3.0/5.0
Slides: 100
Provided by: dono1
Learn more at: http://www.waiug.org
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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 DB2But 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_at_saic.com
  • J. Warren Donovan
  • Senior Software Engineer, SAIC
  • warren.j.donovan_at_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
  • IncludedHOW to migrate
  • Not IncludedWHY 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
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

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
Whats 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
Warrens Setup Order
  • 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

14
Warrens DB2 Migration Order
  • 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

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

17
Creating Tables
  • Basically, all the same data types
  • One table one tablespace
  • Must specify index location during create table
    statement
  • If youll 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 ReadyTime 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
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

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'13107
    2) 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
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

38
DB2 Bufferpool Basics
  • Comes with a default IBMDEFAULTBP
  • Can create different buffer pools, with different
    page sizes, for different tablespaces and data
  • IBM doesnt 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
    tablespacesthis 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
Lets git it on!
  • Now that we know what Bufferpools, Tablespaces
    and Tables we needlets install and configure
    DB2!
  • But theres a few things well 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
Differences in Install and Initial Configuration
  • INFORMIX
  • Onconfig
  • Sqlhosts
  • Informix.rc
  • DB2
  • Database Manager Configuration (DBM Config)
  • Database Config (DB Config)
  • .rhosts
  • Db2set

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 ltparamgt ltvaluegt
  • 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
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

51
Create Database Script
  • create database nhl_mart on /db2ins07/db2ese
  • catalog tablespace managed by system using
    ('/nhl_mart/syscat/nhl_sys')

52
DB Config
  • One per database
  • Cannot change with editor
  • db2 get db cfg for ltdbnamegt
  • To update on a single partition server
  • db2 update db cfg for ltdbnamegt using ltparamgt
    ltvaluegt
  • To update on a multi partition server
  • db2_all db2 update db cfg for ltdbnamegt using
    ltparamgt ltvaluegt

53
DB Config Parameters
  • BUFFPAGE default bufferpool
  • SORTHEAP
  • AVG_APPLS
  • LOGFILSIZE, LOGPRIMARY, LOGSECOND and NEWLOGPATH
  • DFT_QUERYOPT 1-9
  • LOGBUFSZ

54
Extra Step for Multi-Partition Setup
  • If doing a multi-partition install, you will want
    to setup Partition Groupssince you probably
    dont 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)

55
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

56
SHEAPTHRES Major DB2 Memory Considerations
  • 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

57
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

58
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!

59
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 ltAppl.
    Handle numbergt for all information about that
    query

60
Some Get Snapshot Output
  • The output is much too extensive to review
    entirely, but heres some interesting stuff
    thats in it
  • Dynamic SQL statement text
  • Sort Overflows
  • Rows Read, Rows Written
  • Bufferpool Data Logical Reads

61
Some Cool Tools
  • Materialized Query Tables MQTs
  • Index Advisor db2advis
  • Db2shema - db2look
  • GETDISTRIB Checking your Data Distribution

62
Whats 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.

63
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

64
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 ltdbnamegt -i ltfilenamegt -o ltoutput
    filegt
  • Example
  • db2advis d testdb i trouble.sql o fix.out

65
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!

66
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

67
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 lttablenamegt ltfieldgt
  • Example getdistrib employee empno
  • Returns 1) Partition Number 2) Rows
  • 1 2
  • ----------- -----------
  • 1 151967
  • 2 138988
  • 3 193551
  • 4 162090

68
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

69
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 !

70
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

71
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

72
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

73
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 ltPIDgt
  • DB2 get snapshot for application agentid ltPIDgt
  • Kill a process
  • INFORMIX onmode z ltPIDgt
  • DB2 force application (PID) or force
    application all

74
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

75
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

76
DB2CLP
  • Several ways to execute commands
  • db2 ltcommandgt
  • Example db2 connect to mydb
  • You can also use interactive mode
  • db2 t
  • Connect to mydb
  • Select col1, col2
  • From mytable
  • Quit

77
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 ltschemanamegt
  • Get the layout of a table
  • Describe table ltschemanamegt.lttablenamegt

78
Calling from ksh Script
  • Dbaccess dbname ltltEOF gt stdout 2gtstderror
  • Select bla bla bla
  • EOF
  • Db2 tvl ltlogfilenamegt ltltEOF gt
  • Connect to dbname
  • Select bla bla bla
  • EOF

79
A few little things
  • Default Permissions
  • Informix Public has permissions by default
  • DB2 public does not
  • Updating Statistics (different syntax)
  • Runstats on ltschemagt.lttablegt 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

80
A few little things
  • Dont use double quotes in DB2 !
  • Select from tabname where name Bob
  • DB2 does not support Directives

81
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))

82
Limiting Number of Rows Returned/Optimize
forNumber 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

83
Join Syntax
  • DB2 Outer join syntax is different than Informix
  • DB2 is reportedly ANSI standard and Informix is
    not

84
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
85
Group by
  • Cant use number syntax
  • Group by 1,2,3.
  • Forced to make case statements, etc redundant

86
Group by - INFORMIX
  • Select gender, state_of_birth,
  • Case when age gt 19 and age lt 31 then Young
  • when age gt 30 and age lt 46 then middle
    aged
  • when age gt 46 then Up there
  • End category
  • From employee
  • Group by 1,2,3

87
Group by DB2
  • Select gender, state_of_birth,
  • Case when age gt 19 and age lt 31 then Young
  • when age gt 30 and age lt 46 then middle
    aged
  • when age gt 46 then Up there
  • End case
  • From employee
  • Group by gender, state_of_birth,
  • Case when age gt 19 and age lt 31 then Young
  • when age gt 30 and age lt 46 then middle
    aged
  • when age gt 46 then Up there
  • End case

88
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() gt 1 )

89
Alter Statements
  • Alter capabilities are limited in DB2
  • Cant drop a column
  • Cant change a datatype for a column
  • We of course used the alter drop in our
    Informix Code!

90
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

91
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!

92
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

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

94
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

95
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

96
Utilities
  • Getting the ddl
  • Informix dbschema
  • Dbschema d databasename outputfilename.out
  • DB2 db2look
  • Db2look d databasename e gt outputfilename.out
  • Both have many options
  • Both have usage built in, just type command

97
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 "ltnamegt" is
    ambiguous.
  • Explanation The column "ltnamegt" is used in the
    statement

98
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

99
Summary
  • Yes, you too can migrate to DB2!
About PowerShow.com