ProductsTechnical SupportDownloadsPurchasingSearch EngineNewsHomeE-mail Us

             Frequently Asked Questions about the IBM AS/400
                       Version 2.00

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


Subject: Contents

This FAQ contains the following subjects:

  1. Introduction.
  2. Disclaimer.
  3. Distribution
  4. Where to get the most recent copy
  5. What AS/400 resources are available on the Internet?
  6. What books are available for the AS/400 system?
  7. What magazines/trade publications cover AS/400 topics?
  8. How do the new ILE objects relate to other systems?
  9. What is a User Space?
  10. What is Expert Cache, and will it help my system performance?
  11. How can I find an AS/400 User's Group?
  12. I'm looking for a job, why can't I find any job posts in the newsgroup?
  13. What is Expert Cache, and will it help my system performance?
  14. What resources are available if I am looking for AS/400 related employment?
  15. Saving OV/400 Distribution Lists
  16. Passing long character Parameters with SBMJOB
  17. Save while active
  18. Reducing processing delay for commonly used functions.
  19. DB2/400
  20. How can I find an OS/400 Command?
  21. How can I do a system backup without operator intervention?
  22. How can I use SQL inside an RPG program?
  23. What books are available for C programmers on the AS/400?
  24. How can I force records from an output only file to disk for another program to read?
  25. What is the real cost of various types of program calls?
  26. How can I report security loopholes to IBM?
  27. How can I print Barcodes on an ASCII attached printer without IPDS?
  28. How can I route all batch queries to a particular subsystem?
  29. Acknowledgements
------------------------------ Subject: Introduction. This document discusses some questions and topics that occur or should occur repeatedly on the 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 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. ------------------------------ Subject: Disclaimer. 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. ------------------------------ Subject: Distribution 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 newsgroup in a text form. The most current HTML version is available from: *new* Mirrors of the HTML version are available at: United States: Europe: ------------------------------ Subject: What AS/400 resources are available on the Internet? Newsgroups: - Discussion of all AS/400 Issues alt.comp.synon - 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. *changed* - 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 than service. - 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. *new* - COMMON Argentina (Asociación Argentina de Dirigentes de Sistemas) *new* - Another midrange resources page. *new* - The Midrange Navigator Page (AS/400, 3x). Visitors are encouraged to contribute their Midrange links, info, etc. *new* - web site that contains tutorials and links to AS/400 sites. Mailing Lists: Midrange Discussion List Submission Address: Subscribe: Send "SUBSCRIBE midrange-l your name" to Code/400 Discussion List Submission Address: Subscribe: Send "SUBSCRIBE" to Midrange Discussion List - Submission Address: Subscribe: Send "SUBSCRIBE midrange" to *new* JDE Mailing List - Submission Address: Subscribe: E-MAIL TO: - for single messages only: in the body of your message specify: SUBSINGLE - for digest messages only: in the body of your message specify: SUBSCRIBE FAQ's: A Client Access/400 FAQ is being maintained by for Client Access/400 and ODBC drivers. FTP sites: 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 *new* 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. ISBN 0-9621825-0-8. Cozzi Research 29 W 120 Butterfield Road Suite 101 Warrenville, IL 60555 USA 1-(800) 522-9404 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 CBM Books/McGraw-Hill ISBN 0-07-707641-9 SQL/400 - A Professional Programmer's Guide By Tim Martyn, Tim Hartley and Richard Johnson McGraw-Hill ISBN 0-07-040799-1 Also, the following may be of some use. IBM AS/400 By J Hoskins Wiley Publications ISBN 0471599344 PC Users Guide to the AS/400 By B Kliewer et al Van Nostrand Re ISBN 0442308787 RPG III On The IBM AS/400 By W.J. Tomlinson Sigma Press ISBN 1850582874 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? News/400 P.O. Box 3438 Loveland, CO 80539 800-621-1544 or 970-663-4700 Midrange Computing 5650 El Camino Real, Suite 225 Carlsbad, CA 92008 Phone: 619-931-8615 Fax: 619-931-9935 Subscriber Services E-mail: AS/400 Magazine P.O. Box 6400 Duluth, MN 55806-6400 (507) 253-9847 3X/400 Systems Management 25 Northwest Point Road, SUite 800 Elk Grove Village, IL 60007 708-427-9512 Fax: 708-427-2006 Email: Midrange Systems 1300 Virginia Drive, Suite 400 Fort Washington, PA 19034 708-564-1385 Fax: 708-564-9002 ------------------------------ 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 user space. 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: COMMON 401 North Michigan Avenue, Chicago, IL 60611. Phone: 800-777-6734 or 312-644-6610. Fax: 312-245-1083 email: A list of local users group is available via Midrange Computing's home page at: ------------------------------ Subject: I'm looking for a job, why can't I find any job posts in the newsgroup? Job posts are prohibited in the 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 region. If you are a recruiter, you can send information on e-mail addresses, web pages, and other resources you provide to, and they will be included (although often edited for size) in the next subject in this document. 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 -,,, etc. ------------------------------ Subject: What resources are available if I am looking for AS/400 related employment? *new* 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 When requesting please use the word AS400 in your topic heading. 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 610 667-8735 (fax) 610 667-2990 (voice) *new* 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: CINDY PRESCOTT/RECRUITER Phone: 800-666-0730 ------------------------------ Subject: Saving OV/400 Distribution Lists If you ever need to save distribution lists for any reason, copy the files QUSRSYS/QAOPK02A and QUSRSYS/QAOPK03A to a different location and restore them later. These are the database files OV/400 uses to define and store your distribution lists. Keith Hatfull ------------------------------ 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. Andre Choquette ------------------------------ 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. (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 sharon@VNET.IBM.COM 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. (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: In-Line Subroutine Call Background (Data Queues) And two ways of encoding: In-Line /Copy 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 end The next would be Subroutine: 1 do 10 x exsr fun end fun begsr move testa testb testb chain file 99 end endsr 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 meg. (Lisa B.) ------------------------------ Subject: DB2/400 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? *new* 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 parameters. 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? *new* >We are trying to automate our full system backup to eliminate operator >intervention. >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 job. 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 there. Michael Green ------------------------------ Subject: How can I use SQL inside an RPG program? *new* 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 Basics 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: CRTSQLRPG 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 more details. The syntax for including SQL statements in your RPG source is: C/EXEC SQL statement C+ continuation of statement (optional) C/END-EXEC For example: C/EXEC SQL C+ UPDATE OA1320F1 C+ SET W2USER = ' ' C+ WHERE W2USER = :W2USER C+ AND DATE(W2TMST) <= CURRENT_DATE C/END-EXEC 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/EXEC SQL C+ UPDATE OA1320F1 C+ SET W2USER = ' ' C+ WHERE W2USER = :W2USER C+ AND DATE(W2TMST) <= CURRENT_DATE C/END-EXEC 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. Simple SELECT's: 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 variable maxord. 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. Using cursor's: 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 SELECT W2ORDN,W2TMST FROM OA1320F1 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: OPEN DTLACTN This is similar to opening a file. You can't read unless you open the file. Then you will process a row at time from the result set (similar to a read): 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 = 100). When you are done with the open cursor, you can close it with: CLOSE DTLACTN Pretty simple. 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 or 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 OA1320F1. Advanced Topics: 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. Vincent Greene ------------------------------ Subject: What books are available for C programmers on the AS/400? *new* If you are programming in C on the AS/400, you'll be interested in these books: V3R1 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 V3R6 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 Redbooks 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 Michael Mundy AS/400 Open Systems Enablement ------------------------------ Subject: How can I force records from an output only file to disk for another program to read? *new* 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. Matt Sargent ------------------------------ Subject: What is the real cost of various types of program calls? *new* 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. Benchmark Descriptions: 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) Dynamic call LOOPCALL1 RPG->RPG 9.713 1.303 Bound call 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? *new* 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. Steve Glanstein ------------------------------ Subject: How can I print Barcodes on an ASCII attached printer without IPDS? *new* 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. Dean Asmussen Enterprise Systems Consulting, Inc. Fuquay-Varina, NC USA E-Mail: DAsmussen@AOL.COM ------------------------------ Subject: How can I route all batch queries to a particular subsystem? *new* 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. Dan Riehl Seattle, Wa ------------------------------ Subject: Acknowledgements Thanks to the following for contributing to this document (in no particular order): 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 (Ernie Malaga) Senior technical editor for NEWS/400 magazine "D. Sekus" <> Douglas Hart Sr. Consulting Technical Analyst (Brian Phillips) (Chris Denman) Chris Denman Computer Information Services Chris Roberts ( Staffordshire University School of Computing, Stafford, United Kingdom. (rina ann) Charles Ackerman, Publisher (Simon Ritchie) Kenton Milligan - DB2/400 Development - IBM Rochester-AS/400 Division (JBA Support) Andrew Lamb Guidelines Support (JBA)
Return to the Online Support Page