INF107 - Minicomputer Operations - Class Nine

INF107 - Minicomputer Operations - Class Nine

I.Chapter 12 – Data File Utility (DFU)

A.Purpose

1.Provides casual data entry into database files

2.Provides only limited data validation

3.Used primarily by programmers to set up test data

B.Positioning

1.Advantages

a)DFU is much more "user friendly" than SQL

b)You don't require "programmer knowledge" to use

(1)SQL & custom programs require that you know a lot more

2.Disadvantages

a)Limited validation of data (only the validation rules specified in DDS)

b)Limited editing of data (making it "look pretty")

C.STRDFU Command

1.You can create either:

a)A "temporary program"

b)An object similar to a program

(1)Here you can store a permanent definition

(2)This is also much more "efficient"

D.Demonstration

E.DFU Lab

1.Create lab environment

a)Use the command "CALL CRTLAB9" to set up your lab environment. Program "CRTLAB9" will create two files in YOUR library. These files are:

(1)DEPTPF - Department Master File

(2)EMPLPF - Employee Master File

2.Enter data into the Department Master file

a)Using DFU, create a data entry program named "DEPTDFU" in YOUR library for physical file "DEPTPF"

b)Enter the following data records into file "DEPTPF"

Dept.
Nbr. / Department
Name / Location
Code
SLS / Sales / CHICAGO
FIN / Finance / NEW YORK
MFG / Manufacturing / PITTSBURGH
MKT / Marketing / NEW YORK

3.Enter data into the Employee Master file

a)Using DFU, create a data entry program named "EMPLDFU" in YOUR library for physical file "EMPLPF"

b)Enter the following data records into file "EMPLPF"

Empl.
Nbr. /
Name / Dept.
Nbr. / Hire
Date / Job
Code /
Sex /
Salary
101 / Duck, Donald / SLS / 06/01/84 / AC / M / 45000
102 / Runner, Road / MFG / 08/24/84 / AS / M / 25000
103 / Mouse, Minnie / FIN / 09/15/84 / AC / F / 48000
104 / Warner, Dot / MKT / 10/21/84 / CR / F / 42000
106 / Hood, Robin / SLS / 11/23/84 / SL / M / 65000
107 / Poppins, Mary / MKT / 11/27/84 / BS / F / 42000
111 / Bunny, Bugs / SLS / 12/01/84 / SL / M / 23000
112 / Counter, Bean / FIN / 12/23/84 / AC / F / 86000
125 / Claus, Santa / SLS / 12/25/84 / SL / M / 1

II."Open Query File" - OPNQRYF Command

A.Equivalent to dynamically creating a logical file on the fly!

1.Sort

2.Select

3.Join

4.Group

5.Define fields "on the fly"

III.Chapter 24 – Query/400

A.Purpose

1.Produces reports with little programming knowledge

2.Often used to handle “one time requests” or prototypes for new reports

3.Used both by programmers & end users

B.Positioning

1.Advantages

a)Query is much more "user friendly" than SQL

b)Can do simple reports without programming

2.Disadvantages

a)Limited formatting capabilities

b)Can only generate simple reports

C.STRQRY command

1.You are defining an object type that is similar to a program

2.You must specify how the query must run:

a)Interactive - run within the terminal session

b)Batch - submit a batch job to run the query

D.Portions of a Query definition

1.File selections

a)What file(s) are being used to produce the report?

(1)If more than one file specified, the files must be "joined"

(2)A field in each file (join field) must be specified

2.Result Fields

a)Allow calculations on fields

b)Allow definition of new fields

3.Select & Sequence Fields

a)What fields are to be included in the report?

b)What order should they appear across the report?

4.Select records

a)Which rows of the database should be included in the final result?

b)Requires use of "logical operations"

5.Select sort fields

a)What sequence should the lines of the report be sorted in?

b)Should the sort for each field be:

(1)Ascending (Low to High values)

(2)Descending (High to Low values)

6.Select Collating Sequence

a)What determines the rules for sorting the data?

b)Data sorted on AS/400 will be in different sequence than that of a PC

(1)AS/400 - EBCDIC data coding

(2)PC - ASCII data coding

7.Specify Report Column Formatting

a)How should column headings look?

b)Is there some overridding formatting to be done on a field?

8.Select Report Summary Options

a)For each field, do you want to see:

(1)Totals

(2)Averages

(3)Minimum

(4)Maximum

(5)Counts

9.Define Report Breaks

a)Break Level

(1)Field(s) that imply an action should occur when their value changes from one record to the next

(a)This action is usually a summary

(2)More than one field can make up a "break level"

b)A "report break" occurs when a value of field(s) defined as a "break level" changes from one record to the next

c)Report breaks are USUALLY correlated with the sort sequence of the report

10.Select output type & output form

a)Output type - Where should results be displayed?

(1)Display - on screen

(2)Printer - send to a system printer

(3)Database file - write the resulting fields to a database file

b)Output form - How much output should be displayed?

(1)Detail - Print one line for each record read & for each summary produced

(2)Summary - Only print summary information

c)Line wrapping - What should you do if the output is too wide to fit on the device?

11.Specify processing options

a)What should the system do in certain situations?

E.Demonstration

F.Lab

1.Query/400 to produce the following reports. Produce the output on the screen and use the print screen function to print the output.

a)List of all accountants (EMPLQRY1)

(1)“List all employees with hire dates who have a Job Code of ‘AC’ ordered by salary in descending sequence with a total of all of their salaries.”

(2)Use Fields: Employee Name, Date Hired, Salary

(3)Sort by Salary

(4)Select Job Code = ‘AC’

(5)Print a total of all salaries of selected employees

b)Employee Roster by Employee (EMPLQRY2)

(1)“List each employee’s name, number. department name, and location ordered by employee name.”

(2)Use Fields: Employee Name, Employee Number, Department Number, Department Name, Location

(3)Sort by Employee Name

c)Employee Roster by Location (EMPLQRY3)

(1)“For each location, list all employees and their department sorted by employee name”

(2)Use fields: Location, Employee Name, Department Name,

(3)Sort by Location, Employee Name

d)Salary Analysis by Location (EMPLQRY4)

(1)“For each location, list the total of salaries for each employee who is assigned to the location”

(2)Summary Report Only

(3)List fields: Location, Total Salary

(4)Sort field: Location

2.Staple the pages together and write your name on the printed output

3.Hand in your work

IV.Pre-test Review

A.Test covers lecture & reading assignments

B.The lecture notes cover the most important details

1.If the lecture notes say that you should KNOW something contained in the book, it would be a good use of your time to make sure that you know the subject!

C.You are expected to know how to do the following:

1.Use Programming Development Manager (PDM)

2.Use Source Entry Utility (SEU) to enter Data Description Specifications (DDS)

3.Create both an internally defined physical file & externally defined physical file from DDS

4.Use the Data File Utility (DFU) to enter data into a file

5.Use the Query/400 Utility to report on data in files

D.Test will be open AS/400, closed everything else

1.Written test

2.Lab test

Page 1