Solved

AS400 Stored Procedure

Posted on 2010-11-08
18
2,417 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:GeoffSutton
  • 9
  • 5
  • 2
  • +1
18 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 34087386

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

HTH,
DaveSlash
0
 
LVL 10

Author Comment

by:GeoffSutton
ID: 34087450
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
 
LVL 18

Expert Comment

by:daveslash
ID: 34087574

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
 
LVL 27

Expert Comment

by:tliotta
ID: 34088766
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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 34089147
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
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 34089340
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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 34092907
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
 
LVL 27

Expert Comment

by:tliotta
ID: 34105004
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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 34105019
Ok.  As soon as I can connect to my servers again I will upload the connection string for you.

Thanks,
Geoff
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Expert Comment

by:Gary Patterson
ID: 34105291
"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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 34105429
I will try that, thanks.
0
 
LVL 10

Author Comment

by:GeoffSutton
ID: 34140333
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
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
ID: 34140483
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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 34145686
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
 
LVL 34

Assisted Solution

by:Gary Patterson
Gary Patterson earned 500 total points
ID: 34147558
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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 34150285
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
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 34150527
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
 
LVL 10

Author Comment

by:GeoffSutton
ID: 34150548
:)  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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now