Preparation for and Accessing the AS/400 DATABASE

1
Preparation for and Accessing the AS/400
DATABASE
2

• Preparation for DATA storage
• Create a receptacle (file)
• internal -- in program or direct (output /
  crtpf)
• external -- with DDS
• PDM (strpdm, wrkobjpdm)
• SEU (strseu)
• Access to the data
• programmatically
• Utilities (dsp cpy chg)
• DFU
• Query/400

3
Source Physical File vsData Physical File

• Source Physical File
• Layout is fixed 3 fields
• programmers can only alter the length of the text
  field
• Used to store code
• Data Physical File
• Layout is determined by Application Design
• Used to store data

4
Source files - CRTSRCPF
Are created only once in a library All / each
library can contain source physical files Contain
members e.g. Programs
(PGM) external physical file
specification (PF) logical file
specifications (LF) display
files
(DSPF) printer file (PRTF)
Default names for specific source types
are QCLSRC CLP Programs QCBLSRC
COBOL programs QRPGSRC
RPG Programs QCSRC C
Programs QDDSSRC File specifications
for PF, LF, DSPF, PRTF etc. etc.
5
preparation for source files
AS/400 Programming Development Manager (PDM)

Select one of the following


1. Work with libraries
2. Work with objects
3. Work with
members
4. Work with projects
5. Work with groups
6. Work with
parts

9. Work with user-defined
options
PDM provides access to all levels of data
storage LIBRARIES OBJECTS (files) members opti
on 12 will allow drill down from the highest to
the lowest level
6

Specify Members to Work With
Type choices, press Enter.
File . . . . .
. . . . . qddssrc Name, F4 for list
Library . . . . . . . .
aberns LIBL, CURLIB, name
Member
Name . . . . . . .
. . ALL ALL, name, generic
Type . . . . . . . . . ALL
ALL, type, generic, BLANK
Work with Members Using PDM ODIN
File . . . . . . QDDSSRC
Library .
. . . ABERNS Position to
_____________________ . . . . .
Type options, press Enter.
2Edit
3Copy 4Delete 5Display 6Print
7Rename 8Display description
9Save 13Change text 14Compile 15Create
module... Opt Member Type Text
__
APVENDOR PF In class developed
__ ARCUST
PF ASSIGNMENT 2 and in class 3/4
__ ARTRANPF PF
AR transaction file OPS234 fall 2000
__ ARTRNS PF ASSIGNMENT 2
__
ARTRNSDF DSPF Assignment 2
transaction display file __
ASSG2DSP DSPF
7
Source editing for physical files - PF
Columns . . . 1 71 Edit
ABERNS/QDDSSRC SEUgt

ARCUST FMT A .....A. 1 ...... 2
...... 3 ...... 4 ...... 5 ...... 6 ...... 7
Beginning of data
0001.00
A Physical file ARCUST ACCOUNTS RECEIVABLE
Master File 0002.00 A

0003.00 A
UNIQUE
0004.00 A R ARCUSTR
0005.00
A CUSTNO 5S 0
TEXT('CUSTOMER NUMBER') 0006.00 A

ALIAS(AR_CUST_NUMBER) 0007.00 A
NAME 30A
TEXT('CUSTOMER NAME') 0008.00 A

ALIAS(AR_CUST_NAME) 0009.00 A
SALESMAN 2S 0
TEXT('SALESMAN') 0010.00 A

ALIAS(AR_SLSM_NUMBER) 0011.00 A
STATUS 1A
TEXT('STATUS') 0012.00 A

ALIAS(AR_CUST_STATUS)
8
Source editing for display files - DSPF
0002.00 A DISPLAY FILE - ARTRNSDF -
CUSTOMER TRANSACTION INPUT 0003.00 A

0004.00 A SCREEN1 FORMAT
TO PROMPT FOR CUSTOMER NUMBER
0005.00 A
0007.00 A


DSPSIZ(24 80 DS3) 0008.00 A

CA03(03
'F3EXIT') 0009.00 A

INDARA
0010.00 A R SCREEN1

0011.00 A

BLINK 0012.00
A S1DATE
8 0O 1
3ALIAS(SN1_SYS_DATE) 0013.00 A


EDTCDE(Y) 0014.00 A

1 25'Customer
Transaction Input 0015.00 A

DSPATR(RI)
0016.00 A S1PNAME
5A O
1 68ALIAS(SN1_PROG_NAME) 0017.00 A

4 10'CUSTOMER
' 0018.00 A CUSTNO
5A B
4 35ALIAS(SN1_CUST_NUMBER) 0019.00 A
10
7
10'DIAGNOSTIC ' 0020.00 A 10
S1DIAGN 30A O
7 35ALIAS(SN1_DIAGNOSTICS)
0021.00 A

23 2'F3Exit' 0022.00 A
R SCREEN2
0023.00 A

CF12(12 'F12NEXT')
0024.00 A S2DATE
8 0O 1
3ALIAS(SN2_SYS_DATE) 0025.00 A

EDTCDE(Y)
0026.00 A

1 25'Transaction Capture for'
0027.00 A

DSPATR(RI) 0028.00
A S2PNAME 5A
O 1 68ALIAS(SN2_PROG_NAME)
0029.00 A

3 10'CUST NO' 0030.00
A

DSPATR(HI) 0031.00 A

3 20'NAME'
0032.00 A

DSPATR(HI)
9
SEU HELP F1 with cursor on sequence number
function
0002.00 ..........................................
............................. 0003.00
Sequence Number - Help
0004.00

0005.00 F?Show the Select Format display
0006.00
Type F? to show the Select Format display. From
this display 0007.00 choose the
format to use.
0008.00
0009.00
IInsert a line
0010.00 Type I to
insert a blank line after this record.
0011.00
0012.00
InInsert n lines
0013.00 Type In
to insert n blank lines after this record.
0014.00

0015.00 IFInsert line and display format
0016.00

More...
10
Create / Generate the object option 14 a
executable program a physical file a display
(screen) file a report format (regular 8 1/2 x 11
or special forms)
File . . . . . . QDDSSRC
Library . .
. . ABERNS Position to . . . .
.

Type options, press Enter.
2Edit
3Copy 4Delete 5Display 6Print
7Rename 8Display description
9Save 13Change text 14Compile 15Create
module...
Opt
Member Type Text
14 ARTRNSDF
DSPF Assignment 2 transaction display
file ASSG2DSP DSPF

ASSG3DSP DSPF

ASSG3DSPC2 DSPF
11
DATA (files)

• Can come from anywhere disk, tape, screen,
  communications, internet, e-biz
• Is managed / created / manipulated
  by a program
• or a utility such as DFU
• or generated by QUERY as output
• or ??????

12
Types of Physical Files

• Flat files - Arrival order only Sequential or
  Random access only
• Keyed / Indexed Files Sequential, Random,
  Keyed access

13
Physical Files using a Key

• Access a record in a file according to the value
  of a specific field (the Key Field).ex. You
  could access a particular student record using
  the student id as the key rather that read the
  whole file looking for it.
• You specify that a file will have a key when you
  created / entered the DDS source for the file

14
What if we want to access the data in a different
way?

• We can create new access path (s) to the data
  using Logical Files

15
Logical Files

• Logical View or Index over one or more
  physical files
• Refer to physical file(s) and can only be
  created after the Physical file(s) exist
• Do NOT contain data
• They are an access path into a file by using
  index keys from a keyed record to point to a
  position in the physical file it is based on

16
What is a Logical File?

• It is a file which provides another way or view
  of looking at data in a physical file, i.e.
  another access path to the data.
• It does not contain data, the data is stored in
  the physical file member.
• The access path in the logical file acts as an
  index to the data.
• A logical file is always created over one or more
  specific PFs

17
Physical File - STUDENTS (FIFO sequence)
RR
STUDID
LASTNAME
FIRSTNAME
Logical File - STUDENTS by last name

RR
LASTNAME
Example of a simple logical file with a key of
LASTNAME over a physical file that does not
have a key.
18
Entering data

• If the compile (option14) was successful, you
  will have a new object in your library, a
  physical file. (type FILE Attr. PF)
• To enter data into that file. Under program
  control. Using a systems utility (CPYF
  etc.) Using DFU, Data File Utility

19
Helpful commands

• DSPOBJD display object descriptiongives you
  information such as creation date, change date,
  owner, etc.
• DSPFD display file descriptiongives you
  information such as file size, record length,
  number of fields, etc.
• DSPFFD display file field descriptiondisplays
  complete field information

20
DFU Data File Utility

• Easiest way to quickly enter data into a physical
  file.
• Can create permanent DFU programs or use a
  temporary DFU program
• STRDFU 5, or Option 18 within PDM
• Can NOT be used on logical files
• Set the type of activity required
• Entry (F10) to add records
• Change mode (F11) to modify records
• F23 to delete records (hidden function key)
• PGUP, PGDN to see the previous or next record

21
DFU - STRDFU
Work with Objects Using PDM
ODIN Library . . . . . DA234H01A1

Position to . . . . . . . .


Position to type . . . . . Type
options, press Enter.
2Change
3Copy 4Delete 5Display
7Rename 8Display description
9Save 10Restore 11Move ...

Opt
Object Type Attribute
Text
-- STUDENTDFU PGM DFU
STUDENTDFU --
WELCOME PGM CLE
assignment1 ops234 --
ANSWERS FILE PF-SRC

PRINTOUTS FILE PF-DTA
saving printed output
QCSRC FILE PF-SRC
testing for assignmen 1
QDDSSRC FILE PF-SRC

STUDENTDFU FILE DFU
STUDENTDFU
STUDENTS FILE PF-DTA
Deb's student file and andre's test


Bottom
Parameters or command
gt strdfu
or option 18 in PDM use F23 to see

F3Exit F4Prompt
F5Refresh F6Create
F9Retrieve F10Command entry F23More
options F24More keys
22
AS/400 Data File Utility (DFU)

Select one of the following

1. Run a DFU program
2. Create a
DFU program
3. Change a DFU program
4. Delete a DFU program
5. Update data using temporary
program
Create a DFU Program

Type choices, press Enter.


Program . . . . . . . . . . STLSTDFU2
Name, F4 for list Library .
. . . . . . . . AB234MRKA1 Name,
CURLIB
Data
file . . . . . . . . . STLST
Name, F4 for list Library . . .
. . . . . . AB234MRKA1 Name, LIBL,
CURLIB
23
Select and Sequence Fields in DFU


File . . . . . . . . . . . STLST
Library . . . . AB234MRKA1 Record
format . . . . . . . STLSTR


Select fields and their sequence or
press F21 to select all press Enter.



Sequence Field Attr Length
Type Description
1 USERID 10
CHAR
3 SECTIN 1
CHAR
4 STUDID 11
CHAR
5 STATUS 1
CHAR
6 STDATE 6,0 ZONE
7
OBDATE 6,0 ZONE
8
STRLEN 5,0 ZONE
RECORD LENGTH 9
STPOS 5,0 ZONE
BUFFER POSITION 10
STCDAT 6,0 ZONE
CREATE DATE 11
STCTIM 6,0 ZONE
CREATE TIME

More... F3Exit F5Refresh
F12Cancel F14Display definition
F17Fast path F20Renumber F21Select
all Press Enter
to confirm or F17 to confirm with defaults.
24

• ADD - CHANGE - DELETE - DISPLAY records

STLSTDFU3
Mode . . . . CHANGE Format . . .
. STLSTR File . . . .
STLST

RECNBR 1
USERID
DA234H01
NAME
TESTING-FOR ABERNS
SECTIN

STUDID

STATUS R
STDATE
102900
OBDATE

STRLEN 118

STPOS 111

STCDAT
STCTIM

STOBSZ

STMBSZ 8192
NUMRCD
15
OWNCHK




F3Exit
F5Refresh F6Select format
F9Insert F10Entry
F11Change
F23 to delete Confirm with F23 Page Up Down
to see next previous record
25
QUERY/400

• IBM licensed product provided for free with every
  OS/400 system
• USED for QUICK screen inquiries
• On demand ONE OF reports
• Collect data from various physical files and
  generate a new format data file or report
• When saved becomes an object of type QRYDFN
  definition

26
Run Query (RUNQRY)

Type
choices, press Enter.


Query . . . . . . . . . . . . .
------------ Name, NONE
Library . . . . . . . . . . .
LIBL Name, LIBL, CURLIB
Query file

File . . . . . . . . . . . . .
-students--- Name, SAME
Library . . . . . . . . . .
LIBL Name, RUNOPT, LIBL, CURLIB
Member . . . . . . . . . . . .
FIRST Name, RUNOPT, FIRST, LAST
for more values
Report output
type . . . . . . . RUNOPT RUNOPT,
DISPLAY... Output form . . . . .
. . . . . RUNOPT RUNOPT,
DETAIL, SUMMARY Record selection . . .
. . . . . NO NO, YES

27
RESULT of QUICK query run
Display Report

Report width .
. . . . 142 Position to line . . . . .

Shift to column . . . . . . Line
........1........2........3........4........5
........6........7. SOCINS
LNAME FNAME
ADDR1 000001 111,110,002
BILBO BAGGINS
345 HAWTHORNE LN 000002 111,110,003
KARIKOOL CLAPSADDLE
SUNDOWN RD 000003 111,110,004
MATILDA TWIDDLEBOTHAM 1812 RIVER
RD 000004 111,110,001 CHAN
CHARLIE HOLLYWOOD
BLVD 000005 111,110,005 DOWEGER
ENGAR LA LANE
000006 111,110,006 STEAMS
HOT WATER RD
000007 111,110,007 MULLIGAN
SWINGA HOLE89
000008 111,110,008 WILSON ACE
TENNISCOURT 45
000009 111,110,009 ICECOLD
DRINK ARENA5
000010 111,110,010 RAPTURED
PURPLE AIR CENTRE
000011 111,110,011 ARROW
BOWEN BULLSEYE
000012 111,110,012 SHOTPUT
IRONBALLS AROUNDCORNER
000013 111,110,013 GYMNASTICS ACROBAT
RINGALLEY 000014
111,110,014 ATHLETIC RUNNER
BEN JOHNSON ALLEY 000015
111,110,015 RACING HARNESS
HORSESTALLS
28

• STRQRY

QUERY Query Utilities
System ODIN Select one of
the following


Query for AS/400
1.
Work with queries
2. Run an
existing query
3. Delete a query


DB2 for
AS/400
10. Start DB2 Query
Manager for AS/400


Query management
20. Work
with query management forms
21. Work with query
management queries
22. Start a query

23. Analyze a Query for AS/400 definition


More... Selection or command

gt


F3Exit F4Prompt
F9Retrieve F12Cancel F13Information
Assistant F16AS/400 Main menu

.
29

• OPTION 1

Work with Queries

Type
choices, press Enter.


Option . . 1 . . .
1Create, 2Change, 3Copy, 4Delete

5Display, 6Print definition

8Run in batch, 9Run
Query . .????????
Name, F4 for list
Library . . . . . ABERNS Name, LIBL,
F4 for list
30
Define the Query

Query . .
. . . . Option . . . . .
CREATE Library . . . .
ABERNS CCSID . . . . . . 65535


Type options, press Enter. Press F21 to select
all. 1Select



Opt Query Definition Option
1
Specify file selections
1 Define result
fields
1 Select and sequence fields
1
Select records
- Select sort
fields
- Select collating sequence

1 Specify report column formatting
1 Select
report summary functions
- Define report breaks

1 Select output type and output form
-
Specify processing options


F3Exit F5Report
F12Cancel
F13Layout F18Files F21Select
all
31
File selection
Specify File Selections

Type choices, press Enter. Press F9
to specify an additional file selection.


File . . . . . . . . .
STUDENTS Name, F4 for list
Library . . . . . . AB234MRKA1 Name,
LIBL, F4 for list Member . . . . . . . .
FIRST Name, FIRST, F4 for list
Format . . . . . . . . FIRST
Name, FIRST, F4 for list File ID . . . . . .
. T01 A-Z99, ID

File . . . . . . . .
. SCHOOL Name,
F4 for list Library . . . . . .
AB234MRKA1 Name, LIBL, F4 for list Member .
. . . . . . . FIRST Name,
FIRST, F4 for list Format . . . . . . . .
FIRST Name, FIRST, F4 for list
File ID . . . . . . . ID
A-Z99, ID
32
OUTPUT selection
Select Output Type and Output Form

Type choices, press Enter.


Output type . . . . . . . . .
. . 1 1Display

2Printer

3Database file

Form of output . . . . . . . . . . 1
1Detail
2Summary
only
Line
wrapping . . . . . . . . . . N YYes,
NNo Wrapping width . . . . .
. . . . Blank, 1-378
Record on one page . . . . . . . N YYes,
NNo
33

• The END