AS400 Stored Proc

Hello List,

I hate to bother you guys with a general DB2/400 SQL question, but I am having real issues with this. I am trying to write an oustide stored proc to read the F9312 weekly and provide data for a report (SOX Compliance). I don't have any JDE Report writing experience so I am trying to do this on AS400. The following syntax would work for MS SQL stored procs...can anyone tell me how to work this on the AS400?

DECLARE CURDATE CHAR(6),
DECLARE BEGDATE CHAR(6),

SET CURDATE = SELECT MAX(SHUPMJ) FROM SYS7333.F3912
SET BEGDATE = SELECT (MAX(SHUPMJ) - 7) FROM SYS7333.F9312

SELECT * FROM SYS7333.F9312
WHERE SHEVTYP = '01' AND SHEVSTAT = '02'
AND (SHUPMJ BETWEEN begdate AND curdate)
ORDER BY SHUPMJ, SHUPMT, SHUSER;

Thanks

Ryan Hunt

On the AS/400, the library is separated from the file by a '/', so it's sys7333/f9312. Otherwise the statements should work.

In order to create the stored procedure on the AS/400 I think you will need a compiler and have on the system the product 5722-ST1. Instead of doing this on the AS/400, you can create a stored procedure in SQL/Access using either ODBC or ODA connection back to the AS/400.

Thanks for the response Jean. I really need to have it on the AS400 for what I am going to be doing. Also, I created the stored proc using iSeries Navigator so "/" wouldn't work.

Here is what was required...in case you wanted to see it.

CREATE PROCEDURE PRODDTA.FAILEDLOGINS( )

DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC PRODDTA.FAILEDLOGINS
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
BEGIN
DECLARE SQLSTMT VARCHAR ( 1000 ) ;
DECLARE BEGDATE NUMERIC ( 6 ,0 ) ;
DECLARE CURDATE NUMERIC ( 6 ,0 ) ;
DECLARE C1 CURSOR WITH RETURN FOR S1 ;
SET CURDATE = (SELECT MAX(SHUPMJ) FROM SYS7333.F9312) ;
SET BEGDATE = (SELECT (MAX(SHUPMJ)-7) FROM SYS7333.F9312);
SET SQLSTMT =
'SELECT SHUSER AS USERNAME, SHUPMJ AS DATE, COUNT(SHUSER) AS ATTEMPTS
FROM SYS7333.F9312
WHERE SHEVTYP = ''01'' AND SHEVSTAT = ''02''
AND (SHUPMJ BETWEEN ? AND ?)
GROUP BY SHUSER, SHUPMJ
ORDER BY ATTEMPTS DESC, DATE, USERNAME';

PREPARE S1 FROM SQLSTMT ;
OPEN C1 USING BEGDATE, CURDATE ;
SET RESULT SETS CURSOR C1 ;
END ;

---that's a whole lot more than I was expecting!!!
Thanks

Ryan Hunt

Hi Ryan,

How do you get your results back from the proc? Are you calling it from RPG program?

Wes

At the moment I am simply calling the results from the SQL Scripts interface of iSeries Navigator with:

CALL PRODDTA.FAILEDLOGINS

My plan is to call the result set using a VB program that will then save the data as an Excel file for Management to review at their leisure.

Thanks

Ryan

that is alot more than I would have guessed. I was going down the path of using an As/400 program, type RPGSQL or CSQL, whatever. I come from the AS/400 background, not the SQL. Glad you got your answer.



Jean Driscoll
AS/400(V5R2) Co-existent Xe SP22, Update 7/A73Cum12
WWW.JDETips.com

Re: RE: AS400 Stored Proc

Jean, is RPG essentially what I would use to write something equivalent to a windows batch file on AS400? If so, can you suggest any reading? I need to write some very simple automated processes on the AS400 and I wasn't sure how to approach it.

Thanks in advance.

Ryan Hunt

Re: RE: AS400 Stored Proc

Ryan,
IBM has a very good site...
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/rzahgrpgcode.htm. You'll have to user ILE RPG since RPG can't handle fileds more than 6 char.