exploiting the advantages of sql 1999 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Exploiting the Advantages of SQL:1999 PowerPoint Presentation
Download Presentation
Exploiting the Advantages of SQL:1999

Loading in 2 Seconds...

  share
play fullscreen
1 / 34
zizi

Exploiting the Advantages of SQL:1999 - PowerPoint PPT Presentation

102 Views
Download Presentation
Exploiting the Advantages of SQL:1999
An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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