Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

AS400 Stored Procedure

Posted on 2010-11-08
18
Medium Priority
?
2,610 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
  • +1
18 Comments
 
LVL 18

Expert Comment

by:Dave Ford
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:Dave Ford
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 35

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

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 35

Accepted Solution

by:
Gary Patterson earned 2000 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 35

Assisted Solution

by:Gary Patterson
Gary Patterson earned 2000 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 35

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

661 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