Difference between revisions of "Information Systems:DFU"
Jump to navigation
Jump to search
| Line 1: | Line 1: | ||
| − | =DFU – Data File Utility= |
||
| − | |||
| − | <div id="DFU"></div> |
||
'''DFU is a tool to do uncontrolled updates to (almost) any file! So proceed with extreme caution whenever you use it.''' |
'''DFU is a tool to do uncontrolled updates to (almost) any file! So proceed with extreme caution whenever you use it.''' |
||
| Line 144: | Line 141: | ||
=EZVIEW - Easy View a File= |
=EZVIEW - Easy View a File= |
||
| − | |||
| − | <div id="EZVIEW"></div> |
||
| − | |||
| − | The Easy View (EZVIEW) command will display virtually any AS/400 database file. Other functions, such as scan/replace, |
||
| − | change/delete, data base relations, modeling, etc, can be accessed as well, depending on the users AS/400 and/or Easy View authority. |
||
| − | |||
| − | DFU cannot be used on a file that has fields so long that they cannot fit on the display. An example of this is the cart file in Web Orders (XL_WEBSPT/PW_TRNF); it has a field that is 32,672 characters long. You must use EZVIEW to manually edit it. |
||
| − | |||
| − | Figure out the logical file that will allow you to access the record you want. In this example, we know the transaction number (cart number) for the ecommerce transaction file. |
||
| − | |||
| − | Key in EZ and press enter. |
||
| − | |||
| − | Easy View a File (EZVIEW) |
||
| − | |||
| − | Type choices, press Enter. |
||
| − | |||
| − | File Name: . . . . . . . . . . . pw_trnf Name, *PRV, *SELECT |
||
| − | Library Name: . . . . . . . . xl_webspt Name, *LIBL |
||
| − | Member Name: . . . . . . . . . . Character value |
||
| − | Access Method . . . . . . . . . *KEY *KEY, *ARRIVAL |
||
| − | Starting Key . . . . . . . . . . |
||
| − | |||
| − | Additional Parameters |
||
| − | |||
| − | Intial Display Mode . . . . . . *USRDFT *USRDFT, 1, 2, 3, 4, 5, 6... |
||
| − | RRN,Format,Member . . . . . . . *USRDFT *USRDFT, *RRN, *FORMAT... |
||
| − | Upper or Lower Case . . . . . . *USRDFT *USRDFT, *UPPER, *LOWER |
||
| − | |||
| − | F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display |
||
| − | F24=More keys |
||
| − | |||
| − | Fill in the file and library, then press enter again. |
||
| − | |||
| − | Rec. Length.: 32742 File: PW_TRNF Library: XL_WEBSPT Member: PW_TRNF |
||
| − | Rec. Count..: 2218 Access.: *KEY Mode..: INQUIRY |
||
| − | SCAN........: to -OR- *EQ |
||
| − | Field number: 1 field/text locate: |
||
| − | Lower Limits. 00000288claim 0002 US |
||
| − | |||
| − | Transacti Subtrans Name TXSEQN TXLIFE |
||
| − | 8641 288 claim 2 0 |
||
| − | 8623 288 claim 3 0 |
||
| − | 8664 288 claim 4 0 |
||
| − | 433 295 0 30 |
||
| − | 35 299 0 30 |
||
| − | 2029 299 claim 1 0 |
||
| − | 3617 299 claim 2 0 |
||
| − | 7687 299 claim 3 0 |
||
| − | 3172 299 regular 1 0 |
||
| − | 3173 299 regular 2 0 |
||
| − | 3174 299 regular 3 0 |
||
| − | 4147 299 regular 4 0 |
||
| − | 4115 299 regular 5 0 |
||
| − | 4260 299 regular 6 0 |
||
| − | 291 305 0 30 |
||
| − | 557 319 0 30 |
||
| − | F2=Previous Files F3=Exit F4=Select Mode F24=More Keys |
||
| − | |||
| − | You can go directly to the record you want by filling in the 'Lower Limits'. You can see that it is transaction, sub transaction name, and sequence number. Fill in those values for the record you want - making sure to use the same positions. Press enter. |
||
| − | |||
| − | Your options are - |
||
| − | |||
| − | C = CHANGE An update screen will appear, and you may |
||
| − | make changes to the record. |
||
| − | R = REPLICATE To create a new record based on the |
||
| − | data in the one next to the "R". |
||
| − | D (or 4) = DELETE To remove records from the file. |
||
| − | E = To display the record in EZFIELD format. |
||
| − | P = Print record. |
||
| − | S = Save record to clipboard |
||
| − | X = Export record to physical file. Use F23 to set up |
||
| − | the file, or just use X and you will be prompted. |
||
| − | |||
| − | =FTP – File Transfer Protocol= |
||
| − | |||
| − | (Wikipedia) The File Transfer Protocol (FTP) is a standard network protocol used to transfer computer files from one host to another host over a TCP-based network, such as the Internet. |
||
| − | |||
| − | You can run it from a green screen by keying FTP host-name. You will be asked for a user ID and password, then have access to this other computer through a command line. |
||
| − | |||
| − | We don’t do this in production; instead an RPG program will create a script of all the FTP commands required, then run it. We can do this with the command – |
||
| − | |||
| − | OVRDBF FILE(INPUT) TOFILE(QTEMP/QFTPSRCI) MBR(INPUT) |
||
| − | |||
| − | When the FTP command runs, instead of getting input from the keyboard, it will get it from this file. The following command will cause FTP to write any information or response to this file instead of the display. |
||
| − | |||
| − | OVRDBF FILE(OUTPUT) TOFILE(QTEMP/QFTPSRCO) MBR(OUTPUT) |
||
| − | |||
| − | You can see that this file is in the QTEMP library, which means its contents will be lost. We can run query EDQRP050 to output this file to the printer. |
||
| − | |||
| − | If the other FTP host is a Windows machine, we can access it with Explorer. |
||
| − | |||
| − | - Start Internet Explorer |
||
| − | |||
| − | - Click on Tools / Internet Options / Advanced |
||
| − | |||
| − | - Check ‘enable FTP folder view (outside of Internet Explorer)’ |
||
| − | |||
| − | - In address, key in FTP://host-name |
||
| − | |||
| − | - When prompted, key in your user ID and password |
||
| − | |||
| − | - You may get a view of the host, or a message that it cannot be displayed |
||
| − | |||
| − | - Click on View / Open FTP Site in File Explorer |
||
| − | |||
| − | - Again, when prompted, key in your user ID and password |
||
| − | |||
| − | - You will then see everything to which you are authorised |
||
| − | |||
| − | <div id="SQL"></div> |
||
| − | |||
| − | =SQL – Structured Query Language= |
||
| − | |||
| − | SQL (Structured Query Language) is a special purpose programming language designed for managing data held in a relational database management system (according to Wikipedia). |
||
| − | |||
| − | It can be used in place of the traditional coding of positioning, reading, writing, updating, and deleting records. The programmer does not have to decide which logical file to read; SQL will automatically select the best one based on the selections made. |
||
| − | |||
| − | An example of an SQL command is a request to list active items for Roy, alphabetically by description – |
||
| − | |||
| − | select * from xxitemp where ittype = ' ' and itstam = ' ' and itresp = 'ROYF' order by itdesc |
||
| − | |||
| − | Messages generated by the SQL optimiser are (to see these, you must start debug before STRSQL) – |
||
| − | |||
| − | **** Starting optimizer debug message for query . |
||
| − | All access paths were considered for file XXITEMP. |
||
| − | Access path of file XXITEML02 was used by query. |
||
| − | The query access plan has been rebuilt. |
||
| − | **** Ending debug message for query . |
||
| − | |||
| − | This record was added/updated to file QSYS2/SYSIXADV, which is a record of the indexes that the optimiser determined would be the best, but that didn’t exist. |
||
| − | |||
| − | TIMES LAST_ADVISED TABLE TABLE KEY_COLUMNS_ADVISED |
||
| − | ADVISED NAME SCHEMA |
||
| − | |||
| − | 3 2015-02-12-11.18.48.449058 XXITEMP UWDASWVOLD ITSTAM,ITTYPE,ITRESP,ITDESC |
||
| − | |||
| − | The best index didn’t exist, so SQL built one, based on the closest existing one, which was XXITEML02 (keyed by the full description ITDESC). If this SQL was to be run a lot, particularly on a large file, it would run much faster and use less resources, if we built the logical file shown. SQL would automatically detect and use it. |
||
| − | |||
| − | To see this file, run the query SHEILA1/SQL. |
||
| − | |||
| − | Display Report |
||
| − | Report width . . . . . : 521 |
||
| − | Position to line . . . . . Shift to column . . . . . . |
||
| − | ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+.. |
||
| − | TIMES_ADV LAST_ADVISED TABLE_NAME TABLE_SCHEMA KEY_COLUMNS_ADVISED |
||
| − | |||
| − | 1,529,298 2015-04-30-07.16.41.735584 SROORSPL UP1480BFVA OLLINE, OLORNO |
||
| − | 1,006,735 2015-04-30-09.02.56.891188 XXITEMP UWDASWPRDD ITDES1 |
||
| − | 1,004,018 2015-04-30-09.02.08.912312 IOPHDRP UWDASWPRDD IHSTAT, IHCUST |
||
| − | 900,569 2015-04-30-07.49.43.072707 RFORFCLG UP1480BFVA RCSROM, RCUSRFAT, RCHAND, RCDATE |
||
| − | 830,097 2015-04-30-09.03.00.058729 WSITEMP WEBPRDD ITSUPP, ITDIN |
||
| − | 792,597 2015-04-30-07.16.41.735584 WHOSUD UP1480BFVA SDSROM, SDTYPE, SDSGNO, SDSGLI |
||
| − | 756,469 2015-04-30-05.12.40.993270 SROORPPL UP1480BFVA OLSTAT, OLLINE, OLORNO, OLORDS |
||
| − | 691,517 2015-04-26-13.32.01.266089 QASZRAIRD QUSRSYS COMPID, FULLPATH |
||
| − | 586,470 2015-01-05-15.08.04.027398 WHOLTR UP1480BFVA LTSROM, LTPRDC, LTLZON, LTDATE |
||
| − | 524,723 2015-04-30-08.10.27.613593 SROBTR UP1480BFVA INREFX |
||
| − | 483,976 2015-04-30-09.02.57.427908 XXITEMP UWDASWPRDD ITSTAT, ITPRDC |
||
| − | 425,787 2015-04-30-08.53.22.115359 SROISDPL UP1480BFVA IDTYPP, IDAREA, IDIDAT, IDPAGR |
||
| − | 396,622 2015-04-30-07.49.43.072707 RFORFCLG UP1480BFVA RCHAND, RCDATE, RCSROM, RCUSRFAT |
||
| − | 396,622 2015-04-30-07.49.43.072707 RFORFCLG UP1480BFVA RCHAND, RCDATE, RCSROM, RCBAID |
||
| − | 339,796 2015-04-30-09.02.48.980335 OMSADRP UWDASWPRDD OARECP, OAMSGN |
||
| − | 329,019 2015-04-30-07.49.43.072707 RFORFCLG UP1480BFVA RCSROM, RCUSRFAT, RCHAND, RCDATE |
||
| − | 263,891 2015-04-30-05.12.41.523028 SROPCR UP1480BFVA PCSTAT, PCXRTY, PCNANO, PCXPRC |
||
| − | 255,817 2015-04-30-08.55.16.670031 SROORPHE UP1480BFVA OHORNO |
||
| − | |||
| − | F3=Exit F12=Cancel F19=Left F20=Right F21=Split F22=Width 80 |
||
| − | |||
| − | This sorts the file in descending sequence by number of times advised. I usually build the requested logical file when the number of times advised gets over a million. After building new logical files, change this query to omit those requests, by going into ‘Select Records’ and changing the last advised date to after the indexes were built. |
||
| − | |||
| − | Select Records |
||
| − | |||
| − | Type comparisons, press Enter. Specify OR to start each new group. |
||
| − | Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT... |
||
| − | |||
| − | AND/OR Field Test Value (Field, Number, 'Characters', or ...) |
||
| − | LASTADV GT '2015-05-01-00.00.00.000000' |
||
| − | ____ _______________ ____ __________________________________ |
||
| − | ____ _______________ ____ _________________________________ |
||
| − | ____ _______________ ____ _________________________________ |
||
| − | ____ _______________ ____ _________________________________ |
||
| − | |||
| − | __________________________________________________________________________________ |
||
| − | Field Text Len Dec |
||
| − | TIMESADV NUMBER OF TIMES THIS INDEX HAS BEEN ADVISED 18 0 |
||
| − | LASTADV LAST TIME THIS ROW WAS UPDATED 26 Z |
||
| − | TBNAME TABLE OVER WHICH AN INDEX IS ADVISED 258 V |
||
| − | DBNAME SCHEMA CONTAINING THE TABLE 10 |
||
| − | KEYSADV COLUMN NAMES FOR THE ADVISED INDEX 16000 V |
||
| − | More... |
||
| − | F3=Exit F5=Report F9=Insert F11=Display names only |
||
| − | F12=Cancel F13=Layout F20=Reorganize F24=More keys |
||
| − | |||
| − | The most effective use of SQL is in InfoNet, in the programs that allow the user to key in a combination of selection and summary options. The program builds a single SQL statement, and the optimizer determines the best index to use. Without SQL, the programmer would have to program for each possible combination of options. |
||
| − | |||
| − | For example, in ASW Hotline Inquiry, it took one minute to search for customer ‘11005’ and handler ‘SHEILAV’. The same thing in InfoNet took less than a second. (On the 525.) |
||
| − | |||
| − | =Query – SQL User Interface= |
||
| − | <div id="QUERY"></div> |
||
| − | |||
| − | ‘Query’ is a tool to make an SQL call easier to define, and to use. SQL (Structured Query Language) is a special purpose programming language designed for managing data held in a relational database management system (according to Wikipedia). However, ‘Query’ only uses the input functions; not updating or deleting. |
||
| − | |||
| − | On a command line in production ASW (so that you have the correct library list), key in WRKQRY and press enter. |
||
| − | |||
| − | Work with Queries |
||
| − | |||
| − | Type choices, press Enter. |
||
| − | |||
| − | Option . . . . . . 1 1=Create, 2=Change, 3=Copy, 4=Delete |
||
| − | 5=Display, 6=Print definition |
||
| − | 8=Run in batch, 9=Run |
||
| − | Query . . . . . . . test1 Name, F4 for list |
||
| − | Library . . . . . QTEMP Name, *LIBL, F4 for list |
||
| − | |||
| − | |||
| − | |||
| − | F3=Exit F4=Prompt F5=Refresh F12=Cancel |
||
| − | (C) COPYRIGHT IBM CORP. 1988 |
||
| − | |||
| − | Key in 1 to create a query, give it a meaningful name, and a library. If this is for production, use XX2480BP; for your own use, put it in your work library; if it is one time only, use QTEMP (which will be removed when you log off). Press enter. |
||
| − | |||
| − | Define the Query |
||
| − | |||
| − | Query . . . . . . : TEST1 Option . . . . . : CREATE |
||
| − | Library . . . . : QTEMP CCSID . . . . . . : 65535 |
||
| − | |||
| − | Type options, press Enter. Press F21 to select all. |
||
| − | 1=Select |
||
| − | |||
| − | Opt Query Definition Option |
||
| − | 1 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 |
||
| − | |||
| − | F3=Exit F5=Report F12=Cancel |
||
| − | F13=Layout F18=Files F21=Select all |
||
| − | |||
| − | This is a list of the options you can use to build your query. Key a ‘1’ in front of the option you want. You can select all the options you want – at one time, or select them one at a time. |
||
| − | |||
| − | Note that I am prefixing field names with the file ID. This can be done when you select more than one input file, but only must be done then two files have the same field name. |
||
| − | |||
| − | All the screens have ‘help’ (alt-F1). If there isn’t enough space on the screen for everything you want, you can press page down. |
||
| − | |||
| − | ==Specify file selections== |
||
| − | |||
| − | Specify File Selections |
||
| − | |||
| − | Type choices, press Enter. Press F9 to specify an additional |
||
| − | file selection. |
||
| − | |||
| − | File . . . . . . . . . XXITEMP Name, F4 for list |
||
| − | Library . . . . . . *LIBL 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 . . . . . . . . . srosro Name, F4 for list |
||
| − | Library . . . . . . *LIBL Name, *LIBL, F4 for list |
||
| − | Member . . . . . . . . *FIRST Name, *FIRST, F4 for list |
||
| − | Format . . . . . . . . *FIRST Name, *FIRST, F4 for list |
||
| − | File ID . . . . . . . T02 A-Z99, *ID |
||
| − | |||
| − | |||
| − | F3=Exit F4=Prompt F5=Report F9=Add file |
||
| − | F12=Cancel F13=Layout F24=More keys |
||
| − | |||
| − | Key in the name of the file you want to use, clear the library (this will default to *LIBL, which will use your library list to find the file; which is why you run this from production ASW), and press enter. You can press enter again, until you get back to the list of options, or press F9 to add another file (if, for example, you want to see item numbers, descriptions, and on hand quantities). Press enter. |
||
| − | |||
| − | Specify Type of Join |
||
| − | |||
| − | Type choice, press Enter. |
||
| − | |||
| − | Type of join . . . . . . . . 2 1=Matched records |
||
| − | 2=Matched records with primary file |
||
| − | 3=Unmatched records with primary file |
||
| − | |||
| − | |||
| − | F3=Exit F5=Report F10=Process/previous |
||
| − | F12=Cancel F13=Layout F18=Files |
||
| − | |||
| − | ‘2’ is the best choice here. It will include every record in the first file, whether or not there is a matching record in the second file. You can then select which records you want to use. Press enter. |
||
| − | |||
| − | Specify How to Join Files |
||
| − | |||
| − | Type comparisons to show how file selections are related, press Enter. |
||
| − | Tests: EQ, NE, LE, GE, LT, GT |
||
| − | |||
| − | Field Test Field |
||
| − | T01.ITPRDC EQ T02.SRPRDC_____ |
||
| − | ______________ _____ _______________ |
||
| − | ______________ _____ _______________ |
||
| − | ______________ _____ _______________ |
||
| − | ______________ _____ _______________ |
||
| − | ______________________________________________________________________________________ |
||
| − | Field Text Len Dec |
||
| − | T01.ITPRDC Item # 35 |
||
| − | T01.ITDES1 Description 1 50 |
||
| − | T01.ITDES2 Description 2 50 |
||
| − | T01.ITDESC Full Description 100 |
||
| − | T01.ITTYPE Type 1 |
||
| − | More... |
||
| − | F3=Exit F5=Report F10=Process/previous F11=Display names only |
||
| − | F12=Cancel F13=Layout F18=Files F24=More keys |
||
| − | |||
| − | On the top part of this screen you define the link between the two files; in this case item number. You can position your cursor to the bottom part of the screen, and press page down to see all the fields on both fields. Press enter. |
||
| − | |||
| − | ==Define Result Fields== |
||
| − | |||
| − | Define Result Fields |
||
| − | |||
| − | Type definitions using field names or constants and operators, press Enter. |
||
| − | Operators: +, -, *, /, SUBSTR, ||, DATE... |
||
| − | |||
| − | Field Expression Column Heading Len Dec |
||
| − | VALUE T02.SRSTHQ * T02.SRAPCO ___________________ __ __ |
||
| − | _________________________________ ___________________ |
||
| − | _________________________________ ___________________ |
||
| − | _________________________________ ___________________ |
||
| − | _________ _________________________________ ___________________ __ __ |
||
| − | _________________________________ ___________________ |
||
| − | _________________________________ ___________________ |
||
| − | _________________________________ ___________________ |
||
| − | ________________________________________________________________________________ |
||
| − | |||
| − | Field Text Len Dec |
||
| − | T02.SRSTHQ Quantity on hand 15 3 |
||
| − | T02.SRTSHQ Transit stock on hand 15 3 |
||
| − | T02.SRPURQ Quantity on purchase order 15 3 |
||
| − | T02.SRRETQ Quantity on return order 15 3 |
||
| − | More... |
||
| − | F3=Exit F5=Report F9=Insert F11=Display names only |
||
| − | F12=Cancel F13=Layout F20=Reorganize F24=More keys |
||
| − | |||
| − | On the top of the screen, you can key in a calculation; in this case, the value is the on hand quantity times the average cost. Again, you can position your cursor to the bottom part of the screen, and press page down to see all the fields on both fields. Press enter. |
||
| − | |||
| − | ==Select and Sequence Fields== |
||
| − | |||
| − | Select and Sequence Fields |
||
| − | |||
| − | Type sequence number (0-9999) for the names of up to 500 fields to |
||
| − | appear in the report, press Enter. |
||
| − | |||
| − | Seq Field Text Len Dec |
||
| − | 10 T01.ITAGRP Account Group 4 |
||
| − | 20 T01.ITTYPE Type 1 |
||
| − | 30 T01.ITPRDC Item # 35 |
||
| − | 40 T01.ITDESC Full Description 100 |
||
| − | 50 T01.ITSIZE Size 15 |
||
| − | 60 T01.ITRESP Buyer 10 |
||
| − | 70 T02.SRSROM Warehouse number 3 |
||
| − | 80 T02.SRSTHQ Quantity on hand 15 3 |
||
| − | 90 T02.SRAPCO Average purchase cost 17 4 |
||
| − | 100 VALUE T02.SRSTHQ * T02.SRAPCO 31 7 |
||
| − | T01.ITDES1 Description 1 50 |
||
| − | T01.ITDES2 Description 2 50 |
||
| − | |||
| − | |||
| − | F3=Exit F5=Report F11=Display names only F12=Cancel |
||
| − | F13=Layout F20=Renumber F21=Select all F24=More keys |
||
| − | |||
| − | Key in a sequence number for the fields you want to include. Page down to see all the fields available. Press enter. |
||
| − | |||
| − | ==Select Records== |
||
| − | |||
| − | Select Records |
||
| − | |||
| − | Type comparisons, press Enter. Specify OR to start each new group. |
||
| − | Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT... |
||
| − | |||
| − | AND/OR Field Test Value (Field, Number, 'Characters', or ...) |
||
| − | T02.SRSTHQ NE 0_____________________________ |
||
| − | AND T02.SRSROM EQ 'MAI'_________________________ |
||
| − | ____ ______________ ____ ______________________________ |
||
| − | ____ ______________ ____ ______________________________ |
||
| − | ____ ______________ ____ ______________________________ |
||
| − | ____ ______________ ____ ______________________________ |
||
| − | __________________________________________________________________________________ |
||
| − | Field Text Len Dec |
||
| − | T01.ITAGRP Account Group 4 |
||
| − | T01.ITTYPE Type 1 |
||
| − | T01.ITPRDC Item # 35 |
||
| − | T01.ITDESC Full Description 100 |
||
| − | T01.ITSIZE Size 15 |
||
| − | F3=Exit F5=Report F9=Insert F11=Display names only |
||
| − | F12=Cancel F13=Layout F20=Reorganize F24=More keys |
||
| − | |||
| − | Define which records are to be included. Here I am saying all items with quantities in the Main warehouse. |
||
| − | |||
| − | ==Select Sort Fields== |
||
| − | |||
| − | Select Sort Fields |
||
| − | |||
| − | Type sort priority (0-999) and A (Ascending) or D (Descending) for |
||
| − | the names of up to 32 fields, press Enter. |
||
| − | |||
| − | Sort |
||
| − | Prty A/D Field Text Len Dec |
||
| − | 1 A T01.ITAGRP Account Group 4 |
||
| − | 2 D VALUE T02.SRSTHQ * T02.SRAPCO 31 7 |
||
| − | ___ _ T01.ITTYPE Type 1 |
||
| − | ___ _ T01.ITPRDC Item # 35 |
||
| − | ___ _ T01.ITDESC Full Description 100 |
||
| − | ___ _ T01.ITSIZE Size 15 |
||
| − | ___ _ T01.ITRESP Buyer 10 |
||
| − | ___ _ T02.SRSROM Warehouse number 3 |
||
| − | ___ _ T02.SRSTHQ Quantity on hand 15 3 |
||
| − | ___ _ T02.SRAPCO Average purchase cost 17 4 |
||
| − | |||
| − | F3=Exit F5=Report F11=Display names only F12=Cancel |
||
| − | F13=Layout F18=Files F20=Renumber F24=More keys |
||
| − | |||
| − | This will show items in order by item account group, and within that, by highest value first. Press enter. |
||
| − | |||
| − | ==Specify Report Column Formatting== |
||
| − | |||
| − | Specify Report Column Formatting |
||
| − | |||
| − | Type information, press Enter. |
||
| − | Column headings: *NONE, aligned text lines |
||
| − | |||
| − | Column |
||
| − | Field Spacing Column Heading Len Dec Edit |
||
| − | T01.ITAGRP 0 Account 4 __ |
||
| − | Group___________________ |
||
| − | ________________________ |
||
| − | |||
| − | T01.ITTYPE 2 Type 1 __ |
||
| − | ________________________ |
||
| − | ________________________ |
||
| − | |||
| − | T01.ITPRDC 2 Item # 8 __ |
||
| − | ________________________ |
||
| − | ________________________ |
||
| − | |||
| − | |||
| − | F3=Exit F5=Report F10=Process/previous F12=Cancel |
||
| − | F13=Layout F16=Edit F18=Files F23=Long comment |
||
| − | |||
| − | You can change how the display, or report, will look. Here, I changed the heading from ‘VALUE’ to ‘Value’, and made the field lengths smaller. As Value was the result of a calculation, its field length and number of decimals was based on the on hand and average cost fields. It will never be that large, so it can be shortened for the report. ASW defines the item number field as 35 characters long, but we only use 8. Press enter. |
||
| − | |||
| − | ==Select Report Summary Functions== |
||
| − | |||
| − | Select Report Summary Functions |
||
| − | |||
| − | Type options, press Enter. |
||
| − | 1=Total 2=Average 3=Minimum 4=Maximum 5=Count |
||
| − | |||
| − | ---Options--- Field Text Len Dec |
||
| − | _ _ _ _ _ T01.ITAGRP Account Group 4 |
||
| − | _ _ _ _ _ T01.ITTYPE Type 1 |
||
| − | 5 _ _ _ _ T01.ITPRDC Item # 8 |
||
| − | _ _ _ _ _ T01.ITDESC Full Description 100 |
||
| − | _ _ _ _ _ T01.ITSIZE Size 15 |
||
| − | _ _ _ _ _ T01.ITRESP Buyer 10 |
||
| − | _ _ _ _ _ T02.SRSROM Warehouse number 3 |
||
| − | _ _ _ _ _ T02.SRSTHQ Quantity on hand 15 3 |
||
| − | _ _ _ _ _ T02.SRAPCO Average purchase cost 17 4 |
||
| − | 1 4 _ _ _ VALUE T02.SRSTHQ * T02.SRAPCO 9 2 |
||
| − | |||
| − | |||
| − | F3=Exit F5=Report F10=Process/previous F11=Display names only |
||
| − | F12=Cancel F13=Layout F18=Files F23=Long comment |
||
| − | |||
| − | I am saying that I want a count of how many items are on the list, the total value, and the highest individual total. |
||
| − | |||
| − | ==Define Report Breaks== |
||
| − | |||
| − | Define Report Breaks |
||
| − | |||
| − | Type break level (1-6) for up to 9 field names, press Enter. |
||
| − | (Use as many fields as needed for each break level.) |
||
| − | |||
| − | Break Sort |
||
| − | Level Prty Field Text Len Dec |
||
| − | 1 10 T01.ITAGRP Account Group 4 |
||
| − | _ T01.ITTYPE Type 1 |
||
| − | _ T01.ITPRDC Item # 8 |
||
| − | _ T01.ITDESC Full Description 100 |
||
| − | _ T01.ITSIZE Size 15 |
||
| − | _ T01.ITRESP Buyer 10 |
||
| − | _ T02.SRSROM Warehouse number 3 |
||
| − | _ T02.SRSTHQ Quantity on hand 15 3 |
||
| − | _ T02.SRAPCO Average purchase cost 17 4 |
||
| − | _ 20 VALUE T02.SRSTHQ * T02.SRAPCO 9 2 |
||
| − | |||
| − | F3=Exit F5=Report F10=Process/previous F11=Display names only |
||
| − | F12=Cancel F13=Layout F18=Files F23=Long comment |
||
| − | |||
| − | I want totals by item account group; as well as final totals. Press enter until back to the list of options. |
||
| − | |||
| − | ==Special Output Type and Output Form== |
||
| − | |||
| − | This is how to create a data file, which can be used as input to another Query, or to create a CSV file. |
||
| − | |||
| − | Select Output Type and Output Form |
||
| − | |||
| − | Type choices, press Enter. |
||
| − | |||
| − | Output type . . . . . . . . . . . 3 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 |
||
| − | |||
| − | |||
| − | F3=Exit F5=Report F10=Process/previous |
||
| − | F12=Cancel F13=Layout F18=Files |
||
| − | |||
| − | ‘Output type’ is 3 to create a file. ‘Form of output’ is 1 to write all the detail records to the file, or 2 to only write the totals and subtotals (the defined report breaks). |
||
| − | |||
| − | Define Database File Output |
||
| − | |||
| − | Type choices, press Enter. |
||
| − | (The printed definition shows the output file record layout.) |
||
| − | |||
| − | File . . . . . . . . . testfile Name, F4 for list |
||
| − | Library . . . . . . QTEMP Name, F4 for list |
||
| − | Member . . . . . . . . *FILE Name, *FIRST, *FILE, *ALL, |
||
| − | F4 for list |
||
| − | Data in file . . . . . 2 1=New file, 2=Replace file |
||
| − | 3=New member, 4=Replace member |
||
| − | 5=Add to member |
||
| − | |||
| − | For a new file: |
||
| − | Authority . . . . . *LIBCRTAUT *LIBCRTAUT, authorization list name, |
||
| − | *CHANGE, *ALL, *EXCLUDE, *USE |
||
| − | |||
| − | |||
| − | Text . . . . . . . . test file_________________________________________ |
||
| − | Print definition . . . N Y=Yes, N=No |
||
| − | |||
| − | F3=Exit F4=Prompt F5=Report F10=Process/previous |
||
| − | F12=Cancel F13=Layout F18=Files |
||
| − | |||
| − | Give the file a meaningful name and text. If you want to keep this file for production, use library XX2480BP; for your own use, put it in your work library; if it is one time only, use QTEMP (which will be removed when you log off). Press enter. |
||
| − | |||
| − | If you want to print a report – |
||
| − | |||
| − | Select Output Type and Output Form |
||
| − | |||
| − | Type choices, press Enter. |
||
| − | |||
| − | Output type . . . . . . . . . . . 2 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 |
||
| − | |||
| − | |||
| − | F3=Exit F5=Report F10=Process/previous |
||
| − | F12=Cancel F13=Layout F18=Files |
||
| − | |||
| − | Define Printer Output |
||
| − | |||
| − | Type choices, press Enter. |
||
| − | |||
| − | Printer . . . . . . . . . *PRINT *PRINT, name |
||
| − | |||
| − | Form size: |
||
| − | Length . . . . . . . . . Blank, 1-255 |
||
| − | Width . . . . . . . . . 132 Blank, 1-378 |
||
| − | |||
| − | Start line . . . . . . . . Blank, 1-255 |
||
| − | |||
| − | End line . . . . . . . . . Blank, 1-255 |
||
| − | |||
| − | Line spacing . . . . . . . 1 1, 2, 3 |
||
| − | |||
| − | Print definition . . . . . N Y=Yes, N=No |
||
| − | |||
| − | |||
| − | |||
| − | F3=Exit F5=Report F10=Process/previous |
||
| − | F12=Cancel F13=Layout F18=Files |
||
| − | |||
| − | The form width depends on how much data you are including (you can see by pressing F13 to show the layout). If you can go as low as 80 the report will print in portrait, with a larger font. |
||
| − | |||
| − | ==Notes== |
||
| − | |||
| − | To get the current date – |
||
| − | |||
| − | WORK current(date) |
||
| − | |||
| − | To get last month’s date (not exactly, as months can be from 28 to 31 days; so this only works up to the 29th) – |
||
| − | |||
| − | WORK date(days(current(date))-28) |
||
| − | |||
| − | To build from and to dates to search for records dated the previous month (the ‘TO’ value won’t always be correct, but it will work) – |
||
| − | |||
| − | FROM year(work)* 10000 + month(work) * 100 + 01 |
||
| − | TO year(work)* 10000 + month(work) * 100 + 31 |
||
| − | |||
| − | =Create CSV File from Data Base file= |
||
| − | |||
| − | On a command line, key in CPYTOIMPF and press F4 to prompt. |
||
| − | |||
| − | Copy To Import File (CPYTOIMPF) |
||
| − | |||
| − | Type choices, press Enter. |
||
| − | |||
| − | From file: |
||
| − | File . . . . . . . . . . . . . test Name |
||
| − | Library . . . . . . . . . . qtemp Name, *LIBL, *CURLIB |
||
| − | Member . . . . . . . . . . . . *FIRST Name, *FIRST, *ALL |
||
| − | To data base file: |
||
| − | File . . . . . . . . . . . . . Name |
||
| − | Library . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB |
||
| − | Member . . . . . . . . . . . . *FIRST Name, *FIRST, *FROMMBR, *ALL |
||
| − | To stream file . . . . . . . . . uwd staging/sheilav/test.csv_________________ |
||
| − | Replace or add records . . . . . *replace *ADD, *REPLACE |
||
| − | From CCSID . . . . . . . . . . . *FILE 1-65533, *FILE |
||
| − | |||
| − | Press enter to get more parameters. |
||
| − | |||
| − | Stream file CCSID . . . . . . . *PCASCII 1-65533, *STMF, *PCASCII... |
||
| − | |||
| − | Again, press enter for more parameters. |
||
| − | |||
| − | Stream file authority . . . . . *DFT *DFT, *INDIR, *FILE... |
||
| − | Record delimiter . . . . . . . . *CRLF Character value, *EOR... |
||
| − | Record format of import file . . *DLM *DLM, *FIXED |
||
| − | |||
| − | Press enter to run the command. |
||
Revision as of 16:23, 20 June 2016
DFU is a tool to do uncontrolled updates to (almost) any file! So proceed with extreme caution whenever you use it.
DFU cannot be used on a file that has fields so long that they cannot fit on the display. An example of this is the cart file in Web Orders (XL_WEBSPT/PW_TRNF); it has a field that is 32,672 characters long. You must use EasyView (EZ) to manually edit it.
Some item files have ‘trigger programs’ attached. This is so that changes to them will also change the consolidated item master file. When you use DFU to change any of these, you must have the correct library list, either production or test, to match the file you are using. See Programming / Trigger Programs for a list of these files. Note that even though only the physical files are listed here, logical files are also affected.
These examples use the production inventory transaction file. Replace the library and file name with whatever you want.
You can start DFU directly by keying in the command STRDFU and pressing F4.
Start DFU (STRDFU)
Type choices, press Enter.
Option:
DFU option . . . . . . . . . . 5 1-5, *SELECT
Run option . . . . . . . . . . *NONE 1-2, *NONE
DFU program . . . . . . . . . . *PRV Name, *PRV
Library . . . . . . . . . . . *PRV Name, *PRV, *LIBL, *CURLIB
Data base file . . . . . . . . . SRBITR Name, *PRV, *SAME
Library . . . . . . . . . . . UP1480BFVA Name, *PRV, *LIBL, *CURLIB
Member . . . . . . . . . . . . . *PRV Name, *PRV, *FIRST
F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display
F24=More keys
DFU option – 5 to update data using temporary program
Data Base File and Library – the file you wish to either view or change.
Press Enter.
Or you can navigate through PDM (Programming Development Manager).
On the command line, key in WRKOBJPDM UP1480BFVA and press enter.
Work with Objects Using PDM BART
Library . . . . . UP1480BFVA Position to . . . . . . . . srbitr
Position to type . . . . . *file
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
__ FMR017 *PGM RPGLE G/L transactions enquiry and reconcil
__ AMTADM *DTAQ AMT administration data queue
__ AMTALR *DTAQ AMT alert data queue
__ ASWDTAQOFL *DTAQ DPR-Order flow
__ ASWDTAQ1 *DTAQ DPR-Sales
__ ASWDTAQ2 *DTAQ DPR-Purchase
__ ASWDTAQ3 *DTAQ DPR-Other
__ AABABA *FILE LF Asset balance - Int seq/Asset no/Seq
More...
Parameters or command
===> _______________________________________________
F3=Exit F4=Prompt F5=Refresh F6=Create
F9=Retrieve F10=Command entry F23=More options F24=More keys
(C) COPYRIGHT IBM CORP. 1981, 2007.
For ‘position to’ key in SRBITR and for ‘position to type’ key in *FILE, then press enter.
Work with Objects Using PDM BART
Library . . . . . UP1480BFVA 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
18 SRBITR *FILE LF Inventory transactions
__ SRBITS *FILE LF Incident Time Stamps
__ SRBIVC *FILE LF Invoice credit card payments
__ SRBIVS *FILE LF Invoice settlements
__ SRBIXI *FILE LF Inventory management info defaults &
__ SRBJBN *FILE LF Job lock - control submitted job
__ SRBJFPRT *FILE LF ASW JetForm valid printers table
__ SRBJLC *FILE LF Job name (50 char)
More...
Parameters or command
===> _____________________________________________
F3=Exit F4=Prompt F5=Refresh F6=Create
F9=Retrieve F10=Command entry F23=More options F24=More keys
Key ’18’ in front of file SRBITR and press enter.
WORK WITH DATA IN A FILE Mode . . . . : CHANGE
Format . . . . : ITR File . . . . : SRBITR
Inventory event code: SOC
Ord no: 9000000
Ord line: _____
Non-Conf. Cert. No.: _______
Batch: ____________
F3=Exit F5=Refresh F6=Select format
F9=Insert F10=Entry F11=Change
What you see on this screen will depend on the file. This is prompting you for the record you want. For example, key SOC for ‘Inventory event code’ and 9000000 for ‘Ord no’, then press enter. This will position to that place in the index, but in this case, there is not a record with that exact key, so a record is not displayed. Press page down to read the record with the next highest key. Or page down to read the record with the next lowest key.
WORK WITH DATA IN A FILE Mode . . . . : CHANGE
Format . . . . : ITR File . . . . : SRBITR
Inventory event code: SOC
Ord no: 9981273
Ord line: 10
Non-Conf. Cert. No.: _______
Batch: _____________
Stat: _ Inv tran type: 800
Item: 02094803
Whse: RET
Qty: 1000
Description: Sales order, credit
Date: 20140604
Subm time: 152115 Sum trns: _
Transit deliv. Y/N: N
P l no: _______
P L line no: _____
Pick cons. no: _______
Shipm. no: _______
F3=Exit F5=Refresh F6=Select format
F9=Insert F10=Entry F11=Change
There may be too many fields on the record to fit on the display. If so, pressing enter will show more. When all fields have been show, the screen prompting for the key will be shown again.
If you are making a change to a record that takes more than one screen to show all its fields, you must either keep pressing enter until you see the screen prompting for the key, or press page up. If you don’t do this, the change will
If you are making a change to a record that takes more than one screen to show all its fields, you must either keep pressing enter until you see the screen prompting for the key, or press page up. If you don’t do this, the change will not update the record.
You can add a record by pressing F10. To make sure you get everything, copy from an existing record. When you are done, you can press F11 to get back to change mode.
You can delete the record shown by pressing F23 twice. Be careful.
When you are done, press F3 to exit. You will see a count of how many records you have added, changed, or deleted. Press enter.