1 / 39

System i Integrated Relational Database Management System

Introduction to System i for Computer Professionals. System i Integrated Relational Database Management System. Module 8. Module Objectives. After completing this module, you should be able to: Define DB2 Universal Database for i5/OS Discuss SQL versus traditional file access terminology

randyjackson
Download Presentation

System i Integrated Relational Database Management System

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. Introduction to System i for Computer Professionals System i Integrated Relational DatabaseManagement System Module 8

  2. Module Objectives After completing this module, you should be able to: • Define DB2 Universal Database for i5/OS • Discuss SQL versus traditional file access terminology • Define the difference between system libraries and user libraries • Define a library list • Discuss physical and logical files • Define DSPPFM, DSPFD, and DSPFFD commands • Define how to create a schema • Describe an SQL catalog and SQL packages • Explain the terms seizes, locks, journaling, and integrated file system

  3. Referential integrity Stored procedures Two-phase commit System catalog Triggers Scrollable cursors Query governor C2 security Open interfaces (ODBC, DAL) DRDA Multi-language support Online backup and restore Parallel data access Recovery Optimizer SQL Optimizer DB2 UDB Structured Data Unstructured Data Documents, Spreadsheets, Multimedia Objects Integrated RDBMS • Performance • Integration • Interoperability • Security • Flexibility

  4. Terminology

  5. System Library QSYS File System Objects Program Job Queues Spool Data Areas User Libraries QTEMP LIBA LIBB LIBC QTEMP QTEMP File File File User Objects Program Program Program Job Queues Job Queues Job Queues Spool Data Areas Spool Data Areas Spool Data Areas Native Library Structure Hierarchical Structure Compare

  6. System i Objects • Object definition • Library • Object name • Object type • Finding an object • Explicitly identified • Library/ • Implicit • Object name/object type (that is, object FILEA of type *FILE) • Searches a list of libraries • Specified "Library List" scanned in sequence • Two portions • System library list • Defined through system value QSYSLIBL • User library list • Defined in QUSRLIBL or • Job description (parameter INLLIBL) • QTEMP • Temporary library exclusive to the Job

  7. Filea Fileb Filec Filed Filee Filey Filex Application Program Physical/Logical Files Collection/Database/Library Physical Files (data) selection projection union join Logical Files (INDEXES) (no data) Open Data Paths (ODP)

  8. LIBRARYX/DBFILE Header fld-1 fld-2 fld-3 fld-4 Dataspace data data data data Cursor Member-1 { Member-2 Member-n System i Unique Physical File Structure • File header • File description • Qualified file library/name • Owner • Object authority • Number of records • Field level description • Field names • Attributes • Dataspace • Actual data • Multiple file members • Not used by SQL • Index space • Index • Cursor

  9. Display Physical File Member File . . . . . . : CUSTOMERS Library . . . . : ERPDATA Member . . . . . : SECOND Record . . . . . : 1 Control . . . . . Column . . . . . : 1 Find . . . . . . . *...+....1....+....2....+....3....+....4....+....5....+....6 918374Davon A C249 North St LondonTX7521750003003700000000 833245Lee D S21B NW 135 StClay NY1304104001010000000000 123859Jenner T PPO Box 79 BrotonVT0504607001043900000000 345485James S A3 Alpine Way Helen GA3054599992398750003350 393457Peters D A13 Myrtle Dr HectorNY1484110001000000000000 323472Wang C M208 Snow PassDenverCO8022604001005875000150 889900Hoover E J787 Lake Dr SydneyMN5634250003001000000000 423438HumphreyH W59 Archer Rd SutterCA9568507002025000010000 693829Thomas A N3 Dove CircleCasperWY8260999992000000000000 593029WilliamsE D485 SE 2 Ave BangorTX7521802001002500000000 192837Lee F L5963 Oak St ForestNY1484107002048950000050 583990Abraham M T392 Mill St IslandMN5634299993050000000000 ****** END OF DATA ****** LIBRARYX/DBFILE Header fld-1 fld-2 fld-3 fld-4 Dataspace data data data Cursor Members File Members Display Physical File Member File . . . . . . : CUSTOMERS Library . . . . : ERPDATA Member . . . . . : CUSTCDT Record . . . . . : 1 Control . . . . . Column . . . . . : 1 Find . . . . . . . *...+....1....+....2....+....3....+....4....+....5....+....6 938472Henning G K4859 Elm Ave DallasTX7521750003003700000000 839283Jones B D21B NW 135 StClay NY1304104001010000000000 392859Vine S SPO Box 79 BrotonVT0504607001043900000000 938485Johnson J A3 Alpine Way Helen GA3054599992398750003350 397267Tyron W E13 Myrtle Dr HectorNY1484110001000000000000 389572Stevens K L208 Snow PassDenverCO8022604001005875000150 846283Alison J S787 Lake Dr Isle MN5634250003001000000000 475938Doe J W59 Archer Rd SutterCA9568507002025000010000 693829Thomas A N3 Dove CircleCasperWY8260999992000000000000 593029WilliamsE D485 SE 2 Ave DallasTX7521802001002500000000 192837Lee F L5963 Oak St HectorNY1484107002048950000050 583990Abraham M T392 Mill St Isle MN5634299993050000000000 ****** END OF DATA ****** Display Physical File Member File . . . . . . : CUSTOMERS Library . . . . : ERPDATA Member . . . . . : THIRD Record . . . . . : 1 Control . . . . . Column . . . . . : 1 Find . . . . . . . *...+....1....+....2....+....3....+....4....+....5....+....6 323472Wang C M208 Snow PassDenverCO8022604001005875000150 889900Hoover E J787 Lake Dr SydneyMN5634250003001000000000 423438HumphreyH W59 Archer Rd SutterCA9568507002025000010000 693829Thomas A N3 Dove CircleCasperWY8260999992000000000000 593029WilliamsE D485 SE 2 Ave BangorTX7521802001002500000000 192837Lee F L5963 Oak St ForestNY1484107002048950000050 583990Abraham M T392 Mill St IslandMN5634299993050000000000 ****** END OF DATA ******

  10. LIBRARYX/DBFILE Header fld-1 fld-2 fld-3 fld-4 Dataspace data data data Cursor Members File Information (1 of 3) • File description header • Explicit file name • Library name • File name (10 characters) • Alternative file name (20 characters) • File type • Data • Source - program code • Database attributes • File definitions • Externally described • Column (or field) level specifications • Field information stored with file • Program described • Row (or Record) level description • Field information within program(s) • File creation date • File size limits • Record length • Initial records • Size of increment • Number of increments DSPFD Command

  11. LIBRARYX/DBFILE Header fld-1 fld-2 fld-3 fld-4 Dataspace data data data Cursor Members File Information (2 of 3) • Database attributes • Number of members • Maximum • Current • Reuse deleted records • Force write to disk • File activity • Number • Opens/closes • Update/delete operations • Date of last • Change • Save • Restore • File member information • Name/creation date • Last update (date/time) • Number or records DSPFD Command

  12. File Information (3 of 3)

  13. LIBRARYX/DBFILE Header fld-1 fld-2 fld-3 fld-4 Dataspace data data data Cursor Members Record Information • Columns (field) information • Field name • Data type • Character • Numeric • Binary • Length • Buffer positions • Usage • Input/output • Column heading DSPFFD Command (Green Screen)

  14. Data Description Specifications Object Type = *FILE STRSQL Two Interfaces Library Native SQL Collection/Database *LIB

  15. Native I/O (record) i5/OS Query Component Query APIs DB2 for i5/OS Optimizer Data Management Database Accessing the Database Client Access File Transfer ODBC OPNQRYF Query/400 SQL/ISQLQuery Manager record-level processing set-level processing Technology Independent Machine Interface

  16. Create Library/Collection SQL Collection

  17. CREATE Table C catalog for Collection-A catalog for Collection-B Collection-A Collection-B SQL Collection Objects • Create SQL collection • STRSQL • CREATE COLLECTION • System i Systemwide SQL catalog • Equivalent to SQL information schema • View of information in each collection (20 objs) • QSQJRN *JRN • Journal information • QSQJRN0001 *JRNRCV • Journal receiver of database changes • SYSTABLES *FILE • Tables and views • SYSCOLUMNS *FILE • Column attributes • SYSINDEXES *FILE • Indexes • SYSVIEWS *FILE • View Definition • SYSKEYS *FILE • Index keys • And so forth Systemwide catalog B Logical Views A

  18. View SQL Schemas

  19. Access Plan Bind Optimization Database SQL Optimizer • Why? • Improved database performance • Identify technique used to implement query • Selects most efficient technique • Access plan • Internal structure defining method to process SQL statement • Created during SQL parse, syntax check SQL Statement

  20. SQL Statement Access Plan Bind Optimization Database SQL Optimizer • Database access methods: • Dataspace scan • Parallel pre-fetch • Key selection • Key positioning • Hashing and so forth • Optimal access method selection based on: • Implementation cost • SQL statement • Current state of the database • Indexes/file sizes

  21. Access Plan SQL Statement Bind Optimization Database Access Plan SQL Statement Database SQL Packages System i Extended Dynamic SQL Support • Control structures used to execute SQL statements • Access plans stored in *SQLPKG object • Improves performance • i5/OS commands : CRTSQLPKG, DLTSQLPKG • Dynamic re-optimization • File size • New/deleted indexes First time SQL Package Open Data Path (ODP) Validate Subsequent

  22. File Names • Long file names • Supported/used by • SQL • Applications ported to System i • New System i applications • Referred to as “alternative file name" • System i "System" filename limited to 10 chars • Query on SYSTABLES • SYS_TNAME --> NAME

  23. Add Schemas

  24. Database Tables

  25. Locked/Update LF LF LF LF Physical File Seized/Maintenance Seizes/Locks • Ensures data integrity • Locks • Record/row level • Prevents record/row from being updated simultaneously • When read for update • Seizes • File/object level • Index maintenance • Data consistency • Controlled below machine interface • Increases with logical files • Parallel processing minimizes impact

  26. Application Program read updates additions deletions Filea Fileb Filec Filed Filee Database changes • The change is added to the attached journal receiver. • The journal receiver is written to auxiliary storage. • The record is written to the main storage copy of the file. Journal (*JRN) detach Journal Receiver-1 (*JRNRCV) Journal Receiver-3 (*JRNRCV) Journal Receiver-2 (*JRNRCV) tape backup Journaling

  27. record change journal deposit aaaa bbbb cccc dddd aaaa bbbb bbbb cccc cccc dddd (before & after) Journaled Files • Journaling at file level • Optional • Application may require it • Support for logical/business transactions • Commitment control • May be implemented for recoverability • CRTJRNRCV • Creates Journal Receiver • Specify ASP and size • CRTJRN • Creates Journal • Journal Receiver name • Receiver management options • STRJRNPF command • Journal image • Writes entire record to Journal Receiver • *After change • *Before/after change

  28. 0001;John;23456;2345;Highway 52N;Rochester MN:: Data Organization 0001 John 23456 2345 Highway 52N Rochester MN 0002 James 12345 25 Frontage St Greybull WY 0003 Douglas 98765 1259 Another St Jackson WY 0004 Thomas 56789 2345 By Lane Rapid City SD Native Database Files 0002;James;12345;25;Frontage St;Greybull WY:: 0003;Douglas;98765;Another St;Jackson WY:: 0004;Thomas;56789;By Lane;Rapid City SD:: Byte-Stream Files

  29. DB2 UDB Enhancements Complex objects • LOBs • Large objects stored in table columns • DataLinks • Downloadable to PC applications Name Address Picture John Doe 123 First Ave Name Address Picture John Doe 123 First Ave URL File System

  30. Integrated File System ROOT (User) QDLS QSYS.LIB QOpenSys QLANSrv Etc. QFileSvr400 (User Libraries) user objects

  31. IFS

  32. Network Drives • IFS accessed through MS Windows browser • Map network drive through Network Neighborhood support • (System i NetServer support) • For example, \\<iSeries-name>\root • \root must be shared

  33. WRKLNK Access IFS through "5250" session Work with Object Links (WRKLNK) Type choices, press Enter. Object . . . . . . . . . . . . . '*' Object type . . . . . . . . . . *ALL *ALL, *ALLDIR, *ALRTBL... Detail . . . . . . . . . . . . . > *EXTENDED *PRV, *NAME, *BASIC... Display option . . . . . . . . . > *ALL *PRV, *USER, *ALL Bottom F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display F24=More keys

  34. Work with Object Links Directory . . . . : / Type options, press Enter. 3=Copy 4=Remove 5=Next level 7=Rename 8=Display attributes 11=Change current directory ... Opt Object link Type Attribute Text LRP SYMLNK->DIR opt DIR part.csv STMF QJAVA DIR QLANSrv DIR QNetWare DIR QNTC DIR QOpenSys DIR QOPT DIR t STMF tmp DIR QSYS.LIB DIR PROD System Library More... Parameters or command ===> F3=Exit F4=Prompt F5=Refresh F9=Retrieve F12=Cancel F17=Position to F22=Display entire field F23=More options WRKLNK Display

  35. Directory: /* Position to : Record : 1 of New File : 2=Edit 4=Delete File 5=Display 6=Path Size 9=Recursive Delete Opt Name Size Owner Changed Used QOpenSys *DIR QSYS 01/13/05 08:31 01/13/05 08:31 QDLS *FLR QDOC QSYS.LIB *LIB QSYS 03/22/05 11:36 03/22/05 11:36 QOPT *DDIR QDFTOWN QFileSvr.400 *DDIR QSYS 01/31/05 10:58 01/31/05 10:58 QNTC *DDIR QDFTOWN 01/31/05 10:58 03/22/05 13:55 dev *DIR QSYS 01/13/05 08:53 01/13/05 08:53 home *DIR QSYS 01/13/05 08:31 01/17/05 09:24 tmp *DIR QSYS 03/18/05 18:12 03/18/05 18:12 etc *DIR QSYS 01/13/05 08:53 01/17/05 09:24 usr *DIR QSYS 01/13/05 08:31 01/17/05 09:25 QIBM *DIR QSYS 01/13/05 10:57 03/22/05 13:41 QSR *DIR QSECOFR 03/22/05 10:44 03/22/05 10:44 bin *DIR QSYS 01/13/05 10:57 01/17/05 09:25 More... F3=Exit F5=Refresh F12=Cancel F16=Sort F17=Position to F22=Display entire field EDTF (streamfile) Display

  36. Edit File (EDTF) Type choices, press Enter. Stream file, or . . . . . . . . Data base file . . . . . . . . . > QCLSRC Name Library . . . . . . . . . . . > QGPL Name, *LIBL, *CURLIB File member . . . . . . . . . . > QSTRUP Name, *FIRST Bottom F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display F24=More keys EDTF (database file) Display

  37. System i Commands • DSPFD (Display file description) • Shows file information • DSPFFD (Display file field description) • Shows field attributes • DSPDBR (Display database relations) • Shows dependent files • DSPPFM (Display physical file member) • Displays data in the file (table) • EDTF (Edit streamfile in IFS) • Stream files • EDTF STMF('/') • Review/delete directory structures • Display/edit/delete stream file • Database files • EDTF FILE(<library>/<file>) MBR(<mbr>)

  38. Exercise 3 Database

  39. Module Summary Having completed this module, you should be able to: • Define DB2 Universal Database for i5/OS • Discuss SQL versus traditional file access terminology • Define the difference between system libraries and user libraries • Define a library list • Discuss physical and logical files • Define DSPPFM, DSPFD, and DSPFFD commands • Define how to create a schema • Describe an SQL catalog and SQL packages • Explain the terms seizes, locks, journaling, and integrated file system

More Related