Hey, Ted:

Some of our iSeries and AS/400 users are not impressed with the Ps, Qs, and other letters that show up at the end of numeric fields when they press the Field Minus key. The only way I know to make the Field Minus key insert a trailing minus sign is to code an S in column 35, which represents the Keyboard Shift in a display file. However, code S does not permit users to enter the decimal point. In order to let users key the decimal point, I have to use a code Y instead. But, code Y changes the last digit of a negative number to a letter. This is frustrating. Can you help?

I understand your problem, and I don't have a great answer. Since you want users to key decimal points (.), you'll have to specify Y in column 35. I suggest that you have users key a trailing minus sign (-) and press the Field Exit or Field Plus key instead. This is one extra keystroke, but it will provide the result you want. Users not wanting to type the minus sign can press Field Minus and live with the letters.

Here's a little DDS to illustrate:

 *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+

A            CREDITLIM      7Y 2B  7 30EDTCDE(J)  

A                                      CHECK(RB)  

If the coding effort is worth it to you, here's a technique that Midrange Server Technical Editor David Morris has used. You can force an update of the displayed information when the user presses the Field Exit key with the CHECK(ER) keyword. On a fast system, this technique refreshes the display without a noticeable pause; on an overloaded system, you probably don't want to use this technique. Here is a simple program and display file with two numeric fields that will refresh with the minus sign:

A*******************************************************************

A*  Display - TstNumInpZ                                           *

A*  Purpose - Demonstrate numeric input kludge.                    *

A*  Compile - CRTDSPF    FILE(objlib/TSTNUMINPZ)                   *

A*                       SRCFILE(srclib/QDDSSRC)                   *

A*                       RSTDSP(*YES)                              *

A*******************************************************************

A                                      DSPSIZ(24 80 *DS3) 

A                                      CA03(03) 

A                                      CF06(06) 

A          R PROMPT

A                                      OVERLAY

A                                      PUTOVR

A                                  1 26'Numeric Input Demonstration'

A                                      DSPATR(HI) 

A                                  1 72SYSNAME

A                                  2 62OVRATR

A                                      DATE

A                                      EDTCDE(Y) 

A                                  2 72TIME

A                                      OVRATR

A                                  8 15'Enter:'

A                                  9 16'Numeric Value One . .'

A            NUM1           9Y 2B  9 38OVRDTA

A  21

AO 42                                  DSPATR(PC) 

A  21                                  DSPATR(RI) 

A                                      CHANGE(41) 

A                                      CHECK(FE) 

A                                      CHECK(ER) 

A                                      EDTCDE(J) 

A                                 10 16'Numeric Value Two . .'

A            NUM2           9Y 2B 10 38OVRDTA

A  22

AO 41                                  DSPATR(PC) 

A  22                                  DSPATR(RI) 

A                                      CHANGE(42) 

A                                      CHECK(FE) 

A                                      CHECK(ER) 

A                                      EDTCDE(J) 

A          R FOOTER

A                                 23  2'F3=Exit  F6=Submit'

A                                      COLOR(BLU) 





 /TITLE TstNumInp - Demonstrate numeric input. 

 ***************************************************

 *  Module  - TstPrtTkt                            *

 *  By      - David Morris                         *

 *  Purpose - Demonstrate numeric input.           *

 *  Compile - CRTRPGMOD  MODULE(objlib/TSTNUMINP)  *

 *                       SRCFILE(srclib/QRPGLESRC) *

 *                       SRCMBR(TSTNUMINP)         *

 *                       DBGVIEW(*SOURCE)          *

 *  Create  - CRTPGM     PGM(objlib/TSTNUMINP)     *

 *                       TEXT(*ENTMODTXT)          *

 ***************************************************

H OPTION(*SRCSTMT: *NODEBUGIO) 

Fmorrdf    CF   E             WORKSTN

 ***********************************

 * Define main procedure prototype *

 ***********************************

DTstNumInp        PR



 ***************************************************

 * *TstNumInp     Main procedure                   *

 ***************************************************

C*

C* Show valid command keys. 

C                   WRITE     Footer

C*

C* Loop until F3 is pressed or no errors. 

C                   DOU       *IN03 OR *IN06

C*

C* Prompt for numeric input. 

C                   EXFMT     Prompt

C                   IF        NOT *IN03 AND

C                             NOT *IN41 AND

C                             NOT *IN42

C* Perform some editing on enter or F6

C                   ENDIF

C                   ENDDO

C*

C                   EVAL      *INLR       = *ON

C                   RETURN

The combination of Change indicators and position cursor indicators in the display file causes the cursor to advance to the next field. Because very little processing occurs when a value has changed, the response is usually acceptable.

-- Ted

Hey, Ted:

When I want to call a CL command from within Interactive SQL, I type "CALL QCMD" or "CALL QUSCMDLN".

Sorry, Ted. I don't mean to be facetious, but this functionality is available already and does not require any knowledge--which you subtly glossed over--of where stored procedures are held and registered, or the implications of backing up on one system and restoring to another. Sorry again,
Andy Foster

Dear Andy, You've no reason to apologize. I thank you and Russ Khoury for sharing that valuable information with your fellow readers of this newsletter and me. I didn't know the functionality was there, neither did the tech people who helped me. I assume it's been there all along.

-- Ted

Dear Readers:

Andy and Russ wrote to me regarding the October 12, 2001 issue of the Midrange Guru, in which I shared two stored procedures that can be used to execute CL commands from within an interactive SQL/400 session. To put it bluntly, I had reinvented the wheel. As Russ and Andy pointed out, you can use the SQL CALL command to call QCMD or QUSCMDLN. What they didn’t say, and what I found out, is that you can call any compiled program from interactive SQL. In all fairness, this is not evident from reading IBM's SQL reference manual, which says that CALL invokes "procedures". I don't think many RPG programmers, especially those of us who work with ILE, think of a compiled program as a procedure.

You can also call REXX procedures and Java procedures, but you have to use CREATE PROCEDURE to set them up.

As for glossing over information, I need to explain that my goal is to send out short tips that readers can easily implement, not encyclopedic tutorials. Here's a little more information for those who are interested.

I store scripts that contain the appropriate create and drop SQL statements for every function and procedure on my system. When I need to recreate a procedure or function, I run the script with a user-defined PDM option I call RS that looks like this:

RUNSQLSTM SRCFILE(&L/&F) SRCMBR(&N)

This technique also works on any system with the SQL development kit installed and is easy to integrate with most change-management packages. It also keeps the SQL catalog files that Andy mentions in sync. If you go outside SQL, using save and restore to move procedures and functions does get tricky. Here are some excerpts from the SQL Reference manual:

"When a program that was created for an SQL procedure or a service program that was created for an SQL function or a sourced function is restored, it is automatically added to the SYSROUTINES and SYSPARMS catalogs, as long as the procedure or function does not already exist with the same signature. SQL programs created in QSYS will not be created as SQL procedures when restored. Additionally, external programs or service programs that were referenced on a CREATE PROCEDURE or CREATE FUNCTION statement may contain the information required to register the routine in SYSROUTINES. If the information exists and the signature is unique, the functions or procedures will also be added to SYSROUTINES and SYSPARMS when restored.

SYSPARMS and SYSROUTINES may contain references to external non-ILE programs. Since restores of non-ILE programs do not repopulate these catalog tables, you may wish to regularly save these tables."

-- Ted  

TigerTools offers Fast400 to slash interactive response times on AS400 and iSeries servers. Fast400 helps AS400 and iSeries users get better interactive response time WITHOUT having to upgrade the server hardware. This software solution allows interactive users to get more throughput on their AS400 and iSeries server. Fast400 uniquely "tunes" each interactive job on the system. Depending on the AS/400 and iSeries Interactive Features (CPW) installed on your machine, some users can see an increase of 1000% interactive throughput. To learn more about CPW and how Fast400 can affect your server, you can visit http://www.barsaconsulting.com/barsa-tips.htm Fast400 is available to evaluate in your own environment. Fast400 is completely available online. In a matter of minutes, you can download the documentation, the software, and a DEMO license key from http://www.tigertools.com/default.asp Pricing starts from $1,000 USD. Pricing depends on model and feature codes. Quantity discounts are available. For more information, you can email us at sales@tigertools.com or call 800-266-7240 x1700.

One of the great things about the OS/400 community is that it is indeed a community. We may be all working from our cubicles, but we are all connected and trying to figure out how to best employ the computer technology at our disposal. There are more than a few ways to skin any cat, and if you have a clever and unique answer to a problem that one of our Midrange Gurus has solved, we'd love to hear from you. This newsletter is an open dialog, and we value your input as well as your readership.

It goes without saying--but we'll say it anyway--that your hard technical questions pertaining to real world problems are equally valuable as a foundation for this newsletter as are your programming insights. We hope you find all the editions of Midrange Guru valuable, and we are going to work hard to make sure that they are.

If you have a tough problem, our gurus can probably help. Their mailboxes are always open.

* Email Ted Holt at tholt@itjungle.com