slide1 l.
Skip this Video
Loading SlideShow in 5 Seconds..
Informix and DB2 an SQL Perspective PowerPoint Presentation
Download Presentation
Informix and DB2 an SQL Perspective

Loading in 2 Seconds...

play fullscreen
1 / 38

Informix and DB2 an SQL Perspective - PowerPoint PPT Presentation

  • Uploaded on

Informix and DB2 an SQL Perspective. Informix and DB2 SQL. Bob Carts Senior Data Engineer, SAIC Keith Gibert Software Engineer, SAIC Our Story – How We Spent our Summer Vacation. We were told to convert to DB2 version 8.1

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

PowerPoint Slideshow about 'Informix and DB2 an SQL Perspective' - kiaria

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
informix and db2 sql
Informix and DB2 SQL

Bob Carts

Senior Data Engineer, SAIC

Keith Gibert

Software Engineer, SAIC

our story how we spent our summer vacation
Our Story – How We Spent our Summer Vacation
  • We were told to convert to DB2 version 8.1
  • No DB2 Experience or Training
  • Data Warehouse Application
  • 900 GB of data
  • Using Informix XPS version 8.31
  • 684 pieces of ETL code
  • ETL code SQL, KSH, PERL
  • No stored procedures or triggers
  • Certified Informix DBA (Bob)
  • Certified DB2 DBA (Bob)
  • WAIUG Board of Directors (Bob)
  • Work for SAIC (Science Applications International Corporation)
  • 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
scope of presentation
Scope of Presentation
  • 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, dbaccess, SQL
  • Included:
    • Some basic SQL differences
  • Not Included:
    • DBA Stuff, Internals,Embedded SQL, Triggers and Stored Procedures
some things we learned support
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
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, 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 “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 monitoring9
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 monitoring10
Some Things We Learned – Monitoring
  • Veiw 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 tablespace:
    • INFORMIX: onstat –d/-D/-t/-T
    • DB2: list tablespace show detail
interactive access
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
  • 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;


  • 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
  • Dbaccess [dbname] <<EOF > stdout 2>stderror

Select bla bla bla;


  • Db2 –tvl <logfilename> <<EOF >

Connect to [dbname];

Select bla bla bla


a few little things
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 things16
A few little things…
  • Don’t use double quotes in DB2 !

Select * from tabname where name = ‘Bob’

  • DB2 does not support Directives
  • 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 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
Join Syntax
  • DB2 Outer join syntax is different than Informix
  • DB2 is reportedly ANSI standard and Informix is not
join syntax20
Join Syntax

INFORMIX: Select, 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.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
  • Can’t use “number” syntax

Group by 1,2,3….

  • Forced to make case statements, etc redundant
group by informix
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
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

  • 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
  • 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
  • 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 tables27
UnLogged Tables
  • When creating a table must specify that logging can be turned off

Create table

(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 activate not logged initially;

Insert into …


  • If anything goes wrong, boom no useable table!
  • 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;

  • Another load example:

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


  • Import is slow
  • 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
  • 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
  • Both databases allow retrieval of error messages from the command line
    • INFORMIX: finderr -217
    • DB2: db2 ? SQL0203
error messages informix
Error Messages - INFORMIX

-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

not defined in a table and does not appear as a statement local variable

(SLV) definition. Check that the column name or SLV name and the names of

the selected tables are spelled as you intended.

If all names are spelled correctly, you are not using the right tables,

the database has been changed, or you have not defined the SLV. If the

name not found is a reference to a column, that column might have been

renamed or dropped. If the name not found represents an SLV and you

defined the SLV in the statement, make sure that the SLV definition

appears before all other references to that SLV name.

This error message can also appear during the execution of an ALTER TABLE

statement when the engine tries to update views that depend on the table.

error messages db2
Error Messages – DB2

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

Explanation:The column "<name>" is used in the statement and there is more than one possible column to which it could refer. This could be the result of:

two tables specified in a FROM clause that have columns with the same name

the ORDER BY clause refers to a name that applies to more than one column in the select list

a reference to a column from the subject table in a CREATE TRIGGER statement does not use the correlation name to indicate if it refers to the old or new transition variable.

The column name needs further information to establish which of the possible table columns it is.

The statement cannot be processed.

User Response:Add a qualifier to the column name. The qualifier is the table name or correlation name. A column may need to be renamed in the select list.

sqlcode: -203

sqlstate: 42702

informix xps version 8 x
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



Interactive access

Calling from ksh

Default permissions

Update Statistics

Code comments

Double quotes



Limiting rows returned

Join Syntax

Group by


Alter statements

Unlogged tables


Error messages

XPS differences


  • Db2 administration guide (book)
    • Online tutorials geared for certification testing
    • Porting Resources
    • Bunch of other items
  • Contact,

Presentation will be placed on WAIUG site: