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
/*********************************************************************/
/* 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
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
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
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
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
/**************************************************************************/
/* 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
/**************************************************************************/
/* 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((
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
 |
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online