Exploiting the Advantages of SQL:1999

1. Informix User Forum 2005Moving Forward With Informix Exploiting the Advantages of SQL:1999 Dick Snoke Sr. Consulting IT Specialist / IBM Atlanta, Georgia December 8-9, 2005

2. Topics • Context • The SQL Language Standard • Distinct Types • Handling Hierarchical Data • Advanced SQL: The CASE clause • Advanced SQL: SKIP… FIRST… • Advanced SQL: Sequences • User-Defined Routines – some examples • Data Storage and Access Methods • Securing your data and DBMS • Data Federation and Replication

3. Context • Objects as a way of thinking about data • DBMS’s as a way of holding data • SQL as a way of using your data

4. The SQL Language Standard – a brief history • SQL-86, SQL-89 and SQL-92 • The beginning up to a widely supported version • Intermediate Standards • CLI-95 Call Level Interface (ODBC) • PSM-96 Stored Procedure Language • OL-98 Object Language Bindings (Java) • SQL:1999 • The immediate past standard • SQL:2003 • The current standard • SQL:2005 • The next steps along the path

5. What’s New in the SQL:1999 and SQL:2003 Standards? • Reorganized into a core and packages • Packages include • Enhanced datetime facilities • Enhanced integrity management • OLAP facilities • PSM (Persistent Stored Modules) • CLI (Call-level Interface) • Basic object support • Enhanced object support • Active database • SQL/MM (multimedia) support

6. New in SQL:1999 or SQL:2003 • Arrays • Multisets • Sequences These things are standard SQL now!!

7. Distinct Types • Examples of distinct types: • My employee id, 5A8727, as an integer • Currency, $4950.40 US, as money • An account number, AEW6978, as a character string • SQL Example: create distinct type EMP_ID as integer with comparison;

8. Why Does that Matter? • Your schema becomes understandable • Names and data types understood by the application designers • Some SQL errors are more easily detected • Integers can always be added, but should they always be added?

9. Handling Hierarchies – Objects in the database • A classic problem for relational DBMS’s • IDS allows an elegant solution • Why does this matter? • Simplify the overall application by simplifying the SQL • Let the DBMS do the set processing • Don’t code in the application what the DBMS already does well • Avoid some data movement

10. Example: An Org Chart Goyal Vella Elix 0’Connell Snoke Caldera TBH TBH Truax Jones Acosta Knox Royal Munns Woods Smith Thorne Kvidt

11. A Useful Labeling and Traversing Scheme • Use a numbering scheme to delineate the hierarchy • 1.0 is the root node • 1.x are the children of that node • x.y are the children of node x. • x.1 is less than x.2 • x.2.5 is less than x.3.0 • A set of functions that understands this numbering scheme can traverse a hierarchy.

12. Example: An Org Chart 1.0 1.1 1.2 1.3 1.1.1 1.2.1 1.3.1 1.1.2 1.2.2 1.3.2 1.1.3 1.2.3 1.3.3 1.1.4 1.2.4 1.3.4 1.1.5 1.3.5

13. The NODE type and functions • Node is a user-defined type • Functions are • isancestor, isparent, ischild, descendant • All take two nodes as input • all return Boolean values • getmember, getparent • ancestors - returns all the parents up to the root • The type and functions can be added to either DB2 or IDS

14. Hierarchy Using the Node datatype CREATE TABLE employee2 ( Serial_Number char(6) Employee_id Node, Last_Name varchar(30), …. PRIMARY KEY(Employee_Id) );

15. Queries using the node type and functions My chain of command: select name from employee a where a.Employee_id in (ancestors (select b.Employee_id from employee b where b.lastname = ‘Snoke’)) order by a.Employee_id; My immediate manager: select a.name, a.Employee_id from employee a where a.Employee_id = getparent (select b.Employee_id from employee b where b.lastname = ‘Snoke);

16. Why does this matter? • Let the database object model match the application object model. • Simplify the overall application by simplifying the SQL • Let the DBMS do the set processing • Don’t code in the application what the DBMS already does well • Avoid some data movement

17. Data Storage and Access • IDS uses its own Relational Storage Access Method (RSAM) • Other schemes may be added using the Virtual Table Interface • For data not in the DBMS • For alternative storage structures • Other indexing schemes may be added using the Virtual Index Interface • Coppereye’s indexing is just this

18. Data Storage and Access • Use MQ queues • as tables in the database • in SQL functions • See The Informix Built-in Datablade Modules User Guide for details and restrictions • An alternative to other messaging options • Example: put data from a queue into the database using • Less code to develop. • Let the DBMS do the work for you! insert into my_order_table(clob_col) VALUES(MQReadClob());

19. Why does this matter? • No need to develop messaging software • No need to write much application code to do queue operations • Still the need to USE the data. • Focus on the use of the data, not the mechanics of data movement

20. Securing Your Data • Encryption • App-DBMS traffic • Data on the disk • User Authentication • trust, • OS authentication, • a PAM • LDAP • Roles and Default Roles

21. Why Does This Matter Anyway? • Security is becoming a legal requirement. • You may be able to reduce your costs • not developing your own software • simplifying administration

22. Federation and Replication • Let the DBMS handle distributed data and distributing the data • Federation == Synonyms • Replication == Enterprise Replication (ER) • IDS now has functions for using Websphere MQ • Event publishing • Reliable delivery of data

23. Why Does This Matter Anyway? • It’s built in. • No need to purchase other software • No need to write your own software • It’s comprehensive • Covers all data types • Allows for multi-site updates • Does not require all systems available all the time. • Solves problems of • workload balancing • distribution or consolidation of data • business continuity

24. Advanced SQL – Several for the Price of One • Use CASE to perform multiple updates in one statement update staff set manager = CASE when dept = 10 and job = 'Mgr‘ then (select id from staff where job ='CEO') when dept = 15 and job != 'Mgr' then (select id from staff where job = 'Mgr' and dept = 15) when dept = 20 and job != 'Mgr' then (select id from staff where job = 'Mgr' and dept = 20) when dept = 38 and job != 'Mgr' then (select id from staff where job = 'Mgr' and dept = 38) when (dept = 15 or dept = 20 or dept = 38) and job = 'Mgr' then 210 … END ;

25. Advanced SQL – Several for the Price of One • Use CASE to do multiple counts in one pass SELECT count (CASE when score > 90 then 1 ELSE null END) as moregb90, count (CASE when score = 90 then 1 ELSE null END) as equalgb90, count (CASE when score < 70 then 1 ELSE null END) as minorgb90, count (CASE when test_nbr = 500 then 1 ELSE null END) as equalgb500 FROM test_taken;

26. Advanced SQL - SKIP… FIRST… • Not the same results!! • SKIP and FIRST processed after the result set is determined • SKIP and FIRST can be used individually too SELECT SKIP 4 FIRST 2 lname, company FROM customer ORDER BY lname; SELECT SKIP 4 FIRST 2 lname, company FROM customer ORDER BY company;

27. Advanced SQL - Sequences • Use in place of serial data types for greater control • A distinct datatype, but similar to int8 • Independent of transactions • ROLLBACK does not undo generated values • Syntax: CREATE SEQUENCE idnumbers INCREMENT BY 1000 START VALUE 1000 MAXVALUE 99000 NOCYCLE; GRANT ALTER ON idnumbers to dsnoke; INSERT INTO EMP VALUES (“Snoke”, idnumbers.NEXTVAL); SELECT first 1 idnumbers.CURRVALfrom employee;

28. User-Defined Routines • aka stored procedures • Written in the language of your choice • Used in the SQL statements of the application, triggers, etc. • Executed by the DBMS during SQL statement processing

29. Why does this matter anyway? • Make the DBMS capable of doing what you want it to do • The right operators • The right aggregates • The right predicates • Simple, understandable SQL • easier design maintenance and enhancement • Less Data Movement • No need to bring large numbers of tuples back to the application • Less data to be stored • No need to have columns for quarter, promotion, etc. • Better code maintenance • Only one function instead of many predicates to maintain.

30. UDR Examples – Specialized Calendars • Calendar periods for dates: • Fiscal quarters, retail promotions, product development schedules • Create functions that given a date return the period: CREATE FUNCTION quarter (date indate) returning int RETURN case when indate > 09/30/02 and indate < 01/01/03 then 4 when indate > 06/30/02 and indate < 10/01/02 then 3 when indate > 03/31/02 and indate < 07/01/02 then 2 when indate > 12/31/01 and indate < 04/01/02 then 1 else 0 end case; • Then use those functions to simplify your SQL: SELECT … WHERE quarter(txn_date) < 3; SELECT … WHERE promo(txn_date) = “Christmas”;

31. UDR Examples: Other Things • The NODE routines described earlier • Other datablades • Spatial • egrep bladelet • Text search • MQ messaging

32. References • Jacques’ book • Paul’s book • Melton’s books • DeveloperWorks • Redbooks

33. Questions?

34. Informix User Forum 2005Moving Forward With Informix Exploiting the Advantages of SQL:1999 Dick Snoke dsnoke@us.ibm.com Atlanta, Georgia December 8-9, 2005