Julian Ceases

Doing Y2K work, or Y2K-ing, as I like to call it, led me on an adventure with a V3R2 system that extensively used a 5-byte numeric Julian date field. The field was found

in over 40 physical files and was used as one of the key fields in most cases. Logical files also used the numeric Julian date field as part of the index.

The most suitable solution was to convert the field to a Julian date data format. This was done, and the application continued running without a hitch—almost.

I had replaced instances where zeros were being moved to the field with *LOVAL. Moving *LOVAL to a Julian date field does not produce a compilation error. The use of a Julian date field containing a value of *LOVAL will produce an error!

I gleaned the following valuable lessons from this experience:
1. Do not use SETLL on a file when one of the KFLD fields is a Julian date field with a value of *LOVAL.

2. Keep Julian date data types away from PC applications. Neither Excel, Access, MS-Query, 1-2-3, nor Approach support the YYDDD Julian date format. Even if you are successful in retaining the Julian date format upon downloading, none of these applications will know what to do with it. Editors Note: While PC applications such as Excel and Lotus 1-2-3 do not directly support Julian dates, it is possible to create functions within the spreadsheet to handle the date conversion for you.

3. You can only configure Client Access/400 ODBC or Client Access/400 File Transfer to use one date data format—only one.

If you have *ISO and *JUL dates within the same file, you will have to pick one or the other. After transfer, either your *ISO dates will be in *JUL format, or your *JUL dates will be in *ISO format. *ISO is the default for ODBC, and *MDY is the default for file transfer. (This may surprise users who were doing downloads during the time when dates were numeric fields.)

4. You can’t use the CHAR token during file transfer to retain Julian format. While SQL/400 may support CHAR (field name JUL), Client Access/400 file transfer SQL does not!

It never would have occurred to me not to use the Julian format, particularly after having inherited a system that used the numeric Julian date so extensively. Experience is teaching me, though, that there are good reasons to avoid this format. Perhaps someday the Client Access ODBC driver and file transfer engines will be altered to better accommodate Julian dates.

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

Pass-through and DDM Files on a Standalone AS/400

A recent project I worked on was to move all users, programs, and data from one AS/400 to another. The client had recently installed a new model RISC 640. She now wanted to migrate users from an old F20 that was badly overextended. Users on the two machines had communicated with passthrough and Distributed Data Management (DDM) files, and now they were to share the same machine.

It was, on the surface, a simple matter of changing programs and recompiling to remove the Start Pass Through (STRPASTHR) commands and the references to DDM files. However, the deadline was very tight, and I had no time to make all the necessary changes.

The solution I found may be useful to other people in the same situation. I used a loop-back controller so that a single AS/400 communicates with itself as if it were two separate machines!

Start with two AS/400s. The first AS/400 has a local location name AS400A. The second AS/400 has a local location name AS400B. After you have moved everything from AS400B to AS400A, take the following steps to create a loop-back controller:

1. Use the Create APPC Controller (CRT-CTLAPPC) command to create a controller of type *LOCAL. Note that no line description is needed for this type of controller:

CRTCTLAPPC CTLD(LOOPBACK) +

LINKTYPE(*LOCAL)

2. Create a pair of APPC devices that mirror each other’s local and remote location

names:

CRTDEVAPPC DEVD(AS400A) +

RMTLOCNAME(AS400B) +

LCLLOCNAME(AS400A) APPN(*NO)

CRTDEVAPPC DEVD(AS400B) +

RMTLOCNAME(AS400A) +

LCLLOCNAME(AS400B) APPN(*NO)

3. Vary on the devices and the controller and execute the following command to pass through to the same AS/400:

STRPASTHR RMTLOCNAME(AS400B) +

LCLLOCNAME(AS400A)

or (going in the other direction) execute this command:

STRPASTHR RMTLOCNAME(AS400A) +

LCLLOCNAME(AS400B)

DDM files will work in the same way, as long as the loop-back devices are created with the correct local and remote location names.

Once the loop-back controller is up and running, the pass-through programs and DDM files can be removed at a more leisurely pace.

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

Potential Snag with Time Fields

I’ve noticed a potential “gotcha” with using time fields and how 12:00 a.m. is represented. Depending on how a time field is used (stored, sorted, compared, displayed, etc.) various companies may choose to represent 12:00 a.m. as either ‘00.00.00’ or ‘24.00.00’. In fact, both of these values are valid for a time field. If you move *LOVAL to a time field, the result is ‘00.00.00’. If you move *HIVAL, the result is ‘24.00.00’.

Things work a little differently when computing durations involving a time field. If you add or subtract a duration to a time field that results in precisely 12:00 a.m., it is always represented as ‘00.00.00’.

Thus, for example, if you compute a duration and write the result to a file expecting 12:00 a.m. to be ‘24:00:00’, you would be in for a surprise.

— Mason Eckley Creative Software Services This email address is being protected from spambots. You need JavaScript enabled to view it.

Keeping Users from Using Specific Commands

Many AS/400 installations allow users to have access to various AS/400 commands through menu options. For example, you might have a menu that lets users work with printers using the Work with Writer (WRKWTR) command. This option may be OK for certain users, but you may have other users whom you do not want to have access to this command. How do you avoid this problem? There are several methods that will work.

If you are at security level 30 (WRKSYSVAL QSECURITY) or above, for example, you can get around this problem by editing the authority on the command so that a particular user, or even *PUBLIC, doesn’t have access to use it. This method can be a bit cumbersome, though, if you have only a limited number of users that you don’t want to use the command. Using this technique requires you to either change authority on just these users or, alternatively, exclude all users and grant authority to only certain ones. Either way, this quickly becomes confusing.

A different approach may better suit your needs. If you have a certain command that you do not want your users to access (say, the WRKWTR command), try this: Create a new command that has the same name as the original (Figure 1). Now, link this new command to a new command processing program (Figure 2). The new command can perform whatever function you want it to, such as sending a message to users telling them that this function is not available for their use. You can then, based on a user ID, selectively process the commands to which you do want users to have access in your CL program. This option should work at any security level.

— Tim Johnston Technical Editor Midrange Computing

Prototype Type Conversion

You can ensure that the parameters passed to a system API match what the API expects by defining a prototype for the API. Both C and RPG IV permit prototyping.

To create a prototype for an API, first determine the format of the API’s parameters from the manual. If the manual specifies that a parameter is for input only, then pass the parameter as a constant or by value. Doing so causes the system to convert the value passed to the format specified on the prototype. In RPG IV, specify the CONST or VALUE keyword on the prototype.

Refer to Figure 3 for an RPG IV prototype of the Test Argument (CEETSTA) API. Notice the CONST keyword on the second parameter, argument number. You may pass a constant or a variable of any numeric format for this parameter. The system will convert whatever numeric value you pass to a 4-byte integer.

See Figure 4 for an example of how to call a prototyped API. Notice that you must use the CALLP op code, not CALL.

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

Updating Data Through a Join, Part 2

In response to the last item in the August 1998 MC TechTalk (“Update Data Through a Join”), I’d like to offer another solution. Here’s how I update a field of one file from a corresponding field of another file. I use SQL400 to illustrate, but this technique works as well with other relational tools, such as Open Query File (OPNQRYF).

Using the same example as in the August tip, I’ll have the SQL statement select all records from the files named Customers and ZIP Codes where the CustZip field in Customers equals the ZpCode field in the ZIP Codes file. (Refer to the series of SQL and CL commands in Figure 5 as I explain this technique.)

1. Join the two files, sending the output to a temporary work file. In SQL400, you must change the session attributes by pressing the F13 key, selecting option 1 (Change Session Attributes), and changing the Select Output parameter to 3.

Notice that you must use a left outer join to avoid losing customer records with ZIP codes not found in the ZIP Codes file. After the SQL statement runs, the joined data is in file QTEMP/NEWFILE. Be aware that the relational tool may rename some of the fields in the secondary file.

2. Execute an SQL Update command to update the new file. Set the city in the field from the Customers file equal to the city in the field from the ZIP Codes file. If the field being updated doesn’t allow the null value, use the SQL value function (or its equivalent in other tools) to replace null values with blanks (or zeros in numeric fields). Both types of updates are shown in Figure 5.

3. Finally, copy the temporary file to the original Customers file, replacing records and using the format options *MAP and *DROP.

— William W. Smyth Action Analysts, Inc. This email address is being protected from spambots. You need JavaScript enabled to view it.

One Approach to the Y2K Challenge

My database, like most, was set up with six-digit dates. Most were in YYMMDD format, but some were in MMDDYY format. Others had three two-digit fields. Converting our files to use eight-digit dates was going to be a nightmare.

Since the database was the problem, we decided that the database should be the solution. We created a date master file into which we could load dates in various formats. The key field is a six-digit, relative date value, which was the number of days since January 1, 1940. Dates prior to 1940 have negative values. Other date formats include the

six-digit date formats in which our data was stored, as well as eight-digit formats and edited dates (e.g., 15-Mar-98 and March 15, 1998). We also included fields to tell what day of the week the date is, whether or not a date is a federal holiday, and whether or not the date is a regular working day. We created logical files over this file so that we would be able to access the file from the formats we were used to.

We modified programs to read and write the relative date values when doing I/O operations, converting them to and from other formats for data entry, display, and print purposes. We replaced the existing date field values in our database with their relative date values from the dates file.

What did this do for us?
• Database record layouts did not have to be changed, and our files did not have to be re-created. We wrote simple programs to convert the date fields to the relative date values.

• Only programs that interact with date fields had to be modified and recompiled. Sort techniques continued to work properly. We did have to add front-end logic to convert runtime date parameters to the relative date number.

• Date arithmetic got much easier. Determining a date in the future is a matter of chaining to the dates file on a display format, retrieving its relative date, adding the number of days in the future to that number, and chaining again to the dates file. Determining the number of days between two dates is a matter of subtraction of two relative date numbers. Determining the number of working days between dates is also easy. We read the first date randomly, then sequentially, counting working days until we read the ending date.

• Our programs no longer have to include date conversion routines because we’ve placed the edited dates in the file.

Of course, this method is not perfect. DFU and query programs do not display dates in usable formats. However, since we do not use such tools heavily, this has not been a problem.

— Jack Staggs John S. James Company This email address is being protected from spambots. You need JavaScript enabled to view it.

Editors note: This technique may not be suitable for your shop, and, as such, you should consider all possibilities before implementing it. Don’t forget that RPG IV and DDS both support the Date data type and that most of the functionality Jack has described here can easily be performed using this data type and the ADDDUR and SUBDUR operation codes in RPG IV.

Forcing a Password Change

We have recently upgraded one of our AS/400s to V4R2, and we are using Novell’s SAA gateways and Microsoft’s SNA Server to access the AS/400 with PCs.

Whenever users’ passwords expire on the AS/400, the users cannot sign on to the SAA or SNA gateways because they are no longer given the option to enter a new password. IBM changed this feature so that third-party packages can no longer control AS/400 passwords that use the Distributed Data Management (DDM) Transaction Program (which Novell and Microsoft use). Only the Optimized Server Transaction Program will give users the option of entering a new password. Now the problem we face is that users allow their AS/400 password to expire and are then unable to sign on to the gateways to access the AS/400.

To avoid this problem, we are informing users that they must change their AS/400 password before it expires. To help them remember to do this, we send them the message, “Your password will expire in n days” when they sign on to the AS/400.

Unfortunately, some users will still probably forget to change their password, and it will expire. In anticipation of this occurrence and to prevent the data center from being flooded with calls to reset passwords, I have written a CL program that forces the Change Password (CHGPWD) command to be executed before the password expires (see Figure
6).

The program is called from the user’s initial program when they sign on to the AS/400. In order to determine when a password needs to be changed, I need to retrieve the date when the user’s password was last changed, the current system date, and the password interval time system value (QPWDEXPITV). With this information, I can perform a series of calculations to determine how many days are left before the password will expire. If the number of days left before the password expires is less than or equal to 2 and greater than 0, I execute the CHGPWD command and force the users to change their password before they can finish signing on to the AS/400.

— Kevin Dollinger EMI Capitol Music Group, N.A. This email address is being protected from spambots. You need JavaScript enabled to view it.

Let the Query Optimizer Do the Work for You

Recently, we had a situation where a job began taking 10 times longer to run than it ever had before. Upon further analysis, it turned out that most of the extra time was spent in the IDX-filename (index build) step associated with an Open Query File (OPNQRYF) command. As far as we knew, the program containing the OPNQRYF command had not changed in months. So, what had happened?

We called IBM, and one of its database experts asked me to start Debug with the update production files (UPDPROD) set to *YES. After doing that, we ran the OPNQRYF interactively. When it was finished, we displayed the job log and found a message generated by the Query Optimizer. This message suggested a permanent index be built via a logical file (or even a keyed physical file). It also identified the field names and the order they should appear in the index! In this particular application, the field names that the Optimizer chose didn’t appear to make sense as they weren’t part of the sort fields in the query. Thinking it was worth a shot anyway, I created the new logical, using the fields defined by the Optimizer, and re-ran the query. Amazingly, the query runtime went from 20 minutes to five seconds!

— Van C. Baker Bloomington, Illinois This email address is being protected from spambots. You need JavaScript enabled to view it.

/*===================================================================*/

/* */

/* TO COMPILE: */
/* */

/* CRTCMD CMD(XXX/WRKWTR) PGM(XXX/ALTWRKWTR) SRCFILE(XXX/QCMDSRC)*/
/* */

/*===================================================================*/

WRKWTR: CMD PROMPT('WORK WITH WRITER')

PARM KWD(WTR) TYPE(*CHAR) LEN(10) RSTD(*YES) +

DFT(*PRT) VALUES(*PRT *ALL) +

PROMPT('OUTPUT QUEUE')

PARM KWD(OUTPUT) TYPE(*CHAR) LEN(6) RSTD(*YES) +

DFT(*) VALUES(* *PRINT)

PARM KWD(DSPFMT) TYPE(*CHAR) LEN(7) RSTD(*YES) +

DFT(*INLENV) VALUES(*INLENV *S36FMT *NORMAL)

PARM KWD(ASTLVL) TYPE(*CHAR) LEN(9) RSTD(*YES) +

DFT(*PRV) VALUES(*PRV *USRPRF *BASIC +

*INTERMED)

Figure 1: Alternate Work with Writer command

/*===================================================================*/

/* */

/* TO COMPILE: */
/* */

/* CRTCLPGM PGM(XXX/ALTWRKWTR) SRCFILE(XXX/QCLSRC) MBR(ALTWRKWTR)*/
/* */

/*===================================================================*/

PGM PARM(&WTR &OUTPUT &DSPFMT &ASTLVL)

DCL VAR(&WTR) TYPE(*CHAR) LEN(10)

DCL VAR(&OUTPUT) TYPE(*CHAR) LEN(6)

DCL VAR(&DSPFMT) TYPE(*CHAR) LEN(7)

DCL VAR(&ASTLVL) TYPE(*CHAR) LEN(9)

DCL VAR(&USR) TYPE(*CHAR) LEN(10)

RTVJOBA USER(&USR)

IF COND(&USR *EQ 'TIM') THEN(DO)

QSYS/WRKWTR WTR(&WTR) OUTPUT(&OUTPUT) DSPFMT(&DSPFMT) +

ASTLVL(&ASTLVL)

ENDDO

IF COND(&USR *NE 'TIM') THEN(DO)

SNDPGMMSG MSG('SORRY. YOU ARE NOT AUTHORIZED TO RUN +

THIS COMMAND')

ENDDO
ENDPGM

Figure 2: Alternate Work with Writer command source

DCEETSTA PR EXTPROC('CEETSTA')
D PR_ArgPas 10I 0
D PR_ArgNum 10I 0 CONST
D PR_FBCod 12A OPTIONS(*OMIT)

Figure 3: Using an RPG IV prototype ensures that parameters are properly passed to an API

C callp CEETSTA (Arg: Parms: RtnCd)

Figure 4: Calling a prototyped API

select * from customers left outer join zipcodes

on customers.custzip = zipcodes.zpcode

-- if custcity allows the null value
update qtemp/newfile

set custcity = zpcity

-- if custcity does not allow the null value
update qtemp/newfile

set custcity = value(zpcity, ' ')

CPYF FROMFILE(QTEMP/NEWFILE) TOFILE(CUSTOMERS) +

MBROPT(*REPLACE) FMTOPT(*MAP *DROP)

Figure 5: A sequence of SQL and CL commands that simulate updating through a join

/*===================================================================*/

/* */

/* To compile: */
/* */

/* CRTCLPGM PGM(xxx/CHGPWD) SRCFILE(xxx/QCLSRC) MBR(CHGPWD) */
/* */

/*===================================================================*/

PGM

DCL VAR(&PWDEXPITV) TYPE(*CHAR) LEN(6)

DCL VAR(&PWDEXPDAYS) TYPE(*DEC) LEN(6)

DCL VAR(&PWDCHGDATE) TYPE(*CHAR) LEN(6)

DCL VAR(&PWDCHGYEAR) TYPE(*DEC) LEN(2)

DCL VAR(&PWDCHGMTH) TYPE(*DEC) LEN(2)

DCL VAR(&PWDCHGDAY) TYPE(*DEC) LEN(2)

DCL VAR(&SYSDATE) TYPE(*CHAR) LEN(6)

DCL VAR(&SYSYEAR) TYPE(*DEC) LEN(2)

DCL VAR(&SYSMTH) TYPE(*DEC) LEN(2)

DCL VAR(&SYSDAY) TYPE(*DEC) LEN(2)

DCL VAR(&MTHCALVAR) TYPE(*DEC) LEN(2)

DCL VAR(&DAYCALVAR) TYPE(*DEC) LEN(3)

DCL VAR(&TOTALDAYS) TYPE(*DEC) LEN(3)

DCL VAR(&DAYSLEFT) TYPE(*DEC) LEN(3)

DCL VAR(&PRVSIGNON) TYPE(*CHAR) LEN(13)

MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(END))

/* RETRIEVE THE DATE WHEN THE USER LAST CHANGED THEIR PASSWORD */

RTVUSRPRF PWDCHGDAT(&PWDCHGDATE)

CHGVAR VAR(&PWDCHGYEAR) VALUE(%SST(&PWDCHGDATE 1 2))

CHGVAR VAR(&PWDCHGMTH) VALUE(%SST(&PWDCHGDATE 3 2))

CHGVAR VAR(&PWDCHGDAY) VALUE(%SST(&PWDCHGDATE 5 2))

/* RETRIEVE THE SYSTEM DEFAULT PASSWORD INTERVAL TIME */

RTVSYSVAL SYSVAL(QPWDEXPITV) RTNVAR(&PWDEXPITV)

CHGVAR VAR(&PWDEXPDAYS) VALUE(&PWDEXPITV)

/* RETRIEVE THE SYSTEM DATE */

RTVSYSVAL SYSVAL(QDATE) RTNVAR(&SYSDATE)

CHGVAR VAR(&SYSYEAR) VALUE(%SST(&SYSDATE 5 2))

CHGVAR VAR(&SYSMTH) VALUE(%SST(&SYSDATE 1 2))

CHGVAR VAR(&SYSDAY) VALUE(%SST(&SYSDATE 3 2))

IF COND(&SYSMTH *EQ 01) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(31))

IF COND(&SYSMTH *EQ 02) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(28))

IF COND(&SYSMTH *EQ 03) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(31))

IF COND(&SYSMTH *EQ 04) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(30))

IF COND(&SYSMTH *EQ 05) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(31))

IF COND(&SYSMTH *EQ 06) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(30))

IF COND(&SYSMTH *EQ 07) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(31))

IF COND(&SYSMTH *EQ 08) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(31))

IF COND(&SYSMTH *EQ 09) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(30))

IF COND(&SYSMTH *EQ 10) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(31))

IF COND(&SYSMTH *EQ 11) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(30))

IF COND(&SYSMTH *EQ 12) THEN(CHGVAR +

VAR(&MTHCALVAR) VALUE(31))

IF COND(&SYSMTH *EQ &PWDCHGMTH) THEN(CHGVAR +

VAR(&TOTALDAYS) VALUE(&SYSDAY - &PWDCHGDAY))

IF COND(&SYSMTH *NE &PWDCHGMTH) THEN(DO)

CHGVAR VAR(&DAYCALVAR) VALUE(&MTHCALVAR - &PWDCHGDAY)

CHGVAR VAR(&TOTALDAYS) VALUE(&DAYCALVAR + &SYSDAY)

ENDDO

CHGVAR VAR(&DAYSLEFT) VALUE(&PWDEXPDAYS - &TOTALDAYS)

IF COND(&DAYSLEFT *LE 3) THEN(CHGPWD)

END:
ENDPGM

Figure 6: This program warns your users that it’s time to change their password