Frequently Asked Questions about the IBM AS/400
(C) Copyright by Vincent Greene, 1995, all rights reserved.
*new* <- search for the text "*new" to quickly find the new or changed
portions of the document.
This FAQ contains the following subjects:
This document discusses some questions and topics that occur or should
occur repeatedly on the comp.sys.ibm.as400.misc Usenet group. The purpose
of this document is to provide an easy reference source for common
questions. You should check this document and the other FAQ's associated
with the newsgroup for answers before posting a question to the group.
The fact that this document is called "Frequently Asked Questions" does not
preclude the inclusion of questions that have never been asked on the
newsgroup. If you have the answer to a "Frequently Unasked, but Everyone
Should Really About This", send it to firstname.lastname@example.org for possible
inclusion in this document. Answers are greatly preferred to questions in
this regard. If you have a question, and don't know the answer, post it to
the group. Later you can always summarize the correct answers and send
them to me for inclusion in the FAQ.
This article is provided as is without any express or implied warranties.
While every effort has been taken to ensure the accuracy of the information
contained in this article, the maintainer assumes no responsibility for
errors or omissions, or for damages resulting from the use of the
information contained herein.
The contents of this article in all cases represent the opinions of the
individual contributors and not necessarily those of their employers.
This article may be distributed by any means as long as it is distributed
unmodified and in its entirety, including all disclaimers and the copyright
statement. Anybody wishing to mirror (maintain a COPY) of the FAQ on a
web site is encouraged to do so, but please notify the list maintainer so
your mirror can be listed in the document, and you will be notified of
new versions of the FAQ.
Subject: Where to get the most recent copy
This article will be posted regularly to the comp.sys.ibm.as400.misc
newsgroup in a text form.
The most current HTML version is available from:
Mirrors of the HTML version are available at:
Subject: What AS/400 resources are available on the Internet?
- Discussion of all AS/400 Issues
- Discussion of the Synon CASE product
WWW pages: This section includes selected AS/400 home pages that include links to
most other AS/400 resources on the web. The people who maintain these pages
perform an excellent job of maintaining links to other pages. No attempt will
be made to duplicate that effort here.
- David Gibb's Midrange Resources Page
- IBM's AS/400 Home Page
- Midrange Computing's AS/400 Home Page
- List of Computer books, with AS/400 and RPG programming categories
- On With Learning, Videos
- Duke Communications (NEWS/400) Home Page
- Automated Training Systems, Audio Courses
- Martin Taylor's WWW site with lots of AS/400 links
- You may like to have a look at out Web site for
information on JBA's Guidelines package. This is a
complete client/server development package which operates
under OS/2 and allows applications to be created for Windows
and OS/2 and includes easy client/server access to an AS/400.
- JD Edwards web site with a slant towards marketing rather
- Multiple software packages for PCS/400 and CA/400 PCs
- Preventative service planning information from IBM
- If you are interested in buying or selling used AS/400's,
or any kind of computer - mainframes to PC's
- This is the official photo and bio site open for members of the
Midrange Dicussion list described below. This gives you a
chance to "see" some of the people you regularly correspond with.
- solid affordable green screen utilities like :
WRKIFS - view, print, etc. ifs pc files from any AS/400 terminal
WRKSRCCRP - source encryption / decryption
- Netware for SAA level 2 support web page.
- COMMON Argentina (Asociación Argentina de Dirigentes de Sistemas)
- Another midrange resources page.
- The Midrange Navigator Page (AS/400, 3x). Visitors are encouraged to contribute
their Midrange links, info, etc.
- web site that contains tutorials and links to AS/400 sites.
Midrange Discussion List
Submission Address: email@example.com
Subscribe: Send "SUBSCRIBE midrange-l your name" to firstname.lastname@example.org
Code/400 Discussion List
Submission Address: email@example.com
Subscribe: Send "SUBSCRIBE" to firstname.lastname@example.org
Midrange Discussion List - email@example.com
Submission Address: firstname.lastname@example.org
Subscribe: Send "SUBSCRIBE midrange" to email@example.com
JDE Mailing List - JDE@lists.best.com
Submission Address: JDE@lists.best.com
Subscribe: E-MAIL TO: JDEfirstname.lastname@example.org.
- for single messages only:
in the body of your message specify: SUBSINGLE
- for digest messages only:
in the body of your message specify: SUBSCRIBE
A Client Access/400 FAQ is being maintained by Matthew.Huff@tasb.org for Client Access/400
and ODBC drivers.
Cardinal Computers Limited, London, UK has generously volunteered a site
to store Freeware or Shareware for the the AS/400 community:
The instructions are at http://www.cardinal.co.uk
Another FTP site (download only) is available at:
Additional Resources are available:
Compuserve COMMON forum
Compuserve APPC forum
Compuserve NEWS3X400 forum
Compuserve IBMDB2 forum (there is a section for DB2/400)
News/3X Newslink BBS (for Info call: 800-621-1544 or 970-663-4700)
Midrange Computing BBS Phone# 619-931-9909
IBM FORUM400 - Accessable via IBMLink
FidoNet MIDRANGE echomail conference
Subject: What books are available for the AS/400 system?
Many titles: Call Duke Press at 1-800-621-1544 or 1-970-667-2321 to obtain
a list of current titles or to order books. You can also request
information on individual books by fax, by dialing 1-800-847-7731 anytime
and supplying a fax code. The following titles are supported as of October
1995. The number in parentheses is the fax code number:
"Inside the AS/400" (442)
"Desktop Guide to CL Programming" (423)
"Desktop Guide to Programmers' Tools" (426)
"Power Tools for the AS/400 Volume I" (402)
"Power Tools for the AS/400 Volume II" (403)
"DDS Programming for Display and Printer Files" (444)
"Implementing AS/400 Security, 2nd edition" (404)
"Starter Kit for the AS/400, 2nd edition" (405)
"C for RPG Programmers" (409)
"Application Developer's Handbook for the AS/400" (406)
"Common-Sense C" (408)
"Using Query/400" (415)
"An Introduction to Communications for the AS/400" (401)
"Jim Sloan's CL Tips & Techniques" (421)
"Programming in RPG/400, 2nd edition" (414)
"Programming in RPG IV" (443)
"The Quintessential Guide to PC Support" (413)
"Object Oriented Programming for AS/400 Programmers" (412)
"Mastering the AS/400: A Practical, Hands-On Guide" (411)
"CL Programming for the AS/400" (407)
"RPG IV Jump Start" (416)
Information on any Duke Press books can also be obtaining from Duke's Web page:
"The Modern RPG Language" by Robert Cozzi, Jr.
29 W 120 Butterfield Road
Warrenville, IL 60555
Outside USA (708) 393-4474
24 hour FAX (708) 393-2936
Many titles: call Midrange Computing at 1-800-477-5665.
they will gladly send you info on the many book they
publish. But be prepared to pay $80 - $125 per book.
A list of books published by Midrange Computing is located at:
Another publisher to try is CAS Books at 1-800-292-4227.
You might try the following titles, both in the J. Ranade series of data
processing books published by McGraw-Hill (phone number: 1-800-822-8158):
AS/400 Concepts & Facilities by Tony Baritz and David Dunne
AS/400 System Administration Guide by Jesse Gamble and Bill Merrow
CBM Books (1-800-285-1755) also publishes The AS/400 Companion and
Navigating the AS/400: A Hands-On Guide.
Navigating the AS/400 - A Hands-on Guide
By John Enck and Michael Ryan
SQL/400 - A Professional Programmer's Guide
By Tim Martyn, Tim Hartley and Richard Johnson
Also, the following may be of some use.
By J Hoskins
PC Users Guide to the AS/400
By B Kliewer et al
Van Nostrand Re
RPG III On The IBM AS/400
By W.J. Tomlinson
A list of computer books, AS/400 and otherwise can be found at:
and for those of you who aren't in the habit of carrying a filing
cabinet in your back pocket and have access to a CD-Rom, the IBM AS/400
Softcopy Library (SK2T-8250-03) available from IBM on CD-Rom contains the
complete volumes of the AS/400 Manuals is recommended.
An even better choise of CD might be the one with Redbooks _and_ the
softcopy manuals on one CD; number SK2T-2172
Subject: What magazines/trade publications cover AS/400 topics?
P.O. Box 3438
Loveland, CO 80539
800-621-1544 or 970-663-4700
5650 El Camino Real, Suite 225
Carlsbad, CA 92008
Phone: 619-931-8615 Fax: 619-931-9935
Subscriber Services E-mail: email@example.com
P.O. Box 6400
Duluth, MN 55806-6400
3X/400 Systems Management
25 Northwest Point Road, SUite 800
Elk Grove Village, IL 60007
1300 Virginia Drive, Suite 400
Fort Washington, PA 19034
Subject: How do the new ILE objects relate to other systems?
In short, if you are familiar with PC operating systems (MS-DOS,
MS-Windows, OS/2), you should recognize the following translations:
ILE term = PC Term
"Binding" = "Linking"
*MODULE = *.obj file
*PGM = *.exe or *.com file
*SRVPGM = *.dll file (MS-Windows or OS/2)
*BNDDIR = *.lib (this is a loose interpretation, lib files have a much
wider scope than AS/400 binding directories)
Subject: What is a User Space?
User spaces and character data areas are similar in that they have no internal
structure - you typically define a data structure within a program to
reference each byte. However, character data areas have a maximum size of 2000
bytes while user spaces have a maximum size of 16 Mbytes and can be set to
_automatically_ grow in size as they are filled with data.
Generally, user spaces are used to contain the output of IBMs List
Application Programming Interfaces (APIs). But there is no reason why an
ambitious programmer can't use the Change User Space API to fill their own
user space. Examples of IBM's List APIs include QUSLOBJ which does more or
less the same thing as the command DSPOBJD to an outfile (except much faster).
Like the DSPOBJD to an outfile, QUSLOBJ writes many 'records' of data into the
IBM provides several APIs to access user spaces (I can't remember the names
of all of them):
QUSCRTUS - Create user space
QUSCUSAT - Change user space attributes
QUSCHGUS - Change user space
QUSDLTUS - Delete user space
QUSRTVUS - Retrieve user space
QUSRUSAT - Retrieve user space attributes
QUSPTRUS - Retrieve pointer to user space
In my experience, using a pointer to the user space is the best way to
access a user space, but the Retrieve User Space API works fine from RPG III.
Because the APIs can be called from any language that I can think of,
user spaces can be used from these same languages. Of course some languages
offer better structures to use the API data - trying to define a data structure
in CL will probably drive you crazy, and receiving a pointer to a user space from
a langauge that doesn't support pointers isn't going to work either.
The format of the data placed into a user space by an IBM List API is defined
in the API reference manual. There are two major parts to the format. The
first section is the Generic List Header and the second is the returned data.
The generic list header returns number of entries, length of each entry,
offset to first entry etc. This allows your program to read through the data.
Read Chapter 2 of the manual for a complete description of the Generic List
Header, and read the API definition for the List API you are calling for the
returned data format.
For more information, Ernie A. Malaga wrote an article, "Open Your System
with APIs" which was published in the December '93 issue of NEWS 3X/400.
Subject: What is Expert Cache, and will it help my system performance?
Expert cache can be set on/off by storage pool. Use the change shared pools
command (CHGSTGPOOL) or the work with shared pools menu. To turn it on
you put *calc for the PAGING parameter. To turn it off you say *fixed.
It effectively causes all disk read accesses into that pool to be blocked
up to something larger than the norm. If a program already blocks up it's read
accesses, or the read accesses are random the likelihood of expert cache
being a big help is pretty slim.
However, if the accesses are sequential and not blocked up or key'ed
sequential and not blocked, there's a good chance of some help. the
main benefit will be fewer disk accesses, but a seconadary phenomena
will be a slightly smaller number of instructions executed.
There are cases where expert cache can actually slow things down. These
cases are usually memory constrained. Not enough pages to keep the
cached data long enough to get additional hits on the page and thus disk
ops actually increase.
For more Information,
A Red Book on Performance Management of V3R1: GG24-3723 has three or
four pages on Expert Cache in one of the appendicies.
AS/400 magizine, sept/oct 93 page 34 - describes expert cache when it
first appeared on v2r3.
Subject: How can I find an AS/400 User's Group?
The largest AS/400 user's group is COMMON, which hosts a major conference
twice annually, and also hosts smaller regional conferences. COMMON is a
major force in the AS/400 world. At the conferences, COMMON members vote
on resolutions which are presented to IBM. Many of the most useful
enhancements in OS/400 started as COMMON resolutions. I personally believe
I learned more information directly related to my work in one week at a
COMMON conference than I did in four years at a major University.
To contact COMMON:
401 North Michigan Avenue,
Chicago, IL 60611.
Phone: 800-777-6734 or 312-644-6610. Fax: 312-245-1083
A list of local users group is available via Midrange Computing's home page
Subject: I'm looking for a job, why can't I find any job posts in the
Job posts are prohibited in the comp.sys.ibm.as400.misc newsgroup
charter. This is primarily because they are of interest to a limited
geographical area, while the newsgroup is worldwide. For example, people
in Australia generally don't care about jobs available in the U.S. Midwest
If you are a recruiter, you can send information on e-mail addresses, web
pages, and other resources you provide to firstname.lastname@example.org, and they
will be included (although often edited for size) in the next subject in
If you are a looking for a job and see a job post in the newsgroup, do us
all a favor. If you contact the recruiter based on the posted article (I
won't ask you not to, after all, you need a job) please DON'T tell them you
saw their post on the newsgroup. All that will do is encourage additional
job posts which do little more than waste time and network bandwidth.
Job posts should be limited to the .jobs hierarchies on USENET - misc.jobs,
comp.jobs, az.jobs, etc.
Subject: What resources are available if I am looking for AS/400 related
Rather than fill this newsgroup up with AS400 Job
Openings. I will be happy to email them
direct to your email address. My email dsitribution
lists are private and your email address will not be
given out to any other source. It is not a listserv
so you won't get any unsubscribe messages just good
quality information. Full time and contract.
Send email to request to RAYOSB@worldnet.att.net
When requesting please use the word AS400 in your
Be sure to see my AS400 interests and links
at my WEB site at
EDP CONTRACT SERVICES -
For AS/400 job postings, industry news, and general information
about the nation's fourth largest consulting company, visit our new
web site at http://edpcs.com
610 667-8735 (fax) 610 667-2990 (voice) email@example.com
CPi/The Experts, a leading FL consulting firm, is looking for AS/400
programmers. We are on the Vendors Lists of many AS/400 shops and have
contacts with other Clients that do not have Vendors Lists. Most of our
clients are located in South Florida. We are looking for COBOL and RPG
Programmers. For more information please contact:
Subject: Saving OV/400 Distribution Lists
If you ever need to save distribution lists for any reason, copy the files
to a different location and restore them later. These are the database files
OV/400 uses to define and store your distribution lists.
Subject: Passing long character Parameters with SBMJOB
A good way to pass a character parameter that has more than 32
positions is to define the string one position more than you need and
place a character in the extra position. With the last solution, there is no need
to check if it's the special character which appear or not in the last position.
Subject: Save while active
A consideration when using Save While Active is the type of locking set on
objects while being saved. During the checkpoint processing, an *SHRNUP
(shared no update) lock is placed on objects being saved. Once the
checkpoint is established, and until the end of the save, this is dropped
to a *SHRRD (Shared read).
As others have already said, it's best to end all user jobs until the
checkpoint is reached. However, it is possible to run jobs that only
require a *SHRRD lock thru the checkpoint if necessary. This could be a
report or something. It will lengthen the wait for the checkpoint slightly.
The thing to bear in mind, though, is that once the checkpoint is
established, you can't run any job that will require an *EXCL
(exclusive) lock on any of those objects. i.e. CLRPFM, RMVM, RGZPFM etc.
firstname.lastname@example.org (Simon Ritchie)
There is no way to restart a swa save which has failed for any reason such
as a media error. Once the save has failed the checkpoint is gone.
Sharon A Schneider
Information Development Planning
First - Save While Active is somewhat of a misnomer. The objects to be saved
still have to be unused, but only until the checkpoint is created. After that,
the system continues the save while the objects can be used. Note that not all
commands can be run; i.e. DLTF and CLRPFM will fail. So it is not possible to
get a checkpoint in the middle of the MRP run.
Second - When the SAVxxx command, a message queue for the checkpoint message can
be specified. So you can then have your nightjob submit the SWA and then wait
on the message queue until the appropriate message is received, at which point
it can continue.
email@example.com (Tom Jedrzejewicz)
Subject: Reducing processing delay for commonly used functions.
The issue of how fast a program will "load" is no doubt a question
from someone who is trying shorten the start time of a "cold" program.
I spent a month understanding this simply process on an AS400 after
the users responded that the programs started to slow, and there is no
one simple answer.
RPG has at least 4 "modes" of "calling" a process:
Background (Data Queues)
And two ways of encoding:
Each combination allows for different coding, reuse, and maintance
charactics. The fast run time is always In-Line:
1 do 10 x
move testa testb
testb chain file 99
The next would be Subroutine:
1 do 10 x
move testa testb
testb chain file 99
but with both of these methods, we have improved our run time
performance at the expense of load time (the file open, and pag size).
The Call has the same problems but the cost of the file open is during
run time, so some screen change will be slow.
All three cases cost PAG size for the same file, if everyone needs to
use the same file.
The case Data Queue suffers only the original load time. before
anyone can use the function and the file overhead is only charged to
the background program (which means fewer locks). Cost to send a
meesage to a background program and back to the sender is 1/4 the time
to read a record from disk.
We used this "client/server" server design for 40 different complete
functions including Multiple Name Searches that required 22 different
files to be checked (at different times) with at least 25 file reads
prescreen. We changed screen time from 5 seconds to 1 second by just
not Calling a program but making a Send/Receive Request (model B30
processor). This also allowed for a PAG reduction from 4 megs to 1
firstname.lastname@example.org (Lisa B.)
DB2/400 is an upgrade to/rename of the 'native' OS/400 database, which
previously had no real name besides OS/400. What format are it's files
stored in under DB2/400? The same as under OS/400. What format is that?
Well, gurus I know say that it's similar to VSAM files, but I've never
really checked it out thoroughly. Nor do I really care- OS/400 and
DB2/400 beat the pants off the other 'non-proprietary' (how many of us
have heard OS/400 referred to as a proprietary database?) database
engines out there.
Since IBM is really standing behind DB2 on it's other platforms, they
decided that, in conjunction with adding features like triggers,
built-in referential integrity, and SMP in the query engine, they'd
rename OS/400's database services to DB2/400. I'm pretty certain that
the file formats do not match that of DB2/2 or DB2 for the 390, or
DB2/AIX- but the engines *do* talk very well together, and will
cross-propagate (I've actually seen this working), etc.
-Paul B. Davidson
Subject: How can I find an OS/400 Command?
In general, the commands are much more consistently constructed compared
to other systems. A starting point is always 'GO MAIN' which takes you to the
main menu. This menu contains pretty much every command on the system.
AS/400 commands are generally composed of where the
parts of the command are consistent abbreviations (usually 3 or 4
characters) that represent what you want to do.
Common Verbs: WRK (work with), CRT (Create), DLT (Delete), CHG
(Change), DSP (Display)
Common Nouns: PGM (program), SRVPGM (Service Program), MOD (module),
LIB (Library), JOB (Job), ACTJOB (Active Jobs), DIR (IFS Directory)
dozens of others...
Usually you can combine the verbs and nouns and simply guess the
commands out of thin air.
i.e. CRTLIB, DLTLIB... etc.. Sometimes they don't work that easily, because
of the 4 character abbreviations or some object/abbreviation you didn't expect.
In these situations, use 'GO MAIN' to search for and learn the command.
Also you can use the command 'GO CMD' to get more specific menus.
(Example GO CMDLIB to go to a menu containg all the library commands).
Another vital tidbit is the F4 key. This is the prompt key and it
shows a screen that prompts you for all the possible parameters you
might want to enter for a command. Example: Type 'CRTLIB' and then hit
F4. The system will prompt you for some things you can enter. Some
fields are blank and you must fill them in, others are filled in with
default values for you. Of course, its easier/faster just to type
CRTLIB FRED or CRTLIB LIB(FRED) than use F4 or the menus. But the
F4 and menus really are a great way to start or examine all the possible
F1 is always context sensitive help.
With that little arsenal there, it shouldn't take you much to learn all
of the useful commands and their syntax. There are other introductory
things that might help, but we'll leave it at that.
Fred Kulack Open Systems Enablement - AS/400 PThreads & Unix APIs
Subject: How can I do a system backup without operator intervention?
>We are trying to automate our full system backup to eliminate operator
>I have tried doning the ENDSBS *ALL and the SAVSYS in a CL program that
>is then submitted. But it appears those can only be done interactively.
> The best I have been able to do is to dial-in to my PC, transfer control
>to QCTL and call my CL program.
>I was wondering if there is any techinques or software products to
>accomlish an operatorless full system backup?
Try changing your program to do the following:
SAVSECDTA then SAVCFG instead of SAVSYS
(just saves user profiles, private authorities and configuration so
remember to keep your last couple of SAVSYS tapes as you will need
them if you ever need to restore your operationg system.)
Then do a SAVLIB *IBM followed by a SAVLIB *ALLUSR - together, these
two save the same libraries as SAVLIB *NONSYS but this way you don't
need the system in a restricted state - so you can run them in a batch
Follow this by saving all documents with SAVDLO.
Put together, the commands save just about everything except the basic
operating system but that doesn't change very often so you don't
really need to save it very often. Just do a SAVSYS when you've
applied PTFs or done an upgrade.
You might get problems if objects are in use during the save so I'd
end all subsystems except QCTL and run the save as a batch job in
Subject: How can I use SQL inside an RPG program?
This is the outline for an internal education session prepared on Embedded SQL:
Embedded SQL Outline
Embedded SQL can be used to replace many different file operations in
an HLL program. RPG examples (actually RPGLE) will be used because it
is what we all understand, and because I hate COBOL.
The source types for RPG with embedded SQL are:
SQLRPG for RPG/400 with embedded SQL (use implementer code RPGSQL)
SQLRPGLE for RPG/ILE with embedded SQL (implementer code RPGSQLE)
The commands for compiling RPG with embedded SQL are:
CRTSQLRPGI ( this is used for both modules and bound programs)
When compiling an embedded SQL program, you should normally specify:
COMMIT(*NONE) - Specifies that commitment control is not in effect.
CLOSQLCSR(*ENDMOD or *ENDPGM) - Prevents open access paths from
remaining open after the program closes. Read the help text for
The syntax for including SQL statements in your RPG source is:
C/EXEC SQL statement
C+ continuation of statement (optional)
C+ UPDATE OA1320F1
C+ SET W2USER = ' '
C+ WHERE W2USER = :W2USER
C+ AND DATE(W2TMST) <= CURRENT_DATE
Results of an SQL statement can be determined by checking the value of
the reserved variable SQLCOD. Negative values are hard errors,
Positive values are warnings. A full list of possible SQLCOD values
can be found in the SQL reference. Hard errors also log diagnostic
messages to the job log, which can be handy while debugging.
Simple Embedded SQL examples:
When describing SQL in general, the simplest statement to understand is
the SELECT. When dealing with EMBEDDED SQL, the simplest is a group
UPDATE or DELETE statement. For example:
C+ UPDATE OA1320F1
C+ SET W2USER = ' '
C+ WHERE W2USER = :W2USER
C+ AND DATE(W2TMST) <= CURRENT_DATE
This will blank the W2USER field in the OA1320F1 file in each record
where the timestamp field (W2TMST) is less than or equal to today's
date (CURRENT_DATE is an SQL constat), and the W2USER field in the file
equals the current value of the host variable W2USER (:W2USER). You
will notice host variables are preceded with a colon.
The group DELETE statement works the same way.
It is also simple to use the SELECT ... INTO syntax to return a single
row of values. For example:
SELECT MAX(hdordn) INTO :maxord FROM opmhdrf1
will return the highest order number in the opmhdrf1 file into the host
SELECT MAX(adsuff) INTO :maxsuf FROM opmaddf1 WHERE hdordn = :maxord
will return the highest suffix for the order in host variable maxord
into the host variable adsuff.
SELECT adsuff INTO :maxsuf FROM opmaddf1 WHERE hdordn = :maxord
is different, it will return the ONLY suffix into maxsuf for the order
maxord. If there is more than one record, this statement will FAIL
(SQLCOD will be positive). The SELECT ... INTO syntax is only valid if
the statement returns only one result row. Typically this is only good
for totals or other column functions, or to duplicate a CHAIN with a
full UNIQUE key. If no value is available, a SELECT INTO will set
SQLCOD = -100.
Okay, so if I can't SELECT multiple rows with SELECT INTO, how can I
process a multiple row result set? Answer: you must use cursors.
A cursor in SQL is a basically a pointer to a single row in a result
set. You can compare it to a record pointer in normal native access.
To use a cursor with a select statement, you must use a series of SQL
statements. First you must declare the cursor -- this is like defining
a file name for a select statement. For example:
DECLARE DTLACTN CURSOR FOR
WHERE DATE(W2TMST) <= CURRENT_DATE
AND W2ACTN = :W2ACTN AND W2USER = ' '
This defines the name DTLACTN to refer to the results of the included
select statement. Notice that at this point, the select statement has
not been run. This is a definition statement.
The next step is to open the cursor using:
This is similar to opening a file. You can't read unless you open the
Then you will process a row at time from the result set (similar to a
FETCH NEXT FROM DTLACTN INTO :W2ORDN, :W2TMST
this grabs the next row from the open cursor DTLACTN into the host
variables W2ORDN and W2TMST. This processes one row at a time. You
will repeat this for each record until you reach end of file (SQLCOD =
When you are done with the open cursor, you can close it with:
Other operations with a cursor:
There are special extensions to the WHERE clause on the UPDATE and
DELETE commands which allow updating or deleting the current row
retrieved from a SELECT cursor. For example:
UPDATE OA1320F1 SET W2USER = ' ' WHERE CURRENT OF DTLACTN
DELETE FROM OA1320F1 WHERE CURRENT OF DTLACTN
will update or delete the last FETCH'ed row for a specific cursor.
you cannot use the FOR FETCH ONLY clause when either of these
operations will be used.
Using * on a SELECT statement:
It is possible to use the SELECT * syntax to select all fields from a
file into a data structure which defines the entire file. Thus if you
define data structure RECORD as:
RECORD E DS EXTNAME(OA1320F1)
you can use the SQL statements:
DECLARE STUFF CURSOR FOR SELECT * FROM OA1320F1
FETCH NEXT STUFF INTO :RECORD
to get ALL of the fields into the externally defined data structure
There are many other things you can do, but they are subjects for a
more advanced discussion in the future:
Multiple row FETCH operations: You can define an array to hold the
results of a fetch and use a modification of the FETCH syntax to fetch
multiple rows into the array.
Multiple row INSERT operations: You can define an array and use it to
INSERT multiple rows into a table as well.
Dynamic SQL: All of the examples here used a fixed format of a given
statement. The only variation in the statements are defined by
specific host variables e.g. "WHERE W2ORDN = :selord". You can also
create dynamic SQL statements where the entire WHERE (or other) clause
is created via string manipulation. These are created using the
PREPARE and EXECUTE statements.
varying-list SELECT processing: Given that you can create SQL
statements dynamically, you can obviously change the list of fields to
be returned by a statement. This is called a varying-list SELECT,
which MUST be processed by using an SQL descriptor. This is WAY beyond
the scope of this discussion.
Error trapping: There is a WHENEVER statement which can be used to
automate some error trapping in embedded SQL. Basically, it will
branch to a given TAG when either a NOT FOUND, SQLERROR, or SQLWARNING
condition exists. Since this constitutes a form of GOTO, and GOTO is
prohibited, I will not cover this topic.
Subject: What books are available for C programmers on the AS/400?
If you are programming in C on the AS/400, you'll be interested in
SC09-1821-00 AS/400 ILE C/400 Programmer's Reference
SC09-1820-00 AS/400 ILE C/400 Programmer's Guide
SC41-3420-00 TCP/IP Configuration and Reference
SC41-3422-00 OS/400 Sockets Programming
SC41-3606-00 AS/400 ILE Concepts
SC41-3801-00 AS/400 System API Reference (every API on the system)
SC41-3820-00 CPA Toolkit/400 Extensions Reference
SC41-3711-00 OS/400 Integrated File System Introduction
V3R2 (some of the books were not updated for this release)
SC09-1821-00 AS/400 ILE C/400 Programmer's Reference
SC09-1820-00 AS/400 ILE C/400 Programmer's Guide
SC41-3420-03 TCP/IP Configuration and Reference
SC41-3422-01 OS/400 Sockets Programming
SC41-3606-00 AS/400 ILE Concepts
SC41-3801-01 AS/400 System API Reference (every API on the system)
SC41-3802-01 CPA Toolkit/400 Reference
SC41-3711-01 OS/400 Integrated File System Introduction
SC09-2070-00 AS/400 ILE C/400 Programmer's Reference
SC09-2069-00 AS/400 ILE C/400 Programmer's Guide
SC41-3420-01 TCP/IP Configuration and Reference
SC41-4422-00 OS/400 Sockets Programming
SC41-4606-00 AS/400 ILE Concepts
SC41-4801-00 AS/400 System API Reference (every API on the system)
SC41-4802-00 CPA Toolkit/400 Reference
SC41-4711-00 OS/400 Integrated File System Introduction
SC41-4875-00 OS/400 UNIX-type APIs
GG24-4148-00 AS/400 ILE: A Practical Approach
SGÃ24-2572-00 Developing DCE Applications for OS/400
SG24-4438-00 UNIX C Applications Porting to AS/400
AS/400 Open Systems Enablement
Subject: How can I force records from an output only file to disk for another program to read?
What you need to do is use the FEOD (Force End Of Data) opcode
just prior to invoking the second module. This will force the data to be
written to the disk. The advantage of this is that it gives you complete
control, and it doesn't require any overrides to be performed in CL, or
have any bogus non-executing RPG code cluttering up your program.
Subject: What is the real cost of various types of program calls?
Here are some performance measurements which attempt to measure the cost
of transferring control from an ILE RPG program to a module written in
ILE RPG and to a module written in ILE C.
LOOPCALL1 uses CALL in a loop which iterates 50,000 times to call a RPG
IV program which has no C-specs.
LOOPCALLB1 uses CALLB in a loop which iterates 50,000 times to call a
RPG IV module which has no C-specs.
LOOPCALLB2 uses CALLB in a loop which iterates 50,000 times to call an
ILE C function which has no statements.
CALLXLATE1 uses CALLB in a loop which iterates 50,000 times to call a
RPG IV module which uses XLATE to translate a string which is passed as
a parameter to the module.
CALLXLATE2 uses CALLB in a loop which iterates 50,000 times to call an
ILE C function which uses the XLATEB MI instruction to translate a
string which is passed as a parameter to the function.
Results: (best times taken from each system)
CISC System (D80) RISC System (530)
Benchmark CPU(sec) CPU(sec)
LOOPCALL1 RPG->RPG 9.713 1.303
LOOPCALLB1 RPG->RPG 3.359 .290
LOOPCALLB2 RPG->C .562 .055
Bound call to XLATE function
CALLXLATE1 RPG->RPG 4.013 .351
CALLXLATE2 RPG->C .806 .115
Bob Donovan, IBM Rochester
Subject: How can I report security loopholes to IBM?
I have been informed by Fred Robinson of IBM that there is now an
abbreviated procedure that permits individuals without support-line to
report security loopholes privately to IBM. Last May, I spoke with Fred
and he informed me that one need do only do the following:
1. Call IBM at 1-800-274-0015.
2. Inform them that you wish to speak to the Duty manager.
3. Tell the Duty manager that you wish to report a security loophole.
Subject: How can I print Barcodes on an ASCII attached printer without IPDS?
Send your ASCII hex codes to the print file on a format that
has a TRNSPY CVTDTA command on it. You should be able to emulate your
current applications, although you still have the COR and emulation (if via
CA) issues to deal with.
The TRNSPY CVTDTA command sends the printer into transparent mode and
converts the data into ASCII.
Enterprise Systems Consulting, Inc.
Fuquay-Varina, NC USA
Subject: How can I route all batch queries to a particular subsystem?
You can write a routing program that looks for RUNQRY in the Request
Data. If it finds it, reroute the job to another job queue, If not
simply call QCMD.
I use this technique and it works fine. See the work mgmt guide for
info on writing a routing program.
Thanks to the following for contributing to this document (in no
Note: this section was created for the original version - new entries have an
acknowledgement at the end of the applicable section.
Larry Whitley <lwhitley@VNET.IBM.COM>
L. D. Whitley - System Performance, IBM Rochester, Minnesota
email@example.com (Ernie Malaga)
Senior technical editor for NEWS/400 magazine
"D. Sekus" <firstname.lastname@example.org>
Sr. Consulting Technical Analyst
email@example.com (Brian Phillips)
firstname.lastname@example.org (Chris Denman)
Computer Information Services
Chris Roberts (email@example.com)
Staffordshire University School of Computing, Stafford, United Kingdom.
firstname.lastname@example.org (rina ann)
Charles Ackerman, Publisher
email@example.com (Simon Ritchie)
Kenton Milligan - DB2/400 Development - IBM Rochester-AS/400 Division
firstname.lastname@example.org (JBA Support)
Guidelines Support (JBA)
- Where to get the most recent copy
- What AS/400 resources are available on the Internet?
- What books are available for the AS/400 system?
- What magazines/trade publications cover AS/400 topics?
- How do the new ILE objects relate to other systems?
- What is a User Space?
- What is Expert Cache, and will it help my system performance?
- How can I find an AS/400 User's Group?
- I'm looking for a job, why can't I find any job posts in the newsgroup?
- What is Expert Cache, and will it help my system performance?
- What resources are available if I am looking for AS/400 related employment?
- Saving OV/400 Distribution Lists
- Passing long character Parameters with SBMJOB
- Save while active
- Reducing processing delay for commonly used functions.
- How can I find an OS/400 Command?
- How can I do a system backup without operator intervention?
- How can I use SQL inside an RPG program?
- What books are available for C programmers on the AS/400?
- How can I force records from an output only file to disk for another program to read?
- What is the real cost of various types of program calls?
- How can I report security loopholes to IBM?
- How can I print Barcodes on an ASCII attached printer without IPDS?
- How can I route all batch queries to a particular subsystem?
Return to the Online Support Page