SQL for IBM i : a database modernization guide

From the Inside FlapYou may be familiar with SQL, the IBM DB2 for i data-access language. But you might not know that you can use SQL to modernize your IBM i database, to make it more efficient and easier to access. Rafael Victória-Pereira knows firsthand how to use SQL's database modernization power—and in SQL for IBM i: A Database Modernization Guide, he shares this knowledge, explaining how you can use SQL's capabilities to modernize and enhance your IBM i database.The book takes you on a journey: the modernization of a fictional university's database. You'll start with a database that's basically a set of physical files (typical of many RPG applications), then transform it into a proper SQL database with advanced features that can be used both internally (by an RPG application, for instance) or externally (by a .NET or Java Web application).As you progress, you'll get plenty of easy-to-understand examples and exercises to help you grasp and master the concepts, so you can use them on the job. The author's down-to-earth style and solid knowledge of SQL on IBM i make for an absorbing, highly educational journey into IBM i database modernization.From the Back CoverAfter you've read this book, you will know how to:* Modernize your database, freeing it of its RPG/COBOL shackles* Migrate your database definition from DDS (the native way) to SQL's DDL (the "modern" way)* Use advanced SQL concepts, such as user-defined functions, common table expressions, and more* Make your database easier to access* Open your database to non-native applications by moving business rules to the database, automating data consistency checks, and more* Use the system catalog to better understand the database and its componentsBook code and examples: http://mcpressonline.com/Look%20Inside%20the%20Book/5167_CODE.zip Front Cover Title Page Copyright Contents Acknowledgments Why You Need This Book How Can You Get More Out of the Database? 1: Meet the UMADB: Our Example Database The Students Table The Courses Table The Teachers Table The Classes Table The Grades Table Just a Few Tables, and So Many Problems In This Chapter, You’ve Learned ... 2: A Data Manipulation Language Basics Recap Using the BETWEEN and IN Predicates Joining Tables The JOIN You’ve Been Using Without Realizing: INNER JOIN Start Joining Tables Left and Right “Just Get Me Everything”: FULL JOIN A Join Summary A Handful of Column Functions The COUNT Function Finding the Minimum and Maximum Values of a Column Sums and Averages Made Easy The Shape-shifting CAST Function Aggregating Data with GROUP BY The Two Flavors of INSERT Vanilla INSERT: Plain, Simple, and Kind of Boring Strawberry (or Whatever Your Favorite Ice Cream Flavor Is) INSERT Data Adjustments with UPDATE DELETE: A Blessing and a Curse In This Chapter, You’ve Learned ... 3: A Data Definition Language Basics Recap Converting UMADB’s Physical Files to SQL Tables CREATE TABLE Fundamentals Moving a Step Closer to a Real SQL Table Adding a Unique, Self-Managed Key Providing Proper Default Values for Optional and Audit-Related Columns Time for Some Practice A First Step Toward Easier Data Access: Creating a Few Simple Views Why Do You Need Views? How Do You Create Views? Repeat After Me: A View Is Not a Logical File The Missing Piece: SQL Indexes A Side Note: Why Is It So Important to Keep the Source Statements Safe? Exploring a Few CREATE INDEX Options In This Chapter, You’ve Learned ... 4: Making the Database User-Friendly Using Longer Table and Column Names Time for Some Practice Hiding Database Complexity Using the Same Table Twice in a Query Using Sub-queries Using EXISTS to Omit Records on a SELECT Statement Checking Database Consistency with the Exception Join Time for Some (More) Practice Using CASE to Return “Friendlier” Column Values Using CASE in the ORDER BY Clause Using a Secondary Table to Produce Customizable Sorting Sequences In This Chapter, You’ve Learned ... 5: Tidying Up the Database A Quick Refresher on Relational Databases Relational Databases and Their Use of Keys Primary Keys Unique Keys Foreign Keys Table Relationship Types Understanding Identifying and Non-Identifying Relationships Referential Integrity Entity Relationship Diagram Notation Solving UMADB’s Database Problems Through Normalization Data Anomalies Some Boring, Yet Important, Math Jargon Introducing the Normal Forms, Academic Version Normalizing UMADB to 3NF Data Modeling with Visual Paradigm Introducing Visual Paradigm Community Edition Time for Some Practice Creating the New and Improved UMADB Time for Some (More) Practice Data Modeling Is Fun, but Where’s the Code? Migrating the Data to the New, Improved UMADB Populating the Teacher Ranks Table Populating the Departments Table Creating the Persons Data Populating the Teachers Table Populating the Students Table Populating the Courses Table Creating the Class Definitions Data Creating the Classes per Year Data Creating the Class Enrollment per Year Data Populating the Sort Sequence Table Time for a Bit More Practice Hiding the Database Complexity (Again) by Recreating the Views In This Chapter, You’ve Learned ... 6: Introducing Data-Centric Programming and SQL/PSM A Call from the Dean’s Office Introducing Data-Centric Programming An SQL/PSM Crash Course Variable Declaration Variable Assignment Flow Control Loop Control Calling Other SQL Routines, FOR Loops, and Cursors Dynamic SQL Compound Statements Error Handling In This Chapter, You’ve Learned ... 7: Creating and Using Stored Procedures SPs, UDFs, and Triggers: When to Use Each Analyzing the SP’s Structure What’s in a Name? Procedure Parameters Procedure Properties Procedure Options Procedure Body Creating Your First Stored Procedure Time for Some Practice Data-returning Stored Procedures Creating Data-returning Stored Procedures Creating More Complex Data-returning Stored Procedures Time for Some (More) Practice Using Data-returning Stored Procedures How to Use the Result Sets Returned by a Stored Procedure And There’s Still Time for a Little More Practice In This Chapter, You’ve Learned ... 8: Exploring User-Defined Functions Function Versus Procedure: The RETURNS Property Pick Your UDF Flavor A Couple of Simple UDFs to Get You Started Using Flow Control in a UDF UDF Overloading Testing the Get_Student_Id UDF Time for Some Practice A More Complex UDF: Calculating the Student GPA Creating a Generic “Translation” Table ... ... And a Couple of Functions to Access It Finding a Place for the Credit Hours per Class Creating the Calculate_GPA UDF Creating a Simple UDTF Anatomy of a UDTF Using a UDTF in a SELECT Statement Writing More Complex UDTFs Scheduled User Profile Review UDTF Using a UDTF’s Data in Another UDTF: Introducing the User Review Report In This Chapter, You’ve Learned ... 9: Making a Trigger-Happy Database Introducing Database Triggers What Triggers a Trigger? Why Use Triggers? SQL Triggers’ Advantages SQL Trigger Mechanics A Simple Trigger Example Testing Your First Trigger Time for Some Practice Exploring More Complex Trigger Scenarios Using Triggers for Data Validation Testing the Teacher Salary Update Check Trigger Using Triggers for Auditing Testing the Trigger Using Triggers to Compose and Fill Columns Using INSTEAD OF Triggers to Supercharge Views Use Triggers with Care Triggers Are Like Salt In This Chapter, You’ve Learned ... 10: Moving Business Validations to the Database Re-introducing DDL Constraints Unique Constraints Referential Constraints Check Constraints Enforcing More Complex Sets of Business Rules Keeping the Teachers’ Salaries in Check Tidying Up the Personnel (Records) Enforcing the “Informal” Integrity of the Courses Table In This Chapter, You’ve Learned ... 11: Exploring the Database Catalog Information Data About Data and the System Catalog Where It All Starts: Tables In This Chapter, You’ve Learned ... 12: Parting Gifts The Personnel Contact Information Update Scenario Creating a Table Based on a SELECT Statement Anatomy of the MERGE Statement A Quick-and-Dirty UPDATE-only Alternative to MERGE Common Table Expressions CTE Anatomy A Simple CTE Example Another CTE Example: Recreating the Student Grades Info View Never Stop Learning! Index This book aims to give IBM i technical users basic to intermediate SQL knowledge and tools they can use to get more out of the IBM i database. The book can be useful to veteran IBM i programmers, who have RPG and COBOL roots, system administrators looking to get more information out of their IBM i system, or even Java and .NET developers who need to “talk” to IBM i database. The author provides comprehensive examples and exercises to help readers understand and practice what they have learned.