1 / 21

Database Files Overview and Creation Process

Learn about physical and logical database files on the iSeries system, including how to create data physical files and tools for describing database files like DDS and SQL.

kgualtieri
Download Presentation

Database Files Overview and Creation Process

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. IBC233 Week 5

  2. Important Dates • Test 1 – today!

  3. Agenda • Database Files • Physical files • Logical files

  4. Database Files

  5. DB2 Database Files • Physical Files *FILE PF-DTA • Logical Files *FILE LF

  6. Physical Files • It is an system i file used to store data or source code. • Physical files have members. • The members contain data or source code. • Source physical files have many members eg. One for each program • Data physical files usually have 1 member (but can have more)

  7. How do we create data physical files?

  8. Creating Physical Data Files • Creating an iSeries data file is very similar to creating a program: • Write the source code (the source code will describe what the file will look like). The source code is stored in a member in a source file. • Compile the source code (this creates a *file object).

  9. Tools for Describing Database Files • Data Description Specifications (DDS) • system i language to create source code for Files • SQL (Structured Query Language

  10. Layout of a DDS Program • File level keywords • Eg. UNIQUE, Function Keys • Record format name • Shouldn’t be the same name as the object • List the fields • Name, type, size and functions • TEXT (used by DFU and DSPFFD) • COLHDG (used by Query/400) • Access Path information

  11. Item File • Write the DDS code to define a *FILE that has the following attributes: • Item Number (5 numeric – 1 digit/byte) • Also the primary key • Item Name (30 Alphanumeric) • Stocking Size (5 Alphanumeric) • In Stock Quantity (7 numeric including 2 decimals – 2 digit/byte) • Date Last Updated

  12. Unique feature of system i Files • The record description is stored with the file object (externally described file) • It can then be used by system i utilities • The record description does not have to be coded in programs that use it. • Can be viewed using DSPFD, DSPFFD

  13. Entering data • If the compile was successful, you will have a new object in your library, a physical file. • To enter data into that file, use DFU, Data File Utility • UPDDTA

  14. DFU • STRDFU, then option 5 or… • PDM option 18 • F10 to enter new records (entry mode) • F11 to change records (change mode), page up and down to find records • F23 to delete a record

  15. Viewing records • RUNQRY QRYFILE(filename) • DSPPFM filename

  16. Access Paths Allows us to sort or select/omit data

  17. Logical Files

  18. Logical Files • Resort data in a physical file • Select/Omit specific sets of data • Hide data • Join or Merge physical files together

  19. Why a logical file • A customer file is made up of customer records (1 per customer). Each customer record has fields containing unique pieces of info about a particular customere.g. customer name, address, sales territory,billing info, shipping instructions,credit information • If we want to make sure that the customer id is unique • If we want to display customer records sorted by name • If we want to select customers in a specific territory • If we want to provide a maintenance screen hiding Credit Information

  20. Creating a Logical File • Create the source file (CRTSRCPF) which is named QDDSSRC (only done once) • Create a source member, type LF • Enter the source code using SEU • Save source code and compile to create the file • Put data into the file.

  21. Create a logical file that sorts Item file by Name and Stocking Size

More Related