Well maybe not frequently, but I asked them at least once and maybe
others have as well. Here are some tips culled from my own experience and from the archives of
Yahoo Easy400Group - Easy400.
Hopefully this material will be helpful, but use at your own risk,
because I reserve the right to be wrong occationally.
1. Tuning HTTP for performance
2. System Date vs. Batch Date
3. Example of SQL embedded in RPG using DECLARE...CURSOR
4. Example of SQL embedded in RPG using DECLARE...CURSOR and PREPAR
5. Creating an SQL View using iSeries RUNSQLSTM Comand
6. Constructing SQL statements with dynamic WHERE clause
7. Why is data in Dataarea getting all mixed up?
8. Handling of libraries and library lists in iSeries CGI programming
1. Tuning HTTP for performance
Easy400 delivers good and consistent performance. Often slow response time, especially after IPL and restarting the HTTP server, is an HTTP server issue.
Refer to RedBook "HTTP Server (powered by Apache): An
Integrated Solution for IBM e-server iSeries Servers", Chapter 10
(Performance), URL
http://publib-b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/sg246716.html?Open
Giovanni B. Perotti, Easy400 Site Owner offers these tips:
1- The far best medicine is to use the output compressor facility (the
output buffer will be zipped on the iSeries, and automatically expanded by
all browsers).
The Apache directives to be used are:
LoadModule deflate_module /QSYS.LIB/QHTTPSVR.LIB/QZSRCORE.SRVPGM
SetOutputFilter DEFLATE
2-Though many claim that the Fast Response Cache Accelerator (FRCA) is a
fantastic performance feature, i did not notice any significant advantage
out of it. The Apache directives look like
FRCAEnableFileCache On
FRCACacheLocalFileRunTime /path1/*
...
FRCACacheLocalFileRunTime /pathn/*
3-Also little advantage I gained from the Local Cache. Apache directives look like
CacheLocalSizeLimit 25000
CacheLocalFile /path1/*
...
CacheLocalFile /pathn/*
4-Another annoying issue was that anytime Apache is restarted it takes
minutes to respond to the first CGI transactions. There is a way to preload
CGI HTTP jobs with an initial CGI (usually the home page). Then initial
response times are much better. This is what I did for my site:
StartCGI 2 QTMHHTP1
CgiInitialUrl /qsys.lib/easy400.lib/easy400p01.pgm QTMHHTP1
where easy400p01 is the CGI program running as home page.
One issue that many CGIDEV2 programmers quickly discover is that because commonly used UDATE and *DATE op-codes reference the job date not the system date, their job date may not change for weeks or months at a time!
Because a CGIDEV2 job begins when the HTTP server is restarted
and *not* when the user logs in, it is important not to use
UDATE or *DATE op-codes when having the actual system date
is important to the application.
Here are a few techniques to get the actual system date.
d* Here are a few ways to get the current SYSTEM date in as a d* 10 character Alpha format YYYY-MM-DD. d d Timestamp s Z d Timestampc s 26A d Today_Alpha s 10A d Today_Date s D d Today_Time s T * d DS d TIME12 1 12 0 INZ d TIME6 1 6 0 d TIME4 1 4 0 d DATE6 7 12 0 d * You will recall that timestamp retrieves System date. * Timestamp format is YYYY-MM-DD-HH.MM.SS.MMMMMM, so take * first 10 characters which represent system date. c time Timestamp c move Timestamp Timestampc c eval Today_Alpha = %subst(timestampc:1:10) c * Here is a faster method using the %date * Built-in-function WITHOUT PARAMETERS c eval Today_Date = %date() c eval Today_Alpha = %char(Today_date) c * Here I use both the %date and %timestamp Built-in-functions * Note that when I add the %timestamp paramter, I get will get * JOB Date, which is not what I wanted. c c eval Today_Date = %date(%Timestamp()) c eval Today_Alpha = %char(Today_date) c * A similar method to get current time (time format: HH.MM.SS) * c eval Today_Time = %time() c * For data structure lovers, this gives you both System date and * time in one line! c TIME TIME12 c c Seton Lr c RETURN |
DECLARE defines a cursor by giving it a name and associating
it with a specific query. In this example, DECLARE defines a
cursor named myCursor and associates it with string SqlStmtStr,
as follows:
FGLMAST IF E K DISK FQPRINT O F 132 PRINTER c*-------------------------------------------------------------------- c SqlStmtStr s 512a c*-------------------------------------------------------------------- c EXSR SQLopenGL c EXSR SQLgetGL c EXCEPT DETAIL1 c Dow Sqlcod <> 100 c EXCEPT DETAIL1 c EXSR SQLgetGL c ENDDO c EXSR SQLclose c Eval *inLR = *on c Return c*-------------------------------------------------------------------- c SQLopenGL BEGSR c/EXEC SQL c+ DECLARE myCursor CURSOR FOR c+ SELECT * c+ FROM GLMAST c/END-EXEC c*-------------------------------------------------------------------- c/EXEC SQL c+ OPEN myCursor c/END-EXEC c ENDSR c*-------------------------------------------------------------------- c SQLgetGL BEGSR c/EXEC SQL c+ FETCH NEXT FROM myCursor INTO: STAGLM,: CMPGLM,: GL#GLM c/END-EXEC c ENDSR c*-------------------------------------------------------------------- c SQLclose BEGSR c SQLclose BEGSR c/EXEC SQL c+ CLOSE myCursor c/END-EXEC c ENDSR c*--------------------------------------------------- OQPRINT E DETAIL1 1 o GL#GLM +1 |
The PREPARE statement associates a statement name
with a string containing a SQL statement.
FGLMAST IF E K DISK FQPRINT O F 132 PRINTER c*-------------------------------------------------------------------- d SqlStmtStr s 512a c*-------------------------------------------------------------------- c EXSR SQLPrepCurs c EXSR SQLprepStsmt c EXSR SQLopenGL c EXSR SQLgetGL c EXCEPT DETAIL1 c Dow Sqlcod <> 100 c EXCEPT DETAIL1 c EXSR SQLgetGL c ENDDO C EXSR SQLclose c Eval *inLR = *on c Return c*-------------------------------------------------------------------- c SQLPrepCurs BEGSR c/EXEC SQL c+ DECLARE myCursor CURSOR FOR dynsql c/END-EXEC c ENDSR c*-------------------------------------------------------------------- C SQLprepStsmt BEGSR c Eval SqlStmtStr = 'Select * from GLMAST' C/EXEC SQL C+ prepare dynsql from :SqlStmtStr C/END-EXEC C ENDSR c*-------------------------------------------------------------------- C SQLopenGL BEGSR c/EXEC SQL c+ OPEN myCursor C/END-EXEC C ENDSR c*-------------------------------------------------------------------- c SQLgetGL BEGSR c/EXEC SQL c+ FETCH NEXT FROM myCursor INTO: STAGLM,: CMPGLM,: GL#GLM c/END-EXEC c ENDSR c*-------------------------------------------------------------------- c SQLclose BEGSR c/EXEC SQL c+ CLOSE myCursor c/END-EXEC c ENDSR c*-------------------------------------------------------------------- OQPRINT E DETAIL1 1 o GL#GLM +1 |
-- This is an example of iSeries SQL View.
-- Here I use SQL to create a summary View. Note that this cannot be accomplished with DDS.
-- In the second part of this script, I add labels to my View
-- I may want to store this information text file in Source File Member QDDSSRC
or make a special source memeber called QSQLSRC
-- To create this view:
-- RUNSQLSTM SRCFILE(MYCGILIB/QDDSSRC) SRCMBR(EXPDET41A) OUTPUT(*NONE)
CREATE VIEW MYCGILIB/EXPDET41A (
ACCTP41 ,
ACCTY41 ,
USERID41 ,
EXPSRF41 ,
CPYCDE41 ,
EXPSTS41 ,
SUM_KM ,
SUM_AMT )
AS SELECT ACCTP41, ACCTY41, USERID41, EXPSRF41, CPYCDE41,
EXPSTS41, SUM(KILOM41), SUM(CHKAM41)
FROM MYCGILIB/EXPDET41
GROUP BY ACCTP41, ACCTY41, USERID41, EXPSRF41, CPYCDE41,
EXPSTS41 ;
LABEL ON COLUMN MYCGILIB/EXPDET41A (
ACCTP41 TEXT IS 'ACCOUNT PERIOD' ,
ACCTY41 TEXT IS 'ACCOUNTING YEAR' ,
USERID41 TEXT IS 'USER ID' ,
EXPSRF41 TEXT IS 'EXPENSE REPORT REF#' ,
CPYCDE41 TEXT IS 'COMPANY CODE' ,
EXPSTS41 TEXT IS 'EXPENSE STATUS' ,
SUM_KM TEXT IS 'SUM OF KM' ,
SUM_AMT TEXT IS 'SUM OF NET AMOUNT') ;
|
d WhereCLause s 512a
d FirstWhere s N
d cQuote C const('''')
d SqlStmtStr s 512a
d SqlStmtStrSum s 512a
/free
//--------------------------------------------------------------------
BEGSR SQLprepStsmt ;
//--------------------------------------------------------------------
// Blank out your where clause
Eval WhereClause = *blanks ;
// Default FirstWhere to Yes
Eval FirstWhere = *on ;
// Where Year is specified
If AcctYear <> *blanks ;
Exsr Add_And_SR ;
Eval WhereClause = ' ACCTY41 = ' + cQuote + AcctYear + cQuote ;
EndIf ;
// Where Period is specified
If AcctPeriod <> *blanks ;
Exsr Add_And_SR ;
EndIf ;
// Where Status Code is specified
If StatusCode <> *blanks ;
Exsr Add_And_SR ;
Eval WhereClause = %trim(WhereClause) +
' EXPSTS41 = ' + cQuote + StatusCode + cQuote ;
EndIf ;
// Where USERID Code is specified
If UserCode <> *blanks ;
Exsr Add_And_SR ;
Eval WhereClause = %trim(WhereClause) +
' USERID41 = ' + cQuote + UserCode + cQuote +' ' ;
EndIf ;
// Add WHERE to Where clause when not first Segment
EXSR Add_Where ;
Eval SqlStmtStr = 'Select * from EXPDET41B ' +
%trim(WhereClause) +
' Order by ' +
' USERID41 asc, ' +
' EXPSRF41 asc ' ;
//-----------------------------------------------------------------
BEGSR Add_And_SR ;
//-----------------------------------------------------------------
// only start adding 'AND' to 'Where' clause after first 'where' clause segment
If FirstWhere = *off ;
Eval WhereClause = %trim(WhereClause) + ' and ' ;
EndIf ;
Eval FirstWhere = *off ;
ENDSR ;
//-----------------------------------------------------------------
BEGSR Add_Where ;
//-----------------------------------------------------------------
// add the 'Where' only if there is at least one 'where' segment
// set FirstWhere to *off
If FirstWhere = *off ;
Eval WhereClause = ' Where ' + %trim(WhereClause) ;
EndIf ;
ENDSR ;
//-----------------------------------------------------------------
7. Why is data in my Dataarea getting all mixed up?
Non-persistent CGI is "stateless". In other words, the
browser connects to the server, makes a request (calls a CGI program),
receives the response, then disconnects from the server. On the
next request from the same browser, there is no guarantee that
the same program has maintained the state (variables and record
positioning) it was left with at the end of the previous
request." - GP
The use of Data areas, and anything in QTEMP for that matter, is
generally not recommended with iSeries CGIDEV2 programming because
a specific web user does not have a unique dedicated job as is typical
in green screen programming. If a dataarea is used in CGI
programming, then there is a very good chance that a user may read
in a dataarea populated from another user.
The same issue is found in program variables that are not initiated
or explicitly set within the program.
A more reliable strategy is to create a unique iSeries
userspace in a special library for each user (maybe based
on a random number), store data in the user space or link
the userspace name to a key field in a PF record... Then
thread the Userspace name from page to page in a hidden
variable and use it to retrieve information as required.
Instead of passing the userspace name from page to page, cookies
can be used instead.
see references:
http://www.easy400.net/cgidev2o/exhibiu9.htm#3a
http://www.easy400.net/cgidev2h/readme.htm#funcrandomnumber
http://www.easy400.net/cgidev2o/exhibiu8.htm
The handling of libraries and library lists in iSeries CGI
programming environment generates many questions for new
iSeries web developers. Since the HTTP
web server and CGI-web concepts have been developed on other
platforms and ported to the AS/400, we have to put aside some
of our as/400 notions of "the way it should work" and deal
with the way it does work.
A web user may run an AS/400 CGI program by indicating the
program's qualified path, name and parameters in the URL of
his web browser. However this user cannot run
just any program on the AS/400. A web user may only run programs
in the library or libraries permitted by the HTTP server
configuration. This often point
can cause some grief to new CGI programmers.
When you look at WRKACTJOB you will see CGI jobs running under
the HTTP Server user profile called QTMHHTTP. User QTMHHTTP uses
job description QDFTJOBD for library list. However playing with
user profile QTMHHTTP or job description QDFTJOBD to define your
library list is a waste of time. The actual library list
is found in job description QHTTPSVR.
Now don't stop reading here and go off and start fixing QHTTPSVR
before reading a bit about some recommended web development practices
regarding libraries and library lists.
Isolate programs in separate library
It is recommended to keep your CGI programs in a separate
library away from other production programs. This is especially
true for potentially harmful programs. For instance, a web user
who has access to library MYLIB so that he can call an Order
Entry program called MYLIB/ADD_ORDER
(http://as400.MySite.com/MYLIB/ADD_ORDER)
may be able to figure out how to run your
MYLIB/DEL_USER or MYLIB/YEAR_END programs. Luckily green screen
programs that have mandatory entry parameters cannot be
successfully called from a web browser, however you
probably don't want someone trying this out. If you must
share your CGI program libary with your green screen programs, there
are some Apache directives that will permit/restrict web
access to certain programs. Similarly, although it is
not as big a risk, it is also recommended to keep production
data files outside of the libraries permitted by the HTTP server
OK then, if my web library list has only one library and
I want to use data files and some utility programs in other
libraries, then what am I supposed to do?
Accessing data Securely
The most secure strategy for controlling access to data files is
by allowing access to the file on a case by case basis. This is best done by:
1. Use OVRDBF ... SECURE(*YES) in a CL before each program call that requires a file.
This can equally be done within the RPG program with a QCMDEXEC or a
DoCmd
2- use USROPN on every file definitions in your RPG program
3- OPEN and CLOSE files as they are required in the program
If you feel that adding OVRDBF for each file before each program is too much work, you can alternatively use the ADDLIBLE at the beginning of each program and RMVLIBLE at the end of each program. This is as effective and almost as secure.. The CGIDEV2 DoCmd() function will simplify this process (example: DOCMD(ADDLIBLE MYLIB1) ).
In either case, hardcoding library names in your program is not always the wisest approach. I prefer to keep my required libraries in a physical file. At the beginning of each program I run a program that loads up required libraries and at the end of the program I call a program to remove them. Some slight enhancement to this technique would permit you to control library access for production and test environments, or control library access by company/division.
As of V5R1M0, IBM added the EXTFILE file specification keyword.
This can be used to specify the external file name and its library, thus removing the need to perform an override. USROPN is not required but can be used if you want.
Changing default library list with routing entries
Now if you are intent on using a global library list for all your HTTP/CGI jobs you can add a routing entry (ADDRTGE) to the QHTTPSVR subsystem to call a custom program that changes the library list of the job being started. No changes to your CGI code are necessary. Note that you have to remember/automate the ADDRTGE for subsystem QHTTPSVR because IBM resets the subsystem description back to the default when you upgrade to a new version of the operating system.
Here is a sample of the program used in this approach.
PGM
DCL VAR(&JOB) TYPE(*CHAR) LEN(10) /* name of the http server instance */
RTVJOBA JOB(&JOB)
/* create library list for each server instance */
IF COND(&JOB *EQ 'PRODUCTION') THEN(CHGLIBL +
LIBL(QTEMP PRDPGMLIB1 PRDPGMLIB2 PRDPGMLIB2 +
PRDDATLIB1 PRDDATLIB2 PRDDATLIB3 ))
IF COND(&JOB *EQ 'DEVELOPMNT') THEN(CHGLIBL +
LIBL(QTEMP DEVPGMLIB1 DEVPGMLIB2 DEVPGMLIB2 +
DEVDATLIB1 DEVDATLIB2 DEVDATLIB3 ))
TFRCTL PGM(QCMD)
ENDPGM
Changing default library list with Apache Server directive
Another option if you are on version 2.0.47 of the Apache HTTP server or greater is to use the SetEnv QIBM_CGI_LIBRARY_LIST directive. Example:
SetEnv QIBM_CGI_LIBRARY_LIST "PRDPGMLIB1; PRDDATLIB1; PRDDATLIB2"
A few words about Stored
Procedures