1 / 31

Chapter 12 Understanding database managers on z/OS

Chapter 12 Understanding database managers on z/OS. Chapter 12 objectives. Be able to: Explain the role of DB2 in online transaction processing. List common DB2 data structures. Compose simple SQL queries to run on z/OS. Give an overview of application programming with DB2.

chesna
Download Presentation

Chapter 12 Understanding database managers on z/OS

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 12 Understanding databasemanagers on z/OS

  2. Chapter 12 objectives • Be able to: • Explain the role of DB2 in online transaction processing. • List common DB2 data structures. • Compose simple SQL queries to run on z/OS. • Give an overview of application programming with DB2. • What the IMS components are • The structure of the IMS DB subsystem

  3. bind DBMS EXPLAIN modified source SPUFI SQL SYSADM view Key terms in this chapter

  4. DB2 Concepts: Data Structures

  5. Schema structures • User-defined Data Type (UDT) • User-defined Function (UDF) • Triggers • Large Object (LOB) • Stored Procedure

  6. System Structure • Catalog & Directory: stores ALL DB2 information • Buffer Pool • Active and Archive Logs • Bootstrap data set (BSDS)

  7. DB2 for z/OS Architecture • DB2 Address Spaces • System Service address space (SSAS) • Database Service address space (DBAS) • Internal Resource Lock Manager (IRLM) • DB2 Attachment Facilities • CICS • IMS • TSO

  8. First you need to create the output file (if not existing) Invoke SQL on z/OS: SPUFI Select option 1 to enter SPUFI

  9. 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.

  10. Invoke SQL on z/OS: SPUFI (CONT…) Enter the SQL statement you want to execute. Press F3 to return to the previous screen (to execute the SQL).

  11. 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.

  12. Invoke SQL on z/OS: SPUFI (CONT…) F8 brings the rest of the results on your screen

  13. Invoke SQL on z/OS: SPUFI (CONT…)

  14. Application Programming: the flow

  15. EXPLAIN ALL SET QUERYNO = 1 SELECT EMPNO, LASTNAME FROM EMP WHERE LASTNAME = 'MILLER'; Get the access path: EXPLAIN • The query is NOT executed • The access path is placed in userid.PLAN_TABLE, if it exists

  16. Other development options • ODBC • JAVA • SQLJ • JDBC • XML • XML Column Access • XML Collection Access

  17. Managing DB2: System Administration • Installation • System Object Management • System and Disaster Recovery • Monitoring System Performance

  18. Managing DB2: Database Administration • Creation & Management of DB2 Objects • Execution of Utilities: • Data Organization • Backup & recovery • Data Consistency • Commands

  19. 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

  20. Implementation of IMS Databases • Depending on user' requirements • Technologies : • IMS DB or DL/I or DL1 or Full Function Database • IMS DEDB or Data Entry DB or Fast Path Database • IMS Main storage database (MSDB) • IBM DB2 • Database Recovery Control (DBRC)

  21. Structure of IMS DB Subsystem

  22. Databases used by IMS: Database basics • Access paths • Normalization within IMS • Unique entities • 1 occurrence only • No many-to-many relationships

  23. Databases used by IMS: DB Model • Sequence to access the segments

  24. Databases used by IMS: DB model • Additional access paths to segments • Logical relationships • Secondary indices

  25. 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

  26. Program Structure

  27. IMS & the World Wide Web • Message flow in IMS transaction

  28. IMS & the World Wide Web • Message flow between Web Browser & Web Server

  29. Message flow IMS transaction & Web Server CGI Programs IMS & the World Wide Web

  30. Summary • The relational database is the predominant approach to data organization in today's business world. • IBM’s DB2 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.

  31. Summary (continued) • The only way to access the data in DB2 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.

More Related