Informix SQL Tips and Tricks

Informix SQL Tips and Tricks

SQL Tips and Tricks Bob Carts Senior Data Engineer, SAIC robert.carts@saic.com Warren Donovan Senior DBA, SAIC warren.donovan@saic.com

Introduction • Certified Informix DBAs • WAIUG Board of Directors • Work for SAIC • NT and UNIX (Solaris, IBM AIX) • IDS 7.31, 9.21, 9.3 and XPS 8.3 • Data Warehouse and OLTP Applications Informix

Introduction • Some Tips/Syntax work on specific releases • Be careful with your data! • Many other techniques in shell script and stored procedures not included here • What release are you using?

Introduction • Data Warehouse Project • ETL Programming • Evolution • C programs • Stored Procedures • SQL • Good Introduction to SQL I have to change the C program Again?!

New Commands (or sort of new) • Case - Useful in transforming data (7.3 on) • decode - (7.3 on) • NVL function - • first - Just get a few rows (7.3 on) • middle - Just get some middle rows (XPS only)

Case Statement • Generic or Linear • Can be nested and contain subqueries, functions • Can be contained within functions • Result must be a common datatype • Finds first true statement and then stops select hospital, case when beds < 100 then “small” when beds > 99 then “big” else “No Value” • Linear Format case beds when < 100 then “small” when > 99 then “big” else “No Value”

Case Statement • Example: case when MOD("11-30-2001"-(enterdate),30))/30)+1 > 1 then ((servicesraw)/(0.993712-(1.046021*EXP(-0.362649* POW((("11-30-2001"- (enterdate)-MOD("11-30-2001"- (enterdate),30))/30)+1,0.925358))))) else null end • Example: sum(case when donotcount_flag ="0" then 0 when donotcount_flag is null then 0 else entry_count end

Other Statements • Decode • can’t decode a null select manufacturer, DECODE(beertype, 1, “stout” 2, “ale” 3, “lager”, “other”) … • NVL function Select applicant, NVL(age, “age not available”)… • Example 1 - If middle name is null then entire result is null select trim(last_name)||", "||trim(first_name)||" "|(middle_name[1],"") from staff; • Example2 - Fixed with NVL select trim(last_name)||", "||trim(first_name)||" "||nvl(middle_name[1],"") from staff;

Other Statements • First (7.3, 8.3, 9.2, 9.3) • Select first 10 * from bigtable; • Can’t say into temp/scratch table • Can’t use in subquery or select clause of insert statement • Can’t uses as embedded select statement expression • Can use order by (first 10 after sort) • Middle (XPS only) • returns middle rows!

Cleaning up Data • Delete from • Rename table • Truncate • The TRUNCATE TABLE <tablename> command quickly drops all data in a table and resets the extents. A quick way to clear out a table, is equivalent to dropping and rebuilding a table: it not only instantly drops all the data, but actually resets all of the tables extents at the same time! • WARNING: this command cannot be rolled back! Available in 8.3x Alter table drop Column/add Column

Cleaning up Data • Cleaning up a column • Example: update tablename set status = “”; • Another Example Alter table tablename drop status; Alter table tablename add status;

Testing and Test Data • Test your SQL without mangling your database • Issue a “begin work;” statement, then run your code, then issue a “rollback;” statement • Only works with logged databases!! • Only works for code that doesn’t have commits !! • Always make sure you have a backup of your data!! • Add a where condition that can’t be true: where 1= 2; • Set Explain On Avoid Execute;

Testing and Test Data • Table is huge, just want a few rows for test data • Create testtable • Unload to “filename” Select first 100 * from bigtable; • Load from filename insert into testtable; • No test data is available • Alter statement to add the new columns and populate using the default clause • Then write another alter to remove the default. It leaves the test data behind

Using Directives • Directives are available in IDS 9.X and XPS 8.32 and up • Directives force the database engine to run the query the way you tell it to: your directives will only be as good as your knowledge of the data • Force the engine to query tables in the order you specify • Force the engine to use a certain index • Force the engine to ignore indexes • Force the engine to Join tables in a specific way • Optimize for all data • Optimize a quick response

Using Directives: ORDERED • Forces the engine to query tables in the order you specify in the FROM clause of your SQL statement • Example: SELECT --+ORDERED c.x,c.y,b.z FROM a,b,c WHERE c.x=b.n AND b.z=a.z; Will force the query to scan table a first, table b second and table c third, which would be especially beneficial if tables a and b were both small reference tables

Using Directives: INDEX / AVOID_INDEX • Forces the engine to use / ignore a specific index • Example: SELECT {+ORDERED, INDEX(a y), AVOID_INDEX(b n)} c.x,c.y,b.z FROM a,b,c WHERE c.x=b.n AND b.z=a.z; Will force the query to scan in the order in the from clause, and to use the index on the a.y column, and to ignore indexes on the b.n column.

Using Directives: FULL / AVOID_FULL • Forces the engine to perform or to avoid performing a full table scan, even if an index exists. • Example: SELECT {+ORDERED, INDEX(a y), AVOID_INDEX(b n), FULL(c)} c.x,c.y,b.z FROM a,b,c WHERE c.x=b.n AND b.z=a.z; Will force the query to scan in the order in the from clause, to use the index on the a.y column, to ignore indexes on the b.n column and to perform only full table scans on table c.

Using Directives: JOIN METHOD DIRECTIVES • Forces the engine to perform or to avoid performing certain join methods, such as Nested Loop or Hash Joins. • Example: SELECT {+ORDERED, INDEX(a y), AVOID_INDEX(b n), FULL(c), USE_HASH(c/BUILD)} c.x,c.y,b.z FROM a,b,c WHERE c.x=b.n AND b.z=a.z; Will force the query to scan in the order in the from clause, to use the index on the a.y column, to ignore indexes on the b.n column and to perform only full table scans on table c. Furthermore, on the first join, table c will be used as the table from which the hash index is built for the join with table b, which will be probed.

Using Directives: OPTIMIZATION GOAL DIRECTIVES • Forces the engine to query the tables in such a way as to produce either the full result set as fast as possible (default) or to get the first rows as quickly as possible. • Example: SELECT {+ORDERED,FIRST_ROWS} c.x,c.y,b.z FROM a,b,c WHERE c.x=b.n AND b.z=a.z; Will force the query to scan in the order in the from clause and to perform the rest of the operations in such a way as to get the first rows of the result set out as quickly as possible. The method may differ significantly from the method optimized to get ALL_ROWS as quickly as possible.

External Tables • External Tables (XPS only) • External Tables replace the HPL on 8.3x systems. Essentially, allows you to create a table that you can query with SQL, but is not located in a dbspace: instead, the table points to a flatfile • Advantages: extremely fast data load / unload, you can perform SQL against the data flatfiles when loading. • Disadvantages: if you insert data into the external table, it immediately overwrites all the data in the table (ie: your flatfiles), cannot update the data, cannot update statistics, so if your SQL query joins it to another table, be sure to use DIRECTIVES.

External Tables • External Tables (XPS only) basic syntax: CREATE EXTERNAL TABLE <tablename> <define table> *OR* SAMEAS <target tablename> USING ( DATAFILES(location of files), FORMAT <delimited or fixed>, DELIMITER “!”, REJECTFILE “<location of rejectfile>”, EXPRESS or DELUXE (mode)

External Tables • External Tables (XPS only) basic syntax example: create external table cap_rateext sameas cap_rate using ( datafiles( "disk:1:/bigfs/cap_rate/cap_rate.unl" ), delimiter '|', rejectfile "/informix/current_version/dump/cap_rate.%c", maxerrors 10, express );

Using Temp Tables • Need to create table structures dynamically for temporary use Select * from permanent_table where 1=0 into temp tmptab with no log • Then load the tables with the data you need to evaluate load from “filename” insert into tmptab;

Using Temp Tables • Example: You have a multi-join query that is taking forever to run • Reduce the number of initial joins and put the results in a temp table, then use the temp table to join to the remaining tables • Worked well in XPS using dynamic indexes

Generating SQL using SQL • Example: 500 table database needs to have a new datetime column added to every table! • Use a query against the systables table to generate the sql syntax to perform the alter statement. • Careful with long, 128 character names and wra ping • Simple example shown, you can do most anything limited only by your understanding of the system tables • generate similar stored procedures and triggers

Generating SQL using SQL • The Generating Query: output to addcolumn.sql without headings select "alter table "||tabname, "add chg_dte date;" from systables where tabid > 99 and tabtype = "T"; • The result (addcolumn.sql) alter table code_typ_dm add chg_dte date; . alter table lookup_dm add chg_dte date; (and so on …)

Group by • Task to create a table with id, subid, zip5 and name from a wider table to lookup names • Multiple names exist, need to get only one id subid zip5 type name 010211494 0001 04240 46 CENTRAL MAINE SERVICE 010211494 0001 04240 10 CENTRAL MAINE MOTORS CTR 010211494 0001 04240 70 CENTRAL MAINE PARTS • First Try select id, subid, zip5, name from storenames group by 1,2,3 # ^ # 294: The column (name) must be in the GROUP BY list.

Group by • Second Try-Wrong Name! select id, subid, zip5, (min)name from storenames group by 1,2,3 id subid zip5 (min) 010211494 0001 04240 CENTRAL MAINE PARTS • New info, best single name associated with lowest type select id, subid, zip5, min(type||name) from storenames group by 1,2,3 id subid zip5 (min) 010211494 0001 04240 10CENTRAL MAINE SALES CTR • Can strip off the “10” later using [3,43]

Summary • case • decode • nvl • first • middle • directives • external tables • temp tables • making sql using sql • concatenating group by

Suggestions • Submit your tips (robert.m.carts@saic.com, warren.donovan@saic.com ) • Will be placed on WAIUG site • Indexed by “How do I”? • Thank You !!!

Informix SQL Tips and Tricks