Week # 7 - Agenda • DFU Overview • Query/400 Overview • SDA Overview • Websphere Development Studio Client (WDSC) • File Transfer using Client Access & IFS
DFU (Data File Utility) • What is it? • Temporary vs Permanent specification • Access to both Physical & Logical files • File must first exist • Using STRDFU • Using CHGDTA & UPDDTA • Audit Log • Generates DDS source code • Walk-thru
Query/400 • What is it? • Output methods • Using STRQRY/WRKQRY/RUNQRY • Access to both Physical & Logical files • Query allows multiple file access • File Dump vs comprehensive formatted report • Object type • No Source code • Walk-thru
SDA Overview(Screen Design Aid) • What is it? • Using STRSDA • Display Files • Menus • Generates DDS source code • Walk-thru
Websphere Development Studio Client (WDSC) • What is it? • Remote System Explorer • Web Facing
File Transfer using Client Access • Client Access allows a User to transfer data in a file from a PC drive to a file on the iSeries or transfer data from a file on the iSeries to a file on a PC drive.
File Transfer using IFS • IFS(Integrated File system) allows a user to transfer/move/exchange data between the native iSeries database and PC-based files such as directory/stream files for transfer to other systems such as UNIX.
File Transfer using FTP • FTP (File Transfer Protocol) will allow a user to transfer data (in a file) from/to the iSeries to/from a PC platform.
Week # 7Lesson Overview • Learn enough about Data File Utility (DFU) to be able to create permanent DFU programs and use them to enter data records into a database file. • Learn enough about Query/400 to be able to create various Query/400 reports. • Learn Enough about Screen Design Aid (SDA) to create menus.
Week # 7 Data File Utility (Continued) • DFU (Data File Utility) provides a convenient, easy way to change records in and add records to a physical (or thru a logical) database file without the need to write an HLL data-entry program. • DFU is useful for entering and changing test data. • DFU is a valuable tool for programmers -- it’s another member of the Application Development ToolSet (ADTS) team and, with PDM and SEU, is available on any AS/400 on which program development or maintenance is done.
Week # 7 Data File Utility (Continued) Access DFU by using the STRDFU (Start DFU) command or choosing option 18, Change using DFU, on a database *FILE type object from the Work with Objects Using PDM screen. If you enter STRDFU command on any command line, the DFU menu appears; DFU either: a) Creates and executes a temporary entry/update program, or b) Builds and stores a “permanent” update program that can be called and used again.
Week # 7 Data File Utility (Continued) If you execute the STRDFU command, you can select option 5 from the DFU menu to update data using a temporary program. NOTE: Providing a temporary update program is also the approach the system takes when you select option 18 on a file from Work with Objects Using PDM.
Week # 7 Data File Utility (Continued) The advantage of a temporary DFU when working with externally described files is: You don’t need to specify a screen layout or field headers or edit and select fields. DFU generates a default update program using the record-format field-level attributes and file information stored in the data-file object.
Week # 7 Data File Utility (Continued) The disadvantages of temporary DFU programs are: 1. Lack of flexibility 2. The machine time required to recreate the program each time you need it.
Week # 7 Using a Temporary DFU Program Selecting option 5 from the DFU menu brings up an entry screen (DFU Update Data Using Temporary Program) that requests the name and member of the file to be updated. DFU remembers the last time you used a temporary program and provides that data-file name as a default value when you start another temporary DFU program from the menu.
Week # 7 Using a Temporary DFU Program (Continued) You might need to type over the old name, replacing it with the name of the file you want to use. Press Enter and DFU creates an update program using the field-level attributes from the file description of the file being maintained. Meanwhile, the message “DFU is creating temporary program QDZTD00001 for you to run” is displayed on the message line.
Week # 7 Starting DFU on an Empty File When you start DFU, using an empty data file, DFU’s Entry mode is active and displayed in the update screen’s upper-right corner. When you’re entering data, either the Field advance (Tab) or Field exit key moves the cursor to the next field. Character data is stored exactly as entered into the field (if you key leading blanks, the data is stored that way).
Week # 7 Starting DFU on an Empty File (Continued) Numeric data is treated as right-aligned within the field. A temporary DFU program doesn’t let you enter any editing characters (i.e., a decimal point or comma thousands separator) in a numeric field, and this restriction applies to dates and times stored as numbers. Date and time data (data types L and T) must be entered using the proper format and separator characters.
Week # 7 Starting DFU on an Empty File (Continued) NOTE: When the last field typed in is numeric and you do not enter the maximum number of digits, you must press Field exit or the Tab key to exit the field before pressing Enter. When you are ready to write the record to the file, press Enter key or F10 to save the record (Enter is easier to reach); the system stores the record and displays an empty entry screen for the next record. If after the fact you realize there was an error in the data, get out of entry mode and into change mode.
Week # 7 DFU Change Mode Function key F11 takes you to DFU’s change mode; for our examples, when you first reach change mode, the only input field on the screen will be the one named *RECNBR. All physical-file records are given a record number that indicates their order of entry; if you know the record number of the erroneous record, enter it in this field and DFU will retrieve it, if it exists, or use the Page up key to retrieve the last record entered.
Week # 7 DFU Change Mode (Continued) • From any record currently displayed in change mode, pressing the Page up key takes you to the next lower record number and pressing Page down takes you to the next higher record number. • Once the record has been corrected, continue to enter new records by returning to entry mode -- press function key F10 from change mode. • To delete (vs. change) a bad record, display the record in change mode and then press F23, and then F23 again to confirm.
Week # 7 DFU Change Mode (Continued) CAUTION: DFU enables other function keys besides those shown at the bottom of the screen but does not show F24=More Keys; however, you can see the entire list of enabled function keys by using context-sensitive Help. To take advantage of this help, move the cursor to any part of the function-key area and press F1.
Week # 7 Exiting DFU • Function key F3 takes you to the End Data Entry screen. • The End Data Entry screen reports the number of record adds, changes, and deletions and asks if you are finished. NOTE: If you reach this screen by mistake, return to the DFU data-entry screen by typing N over the default value of Y for the screen’s End data entry prompt.
Week # 7 Why DFU? The AS/400’s Data File Utility (DFU) (continued): • Program code is generated as you respond to a series of DFU entry screens • Is a handy tool for programmers who need to build or change test data files • Can save time over HLL application development for end users with fairly straightforward data-entry or file-update needs
Week # 7 Why DFU? (Continued) If your application requires any of the following, you’ll need to write an HLL program or use a more powerful utility: • Creating derived (virtual) fields for display by performing calculations on other fields • Field validation that requires testing the value of another field in the same record (e.g., HIREDATE>BIRTHDATE
Week # 7 Accessing DFU Enter the STRDFU (Start DFU) command on any command line. Option 5 on the initial menu (Update data using temporary program) creates a temporary DFU. Option 2 (Create a DFU program) lets you create a permanent DFU. Option 3 lets you change an existing DFU program. Option 4 lets you delete a permanent DFU program.
Week # 7 Creating a DFU Program Option 2 from the DFU menu takes you to the “Create a DFU Program” screen. Name the program you’ll create, the library in which the program will reside, and the data file the program will use. NOTE: The Library value for the Program parameter defaults to *CURLIB the first time you use option 2; after that, DFU uses the value from the previously created program.
Week # 7 Creating a DFU Program (Continued) The data file value will be blank (first time) or display previously used Data file name; if blank, the Library value defaults to *LIBL. With Program and Data file fields, use function key F4 to prompt for a list of existing programs or for a list of all physical and logical files in the specified library. Prompting requires a specific library reference (*LIBL won’t work).
Week # 7 Creating a DFU Program (Continued) NOTE: Attempting to select a Join file would result in an error because DFU does not permit the use of join logical files. When you select a file from a prompt list, the list values – Data files and *CURLIB for Library -- are filled in on the Create a DFU Program screen.
Week # 7 Defining General Information When you press Enter from a completed Create a DFU Program screen, the next screen depends on the type of data file you named. If the specified file is a nonindexed file, you get the Define General Information/Nonindexed File screen. For an indexed file (a file with a keyed-sequence access path), the display differs.
Week # 7 Defining General Information (Continued) Available options common to nonindexed AND indexed files (please reference the table for brief descriptions of each option listed below): • Job title • Display format • Audit report • S/36 style • Suppress errors • Edit numerics • Allow updates on roll
Week # 7 Defining General Information (Continued) Options specific to nonindexed files are (refer to table for brief descriptions of each): • Generate (record numbers) • Store in a field • Heading • Processing If an indexed file were specified for use by our DFU program, we would see a Define General Information/Indexed File screen, which offers options for the indexed file’s keys.
Week # 7 Defining General Information (Continued) The two options are: Generate: Enter Y if you want DFU to generate numeric keys in multiples of 10 for records added to the file; if the key field is a data field, take the default N. Changes allowed: Y permits the value of a data key field to be changed when the DFU is in change mode; when keys should not be changed, use N.
Week # 7 Defining General Information (Continued) For a single-column format selected, if all fields fit into one column, the screen will be formatted as single column even if Multiple or Maximum had been the chosen format. You can enter a Job title to clearly define the purpose and have chosen to print an audit report, selected edit numerics, allowed updates on a roll, and changed the record number heading -- the DFU program will process the file sequentially, with new records added to the end of the file.
Week # 7 Defining Audit Control andChoosing Record Formats After pressing Enter on the Define General Information/Nonindexed File screen, we get Define Audit Control screen because we chose to print an audit report. You can use this screen to change report width, spacing, and even the types of transactions to be printed. The Work with Record Formats screen is next. For a single record format, you can tell DFU to select fields using that format by typing option 2 next to the format name.
Week # 7 Selecting and Sequencing Fields From the Select and Sequence Fields screen, choose which fields to include on the DFU Update display by assigning them a sequence number (those not given a number will not appear). The relative magnitude of the sequence numbers determines the ordering of fields on the DFU Update display screen, with the lowest-numbered field occupying the top (or leftmost position) of the field list.
Week # 7 Selecting and Sequencing Fields (Continued) Press F21 to select all fields. Decide whether to use extended field definitions or special features, such as auto-duplicate, for any fields. Press F17 (Fast path) to go directly to DFU exit screen if no special features are chosen. Press Enter to use special features.
Week # 7 Specifying Extended Field Definitions Press Enter and the Work with Fields screen appears; this screen lists all the fields and lets you specify or delete extended definitions. Value N or Y under the Extended Definition column tells you whether an active extended definition currently exists for a field. Under Heading you can change the field identification that will appear on the DFU Change/Display screen.
Week # 7 Specifying Extended Field Definitions (Continued) Specify Extended Field Definition screen has these options for a numeric field: • Auto-duplicate • Accumulate • Extended field heading • Initial value (to specify starting value) • Auto-increment • Validity checks
Week # 7 Specifying Extended Field Definitions (Continued) Second page of Specify Extended Field Definition screen: First three options let you place the field at the beginning of a new line, require Field exit, and select a field that should be viewed only and not changed. The Non-display option lets you hide a sensitive field.
Week # 7 Specifying Extended Field Definitions (Continued) Second page of Specify Extended Field Definition screen (continued): You can specify spacing or use default spacing, and you can choose an edit code or edit word for numeric fields. When Edit numerics has been set to Y on the Define General Information screen, these edit codes work in entry mode; otherwise, editing is not used.
Week # 7 Specifying Extended Field Definitions (Continued) Codes permit different combinations of four editing variables: • Whether commas are used to separate hundred thousands, thousands, etc. • Whether decimal points are printed for non-integer numbers More...
Week # 7 Specifying Extended Field Definitions (Continued) • How a negative value is shown: • NS no sign • R- minus sign to the right • CR CR to the right • -F minus sign to the left (floats to first digit) • Whether a zero value is displayed (0 for integers or .00 for decimals) or is replaced with all spaces.
Week # 7 Specifying Extended Field Definitions (Continued) Predefined edit codes and values for variables: Decimal Negative Zero Code Commas Point ValueValue (blank) No No NS 0 all positions 1 Yes Yes NS 0 2 Yes Yes NS 3 No Yes NS 0 4 No Yes NS A Yes Yes CR 0 B Yes Yes CR C No Yes CR 0 D No Yes CR J Yes Yes R– 0 K Yes Yes R– L No Yes R– 0 M No Yes R– N Yes Yes –F 0 O Yes Yes –F P No Yes –F 0 Q No Yes –F Y No No NS 0/00/00
Week # 7 Specifying Extended Field Definitions (Continued) Predefined edit codes and values (continued): The main difference among the groups of Codes 1-4, A-D, J-M and N-Q lies in how they handle negative values; otherwise, comma insertion and print zero value patterns repeat from group to group. Y code is used to edit numeric integer fields whose values are dates -- it inserts slashes between month, day, and year values.
Week # 7 Specifying Extended Field Definitions (Continued) Specify Validity Checks screen offers several ways to check the validity of numeric fields. Mandatory entry: A value must be entered when the DFU program is in entry or insert mode -- select this option for any field that requires a value.
Week # 7 Specifying Extended Field Definitions (Continued) Mandatory fill requires all positions of a field to be entered, and the next two options check for valid self-checking numbers using either of two popular self-checking number algorithms, Modulus 10 and Modulus 11. Relational operator and List of values provide for comparing the contents of a field against a single value, a range of values, or a list of up to 20 values. (Any value entered in the field must test true for it to be accepted on input; if an invalid value is entered, DFU displays an error message and rejects the change or addition until a valid value is entered).
Week # 7 Specifying Extended Field Definitions (Continued) After completing the extended definition for fields, the changed Work with Fields screen appears. You can always take option 2 again if necessary to change a field’s extended definition. NOTE: There are differences between a screen for a numeric field and a screen for a character field. If the data entered for a field will contain lowercase characters, set the Allow lowercase option to Y. Validity checks are permitted for non-numeric fields, but with some differences from numeric fields.
Week # 7Specifying Extended Field Definitions (Continued) Y for Name check means the field must contain a valid AS/400-style name. Extended name check option permits lowercase a-z, but converts characters to upper case in the record unless the user encloses the entire name within double quotation marks. Both options are mutually exclusive, and neither can be used with range or list-of-values checks.
Week # 7 Specifying Extended Field Definitions (Continued) After extended field definitions have been taken, select other fields from the Work with Fields screen for extended definitions. Additional field definitions would be required for other fields, too.