Friday, October 14, 2011

SQL-WITH-RPGLE

C**** SQL-WITH-RPGLE
(Source Member Type - SQLRPGLE)
(Object Type Type - RPGLE) *****
D DS
D CUSTINFO            1                    25
D CUSTNUM            1                      4  0
D CUSTNAME          5                    24
C                               EVAL                 CUSTNUM = 1000
C/EXEC SQL
C+ SELECT CUSNO , CUSNAME
C+ INTO :CUSTNUM , :CUSTNAME
C+ FROM VBT/CLT
C+ WHERE CUSNO = :CUSTNUM
C/END-EXEC
C*****
C                                CUSTINFO DSPLY
C                               *CUSTNAME DSPLY
C*****
C                                 RETURN
C****

Define LDA Concept

   *FOR READING AND WRITING THE DATA AREA IN AS400/RPG.
D* LOCAL DATA AREA DATA STRUCTURE
D LDADS                  DS
D LDB                 4                  5
D LFNAM         81                90
D LFTRN         102             102
D LETP            105             105
D LPRD           108             108
************************************************************************************
C                 *DTAARA     DEFINE         *LDA             LDADS
C                                        IN                   LDADS                                                      READ LDA
C*Change Lda variable using program variable
C                                        OUT               LDADS                                                      WRITE LDA

Wednesday, October 12, 2011

Important Links For AS400 Topics

File Information Data Strecture

File Information Data Strecture

DINFDS ds
D File *FILE * File name
D OpenInd 9 9 * File open?
D EOFInd 10 10 * File at eof?
D FileStatus *STATUS * Status code
D OpCode *OPCODE * Last opcode
D Routinr *ROUTINE * RPG Routine
D ListNum 30 37 * Listing line
D SpclStat 38 42S 0 * SPECIAL status
D RecordFmt *RECORD * Record name
D MsgID 46 52 * Error MSGID
D Screen_P *SIZE * Screen size

* Open feedback area 81-240
*
D ODP_TYPE 81 82 * ODP Type
D FILE_NAME 83 92 * File name
D LIBRARY 93 102 * Library name
D SPOOL_FILE 103 112 * Spool file name
D SPOOL_LIB 113 122 * Spool file lib
D SPOOL_NUM 123 124I 0 * Spool file num
D RCD_LEN 125 126I 0 * Max record len
D KEY_LEN 127 128I 0 * Max key len
D MEMBER 129 138 * Member name
D TYPE 147 148I 0 * File type
D ROWS 152 153I 0 * Num PRT/DSP rows
D COLUMNS 154 155I 0 * Num PRT/DSP cols
D NUM_RCDS 156 159I 0 * Num of records
D ACC_TYPE 160 161 * Access type
D DUP_KEY 162 162 * Duplicate key?
D SRC_FILE 163 163 * Source file?
D VOL_OFF 184 185I 0 * Vol label offs
D BLK_RCDS 186 187I 0 * Max rcds in bl
D OVERFLOW 188 189I 0 * Overflow line
D BLK_INCR 190 191I 0 * Blk increment
D FLAGS1 196 196 * Misc flags
D REQUESTER 197 206 * Requester name
D OPEN_COUNT 207 208I 0 * Open count
D BASED_MBRS 211 212I 0 * Num based mbrs
D FLAGS2 213 213 * Misc flags
D OPEN_ID 214 215 * Open identifie
D RCDFMT_LEN 216 217I 0 * Max rcd fmt le
D CCSID 218 219I 0 * Database CCSID
D FLAGS3 220 220 * Misc flags
D NUM_DEVS 227 228I 0 * Num devs defin

D* I/O feedback area 241-366
D * 241-242 not used
D WRITE_CNT 243 246I 0 * Write count
D READ_CNT 247 250I 0 * Read count
D WRTRD_CNT 251 254I 0 * Write/read count
D OTHER_CNT 255 258I 0 * Other I/O count
D OPERATION 260 260 * Current operation
D IO_RCD_FMT 261 270 * Rcd format name
D DEV_CLASS 271 272 * Device class
D IO_PGM_DEV 273 282 * Pgm device name
D IO_RCD_LEN 283 286I 0 * Rcd len of I/O

D* POST area 241-nnn
D* Display D PGM_DEV_P 241 250 * Program device
D DEV_DSC_P 251 260 * Dev description
D USER_ID_P 261 270 * User ID
D DEV_CLASS_P 271 271 * Device class
D DEV_TYPE_P 272 277 * Device type
D REQ_DEV_P 278 278 * Requester?
D ACQ_STAT_P 279 279 * Acquire status
D INV_STAT_P 280 280 * Invite status
D DATA_AVAIL_P 281 281 * Data available
D NUM_ROWS_P 282 283I 0 * Number of rows
D NUM_COLS_P 284 285I 0 * Number of cols
D BLINK_P 286 286 * Allow blink?
D LINE_STAT_P 287 287 * Online/offline?
D DSP_LOC_P 288 288 * Display location
D DSP_TYPE_P 289 289 * Display type
D KBD_TYPE_P 290 290 * Keyboard type
D CTL_INFO_P 342 342 * Controller info
D COLOR_DSP_P 343 343 * Color capable?
D GRID_DSP_P 344 344 * Grid line dsp?
D* Device independent area 367-nnn
D* D* Printer
D CUR_LINE 367 368I 0 * Current line num
D CUR_PAGE 369 372I 0 * Current page cnt
D PRT_MAJOR 401 402 * Major ret code
D PRT_MINOR 403 404 * Minor ret code

D* Disk
D FDBK_SIZE 367 370I 0 * Size of DB fdbk
D JOIN_BITS 371 374I 0 * JFILE bits
D LOCK_RCDS 377 378I 0 * Nbr locked rcds
D POS_BITS 385 385 * File pos bits
D DLT_BITS 384 384 * Rcd deleted bits
D NUM_KEYS 387 388I 0 * Num keys (bin)
D KEY_LEN 393 394I 0 * Key length
D MBR_NUM 395 396I 0 * Member number
D DB_RRN 397 400I 0 * Relative-rcd-num
D KEY 401 2400 * Key value (max
D* * size 2000)

D* Display
D DSP_FLAG1 367 368 * Display flags
D DSP_AID 369 369 * AID byte
D CURSOR 370 371 * Cursor location
D DATA_LEN 372 375I 0 * Actual data len
D SF_RRN 376 377I 0 * Subfile rrn
D MIN_RRN 378 379I 0 * Subfile min rrn
D NUM_RCDS 380 381I 0 * Subfile num rcds
D ACT_CURS 382 383 * Active window
D* * cursor location
D DSP_MAJOR 401 402 * Major ret code
D DSP_MINOR 403 404 * Minor ret code

Program Status Data Strecture

Program Status Data Strecture
D SDS
D PROC_NAME *PROC Procedure name
D PGM_STATUS *STATUS Status code
D PRV_STATUS 16 20S 0 Previous status
D LINE_NUM 21 28 Src list line nu
D ROUTINE *ROUTINE Routine name
D PARMS *PARMS Num passed parms
D EXCP_TYPE 40 42 Exception type
D EXCP_NUM 43 46 Exception number
D PGM_LIB 81 90 Program library
D EXCP_DATA 91 170 Exception data
D EXCP_ID 171 174 Exception Id
D DATE 191 198 Date (DATE fmt)
D YEAR 199 200S 0 Year (YEAR fmt)
D LAST_FILE 201 208 Last file used
D FILE_INFO 209 243 File error info
D JOB_NAME 244 253 Job name
D USER 254 263 User name
D JOB_NUM 264 269S 0 Job number
D JOB_DATE 270 275S 0 Date (UDATE fmt)
D RUN_DATE 276 281S 0 Run date (UDATE)
D RUN_TIME 282 287S 0 Run time (UDATE)
D CRT_DATE 288 293 Create date
D CRT_TIME 294 299 Create time
D CPL_LEVEL 300 303 Compiler level
D SRC_FILE 304 313 Source file
D SRC_LIB 314 323 Source file lib
D SRC_MBR 324 333 Source file mbr
D PROC_PGM 334 343 Pgm Proc is in
D PROC_MOD 344 353 Mod Proc is in
D CURR_USER 358 367 Mod Proc is in

The Essential RPG IV Style Guide

Professional programmers appreciate the importance of standards in developing programs that are readable, understandable, and maintainable. The issue of programming style goes beyond any one language, but the introduction of the RPG IV syntax demands that we reexamine standards of RPG style. With that in mind, here are some simple rules of thumb you can use to ensure that bad code doesn't happen to good RPG software construction.

Use comments to clarify - not echo - your code.
Comments that merely repeat the code add to a program's bulk, but not to its value. In general, you should use comments for just three purposes:
to provide a brief program or procedure summary
to give a title to a subroutine, procedure, or other section of code
to explain a technique that isn't readily apparent by reading the source

Always include a brief summary at the beginning of a program or procedure.
This prologue should include the following information:
a program or procedure title
a brief description of the program's or procedure's purpose
a chronology of changes that includes the date, programmer name, and purpose of each change
a summary of indicator usage
a description of the procedure interface (the return value and parameters)
an example of how to call the procedure

Use consistent "marker line" comments to divide major sections of code.
For example, you should definitely section off with lines of dashes or asterisks the declarations, the main procedure, each subroutine, and any subprocedures. Identify each section for easy reference.

Use blank lines to group related source lines and make them stand out.
In general, you should use completely blank lines instead of blank comment lines to group lines of code, unless you're building a block of comments. Use only one blank line, though; multiple consecutive blank lines make your program hard to read.

Avoid right-hand "end-line" comments in columns 81-100.
Right-hand comments tend simply to echo the code, can be lost during program maintenance, and can easily become "out of synch" with the line they comment. If a source line is important enough to warrant a comment, it's important enough to warrant a comment on a separate line. If the comment merely repeats the code, eliminate it entirely.

Declarations


With RPG IV, we finally have an area of the program source in which to declare all variables and constants associated with the program. The D-specs organize all your declarations in one place.

Declare all variables within D-specs.
Except for key lists and parameter lists, don't declare variables in C-specs - not even using *LIKE DEFN. Define key lists and parameter lists in the first C-specs of the program, before any executable calculations.

Whenever a literal has a specific meaning, declare it as a named constant in the D-specs.
This practice helps document your code and makes it easier to maintain. One obvious exception to this rule is the allowable use of 0 and 1 when they make perfect sense in the context of a statement. For example, if you're going to initialize an accumulator field or increment a counter, it's fine to use a hard-coded 0 or 1 in the source.

Indent data item names to improve readability and document data structures.
Unlike many other RPG entries, the name of a defined item need not be left-justified in the D-specs; take advantage of this feature to help document your code:
D ErrMsgDSDS DS
D ErrPrefix 3
D ErrMsgID 4
D ErrMajor 2 OVERLAY(ErrMsgID:1)
D ErrMinor 2 OVERLAY(ErrMsgID:3)

Use length notation instead of positional notation in data structure declarations.
D-specs let you code fields either with specific from and to positions or simply with the length of the field. To avoid confusion and to better document the field, use length notation consistently. For example, code
D RtnCode DS
D PackedNbr 15P 5
instead of
D RtnCode DS
D PackedNbr 1 8P 5

Use positional notation only when the actual position in a data structure is important.
For example, when coding the program status data structure, the file information data structure, or the return data structure from an API, you'd use positional notation if your program ignores certain positions leading up to a field or between fields. Using positional notation is preferable to using unnecessary "filler" variables with length notation:
D APIRtn DS
D PackedNbr 145 152P 5
In this example, to better document the variable, consider overlaying the positionally declared variable with another variable declared with length notation:
D APIRtn DS
D Pos145 145 152
D PackNbr 15P 5
OVERLAY(Pos145)

When defining overlapping fields, use the OVERLAY keyword instead of positional notation.
Keyword OVERLAY explicitly ties the declaration of a "child" variable to that of its "parent." Not only does OVERLAY document this relationship, but if the parent moves elsewhere within the program code, the child will follow.

If your program uses compile-time arrays, use the **CTDATA form to identify the compile-time data.
This form effectively documents the identity of the compile-time data, tying the data at the end of the program to the array declaration in the D-specs. The **CTDATA syntax also helps you avoid errors by eliminating the need to code compile-time data in the same order in which you declare multiple arrays.

Naming Conventions


Perhaps the most important aspect of programming style deals with the names you give to data items (e.g., variables, named constants) and routines.
When naming an item, be sure the name fully and accurately describes the item.
The name should be unambiguous, easy to read, and obvious. Although you should exploit RPG IV's allowance for long names, don't make your names too long to be useful. Name lengths of 10 to 14 characters are usually sufficient, and longer names may not be practical in many specifications. When naming a data item, describe the item; when naming a subroutine or procedure, use a verb/object syntax (similar to a CL command) to describe the process. Maintain a dictionary of names, verbs, and objects, and use the dictionary to standardize your naming conventions.

When coding an RPG symbolic name, use mixed case to clarify the named item's meaning and use.
RPG IV lets you type your source code in upper- and lowercase characters. Use this feature to clarify named data. For RPG-reserved words and operations, use all uppercase characters.

Avoid using special characters (e.g., @, #, $) when naming items.
Although RPG IV allows an underscore (_) within a name, you can easily avoid using this "noise" character if you use mixed case intelligently.
Indicators


Historically, indicators have been an identifying characteristic of the RPG syntax, but with RPG IV they are fast becoming relics of an earlier era. To be sure, some operations still require indicators, and indicators remain the only practical means of communicating conditions to DDS-defined displays. But reducing a program's use of indicators may well be the single most important thing you can do to improve the program's readability.

Use indicators as sparingly as possible; go out of your way to eliminate them.
In general, the only indicators present in a program should be resulting indicators for opcodes that absolutely require them (e.g., CHAIN before V4R2) or indicators used to communicate conditions such as display attributes to DDS-defined display files.

Whenever possible, use built-in functions (BIFs) instead of indicators.
As of V4R2, you can indicate file exception conditions with error- handling BIFs (e.g., %EOF, %ERROR, %FOUND) and an E operation extender to avoid using indicators.

If you must use indicators, name them.
V4R2 supports a Boolean data type (N) that serves the same purpose as an indicator. You can use the INDDS keyword with a display-file specification to associate a data structure with the indicators for a display or printer file; you can then assign meaningful names to the indicators.

Use an array-overlay technique to name indicators before V4R2.
Using RPG IV's pointer support, you can overlay the *IN internal indicator array with a data structure. Then you can specify meaningful subfield names for the indicators. This technique lessens your program's dependence on numeric indicators. For example:
D IndicatorPtr * INZ(%ADDR(*IN))
D DS BASED(IndicatorPtr)
D F03Key 3 3
D F05Key 5 5
D CustNotFnd 50 50
D SflClr 91 91
D SflDsp 92 92
D SflDspCtl 93 93
C IF F03Key = *ON
C EVAL *INLR = *ON
C RETURN
C ENDIF
Use the EVAL opcode with *Inxx and *ON or *OFF to set the state of indicators.
Do not use SETON or SETOFF, and never use MOVEA to manipulate multiple indicators at once.

Use indicators only in close proximity to the point where your program sets their condition.
For example, it's bad practice to have indicator 71 detect end- of-file in a READ operation and not reference *IN71 until several pages later. If it's not possible to keep the related actions (setting and testing the indicator) together, move the indicator value to a meaningful variable instead.

Don't use conditioning indicators - ever.
If a program must conditionally execute or avoid a block of source, explicitly code the condition with a structured comparison opcode, such as IF. If you're working with old S/36 code, get rid of the blocks of conditioning indicators in the source.

4.6 Include a description of any indicators you use.
It's especially important to document indicators whose purpose isn't obvious by reading the program, such as indicators used to communicate with display or printer files or the U1-U8 external indicators, if you must use them.

Structured Programming Techniques


Give those who follow you a fighting chance to understand how your program works by implementing structured programming techniques at all times.

Don't use GOTO, CABxx, or COMP.
Instead, substitute a structured alternative, such as nested IF statements, or status variables to skip code or to direct a program to a specific location. To compare two values, use the structured opcodes IF, ELSE, and ENDIF. To perform loops, use DO, DOU, and DOW with ENDDO. Never code your loops by comparing and branching with COMP (or even IF) and GOTO. Employ ITER to repeat a loop iteration, and use LEAVE for premature exits from loops.

Don't use obsolete IFxx, DOUxx, DOWxx, or WHxx opcodes.
The newer forms of these opcodes - IF, DOU, DOW, and WHEN - support free-format expressions, making those alternatives more readable. In general, if an opcode offers a free-format alternative, use it.

Perform multipath comparisons with SELECT/WHEN/OTHER/ENDSL.
Deeply nested IFxx/ELSE/ENDIF code blocks are hard to read and result in an unwieldy accumulation of ENDIFs at the end of the group. Don't use the obsolete CASxx opcode; instead, use the more versatile SELECT/WHEN/OTHER/ENDSL construction.

Always qualify END opcodes.
Use ENDIF, ENDDO, ENDSL, or ENDCS as applicable. This practice can be a great help in deciphering complex blocks of source.

5.5 Avoid programming tricks and hidden code.
Such maneuvers aren't so clever to someone who doesn't know the trick. If you think you must add comments to explain how a block of code works, consider rewriting the code to clarify its purpose. Use of the obscure "bit-twiddling" opcodes (BITON, BITOFF, MxxZO, TESTB, TESTZ) may be a sign that your source needs updating.

Modular Programming Techniques


The RPG IV syntax, along with the AS/400's Integrated Language Environment (ILE), encourages a modular approach to application programming. Modularity offers a way to organize an application, facilitate program maintenance, hide complex logic, and efficiently reuse code wherever it applies.

Use RPG IV's prototyping capabilities to define parameters and procedure interfaces.
Prototypes (PR definitions) offer many advantages when you're passing data between modules and programs. For example, they avoid runtime errors by giving the compiler the ability to check the data type and number of parameters. Prototypes also let you code literals and expressions as parameters, declare parameter lists (even the *ENTRY PLIST) in the D-specs, and pass parameters by value and by read-only reference, as well as by reference.

Store prototypes in /COPY members.
For each module, code a /COPY member containing the procedure prototype for each exported procedure in that module. Then, include a reference to that /COPY module in each module that refers to the procedures in the called module. This practice saves you from typing the prototypes each time you need them and reduces errors.

Include constant declarations for a module in the same /COPY member as the prototypes for that module.
If you then reference the /COPY member in any module that refers to the called module, you've effectively "globalized" the declaration of those constants.

Use IMPORT and EXPORT only for global data items.
The IMPORT and EXPORT keywords let you share data among the procedures in a program without explicitly passing the data as parameters - in other words, they provide a "hidden interface" between procedures. Limit use of these keywords to data items that are truly global in the program - usually values that are set once and then never changed.

Character String Manipulation


IBM has greatly enhanced RPG IV's ability to easily manipulate character strings. Many of the tricks you had to use with earlier versions of RPG are now obsolete. Modernize your source by exploiting these new features.

Use a named constant to declare a string constant instead of storing it in an array or table.
Declaring a string (such as a CL command string) as a named constant lets you refer to it directly instead of forcing you to refer to the string through its array name and index. Use a named constant to declare any value that you don't expect to change during program execution.

Avoid using arrays and data structures to manipulate character strings and text.
Use the new string manipulation opcodes and/or built-in functions instead.

Use EVAL's free-format assignment expressions whenever possible for string manipulation.
When used with character strings, EVAL is usually equivalent to a MOVEL(P) opcode. Use MOVE and MOVEL only when you don't want the result to be padded with blanks.

Avoid Obsolescence


RPG is an old language. After 30 years, many of its original, obsolete features are still available. Don't use them.
Don't sequence program line numbers in columns 1-5.
Chances are you'll never again drop that deck of punched cards, so the program sequence area is unnecessary. In RPG IV, the columns are commentary only. You may use them to identify changed lines in a program or structured indentation levels, but be aware that these columns may be subject to the same hazards as right-hand comments (see style guideline 1.5).

Avoid program-described files.
Instead, use externally defined files whenever possible.

If an opcode offers a free-format syntax, use it instead of the fixed- format version.
Opcodes to avoid include CABxx, CASxx, CAT, DOUxx, DOWxx, IFxx, and WHxx.

If a BIF offers the same function as an opcode, use the BIF instead of the opcode.
With some opcodes, you can substitute a built-in function for the opcode and use the function within an expression. At V4R1, the SCAN and SUBST opcodes have virtually equivalent built-in functions, %SCAN and %SUBST. In addition, you can usually substitute the concatenation operator (+) in combination with the %TRIMx BIFs in place of the CAT opcode. The free-format versions are preferable if they offer the same functionality as the opcodes.

Shun obsolete opcodes.
In addition to the opcodes mentioned earlier (style guidelines 5.2 and 5.3), some opcodes are no longer supported or have better alternatives.

CALL, CALLB.
The prototyped calls (CALLP or a function call) are just as efficient as CALL and CALLB and offer the advantages of prototyping and parameter passing by value. Neither CALL nor CALLB can accept a return value from a procedure.

DEBUG.

With OS/400's advanced debugging facilities, this opcode is no longer supported.
You should use display file I/O to display information or to acquire input.

FREE.
This opcode is no longer supported.

PARM, PLIST.
If you use prototyped calls, these opcodes are no longer necessary.

Miscellaneous Guidelines


Here's an assortment of other style guidelines that can help you improve your RPG IV code.

In all specifications that support keywords, observe a one-keyword-per-line limit.
Instead of spreading multiple keywords and values across the entire specification, your program will be easier to read and let you more easily add or delete specifications if you limit each line to one keyword, or at least to closely related keywords (e.g., DATFMT and TIMFMT).

Begin all H-spec keywords in column 8, leaving column 7 blank.
Separating the keyword from the required H in column 6 improves readability.

Relegate mysterious code to a well-documented, well-named procedure.
Despite your best efforts, on extremely rare occasions you simply will not be able to make the meaning of a chunk of code clear without extensive comments. By separating such heavily documented, well-tested code into a procedure, you'll save future maintenance programmers the trouble of deciphering and dealing with the code unnecessarily.
Final Advice


Sometimes good style and efficient runtime performance don't mix. Wherever you face a conflict between the two, choose good style. Hard-to-read programs are hard to debug, hard to maintain, and hard to get right. Program correctness must always win out over speed. Keep in mind these admonitions from Brian Kernighan and P.J. Plauger's The Elements of Programming Style:
Make it right before you make it faster.
Keep it right when you make it faster.
Make it clear before you make it faster.
Don't sacrifice clarity for small gains in efficiency.

Convert Date (CVTDAT)

The Convert Date (CVTDAT) command is use to convert a Date but at the same time you can use it to check the validity of a date.

DCL VAR(&oneDat) TYPE(*CHAR) LEN(6)
DCL VAR(&OK) TYPE(*CHAR) LEN(1)
DCL VAR(&ERR) TYPE(*CHAR) LEN(8)

CHGVAR VAR(&OK) VALUE('0')

CVTDAT DATE(&oneDat) FROMFMT(*MDY) +
TOVAR(&ERR) TOFMT(*YYMD) TOSEP(*NONE)

MONMSG MSGID(CPF0550 CPF0551 CPF0552 CPF0553 +
CPF0555 CPF0556 CPF0557) +
EXEC(DO)
CHGVAR VAR(&OK) VALUE('1')
ENDDO

Call Subroutine (CALLSUBR)

pgm

Dcl Var(&Counter) Type(*Dec) Len(1 0)
Dcl Var(&CounterChr) Type(*Char) Len(1)
Dcl Var(&Exit) Type(*Int)

DoUntil Cond(&Exit *EQ 1)
CallSubr Subr(DoCalcs) RtnVal(&Exit)
EndDo
Return

Subr Subr(DoCalcs)
ChgVar Var(&Counter) Value(&Counter + 1)
CallSubr Subr(DspResults)
If Cond((&Counter / 2) *EQ 2) Then( +
RtnSubr RtnVal(1))
CallSubr Subr(DspResults)
EndSubr




Subr Subr(DspResults)
chgVar Var(&CounterChr) Value(&Counter)
SndUsrMsg Msg(&CounterChr) MsgType(*Info)
EndSubr

EndPgm

Tuesday, October 11, 2011

What is RPG

RPG is a programming language for business applications. Originally an initialism for Report Program Generator, it officially no longer stands for anything. Note that while the primary vendor of RPG has been and remains IBM, the language was available from other mainframe and microcomputer manufacturers, including Unisys. The latest incarnation of RPG is RPG IV (ILE RPG) on IBM's System i servers; it inherits the System i Integrated Language Environment’s features such as prototyped functions and procedures, static and dynamic binding, access to C routine libraries, dynamic link libraries, and fully recursive and re-entrant modular code.

Overview
RPG is the mainstay programming language of the IBM System i platform. Originally designed as a query tool, IBM has enhanced the language to become a full-fledged, powerful programming language.
An RPG program typically starts off with File Specifications, listing all files being written to, read from or updated, followed by Data Definition Specifications containing program elements such as Data Structures and dimensional arrays, much like a "Working-Storage" section of a COBOL program or var statements in a Pascal program. This is followed by Calculation Specifications, which contain the actual meat of the code. Output Specifications can follow which can be used to determine the layout of other files or reports. Alternatively files, some data structures and reports can be defined externally, mostly eliminating the need to hand code input and output specifications.
In the early days of RPG, its major strength was known as the program cycle: every RPG program executes within an implied loop, which can apply the program to every record of a file. At that time each record (individual punch card) would be compared to each line in the program, which would act upon the record, or not, based upon whether that line had an "indicator" turned "on" or "off" — from a set of logical variables numbered 01–99 for user-defined purposes, or other smaller sets based upon record, field, or report processing functions.
Alternatively, the cycle can make an interactive program continue to run until explicitly stopped.
Today, most RPG programmers avoid using the cycle in favor of controlling the flow of the program with standard looping constructs. The concept of level breaks and matching records is unique to the RPG II language. It was originally developed with card readers in mind. RPG III adds some interesting constructs, but the original RPG language is difficult to beat assuming the developer embraces all of the available constructs and features.

History
RPG is one of the few languages created for punch card machines that is still in common use today. This is because the language has evolved considerably over time. It was originally developed by IBM in the 1960s. The name Report Program Generator was descriptive of the purpose of the language: generation of reports from data files, including matching record and sub-total reports.
FARGO (Fourteen-o-one Automatic Report Generation Operation) was the predecessor to RPG. Both FARGO and RPG were intended to facilitate ease of transition for IBM tabulating machine unit record equipment technicians to the new IBM 1401 series of computers.
Tab machine technicians were accustomed to plugging wires into control panels or plug-boards to implement input, output, control and counter operations (add, subtract, multiply, divide). Tab machines programs were executed by impulses emitted in a machine cycle; hence, FARGO and RPG emulated the notion of the machine cycle with the program cycle. RPG was superior to and rapidly replaced FARGO as the report generator program of choice.
The alternative languages generally available at the time were either COBOL or FORTRAN: COBOL was a verbose business oriented language, FORTRAN was a language that facilitated mathematical applications. Other languages of the era included PL/1, ALGOL, and Autocoder. COBOL was more common in mainframe business operations (System/360 models 30 and above) and RPG was more common in shops that were transitioning from tabulating equipment (System/360 model 20).

Language evolution
RPG II was introduced with the System/3 series of computers. It was later used on System/32, System/34, and System/36, with an improved version of the language. ICL also produced a version on its VME/K operating system.
RPG III was created for the System/38 and its successor the AS/400 (a mid-range machine). RPG III significantly departed from the original language, providing modern structured constructs like IF-ENDIF blocks, DO loops, and subroutines (RPG2 Supported Subroutines). RPG III was also available for larger systems including the IBM System/370 mainframe running OS/VS1. It was also available from Unisys for the VS/9 operating system running on the Univac 90/60 mainframe.
DE/RPG or Data Entry RPG was exclusively available on the IBM 5280 series of data-entry workstations in the early 80s. It was similar to RPG III but lacking external Data Descriptions (DDS) to describe data(files) like on the System/38 and its successors. Instead, the DDS part had to be included into the RPG source itself.
RPG/400 with a much cleaner syntax, and tighter integration with the integrated database. This language became the mainstay of development on the AS/400, and its editor was a simple line editor with prompt templates for each specification (type of instruction).
RPG IV (aka RPGLE, aka RPG/ILE) was released in 1994 and the name, officially, was no longer an initialism. RPG IV offered a greater variety of expressions within its new Extended Factor-2 Calculation Specification.

RPG IV, a modern language
In 2001, with the release of OS/400 V5R1, RPG IV offered even greater freedom for calculations than offered by the Extended Factor-2 Calculation Specification: a free-format text-capable source entry, as an alternative to the original column-dependent source format. The "/FREE" calculation does not require the operation code to be placed in a particular column; the operation code is optional for the EVAL and CALLP operations; and syntax generally more closely resembles that of mainstream, general-purpose programming languages.
Today, RPG IV is a considerably more robust language. Editing can still be done via the simple editor or it can be edited via PC using IBM's Websphere Development Studio (a customized implementation of Eclipse). IBM is continually extending its capabilities and adding more built-in functions (BIFs). It has the ability to link to Java objects (See IBM's RPG Reference Manual), and i5/OS APIs; it can be used to write CGI programs with the help of IBM's Cgidev2 web toolkit, RPG xTools CGILIB and other commercial Web enabled packages. Even with the changes it retains a great deal of backward compatibility, so an RPG program written 37 years ago could run today with little or no modification.
OS/400 was later renamed i5/OS to correspond with the new IBM System i5 branding initiative; the 5 was later dropped in favor of just System i. In March 2008 i5/OS was renamed IBM i as part of the Power Systems consolidation of System i and System p product lines. The new Power Systems also adopt more mainstream version numbers, substituting 6.1 for the twenty year old V1R1M0 notation. The latest release is now referred to as IBM i 6.1 and fully supports the RPG IV language, as well as many others. WebSphere Development Studio Client (WDSC) is now referred to as Rational Developer for i (RDi), of which three product levels are available. They are called Rational Developer for i (RDi), RDi Service Oriented Architecture (RDi SOA), and Rational Application Developer (RAD). The new lineup provides in more granular packaging all of the development tools and support previously offered by WDSC and WDSC Advanced Edition

Platforms
As stated above, the RPG programming language originally was introduced by IBM for their proprietary 1401, /360, /3, /32, /34, /36, /38 AS/400 and System i systems. There have also been implementations for the Digital VAX, Sperry Univac BC/7, Univac system 80, Siemens BS2000, Burroughs B1700, Hewlett Packard HP3000, ICL 2900 series, Honeywell 6220 and WANG VS, as well as miscellaneous compilers & runtime environments for Unix-based systems (INFINITE 36 (formerly UNIBOL36)) and PCs (Baby/400, Lattice-RPG).
RPG II applications are still supported under the HP MPE operating system on HP3000 and the OpenVMS operating system on VAX, Alpha, Unisys MCP, and Integrity processors by the third party Migration RPG compiler

Creating AS400 Query WRKQRY

Creating an as400 Query

As400 Query, QUERY/400 as it used to be called or IBM Query for Iseries, which is whats shown these days on the Installed Licensed Programs screen (GO LICPGM Option 10) is probably one of the most widely used tools on the as400. This tool has a very straightforward user interface, so is often used by both technical and non technical staff (with relevant training of course). Its a very easy to use tool for displaying, extracting and reporting on data. Query is a separate chargeable product, but I don’t think I have ever worked at a site that didn’t have it installed on all their systems, so you can be pretty sure almost every AS/400 will have the IBM provided query language QUERY/400.

As an example, we will write a query over a typical database file that almost every business system has, the CUSTOMER master file. After extracting the data we will create a report.
At the as400 command line type in WRKQRY and hit ENTER:

----------------------------------------------------------------
Work with Queries

Type choices, press Enter.

Option . . . . . . 1=Create, 2=Change, 3=Copy, 4=Delete
5=Display, 6=Print definition
8=Run in batch, 9=Run
Query . . . . . . . Name, F4 for list
Library . . . . . QGPL Name, *LIBL, F4 for list
-----------------------------------------------------------------

We will create a query to view the data in the CUSTOMER master file. Type in 1 for the option to create a query and the press ENTER.

-----------------------------------------------------------------
Define the Query

Query . . . . . . : Option . . . . . : CREATE
Library . . . . : QGPL CCSID . . . . . . : 65535

Type options, press Enter. Press F21 to select all.
1=Select

Opt Query Definition Option
1 Specify file selections
Define result fields
Select and sequence fields
Select records
Select sort fields
Select collating sequence
Specify report column formatting
Select report summary functions
Define report breaks
Select output type and output form
Specify processing options
-----------------------------------------------------------------

Next "Specify file selections". This is already selected by default so just hit ENTER and you will see:

--------------------------------------------------------------
Specify File Selections

Type choices, press Enter. Press F9 to specify an additional
file selection.

File . . . . . . . . . Name, F4 for list
Library . . . . . . QGPL Name, *LIBL, F4 for list
Member . . . . . . . . *FIRST Name, *FIRST, F4 for list
Format . . . . . . . . *FIRST Name, *FIRST, F4 for list

---------------------------------------------------------------
Enter CUSTOMER (or whatever your customer master file is called) as the file you want to query and YOURLIB (replace with your relevant database library) as the Library and press ENTER. Pressing ENTER again will take you back to the Define the Query screen.

The next option down, the Define result fields option, is described thus: “Select this option either to define one or more new fields to
be used as result fields or to see what result fields are already defined for the query. The new fields will hold the results of arithmetic, character, or date and time operations done on other fields in this query”. In this case we don’t need any result fields so we can ignore this option for now.

We do need to select fields that we want to report on. Type 1 next to "Select and Sequence Fields" and press ENTER
You should now see:

----------------------------------------------------------------
Type sequence number (0-9999) for the names of up to 500 fields to
appear in the report, press Enter.

Seq Field Text Len Dec
CUSTN Customer Number 10 0
CUSTNA1 Cust. Name & Address 1 50
CUSTNA2 Cust. Name & Address 2 50
CUSTNA3 Cust. Name & Address 3 50
CUSTNA4 Cust. Name & Address 4 50
CUSTTOWN Customer Town 10
CUSTREPN Customer Report Name 20
LOCCD Location Code 5


More...
F3=Exit F5=Report F11=Display names only F12=Cancel
F13=Layout F20=Renumber F21=Select all F24=More keys
----------------------------------------------------------------

Since the AS/400 Query knows the file you want to see, as you selected it earlier, it has retrieved the fields in the file and listed them for you to choose from.
Weare going to select the following columns:
cust number
cust name and address 1
cust town
location code
We need to enter a number to select each of the fields that we require, so enter a number next to each field, which will determine the order in which they are displayed. Enter 2 next to CUSTNA1, 1 next to CUSTN, 4 next to CUSTTOWN, and 6 next to LOCCD. Press ENTER twice and then you will be back at the definition screen.

as400 QUERY allows you to view the results of the query while you are modifying it, which is handy as it allows you correct any errors there and then

---------------------------------------------------------------
Display Report
Report width . . . . . : 115
Position to line . . . . . Shift to column . . .
....1....+....2....+....3....+....4....+....5....+....6....+....7
Customer Name and Address 1 Town Location
Number Code
000001 000001 Davis, 2 High St London 010
000002 000012 Charles, 1 High St London 010
000003 000015 Fish, 3 High St London 010
000004 000020 Brompton, 4 High St London 010

-----------------------------------------------------------------

This is the first steps to creating a query that shows records on an as400 database file.

The standard IBM CUA window keys are available on this screen. F20 will move the data window to the right, F19 will move the data window back to the left. F21 will split the screen into two mini windows, one fixed and one moveable, so you can view 2 parts of the data similultaneously. F12 to cancel returns to the main definition screen

Next is to take option 1 for Column Formatting to modify the column headings to be more descriptive. This allows you to change them to be more meaningful to the out being produced.

To change the sequencing of the query, type 1 next to "Select Sort Fields". Typing a 1 next to the customer number field will ensure that the data is produced in customer number order. Pressing F5 will allow review of the result of applying the sort to the customer number field.

The next step is to decide which records to select. Enter a 1 next to select records. In this case we only want to select records with a location code of '030'. The screen will look like:

----------------------------------------------------------
Select Records

Type comparisons, press Enter. Specify OR to start each new
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISN

AND/OR Field Test Value (Field, Number, 'Char
LOCCD EQ '030'

----------------------------------------------------------
Now the extract will only show customers with a location code of '030'.

Now we need to save the query. Pressing F3 and filling in detals give:

-------------------------------------------------------------
Exit this Query

Type choices, press Enter.

Save definition . . . Y Y=Yes, N=No

Run option . . . . . . 3 1=Run interactively
2=Run in batch
3=Do not run

For a saved definition:
Query . . . . . . . TEMPQRY Name
Library . . . . . QGPL Name, F4 for list

Text . . . . . . . . Display CUSTOMER Master File
---------------------------------------------------------------

And to run the query, from an AS400 command line, key in the command to run a query:

RUNQRY QGPL,TEMPQRY

This should show the results for the as400 query.

OPNQRYF IN AS400

This is my Physical file 'EMPS' existing in library "TAMIL1" and its record format is 'EMPRCD"
a)its source is

R EMPRCD
ENO 4 0
ENAME 10
EADD 10

b)The records present in EMPS are as follows

ENO ENAME EADD

0001 tamil coimbatore
0002 kumar bangalore
0003 sunder bangalore
0004 arunkumar chennai
0005 pandi hyderabad
0006 santhosh hyderabad
0007 sasi salem
0008 kalai chennai
0009 suresh hyderabad
0010 vijay bangalore
0011 Arul chennai
0012 velu chennai
0013 khan bangalore
0014 praba chennai
0015 praba.p coimbatore
0016 anand ooty
0017 raja erode
0018 sankar erode
0019 vadivel namakkal
0020 anbu chennai
0021 Ajith mumabi

c)now i want to select the 'ENAME' field records starting with 'S'
for that i have created a RPG program
its source code is

PGM
DCLF FILE(TAMIL1/EMPS)
DCL VAR(&MYENO) TYPE(*CHAR) LEN(4)
OVRDBF FILE(EMPS) SHARE(*YES)
OPNQRYF FILE((TAMIL1/EMPS)) QRYSLT('ENAME *EQ %WLDCRD("S*")')
READ: RCVF RCDFMT(EMPRCD)
MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(END))
CHGVAR VAR(&MYENO) VALUE(&ENO)
SNDUSRMSG MSG(&MYENO)
SNDUSRMSG MSG(&ENAME)
SNDUSRMSG MSG(&EADD)
GOTO READ
END: CLOF OPNID(EMPS)
DLTOVR FILE(EMPS)
ENDPGM

Saturday, October 8, 2011

What is Service Program?

A Service Program is a collection of runnable procedures and available data items easily accessible by other ILE programs. In many respects it is similar to a subroutine library and procedure library.
A service program differs from a program in two ways:
It does not contain a program entry procedure. This means that you cannot call a service program using the CALL operation.
A service program is bound into a program or other service programs using binding by reference.

OVERLAY

OVERLAY
It allows a record format to be displayed on screen retaining the previous displayed record formats.

EDTCDE & EDTWRD

EDTCDE & EDTWRD
are key words used for formatting purpose. EDTCDE cannot be applied to Character filed. And EDTCDE has some Codes pre-defined for example, EDTCDE(Z) – for suppressing the leading zero Y – for date field.
EDTWRD can be used to define user defined formatting for a fields.

Command Attention and Command Function (CA & CF)

Command Attention key and Command Function Key ?

With the help of Command attention key we can pass only the indicator status to program not the data from screen. While command function key passes indicator status as well as a data from screen to program.

Monday, October 3, 2011

*PSSR - Error handling in RPG

Excellent! Nice to see an AS/400 programmer caring about error handling for a
change.

There are three major error handling techniques in RPG; do nothing and let the
default error handler take
control (that's the one which displays something like "Error occurred at line
1234 (C G D S)"), use resulting
indicators and TEST their status, or code automatic subroutines to handle
errors.

There are two possible subroutines; A file error subroutine (INFSR) and a
program error subroutine (*PSSR).
File error routines can be given any name but need to be associated with the
file. That is done on the
F-spec by using the INFSR keyword. The program error subroutine MUST be called
*PSSR and simply having it
declared in the program is enough for the RPG compiler to pass control to it
when an unmonitored (no
resulting indicator) exception occurs. Note that the *PSSR WILL NOT
automatically get control for I/O
exceptions however the *PSSR can be used for both purposes if you name the
*PSSR as the INFSR routine. Both
the file and program error routines can be invoked by EXSR e.g., EXSR *PSSR.

Now for the bad news: When the file and program error routines are invoked by
the RPG exception handler due
to an unmonitored exception it is done as an unconditional branch, a GOTO if
you will, rather than an EXSR.
This makes it very difficult to determine the return point. The return points
provided by RPG (*GETIN,
*DETC, etc) are parts of the cycle and are effectively targets for a GOTO from
the ENDSR statement in the
*PSSR. *GETIN is the start of the Get Input section of the logic cycle. It is
not possible to provide your
own branch points on the ENDSR statement.

Usually what one does is to use the error routines to gracefully exit the
program rather than try to recover.
However it may be possible to use the STATUS code fields in the INFDS or SDS
data structures to determine if
the error is recoverable and design logic into your program such that the *PSSR
can determine where it was
called from; a status flag of some sort which is tested and then an
unconditional branch is performed to the
appropriate section of the program. This is usually SO UGLY that I cannot
commit such an atrocity and mostly
send a nice screen to the user telling them why the program broke, send a
message to QSYSOPR, dump the
program, job, and joblog, and cleanly close down.

Russ Popiel wrote a book called "RPG Exception Handling" available from Duke
Press (News/400 Magazine) which
is fairly good.

Here is an extract from my template showing how this stuff is defined. Note
that the SDS is externally
described and has the fields #STSCD and #LFUST, etc. The DUMP subroutine is
the one which decides whether to
dump the program, joblog, job, etc. You would test the value of the DBFSTS or
DSPSTS fields in the *PSSR to
determine if the error was recoverable. A SELEC block with tests and GOTOs
would work (ugly but no other
choice)
***************************************************************************
FABC2 UF A E K DISK InfSR(*PSSR)
D*************************************************
C MOVE '12' KEY 2
C KEY CHAIN ABC2 90
C IF NOT%FOUND(ABC2)
C MOVE KEY VBID
C MOVE 'AAAAAA' VBNAME
C WRITE ABCR2
C ELSE
C UPDATE ABCR2
C ENDIF
C
C*************************************************
C EVAL *INLR = '1'
C *PSSR BEGSR
C RETURN
C ENDSR
***************************************************************************

Sunday, October 2, 2011

User Defined Command

 Creating the User Defined Command :-
The File has two Filed :- 
OPTION     CHAR            2      
COMMAND    CHAR     250  
While Writing the Command some important Paramater Shorcut Required 
&L = Library
&N = Object
&F = Source File
&O = Object
            Option    Command

000001    C    CALL &O/&N                                                                                              
000002    X    SIGNOFF                                                                                                 
000003   AR    CALL PGM(MEDPRIME/ARCSRC) PARM(&F &N &O)                                                                
000004   BR    DSPPFM FILE(&L/&N)                                                                                      
000005   CC    CHGCURLIB CURLIB(&L)                                                                                    
000006   CL    CHGCURLIB CURLIB(&N)                                                                                    
000007   CD    STRDFU OPTION(2)                                                                                        
000008   CM    STRSDA OPTION(2) SRCFILE(&L/&F) ??SRCMBR()                                                              
000009   CP    SBMJOB CMD(CALL PGM(SPOVRCMP) PARM(&F &N &L &S &O)) JOB(&N)                                             
000010   CS    STRSDA OPTION(1) SRCFILE(&L/&F) ??SRCMBR()                                                              
000011   CX    CMPPFM NEWFILE(QCLSRC) OLDFILE(P#00000263/NEWCLSRC) OUTPUT(*PRINT)                                      
000012   C1    CRTBNDRPG PGM(&L/&N) SRCFILE(&L/&F) SRCMBR(&N) DBGVIEW(*LIST) FIXNBR(*ZONED *INPUTPACKED) TGTRLS(*PRV)  
000013   C2    CRTCLPGM PGM(&L/&N) SRCFILE(&L/&F) SRCMBR(&N) OPTION(*LSTDBG)                                           
000014   DF    DSPFFD FILE(&L/&N)                                                                                      
000015   DL    DLTSPLF FILE(*SELECT)                                                                                   
000016   DM    DSPMSG                                                                                                  
000017   DO    DLTOVR FILE(*ALL)                                                                                       
000018   DP    dsppgmref PGM(&L/&N)                                                                                    
000019   EA    EDTOBJAUT OBJ(&L/&N) OBJTYPE(&T)                                                                    
000020   ED    enddbg                                                                                                              
000021   EL    EDTLIBL                                                                                                             
000022   FB    CALL PGM(FEUPRO/STRFEUPDM) PARM(&L &F &N B N &T)                                                                    
000023   FE    CALL PGM(FEUPRO/STRFEUPDM) PARM(&L &F &N E N &T)                                                                    
000024   GO    GO &L/&N                                                                                                            
000025   IF    CALL PGM(PBIFSETC) PARM(&F &N &O)                                                                                   
000026   IN    call utchkinc (&F &N &O)                                                                                            
000027   JL    DSPJOBLOG                                                                                                           
000028   LE    SBMJOB CMD(CRTBNDRPG PGM(&O/&N) SRCFILE(&L/QRPGLESRC) SRCMBR(&N) DBGVIEW(*SOURCE) FIXNBR(*ZONED *INPUTPACKED)) JOB(&n)
000029   LO    SBMJOB CMD(CRTBNDRPG PGM(&O/&N) SRCFILE(&L/QRPGLESRC) SRCMBR(&N) FIXNBR(*ZONED *INPUTPACKED)) job(&n)               
000030   L1    CRTBNDRPG PGM(&O/&N) SRCFILE(&L/&F) SRCMBR(&N) DBGVIEW(*SOURCE) FIXNBR(*ZONED *INPUTPACKED)                         
000031   OV    OVRDBF FILE(EMP) TOFILE(&L/&F) MBR(&N)                                                                              
000032   RE    RPGEXT/STRRPGEXT FROMFILE(&L/&F) FROMMBR(&N) TOFILE(MEDEXTDF/QRPGLESRC) COMPARE(Y)                                  
000033   RQ    RUNQRY *N &L/&N                                                                                                     
000034   SD    STRDBG PGM(*LIBL/&N) UPDPROD(*YES) OPMSRC(*yes)                                                                     
000035   SL    SBMJOB ??CMD(SAVLIB LIB(&N))                                                                                        
000036   SM    SBMJOB ??CMD(SAVOBJ OBJ(&F) LIB(&L) OBJTYPE(*FILE) FILEMBR((&F (&N))))                                              
000037   SO    SBMJOB ??CMD(SAVOBJ OBJ(&N) LIB(&L))                                                                                
000038   SP    WRKSPLF                                                                                                             
000039   SQ    STRSQL                                                                    
000040   S1    chgpf    (&L/&N) maxmbrs(200)                                             
000041   TS    CALL PGM(TESTOPTC) PARM(&S &T)                                            
000042   WA    wrkactjob                                                                 
000043   WM    WRKOBJPDM  QTEMP                                                          
000044   WO    WRKOBJLCK OBJ(&L/&N) OBJTYPE(*FILE)                                       
000045   WS    WRKSBMJOB                                                                 
000046   IO    ?IMPPART OBJ(&L/&N) OBJTYPE(&T) TYPE(&S) PART(&N) TEXT(&X)                
000047   IM    ?IMPPART OBJ(&L/&F) OBJTYPE(*FILE) MBR(&N) PART(&N) LANG(&S) TEXT(&X)     
000048   AP    ADDPRJLIBL PRJ(&ZP) GRP(&ZG) SCAN(&ZH) SCHPTH(&ZS)                        
000049   RP    RMVPRJLIBL                                                                
****** ********  End of report  ********                                                 
   On the WRKMBPDM
Press :- >Change Defaults (F18)

Option file  . . . . . . . .   MPPOPT      
  Library  . . . . . . . . .     KMGWRKLIB 
Member . . . . . . . . . . .   MPPOPT      



Basic SQL Tips


CREAT A TABLE
--------------
CREATE TABLE EMPLOYEE (EMPID  INT, EMPNAME CHAR(10),EMPADD CHAR(20))

CREATE TABLE EMPTAB2(EMPID  INT, EMPNAME CHAR(10),EMPADD CHAR(20))

INSERT THE VALUE
----------------
INSERT INTO EMPTAB2 VALUES(100,'VIDYA BHUSHAN','MADHUBANI','98717103
 67')  

 INSERT INTO EMPTAB2 VALUES(100,'VIDYA BHUSHAN','MADHUBANI','98717103
 67')            
                                                 
RETRIVE THE RECORDS
--------------------
SELECT * FROM KMGEDU5/EMPTAB2

UPDATE THE RECORDS
------------------
UPDATE KMGEDU5/EMPTAB2 SET EMPMOB='99999999' WHERE EMPID=100
                             
SELECT * FROM KMGEDU5/EMPTAB2

DELETE THE RECORDS
------------------
DELETE EMPADD FROM AMGEDU5/EMPTAB2 WHERE EMPID=100

DELETE EMPMOB FROM KMGEDU5/EMPTAB2 WHERE EMPID=100

INSERT THE RECORDS
------------------                                                                    
INSERT INTO KMGEDU5/EMPTAB2 VALUES(100, 'VIDYA' ,'MADHUBANI','987171
0367')

UPDATE THE RECORDS
-------------------
UPDATE EMPTAB2 SET EMPMOB= ' ' WHERE EMPID =100

RETRIVE THE RECORDS
-------------------
SELECT * FROM EMPTAB2

CREATE THE TABLE
-----------------
CREATE TABLE KMGEDU5/EMP (EMPNAME FOR COLUMN EMPL_NAME VARCHAR (25
), EMPSAL FOR COLUMN EMPL_SAL DECIMAL (9 , 2) NOT NULL, EMPADD FOR
COLUMN EMPL_ADD VARCHAR (25 ), EMPDES FOR COLUMN EMPL_DESGN VARCHAR
(25 ), EMPACNO FOR COLUMN EMPL_ACCNT DECIMAL (14 ))

INSERT THE VALUES
-----------------
INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
VALUES('VIDYA BHUSHAN THAKUR', 10000, 'NOIDA', 'S/W TRAINEE', 27262)

INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
VALUES('VIDYA BHUSHAN THAKUR', 10000, 'NOIDA', 'S/W TRAINEE', 27262)

INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
VALUES('SAURABH RAI', 12000.50, 'BINA INDORE', 'SW TRAINEE',    
225632)                                                          

INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
VALUES('GUNJAN GUPTA, 12200.50, 'LOCKNOW', 'SW TRAINEE',          
256632)                                                          

 INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
 VALUES('GUNJAN GUPTA', 12500.00, 'LOCKNOW', 'SW TRAINEE', 2567891)

 INSERT INTO KMGEDU5/EMP (EMPNAME, EMPSAL, EMPADD, EMPDES, EMPACNO)
 VALUES('KIRAN REDDY', 12500.00, 'HYDRABAD, 'SW TRAINEE', 2567891)

UPDATE THE VALUES
-----------------
UPDATE KMGEDU5/EMP EMPNAME SET EMPNAME = 'GUNJAN GUPTA', EMPSAL =
15000.00 WHERE EMPSAL >= 10000                                                                  

RLU in AS400


Showcase Article - Report Layout Utility (RLU)
URL:-> Http://www.texas400.com/SArlu.html
Learning to use the tools on the AS/400 is one of the best ways to leverage your expertise. Managers may be surprised to learn how easy it is to provide users with sample report layouts that can be designed with an on-line tool. This on-line tool is RLU, Report Layout Utility. Because many programmers find its interface to be confusing, not many have become proficient RLU users. Read on and you will learn to create report layouts quickly and easily.
In the early days of RPG, reports were always printed using OUTPUT specifications. With the advent of DDS, it became possible to make print files using DDS. This is similar to the way DDS is used to make display files. The AS/400 provides SDA, Screen Design Aid, as an on-line tool to design display files. Similarly, RLU, is an on-line tool to design reports.
Suppose a user needs a report showing:
1.  Customer number
2.  Customer name
3.  Customer city
4.  Annual purchase amount
These fields are in the customer master file, CUST. We will write an RPG program, CUSR005 to read the CUST file and write a print file, CUSW005.
Figure 1 shows the finished report that we will design with RLU. To start RLU, enter the command STRRLU and hit F4. Enter QDDSSRC as the source file. Enter the library name and CUSW005 as the source member. Since this is a narrow report, enter 65 for the page width. For a standard width report, you would use 132 for the page width.
RLU now displays its DESIGN REPORT screen. RLU uses so many function keys that there is a base set of function keys and an alternate set of keys. F22 is used to toggle between the base keys and the alternate keys. Right now RLU shows the word, BASE in the upper left portion of the screen to indicate that the base set of function keys is in use. F22 changes to the alternate keys and the changes the display from BASE to ALT.
The simplest way to use RLU is to define each line as a separate record. To start working with a line of the report, key DR for DEFINE RECORD in the command area on a blank line. When you hit the enter key, RLU names the record with the default name RCD001. After defining a record, enter VF for VIEW FIELDS in the command area of the same line. RLU then adds a line above the record to show the field information.
The heading for the report will be CUSTOMER ANNUAL PURCHASES. On the line with RCD001, key in the heading. Then, convert this string of characters to a constant. Do this by placing the cursor on the first character of the heading and hitting F11 for DEFINE FIELD. Hit F11 again and then ENTER to convert the string of characters to a constant. Finally, center the heading by keying CF for CENTER FIELD on the command portion of the RCD001 line. When you hit ENTER, the heading will center.
Now, create a second record. This will be used to print the customer fields. First insert a line to work with. Key I for INSERT in the command area of the RCD001 line and hit ENTER. Then, as before, key DR to define this as a record and then VF for VIEW FIELDS.
Next, use RLU to retrieve the field definitions from the master file. Hit F10 for DATABASE FIELDS. Key 1 for the option to ADD FIELD. Leave the FIELD value blank. Enter the library name and CUST for the File. Enter the record name of the CUST file. RLU will then retrieve all the fields in the CUST file and list them. Select the fields for the report. Hit ENTER until you return to the DESIGN REPORT screen.
The names of the fields you selected are at the bottom of the screen. Each field is sequentially numbered. To insert the first field in the report, key 1 in the text area of the FLD record above the detail record line. When you hit ENTER, RLU puts that field in the layout. RLU has re-sequenced the remaining fields. Place the remaining fields in the report.
If you overlap fields, RLU can get confusing. The simplest way to recover is to delete the record by keying a D in the command area of the record. Then start the record over. If you want to remove a field, put the cursor on the field and hit F16. If you need to move a field, use F13 to mark where the cursor is positioned and then F15 to move the field to a new location.
Now that you have placed the fields in a detail record, create headings for the detail line. Insert a line above the detail line. As always, key DR for DEFINE RECORD and then VF to VIEW FIELDS. Simply key the headings in the text area of the blank record that you inserted. Then convert each field to a constant by putting the cursor on the first character of each literal and hitting F11 twice.
To help visualize the report, create some sample data lines. Make 5 copies of the detail line by keying SD5 on the command line of the detail line.
Now, add a line at the bottom of the report for the total annual purchases. Do this by keying I in the command line of the last record on the screen. As always, key DR and then VF in the command area of the record.
Key the word TOTAL in the text area of the record. Convert the word to a constant by placing the cursor on the first letter of the word and hitting F11 twice. Now, add a new field for the accumulated total. This can get confusing.
Place the cursor where you want the total amount to print. Hit F11 to DEFINE FIELD. On the DEFINE FIELD INFORMATION screen, change the name of the field from the default FLD001 to TOTPUR. Change the length of data to 9. Hit PAGE DOWN. Change the data type to 2 for ZONED and enter 2 for decimal positions. Then hit F10 to WORK WITH KEYWORDS. Select EDTCDE by keying 2 next to it. Hit ENTER and key 1 for the EDTCDE value. Hit ENTER a few times and you will see that you have added a field for the total purchases.
Finally, add the date and page counter to the first heading line. Do this by adding a field of the appropriate length, converting it to a constant and then selecting the appropriate keyword. Also, rename the records by using F18 for RECORD KEYWORDS and then F10 to rename the record.
Exit RLU with F3. You can save the DDS, compile it and generate a prototype report all from the exit screen.
Figure 2 shows the complete RPG program to print the report.

Figure 1 - This is the finished report designed by RLU.                                                                   FLD1    <........>          <.......................>       <..>   <...>         HDG1 01/04/2001          CUSTOMER ANNUAL PURCHASES       Page   9,999      FLD1    <..>    <...........>             <..>           <..........>            HDG2 CUS#    CUSTOMER NAME             CITY           ANNUAL PURCH         00004 +                                                                       FLD1    <....>  <.......................> <.............>   <.......>             DTL 999999  XXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXX   99,999.99         00006 S 999999  XXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXX   99,999.99         00007 S 999999  XXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXX   99,999.99         00008 S 999999  XXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXX   99,999.99         00009 S 999999  XXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXX   99,999.99         00010 S 999999  XXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXX   99,999.99         FLD1                                      <...>          <..........>            TOTL                                   TOTAL          9,999,999.99         Figure 2 - This is the complete RPG program to print the report.FCUST      IF   E           K DISK                                     FCUSW005   O    E             PRINTER OFLIND(*In99)                    C                   Move      *On           *In99                      C                   Read      CUST                                   90C     *In90         DoWEq     *Off                                     C     *In99         IfEq      *ON                                      C                   Write     HDG1                                     C                   Write     HDG2                                     C                   Move      *Off          *In99                      C                   EndIf                                              C                   Add       CUSAP         TOTPUR                     C                   Write     DTL                                      C                   Read      CUST                                   90C                   EndDo                                              C                   Write     TOTL                                     C                   Eval      *INLR = *On                              C                   Return 


DYNSLT in Logical File


|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....800010A                                      DYNSLT00020A          R RECORD1                   PFILE(PF1)00030A            FLD100040A            FLD200050A          S FLD1                      COMP(GT 2)

EDTCODE in AS400


EDTCDE - EDITC - EDITW


Edit Code
Description
No
Sign
CR
Sign
Sign-
(R)
-Sign
(L)
Commas and zero balances1AJN
Commas2BKO
Zero balances3CLP
No commas or zero balances4DMQ
Date edit (4 digits)W   
No edit (suppress sign)X   
Date editY   
Suppress leading zerosZ   
     
User defined edit codes 5 - 9

User defined edit codes are created with the CRTEDTD or WRKEDTD commands. Any user-defined edit code will exist in library QSYS, with the name QEDIT5, QEDIT6. QEDIT7, QEDIT8 and QEDIT9.


Edit CodePositiveNegativeZeroes
112,345.6712,345.67.00
212,345.6712,345.67 
312345.6712345.67.00
412345.6712345.67 
A12,345.6712,345.67CR.00
B12,345.6712,345.67CR 
C12345.6712345.67CR.00
D12345.6712345.67CR 
J12,345.6712,345.67-.00
K12,345.6712,345.67- 
L12345.6712345.67-.00
M12345.6712345.67- 
N12,345.67-12,345.67.00
O12,345.67-12,345.67 
P12345.67-12345.67.00
Q12345.67-12345.67 


Edit codes are generally used in RPG Output specifcations,
EDTCDE & EDTWRD in DDS specifications,
%EDITC & %EDITW in Definition and Calculation specifications.

RPG Output Specifications.

The edit code is in position 44 on the same line as the field name to be edited.
An edit word can be entered in quotation marks for more specific editing.

DDS Specifications.

EDTCDE() or EDTWRD() on screen and printer dds's is entered in the section against the field name to specify how the field is displayed.  If the EDTCDE() orEDTWRD() is used on a PF dds, the keyword is used to format the field in QUERY reports.

Calculation specifications.

%EDITC in the calculations section formats a numeric field and stores it in a character field.   %TRIM can be used with this to left justify an edited field.



%EDITC has the following formats ...



%EDITC(number: editcode);12,345.67
%EDITC(number: editcode: currency);€12,345.67
%EDITC(number: editcode: *astfill);***12,345.67
%EDITC(number: editcode: *cursym);€12,345.67

where number = 0012345.67,
editcode = K,
currency = €,
*cursym = €.
*CURSYM has a default value of $.
This is defined in the system values at QCURSYM. To change the value of *CURSYM for the program, use CURSYM on the H (control) specification.
e.g. H cursym('€')



This is an example of using %EDITW ...



      %EDITW(znumber: zword);12,345Euros 67Cents

where znumber is defined as 7,2 and containes 0012345.67,
zword contains "    ,   Euros&  Cents".
(There are 4 spaces to the left of the comma, 3 spaces to the right of the comma and 2 spaces after Euros.
The & will force a space to be output, the spaces will be filled with digits from the number field.)