End-of-Year Odds and Ends

Merry Christmas, intelligent i professional! Let’s end the year with a few little tips, including two unusual ways to use OPNQRYF’s %ABSVAL function from reader Victor Pisman. May 2009 be the best year ever for all of us.

–Ted

Run Qshell from CL Procedures

Q: Do you have a simple example of using Qshell with a script? I think I can muddle through setting up the script. I am unsure of how to call it from a CL procedure.

A: You can use the dot utility to run a script that you’ve created in the IFS. This command runs the IFS file MyScript.sh.

SBMJOB CMD(QSH CMD('. MyScript.sh')) JOB(MYJOB) JOBQ(SOMEJOBQ)

Or you can put the script into a CL variable and execute it from there, like this:

      chgvar  &Script +  
        (+               
        'cd' *bcat &CurDir *bcat '|| exit 1; + 
         [[ -d text ]] || exit 3; + 
         [[ -d archive ]] || exit 5; +
         ... more script lines omitted in this example ...
         ')
      qsh cmd(&Script)

Please Find My Message

Q: How can I search through the descriptions in a message file without manually looking at all the messages?

A: Print the message descriptions.

DSPMSGD RANGE(*FIRST *LAST) MSGF(MYMSGF) DETAIL(*BASIC) OUTPUT(*PRINT)

Then look at the spool file and search.

Everything Is Oojah-cum-spiff

Q: How do I display a message to a user at the bottom of the screen after a CL program has completed?

A: Use SNDPGMMSG to send a completion message.

sndpgmmsg  msgid(cpf9898) msgf(qcpfmsg) msgtype(*comp) +
              msgdta('Program' *bcat &PgmName *bcat +
                     'completed normally')

Notice the message type is *COMP, for completion.

DATFMT Error in SQL

Q: I am having a problem with date data type fields in my files. If the date fields are the default “0001-01-01”, SQL gives me a mapping error. If I put the DATFMT(*ISO) keyword on the HSPEC, it still doesn’t fix the problem. I have to prompt the compile and change the CRTSQLRPGI DATFMT to be *ISO to fix the problem. Have you had this problem?

A: The compiler wants to use the default *MDY format, which only accepts dates from 1940-2039. Prompting and changing the format makes it use *ISO format instead. If you don’t want to have to prompt, put the compiler option in your source code.

exec sql set option datfmt=*iso;

Remember that options have to precede all other SQL commands in the program.

You Can Look, But Don’t Touch!

Q: I want to have users be able to view a job queue to see that their program is running or waiting to run. I don’t want them to be able to change any attributes or have access to the command line. Is this possible?

A: Revoke authority to commands ADDJOBQE, CHGJOBQE and RMVJOBQE.

Use %ABSVAL to Avoid Division by Zero

We know that division by zero is undefined. But sometimes we are faced with that possibility in real life. For example, a profit margin value is calculated by the following formula.

Margin = (Price - Cost) / Price

Normally if the price is zero, this formula cannot be used. But with %ABSVAL this formula can be used even if the price is zero. The following margin formula handles division by zero.

Margin = (Price-Cost) / ((%ABSVAL (Price - 0.01)+Price+0.01)/2)

For example, if price = 9.60 and cost = 7.20 the formula returns a margin of 25 percent.

Margin = (9.60-7.20) / ((%ABSVAL (9.60-0.01)+9.60+0.01)/2) 
       = 2.40 / ((9.59 + 9.61)/2)
       = 2.40 / 9.60
       = 0.25.	

When the price and the cost are zero, the margin is zero.

Margin = (0-0) / ((%ABSVAL (0-0.01)+0+0.01)/2) = 0 / (0.01 + 0.01)/2
       = 0 / 0.01
       = 0 

Here’s a CL example showing how the formula would be coded.

OPNQRYF FILE ((PRR)) FORMAT (TESTF3) KEYFLD ((PRITNO)) + 
   MAPFLD ((FLD1 'PRICE - 0.01')  +       
           (FLD2 'PRICE + 0.01')   +                    
           (FLD3 '(%ABSVAL (FLD1)+(FLD2))*0.5')   +       
           (FLD4  '(PRICE - COST) * 100')   +  
           (MARGIN  'FLD4 / FLD3')) +
   OPNID (QRYFILE)                      

Use %ABSVAL to Convert Negative Numbers to Zero

The quantity available for purchase for an item is defined as the quantity on hand less the current customer requirements. If there are 100 units of an item on hand, and there are customer requirements for 80 of them, 20 are available. But if there are current requirements for 150, then the available quantity is 100-150, or -50.

The negative values are equivalent to zero and needs to be converted to zero. The following is the formula to convert negative values to zero using %ABSVAL:

Available =  (%ABSVAL (OnHand - Requirements) + OnHand - Requirements) / 2

If OnHand is 100 and Requirements is 80, Available is 20.

Available = (%ABSVAL (100 - 80) + 100 - 80) / 2
          = (20 + 20) / 2
          = 20

And when OnHand is 100 and Requirements is 150, Available is zero.

Available = (%ABSVAL (100 - 150) + 100 - 150) / 2
          = (50 - 50) / 2
          = 0

The following CL demonstrates this formula:

OPNQRYF FILE ((IBR)) FORMAT (TESTF1) KEYFLD ((ITEM)) +
   MAPFLD((FLD2 'ONHAND - REQ')  +   
          (FLD1 '(%ABSVAL(FLD2)+FLD2)*0.5')) 

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot