Accessing the AS/400 DATABASE 1
Preparation for DATA storage Create a receptacle (file) internal -- in program or direct (output / crtpf) external -- with DDS PDM SEU (strpdm, wrkobjpdm) (strseu) Access to the data programmatically Utilities (dsp cpy chg) DFU Query/400 2
Source Physical File vs Data Physical File • Source Physical File (in between stage, a blueprint) – Layout is fixed: 3 fields • programmers can only alter the length of the text field – Used to store code • Data Physical File (actual object) – Layout is determined by Application Designer – Used to store data 3
Source files - CRTSRCPF Are created only once in a library (container for blueprints) All / each library can contain source physical files Contain zero to many members (specifications) 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 QDDSSRC etc. C Programs File specifications for PF, LF, DSPF, PRTF 4
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 : : : 9. Work with user-defined options PDM provides access to all levels of data storage LIBRARIES File objects (*file) members option 12 will allow “drill down” from the highest to the lowest level 5
Specify Members to Work With Type choices, press Enter. File. . qddssrc Library. . . . aberns Member: Name. . *ALL Type. . *ALL Name, F 4 for list *LIBL, *CURLIB, name *ALL, name, *generic* *ALL, type, *generic*, *BLANK Work with Members Using PDM ODIN File. . . QDDSSRC Library. . ABERNS Position to ___________. . . Type options, press Enter. 2=Edit 3=Copy 4=Delete 5=Display 6=Print 7=Rename 8=Display description 9=Save 13=Change text 14=Compile 15=Create module. . . Opt Member __ APVENDOR __ ARCUST __ ARTRANPF __ ARTRNSDF __ ASSG 2 DSP Type PF PF DSPF Text In class developed ASSIGNMENT 2 and in class 3/4 AR transaction file OPS 234 fall 2000 ASSIGNMENT 2 Assignment 2 transaction display file 6
Source editing for physical files - PF Columns. . . : 1 71 Edit ABERNS/QDDSSRC SEU==> 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 5 S 0 TEXT('CUSTOMER NUMBER') 0006. 00 A ALIAS(AR_CUST_NUMBER) 0007. 00 A NAME 30 A TEXT('CUSTOMER NAME') 0008. 00 A ALIAS(AR_CUST_NAME) 0009. 00 A SALESMAN 2 S 0 TEXT('SALESMAN') 0010. 00 A ALIAS(AR_SLSM_NUMBER) 0011. 00 A STATUS 1 A TEXT('STATUS') 0012. 00 A ALIAS(AR_CUST_STATUS) 7
Source editing for display files - DSPF 0002. 00 0003. 00 0004. 00 0005. 00 0007. 00 0008. 00 0009. 00 0010. 00 0011. 00 0012. 00 0013. 00 0014. 00 0015. 00 0016. 00 0017. 00 0018. 00 0019. 00 0020. 00 0021. 00 0022. 00 0023. 00 0024. 00 0025. 00 0026. 00 0027. 00 0028. 00 0029. 00 0030. 00 0031. 00 0032. 00 A* DISPLAY FILE - ARTRNSDF - CUSTOMER TRANSACTION INPUT A* A* SCREEN 1 FORMAT TO PROMPT FOR CUSTOMER NUMBER A* A DSPSIZ(24 80 *DS 3) A CA 03(03 'F 3=EXIT') A INDARA A R SCREEN 1 A BLINK A S 1 DATE 8 0 O 1 3 ALIAS(SN 1_SYS_DATE) A EDTCDE(Y) A 1 25'Customer Transaction Input A DSPATR(RI) A S 1 PNAME 5 A O 1 68 ALIAS(SN 1_PROG_NAME) A 4 10'CUSTOMER =' A CUSTNO 5 A B 4 35 ALIAS(SN 1_CUST_NUMBER) A 10 7 10'DIAGNOSTIC =' A 10 S 1 DIAGN 30 A O 7 35 ALIAS(SN 1_DIAGNOSTICS) A 23 2'F 3=Exit' A R SCREEN 2 A CF 12(12 'F 12=NEXT') A S 2 DATE 8 0 O 1 3 ALIAS(SN 2_SYS_DATE) A EDTCDE(Y) A 1 25'Transaction Capture for' A DSPATR(RI) A S 2 PNAME 5 A O 1 68 ALIAS(SN 2_PROG_NAME) A 3 10'CUST NO' A DSPATR(HI) A 3 20'NAME' A DSPATR(HI) 8
SEU HELP F 1 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 : I=Insert a line : 0010. 00 : Type I to insert a blank line after this record. : 0011. 00 : : 0012. 00 : In=Insert n lines : 0013. 00 : Type In to insert n blank lines after this record. : 0014. 00 : : 0015. 00 : IF=Insert line and display format : 0016. 00 : More. . . : 9
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. 2=Edit 3=Copy 4=Delete 5=Display 6=Print 7=Rename 8=Display description 9=Save 13=Change text 14=Compile 15=Create module. . . Opt Member 14 ARTRNSDF ASSG 2 DSP ASSG 3 DSPC 2 Type DSPF Text Assignment 2 transaction display file 10
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 ? ? ? 11
Types of Physical Files • Flat files - Arrival order only Sequential or Random access only • Keyed / Indexed Files Sequential, Random, Keyed access 12
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 than read the whole file a record at the time looking for it. • You specify that a file will have a key when you created / entered the DDS source member for the file 13
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 Type LF 14
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 key’s from a keyed record to point to a position in the physical file it is based 15 on
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 16 or more specific PF’s
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. 17
Entering data • If the compile (option 14) 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 18
Helpful commands • DSPOBJD: display object description gives you information such as creation date, change date, owner, etc. • DSPFD: display file description gives you information such as file size, record length, number of fields, etc. • DSPFFD: display file field description displays complete field information 19
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 20 *QRYDFN definition
Run Query (RUNQRY) Type choices, press Enter. Query. . . Library. . . Query file: File. . . Library. . Member. . . + for more values Report output type. . . . Output form. . Record selection. . . . ------ Name, *NONE *LIBL Name, *LIBL, *CURLIB -students--*LIBL *FIRST *RUNOPT *NO Name, *SAME Name, *RUNOPT, *LIBL, *CURLIB Name, *RUNOPT, *FIRST, *LAST *RUNOPT, *DISPLAY. . . *RUNOPT, *DETAIL, *SUMMARY *NO, *YES 21
RESULT of QUICK query run Display Report width. . . : 142 Position to line. . . Shift to column. . . Line. . +. . 1. . +. . 2. . +. . 3. . +. . 4. . +. . 5. . +. . 6. . +. . 7. SOCINS LNAME FNAME ADDR 1 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 HOLE 89 000008 111, 110, 008 WILSON ACE TENNISCOURT 45 000009 111, 110, 009 ICECOLD DRINK ARENA 5 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 22
• 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 DB 2 for AS/400 10. Start DB 2 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 ===> F 3=Exit F 4=Prompt F 9=Retrieve F 12=Cancel F 13=Information Assistant F 16=AS/400 Main menu. 23
• OPTION 1 Work with Queries Type choices, press Enter. Option . . 1. . . Query. . ? ? ? ? Library. . . ABERNS 1=Create, 2=Change, 3=Copy, 4=Delete 5=Display, 6=Print definition 8=Run in batch, 9=Run Name, F 4 for list Name, *LIBL, F 4 for list 24
Define the Query. . . : Library. . : Option. . . : CREATE ABERNS CCSID. . . : 65535 Type options, press Enter. Press F 21 to select all. 1=Select Opt 1 1 1 1 - Query Definition Option Specify file selections Define result fields Select and sequence fields Select records Select sort fields Select collating sequence Specify report column formatting Select report summary functions Define report breaks Select output type and output form Specify processing options F 3=Exit F 13=Layout F 5=Report F 18=Files F 12=Cancel F 21=Select all 25
File selection Specify File Selections Type choices, press Enter. Press F 9 to specify an additional file selection. File. . Library. . . Member. . . . Format. . . . File ID. . . . STUDENTS AB 234 MRKA 1 *FIRST T 01 Name, F 4 for list Name, *LIBL, F 4 for list Name, *FIRST, F 4 for list A-Z 99, *ID File. . SCHOOL Library. . . AB 234 MRKA 1 Member. . . . *FIRST Format. . . . *FIRST File ID. . . . *ID Name, F 4 for list Name, *LIBL, F 4 for list Name, *FIRST, F 4 for list A-Z 99, *ID 26
OUTPUT selection ! REDIRECTION ! Select Output Type and Output Form Type choices, press Enter. Output type. . . 1 1=Display 2=Printer 3=Database file Form of output. . 1 1=Detail 2=Summary only Line wrapping. . N Wrapping width. . Record on one page. . . . N Y=Yes, N=No Blank, 1 -378 Y=Yes, N=No 27
The END 28
Скачать презентацию Accessing the AS 400 DATABASE 1 Preparation for
bf3b5dd89a250894d368288936453ef0.ppt