Graeme Birchall + Rodney Krick: DB2 SQL Cookbook

Graeme Birchall + Rodney Krick: DB2 SQL Cookbook

Old dead link: http://mysite.verizon.net/Graeme_Birchall/index.html, Graeme Birchall: DB2 SQL Cookbook

DB2 SQL Cookbook Content

Table of Contents

    Introduction
    1. Document history
    2. Quick find
        2.1. Index of Concepts
        2.2. Summary Table
    3. Introduction to SQL
        3.1. Syntax Diagram
        3.2. SQL Comments
        3.3. Statement Delimiter
        3.4. SQL Components
        3.5. SELECT Statement
        3.6. SQL Predicates
        3.7. CAST Expression
        3.8. VALUES Statement
        3.9. CASE Expression
        3.10. Miscellaneous SQL Statements
        3.11. Unit-of-Work Processing
    4. Data Manipulation Language
        4.1. Insert
        4.2. Update
        4.3. Delete
        4.4. Select DML Changes
        4.5. Merge
    5. Compound SQL
        5.1. Introduction
    6. Column Functions
        6.1. Column Functions, Definitions
    7. OLAP Functions
        7.1. Introduction
        7.2. The Bad Old Days
    8. Scalar Functions
        8.1. Introduction
        8.2. Sample Data
        8.3. Scalar Functions, Definitions
        8.4. User Defined Functions
    9. Table Functions
    10. Useful User-Defined Functions
        10.1. Julian Date Functions
        10.2. Get Prior Date
        10.3. Get Prior Month
        10.4. Get Prior Week
        10.5. Check Data Value Type
        10.6. Hash Function
    11. Order By, Group By, and Having
        11.1. Order By
    12. Joins
        12.1. Why Joins Matter
        12.2. Sample Views
        12.3. Join Syntax
    13. Sub-Query
        13.1. Sample Tables
        13.2. No Keyword Sub-Query
        13.3. SOME/ANY Keyword Sub-Query
        13.4. EXISTS Keyword Sub-Query
        13.5. NOT EXISTS Keyword Sub-query
        13.6. IN Keyword Sub-Query
        13.7. NOT IN Keyword Sub-Queries
        13.8. Correlated vs. Uncorrelated Sub-Queries
        13.9. Which is Faster
        13.10. Multi-Field Sub-Queries
        13.11. Nested Sub-Queries
        13.12. Usage Examples
        13.13. True if TEN Match
        13.14. True if ALL match
        13.15. False if no Matching Rows
    14. Union, Intersect, and Except
        14.1. Union & Union All
        14.2. Except, Except All & Minus
    15. Materialized Query Tables
        15.1. Introduction
        15.2. Db2 Optimizer Issues
        15.3. Select Statement
        15.4. Optimizer Options
        15.5. Organizing by Dimensions
    16. Identity Columns and Sequences
        16.1. Identity Columns
        16.2. Rules and Restrictions
        16.3. Rules
        16.4. Syntax Notes
        16.5. Identity Column Examples
        16.6. Usage Examples
        16.7. Altering Identity Column Options
        16.8. Gaps in Identity Column Values
        16.9. Find Gaps in Values
        16.10. Sequences
        16.11. Usage Examples
        16.12. Multi-table Usage
        16.13. Counting Deletes
        16.14. Identity Columns vs. Sequences - a Comparison
        16.15. Roll Your Own
        16.16. Support Multi-row Inserts
        16.17. Design Comments
    17. Temporary Tables
        17.1. Introduction
        17.2. Single Use in Single Statement
        17.3. Multiple Use in Single Statement
        17.4. Common Table Expression
        17.5. Insert Usage
        17.6. Full-Select
        17.7. Full-Select in FROM Phrase
        17.8. Table Function Usage
        17.9. Full-Select in SELECT Phrase
        17.10. INSERT Usage
        17.11. UPDATE Usage
        17.12. Declared Global Temporary Tables
        17.13. Tablespace
        17.14. Do NOT use to Hold Output
    18. Recursive SQL
        18.1. How Recursion Works
        18.2. Introductory Recursion
        18.3. Clean Hierarchies and Efficient Joins
    19. Triggers
        19.1. Trigger Types
        19.2. Action Type
        19.3. Trigger Examples
        19.4. Before Row Triggers - Set Values
        19.5. Before Row Trigger - Signal Error
        19.6. After Row Triggers - Record Data States
        19.7. After Statement Triggers - Record Changes
        19.8. Tables After DML
    20. Protecting Your Data
        20.1. Sample Application
        20.2. Customer Balance Table
        20.3. Enforcement Tools
        20.4. Customer-Balance Table
        20.5. US-Sales Table
        20.6. Conclusion
    21. Retaining a Record
        21.1. Schema Design
        21.2. Table Design
        21.3. Triggers
        21.4. Views
        21.5. Limitations
        21.6. Multiple Versions of the World
        21.7. Summary
    22. Using SQL to Make SQL
        22.1. Export Command
        22.2. Export Command Notes
        22.3. SQL to Make SQL
    23. Running SQL Within SQL
        23.1. Introduction
        23.2. Generate SQL within SQL
        23.3. Make Query Column-Independent
        23.4. Business Uses
        23.5. Db2 SQL Functions
        23.6. Function and Stored Procedure Used
        23.7. Different Data Types
        23.8. Usage Examples
        23.9. Efficient Queries
        23.10. Java Functions
        23.11. Query Logic
        23.12. Java Logic
        23.13. Update Real Data using Meta-Data
    24. Fun with SQL
        24.1. Creating Sample Data
        24.2. Other Fun Things
        24.3. Convert Character to Numeric
        24.4. Convert Number to Character
        24.5. Decimal Input
        24.6. Normalize Denormalized Data
        24.7. Denormalize Normalized Data
        24.8. Transpose Numeric Data
        24.9. Reversing Field Contents
        24.10. Fibonacci Series
        24.11. Business Day Calculation
        24.12. Query Runs for "n" Seconds
        24.13. Function to Pause for "n" Seconds
        24.14. Sort Character Field Contents
        24.15. Calculating the Median
        24.16. Converting HEX Data to Number
        24.17. Endianness
    25. Quirks in SQL
        25.1. Trouble with Timestamps
        25.2. Using 24 Hour Notation
        25.3. No Rows Match
        25.4. Dumb Date Usage
        25.5. RAND in Predicate
        25.6. Getting "n" Random Rows
        25.7. Summary of Issues
        25.8. Date/Time Manipulation
        25.9. Use of LIKE on VARCHAR
        25.10. Comparing Weeks
        25.11. Db2 Truncates, not Rounds
        25.12. CASE Checks in Wrong Sequence
        25.13. Division and Average
        25.14. Date Output Order
        25.15. Ambiguous Cursors
        25.16. Multiple User Interactions
        25.17. Check for Changes, Using Trigger
        25.18. Check for Changes, Using Generated TS
        25.19. Other Solutions - Good and Bad
        25.20. What Time is It
        25.21. Floating Point Numbers
        25.22. DECFLOAT Usage
    26. Appendix
        26.1. Db2 Sample Tables
    27. Thank you, Graeme Birchall!
        27.1. Preface Important!
        27.2. Acknowledgments
        27.3. Disclaimer & Copyright
        27.4. Tools Used
        27.5. Book Binding
        27.6. Author / Book
        27.7. Preface
        27.8. Book Binding
    Index

pro et con : Analyse – Reengineering – Migration

pro et con : Analyse – Reengineering – Migration

Whitepaper

  • “ARNO-Projekt – Herausforderungen und Erfahrungen in einem großen, industriellen Software-Migrationsprojekt”[204 KB]
  • ”Methoden und Werkzeuge für die Software-Migration”[222 KB]

Wissenswertes zum Unternehmen

Das inhabergeführte Unternehmen wurde 1994 von Wissenschaftlern der Informatik-Fakultät der TU Chemnitz gegründet. Ziel war es, mit wissenschaftlichen Technologien zur Compilertechnik und eigenentwickelten Software-Werkzeugen kommerzielle Migrationsprojekte zu realisieren. Wir besitzen mehr als 20 Jahre Erfahrung auf diesem Informatikspezialgebiet. Durch gemeinsame Forschungsprojekte mit Universitäten werden unsere Technologien und Werkzeuge ständig modernisiert. Die Forschungsergebnisse werden publiziert.

Erfolgreiche BS2000-Migration bei Amadeus Germany

Es hat wirklich geklappt. Am 30.04.2008, 23.55 Uhr wurde bei Amadeus Germany die letzte BS2000-Anlage abgeschaltet.

[Zusammenarbeit Amadeus – pro et con] Vorausgegangen war ein in seiner Komplexität wohl einmaliges Migrationsprojekt. Während der 3-jährigen Projektlaufzeit wurden Tausende von SPL-Programmen und SDF-Prozeduren mit mehreren Millionen Lines Of Code (LOC) automatisch nach C++ bzw. Perl unter UNIX konvertiert. Das hochperformante Filehandling-System auf BS2000 migrierte Amadeus werkzeugunterstützt in relationale Oracle-Datenbanken, der DCAM- Monitor wurde durch openUTM auf UNIX-Seite ersetzt. Die Zielsysteme laufen in einem nahezu 7*24-Stunden-Betrieb und bearbeiten in Spitzenzeiten ca. 500 Benutzertransaktionen pro Sekunde (TA/s), dies entspricht rund 1.000 technischen TA/s.

The COBOL for the Java Platform – isCOBOL Technology from Veryant

The COBOL for the Java Platform – isCOBOL Technology from Veryant

COBOL to Java Compiler

Develop in a familiar ANSI-compliant COBOL environment while deploying on any platform supporting the Java Virtual Machine
The isCOBOL Compiler is a platform agnostic, ANSI-compliant COBOL compiler that generates object-orientated code which efficiently runs on any platform that supports a Java Runtime Environment (JRE) version 1.4.2 or greater. Because the isCOBOL Compiler is written 100% in Java, one COBOL compiler can be used to develop, deploy and test on a wealth of platforms including AIX, HP-UX, Linux, Solaris, Windows and mainframe systems.

Highlights
· 100% portable COBOL Compiler, written 100% in Java
· Compliant with ANSI standards, supporting modern and legacy COBOL dialects
· Object-orientated; includes native support for Unicode and JavaBean graphical controls
· Automatically translates ESQL to JDBC; no ESQL pre-compiler required
· Provides flexible access to Indexed file systems and RDBMS sources, as well as simultaneous access to both sources from a single program