Preventing Job logs:

To prevent a job log from being produced at the completion of a batch job, you can specify *NOLIST for the message level text of the LOG parameter on the
Batch Job (BCHJOB),
Submit Job (SBMJOB),
Change Job (CHGJOB),
Create Job Description (CRTJOBD), or
Change Job Description (CHGJOBD) command.

If you specify *NOLIST for the message text value of the LOG parameter, the job log is not produced at the end of a job unless the job end code is 20 or greater. If the job end is 20 or greater, the job log is produced. For an interactive job, the value specified for the LOG parameter on the SIGNOFF command takes precedence over the LOG parameter value specified for the job.

DB2 – Buffer Pool:

A buffer pool is an area of storage in memory into which database pages (containing table rows or index entries) are temporarily read and changed. The purpose of the buffer pool is to improve database system performance. Data can be accessed much faster from memory than from a disk. Therefore, the fewer times the database manager needs to read from or write to a disk, the better the performance. The configuration of one or more buffer pools is the single most important tuning area, since it is here that most of the data manipulation takes place for applications connected to the database (excluding large objects and long field data).

By default, applications use the buffer pool called IBMDEFAULTBP, which is created when the database is created. The DB2 database configuration parameter BUFFPAGE controls the size of a buffer pool when the value of NPAGES is -1 for that buffer pool in the SYSCAT.BUFFERPOOLS catalog table. Otherwise the BUFFPAGE parameter is ignored, and the buffer pool is created with the number of pages specified by the NPAGES parameter.

Make Subfile Page to retain its page:

If you select a record from a subfile with multiple pages (e.g. selecting an item from a subfile to change), make your change and then return back to your subfile, the subfile would usually reload from record one to the end, making you page down to the record you had changed. To bring you back to the page you were on, add the following code to your display file and program.
Code
Using PDM, in the control section of your display file, add in the line
@pag 4 0H SFLRCDNBR
@pag is in the NAME column, the 4 is the length, the 0 is the decimal position and the SFLRCDNBR is in the functions column.
In your program, initialize @pag to 1.
When you select the line to change, z-add the rrn to @pag, and your program will return you to the subfile page you were on.

Easy way to Sort Subfile data:

Sorting data is something RPG programs often need to do. If it's just a simple single field array you're sorting in order to use the much faster binary search possible with %Lookup, for example, then SORTA works well and is simple. But what if it is a more complex task like sorting the data in a subfile on a user-selected column? Surely you need some more involved techniques, such as retrieving the data from the database again using a different ORDER BY on an SQL SELECT statement or using a different logical file or you could use the qsort C function for sorting the array elements in the program. Something as simple as SORTA can't be used for that, right?
Maybe so. The circumstances where this is effective are limited, for sure, but if your requirements fit, then using SORTA with a group field can be the simplest way and often a faster alternative than other methods you may have tried.
First of all, what's a group field? It's a field in a data structure that is broken down into smaller subfields. For example, group field SflData might be made up of information about products (name, price, quantity) by using the Overlay keyword, such as:
D SflDS Ds Inz
D SflData Like(SflRecData)
D Dim(999)
D Name Like(ProdDS)
D Overlay(SflData)
D Price Like(SellPr)
D Overlay(SflData:*Next)
D Qty Like(STOH)
D Overlay(SflData:*Next)
The effect is similar to nested data structures, except without the requirement to use qualified names. (Likewise, there are many limitations on group fields because of the lack of name qualification.) One additional thing that's nice about group fields compared to nested DSs is that we can use SORTA against any of the subfields in a group field array.
So this means if I wanted to sort the data in the SflData array by product name, I could do that with the following statement: SortA Name;. Much simpler than any of those other options I mentioned above! Of course, in nearly all cases, it would require the use of the built-in function %SubArr (substring array) because I'm not likely to have filled up all 999 elements of SflData. Even so, the entire bit of logic to accomplish sorting this subfile data in the sequence of any of the three fields could be as simple as:
If SortByName;
SortA %SubArr(Name:1:Count);
ElseIf SortByQty;
SortA %SubArr(Qty:1:Count);
ElseIf SortByPrice;
SortA %SubArr(Price:1:Count);
EndIf;
This technique is very simple and in most cases quite a fast way to sort subfile data (or any other kind of repeating data). It does have significant limitations. For example, you can only sort on one subfield at a time. (Of course, you could group two subfields together if they happen to be adjacent in the subfile record.) Also, you must be able to retrieve and store all the data destined for the subfile into an array so that you can sort it all together. For some very large subfiles, that won't be practical. But for those occasions where it works, it couldn't get much simpler.

Conditional Insert in SQL:

Sometimes we would like to insert records into database on a conditional basis.

Example:

if not exists (select 1 from table1 where key1 = ?) then
insert into table1
(key1, key2, key3, key4)
values (?, ?, ?, 1);
end if

As DB2 does not support dynamic scripting, this can be achieved by the following query.

insert into table1
(key1, key2, key3, key4)
Select ?,?,?,1
From SysDummy1
Where Not Exists
(Select 1
From table1
Where key1=?)

The question marks represent parameter markers (roughly equivalent to host variables in pre-compiled SQL.) Values need to be assigned to each of these markers before the statement can execute successfully.
Take note that SysDummy1 is a special IBM one row table that can be used as a trick for these one row operation situations! This is because inserting parameter values from a one row table is equivalent to the INSERT/VALUES statement. Placing the NOT EXISTS predicate in the WHERE clause instead of using an IF statement still allows us to condition if the row should be inserted by testing whether the row already exists.
As a side note, SysDummy1 resides in the SysIBM schema so it should be part of the library list when using the *SYS naming convention or fully qualified (SYSIBM.SysDummy1) when using the *SQL naming convention. Alternatively, if you have a one row table in your own schema it can be substituted for SYSDUMMY1 as well.

Digital Signage:

Definition: A variety of electronic display devices connected by a network, enabling a retailer to control their promotional messages quickly and effectively.

Also Known As: Captive Audience Networks, Narrowcasting, Electronic Billboards, Electronic Display

Examples: Instead of cluttering our checkout counter with fliers or brochures of printed information on our value-added services, our retail store has a digital signage system with several displays of our staff actually performing these services for customers. These digita signs are strategically placed around the store and run certain services at particular times of the day.

Work IP Device-TAA (WRKIPDEV):

If multiple printers are attached with as400 system, and in order to know the names of the OUTQ attached to IP address without looking at each one.

Enter the following command (WRKIPDEV) from Command prompt if TAATOOL available on the system.

Command:

TAATOOL/WRKIPDEV DEVTYPE(*PRT) OUTQ(*ALL/*ALL) OUTPUT(*PRINT)

Once the command is been executed check in spool file. This will give you a list of IP printers, by IP address, on your system with Dev/OutQ name.



If you want to view in display mode and not in spool file execute the below command without providing OUTPUT (*PRINT) value.

TAATOOL/WRKIPDEV DEVTYPE(*PRT) OUTQ(*ALL/*ALL)

Run CL Commands from Windows:

This summary is not available. Please click here to view the post.

Create physical files on the fly:

CL programmers sometimes need to create physical files (PFs) on the fly. To create a PF that has an external definition, you have to use either DDS or DDL, and for CL programmers, that means having a separate source member that contains the file definition. Or does it have to mean that?
This article demonstrates how you can take advantage of QShell from a CL program to create a PF in which you can embed the source code, including the field definitions, inside the code of the CL program itself.
The trick to producing a PF on the fly from CL is QShell's db2 utility. This utility runs an SQL statement that's passed as a parameter. Because QShell commands can be run from CL's STRQSH command, embedding an SQL statement in a CL program is relatively easy. For example:
PGM
STRQSH CMD('db2 "create table SOMELIB.SOMEFILE ( +
field1 decimal(5,0), +
field2 char(30), +
field3 date +
)"')
ENDPGM

Verify the JDE Version:

How to Verify your JD Edwards World Release Level
From any menu, type 97 on the command line.
Under the column labeled Release Level, find the highest letter/number combination. For example A8.1.

SQL Case Expression:

An SQL case expression offers a simple way to add conditional evaluation to an SQL statement. It can often simplify what would otherwise be a difficult or even impossible task.
Use:
Multiple updates based on multiple conditions, in one pass could be done by SQL case expressions:
E.g.:
The following two SQL statements can be combined into one. The single statement may run faster, especially against a large file, since it makes only one pass.
Update filename set field1 = 'Y1' where field1 = 'X1'
Update filename set field2 = 'Y2' where field2 = 'X2'
Single statement:
Update filename set
field1 = CASE
When field1 = 'X1' then 'Y1'
Else field1
END,
field2 = CASE
When field2 = 'X2' then 'Y2'
Else field2
END
Where field1 = 'X1' or field2 = 'X2'

Replace the Value of the field in a particular position in SQL:

A particular position or a particular value in a string can be replaced with a new value in SQL. REPLACE function does the job.
Syntax:

REPLACE (string_expression1, string_expression2, string_expression3)
string_expression1
Is the string expression to be searched. string_expression1 can be of a character or binary data type.
string_expression2
Is the substring to be found. string_expression2 can be of a character or binary data type.
string_expression3
Is the replacement string. string_expression3 can be of a character or binary data type.
Example:
UPDATE kgipkemp set kmfld1 = REPLACE(kmfld1,substr(kmfld1,21,6), '080901' )

Display Status Message in Reverse Image:

To display status messages in reverse image, define two single-character fields to hold the hex code for display attributes: one to contain the reverse-image attribute byte and one to contain the normal attribute byte. Then, concatenate these fields with your message field, as the following partial program illustrates.
DCL VAR(&REVERSE) TYPE(*CHAR) LEN(1) VALUE(X'21')
DCL VAR(&NORMAL) TYPE(*CHAR) LEN(1) VALUE(X'20')
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) +
MSGDTA(&REVERSE *CAT &MSG *CAT &NORMAL) +
TOPGMQ(*EXT) MSGTYPE(*STATUS)

Find Data in a Multi-Member file:

Ever have to look through a large multimember file for a particular record? It can be a time consuming task.
Key in FNDSTRPDM + F4. Options are self-explanatory.
Option member allow user to key in "*ALL" and will search through all members for the strings keyed.
Allows user to display, print, and edit records when string is found.

Use SQL to remove extra spaces:

The REPLACE function can be used to remove extra spaces within a character string. IBM added the REPLACE function to SQL in V5R3.

Run the following query to see what would happen.
select name,
replace(replace(replace(name,' ','<>'),'><',''),'<>',' ')
from qtemp/mydata

This is the output:

NAME REPLACE
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith

So how does it work? The innermost REPLACE changes all blanks to a less-than greater-than pair. So, if there are three spaces between Joe and Smith, the innermost REPLACE returns Joe<><><>Smith.

The middle REPLACE changes all greater-than less-than pairs to the empty string, which removes them. Joe<><><>Smith becomes Joe<>Smith.

The outer REPLACE changes all less-than greater-than pairs to a single blank. Joe<>Smith becomes Joe Smith.

You do not have to use the less-than and greater-than symbols. Any two characters that are not used in the field will work.

Unexplored User profile User Options:

When you create or change a user profile, there are many optional parameters for which you can specify a value. Since we normally just clone an existing user profile to make a new one, most of us never get around to examining all of those optional parameters.
When using the CRTUSRPRF, CHGUSRPRF, or CHGPRF command, one of the last parameters is User Options(USROPT).
The default value for USROPT is *NONE, so when we do not specify an alternate value, we get no special options for that user.
The alternate values we can specify include *CLKWD, *EXPERT, *ROLLKEY, *NOSTSMSG, *STSMSG, *HLPFULL, and *PRTMSG.
Here's an example of using the USROPT parameter.
CRTUSRPRF USRPRF(MYUSER)… USROPT(*HLPFULL *PRTMSG)
*CLKWD – Show CL keywords
F11 is a toggle switch to see choices or CL Keywords. *CLKWD simply changes the display you see first; choices or keywords.
When Prompting a Control Language command with F4=Prompt, we first see a textual description of the choices you can enter for the parameter on the right side of the parameter entry area. If we press F11=Keywords, we will see the CL keyword names to the left of the parameter entry area.
If we assign the user option *CLKWD to a user profile, these displays are reversed. In other words, you will first be prompted with the CL Keywords, and will see the choices after pressing F11=Choices.
*EXPERT – Use expert mode on certain displays
*EXPERT mode removes the instructions on some operational screens. It is similar to setting the *ADVANCED assistance level on screens that support that level.
*ROLLKEY – Change page up/page down
If you set the *ROLLKEY option for a user, the functions of page up and page down are reversed.
*NOSTSMSG or *STSMSG – *Status message display
I guess some users get real confused when they see those status messages blinking at them from line 24 of the display. It happens a lot during Query operations, and will also show up in custom code where the programmer is sending *status messages to keep the user informed.
But as the programmer, you may not want the user to know what the code is doing, so sometimes you want to hide your *status messages.
If *STSMSG is selected, the user will see status messages. If *NOSTSMSG is selected, the user will not see *status messages. If neither of these values is selected, the user will see *status messages.
Caveat: There are other avenues to control the display of *status messages: the system value QSTSMSG and the job attribute STSMSG.
*HLPFULL – Full screen help text
The i/OS has great HELP facilities. Cursor-sensitive HELP text is available on all IBM displays, and IBM and 3rd party vendors have provided great tools to easily build HELP text for your own application screens.
When you use the F1=HELP key, you typically get a limited help text window, which you can then scroll through to view the entire help text.
The *HLPFULL user option changes that windowed help text to cover the entire screen instead of a fraction of the screen. So *HLPFULL means full screen help text. I like this user option.
*PRTMSG – Message on printing action
By turning on *PRTMSG for a user, they receive an interrupting message telling them when their report is done printing.

Executing Commands with system() function in RPG:

It's common to use the QCMDEXC when you want to execute a CL command from an RPG program. But you may find it more convenient to use a C runtime library function, system(), to accomplish the same purpose. The system() function will pass a command string to the command processor, without the need to pass the length of the command string, or any other parameters for that matter.

To call the system() function, you simply pass it a pointer to the command string. Here's the suggested prototype (along with some necessary H-specs):

/If Defined(*Crtbndrpg)
H Dftactgrp(*No)
/Endif
H Bnddir('QC2LE')

// ------------------------------------------------------- Prototypes
D GoCmd PR 10I 0 Extproc('system')
D CmdString * Value
D Options(*String)

The command string may be a variable, literal, named constant, or an expression. The following example shows a typical use:

/If Defined(*Crtbndrpg)
H Dftactgrp(*No)
/Endif
H Bnddir('QC2LE')

// ------------------------------------------------------- Prototypes
D GoCmd PR 10I 0 Extproc('system')
D CmdString * Value
D Options(*String)

D NullString
C -1
D Success C 0

D Returncode S 10I 0
D User S 10 Inz(*User) Varying

/Free

Returncode = Gocmd('WRKSPLF SELECT(' + User + ') OUTPUT(*PRINT)');

Select;
When Returncode = Success; // Command was successful
...
When Returncode = NullString; // Command string was null
...
Other;
// Command failed
...
Endsl;

/End-free

The return code will let you check for the success or failure of the system() function. The return code is zero if the command is successful, or 1 if the command fails. If you pass a null pointer to a string, system() returns -1, and the command processor is not called.

If the system() function fails (i.e., return code is 1), it sets a global variable _EXCP_MSGID with the CPF message ID. You can import this variable into your program to check for specific errors, as the
following example shows:
/If Defined(*Crtbndrpg)
H Dftactgrp(*No)
/Endif
H Bnddir('QC2LE')

// ------------------------------------------------------- Prototypes
D GoCmd PR 10I 0 Extproc('system')
D CmdString * Value
D Options(*String)

D NullString C -1
D Success C
0

D ObjectNotFound C 'CPF3142'
D ObjectInUse C 'CPF3156'

D Errmsgid S 7 Import('_EXCP_MSGID')
D Returncode S 10I 0

/Free

Returncode = Gocmd('DLTF MYLIB/MYFILE');

Select;
When Returncode = Success; // Command was successful
...
When Returncode = NullString; // Command string was null
...

When Errmsgid = ObjectNotFound; // CPF3142
...
When ErrMsgid = ObjectInUse; // CPF3156
...
Other; // Some other error
...
Endsl;

/End-free
To use the system() function, you must refer to binding directory QC2LE when compiling and/or binding the program. The above examples name QC2LE in the H-specs.

Handle errors in RPG like CL:

We can catch any type of exception in the RPG program by using MONITOR opcode, it is like the MONMSG of CLP. You can put any code between MONITOR and ENDMON opcodes, so that whatever error occurred in this range will be monitored. See the example below:
Example:
Declare an array like Arr with DIM(2) Declare 3 variables A, B and C with length 2,0.
The below code shows, how to monitor the runtime errors. The initial value of A and C is 0, initiate value of B is 11.

MONITOR
B DIV A
Arr(B) Dsply
ON-ERROR 0102
'Div by 0' Dsply
ON-ERROR 0121
'Index Err' Dsply
ON-ERROR
'Error' Dsply
ENDMON

Similar way specific errors can be captured and handled accordingly in RPG.

Quick method to find the name of the Calling Program:

Infinitely, IBM has kindly provided us with QWVRCSTK at V5.

D GetCaller PR Extpgm('QWVRCSTK')
D 2000
D 10I 0
D 8 CONST
D 56
D 8 CONST
D 15

D Var DS 2000
D BytAvl 10I 0
D BytRtn 10I 0
D Entries 10I 0
D Offset 10I 0
D EntryCount 10I 0
D VarLen S 10I 0 Inz(%size(Var))
D ApiErr S 15

D JobIdInf DS
D JIDQName 26 Inz('*')
D JIDIntID 16
D JIDRes3 2 Inz(*loval)
D JIDThreadInd 10I 0 Inz(1)
D JIDThread 8 Inz(*loval)

D Entry DS 256
D EntryLen 10I 0
D PgmNam 10 Overlay(Entry:25)
D PgmLib 10 Overlay(Entry:35)
D
C CallP GetCaller(Var:VarLen:'CSTK0100':JobIdInf
C :'JIDF0100':ApiErr)
C Do EntryCount
C Eval Entry = %subst(Var:Offset + 1)
C Eval Offset = Offset + EntryLen
C Enddo
C Eval *InLR = *on

Tips for faster Query Access:

Faster is better when accessing large volumes of data. There are many ways to improve SQL performance, but here are four tips that are especially useful for high volume, read-only database access.

• Code a Set Option AlwCpyDta = *Optimize SQL statement (or the AlwCpyDta(*Optimize) parameter on the appropriate CL command). This lets the optimizer choose whether to create a new index or use a sort for a temporary copy of the data.

• Note that AlwCpyDta=*Yes actually means "use a copy only when it's required to perform the query." This allows the optimizer less latitude than the *Optimize option provides.

• Code a Set Option AlwBlk = *AllRead SQL statement (or the AlwBlk(*AllRead) parameter on the appropriate CL command). This maximizes system blocking when possible.

• Use a CL OvrDbF (Override with Database File) command with the SeqOnly(*Yes, mm) and/or the NbrRcds(nn) parameter(s) to specify system blocking for batch sequential Fetch's.

Use multi-row fetches to read a set of records with each Fetch statement.