End of Year Feedback

We’ve made it through another year. I hope it’s been a good one for you in spite of the hardships and challenges. Let’s read some of the feedback that I received in 2015 and see what we can learn.

Hey, Ted:

To make a grep search case-insensitive, use the “-i” switch. I also add the “-n” switch to show the line number.

catsplf -j 681206/MYSELF/MYJOB QSYSPRT 1 | grep -in 'smith'

I also have a SCANSPLF command. It is not case insensitive, but that would not be hard to implement.

Keep the great articles coming.

–Bryan

Bryan wrote in response to Case-Insensitive Searching Of Spooled Files. I completely forgot about the -i switch, which eliminates the need to call the tr utility.

Hey, Ted:

Just to let you know I finally got a little time to spare and loaded the BASS software on one of our IBM i systems. Needless to say, it works fantastically. It will definitely come in handy in my job.

I do have one suggestion for the future. The output is going to the IFS. It would be nice to have the capability to go to a DB2 file in an either option.

Thanks again!

–Al

Thanks for letting me know you gave BASS a try, Al. I’m glad to hear it works for you. The last shop I worked in runs BASS scripts all day long, including in the overnight processing.

You can write to a physical file member by specifying the member name in IFS format. For example, member MYMBR of physical file is MYLIB/MYFILE would be like this:

/QSYS.LIB/MYLIB.LIB/MYFILE.FILE/MYMBR.MBR

Hey, Ted:

Regarding your article CONST Doesn’t Mean You Can’t Change It”, I’d like to mention another way that a CONST parameter can get changed during a call.

The scenario is passing the same field to two parameters on the same call. The following program passes FLD1 to both P1 (passed by reference) and P2 (const) for procedure MYPROC. For that call to MYPROC, P1 and P2 refer to the same storage. So P2 also gets changed when P1 gets changed.

ctl-opt dftactgrp(*no);
dcl-s fld1 char(10) inz('init val');

myproc(fld1 : fld1); // Pass the same field to two parameters
return;

dcl-proc myproc;
 dcl-pi *n;
   p1 char(10);
   p2 char(10) const;
 end-pi;
 dsply ('p2 before ' + p2);
 p1 = 'new val';
 dsply ('p2 after ' + p2);
end-proc;

–Barbara Morris, IBM Toronto

The list of audit codes in Ron Adams’ article Auditing Object Changes In A Production Environment is a good start, but it should include a few more codes, such as AD and CA entries. Additionally, the example given doesn’t give sufficient QAUDLVL entries to properly monitor a system. For example, it’s missing *SYSMGT. Overall, this is a good start of an article but could leave smaller customers with the impression that this is sufficient auditing.

Another interesting thing to consider is use of some of the newer journal capabilities rather than use CPYAUDJRNE.

CREATE TABLE QTEMP/QRYJRN AS (
SELECT JOURNAL_CODE AS CODE,
    JOURNAL_ENTRY_TYPE AS TYPE,
    ENTRY_TIMESTAMP AS DATTIM,
    SUBSTR(OBJECT, 11, 10) AS OBJLIB,
    SUBSTR(OBJECT, 1, 10) AS OBJNAM,
    OBJECT_TYPE AS OBJTYP,
    "CURRENT_USER" AS CURUSR, JOB_NAME AS JOBNAM,
    JOB_USER AS JOBUSR, JOB_NUMBER AS JOBNBR,
    PROGRAM_NAME AS PGMNAM,
    PROGRAM_LIBRARY AS PGMLIB,
    CAST(REMOTE_PORT AS CHAR(8)) AS PORT,
    SUBSTR(REMOTE_ADDRESS,1,30) AS IPADDR
 FROM TABLE
     (DISPLAY_JOURNAL('QSYS', 'QAUDJRN', '', '',
            CAST(NULL AS TIMESTAMP),
            CAST(NULL AS DECIMAL(21,0)),
            '', 'AD CA CO DO OM OR OW ZC',
            '', '', '', '', '', '', '' )) AS X
 WHERE OBJECT LIKE '%yourlibname%'
 ORDER BY DATTIM) WITH DATA; 

–Terry Ford, Project Manager: Security Services Delivery
IBM Systems & Technology Group–Lab Services, Rochester, MN

Thanks for your comments and for reminding us of the SQL-based DB2 for i Services, Terry. Michael Sansoterra has written about the services, but I don’t think we’ve covered DISPLAY_JOURNAL.

Hey, Paul:

First I would like to say how much I appreciate your articles. I have learned much from them.

I have a question about your article A First Look At SQL Descriptors. In the code example, for each of the three parameters, you repeat the “pad_And = ADD_AND;” AFTER defining the “myWhere” variable. Why not use it only where needed, in the second and third parameter additions? Also, why not just use the initialized ADD_AND variable each time?

–Jim

Thank you for the kind words, Jim. I am glad you find the articles of benefit.

The “pad_And = ADD_AND;” is just one way of handling the addition of the “AND” before a comparison. Basically, the “AND” should be before all conditions except the first one. Since there is no sure way of knowing which of the three conditions (if any) will be the first, the logic is:

• Always add a padder before a condition
• Start with a padder of “” (so the first condition has nothing before)
• Once a condition is added, change the padder to “AND ” (all following conditions will now be preceded by “AND”

By repeating the exact same logic for all conditions, when the ugly spectre of maintenance raises its ugly head and we need to add a fourth condition, I am not concerned where I add the code: at the start, middle, or end.

–Paul Tuohy

Hey, Ted:

I agree with your use of the library list. (See Where Do Library Lists Reside?). I generally follow that practice except when using CPYFRMSTMF and CPYTOSTMF.

Do you have any suggestions to get away from hard-coding libraries when using those commands? It’s a real problem when going between production and test environments.

–Greg

Use the Retrieve Member Description (RTVMBRD) to obtain the library name, Greg.

dcl  &DataFile   *char     128
dcl  &RtnLib    *char   10

rtvmbrd  file(SOMEFILE) rtnlib(&RtnLib)

chgvar   &DataFile ('/QSYS.LIB/' *cat &RtnLib *tcat +
          '.LIB/SOMEFILE.FILE/SOMEFILE.MBR')
cpytostmf frommbr(&DataFile) tostmf(somefile.dat) stmfopt(*replace)

Hey, Ted:

One of my former colleagues changed the text of message SQL6789. On the production system, the message read:

Have you considered using System i Navigator's Run SQL Scripts instead 
of STRSQL. Was it too slow?

This was the text on the development system:

Have you considered using System i Navigator's Run SQL Scripts instead 
of STRSQL. It's slower but it has a pretty interface.

–Darron

Darron refers to Where Do Library Lists Reside?

Hey, Ted:

I use the relatively new INCLUDE command in CL procedures to bring in named constants of the display attributes. That way I can simply add color to the messages as needed.

ChgVar Var( &Message ) Value( &Red *Cat &Message *Cat &Green )

We use it sparingly, as in your example. However it does come in handy to make messages stand out when we need it.

–Spencer

Spencer refers to Clamoring For Attention. For more about the color attributes, see Anatomy of a P-Field

Hey, Ted:

I just wanted to say I enjoyed Beware The Temporary Table. It was neat to see the alternatives. I usually settle with the first one that works. I do like to use views. However sometimes I feel like they just flounder out there and you may never use them again, and who has time to go back and clean them up? Nice response!

–David

Thanks, David. I’m glad Lynne brought this topic to our attention.

Hey, Ted:

I had an opportunity today to try using row value expressions. I like them. What I found was that green-screen STRSQL does not like it. I got the error” “Prompting not supported”. It’s just another reminder to get off the green screen and use other SQL tools.

–Russ

Hey, Ted:

Your version is:

select * from glxacts
where date between '2015-02-01' and '2015-02-28'
 and (department, account) in
    (values(22,180), (34,135), (46,198))

We have been coding such queries this way:

select * from glxacts 
where date between '2015-02-01' and '2015-02-28'
 and (department*1000+ account) in
    (22180, 34135, 46198)

Do row value expressions make the query run faster?

–Robert

I’ve used similar techniques before, Robert. Using a row value expression should be faster because the system can use indexes to select the data. Also, multiplication and addition, being arithmetic operations, are slow.

I was really excited to learn about this use of VALUES because it means I should have to do less of the method you (and I) have been using.

Hey, Ted:

The upsert article is very cool. I was not aware of the upsert terminology. I have been doing that sort of thing for a while using the old “try to update and if no record was affected, then do the insert” method.

–Daniel

SQL has plenty of features that I find of little or no use, but MERGE is not one of them.

Hey, Ted:

Thank you for the concise and clear article Remove Misleading Messages from Job Logs . It solved a problem that I had with a utility program that was the cause of huge job logs.

–Richard

We ran that article 10 years ago, Richard. It’s nice to know that “old” articles still help people today.

RELATED STORIES

CONST Doesn’t Mean You Can’t Change It”

Anatomy of a P-Field

Auditing Object Changes In A Production Environment

A First Look At SQL Descriptors

Where Do Library Lists Reside?

Clamoring For Attention

Beware The Temporary Table

Case-Insensitive Searching Of Spooled Files

Remove Misleading Messages from Job Logs

Row Value Expressions Simplify Complex Row Selection

The Powerful SQL Upsert