Valid Client Access/400 License Required

Want to find out who’s currently using one of your Client Access/400 licenses? Type in the Work with License Information (WRKLICINF) command on an AS/400 command line and press the Enter key. Page Down until you see the entry for Client Access/400 Windows Family Base (or its equivalent). Enter option 8 next to this entry, and you are presented with a list of the job information for all current users of the product. This tip works for any AS/400 licensed program.

— Shannon O’Donnell Senior Technical Editor

Midrange Computing

Change (Any Field) Is Good!

Has your accounting manager ever changed the general ledger codes for a department without telling MIS until the general ledger was out of balance? Has your interface with third-party software ever stopped working correctly because of data changes in the vendor’s application upgrade? Worse yet (and I’m sure no one in the AS/400 world has ever done this), have you ever released a bug into production that corrupted data? What do you do, correct the data or modify your programs? Well, it depends.

For those times when you choose to correct the data, you could go about it the old- fashioned way: writing an RPG program with an F-spec for the particular file involved and adding logic to change the field to its intended value. But if you need to change multiple files and fields, you must either clone the program or add F-specs and more logic. Then, all you would need to do is compile and run the programs and hope you don’t forget anything!

If you have SQL on your AS/400, there is a better way to solve the problem. In these situations, I use a utility command I wrote (which is shown in Figure 1) called Change Any Field (CHGANYFLD). CHGANYFLD updates any field in any file in any library at any time. The only two catches are that the file you want to update must first be journaled and that SQL/400 must be installed on your AS/400. You supply the library and file names in the first two parameters of the command interface; the power and dynamics of the utility lie in the use of the third parameter. In the third parameter, you can simply enter a field name and its new value to make unconditional and global changes, or you can enter SQL clauses, such as WHERE, and sub SELECTS to condition and scope the update of the field in question. Additionally, you can run the command immediately, in batch mode, or schedule it for a later date and time.

Here’s how the utility works. The command interface, with its context-sensitive help, accepts parameters to identify exactly which file in the DB2/400 database needs to be changed along with which conditions affect the update. The ILE program consists of an ILE CL module (Figure 2) and an SQLRPGLE module (Figure 3). I’ve also supplied a Help panel (Figure 4), which you can attach to the CHGANYFLD command. The command is self-submitting in that it interrogates the job attributes. If the job is running as an interactive job, the CL prompts the Submit Job (SBMJOB) command to submit the program and then ends. When the CL determines that the job is running in batch, the SQLRPGLE module is called. This module is very short but very powerful. Using dynamic SQL, the module builds the SQL Update statement and appends the New value parameter you supply through the command interface. It then executes the SQL Update statement. When the command is finished, it creates a log file and writes the results of the command to it. If the log file already exists, it overlays the log file.

Now, bear in mind that this is not an all-purpose, generic SQL statement generator. You cannot delete or add records to a file; you cannot create a new file (except for the audit log file); and you’re limited to just 226 bytes for your SQL clause. However, it should be a simple matter to modify this little utility to handle all of the aforementioned conditions, should you decide that it would be useful.

— Claude Osgood President, Alpha-Omega/400 This email address is being protected from spambots. You need JavaScript enabled to view it.

/*********************************************************************/

/* CRTCMD CMD(xxx/CHGANYFLD) PGM(xxx/CO0001CP) + */
/* HLPPNLGRP(xxx/CO0001H) */
/* */

/*********************************************************************/

Cmd PROMPT('Chg ANY Field in ANY Lib/File')

Parm Kwd( Lib ) +

Type( *Char ) +

Len( 10 ) +

Prompt( 'Library' )

Parm Kwd( File ) +

Type( *Char ) +

Len( 10 ) +

Prompt( 'File' )

Parm Kwd( NewValue ) +

Type( *Char ) +

Len( 236 ) +

Prompt( 'field = new value' ) /*********************************************************************/

/* */

/* CRTCLMOD MODULE(XXX/CO0001C) SRCFILE(XXX/QCLSRC) + */
/* MBR(CO0001C) */
/* */

/* CRTPGM PGM(XXX/CO00001CP) MODULE(CO0001C CO0001R) + */
/* ACTGRP(*CALLER) */
/*********************************************************************/

Pgm ( &Lib &File &NewValue )

Dcl &Lib *Char 10

Dcl &File *Char 10

Dcl &NewValue *Char 226

Dcl &Type *Char 1

/* Submit the job */

RtvJobA Type( &Type )

If ( &Type = '1' ) +

Then( Do )

?SbmJob Cmd( CHGANYFLD Lib( &Lib ) +

File( &File ) +

NewValue( &NewValue )) +

Job( ChgAnyFld )

MonMsg CPF6801

GoTo End

Figure 1: Use CHGANYFLD to dynamically change the contents of your file data.

EndDo

Callprc CO0001R ( &Lib &File &NewValue )

End: Return

EndPgm

*********************************************************************

* Change CrtPgm defaults to ...BndDir(ChgAnyFld)

* ...ActGrp(*Caller)

* CRTSQLRPGI OBJ(XXX/CO0001R) SRCMBR(XXX/QRPGLESRC) +

* MBR(CO0001R) COMMIT(*NONE)

*

*********************************************************************

D Lib S 10

D File S 10

D NewValue S 236

D SQLstring S 256

D SQLcode S 4 0

C *Entry Plist

C Parm Lib

C Parm File

C Parm NewValue

* Construct the SQL statement dynamically from the parms

* "UPDATE &lib/&file SET &newvalue"

C Eval SQLstring =

C 'Update ' + %Trim( Lib ) + '/' +

C %Trim( File ) + ' ' +

C 'Set ' + %Trim( NewValue )

* Process the SQL statement at execution time

C/Exec SQL Execute Immediate :SQLstring

C/End-Exec

C Eval SQLcode = SQLcod

* Create a log file in yourlib

C/Exec SQL Create Table QGPL/ChgFldStat

C+ (Lib Char( 10 ) Not Null,

C+ File Char( 10 ) Not Null,

C+ SQLcode Dec( 4 ) Not Null,

C+ NewValue Char( 236 ) Not Null)

C/End-Exec

* Add a record to log the update

C/Exec SQL Insert Into QGPL/ChgFldStat

C+ Values( :lib, :File, :SQLcode, :NewValue )

C/End-Exec

* end

C Eval *InLR = *On

C Return :PnlGrp.

:Help Name=ChgAnyFldH.
Change ANY field in ANY file in ANY lib Any time!
:p.
This command allows you to change ANY field in ANY file in ANY library
at ANY time using dynamically created and bound SQL statements.
:p.
:p.
The &newvalue parameter fits into the following SQL statement:
:p.

UPDATE &lib/&file SET &field = &newvalue
:p.
:p.
Use with extreme caution! SQL is absolutely non-forgiving!
:EHelp.

:Help Name='ChgAnyFldH/Lib'.
Library
:xh1.Library
:p.
Specify the DB2/400 library in which the file intended for update is.
:EHelp.

:Help Name='ChgAnyFldH/File'.
File
:xh1.File

Figure 2: This is the command processing program (CPP) for CHGANYFLD.

Figure 3: This SQL RPG IV module updates file fields based on the values entered into CHGANYFLD.

:p.
Specify the DB2/400 file in which the field intended for update is.
:EHelp.

:Help Name='ChgAnyFldH/NewValue'.
New Value
:xh1.New Value
:p.
Enter the field and its new value.
:p.
:p.
- &newvalue may consist of a field followed by an equal sign followed
by a single value or an expression to unconditionally and globally
change.
:p.
I.e. "afield = 19" would change all occurrences of a new century
field to '19'
:p.
"afield = (19000000 + afield)" would add the century to a field
containing a date in the *YMD format.
:p.
- &newvalue may consist of the above followed by an SQL expression
:p.
I.e. "afield = 'N' WHERE afield 'Y' AND afield ' '"
:p.
:p.
Remember, &newvalue is programmatically embedded into the SQL as:
:p.

"UPDATE &lib/&file SET &newvalue"
:p.
Punctuation in this text is for example and readability. The actual
contents of &newvalue, as keyed by you, must comply to SQL rules.
Note: "afield" would be a literal field name.
:EHelp.

:EPnlGrp.

Figure 4: Attach this Help panel to CHGANYFLD so you can add helpful text on how to use the command.

You’ll Feel Warmer if You Throw Another Log onto the Firewall

Recently, I enabled detailed logging on my company’s firewall because I was very interested to see what traffic it denies. Specifically, I noticed that a large number of requests sent to port 137 were being denied. (For those of you unfamiliar with TCP/IP, a port designates an application. The most common port is port 25, which operates incoming email.)

The port in question, port 137, is assigned to the NetBIOS Name Service as defined in RFC 1700, which lists definitions of Internet protocols and policies. (For more information on these protocols and policies, point your browser to www.cis.ohiostate.edu/hypertext/information/rfc.html.) Several applications are available on the Internet that let you exploit a common bug in PC-based systems that run NetBIOS. (Windows 95 is a common target.) Applications such as WinNuke send User Datagram Packet (UDP) requests to port 137. Without a patch to TCP/IP, Windows 95 is brought to a “Blue Screen of Death,” and TCP/IP remains unusable until someone reboots the system.

I surmised that, in an attempt to be funny (although it was not funny to me), someone tried to bring down systems on my network with the NetBIOS Name Service exploit, a tactic often called the OOB (out of bounds) data attack. Luckily for me, however, the firewall rejected the attempt and simply sent me an email.

There are actually two morals to this story. The first is that you should watch out for port 137 attacks. Without the recent patches Microsoft has developed, your Windows- based system could be rendered a vegetable. I have tested it before, and it works quite well. The second moral is that, if I had not had logging available on the firewall, I would have had no indication that this was occurring. Even scarier is that I also would not have known what else was going on. If someone were trying to hack into your network, would you know?

— Chris Green This email address is being protected from spambots. You need JavaScript enabled to view it.

Journaling What You Want When You Want It

Q: I want to be able to journal files to find out which program deleted the records in my shop’s order note file. I’ve created a journal receiver and journal, but can I restrict the system so it audits only deleted transactions and ignores transactions such as update and read?

— Jason Cheang This email address is being protected from spambots. You need JavaScript enabled to view it.

A: A trigger program added to the file for delete operations can log to a journal by using the send journal entry function. You can format the order number or any other data you desire, making a query of the Display Journal (DSPJRN) output much simpler to do.

— Chuck Pence This email address is being protected from spambots. You need JavaScript enabled to view it.

Breaker, Breaker!

Q: I have a CL that was submitted to run in batch, and I would like to send a message to the user who submitted it if certain conditions are met. I tried to send a message by using the Send Break Message (SNDBRKMSG) command but received an error message saying that the message queue has to be a workstation message queue. How do I accomplish this?

— J. Perez
This email address is being protected from spambots. You need JavaScript enabled to view it.

A: You can get a break message only at a workstation message queue. However, there is a system API that lets you send a break message to a user profile: Send Message API (QEZSNDMG). If the user is signed on, the message is sent to the user’s workstation message queue. If the user is not signed on, the message is sent to the user’s message queue. You can find this API in the OS/400 Operational Assistant APIs V4R4 manual (SC41-5867-03, CD-ROM QB3AMS03), and Figure 5 shows a little CL program I wrote to demonstrate how it works. To get full use out of this technique, you probably want to create a command interface for it, passing it the value for the USERS variable. In any event, before you try out this program, make sure you change the value in variable USERS to a valid User ID on your system.

— Richard Casey This email address is being protected from spambots. You need JavaScript enabled to view it.

/**************************************************************************/

/* To Create: */
/* Crtclpgm Pgm(xxx/RC0001C) Srcfil(xxx/Qclsrc) */
/* Srcmbr(RC0001C) */
/* */

/**************************************************************************/

/* NOTE: Change the value in variable &USERS to the User ID you want to */
/* send messages to. */
/**************************************************************************/

PGM

DCL VAR(&MSGTYPE) TYPE(*CHAR) LEN(10) +

VALUE('*INFO')

DCL VAR(&DELMODE) TYPE(*CHAR) LEN(10) +

VALUE('*BREAK')

DCL VAR(&MSGTEXT) TYPE(*CHAR) LEN(80) +

VALUE('TEST MESSAGE')

DCL VAR(&MSGLENG) TYPE(*CHAR) LEN(04) +

VALUE(X'00000050')

DCL VAR(&USERS) TYPE(*CHAR) LEN(10) +

VALUE('USERID ')

DCL VAR(&USRCNT) TYPE(*CHAR) LEN(04) +

VALUE(X'00000001')

DCL VAR(&MSGSENT) TYPE(*CHAR) LEN(04) +

VALUE(X'00000000')

DCL VAR(&FUNCREQ) TYPE(*CHAR) LEN(04) +

VALUE(X'00000000')

DCL VAR(&ERROR) TYPE(*CHAR) LEN(256) +

VALUE(X'00000100')

DCL VAR(&SHOWMSG) TYPE(*CHAR) LEN(01) VALUE('N')

DCL VAR(&REPLYMQ) TYPE(*CHAR) LEN(20)

DCL VAR(&NAMETYPE) TYPE(*CHAR) LEN(04) +

VALUE('*USR')

CALL PGM(QEZSNDMG) PARM(&MSGTYPE &DELMODE +

&MSGTEXT &MSGLENG &USERS &USRCNT &MSGSENT +

&FUNCREQ &ERROR &SHOWMSG &REPLYMQ &NAMETYPE)

ENDPGM

Figure 5: This technique sends break messages to non-program message queues.

We’ve Got Your Commas Right Here!

Need to create a comma-delimited text file on your PC but don’t want to spend a lot of time doing it? Check out the code in Figure 6. This is a very quick way to create your comma- delimited text file and send it to the PC.

— David Morris This email address is being protected from spambots. You need JavaScript enabled to view it.

/**************************************************************************/

/* To Create: */
/* Crtclpgm Pgm(xxx/DM01) Srcfil(xxx/Qclsrc) MBR(DM01) */
/**************************************************************************/

PGM

CPYTOIMPF FROMFILE(DAVID/CFM10P) +

TOSTMF('QDLS/DAVID/CFM10P.TXT') +

RCDDLM(*CRLF) STRDLM('"')

CHGDOCD DOC(CFM10P.TXT) FLR('DAVID') +

DOCD('Comma Delimited Text Fie')

SNDDST TYPE(*DOC) TOINTNET((This email address is being protected from spambots. You need JavaScript enabled to view it.)) +

DSTD('Recipient Distribution Directory') +

MSG('Message Text') DOC(CFM10P.TXT) +

FLR(DAVID)

ENDPGM

Figure 6: Here’s a quick way to create a comma-delimited text file on your PC.

Know WHO You’re Working For

My job as a consultant sometimes finds me logged onto more than one AS/400 at a time for more than one client. It’s easy to get confused in this kind of environment and forget which session is which. To keep things straight, I use the Display Workstation User (DSPWSUSR) command. This command displays such relevant information as the displaystation ID, the number of interactive jobs in this session, and the current interactive job. The command even works for group jobs! Figure 7 shows an example of the output from this command.

DSPWSUSR is one of those commands that is easy to misspell, so, to make things easier for those times when I’m in a hurry, I used the Create Duplicate Object (CRTDUPOBJ) command to create a copy of the DSPWSUSR command and named the new command WHO. Now, when I get confused as to which session is which, I simply type WHO on any command line and press Enter, and I instantly know where I am.

— Bob Gillespie This email address is being protected from spambots. You need JavaScript enabled to view it.

<>
<>
<>

Figure 7: DSPWSUSR tells you which system you are logged onto.

Also Read

The Power of Writing Code in a Low-Code Solution

TechTip: Let’s Have a Date, Part 2

Going off the Rails