29
Fri, Nov
0 New Articles

TechTalk

General
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.



TechTalk07-00.png 670x368
BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More 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.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY 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.

  • SB HelpSystems ROBOT GenericForms 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.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA 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:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA 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.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra 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: