Difference between revisions of "Information Systems:DFU"

From uniWIKI
Jump to navigation Jump to search
m (Norwinu moved page Information Systems:DFU to DFU without leaving a redirect)
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 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 [[Utilities#EZVIEW|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.
  +
  +
=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:22, 20 June 2016

DFU – Data File Utility

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.

EZVIEW - Easy View a File

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

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

‘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.

Retrieved from "https://owl.unipharm.com/mediawiki/index.php?title=Information_Systems:DFU&oldid=3181"