Transcript Informix SQL Tips and Tricks

Informix
SQL Tips and Tricks
SQL Tips and Tricks
Bob Carts
Senior Data Engineer, SAIC
[email protected]
Warren Donovan
Senior DBA, SAIC
[email protected]
3/6/2002
Slide 2
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
3/6/2002
Slide 3
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?
3/6/2002
Slide 4
Introduction

Data Warehouse Project

ETL Programming

Evolution
C
programs
 Stored Procedures
 SQL

Good Introduction to SQL
3/6/2002
Slide 5
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)
3/6/2002
Slide 6
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”
3/6/2002
Slide 7
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
3/6/2002
Slide 8
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;
3/6/2002
Slide 9
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!
3/6/2002
Slide 10
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
3/6/2002
Slide 11
Cleaning up Data

Cleaning up a column
 Example:
update tablename
set status = “”;
 Another
Example
Alter table tablename
drop status;
Alter table tablename
add status;
3/6/2002
Slide 12
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;
3/6/2002
Slide 13
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
3/6/2002
Slide 14
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
3/6/2002
Slide 15
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
3/6/2002
Slide 16
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.
3/6/2002
Slide 17
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.
3/6/2002
Slide 18
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.
3/6/2002
Slide 19
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.
3/6/2002
Slide 20
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.
3/6/2002
Slide 21
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)
3/6/2002
Slide 22
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
);
3/6/2002
Slide 23
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;
3/6/2002
Slide 24
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
3/6/2002
Slide 25
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
ping

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


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

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
010211494 0001 04240 10
CENTRAL MAINE
010211494 0001 04240 70
CENTRAL MAINE
 First Try
select id, subid, zip5, 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
 Second Try-Wrong Name!
select id, subid, zip5, (min)name
from storenames
group by 1,2,3
id
subid zip5
010211494 0001 04240
(min)
CENTRAL MAINE PARTS
 New info, best single name associated with
select id, subid, zip5, min(type||name)
from storenames
group by 1,2,3
lowest type
id
subid zip5 (min)
010211494 0001 04240 10CENTRAL MAINE SALES CTR

Can strip off the “10” later using [3,43]
3/6/2002
Slide 29
Summary

case

decode

nvl

first

middle

directives

external tables

temp tables

making sql using sql

concatenating group by
3/6/2002
Slide 30
Suggestions

Submit your tips ([email protected],
[email protected] )

Will be placed on WAIUG site

Indexed by “How do I”?

Thank You !!!
3/6/2002
Slide 31