Introduction to the new mainframe Chapter 12 Understanding

  • Slides: 31
Download presentation
Introduction to the new mainframe Chapter 12 Understanding database managers on z/OS © Copyright

Introduction to the new mainframe Chapter 12 Understanding database managers on z/OS © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Chapter 12 objectives Be able to: Explain the role

Introduction to the new mainframe Chapter 12 objectives Be able to: Explain the role of DB 2 in online transaction processing. List common DB 2 data structures. Compose simple SQL queries to run on z/OS. Give an overview of application programming with DB 2. What the IMS components are The structure of the IMS DB subsystem © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Key terms in this chapter bind DBMS EXPLAIN modified

Introduction to the new mainframe Key terms in this chapter bind DBMS EXPLAIN modified source SPUFI SQL SYSADM view © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe DB 2 Concepts: Data Structures © Copyright IBM Corp.

Introduction to the new mainframe DB 2 Concepts: Data Structures © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Schema structures User-defined Data Type (UDT) User-defined Function (UDF)

Introduction to the new mainframe Schema structures User-defined Data Type (UDT) User-defined Function (UDF) Triggers Large Object (LOB) Stored Procedure © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe System Structure Catalog & Directory: stores ALL DB 2

Introduction to the new mainframe System Structure Catalog & Directory: stores ALL DB 2 information Buffer Pool Active and Archive Logs Bootstrap data set (BSDS) © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe DB 2 for z/OS Architecture DB 2 Address Spaces

Introduction to the new mainframe DB 2 for z/OS Architecture DB 2 Address Spaces • System Service address space (SSAS) • Database Service address space (DBAS) • Internal Resource Lock Manager (IRLM) DB 2 Attachment Facilities • CICS • IMS • TSO © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Invoke SQL onneed z/OS: to. SPUFI First you create

Introduction to the new mainframe Invoke SQL onneed z/OS: to. SPUFI First you create the output file (if not existing) Select option 1 to enter SPUFI © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Invoke SQL on z/OS: SPUFI (CONT…) Enter the input

Introduction to the new mainframe Invoke SQL on z/OS: SPUFI (CONT…) Enter the input and output dataset, if they are not yet in place. Change the member of the PDS, if you want to enter a new SQL Defaults are set to NO from YES. © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Invoke SQL on z/OS: SPUFI (CONT…) Enter the SQL

Introduction to the new mainframe Invoke SQL on z/OS: SPUFI (CONT…) Enter the SQL statement you want to execute. Press F 3 to return to the previous screen (to execute the SQL). © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Invoke SQL on z/OS: SPUFI (CONT…) When you get

Introduction to the new mainframe Invoke SQL on z/OS: SPUFI (CONT…) When you get back to this screen, the “edit input” is put to “*”. Press ENTER to execute the SQL and to see the output. © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Invoke SQL on z/OS: SPUFI (CONT…) F 8 brings

Introduction to the new mainframe Invoke SQL on z/OS: SPUFI (CONT…) F 8 brings the rest of the results on your screen © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Invoke SQL on z/OS: SPUFI (CONT…) © Copyright IBM

Introduction to the new mainframe Invoke SQL on z/OS: SPUFI (CONT…) © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Application Programming: the flow © Copyright IBM Corp. ,

Introduction to the new mainframe Application Programming: the flow © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Get the access path: EXPLAIN ALLEXPLAIN SET QUERYNO =1

Introduction to the new mainframe Get the access path: EXPLAIN ALLEXPLAIN SET QUERYNO =1 SELECT EMPNO, LASTNAME FROM EMP WHERE LASTNAME = 'MILLER'; -The query is NOT executed -The access path is placed in userid. PLAN_TABLE, if it exists © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Other development options ODBC JAVA SQLJ JDBC XML Column

Introduction to the new mainframe Other development options ODBC JAVA SQLJ JDBC XML Column Access XML Collection Access © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Managing DB 2: System Administration Installation System Object Management

Introduction to the new mainframe Managing DB 2: System Administration Installation System Object Management System and Disaster Recovery Monitoring System Performance © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Managing DB 2: Database Administration Creation & Management of

Introduction to the new mainframe Managing DB 2: Database Administration Creation & Management of DB 2 Objects Execution of Utilities: • Data Organization • Backup & recovery • Data Consistency Commands © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Functions of the IMS database manager A DBMS provides:

Introduction to the new mainframe Functions of the IMS database manager A DBMS provides: • • Multiple-user access to a single copy of data Integrity for all updates Minimal hardware and OS access method dependencies Reduced data redundancy © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Implementation of IMS Databases Depending on user' requirements Technologies

Introduction to the new mainframe Implementation of IMS Databases Depending on user' requirements Technologies : • IMS DB or DL/I or DL 1 or Full Function Database • IMS DEDB or Data Entry DB or Fast Path Database • IMS Main storage database (MSDB) • IBM DB 2 Database Recovery Control (DBRC) © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Structure of IMS DB Subsystem © Copyright IBM Corp.

Introduction to the new mainframe Structure of IMS DB Subsystem © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Databases used by IMS: Database basics Access paths Normalization

Introduction to the new mainframe Databases used by IMS: Database basics Access paths Normalization within IMS • Unique entities • 1 occurrence only • No many-to-many relationships © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Databases used by IMS: DB Model Sequence to access

Introduction to the new mainframe Databases used by IMS: DB Model Sequence to access the segments © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Databases used by IMS: DB model Additional access paths

Introduction to the new mainframe Databases used by IMS: DB model Additional access paths to segments • Logical relationships • Secondary indices © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Application programming overview Program is subroutine of IMS region

Introduction to the new mainframe Application programming overview Program is subroutine of IMS region controller • Needs a program specification block (PSB) • Uses services: • • Send/receive message from terminals Access db Issue IMS commands Issue IMS service calls e. g. Checkpoint calls, Sync call © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Program Structure © Copyright IBM Corp. , 2005. All

Introduction to the new mainframe Program Structure © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe IMS & the World Wide Web Message flow in

Introduction to the new mainframe IMS & the World Wide Web Message flow in IMS transaction © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe IMS & the World Wide Web Message flow between

Introduction to the new mainframe IMS & the World Wide Web Message flow between Web Browser & Web Server © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe flow IMS transaction & Web Server CGI Programs IMS

Introduction to the new mainframe flow IMS transaction & Web Server CGI Programs IMS Message & the World Wide Web © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Summary The relational database is the predominant approach to

Introduction to the new mainframe Summary The relational database is the predominant approach to data organization in today's business world. IBM’s DB 2 implements such relational principles as primary keys, referential integrity, a language to access the database (SQL), nulls, and normalized design. In a relational database, the most fundamental structure is the table with columns and rows. © Copyright IBM Corp. , 2005. All rights reserved.

Introduction to the new mainframe Summary (continued) The only way to access the data

Introduction to the new mainframe Summary (continued) The only way to access the data in DB 2 databases is with SQL. On the mainframe, SPUFI is a tool used to enter SQL statements. The DBRM performs a bind process that determines the access path and stores this executable SQL code in a package. SQL can handle both static and dynamic statements, and EXPLAIN can be used to find out what access path the optimizer chose for the SQL. © Copyright IBM Corp. , 2005. All rights reserved.