AS400 Stored Procedure

I am attempting to create a stored procedure on an iSeries (v5R4) which doesn't seem to be functioning.  I am generating 2 temp tables, then pulling data from them on a join.
 
--  Generate SQL 
--  Version:                   	V5R4M0 060210 
--  Generated on:              	11/08/10 08:35:30 
--  Relational Database:       	MCAA4 
--  Standards Option:          	DB2 UDB iSeries 
  
SET PATH "QSYS","QSYS2","GSUTTON" ; 
  
CREATE PROCEDURE SCDATA.ROGERSCHANGED ( 

	IN DT DECIMAL(8,0) ) 

	DYNAMIC RESULT SETS 1 

	LANGUAGE SQL 

	SPECIFIC SCDATA.ROGERSCHANGED 

	NOT DETERMINISTIC 

	MODIFIES SQL DATA 

	CALLED ON NULL INPUT 

	SET OPTION  ALWBLK = *ALLREAD , 

	ALWCPYDTA = *OPTIMIZE , 

	COMMIT = *NONE , 

	DECRESULT = (31, 31, 00) , 

	DFTRDBCOL = *NONE , 

	DYNDFTCOL = *NO , 

	DYNUSRPRF = *USER , 

	SRTSEQ = *HEX   

	BEGIN 

--unused, holder for the SQLEXCEPTION Handler 

DECLARE MSG CHAR ( 50 ) ; 

--The string to hold the import command 

DECLARE OS400_CMD CHAR ( 256 ) ; 

--the string to hold the final SQL Statement 

DECLARE SQL_STMT CHAR ( 1200 ) ; 

--Cursor used for the result set 

DECLARE C1 CURSOR FOR S1 ; 

  

--configure the  exception handler - this is in place to allow a drop table statement even if the table doesn't exist 

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 

	SET MSG = 'No table' ; 

--to ensure that the tables aren't created twice in the same session 

DROP TABLE QTEMP . TEMPROGERS ;

DROP TABLE QTEMP.TEMPROGERS2 ; 

  

--create the temporary table to hold retrieved values 

DECLARE GLOBAL TEMPORARY TABLE TEMPROGERS ( CASE# CHAR ( 18 ) , FNAME CHAR ( 7 ) , LNAME CHAR ( 18 ) ) ; 

--create the temporary table to hold retrieved values 

DECLARE GLOBAL TEMPORARY TABLE TEMPROGERS2 ( CASE# CHAR ( 18 ) , NAME CHAR ( 30 ) ,SEQ DECIMAL(5,0) ) ; 



--insert data into temp table

INSERT INTO TEMPROGERS (SELECT GCASE#, GFIRST, GLAST FROM SCACCTTR WHERE GPLDAT>=DT AND SCACCTTR.GCLNT IN (221500, 221501, 221503, 221504, 221505, 221506, 

                      221507, 221508, 221509, 221510, 221511, 221512, 221513, 221514, 221517, 221518, 221524, 221525, 221529, 221536, 221537, 230111, 230112, 

                      230113, 230114, 230115, 230116, 230117, 230123, 230124, 230125, 230126, 230127, 230128, 230129, 230136, 230137, 230138, 661503, 661504, 

                      661505, 661506, 916559, 916560, 916561, 916562, 916563, 916564, 916565, 916566, 916567, 916568, 916569, 916570) AND GSTCOD=''
);



INSERT INTO TEMPROGERS2 (SELECT UDWCASE, UDWTEX, UDWSEQ FROM SCUDWIN WHERE UDWWI#=53 AND (UDWSEQ=17 OR UDWSEQ=18) AND UDWCASE IN (SELECT CASE# FROM TEMPROGERS
));



--create the SQL for the final result set query, concatenating the input variable cols to allow changing which columns are selected 

SET SQL_STMT = 'SELECT CASE# FROM QTEMP.TEMPROGERS2 AS T1 AC INNER JOIN QTEMP.TEMPROGERS as T2  ON T1.CASE# = T2.CASE# WHERE (T1.NAME<>T2.FNAME AND T1.SEQ=17) OR (T1.NAME<>T2.LNAME AND T1.SEQ=18)' 
;

  

--insert the prepared query into the actual cursor query 

PREPARE S1 FROM SQL_STMT ; 


--open the cursor to return the result set 

OPEN C1 ; 

  

END

Open in new window

When I call the procedure from .NET I get an error: "SQL0104 Token SCDATA was not valid. Valid tokens: ( INTO USING."  When I try to call it from a SQL Window on iSeries Navigator it executes correctly, but returns no result set whatsoever.  Is there a way to simplify this procedure and to ensure that it is built correctly?

Thanks,
Geoff
LVL 10
GeoffSuttonAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Gary PattersonConnect With a Mentor VP Technology / Senior Consultant Commented:
Return code 0 means that the query ran successfully.  Your query probably just doesn't produce any results.  Substitute a simple, known results-producing query and see if that rives you a result set.

Or run the query in the SP manually to verify that it produces results.

- Gary Patterson

0
 
Dave FordSoftware Developer / Database AdministratorCommented:

It appears that it cannot find the schema entitled SCDATA. Ensure that the SCDATA schema is in your library-list.

HTH,
DaveSlash
0
 
GeoffSuttonAuthor Commented:
It is, I think.  I have modelled this on several other queries and procedures written in the distant past which function correctly.  The paths and schema's are all good so far as I can tell.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Dave FordSoftware Developer / Database AdministratorCommented:

Strange.

Try specifying QTEMP as the schema for the INSERTs into the temp tables.

e.g.

--insert data into temp table
INSERT INTO QTEMP.TEMPROGERS (SELECT GCASE#, GFIRST, GLAST FROM SCACCTTR WHERE GPLDAT>=DT AND SCACCTTR.GCLNT IN (221500, 221501, 221503, 221504, 221505, 221506, 
                      221507, 221508, 221509, 221510, 221511, 221512, 221513, 221514, 221517, 221518, 221524, 221525, 221529, 221536, 221537, 230111, 230112, 
                      230113, 230114, 230115, 230116, 230117, 230123, 230124, 230125, 230126, 230127, 230128, 230129, 230136, 230137, 230138, 661503, 661504, 
                      661505, 661506, 916559, 916560, 916561, 916562, 916563, 916564, 916565, 916566, 916567, 916568, 916569, 916570) AND GSTCOD=''
;

INSERT INTO QTEMP.TEMPROGERS2 (
SELECT UDWCASE, UDWTEX, UDWSEQ FROM SCUDWIN WHERE UDWWI#=53 AND (UDWSEQ=17 OR UDWSEQ=18) AND UDWCASE IN (SELECT CASE# FROM TEMPROGERS
));

Open in new window

0
 
tliottaCommented:
When I call the procedure from .NET I get an error:

I don't quite see where you show how it's called. (Or is the problem that you can't issue the CREATE PROCEDURE statement from .NET? It doesn't seem that you mean that you can't issue the CREATE PROCEDURE because you say it "returns no result set whatsoever" when you "call it" from iNav.)

Why do you think the stored proc itself is the problem rather than the CALL to the stored proc? What properties does your connection have? E.g., what naming convention is used? (iNav defaults to *SQL for me. Does .Net default the same?)

That's not to mean that the proc has no problems. It's just trying to clarify the environment setup that returns an error.

Tom
0
 
GeoffSuttonAuthor Commented:
The reason I believe it is the proc is that I am using exactly the same setups I have used in the past for calling other procedures which work perfectly.  Those procedures are also in the SCDATA schema and are called from .Net with the exact same conventions.  The only change, in fact, is the procedure name I am calling.

Unfortunately I am completely unfamiliar with iSeries SQL conventions and programming, as opposed to Windows and .NET code, so my belief is that the error lies where I am weakest :)

If the errors in the proc can be pointed out then the rest should fall into place.

I will reply tomorrow with the actual calling conventions should you still desire them.
Thanks,

Geoff
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Show the segment of .NET code where you are calling this sproc.   Looks like you aren't formatting the call properly.  If you are building it from a variable,then run it under debug and look at the variable contents.  Probably something simple there: string-building error.

To debug the sproc itself, it looks like you can just run the queries manually and verify that you get the expected results at each stage.

- Gary Patterson






0
 
GeoffSuttonAuthor Commented:
The call is somewhat split up:  Connection string is pulled from the Web.Config and passed in as a full connection.  Main function call is this:

DataTable dt = Get400Rogers(MCAIntranet.Data.get400Connection());
The function itself is fairly simple:
 
I know the connection string works as I use the same function and connection object for several other iSeries calls.

public System.Data.DataTable Get400Rogers(IBM.Data.DB2.iSeries.iDB2Connection connection)
    {
        System.Data.DataTable dt = new System.Data.DataTable();
        IBM.Data.DB2.iSeries.iDB2Command cmd = new IBM.Data.DB2.iSeries.iDB2Command("CALL SCDATA.ROGERSCHANGED(" + string.Format("{0:yyyyMMdd}",DateTime.Now.AddMonths(-6)) + ")", connection);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandTimeout = 1200000;
        connection.Open();
        dt.Load(cmd.ExecuteReader());
        connection.Close();
        return dt;
    }

Open in new window

0
 
tliottaCommented:
A part that seems incongruent is "When I try to call it from... iSeries Navigator it executes correctly...". If no error messages can be found through iNav, it's difficult to see how the stored proc has the relevant problem.

It might be easier to see if we could see the connection string for .Net. Although the connection string works with other stored procs, we don't know how to interpret the stored proc and the CALL in this question. For iNav, we can look at our own systems to see how to interpret a CALL.

We really need to see how you're connecting from .Net.

Tom
0
 
GeoffSuttonAuthor Commented:
Ok.  As soon as I can connect to my servers again I will upload the connection string for you.

Thanks,
Geoff
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
"CALL SCDATA.ROGERSCHANGED(" + string.Format("{0:yyyyMMdd}",DateTime.Now.AddMonths(-6)) + ")"

Take all of this, stick in into a string, and inspect the string before you call the SP.  Also, depending on how your connection is set up, you may need to use *SYS naming convention or change the connection properties. to use *SQL naming.

*SYS: "CALL SCDATA/ROGERSCHANGED(" + string.Format("{0:yyyyMMdd}",DateTime.Now.AddMonths(-6)) + ")"


*SQL: "CALL SCDATA.ROGERSCHANGED(" + string.Format("{0:yyyyMMdd}",DateTime.Now.AddMonths(-6)) + ")"



- Gary Patterson

Open in new window

0
 
GeoffSuttonAuthor Commented:
I will try that, thanks.
0
 
GeoffSuttonAuthor Commented:
Nothing has worked so far.  I have pulled some of the processing away from the iSeries, and am left with 1 slow query, which is relatively simple except that the table is huge.  I am performing a pivot on this table but that doesn't seem to affect the duration of the query.  

I have ported the query into a stored procedure
--  Generate SQL 

--  Version:                   	V5R4M0 060210 

--  Generated on:              	11/15/10 13:35:09 

--  Relational Database:       	MCAA4 

--  Standards Option:          	DB2 UDB iSeries 

  

SET PATH "QSYS","QSYS2","GSUTTON" ; 

  

CREATE PROCEDURE SCDATA.GETSCUDWINDATA ( 

	IN CASE DECIMAL(9, 0) ) 

	DYNAMIC RESULT SETS 1 

	LANGUAGE SQL 

	SPECIFIC SCDATA.GETSCUDWINDATA 

	NOT DETERMINISTIC 

	MODIFIES SQL DATA 

	CALLED ON NULL INPUT 

	SET OPTION  ALWBLK = *ALLREAD , 

	ALWCPYDTA = *OPTIMIZE , 

	COMMIT = *NONE , 

	DECRESULT = (31, 31, 00) , 

	DFTRDBCOL = *NONE , 

	DYNDFTCOL = *NO , 

	DYNUSRPRF = *USER , 

	SRTSEQ = *HEX   

	BEGIN 

--unused, holder for the SQLEXCEPTION Handler 

DECLARE MSG CHAR ( 50 ) ; 

--The string to hold the import command 

DECLARE OS400_CMD CHAR ( 256 ) ; 

--the string to hold the final SQL Statement 

DECLARE SQL_STMT CHAR ( 2500 ) ; 


--Cursor used for the result set 

DECLARE C1 CURSOR FOR S1 ; 

  

--configure the  exception handler - this is in place to allow a drop table statement even if the table doesn't exist 

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 

	SET MSG = 'No table' ; 

  

--create the SQL for the final result set query, concatenating the input variable cols to allow changing which columns are selected 

SET SQL_STMT = 'Select UDWCAS,
 

	min(Case When UDWTEX LIKE "FIRST_NAME%" 
Then TRIM(SUBSTR(UDWTEX,13,CHAR_LENGTH(UDWTEX)-12)) End) As FirstName,

            	min(Case When UDWTEX LIKE "LAST_BUSINESS%
" Then TRIM(SUBSTR(UDWTEX,22,CHAR_LENGTH(UDWTEX)-21)) End) As LastName

            FROM SCDATA.SCUDWIN WHERE UDWWI#=53 AND (UDWCAS= || CASE || )
 GROUP BY UDWCAS'
; 

  

--insert the prepared query into the actual cursor query 

PREPARE S1 FROM SQL_STMT ; 

--open the cursor to return the result set 

OPEN C1 ; 

  

END

Open in new window

but when I call it  
CALL SCDATA.GETSCUDWINDATA(101371618)

Open in new window

I get no results, although the iSeries navigator responds  
Statement ran successfully   (140 ms)

> CALL SCDATA.GETSCUDWINDATA(101371618)

Return Code = 0

Open in new window


Any ideas where I am going wrong?
0
 
GeoffSuttonAuthor Commented:
I don't believe that the query produces no results.  When I run the same query from .NET  
//            string q = @"Select UDWCAS,
//            min(Case When UDWTEX LIKE 'FIRST_NAME%' 
//            Then TRIM(SUBSTR(UDWTEX,13,CHAR_LENGTH(UDWTEX)-12)) End) As FirstName,
//            min(Case When UDWTEX LIKE 'LAST_BUSINESS%'
//            Then TRIM(SUBSTR(UDWTEX,22,CHAR_LENGTH(UDWTEX)-21)) End) As LastName
//            FROM SCDATA.SCUDWIN WHERE UDWWI#=53 AND (UDWCAS=" + debt + @")
//            GROUP BY UDWCAS";

Open in new window

it functions perfectly, just so slowly that everything times out.  This is why I tried moving it to a stored procedure.

Thanks,
Geoff
0
 
Gary PattersonConnect With a Mentor VP Technology / Senior Consultant Commented:
Well, your query appears to be completing successfully, and nothing is being returned.  You code structure looks OK - by that I mean that if the query returned results, it would generate a result set.  

Since there is no error, and no results are coming back, the simplest and most likely answer is: "query is producing no results".

You can easily test this theory by substituting a simple "no variable" query, like:

SELECT UDWCAS, 'Bob' as FirstName, 'Smith' as LastName from SCDATA.SCUDWIN

as I suggested earlier.  If it returns results properly, then you have your answer.

Whenever I have a similar problem, I take a hard look at the SQL statement that I THINK I'm building.  I often find that I'm not REALLY building the statement I think I am.  

I think that may be the case (no pun intended) here.

It really isn't quite the same query.  For example, you've renamed the variable "debt" to "case", and I bet the debt/case data types aren't the same either from .NET to SP.

"Case" is a bad name for a variable, in my opinion.  CASE is a valid SQL token, as you already know because you use it elsewhere in the query.  That's generally a bad idea.  Leads to potential confusion.

Rename it and see what happens.  

I think the parser is interpreting that "CASE" as a case statement, as opposed to a variable.  I suspect your SQL will suddenly start failing, since then you will be attempting to perform string concatenation using a DECIMAL value.

If you just run the SP under debug, and inspect the contents of the SQL_STMT variable, all will become clear.  If you don't know how to run an SQL SP under debug, this Redbook explains the process in detail.  Very handy for this sort of thing.

http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf

PS - You mentioned that you are doing this in an effort to improve query performance.   I don't see anything here that would result in an appreciable change in performance.  It looks to me like you're just running the same basic "prepared statement" query either way, and in both you are returning the same basic result set to the client application.  

Now, if it is -very- frequently run, you might see some improvement, especially of you are not using extended dynamic SQL for the .NET connection, but if the query is so long-running that you are experiencing timeouts (how long is that, by the way?), then just taking the same basic logic and shoving it into a SP isn't likely to produce much performance benefit.

You get the biggest gain from converting to a SP when you can eliminate a lot of data transfer by shifting processing to the host.  In this case, the AS/400 DBMS runs the same query, and returns the same amount of data either way.

Stored procedure calls that you do from the same connection are likely to suffer similarly (I'm assuming that you're just planning to call the SP from the .NET application in place of the current query, though).

An index over SCUDWIN by UDWI# and UDWCAS might also speed things up.

Hope this helps!

- Gary Patterson

0
 
GeoffSuttonAuthor Commented:
Thanks Gary,

I am afraid that I know NOTHING of iseries :) or anything involved.  So I suspect that i am way over my head.  Because of the time involved (SCUDWIN contains several million rows, if not hundreds of millions) I have decided to use the DTF abilities to query and concatenate what I want - That is working splendidly for me.  Then I can dump what I want for data into a SQL server database and perform the joins there, seemingly much more quickly.  Fortunately nightly updates are sufficient for this report so realtime data is not needed.

I am going to continue to work on this though, with the advice you've given me, to try and understand where I am wrong.  You mentioned using "Debug", but I suspect you imagined you were talking to somebody who had a clue about the iSeries :)  I will look for and see what I can find.  Thanks for the heads up that this will not save any time - I was thinking that the bottleneck was in the AS400 trying to debug the code I sent it, so using a procedure would speed that up.  I suspect that at some point I will have to give in and learn about the AS400 if they keep me working with data from it.

Again thanks,

Geoff
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Geoff,

There is a certainly a minimum level of technical ability required to successfully write and debug stored procedures on any platform.

The Stored Procedures Redbook explains AS/400 SP debugging procedures in detail, including the use of debugging utilities that allow a programmer to single-step through a stored procedure, examine the contents of variables. set breakpoints, and more.

If you've never used a debugger, you may find it a handy skill set to develop!

- Gary Patterson

0
 
GeoffSuttonAuthor Commented:
:)  I am almost entirely a Visual Studio programmer, so all my debugging skills come from there, or from using popup alerts in scripting.  I have attempted to broach the redbooks previously, but did not spend much time with them, being busy with other projects.  Maybe someday I will have the opportunity again.

Thanks,
Geoff
0
All Courses

From novice to tech pro — start learning today.