iseries database files physical files *file pf-dta logical files *filelf
TRANSCRIPT
![Page 1: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/1.jpg)
iSeries Database Files
• Physical Files *FILE PF-DTA
• Logical Files *FILE LF
![Page 2: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/2.jpg)
Physical Files• It is an as/400 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)
![Page 3: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/3.jpg)
Data FilesFiles, Records, Fields
• A file member contains one or more records
• A record is made up of one or more fields which give it its record format or file description
• Each field contains a unique piece of information e.g. student no, first name, last name, address, telephone no, etc.
![Page 4: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/4.jpg)
Examples of Files, Records, Fields• 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
• Student file - student records (1 per student)- student info: name, address...
![Page 5: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/5.jpg)
More Examples of Files, Records, Fields
• Parts file - parts records - info about part:part number, description, warehouse location, supplier, quantity on hand,
• Payroll file - payroll records - payroll info for employees: employee number, name, date of hire, hourly or salary, hourly rate or annual salary, income tax info….
![Page 6: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/6.jpg)
How do we create data physical files?
![Page 7: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/7.jpg)
Creating Physical Data Files
• Creating an AS/400 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).
![Page 8: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/8.jpg)
Tools for Describing Database Files
• Data Description Specifications (DDS)– AS/400 language to create source code for Files
• Others:– SQL (Structured Query Language), (you will do this in DB2
course)
![Page 9: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/9.jpg)
Using DDS• Source code is entered into a source physical file member in the
form of DDS statements. This is done using the iSeries editor SEU
• The DDS statements describe each field of the record in detail e.g. field name, data type (numeric, character, packed numeric, etc), standard column headings, etc.
• The source file for DDS is QDDSSRC.
![Page 10: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/10.jpg)
Example of DDS
Columns . . . : 1 71 Browse DMENKEN/QDDSSRC SEU==> STUPF FMT PF .....A..........T.Name++++++RLen++TDpB......Functions++++++++++++++++++ *************** Beginning of data ************************************* 0001.00 0002.00 A R STUPFR 0003.00 A SOCINS 9S 0 COLHDG('Social' 'Insurance' 0004.00 A FNAME 15A COLHDG('First' 'Name') 0005.00 A LNAME 20A COLHDG('Last' 'Name') 0006.00 PHONE 10S COLHDG('Phone' 'No.') 0007.00 A ADDR1 25A COLHDG('Street' 'Address') 0008.00 A ADDR2 25A COLHDG('City') 0009.00 A ZIP 5S 0 COLHDG('Zip Code') ****************** End of data ****************************************
![Page 11: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/11.jpg)
Creating a Database File
• Create the source file (CRTSRCPF) which is named QDDSSRC (only done once)
• Create a source member, type PF
• Enter the source code using SEU
• Save source code and compile to create the file
• Put data into the file.
![Page 12: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/12.jpg)
• The source type: – tells SEU what type of source you are
creating e.g. TXT, PF, LF, etc. Used for prompting and checking the syntax
– will tell PDM which compiler to use when creating the object.
![Page 13: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/13.jpg)
Entering DDS code
• First statement is the Record Format statement. Put an R in the Name Type field and the record name in the Name field.
• Then enter the field info (field name, length, data type, decimal positions, standard column heading) (nothing in Name Type field)
![Page 14: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/14.jpg)
Demo
![Page 15: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/15.jpg)
Compiling the Source Code
• CRTPF command or Option 14 in PDM
takes the source code and creates the object i.e. the physical file
the physical file is what holds the data
![Page 16: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/16.jpg)
How do you know if the compile worked???
• DSPMSG- you will have a message which tells you whether your job completed normally or not.
• An output file is created with each compile (the compile listing) which tells you what happened. READ it!
• Where is that output file??– In your output queue!!
![Page 17: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/17.jpg)
Question!
• If the compile ended abnormally, what do you do? How can you find out why?
![Page 18: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/18.jpg)
Unique feature of iSeries Files
• The record description is stored with the file object (externally described file)
• It can then be used by iSeries utilities
• The record description does not have to be coded in programs that use it.
• Can be viewed using DSPFD, DSPFFD
![Page 19: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/19.jpg)
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
![Page 20: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/20.jpg)
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
![Page 21: iSeries Database Files Physical Files *FILE PF-DTA Logical Files *FILELF](https://reader036.vdocuments.us/reader036/viewer/2022081416/551b3900550346d41a8b52d9/html5/thumbnails/21.jpg)
Viewing records
• RUNQRY QRYFILE(filename)
• DSPPFM filename