Transcript IBC233 W6 powerpoint

IBC233
How’s assignment 1
coming?
Winter 2007
Agenda
• Test 2, Feb 19
• Assignment 1? Due Feb. 23
– Telephone number editing
– Order Date
• Some more CL Programming
– %SST, *CAT and basic math
• Database Files
– Physical files
– Logical files
%SST - Substring
%SST(&FIELDA offset length)
CHGVAR &FIELDA VALUE(‘Cindy Laurin-Moogk’)
CHGVAR &FNAME VALUE(%SST(FIELDA 1 5))
CHGVAR &LNAME VALUE(%SST(FIELDA 7 6))
CHGVAR &NEWNAME VALUE(&FNAME *BCAT
&LNAME)
Concatenate
*CAT – Puts two character strings together as is
the receiving string variable has to be declared
with the combined length else truncation will
occur.
*BCAT – Puts two character strings together by deleting
the trailing spaces in the first field, inserting a blank
space and then adding the second field
*TCAT – Puts two character strings together by deleting
the trailing spaces in the first field.
Concatenate Examples
DCL FLDA *CHAR 10 VALUE(‘CINDY’)
DCL FLDB *CHAR 30 VALUE(‘LAURIN)
DCL FLDC *CHAR 50 VALUE(‘ ‘)
CHGVAR FLDC VALUE(FLDA *CAT FLDB)
CHGVAR FLDC VALUE(FLDA *TCAT FLDB)
CHGVAR FLDC VALUE (FLDA *BCAT FLDB)
CHGVAR - Math
DCL &TOTAL
DCL &FLDA
DCL &FLDB
DCL &FLDC
*DEC LEN(7 2)
*DEC LEN(5 0) VALUE(10)
*DEC LEN(5 0) VALUE(2)
*DEC LEN(5 0) VALUE(60)
CHGVAR &TOTAL (&FLDA * &FLDA /
(&FLDA - &FLDB) + &FLDC)
What is the value of &total?
+
SUBSTRING program
Database Files
iSeries Database Files
• Physical Files
• Logical Files
*FILE
*FILE
PF-DTA
LF
Physical Files
• It is an iSeries 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)
Data Files
Files, 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.
• Looks like a spreadsheet with rows and
columns (rows = records, columns = fields)
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 customer
e.g. customer name, address, sales
territory,billing info, shipping instructions,
credit information
• Student file - student records (1 per
student)- student info: name, address...
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….
How do we create data
physical files?
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).
Tools for Describing Database
Files
• Data Description Specifications (DDS)
– iSeries language to create source code for
Files
• Others:
– SQL (Structured Query Language) – next
week
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 LPEX
• 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.
Layout of a
Data Definition Specification member
(DDS)
• File level keywords
– Eg. UNIQUE
• Record format name
– Shouldn’t be the same name as the PF
• List the fields
– Name, type, size and functions
• TEXT (used by DFU and DSPFFD)
• COLHDG (used by Query/400)
• Access Path information
Example of DDS
Columns . . . :
1 71
Browse
DMENKEN/QDDSSRC
SEU==>
STUPF
FMT PF .....A..........T.Name++++++RLen++TDpB......Functions++++++++++++++++++
*************** Beginning of data *************************************
0001.00
UNIQUE
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
10P
COLHDG('Phone' 'No.')
0007.00
A
ADDR1
25A
COLHDG('Street' 'Address')
0008.00
A
ADDR2
25A
COLHDG('City')
0009.00
A
POSTAL
6A
COLHDG(‘Postal Code')
DTEHIRE
L
COLHDG(‘Date’ ‘Hired’)
K SOCINS
****************** End of data ****************************************
Creating a Physical 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 code into the file.
• The source type:
– tells OS/400 what type of source you are
creating e.g. TXT, PF, LF, etc. Used by the
editor for prompting and syntax checking
– will tell OS/400 which compiler to use when
creating the object.
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)
Demo
Compiling the Source members
Code into an usable object
• CRTPF
Takes the source code and creates the
object i.e. the physical file
the physical file is what holds the data
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
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
DFU
• STRDFU, then option 5
• PDM option 18
or…
• 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
(F23 again to confirm)
Viewing records
• RUNQRY QRYFILE(filename)
• DSPPFM
filename
Access Paths
Allows us to sort or select/omit
data
Logical Files
Logical Files
•
•
•
•
Resort data in a physical file
Select/Omit specific sets of data
Hide data
Join or Merge physical files together
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 customer
e.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
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.
Create a logical file that sorts
STUDENTS by LNAME, FNAME
Next Week
• Programming with Database files