Informix SQL Tips and Tricks SQL Tips and

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 3/6/2002 Slide 2

Introduction l Certified Informix DBAs l WAIUG Board of Directors l Work for SAIC l NT and UNIX (Solaris, IBM AIX) l IDS 7. 31, 9. 21, 9. 3 and XPS 8. 3 l Data Warehouse and OLTP Applications Informix 3/6/2002 Slide 3

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

Introduction l Data Warehouse Project l ETL Programming l Evolution ØC programs Ø Stored Procedures Ø SQL l Good Introduction to SQL 3/6/2002 Slide 5

New Commands (or sort of new) l Case - Useful in transforming data (7. 3 on) l decode - (7. 3 on) l NVL function - l first - Just get a few rows (7. 3 on) l middle - Just get some middle rows (XPS only) 3/6/2002 Slide 6

Case Statement l l l 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” l Linear Format case beds when < 100 then “small” when > 99 then “big” else “No Value” 3/6/2002 Slide 7

Case Statement l 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 l Example: sum(case when donotcount_flag ="0" then 0 when donotcount_flag is null then 0 else entry_count end 3/6/2002 Slide 8

Other Statements l Decode Ø can’t decode a null select manufacturer, DECODE(beertype, 1, “stout” 2, “ale” 3, “lager”, “other”) … l 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; Ø Example 2 - Fixed with NVL select trim(last_name)||", "||trim(first_name)||" "||nvl(middle_name[1], "") from staff; 3/6/2002 Slide 9

Other Statements l First (7. 3, 8. 3, 9. 2, 9. 3) l 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) l Middle (XPS only) Ø returns middle rows! 3/6/2002 Slide 10

Cleaning up Data l Delete from l Rename table l 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. 3 x Alter table drop Column/add Column 3/6/2002 Slide 11

Cleaning up Data l Cleaning up a column Ø Example: update tablename set status = “”; Ø Another Example Alter tablename drop status; Alter tablename add status; 3/6/2002 Slide 12

Testing and Test Data l Test your SQL without mangling your database l 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!! l Add a where condition that can’t be true: where 1= 2; l Set Explain On Avoid Execute; 3/6/2002 Slide 13

Testing and Test Data l 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; l 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 3/6/2002 Slide 14

Using Directives l Directives are available in IDS 9. X and XPS 8. 32 and up l 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 l Force the engine to query tables in the order you specify l Force the engine to use a certain index l Force the engine to ignore indexes l Force the engine to Join tables in a specific way l Optimize for all data l Optimize a quick response 3/6/2002 Slide 15

Using Directives: ORDERED l Forces the engine to query tables in the order you specify in the FROM clause of your SQL statement l 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 3/6/2002 Slide 16

Using Directives: INDEX / AVOID_INDEX l Forces the engine to use / ignore a specific index l 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. 3/6/2002 Slide 17

Using Directives: FULL / AVOID_FULL l Forces the engine to perform or to avoid performing a full table scan, even if an index exists. l 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. 3/6/2002 Slide 18

Using Directives: JOIN METHOD DIRECTIVES l Forces the engine to perform or to avoid performing certain join methods, such as Nested Loop or Hash Joins. l 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. 3/6/2002 Slide 19

Using Directives: OPTIMIZATION GOAL DIRECTIVES l 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. l 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. 3/6/2002 Slide 20

External Tables l External Tables (XPS only) Ø External Tables replace the HPL on 8. 3 x 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. 3/6/2002 Slide 21

External Tables l 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) 3/6/2002 Slide 22

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

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

Using Temp Tables l Example: You have a multi-join query that is taking forever to run l 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 l Worked well in XPS using dynamic indexes 3/6/2002 Slide 25

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

Generating SQL using SQL l l 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 …) 3/6/2002 Slide 27

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

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

Summary l case l decode l nvl l first l middle l directives l external tables l temp tables l making sql using sql l concatenating group by 3/6/2002 Slide 30

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