|
 
| |
WRKDBF Freeware Utility
Okay here goes... (I attached the code as text docs)
http://www.code400.com/forum/showthread.php?threadid=108&highlight=COMMIT
I am not an expert here but this is what I have
here is my physical file
Code:
A R FILE1R
A*
A F1FLD1 10 COLHDG('Field One')
A F1FLD2 10 COLHDG('Field Two')
A*
A K F1FLD1
Okay now the CL program the start the whole mess
Code:
PGM
DCL VAR(&ERROR) TYPE(*CHAR) LEN(1)
MONMSG MSGID(CPF0000)
CRTJRNRCV JRNRCV(QGPL/JRNFILE1)
CRTJRN JRN(QGPL/JRNFILE1) JRNRCV(QGPL/JRNFILE1)
STRJRNPF FILE(FILE1) JRN(QGPL/JRNFILE1) IMAGES(*BOTH)
STRCMTCTL LCKLVL(*ALL)
CALL PGM(COMMITR) PARM(&ERROR)
IF COND(&ERROR *EQ 'Y') THEN(DO)
ROLLBACK
GOTO EXIT
ENDDO
COMMIT
EXIT: ENDCMTCTL
ENDPGM
you only need create the journal and journal reciever once...
but for this example it tries every time.
Now the RPG....The error flag defaults to 'Y' meaning when
you leave the RPG via F3 the records written to file FILE1 will be
ROLLEDBACK meaning removed from the file.
Code:
fCOMMITD cf e workstn
fFILE1 uf a e k disk commit
f usropn
d DisplayScrn1 S n inz('1')
d CmdString S 256
d CmdLength S 15 5
d OutError S 01
*
c open FILE1
*
c eval S1ERROR = 'Y'
*
c dow DisplayScrn1 = *On
*
c exfmt SCRN1
c select
c when *in03
c
c eval DisplayScrn1 = *Off
c other
c eval F1FLD1 = S1FLD1
c eval F1FLD2 = S1FLD2
c write FILE1R
c clear S1FLD1
c clear S1FLD2
c endsl
*
c enddo
*
c eval OutError = S1ERROR
c eval *Inlr = *On
*----------------------------------------------- ***
* *Inzsr - One time run auto subroutine
*----------------------------------------------- ***
c *INZSR begsr
c *entry plist
c parm OutError
c endsr
So write three or so records...Leave the display up(do not press F3)
open a second session and use DBU/DFU/WRKDBF/QUERY...
to check the file....You will see your records.
Now F3 Outta the program and the records will be removed.
Call the CL again this time change error flag to 'N'
records will be written then commited in the CL.
|
FTP Instructions
The WRKDBFR.EXE file you have downloaded from the
web site is a self-extracting Zip file that, when unzipped, should
contain five (5) files:
1. WRKDBF RISC FTP Installation Instructions (this document)
2. WRKDBF RISC Folder Installation Instructions
3. User Documentation
4. Summary of changes in the new version
5. WRKDBFR.SAV file. This AS/400 save file contains the WRKDBF
product library that must be uploaded to the AS/400, as described
below:
PREREQUISITES
WRKDBF (the RISC version) requires a RISC-based AS/400 running under
OS/400 version V3R7M0 or higher
INSTALLATION INSTRUCTIONS (VIA FTP)
1. Unzip the WRKDBFR.SAV save file by double-clicking on the
self-extracting Zip file WRKDBFR.EXE
2. On the AS/400, create a save file
CRTSAVF FILE(QGPL/WRKDBFR)
3. Upload the PC file from your PC to the AS/400:
Open an MS-DOS window on your PC
Type ftp to start a File Transfer Protocol session
Type lcd c:\temp (or whatever local directory contains the
wrkdbfr.sav)
Type open xxx.xxx.xxx.xxx (where the x’s are the TCP/IP address of
the AS/400)
Enter your user id and password
Type bin to set the transfer mode to binary IMAGE
Type cd qgpl to change to the AS/400 Library where you created the
save file in step 2 above
Type put wrkdbfr.sav to upload the .sav to the AS/400 Library
Type quit to exit the ftp session
1. If you have previously installed WRKDBF and you have been saving
Field Sequences, you should move your saved Field Sequences file to
another library (such as QGPL) so it doesn't get overlaid by the
install:
MOVOBJ OBJ(WRKDBF/DBPFLDSQ) OBJTYPE(*FILE) TOLIB(QGPL)
2. Restore the WRKDBF product library containing the WRKDBF objects:
RSTLIB SAVLIB(WRKDBF) DEV(*SAVF) SAVF(QGPL/WRKDBFR)
3. If you do not have the WRKDBFSQL library, restore it to your
system:
RSTLIB SAVLIB(WRKDBFSQL) DEV(*SAVF) SAVF(WRKDBF/WRKDBFSQL)
7. Move the three commands in the WRKDBF library to library QGPL:
MOVOBJ OBJ(WRKDBF/WRKDBF) OBJTYPE(*CMD) TOLIB(QGPL)
MOVOBJ OBJ(WRKDBF/WRKDBFSQL) OBJTYPE(*CMD) TOLIB(QGPL)
MOVOBJ OBJ(WRKDBF/WRKDBFUNDL) OBJTYPE(*CMD) TOLIB(QGPL)
8. If you moved your saved Field Sequences file in step 4 above, you
should copy your saved Field Sequences back and then delete the file
as follows:
CPYF FROMFILE(QGPL/DBPFLDSQ) TOFILE(WRKDBF/DBPFLDSQ)
MBROPT(*REPLACE)
DLTF FILE(QGPL/DBPFLDSQ)
|
WRKDBF
“Work with Database File”
Version 10.10.03
User Documentation
Written by: William K. Reger
For: Levitz Furniture Corporation
Phone: (561) 994-5114
Email Address: BReger@Levitz.com
WRKDBF Web Site: http://www.wrkdbf.com
Version: V10.10.03
1999, 2000 Levitz Furniture Corporation 1. What is WRKDBF? 3
2. Features and Restrictions of WRKDBF 4
3. Installation and Prerequisites 7
3.1 PREREQUISITES 7
3.2 FTP INSTALLATION INSTRUCTIONS 7
3.3 FOLDER INSTALLATION INSTRUCTIONS 9
4. WRKDBF QuickStart 11
4.1 The WRKDBF Command in Detail 11
4.2 National Language Support 13
5. “Work with Database File” Panel 14
5.1 General Information about the “Work with Database File” Panel 14
5.2 Information at the top of the “Work with Database File” Panel 15
5.3 The WRKDBF Command Line 15
5.4 “Work with Database File” Record Options 16
5.5 “Work with Database File” Function Keys 16
5.6 “Work with Database File” on 27x132 or 24x80 character displays
17
5.7 “Undeleting” database records 18
6. “Change a Database Record” Panel 19
6.1 General Information about the “Change a Database Record” Panel
19
6.2 “Change a Database Record” Function Keys 20
7. “Select Field Sequence to Display” Panel 21
7.1 General Information about “Select Field Sequence to Display”
Panel 21
7.2 “Select Field Sequence for Display” Function Keys 21
7.3 Saving and Restoring Field Sequences 23
8. Executing SQL Commands 25
8.1 General Information about the SQL Command Panels 25
1. What is WRKDBF?
WRKDBF (“Work with Database File”) is a powerful AS/400 File Editor
designed to allow you to easily display and maintain records within
AS/400 database files. It greatly enhances a developers ability to
test, debug, and maintain database files. Originally written in
1992, it has been continuously improved and enhanced ever since. It
is a full-featured and mature utility that is used every day. It is
in use worldwide and has been downloaded from the Internet thousands
of times.
WRKDBF is written almost entirely in RPG ILE and CL ILE. Although it
does use a small MI routine for “undeleting” database records, it
does not operate outside of normal OS/400, so you can be assured
that nothing unusual is being done. It fully operates under standard
AS/400 security rules. If you’re not authorized to update a file,
you won’t be able to update it with WRKDBF either.
WRKDBF is available to the entire AS/400 community as “freeware”.
WRKDBF is distributed with the executable objects only, without
source code.
Levitz Furniture Corporation retains the source code and all
copyrights to WRKDBF but grants you the rights to use the original
unchanged executable objects on any and all AS/400’s that you may
have. The only restriction is that you may not distribute WRKDBF in
whole or as part of any other software for the purposes of monetary
gain. If we allow you to use it for free, the least we can expect is
that everyone else also gets to use it for free.
WRKDBF is distributed on an “AS-IS” basis. While it has been used
successfully for many years in many different situations, no
warranty or guarantees can be made. By downloading WRKDBF, you
assume sole responsibility for it’s use on your AS/400 systems and
databases and agree not to hold Levitz responsible for any system
failures and/or problems that may result from the use of WRKDBF.
If you have any questions, comments, or requests for enhancements
please feel free to contact us via email at BReger@Levitz.com. We
are always looking for ways to make this utility even better! 2.
Features and Restrictions of WRKDBF
Multiple records are displayed on the initial panel allowing you to
see up to 15 records at a time, with Roll keys available
Multiple fields with column headings are displayed horizontally
across the initial panel. Window Left and Right functionality is
provided, as is the ability to specify absolute field positioning
Individual fields can be repositioned, reordered, hidden and/or
“locked down” (“locking down” is the ability to keep fields on the
panel while windowing the panel left or right). These field
positions can also be saved, given a name, and retrieved for use
again at a later time
File positioning is available by full or partial Keys (if the file
is keyed) or by Relative Record Number in arrival sequence. When
accessing a file by Key, the Keys can optionally be defined to be
“restrictor” keys (read equal type)
Both 27x132 and 24x80 column displays are supported
File information is provided such as:
Library, File, Member, and Format names
Object Text
Number of records
Object size in bytes
Maximum record length
Individual database records can be:
Displayed
Changed
Copied
Deleted
Undeleted
Printed
New database records can be Added
A powerful SQL capability is provided allowing you to operate upon
more than one record at a time. This is fully implemented with
normal OS/400. No separate SQL product is required. The SQL commands
enabled are:
COUNT
DELETE
INSERT
SELECT
UPDATE
Multiple format logical files are fully supported (including update
capability) with proper column headings shown for each record format
DDM files are fully supported
Data field values are displayed in their “natural” way, meaning
packed fields are displayed to you unpacked, binary fields are
converted, numeric fields are shown with a decimal (if appropriate),
etc.
A hexadecimal view is available so you can display and maintain
records using the internal formats of individual fields
ISO Date, Time, and Timestamp fields are fully supported
Floating point (scientific notation) fields are fully supported
(RISC version required)
Variable length fields are fully supported and displayed to you
without the binary length, once again the data is displayed in it’s
“natural” way
A “Find” function is available to help you find the records you wish
to work with. The “Find” function is not case-sensitive
“Flat files” (such as S/36 files and PC files) are fully supported
with the capability available to you to define the field formats so
that the file appears to be externally described, rather than having
one long data field
Generic file names (such as CUSTMAS* or *ALLUSR/POMAST) will present
you with a list of files matching the generic criteria, from which
you can select a file to work with
The Database Relations of a file can be used to select a file to
work with
The File Member to be used can be selected from a list of all
Members
An Audit report capability is available to automatically print out
any changes made with WRKDBF
You can change to a different File/Library/Member from within the
main panel, without having to first exit out
National Language support is provided
At the present time the following data types are either not
supported or have not been tested:
DBCS fields
NULL-capable fields (RPG restriction, sorry!)
Graphic data types
Any future data type that IBM may dream up!
3. Installation and Prerequisites
The WRKDBFR.EXE file (or WRKDBFC.EXE for CISC) you have downloaded
from the web site is a self-extracting Zip file that, when unzipped,
should contain five (5) files:
1. WRKDBF FTP Installation Instructions
2. WRKDBF Folder Installation Instructions
3. User Documentation (this document)
4. Summary of changes in the new version
5. WRKDBFR.SAV file (or WRKDBFC.SAV). This AS/400 save file contains
the WRKDBF product library that must be uploaded to the AS/400, as
described below:
3.1 PREREQUISITES
WRKDBF requires either a RISC-based AS/400 running under OS/400
version V3R7M0 or higher, or a CISC-based AS/400 running version
V3R2M0 or higher.
3.2 FTP INSTALLATION INSTRUCTIONS
1. Unzip the WRKDBFR.SAV save file by double-clicking on the
self-extracting Zip file WRKDBFR.EXE
2. On the AS/400, create a save file:
CRTSAVF FILE(QGPL/WRKDBFR)
3. Upload the PC file from your PC to the AS/400:
Open an MS-DOS window on your PC
Type ftp to start a File Transfer Protocol session
Type lcd c:\temp (or whatever local directory contains the
wrkdbfr.sav)
Type open xxx.xxx.xxx.xxx (where the x’s are the TCP/IP address of
the AS/400)
Enter your user id and password
Type bin to set the transfer mode to binary IMAGE
Type cd qgpl to change to the AS/400 Library where you created the
save file in step 2 above
Type put wrkdbfr.sav to upload the .sav to the AS/400 Library
Type quit to exit the ftp session
1. If you have previously installed WRKDBF and you have been saving
Field Sequences, you should move your saved Field Sequences file to
another library (such as QGPL) so it doesn't get overlaid by the
install:
MOVOBJ OBJ(WRKDBF/DBPFLDSQ) OBJTYPE(*FILE) TOLIB(QGPL)
2. Restore the WRKDBF product library containing the WRKDBF objects:
RSTLIB SAVLIB(WRKDBF) DEV(*SAVF) SAVF(QGPL/WRKDBFR)
3. If you do not have the WRKDBFSQL library, restore it to your
system:
RSTLIB SAVLIB(WRKDBFSQL) DEV(*SAVF) SAVF(WRKDBF/WRKDBFSQL
7. Move the three commands in the WRKDBF library to library QGPL:
MOVOBJ OBJ(WRKDBF/WRKDBF) OBJTYPE(*CMD) TOLIB(QGPL)
MOVOBJ OBJ(WRKDBF/WRKDBFSQL) OBJTYPE(*CMD) TOLIB(QGPL)
MOVOBJ OBJ(WRKDBF/WRKDBFUNDL) OBJTYPE(*CMD) TOLIB(QGPL)
8. If you moved your saved Field Sequences file in step 4 above, you
should copy your saved Field Sequences back and then delete the file
as follows:
CPYF FROMFILE(QGPL/DBPFLDSQ) TOFILE(WRKDBF/DBPFLDSQ)
MBROPT(*REPLACE)
DLTF FILE(QGPL/DBPFLDSQ)
3.3 FOLDER INSTALLATION INSTRUCTIONS
1. Unzip the WRKDBFR.SAV save file by double-clicking on the
self-extracting Zip file WRKDBFR.EXE
2. On the AS/400, create a save file
CRTSAVF FILE(QGPL/WRKDBFR)
3. On the AS/400, create a folder
CRTFLR FLR(WRKDBFR) INFLR(*NONE)
4. On the AS/400, create a temporary physical file
CRTPF FILE(QGPL/WRKDBFPF) RCDLEN(528) SIZE(50000)
5. Copy the PC file WRKDBFR.SAV into the AS/400 folder WRKDBFR. This
is done using the DOS COPY command or via the Windows Explorer.
6. Copy the save file WRKDBFR.SAV from your shared folder into the
physical file WRKDBFPF that you created in step 4 above.
CPYFRMPCD FROMFLR(WRKDBFR)
TOFILE(QGPL/WRKDBFPF)
FROMDOC(WRKDBFR.SAV)
TOMBR(*FIRST)
MBROPT(*REPLACE)
TRNTBL(*NONE)
TRNFMT(*NOTEXT)
7. Next you need to enter and compile the following RPG source code:
FFMFILE IPE F 528 DISK
FTOFILE O F 528 DISK A
IFMFILE NS 01
I 1 512 FLD1
I 513 528 FLD2
OTOFILE DADD 01
O FLD1 512
O FLD2 528
8. Next you must run the program above to convert the Temporary
Physical File into the Save File.
OVRDBF FILE(FMFILE) TOFILE(QGPL/WRKDBFPF)
OVRDBF FILE(TOFILE) TOFILE(QGPL/WRKDBFR)
CALL PGM(“program name”)
9. If you have previously installed WRKDBF and you have been saving
Field Sequences, you should move your saved Field Sequences file to
another library (such as QGPL) so it doesn't get overlaid by the
install:
MOVOBJ OBJ(WRKDBF/DBPFLDSQ) OBJTYPE(*FILE) TOLIB(QGPL)
10. Restore the WRKDBF product library containing the WRKDBF
objects:
RSTLIB SAVLIB(WRKDBF) DEV(*SAVF) SAVF(QGPL/WRKDBFR)
11. If you do not have the WRKDBFSQL library, restore it to your
system:
RSTLIB SAVLIB(WRKDBFSQL) DEV(*SAVF) SAVF(WRKDBF/WRKDBFSQL
12. Move the three commands in the WRKDBF library to library QGPL:
MOVOBJ OBJ(WRKDBF/WRKDBF) OBJTYPE(*CMD) TOLIB(QGPL)
MOVOBJ OBJ(WRKDBF/WRKDBFSQL) OBJTYPE(*CMD) TOLIB(QGPL)
MOVOBJ OBJ(WRKDBF/WRKDBFUNDL) OBJTYPE(*CMD) TOLIB(QGPL)
13. If you moved your saved Field Sequences file in step 9 above,
you should copy your saved Field Sequences back and then delete the
file as follows:
CPYF FROMFILE(QGPL/DBPFLDSQ) TOFILE(WRKDBF/DBPFLDSQ)
MBROPT(*REPLACE)
DLTF FILE(QGPL/DBPFLDSQ)
4. WRKDBF QuickStart
To start up WRKDBF, key in the command name “WRKDBF” followed by a
file name at a command line. For example:
WRKDBF LIBPROD/VMPMAS
4.1 The WRKDBF Command in Detail
File name . . . . . . . . . . . > VMPMAS Name, generic*, *ALL
Library name . . . . . . . . . > LIBPROD Name, *LIBL, *CURLIB...
Member name . . . . . . . . . . *FIRST Name, *SELECT, *FIRST, *FILE
Field sequence . . . . . . . . . *DFT Name, *DFT, *SELECT
Additional Parameters
Field sequence file name . . . . *FILE Name, *FILE
Field sequence library name . *LIB Name, *LIB
Display Database Relations? . . *NO *YES, *NO
Retrieve Flat File F&I Specs? . *NO *YES, *NO
F&I Member name . . . . . . . . *STD Name, *STD
F&I Source File name . . . . . . QRPGSRC Name
F&I Library name . . . . . . . BASESRC Name, *LIBL
Produce Audit Trail? . . . . . . *NO *YES, *NO
Maintain Field Descriptions? . . *NO *YES, *NO
System . . . . . . . . . . . . . *LCL *LCL, *RMT
Language . . . . . . . . . . . . ENG Character value
Entries of any of the following will display a list of files in a
panel where you can select the specific file to work with:
File Name = a generic name (such as VMPM*) or *ALL
Library Name = *LIBL, *ALLUSR, or *ALL
Display Database Relations = *YES
Entering *SELECT in the “Member name” parameter - MEMBER(*SELECT) -
will display a list of all the members in the file in a panel where
you can select the specific member to work with
If you have previously saved a field sequence that you wish to work
with, enter it’s name in the “Field sequence” parameter and this
sequence will be used. Entering *SELECT in the “Field sequence”
parameter will display a list of all saved field sequences for this
file where you can select one to use. For more information on the
field sequence functionality of WRKDBF, see section 7.3 “Saving and
Restoring Field Sequences”
WRKDBF provides special features that can make “Flat Files” (such as
S/36 files) appear to be externally described. The following steps
will allow you to take advantage of this very handy feature:
Create an RPG (not RPGLE) source member having the “F” (file) and
“I” (input) specifications that describe the fields in the file and
the records
the default F&I Source File used for flat file support is QRPGSRC in
BASESRC, but you can use any source file you wish
the default source member name (keyword value of *STD) is “FI”
followed by the File Name
You might wish to use the OS/400 command CHGCMDDFT (change command
default) to change the defaults for F&I support to match your shops
standards for where your source files exist
WRKDBF will support a DDM file by specifying “System” = *RMT
4.2 National Language Support
WRKDBF provides national language support via the “Language”
parameter.
Currently the downloadable version only provides an English language
message file. (Sorry, the author is not bilingual!) If anyone would
like to help create other national language message files, please
email the author at BReger@Levitz.com and we will make arrangements
to have your National Language included in future downloadable
releases of WRKDBF.
The following steps and directions will allow you to create your own
national language message file:
CRTDUPOBJ OBJ(DBMSGFL) FROMLIB(WRKDBF) OBJTYPE(*MSGF)
NEWOBJ(DBMSGFL”xxx”) where “xxx” denotes your language
For example: “ESP” for “Espanol” (Spanish),
“DEU” for “Deutsch” (German), etc.
WRKMSGD MSGF(WRKDBF/DBMSGFL”xxx”)
Now, simply change the “Message text” of every message ID in the
message file into your national language.
NOTE: Each message ID beginning with “DBL” is a screen title,
constant, screen literal, or command key description. It is
important that you NOT exceed the length of any message ID beginning
with “DBL”! These have a fixed length and exceeding what is there
will not be displayed in the panels.
NOTE: Message ID’s beginning with “DBU” are error, status, or
informational messages. You MAY exceed the length of any message ID
beginning with “DBU” if you wish. You are only limited by the width
of the display.
And finally, to have WRKDBF use your national language message file,
enter the following command to start WRKDBF:
WRKDBF “library”/”filename” LANGUAGE(“xxx”)
You might wish to use the OS/400 command CHGCMDDFT (change command
default) to change the default English Language to permanently apply
your National Language for your shop 5. “Work with Database File”
Panel
5.1 General Information about the “Work with Database File” Panel
This is the main panel used within WRKDBF. Although there are many
different panels in WRKDBF that you will use frequently, most of
your time will be spent reviewing records and data fields with this
panel. Therefore, it is recommended that you spend an appropriate
amount of time to become familiar with the rich functionality of
this panel and of the function keys and record options available on
this panel.
You’ll notice that different colors are used within WRKDBF.
Input-capable fields and Output data is shown as “white” (or
high-intensity)
Column headings and Field labels are shown in “green” (or
low-intensity)
Column Headings that are shown in “pink” denote key fields
Function keys and Record Options are generally shown in “blue”
Data that matches the “Find” value is displayed as “reverse image
white”
The “red” line of numbers shown above is known as the “ruler”. It is
used to give you a reference as to the horizontal positions across
the panel
All “locked down” fields are displayed in “yellow”. When fields are
locked down, the “ruler” will always be positioned to start with the
first field that is not “locked down”.
5.2 Information at the top of the “Work with Database File” Panel
(from left to right and starting at the top)
AS/400 System Name
Panel Title
WRKDBF Version Number
File Name
Object Text of the File
Window Position
Library Name
Record Format Name (or *MULTIPLE with multiple format logical files)
Number of Records in the File
Maximum Record Length
Member Name
Type of File PF or LF
Object Size in Bytes
Access Method *INDEXED or *ARRIVAL
WRKDBF Command Line
Note that the File Name, Library Name, and Member Name fields are
input capable. This allows you to start working with a different
file or member without having to first return to the command line.
Any of the values that are allowed with the WRKDBF command are
allowed here also. See section 4.1 for more details on the WRKDBF
command and it’s allowed values.
5.3 The WRKDBF Command Line
This WRKDBF command line operates much like the command line found
in WRKSPLF, SEU, etc. but with it’s own unique commands.
The “Find:”, “RRN:”, “Window:”, and “Scan Limit:” fields from
previous versions of WRKDBF have been removed and replaced by a
single input-capable command line. This has been done to open up
some panel “real estate” and also to add several new functions
without having to create separate input-capable fields.
The following commands (and abbreviations) are available on the new
command line:
TOP positions you to the TOP of the file
T “
BOTTOM positions you to the BOTTOM of the file
B “
CHANGE ACCESS switches between *ARRIVAL and *INDEXED access
C ACCESS “
RRN 12500 positions you to this relative record number (RRN mode
only)
R 12500 “
SCAN 1000 changes your scan limit to this number
S 1000 “
FIND CITY finds the next occurrence of the word CITY
F CITY “
CITY (F16) “
FIND ‘MAIN ST’ finds the next occurrence of the phrase ‘MAIN ST’
(note: quotes are required if your find string has embedded blanks)
F ‘MAIN ST’ “
‘MAIN ST’ (F16) “
F16 finds the next occurrence of the previous find string
WINDOW 100 moves the display to show data beginning at column 100
W 100 “
5.4 “Work with Database File” Record Options
2 = Change record
3 = Copy record adds a new record based on the record being copied
4 = Delete record
5 = Display record
6 = Print record
8 = Undelete record
5.5 “Work with Database File” Function Keys
F3 = Exit
F4 = Position allows you to position the File based on Key values
you specify (available only with Keyed files). You can also
optionally specify “restrictor” (read equal) keys
F6 = Add record
F7 = Select only show records matching the specified criteria
F8 = Hex/Char toggles between showing the records in Character mode
and Hexadecimal mode
F9 = Previous retrieves the previous command executed
F11 = Headings toggles between showing standard column headings or
field names
F14 = Fields provides File Field Description information about the
various fields and allows you to resequence, hide, and/or “lock
down” fields
F16 = Find scans the data records looking for the next occurrences
of the previous Find string entered in the command line. The “find”
function is not case-sensitive. Both uppercase & lowercase will
match
F17 = Hide/Show Deletes
toggles between hiding deleted records and showing them (available
with non-Keyed files only)
F18 = SQL Cmds Allows you to execute SQL commands against the data
file
F19 = Left Window the panel Left
F20 = Right Window the panel Right
F21 = Cmd Entry Brings up a command line window
F22 = Hide/Show RRN
Displays the Relative Record Number to the left of the actual data
fields. The F22 command key acts as a toggle that will alternately
Hide and Show the RRN
F24 = More keys Show the next command key line
HOME Refresh
ROLL FWD/BACK Rolls keys are enabled
5.6 “Work with Database File” on 27x132 or 24x80 character displays
The panel above is the main WRKDBF panel that is displayed when you
have a 27x132 character display. If you have a 24x80 character
display, you will see a similar panel but with much less
information. The following table describes the differences between
the 27x132 and 24x80 character panels. For these reasons, it is
strongly recommended that you use a 27x132 display device.
More columns available to display (125 versus 73 characters = 71%
more)
More records available to display (15 versus 12 records = 25% more)
File Type (27x132 only)
Number of Records (27x132 only)
Object Size in Bytes (27x132 only)
Maximum Record Length (27x132 only)
Many of the other panels of WRKDBF are “Windows“ that cover a
portion of the underlying panels. With the wider display you will
see more of the underlying panel. The narrow display usually covers
the underlying panel completely.
5.7 “Undeleting” database records
If you are working with either an “unkeyed” database file or have
chosen to work with a “keyed” file by relative record number (by
specifying ACCESS(*RRN) on the WRKDBF command), you can “undelete”
deleted records. Deleted records are displayed in this panel as
“*Deleted record”. These are only records that will accept the
“8=Undelete record” option. 6. “Change a Database Record” Panel
6.1 General Information about the “Change a Database Record” Panel
Although the window panel above is titled “Change a Database
Record”, this same panel is used for all of the Change, Copy,
Delete, Display, and Undelete record functions. The title changes to
reflect the actual function that has been entered into the Record
Option field.
This panel is designed to allow you to change the field values of
this database record. Notice that the “based-on PF” name is shown at
the top along with the relative record number of this record. When
the panel first comes up, you are shown the current values of every
field that has not been “hidden”. Simply key the data you want to
change, press the enter key, and “confirm” the update.
At this point an explanation of the “Confirm Prompt” is needed. The
Confirm Prompt is used throughout WRKDBF as a confirmation of the
data that has been entered. Keying in a “Y” says that you are
confirming that this is the correct data and that the update can
take place. Keying in an “N” says that you need to change some of
the data before allowing the update to occur. Note that the confirm
prompt does not require the enter key to be pressed. Once you key a
“Y” or “N”, a record advance is automatically done. The confirm
prompt only displays when there are no errors in the data you have
keyed.
6.2 “Change a Database Record” Function Keys
F3 = Exit exit and bypass changing this record
F5 = Refresh redisplay this record as it exists in the database
F8 = Hex Mode bring up and change this record in hexadecimal mode
F10 = Allow Lowercase
F13 = Immed Exit exit without change and bypass all records that
were selected after this record. Note that multiple record options
can be taken in the “Work with Database File” panel. F13 bypasses
any that are still to be done. 7. “Select Field Sequence to Display”
Panel
7.1 General Information about “Select Field Sequence to Display”
Panel
This panel is accessed by pressing “F14=Fields” in the “Work with
Database File” panel. It show you in detail the File Field
Descriptions (FFD’s) and keys. This is also the panel where you can
resequence the fields into a different order, hide fields you don’t
want to see, and “lock down” fields that you always want to see.
The way you sequence and hide the fields will be carried back to the
“Work with Database File” panel and will also be used throughout the
other panels of WRKDBF. If you want to have every field in the file
hidden except the last 2 fields in the file, these are the only 2
fields you will see. This sequence is used until you either return
to the “Select Field Sequence to Display” panel and make changes, or
if you exit all the way out of WRKDBF.
The trick to this panel is the entries you make in the sequence
number (Seq.) fields. As you will notice in the “red” line above, a
sequence number of Zero will hide the field and all other sequence
numbers serve to order the fields by that number. Sequence numbers
from .1 to .9 (greater than zero and less than one) have an
additional function in that these fields will be “locked down”.
7.2 “Select Field Sequence for Display” Function Keys
F3 = Exit
F10 = Hide all this automatically places a Zero into every field in
the file. This is a good shortcut when you want to hide most of the
fields. It is equivalent to you manually entering a Zero into every
field, but much quicker
F12 = Reset Seq this resets all the sequence numbers to the default
(1.0 for first field, 2.0 for second file, and so on)
F16 = Find this allows you to “find” by Field Name (exact match) or
by Field Text (contains) when F16 is pressed. This takes you quickly
to the matching Field without having to roll through lots of fields
to get to the field you’re interested in
F15 = Lock Keys this automatically “locks down” all the key fields
in key order (.1 for first key, .2 for second key, and so on)
F17 = Save Seq allows you to save the sequence with a name, so it
can be reused in a later session. See section 7.2 below for more
details
F19 = Restore Seq allows you to restore a named sequence that has
been previously saved. See section 7.2 below for more details
F21 = Print FFD Listing allows you to print out a formatted File and
Field Descriptions listing that can be used for documenting the
database file
F24 = More keys
7.3 Saving and Restoring Field Sequences
You can also “save” and “restore” field sequences for use in a later
session. This is a very handy function of WRKDBF that is frequently
overlooked. When used properly, it greatly enhances your ability to
look into and maintain the same database over and over.
The “Save Field Sequence to Display” window panel (accessed with
F17=Save Seq) shown above allows you to name and save the sequence
that is currently in effect in the “Select Field Sequence to
Display” panel. The name must be unique for the file name and
library being worked with. The descriptive field is provided to help
describe what the sequence name is used for.
The “Save as Name” field above will accept a “?” (question mark).
Keying in a “?” will bring up a window panel that displays a list of
all the saved sequence names that exist for this file and library,
allowing you to select one of them. This is handy for those times
when you want to update a previously saved sequence name but can’t
remember the name.
When you want to restore (accessed by F19=Restore Seq) and use a
sequence that was previously saved, the select panel described in
the previous paragraph will be displayed.
By pressing F10 in this select panel, you can change the
file/library names and then select a saved sequence name based on
that file/library, but you will be given a warning message that the
database formats should be similar. You will get a very strange
looking display if you use a sequence from another file that does
not closely match the format of your file. 8. Executing SQL Commands
8.1 General Information about the SQL Command Panels
Pressing “F18=SQL Cmds” on the “Work with Database File” panel
allows you to execute SQL operations with your database file. For
example, in the panel above, we have chosen to produce an on screen
list of Vendors from the state of California.
A hardcopy report can be produced by changing the “Output:” value to
“*PRINT”. The results can also be saved to an *OUTFILE by changing
the "Output:" value to *OUTFILE and entering the name for the
*OUTFILE when prompted.
The SQL commands available are:
Count Count the number of records matching the selection criteria
Delete Delete records matching the selection criteria
Insert Insert matching records from this file into another file
Select Display/Print records matching the selection criteria
Update Update matching records to other entered values
The SQL functionality of WRKDBF is implemented via QMQRY (query
management query) that is included as a normal part of OS/400. You
are not required to purchase any additional SQL licensed product.
You can save the SQL for use at a later time by specifying "Save
SQL? = Y" in the screen above. The SQL QMQRY objects are stored in
the WRKDBFSQL library when you save them. When you want to run the
SQL again later, you should use the WRKDBFSQL command to run it. The
default is to NOT save the SQL.
You can also choose to run the SQL in batch by entering a job
description name (or *USRPRF) in the "Jobd" field above.
If you wish to review or modify the SQL statements before executing
them, you should press “F15=Review SQL” before confirming the
selection criteria panel. This will bring up the SQL in “SEU”. Note
that in this version of WRKDBF selection criteria based on multiple
fields is implemented as “AND” operations. If you need “OR”
operations you can use the F15 function.
In the “Enter SQL Selection Criteria” window, the following field
selection operators are available:
EQ Equal to
NE Not equal to
GT Greater than
GE Greater than or equal to
LT Less than
LE Less than or equal to
LK Use the LIKE operation to determine if a field contains a pattern
that is similar to the comparison pattern you have entered.
NL Use the NOT LIKE operation to determine if a field does NOT
contain the comparison pattern that you have entered.
+ this entry will open up this field for an additional entry. In the
example above, you could use this to select Vendors from more than
one state such as California and Florida
LIKE and NOT LIKE comparisons are only allowed for character fields.
When you use a LIKE test, a record is selected if a certain part of
the field has the same characters as the comparison pattern you
specify. For example, you would use the LIKE test if you wanted to
select all records that have a last name that begins with an A.
You can use the following special characters to represent positions
in the field that you do not care about:
A percent sign (%) means skip as many characters as necessary to get
to the end of the field or to the next specified character. Each %
takes the place of zero or more characters in the field. (Thus, you
need not write a test pattern that is 32 characters long just
because the field is 32 characters long.)
An underscore (_) means skip one character at that position and do
not test for that character. Each underscore takes the place of one
character.
A test pattern must be no longer than, but it can be as long as, the
actual field.
For example, if you are testing for an A in the first position of
the LASTNAME field, and if:
If the field length is 3, you could specify 'A__'
If the field length is at least 1, you could specify 'A%'
|
Folder Installation Instructions
The WRKDBFR.EXE file you have downloaded from the web site is a
self-extracting Zip file that, when unzipped, should contain five
(5) files:
1. WRKDBF RISC Folder Installation Instructions (this document)
2. WRKDBF RISC FTP Installation Instructions
3. User Documentation
4. Summary of changes in the new version
5. WRKDBFR.SAV file. This AS/400 save file contains the WRKDBF
product library that must be uploaded to the AS/400, as described
below:
PREREQUISITES
WRKDBF (the RISC version) requires a RISC-based AS/400 running under
OS/400 version V3R7M0 or higher.
INSTALLATION INSTRUCTIONS (VIA FOLDER)
1. Unzip the WRKDBFR.SAV save file by double-clicking on the
self-extracting Zip file WRKDBFR.EXE
2. On the AS/400, create a save file
CRTSAVF FILE(QGPL/WRKDBFR)
3. On the AS/400, create a folder
CRTFLR FLR(WRKDBFR) INFLR(*NONE)
4. On the AS/400, create a temporary physical file
CRTPF FILE(QGPL/WRKDBFPF) RCDLEN(528) SIZE(50000)
5. Copy the PC file WRKDBFR.SAV into the AS/400 folder WRKDBFR. This
is done using the DOS COPY command or via the Windows Explorer.
6. Copy the save file WRKDBFR.SAV from your shared folder into the
physical file WRKDBFPF that you created in step 4 above.
CPYFRMPCD FROMFLR(WRKDBFR)
TOFILE(QGPL/WRKDBFPF)
FROMDOC(WRKDBFR.SAV)
TOMBR(*FIRST)
MBROPT(*REPLACE)
TRNTBL(*NONE)
TRNFMT(*NOTEXT)
7. Next you need to enter and compile the following RPG source code:
FFMFILE IPE F 528 DISK
FTOFILE O F 528 DISK A
IFMFILE NS 01
I 1 512 FLD1
I 513 528 FLD2
OTOFILE DADD 01
O FLD1 512
O FLD2 528
8. Next you must run the program above to convert the Temporary
Physical File into the Save File.
OVRDBF FILE(FMFILE) TOFILE(QGPL/WRKDBFPF)
OVRDBF FILE(TOFILE) TOFILE(QGPL/WRKDBFR)
CALL PGM(“program name”)
9. If you have previously installed WRKDBF and you have been saving
Field Sequences, you should move your saved Field Sequences file to
another library (such as QGPL) so it doesn't get overlaid by the
install:
MOVOBJ OBJ(WRKDBF/DBPFLDSQ) OBJTYPE(*FILE) TOLIB(QGPL)
10. Restore the WRKDBF product library containing the WRKDBF
objects:
RSTLIB SAVLIB(WRKDBF) DEV(*SAVF) SAVF(QGPL/WRKDBFR)
11. If you do not have the WRKDBFSQL library, restore it to your
system:
RSTLIB SAVLIB(WRKDBFSQL) DEV(*SAVF) SAVF(WRKDBF/WRKDBFSQL
12. Move the three commands in the WRKDBF library to library QGPL:
MOVOBJ OBJ(WRKDBF/WRKDBF) OBJTYPE(*CMD) TOLIB(QGPL)
MOVOBJ OBJ(WRKDBF/WRKDBFSQL) OBJTYPE(*CMD) TOLIB(QGPL)
MOVOBJ OBJ(WRKDBF/WRKDBFUNDL) OBJTYPE(*CMD) TOLIB(QGPL)
13. If you moved your saved Field Sequences file in step 9 above,
you should copy your saved Field Sequences back and then delete the
file as follows:
CPYF FROMFILE(QGPL/DBPFLDSQ) TOFILE(WRKDBF/DBPFLDSQ)
MBROPT(*REPLACE)
DLTF FILE(QGPL/DBPFLDSQ)
|
Summary of changes
Version # Description Highlight! Date Internet
7.04.04 Original Version of WRKDBF Posted to Web Site 7/1/99
7.04.05 Recognize new File Specs. after CHGPF command run 9/19/99
7.04.06 Fix update bug when using multiple member logical files
9/19/99
7.04.07 Disallow use of MBR(*ALL) in WRKDBF command 9/19/99
8.00.00 Allow foreign language translations Highlight! 9/19/99
8.00.01 Fix bug when redisplaying subfile after rolling backwards
9/19/99
8.00.02 Eliminate halt when Windowing beyond Pos. 3000 9/19/99
8.00.03 When Hex. '20' pulled into field length, change to *BLANK
9/19/99
8.01.00 Enable *ARRIVAL sequence for keyed files Highlight! 9/19/99
8.02.00 Change "F16=Find" function to be not case-sensitive
Highlight! 9/19/99
8.03.00 Allow member to be selected via MBR(*SELECT) Highlight!
9/19/99
8.04.00 Expand windowing from 3000 to 9000 positions Highlight!
9/19/99
8.04.01 Improve how screen size (24x80 or 27x132) is determined
9/19/99
8.04.02 Display deleted records in keyed files if ACCESS(*RRN)
Highlight! 9/19/99
8.04.03 Add member name to Add and Change screens 9/19/99
8.04.04 Fix "undelete" bug with DATFMT and TIMFMT keywords 9/19/99
8.04.05 Improve the DATFMT and TIMFMT validation of ISO fields
9/19/99
8.04.06 Back out bad fix # V08.00.01 9/19/99
8.05.00 Show the same subfile page as the selected File/Member
9/19/99
8.05.01 New Version of WRKDBF Posted to Web Site 9/19/99
8.05.02 Fix "record already exists" halt in "Select a Database
Record" 12/9/99
8.05.03 When adding rcds, initialize "Signed" flds w/ decs. properly
12/9/99
8.06.00 Allow SQL pattern selections via LIKE and NOT LIKE
Highlight! 12/9/99
8.06.01 Fix level check halt when Adding Records in Lowercase
12/9/99
8.06.02 Use the correct Member Name when running SQL 12/9/99
8.06.03 Fix borders in "Enter SQL Select Criteria" panel if 24x80
12/9/99
9.00.00 Allow SQL commands to be submitted to batch Highlight!
12/9/99
9.01.00 Enable *OUTFILE support with SQL commands Highlight! 12/9/99
9.02.00 Allow SQL commands to be saved so they can be reused
Highlight! 12/9/99
9.02.01 Do not log CL commands in "Key Positioning" program 12/9/99
9.02.02 Fix Binary Field halt by initializing and displaying
properly 12/9/99
9.02.03 Allow JOBD(*USRPRF) when submitting SQL operations
Highlight! 12/9/99
9.02.04 Change the width of SQL reports from 80 to 132 characters
Highlight! 12/9/99
9.02.05 Fix various minor bugs with *INDEXED Flat Files 12/9/99
9.02.06 Roll back correctly when the first record is deleted 12/9/99
9.02.07 Fix problems with "SST" (substring) Fields 12/9/99
9.02.08 Fix problems with "CONCAT" (concatenate) Fields 12/9/99
9.02.09 New Version of WRKDBF Posted to Web Site 12/9/99
9.02.10 Expand number of fields from 200 to 400 6/12/00
9.02.11 Gracefully handle values in Binary fields that are too large
6/12/00
9.02.12 Fix "record already exists" halt in "Select a Database Mbr"
6/12/00
9.02.13 Fix "mbr already in use" halt in "Select File" & "Select Mbr"
6/12/00
9.02.14 Eliminate halt when DSPDBR(*YES) & File does not exist
6/12/00
9.03.00 Add functionality to print out the File and Field
Descriptions Highlight! 6/12/00
9.03.01 Fix several minor bugs when Printing Database Records
6/12/00
10.00.00 Optionally display the Relative Record Number Highlight!
6/12/00
10.01.00 Make the File Name and Library Name fields input capable
Highlight! 6/12/00
10.01.01 Fix several minor bugs when selecting a Field Sequence
6/12/00
10.02.00 Allow Field Sequence to be specified on the WRKDBF cmd
Highlight! 6/12/00
10.03.00 Allow the previous Key Positioner values to be retrieved
Highlight! 6/12/00
10.03.01 Fix several minor bugs when Positioning at a Key 6/12/00
10.03.02 Enable roll back after positioning at a Key or RRN 6/12/00
10.03.03 Expand number of fields from 400 to 900 6/12/00
10.03.04 Do not allow F6=Add when number of fields exceeds 900
6/12/00
10.04.00 Stop Windowing right at the last displayed Field 6/12/00
10.04.01 Fix several minor windowing bugs 6/12/00
10.05.00 Add a WRKDBF cmd line and create the WRKDBF cmds Highlight!
6/12/00
10.05.01 Improve the Record ID determination when using "Flatfiles"
6/12/00
10.05.02 Eliminate "mapping error" msg when File has ISO Key Flds
6/12/00
10.05.03 Add a Confirmation panel when leaving F15=Review SQL
6/12/00
10.06.00 Optionally display the Keys in "Select a Database File"
Highlight! 6/12/00
10.06.01 Make the Mbr Name field input capable on multiple mbr files
Highlight! 6/12/00
10.06.02 Fix several minor bugs in "Select Database Records" panel
6/12/00
10.07.00 Add functionality to "find" Fields in "Select Field
Sequence" Highlight! 6/12/00
10.08.00 Add functionality to "restrict" on Key values Highlight!
6/12/00
10.08.01 Create a WRKDBF cmd to "CHANGE ACCESS" Method 6/12/00
10.09.00 Create a command to "Undelete" all deleted records 6/12/00
10.09.01 Eliminate "array index" halt when finding a bad Field Name
6/12/00
10.10.00 Add support for Floating Point fields (RISC only) 6/12/00
10.10.01 Fix MAINT(*YES) halt when long alpha fields were present
6/12/00
10.10.02 Eliminate the reverse image window borders 6/12/00
10.10.03 New Version of WRKDBF Posted to Web Site 6/12/00
|
|
![[ ]](http://www.prym.nl/icons/unknown.gif){kind=icon}
![[ ]](http://www.prym.nl/icons/binary.gif){kind=icon}