Transcript MidRange Concepts - Fox Valley Technical College

MidRange Concepts
Test 3 Review
Data File Utility (DFU)
• Data Manipulation Languages (DML):
allow you to add, delete and modify data.
– Application Programs
– SQL
– DFU
Data File Utility (DFU)
• Can create two types of programs:
– Temporary
– Permanent
• Stored as an object with an attribute of DFU
• DFU Program allows the user to:
– Display, modify, retrieve, or delete a single
record at a time.
– Generate an audit report
Data File Utility (DFU)
• Creating permanent programs the user can
control:
– Fields to be accessed
– Format of displayed data
– Audit report content
• Records deleted, added and changed.
– Screen appearance
Data File Utility (DFU)
• Two modes to a DFU program:
– Entry Mode:
• Defaults to if file is empty
– Change Mode:
• Defaults to if file has records
Data File Utility (DFU)
• DFU Menu Options
– 1. Run: Run an existing DFU program
– 2. Create: Create a new DFU program
– 3. Change: Change characteristics of a current
DFU program.
– 4. Delete: Delete a DFU program.
Data File Utility (DFU)
• Do not want to use DFU if you have
significant amounts of data to change.
• Can not create new fields to be displayed in
a DFU program.
• DFU will not display field breaks if there is
no DDS for the file you are editing. It will
provide 1 field, same length as the record,
with all the information.
Query/400
•
•
•
•
Query provides fast access to stored data.
Gives the user formatting control of the output.
Join data from several files
Retrieved data can be
– Displayed
– Printed
– Stored in a new file
• NOT USED for data manipulation, cannot add or
change records!
Query/400
• A saved query creates a query definition
object, type *QRYDFN with an attribute of
QRY.
– Its an executable object that can be run over
and over.
• Query gets the specific field information
from the DDS for that file.
• Only one query specification is required:
– Specify File Selections
Query/400
• Query/400 allows the user to:
– Do Record Selection:
• Must key in field name and comparison value. (eq, gt, lt, range, list,
like…)
• Compound Record Selection (And, Or , Not)
– Select and Sequence fields
– Sort Records for output
– Specify Report Characteristics
• Change column headings, shrink field sizes and spacing.
• Numeric Field Editing: Currency, Date or Time
• Report Headings/footings and cover sheets
Query/400
• Query/400 allows the user to:
• Create Result Fields used with:
– Numeric data: Add, subtract, multiply or divide
– Character data: Concatenate, substring or date
• Specify Report Summary Functions
– Total, Count, Average, Min and Max
• Define Report Breaks
– Enables you to group related records, usually associated
with the sort fields
– Any totaling information will subtotal on break
Query/400
• Query/400 allows the user to:
– Select output type: Display, printer or file
• Detail or summary information
– Specify multiple files and join them 3 ways:
• Join Types:
– Matched: Displays records with matches in both files
– Matched with Primary: Displays all of the primary file
and only records that match from the secondary file.
– UnMatched with Primary: Displays records from
secondary file that do not have a match in the primary file.
Used for Data Integrity.
(Primary File is the first file specified in the file selection.)
Query/400
– Join Requirements:
•
•
•
•
•
Fields must be of the same type
Do Not have to share the same name
Do Not have to be the same length
Do Not have to be included in the output
Must have at least one join argument for every two
files you are joining
• If fields have the same name you have to specify the
file id.
Query/400
• Managing queries:
– 2: Change an existing query
– 3: Copy a query
– 4: Delete a query
• Queries can be saved and rerun
Structured Query Language
(SQL)
• Comprehensive language for controlling and
interacting with a database management
system.
–
–
–
–
–
Complete data manipulation language
Vendor Independent
Portable across operating systems
English like statements
Can be embedded into other code (goes through a
precompiler) This is a STATIC SQL
– Don’t need a lot of programming experience to use
Structured Query Language
(SQL)
• Statement Driven:
–
–
–
–
–
Select
From
Where
Group by
Order by
• STRSQL: activates it on the I-series
Screen Design Aid (SDA)
• Used to generate screen definition files:
– Creates a file object with an attribute of *DSPF
– DDS source code can also be created
– Source definitions are stored in a source file
with a type of DSPF
– Screen definitions are used as display files
– Eliminates the need for Parameter Passing to
programs.
Screen Design Aid (SDA)
• Process of Designing a Screen:
– Option 1, Design Screen
– Specify Source file to use, also name the source
file member to be created.
– Specify the record format name you want to
create.
– Design your screen, hit F3 and define any
function keys
– Hit F3 to save your source code and compile.
Screen Design Aid (SDA)
• Three types of display fields:
– Constant Text Fields
• Like Labels, Screen Headings and Column
Headings. Should enclose in single quotes.
– System Variable Fields
• System Variables that can be included in the display
for output only, *date, *time, *sysname and *user
– Data Fields
• Fields brought in from a physical file or fields that
you create by assigning characteristics.
Screen Design Aid (SDA)
• Bringing data fields onto your screen:
– Must use F10 to link to the data fields.
• Need to specify file, library and record format of
database file.
– Must specify the following per field:
• I : Input only field
• O: Output only field
• B: Input and Output field
Screen Design Aid (SDA)
• Define the location of the field and header:
– &Marks the location of the fields attribute field.
(position prior to field)
– L: Places header to the left of field.
– R: Places header to the right of field.
– C: Places header over the field
– P: Places only the header
Screen Design Aid (SDA)
• Display characters define the field type and
length:
–
–
–
–
–
–
III
OOO
BBB
333
666
999
Three character input field
Three character output field
Three character input/output field
Three digit numeric input field
Three digit numeric output field
Three digit numeric input/output field
Screen Design Aid (SDA)
• Function Keys in Screen Design:
– F10: tie to a database
– F4: Prompt to display all field names
– F14: Display the screen ruler
Screen Design Aid (SDA)
• Editing Commands
– Must be entered in the Attribute field, position
immediately preceding the data field.
•
•
•
•
•
•
•
•
•
•
* Select all field keywords
<> Move left or Right
- with = or == Copy or move field
AC Center across line
U Underline
H Highlight
D Delete
CB Color Blue
CP Color Pink
Etc
Screen Design Aid (SDA)
• Can format data fields by the following:
–
–
–
–
–
–
–
–
–
Display attributes
Colors
Keying Options
Validity Check
Input Keywords
General Keywords
Editing Keywords
Database Reference
Error Messages and Message Ids
Screen Design Aid (SDA)
• Creating Menus
– Must Specify screen design layout
– Also must use F10 to specify commands that will be
associated with the menu options.
– Objects created:
• Two DDS source file members:
– Type: MNUDDS, Screen definition
– Type: MNUCMD, Command definition
• Three Objects:
– Type: *MSGF
– Type: *FILE, Attribute: DSPF
– Type: *MENU, Attribute: DSPF
Executable Program Commands
•
•
•
•
•
•
STRDFU: Start Data File Utility
STRQRY: Start Query/400
STRSQL: Start Structured Query Language
STRSDA: Start Screen Design Aid
STRPDM: Program Development Manager
STRSEU: Start Source Entry Utility