Transcript CREATE --+---------------------+--------- TABLE

SQL, SPL Enhancements
Scott Pickett
WW Informix Technical Sales
For questions about this presentation contact: [email protected]
© 2013 IBM Corporation
Agenda









2
Intersect, Minus/Except Support for Set Operations
Select …. into table
4 GB SPL
Case Statement Support in SPL
Execute Into Support
Trigonometric Functions
Multiple Distinct Operators
Nulls First/Last Sorting Order
Genero
© 2013 IBM Corporation
IDS 12 - Minus, Plus & Intersect Support
© 2013 IBM Corporation
Functionality
 Extension to the existing UNION/UNION ALL SET operation.
 Same rules of UNION also applies e.g.
 Both query block should have exact same number of columns.
 Comparable data types.
 Projection clause will not have BYTE or TEXT.
4
© 2013 IBM Corporation
Functionality
 Order by should be at the end.
 Precedence will be from left to right, unless they are grouped using
parentheses.
 There are existing restrictions for UNION and they will apply to these
operators too.
5
© 2013 IBM Corporation
Intersect and Minus/Except
 Intersect returns only those rows which are returned by both queries
and results will be distinct.
 Minus/Except will returns distinct rows from the left query which are
not found in the right side query.
 All keyword is not available for intersect and minus/except.
 NULL friendly, means when comparing NULL to NULL they are
considered equal.
6
© 2013 IBM Corporation
Examples











create table t1 (col1 int);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (2);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);
insert into t1 values (4);
insert into t1 values (NULL);
insert into t1 values (NULL);
insert into t1 values (NULL);






create table t2 (col1 int);
insert into t2 values (1);
insert into t2 values (3);
insert into t2 values (4);
insert into t2 values (4);
insert into t2 values (NULL);
7
© 2013 IBM Corporation
Examples (cont’d)
 Select * from t1 intersect select * from t2;
– Returns NULL, 1,3 and 4
 Select * from t1 minus/except select * from t2;
– Returns 2
8
© 2013 IBM Corporation
Rewrite
 Select col2, col2 from t1 intersect select col1, col2 from t2;
 Rewrite:
 Select distinct *
 from (select col1, col2 from t1) t11
 where exist (select col1, col2 from (select * from t2) t22 where
t11.col1 = t22.col1 and t11.col2 = t22.col2 );
9
© 2013 IBM Corporation
Minus/Except Rewrite
 select col1, col2 from t1 minus select col1, col2 from t2;
 select distinct *
from (select col1, col2 from t1) t11
where not exists
(select *
from (select col1, col2 from t2) t22
where t11.col1 = t22.col1 and t11.col2
and t11.col2= t22.col2);
10
© 2013 IBM Corporation
SQL Enhancements – New Join Operators
 Example
– Two tables are created and loaded with data
• Includes some, but not all overlapping values
Notice there are duplicate
values in the col1 / col12
columns but not in the col2
/ col22 columns
11
© 2013 IBM Corporation
SQL Enhancements – New Join Operators
 Example (cont.)
– Test the intersect operator
Using a single column, just the common values of
both result sets are returned
12
© 2013 IBM Corporation
SQL Enhancements – New Join Operators
 Example (cont.)
– Test the intersect operator
Adding the second column to the query returns
no rows since the col2/col22 values are unique
13
© 2013 IBM Corporation
SQL Enhancements – New Join Operators
 Example (cont.)
– Test the minus operator
Returns the values that only exist in tab_1
14
© 2013 IBM Corporation
SQL Enhancements – New Join Operators
 Example (cont.)
– Test the minus operator
Adding the second column returns all tab_1
rows because the col2 / col22 values
are unique and none of the combinations
exist in tab_1
15
© 2013 IBM Corporation
Questions
16
© 2013 IBM Corporation
SQL Enhancement - SELECT INTO
© 2013 IBM Corporation
Overview
 You can now create persistent tables in IDS that can be accessed
across sessions using the result set of a SELECT statement.
 Combines the functionality of CREATE TABLE and INSERT into a
single statement.
 Supports all the Storage options of CREATE TABLE on IDS.
 You can now change the EXTENT SIZE and NEXT SIZE of an
existing table without having to unload the table first.
 Allows you to create new schema from existing tables and
populate it with only a subset of data for testing purposes.
 Supported in a Flexible Grid environment.
 Functionality supported via two different syntax enhancements:
– SELECT col1, col2 FROM sourcetab INTO newtab;
– CREATE TABLE newtab AS SELECT col1, col2 FROM sourcetab;
18
© 2013 IBM Corporation
Syntax – Permanent Tables
|-------------------------- SELECT clause --------------------------------------------
,-- STANDARD----,
|
|
>-------- INTO -------+-----------------------+--- tabname --,-----------------------------------------------------------------------,----|
|
|
|
|
‘---- RAW -----------’
‘---| Storage Options |------| Lock Mode Options |---’
19
© 2013 IBM Corporation
Syntax - CREATE TABLE … AS SELECT…
,-- STANDARD---,
|
|
CREATE --+---------------------+--------- TABLE ------- tab_name -----------------------------------------
|
|
‘-------RAW --------’
>---,---------------------------------------------------------,--------,---------------------------------------------------------------------------------------,------
|
|
‘-- ( --,--column_alias , ---,-- ) ---’
|
|
‘--| Storage Options |----| Lock Mode Options |------’
|
|
‘---------------<-----------------’
>-------------- AS --------- SELECT clause --------------------|
20
© 2013 IBM Corporation
Technical Details (1)
 Display Label (or Column Alias) used in SELECT Clause
would be used as Column Name of newly created table.
 All expressions other than simple column expressions must
have a display label (or column alias).
 Any IDS supported SELECT syntax to create a projection list
is valid in the SELECT part of the syntax.
 The type of the column in the new table would be the same as
the type of the corresponding column in the source table.
21
© 2013 IBM Corporation
Technical Details (2)
 For a non-trivial expression used in the projection list, the
column type would correspond to the return type of the
expression.
 Will not inherit any constraints, primary keys, LBAC
properties, encryption or any other special properties of the
columns that are part of the projection list of the SELECT
clause.
 Any constraint will need to be introduced with ALTER TABLE.
 Cannot be part of a sub-query.
22
© 2013 IBM Corporation
How to Create and Populate a Result Table
 Assuming “col1” is of type INTEGER, then column “fcol1” of new
table would be of type FLOAT.
 select col1::float fcol1, col2 from tab1 into stab1;

 create table stab1 (fcol1, col2)
 as select col1::float, col2 from tab1;
The above “create table .. as ..” statement can also be written as:
 create table stab1
 as select col1::float fcol1, col2 from tab1;
23
© 2013 IBM Corporation
Specify Storage Options for a Result Table (1)
select col1::float fcol1, col2
from tab1
into permtab
fragment by expression
fcol1 < 300 in dbs1,
fcol1 >=300 in dbs2;
create table permtab (fcol1, col2)
fragment by expression
fcol1 < 300 in dbs1,
fcol1 >=300 in dbs2
as
select col1::float, col2 from tab1;
24
© 2013 IBM Corporation
Specify Storage Options for a Result Table (2)
 In the following example the target table “permtab” would be
fragmented and split across the dbspaces “dbs1” & “dbs2”
select col1::float fcol1, col2
from tab1
into permtab
fragment by expression
fcol1 < 300 in dbs1,
fcol1 >=300 in dbs2;
create table permtab (fcol1, col2)
fragment by expression
fcol1 < 300 in dbs1,
fcol1 >=300 in dbs2
as
select col1::float, col2 from tab1;
25
© 2013 IBM Corporation
Specify Storage Options for a Result Table (3)
 Change the first Extent Size and/or Next Size of a table that
contains data (without unloading data):
– Specify the new sizes in SELECT INTO and create a new table.
• SELECT * FROM tab1 INTO newtab1 EXTENT SIZE 32 NEXT SIZE 32;
• OR
• CREATE TABLE newtab1 EXTENT SIZE 32 NEXT SIZE 32 AS SELECT
* FROM tab1;
–
–
–
–
–
–
26
Use ALTER TABLE to introduce any constraints/primary keys.
Create required index on the new table.
Drop index on old table.
Drop old table (“tab1”).
Rename new table (“newtab1”) to same name as old table (“tab1”).
Rename index on new table to the same as for old table.
© 2013 IBM Corporation
Questions
27
© 2013 IBM Corporation
4GB Stored Procedure Language Statements
© 2013 IBM Corporation
Overview
 The maximum length of SQL statements and SPL routines is
now 4 GB
– The actual limit may depend on the available memory on the system.
 Valid for Client SDK 3.70.xC5, JDBC 3.70.xC5 or higher.
 Perhaps writing SQL or SPL possibly so large should be
avoided for programmer maintenance issues …..
29
© 2013 IBM Corporation
Limitations
 Transient extended types, collection types are limited to 65535
 sysadmin task command length is limited to 2048
30
© 2013 IBM Corporation
Questions
31
© 2013 IBM Corporation
Case Statement in SPL
© 2013 IBM Corporation
Case Usage Overview
 In a stored procedure when one needs to take a different
branch of execution based on the value of an expression or
value of a SPL variable.
 Reduces the migration work to be done during conversion
from other database products that support CASE statements
in stored procedure language.
 It can simply programming by replacing multiple IF-ELSE IF
statement blocks with a CASE statement in SPL routines.
33
© 2013 IBM Corporation
Technical Details
 Syntax diagram for CASE statement in SPL
>>-CASE--value_expr--------------------------------------------->
(1)
>--+-ELSE--| Statement Block |-------------------------------------------------------------------------------------+-->
| .-----------------------------------------------------------------------------|
|V
(1) |
|
'---WHEN--constant_expr--THEN--| Statement Block |------+--+------------------------------------+---'
|
(1) |
'-ELSE--| Stmt Block |-------'
>--END CASE---------------------------------------------------------------------><
34
© 2013 IBM Corporation
CASE Statement Execution in SPL
 The database server evaluates the value_expr parameter
– If the resulting value matches a literal value specified in the
constant_expr parameter of a WHEN clause, the database server
executes the statement block that follows the THEN keyword in that
WHEN clause.
– If the value of the value_expr parameter matches the
constant_expr parameter in more than one WHEN clause, the
database server executes the statement block that follows the THEN
keyword in the first matching WHEN clause in the CASE statement.
 After the database server executes the statement block that
follows the THEN keyword, it executes the statement that
follows the CASE statement in the SPL routine.
35
© 2013 IBM Corporation
Technical Details (cont’d)
 If the value of the value_expr parameter does not match the
literal value specified in the constant_expr parameter of any
WHEN clause, and if the CASE statement includes an ELSE
clause, the database server executes the statement block
that follows the ELSE keyword.
 If the value of the value_expr parameter does not match the
literal value specified in the constant_expr parameter of any
WHEN clause, and if the CASE statement does not include
an ELSE clause, then the database server returns an error
with a message indicating that the specified case not defined
in the CASE statement.
36
© 2013 IBM Corporation
How to Use the CASE Statement in SPL
 CASE statement without else clause
CASE mod(r,314)
WHEN 1 THEN select tabid into r from informix.systables where tabid = 1;
WHEN 313 THEN LET r = r + 4;
WHEN 3 THEN LET r = r + 3;
END CASE;
 CASE statement with else clause
CASE r
WHEN 317 THEN select tabid into r from informix.systables where tabid = 1;
WHEN 312 THEN LET r = r + 4;
WHEN 3 THEN LET r = r + 3;
ELSE
LET r = 1;
END CASE;
37
© 2013 IBM Corporation
How to use CASE statement (cont’d)
 CASE statement with boolean value and null value
CASE state
WHEN 't'
THEN LET grade = 'a';
WHEN 'f'
THEN LET grade = 'b';
WHEN NULL THEN LET grade = 'c';
ELSE LET grade = 'd';
END CASE;
 CASE statement with multi-statement block
CASE r
WHEN 6 THEN
BEGIN
select 4 into r from informix.systables where tabid = 1;
LET r = r - 1;
END
WHEN 4 THEN LET r = r + 4;
END CASE;
38
© 2013 IBM Corporation
Questions
39
© 2013 IBM Corporation
execute into Statement
© 2013 IBM Corporation
execute into
 Informix has increased the support for, and usability of, UDRs and
the values they return
– Informix has supported execute procedure
• For UDRs that do not return a value
– as well as execute function
• For UDRS that do return value(s)
 When calling a UDR, input and potential return values are defined
with the in, out and/or inout keywords
41
© 2013 IBM Corporation
execute into
 Informix 12.10 functions that return a value can now directly
populate a program variable with the execute into syntax
– The variable has to be defined and initialized before the function is called
 Example
execute function scale_rectangles( rectid = 107, scale = 1.9 )
into new_rectang
42
© 2013 IBM Corporation
Questions
43
© 2013 IBM Corporation
Mathematical Functions
© 2013 IBM Corporation
New Mathematical Functions – Oracle Compatibility
 SIGN(num)
– Returns the sign of the input numeric value (returns integer):
• -1 for n < 0, 0 for n = 0, and 1 for n > 0
 COSH(num)
– Returns hyperbolic cosine (returns float)
 SINH(num)
– Returns hyperbolic sine (returns float)
 TANH(num)
– Returns hyperbolic tangent (returns float)
 ACOSH(num)
– Returns arc hyperbolic cosine (returns float)
 ASINH(num)
– Returns arc hyperbolic sine (returns float)
 ATANH(num)
– Returns arc hyperbolic tangent (returns float)
 LN(num)
– Alias for existing LOGN(num) function (returns float)
45
© 2013 IBM Corporation
Multiple Distinct Operators
© 2013 IBM Corporation
Multiple distinct Operators
 Example
– Legal syntax
• Applied to each predicate
select avg(unique col1), count (distinct col2)
from my_tab;
– Illegal syntax
• Nested operators
select distinct avg(distinct col1) from my_tab;
47
© 2013 IBM Corporation
Questions
48
© 2013 IBM Corporation
Nulls First/Last Sorting Order
© 2013 IBM Corporation
SQL Enhancements – nulls first / last
 When selecting and ordering a column that contains nulls, whether
the data is sorted in ascending or descending order affects where
the null values appear
– Ascending puts the nulls first
– Descending puts the nulls last
 The nulls first / nulls last keywords can be used to move the nulls to
the top or bottom of the result regardless of the sort order
50
© 2013 IBM Corporation
SQL Enhancements – nulls first / last
 Example
Default null
sort order
with the asc
keyword
51
Modified
null sort
order with
the asc
nulls last
keywords
© 2013 IBM Corporation
SQL Enhancements – nulls first / last
 Example
Default null
sort order
with the
desc
keyword
52
Modified
null sort
order with
the desc
nulls first
keywords
© 2013 IBM Corporation
SQL Enhancements – Windows XML Enhancement
 The XML4C and XSLT4C libraries have been ported to Windows 64
enabling XML functionality on that port.
53
© 2013 IBM Corporation
Questions
54
© 2013 IBM Corporation
Genero 2.41 - Enhancements
© 2013 IBM Corporation
IBM Informix Genero v2.41
 eGA’ed in December, 2012
 Genero Application Server and Genero Web Client
– HTML5 snippets and CSF (aka javascript) update
– Enhanced support for Genero features
 Feature enhancements worth taking a note
–
–
–
–
–
–
The Layout
Store Settings and Table Columns UI
Traditional mode (to ease migration from i4GL)
TTY attributes and Genero 4ST
Modal windows and Popup menus
Picture flow
•
•
•
•
56
Better touch based (tablets and phones) ergonomics
Android4 compatibility
iOS5 (iPad and iPhone) compatibility
Hybrid Application templates for iOS and Android
© 2013 IBM Corporation
IBM Informix Genero v2.41
 Enhancements to Genero Studio
– BAM has new templates
– Provide additional modeling options to define the functionality provided
by each form, such as add/modify/delete.
– Data access code and constraints will be centralized at the database
meta-schema level.
– Operations on forms will be published as Web services to provide a
high level access on data by program
 Defect fixes for all components
– GWS, FGL, GDC, GRV, GAS, GWC, GST, GRE
57
© 2013 IBM Corporation
2013 IIUG Informix Conference
April 21 – 25, 2013
San Diego Marriott Mission Valley - San Diego, California, USA
The Board of Directors of the International Informix Users Group (IIUG) announces the IIUG 2013 Informix
Conference, to be held at the San Diego Marriott Mission Valley on April 21 - 25, 2013.
Early Bird registration only $750(USD) until January 17, 2013.
All paid attendees receive Thursday tutorials included!
For more details visit the official conference web site www.iiug2013.org
70
© 2013 IBM Corporation
Questions
71
© 2013 IBM Corporation
Logo
72
© 2013 IBM Corporation
Logo
73
© 2013 IBM Corporation