Informix and DB 2 an SQL Perspective 1

Informix and DB 2 an SQL Perspective 1 -22 -2004

Informix and DB 2 SQL Bob Carts Senior Data Engineer, SAIC robert. m. carts@saic. com Keith Gibert Software Engineer, SAIC myron. k. gibert. sr@saic. com 1 -22 -2004 Slide 2

Our Story – How We Spent our Summer Vacation l We were told to convert to DB 2 version 8. 1 l No DB 2 Experience or Training l Data Warehouse Application l 900 GB of data l Using Informix XPS version 8. 31 l 684 pieces of ETL code l ETL code SQL, KSH, PERL l No stored procedures or triggers 1 -22 -2004 Slide 3

Introduction l Certified Informix DBA (Bob) l Certified DB 2 DBA (Bob) l WAIUG Board of Directors (Bob) l Work for SAIC (Science Applications International Corporation) l Windows and UNIX (Solaris, IBM AIX, HP-UX) l IDS 7. 31, 9. 21, 9. 3, XPS 8. 31, DB 2 8. 1 l Data Warehouse and OLTP Applications 1 -22 -2004 Slide 4

Scope of Presentation l l Goal: To provide basic information on differences between Informix and DB 2 SQL to help you get started in evaluating, planning or executing a conversion Assumption: Ø You l are familiar with Informix, dbaccess, SQL Included: Ø Some l basic SQL differences Not Included: Ø DBA Stuff, Internals, Embedded SQL, Triggers and Stored Procedures 1 -22 -2004 Slide 5

Some Things We Learned - Support l l l Make friends with DB 2 Developers in Toronto because the DB 2 help desk does not answer SQL questions DB 2 SQL assistance is available for $ Informix Help Desk Does answer SQL questions 1 -22 -2004 Slide 6

Some Things We Learned – Documentation l l DB 2 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, DB 2 Technical Support, Product Manuals The manuals we use most: Ø SQL reference Volumes 1 and 2 Ø Data Movement Utilities Guide and Reference 1 -22 -2004 Slide 7

Some Things We Learned – Monitoring l Informix “onstat” commands make for easy monitoring l While monitoring tools are available in DB 2, they can be awkward l Onstat type monitoring commands are on the list to be added to DB 2 in a future release 1 -22 -2004 Slide 8

Some Things We Learned – Monitoring l l l Determine which processes are running: Ø INFORMIX: Onstat –g ses/sql/act/ath Ø DB 2: list applications show detail View a specific process: Ø INFORMIX: onstat –g ses <PID> Ø DB 2: get snapshot for application agentid <PID> Kill a process: Ø INFORMIX: onmode –z <PID> Ø DB 2: force application ‘(PID)’ or force application all 1 -22 -2004 Slide 9

Some Things We Learned – Monitoring l Veiw the database configuration: Ø INFORMIX: onstat –c Ø DB 2: get database configuration and/or get database managers configuration (get db cfg / get db mgr cfg) l View available tablespace: Ø INFORMIX: Ø DB 2: onstat –d/-D/-t/-T list tablespace show detail 1 -22 -2004 Slide 10

Interactive Access l l DBACCESS – Psuedo GUI, Menu bar driven DB 2 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 Ø Db 2 initially requires an explicit connect Ø Informix implicitly connects when using dbaccess 1 -22 -2004 Slide 11

DB 2 CLP l Several ways to execute commands Ø db 2 <command> Ø Example: l db 2 connect to mydb You can also use interactive mode db 2 –t Connect to mydb; Select col 1, col 2 From mytable; Quit; 1 -22 -2004 Slide 12

DB 2 CLP l You can execute OS commands within DB 2 CLP ! Cp file 1 file 2 l Get a list of databases: List active databases; l Get a list of columns: List tables [for schema <schemaname>; l Get the layout of a table: Describe table <schemaname>. <tablename>; 1 -22 -2004 Slide 13

Calling from ksh Script Dbaccess [dbname] <<EOF > stdout 2>stderror Select bla bla; EOF l Db 2 –tvl <logfilename> <<EOF > Connect to [dbname]; Select bla bla EOF l 1 -22 -2004 Slide 14

A few little things… l l Default Permissions Ø Informix: Public has permissions by default Ø DB 2: public does not Updating Statistics (different syntax) Runstats on <schema>. <table> with distribution And indexes all shrlevel change; l Code Comments Ø DB 2 does support the 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 1 -22 -2004 Slide 15

A few little things… l Don’t use double quotes in DB 2 ! Select * from tabname where name = ‘Bob’ l DB 2 does not support Directives 1 -22 -2004 Slide 16

Datatypes l l l DB 2 does not support implicit casting Explicitly cast all data types in expressions Example: Create table bob. tabname (col 1 integer, col 2 char(10), col 3 char(3))… Insert into tabname values (null, ‘bob’, null) --informix Insert into tabname values (cast(null as integer), ‘bob’, cast(null as char)) 1 -22 -2004 Slide 17

Limiting Number of Rows Returned/ Optimize for Number of Rows l Informix: Select first 100 ssn from people; DB 2: Select ssn from people Fetch first 100 rows only; l Optimize for a particular number of rows (db 2 only) Db 2: Select ssn from people Optimize for 20 rows; 1 -22 -2004 Slide 18

Join Syntax l l DB 2 Outer join syntax is different than Informix DB 2 is reportedly ANSI standard and Informix is not 1 -22 -2004 Slide 19

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 = ‘DB 2101’ DB 2: 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 = ‘DB 2101’ 1 -22 -2004 Slide 20

Group by l Can’t use “number” syntax Group by 1, 2, 3…. l Forced to make case statements, etc redundant 1 -22 -2004 Slide 21

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 1 -22 -2004 Slide 22

Group by – DB 2 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 1 -22 -2004 Slide 23

Having l l Syntax available in DB 2 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 ); 1 -22 -2004 Slide 24

Alter Statements l l Alter capabilities are limited in DB 2 Ø Can’t drop a column Ø Can’t change a datatype for a column We of course used the alter – drop in our Informix Code! 1 -22 -2004 Slide 25

Un. Logged Tables l l Using Unlogged databases in Informix is straight forward Using Unlogged tables in db 2 version 7. 2 is Ø Awkward Ø Temporary Ø Dangerous Ø Still l l Possible Db 2 version 8. 1 is less disastrous Basic problem is auto rollback makes table permanently unavailable, must recreate or restore 1 -22 -2004 Slide 26

Un. Logged Tables l When creating a table must specify that logging can be turned off Create table bob. xyz (Col 1 char(2)) In tablespace 123 index in indexspace 456 Not logged initially; l 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; l If anything goes wrong, boom no useable table! 1 -22 -2004 Slide 27

Utilities l DB 2 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; 1 -22 -2004 Slide 28

Utilities l 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) messages messagefile. msg tempfiles path $WORKDIR replace INTO bob. xyz NONRECOVERABLE; l Import is slow 1 -22 -2004 Slide 29

Utilities l l l 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; l Used to strip out quotes and leading zeros 1 -22 -2004 Slide 30

Utilities l l Getting the ddl Informix: dbschema Dbschema –d databasename outputfilename. out l DB 2: db 2 look Db 2 look –d databasename –e > outputfilename. out l l Both have many options Both have usage built in, just type command 1 -22 -2004 Slide 31

Error Messages l Both databases allow retrieval of error messages from the command line Ø INFORMIX: finderr -217 Ø DB 2: db 2 ? SQL 0203 1 -22 -2004 Slide 32

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. 1 -22 -2004 Slide 33

Error Messages – DB 2 SQL 0203 NA 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 1 -22 -2004 Slide 34

INFORMIX XPS (Version 8. x) l l DB 2 does not have the external table feature, must up import, export and load utilities DB 2 requires explicit indexes to perform adequately DB 2 does not have the join update/batch update feature (a subselect must be used) DB 2 does not support truncate command 1 -22 -2004 Slide 35

Summary l Support l l Documentation l Limiting rows returned Join Syntax l Monitoring l Group by l Interactive access l Having l Calling from ksh l Alter statements l Default permissions l l Update Statistics l Unlogged tables Utilities l Code comments l Error messages l Double quotes l XPS differences l Directives l Resources l Datatypes 1 -22 -2004 Slide 36

Resources l Db 2 administration guide (book) l http: //www. ibm. com/developerworks/db 2/ Ø Online tutorials geared for certification testing Ø Porting Resources Ø Bunch of other items 1 -22 -2004 Slide 37

Contact l Contact robert. m. carts@saic. com, myron. k. gibert. sr@saic. com Presentation will be placed on WAIUG site: www. iiug. org/~waiug 1 -22 -2004 Slide 38