Preparation and Accessing Data on the AS/400 Menus & Security,

1. Preparation and Accessing Dataon the AS/400Menus  & Security,

2. Types of Physical Files • Flat files - Arrival order only Sequential or Random access only • Sequential, Random, Keyed access

3. Keyed / Indexed FilesPhysical 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

4. What if we want to access the data in a different order? We can create new access path (‘s) to the data using Logical Files Type LF Called Views in WDSc and SQL

5. What is a Logical File? • It is a file which provides another way or viewof 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 PF’s

6. 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 usingindex key’s from akeyed record to point to a position in the physical file it is based on

7. Examples Physical And “based on” logical’s

8. The physical file – CSCSTPKeyed / indexed 0006.00 A*** file level... 0009.00 A UNIQUE 0012.00 A*** record level... 0017.00 R CSCSTPR 0020.00 *** field level... 0026.00 CUSTNO 6S 0 TEXT('Customer number') 0027.00 CFNAME 10 TEXT('First name') 0028.00 CLNAME 15 TEXT('Last name') 0029.00 CSTRET 20 TEXT('Street address') 0030.00 CCITY 15 TEXT('City') 0031.00 CSTATE 2 TEXT('State') 0032.00 CZIP 9S 0 TEXT('Zip+4') 0033.00 CPHONE 10S 0 TEXT('Phone') 0034.00 ORDDAT 8S 0 TEXT('Last order date') 0035.00 BALDUE 6S 2 TEXT('Balance due') 0038.00 *** primary key field name(s) go at the bottom with a type of K 0039.00 *** 0040.00 K CUSTNO ****************** End of data ****************************************

9. Logical – CSCSTL - Order 0001.00 * LF OF ALL CUSTOMER MASTER DATA BY NAME 0002.00 0003.00 * WHEN NO FIELDS ARE SPECIFIED IN THE LOGICAL FILE,THE RECORD 0004.00 * NAME IN THE LOGICAL FILE MUST BE THE SAME AS THE RECORD NAME 0005.00 * IN PHYSICAL FILE SPECIFIED ON THE PFILE KEYWORD. 0006.00 0007.00 A R CSCSTPR PFILE(CSCSTP) 0008.00 A K CLNAME 0009.00 A K CFNAME ****************** End of data **************************

10. Logical 1 - CSCSTL1 order with projection 0001.00 * LF FOR CUSTOMER MASTER BY LAST/FIRST NAMES 0003.00 * SPECIFYING FILE LEVEL: UNIQUE 0007.00 A R CUSREC PFILE(CSCSTP) 0009.00 * SELECTED FIELDS FROM PF 0010.00 A CUSTNO 0011.00 A CLNAME 0012.00 A CFNAME 0013.00 * SEQ BY LAST/FIRST NAME 0014.00 A K CLNAME 0015.00 A K CFNAME

11. Logical 2 - CSCSTL2order and selection 0001.00 * LF TO RANK CUSTOMERS WHO OWE MONEY BY THEIR BALANCE DUE 0002.00 0003.00 * WHEN NO FIELDS ARE SPECIFIED IN THE LOGICAL FILE,THE RECORD 0004.00 * NAME IN THE LOGICAL FILE MUST BE THE SAME AS THE RECORD NAME IN 0005.00 * PHYSICAL FILE SPECIFIED ON THE PFILE KEYWORD. 0006.00 0007.00 A R CSCSTPR PFILE(CSCSTP) * Note: No fields specified 0008.00 A K BALDUE 0009.00 A S BALDUE COMP(GT 0)

12. Another example of selectionusing a logical file (view) 0001.00 * units quantity and salesman group 0002.00 R PRDSPFR PFILE(PRDSPF) 0003.00 0004.00 K SPRDNO 0005.00 S SUSLD RANGE(50 100) 0006.00 S SLSMNO COMP(LE '100200') 0007.00 ****************** End of data *********************

13. Omission 0001.00 A R STUDR PFILE(STUDENTS) 0002.00 A STUDID 0003.00 A LASTNAME 0004.00 A K STUDID 0005.00 A O STUDID COMP(LT 333333333) ***************** End of data ************** Projection 0001.00 A R STUDR PFILE(STUDENTS) 0002.00 A STUDID 0003.00 A LASTNAME 0004.00 A K STUDID ****************** End of data *****************

14. 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 Type Text 14ARTRNSDF DSPF Assignment 2 transaction display file ASSG2DSP DSPF ASSG3DSP DSPF ASSG3DSPC2 DSPF

15. 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.) Query output redirection Using DFU, Data File Utility

16. Copy File (CPYF) The Copy File (CPYF) command copies all or part of a file from the database or from an external device to the database or to an external device. It can: o Copy data and source files between database files. Records can be copied from physical or logical files. However, records can be copied Only to physical files, not to logical files. o Copy data and source files from external devices, such as diskette and tape, to the database. o Copy data and source files from the database to external devices. o Copy data and source files from external devices to other external devices. o Copy data and source files from inline data files to the database or to external devices. ONLY REQUIRES *USE AUTHORITY TO THE COPIED OBJECT

17. Copy File (CPYF) Type choices, press Enter. From file . . . . . . . . . . . Name Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB To file . . . . . . . . . . . . Name, *PRINT Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB From member . . . . . . . . *FIRST Name, generic*, *FIRST, *ALL To member or label . . .. . *FIRST Name, *FIRST, *FROMMBR, *ALL Replace or add records .. *NONE *NONE, *ADD, *REPLACE... Create file . . . . . . . . . . *NO *NO, *YES Print format . . . . . . . . . . *CHAR *CHAR, *HEX

18. QUERY 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 Y=Yes, N=No Wrapping width . . . . . . . . . Blank, 1-378 Record on one page . . . . . . . N Y=Yes, N=No

19. Create Duplicate Object (CRTDUPOBJ) The Create Duplicate Object (CRTDUPOBJ) command copies a single object or a group of objects. It does not create an exact duplicate of files. You can copy a group of related objects by specifying a generic object name and the library in which the newly created objects are stored. Note: The public and private authorities for the duplicate object will be the same as the original object. The owner of the duplicate object is either the user profile of the user who issues the command or the group profile if the user profile of the user who issues the command is a member user profile that has specified that the group should be the owner.

20. Create Duplicate Object (CRTDUPOBJ) From object . . . . Name, generic*, *ALL From library . . . . Name, *LIBL, *CURLIB Object type . . . . *ALL, *ALRTBL, *AUTL... + for more values To library . . . . . . . *FROMLIB Name, *FROMLIB, *SAME... New object . . . . . *OBJ Name, *OBJ, *SAME From ASP device * Name, *, *CURASPGRP, SYSBAS To ASP device . . *ASPDEV Name, *ASPDEV, *... Needs *CHANGE OR *ALL authorityto the FROM object

21. Use these commands to see the attributes / properties • 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

22. 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 • Set the type of activity required • Entry (F10) to add records • Change mode (F11) to modify/change records • F23 to delete records (hidden function key) • PGUP, PGDN to see the previous or next record

23. DFU - STRDFU Work with Objects Using PDM ODIN Library . . . . . DA234H01A1 Position to . . . . . . . . Position to type . . . . . Type options, press Enter. 2=Change 3=Copy 4=Delete 5=Display 7=Rename 8=Display description 9=Save 10=Restore 11=Move ... 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 ===> strdfu or option 18in PDM use F23 to see F3=Exit F4=Prompt F5=Refresh F6=Create F9=Retrieve F10=Command entry F23=More options F24=More keys

24. 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 . . . . . . . . . ABMRK400 Name, *CURLIB Data file . . . . . . . . . STLST Name, F4 for list Library . . . . . . . . . ABMRK400 Name, *LIBL, *CURLIB

25. 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... F3=Exit F5=Refresh F12=Cancel F14=Display definition F17=Fast path F20=Renumber F21=Select all Press Enter to confirm or F17 to confirm with defaults.

26. 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: F3=Exit F5=Refresh F6=Select format F9=InsertF10=EntryF11=Change F23 to delete Confirm with F23 Page Up Down to see next previous record

27. Creating Menus using SDA updated Winter 2007

28. Menu’s Why? • Customize the system to your day to day needs • Guide users through applications • Collection of “MOST” favorable routines and functions

29. Accounts receivable ARMENU UUUUUUUUUU ARMENU Menu DD/DD/DD TT:TT:TT Select one of the following: 1. Invoicing 2. Cash Received 4. MAINTENANCE functions 5. Adjustments 7. Print Invoice Batch 8. Print Receipts 10. Reporting 90. End AR Selection or command F3=Exit F10=Work with commands F12=Cancel F13=Command area F20=Reverse F24=More keys

30. To Create a Menu... • STRSDA - Start SDA, Option 2- Design a Menu • Enter Source File name and menu name • Work with menu image and commands = Y • Specify constants and field attributes • Press F10 and enter commands • exit SDA and save

31. SDA-Created Menus • Menus consist of 3 parts: • the screen layout (the display file) • the Commands (stored in a Message file with an object type of *MSGF) • the Menu that ties it all together. (an object type of *MENU). Source for the menu is stored, by convention in the source physical file, QDDSSRC. • Therefore, 3 objects and 2 source members are created.

32. Source assigned / maintained in QDDSSRC 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 Type Text ARCUSTLF2 LF ASSIGNMENT 2 and in class 3/4 ARCUSTX PF ASSIGNMENT 2 and in class 3/4 ARMENU MNUDDS ARMENUQQ MNUCMD ARTRANPF PF ar transaction file OPS234 fall 2000 ARTRANPXY PF ar transaction file ARTRNS PF ASSIGNMENT 2 More...

33. ABERNS/QDDSSRC.ARMENU MNUDDS 0002.60 A 5 7'1.' 0002.70 A 6 7'2.' 0002.80 A 8 7'4.' 0002.90 A 9 7'5.' 0003.00 A 11 7'7.' 0003.10 A 12 7'8.' 0003.20 A 14 6'10.' 0003.40 A 019 2'Selection or command 0003.50 A ' 0003.60 A 1 71DATE 0003.70 A EDTCDE(Y) 0003.80 A COLOR(BLU) 0003.90 A 2 71TIME 0004.00 A COLOR(BLU) 0004.10 A 1 14USER 0004.20 A COLOR(BLU) 0004.30 A 5 12'Invoicing' 0004.40 A 6 12'Cash Received' 0004.50 A 9 12'Adjustments' 0004.60 A 11 12'Print Invoice Batch' 0004.70 A 12 12'Print Receipts' 0004.80 A 14 12'Reporting' 0004.90 A 17 6'90.' 0005.00 A 17 12'End AR' 0005.10 A 8 12'MAINTENANCE functions' 0000.01 A* Free Form Menu: ARMENU 0000.30 A DSPSIZ(24 80 *DS3 0000.40 A 27 132 *DS4) 0000.50 A CHGINPDFT 0000.60 A INDARA 0000.70 A PRINT(*LIBL/QSYSPRT) 0000.80 A R ARMENU 0001.00 A DSPMOD(*DS3) 0001.10 A LOCK 0001.20 A SLNO(01) 0001.30 A CLRL(*ALL) 0001.40 A ALWROL 0001.50 A CF03 0001.60 A HELP 0001.70 A HOME 0001.80 A HLPRTN 0001.90 A 1 2'ARMENU' 0002.00 A COLOR(BLU) 0002.10 A 1 35'ARMENU Menu' 0002.20 A DSPATR(HI) 0002.30 A COLOR(YLW) 0002.40 A 3 2'Select one of the following:' 0002.50 A COLOR(BLU)

34. ABERNS/QDDSSRC.ARMENUQQMNUCMD Columns . . . : 1 80 Browse ABERNS/QDDSSRC SEU==> ARMENUQQ FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 *************** Beginning of data ************************************** 0000.01 ARMENUQQ,1 070320 0000.10 0001 call AR010 070320 0000.11 0002 CALL AR020 070320 0000.12 0004 GO ARMAINT 070320 0000.13 0005 CALL AR810 070320 0000.14 0007 CALL AR300 070320 0000.15 0008 CALL AR350 070320 0000.16 0010 GO ARRPTS 070320 0000.17 0090 SIGNOFF 070320 ****************** End of data ***************************************

35. Testing Menus • GO menu_namee.g. GO STUMNU

36. Remember! • Enclose constants in quotations • Do NOT use the Backspace, Insert or Delete keys when designing your screen!!! • Use the arrow keys or mouse to move around • If it’s really bad, delete the field and start over!

37. system i Security • To use an object, you must have authority to it. • Having authority to a library (*lib object) does not automatically give you authority to the objects in the library. • You must have authority to a library before you can use objects in a library even if you have authority to those objects.

38. Who has Authority? • When an object is created, it automatically has 2 authorised users: • The owner • Everybody else i.e. *PUBLIC

39. Pre-Defined Object Authorities • *ALL: the user can do almost anything to the object. • *CHANGE: operational authority, data authorities • *USE: the user can use the object but not change it • *EXCLUDE: nothing is permitted

40. An Authorization List • A Security object which lists users and their respective authorities. • This list can be used to grant the users on the list, the authority listed, to a particular object or objects. • For example, if LAURIN is on an authorization list with *all authority, then LAURIN will have *all authority to any object secured by the list. • Helpful for authorizing a lot of users in one easy step. E.g. IBCPRF

41. How Do You give others Authority to Your Objects? • GRTOBJAUT: Grant object authority • RVKOBJAUT: Revoke Object authority • EDTOBJAUT: Edit object authority

42. GRTOBJAUT-grant object authority • Allows you to grant authority to one or more objects in a library • can specify individual users or an authorization listRemember, you must also give authority to library where the object is stored.

43. RVKOBJAUT-revoke object authority • Allows you to revoke authority from one or more objects in a library • can specify individual users or an authorization list • You must revoke the authority that was given in the first place e.g. if * change authority was given, then *change authority must be revoked (applies to individual users, not lists)

44. EDTOBJAUT-edit object authority • An all-encompassing command • Specify individual authorities • Specify Group Profile authorities • Specify Authorization List authorities • *PUBLIC is everyone else • EA is the PDM short form option

45. Security • Each object has 2 types of authorities: • object authorities • data authorities: data can be the information in a physical file, the objects in a library, the spool files in an output queue, messages in a message queue, etc

46. Object Authorities • Opr: look at object desc; do whatever data authority permits. • Mgt: Move, rename, crtdupobj, grtobjaut, add members • Exist: delete, save and restore operations • Alter: add, clear, change file attributes, chgpf • Ref: specify the object as parent file

47. Data Authorities • Read • Add • Update • Delete • Execute e.g. a pgm, a qrydfn, a dfu

48. Authorization Hierarchy • Owner • Explicit Authority • Authorization List • Group Profile • *PUBLIC

49. Authorizing Your Assignment • First, give authority to your assignment library • WRKLIBPDM DA233X99A3 • Use PDM option EA to invoke EDTOBJAUT command • Change authorization list to IBCPRF

50. Authorizing Your Assignment • Secondly, give authority to your objects in the assignment library • WRKOBJPDM DC233X99A3 • For each object: • Use PDM option EA to invoke EDTOBJAUT command • Change authorization list to IBCPRF • Or……..