Solved

AS/400 Stored Procedure

Posted on 2010-08-12
31
842 Views
Last Modified: 2013-12-06
Hi Experts,
I am trying to write a stored procedure in AS/400 which shuld insert records in TableOut, based on some condition from Table1 and Table2.  I am listing source code below for the procedure. The procedure TestProc is compiling fine and seems like it executes, but doesn't insert the records as it is intended.  I see no errors in the compilition spool file.  When I copy and paste the insert statement in SQL Statement command prompt, it works just fine.  Any idea.  Please let me know if you have any question.  Thank you in advance for trying.

CREATE PROCEDURE TESTPROC (        
IN pItemType VARCHAR(10))            
                                   
LANGUAGE SQL                      
SPECIFIC DEB100PR          
NOT DETERMINISTIC                  
MODIFIES SQL DATA                  
CALLED ON NULL INPUT              
SET OPTION DBGVIEW   = *SOURCE,    
            COMMIT    = *NONE  ,    
            DYNUSRPRF = *OWNER ,    
            OUTPUT    = *PRINT ,    
            SQLPATH   = *LIBL  ,    
            USRPRF    = *OWNER      
BEGIN                              
                                   
DECLARE vItemType VARCHAR (10);

SET vItemType = pItemType;                                                    
                                                                       
INSERT INTO MyLib/TableOut
(SELECT ItemNum1, ItemName1 FROM MyLib/Table1 WHERE ItemNum1 IN
(SELECT ItemNum2  FROM MyLib/Table2 WHERE ItemType = pItemType));                                            
                                                                       
END;                                                                  
                                                                       
-- SET OBJECT AUTHORITY                                                
                                                                       
REVOKE ALL ON SPECIFIC PROCEDURE DEB100PR FROM PUBLIC ;
Tags:
AS/400 Stored Procedure
0
Comment
Question by:RadhaKrishnaKiJaya
[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
  • 15
  • 10
  • 5
  • +1
31 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33425752

Hi RadhaKrishnaKiJaya,

SQL Syntax is just a bit different on the AS/400 than others, but I think that the INSERT statement could use a small adjustment.

Try this:

  INSERT INTO MyLib/TableOut (NumColumn, NameColumn)
  SELECT ItemNum1, ItemName1
  FROM MyLib/Table1 WHERE ItemNum1 IN
  (
    SELECT ItemNum2  FROM MyLib/Table2 WHERE ItemType = pItemType
  );


Kent
0
 
LVL 27

Expert Comment

by:tliotta
ID: 33426120
Both INSERT syntaxes should work, with a minor change -- the outer parentheses probably should be removed from the main sub-select:

INSERT INTO MyLib/TableOut
 SELECT ItemNum1, ItemName1 FROM MyLib/Table1 WHERE ItemNum1 IN
(SELECT ItemNum2  FROM MyLib/Table2 WHERE ItemType = pItemType) ;
I'm a little surprised that no error/warning was issued either during compile of the stored proc or in interactive SQL. I think it would run, but a message should have been issued. It definitely would have been issued had the statement been prompted -- and the parentheses would have been removed automatically.

Further, there is no way that the INSERT from the stored proc would run in interactive SQL without at least some modification. The pItemType variable simply wouldn't be allowed. Therefore, a change had to have been made. We would need to see the exact statement that worked before being certain of anything.

Tom
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33431083
Hi Kdo and tliotta,  I tried both of your commands.  Unfortunately they did not work.  Any other thoughts.  Do you have an example of simple stored procedure, so that I can try it?  Thank you very much for your time.
0
WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

 
LVL 27

Expert Comment

by:tliotta
ID: 33433129
Please show us the database definition of column ItemType.

Tom
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33433704
It is CHARACTER 7,  Thanks.
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33434045
tliotta, Kdo, If you could provide me a small working example, that would be great.  FYI, when I run the sql statememnt alone, removing rest of the lines, the statement executes just fine.  Thank you for your time.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 33434196
It is CHARACTER 7,  Thanks.

Since it is a different data type than either vItemType or pItemType, you might try changing those also to be CHARACTER 7. Be aware that a CHARACTER 7 may have trailing blanks that can be considered as part of the value. If you are unwilling to change those, then you should CAST their values to CHARACTER before making the comparison.

But also be aware that a remote CALL to a stored proc that has a CHARACTER input parameter should have a CHARACTER (not VARCHAR) value sent across the network.

Tom
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33434286
tliotta, No good.  Do you have a simple working Stored Procedure with you?  Thanks.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33436138
Hi RadhaKrishna,

Let's start with the basics.  If we can produce an INSERT statement that does what you want, wrapping it in a stored procedure is pretty easy.

If you extract the INSERT statement from you sample above and run it what happens?  (Sample code is below.)



Kent

--
--  Original
--
INSERT INTO MyLib/TableOut
(SELECT ItemNum1, ItemName1 FROM MyLib/Table1 WHERE ItemNum1 IN
(SELECT ItemNum2  FROM MyLib/Table2 WHERE ItemType = pItemType));

--
--  Run this
--

SELECT ItemNum1, ItemName1 
FROM MyLib/Table1 
WHERE ItemNum1 IN
(
  SELECT ItemNum2  
  FROM MyLib/Table2 
  WHERE ItemType = pItemType
);

Open in new window

0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33447428
Kent, It is not working.  Even I removed the where condition and just ran the simple insert statement, still did not work.  I was able to execeute the same statement in SQL Statement command line successfully.  Any other thought?  Thank you for your time Kent.  
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33447528
Ok.  The INSERT statement works from a command line client, but not when placed in a stored procedure.

Several things come to mind.

-- Has the stored procedure been created in the correct schema?

-- Is the correct stored procedure being called?
    DB2 allows overloading, so you may be calling a function in another schema.

-- pItemType and vItemType are VARCHAR(10).  Is that large enough?
    If the ItemType value is longer than 10 characters, you'll never get a match.

-- What value are you passing to the stored procedure?
    Items are case sensitive.  You may have a simple upper/lower case issue.


Kent
0
 
LVL 14

Expert Comment

by:daveslater
ID: 33447734
Hi
can you try
INSERT INTO MyLib/TableOut
(SELECT ItemNum1, ItemName1 FROM MyLib/Table1 WHERE exists(SELECT ItemNum2  FROM MyLib/Table2 WHERE ItemNum1 = ItemNum2 and ItemType = pItemType));

Dave

0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33447803
Kdo,

-- Has the stored procedure been created in the correct schema?
Ans: How do I verify that?

-- Is the correct stored procedure being called?
    DB2 allows overloading, so you may be calling a function in another schema.
Ans: I don't know what you mean.  But I use RUNSQLSTM to compile and run the SP.

-- pItemType and vItemType are VARCHAR(10).  Is that large enough?
    If the ItemType value is longer than 10 characters, you'll never get a match.
Ans:  Yes, they are large enough.

-- What value are you passing to the stored procedure?
    Items are case sensitive.  You may have a simple upper/lower case issue.

Ans: Currently I am not passing anything.  I have a hardcoded value, if that part work, I will pass some value.

Do you have a example of small SP, so that I can try it here.  Thanks.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33447833
Hi RadhaKrishna...

I don't believe that a sample procedure will help here.  You seem to have a compiled SP with a specific problem.

Can you post the procedure?



Kent
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33448046
daveslater,
I have tried even simpler INSERT statement as below, It did not work.  If you can give me a example of simple stored procedure, I can try it here.  I think I am making some basic mistake.  Thank you for your time.

INSERT INTO MyLib/TableOut SELECT ItemNum1, ItemName1 FROM MyLib/Table1
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33448212
Kent, There you go.  Thanks.

CREATE PROCEDURE TESTPROC (        
IN pItemType VARCHAR(10))            
                                   
LANGUAGE SQL                      
SPECIFIC DEB100PR          
NOT DETERMINISTIC                  
MODIFIES SQL DATA                  
CALLED ON NULL INPUT              
SET OPTION DBGVIEW   = *SOURCE,    
            COMMIT    = *NONE  ,    
            DYNUSRPRF = *OWNER ,    
            OUTPUT    = *PRINT ,    
            SQLPATH   = *LIBL  ,    
            USRPRF    = *OWNER      
BEGIN                              
                                   
DECLARE vItemType VARCHAR (10);

SET vItemType = pItemType;                                                    
                                                                       
INSERT INTO MyLib/TableOut
(SELECT ItemNum1, ItemName1 FROM MyLib/Table1 WHERE ItemNum1 IN
(SELECT ItemNum2  FROM MyLib/Table2 WHERE ItemType = pItemType));                                            
                                                                       
END;                                                                  
                                                                       
-- SET OBJECT AUTHORITY                                                
                                                                       
REVOKE ALL ON SPECIFIC PROCEDURE DEB100PR FROM PUBLIC ;
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33448257
Hi RadhaKrishna…

Do you get an error when you run the simple INSERT that you posted?

  INSERT INTO MyLib/TableOut SELECT ItemNum1, ItemName1 FROM MyLib/Table1


Does the stored procedure compile or does it generate an error when you try and create it?


Kent
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33448302
Kent, The SP comiles with no errors.  Also, it executes.  But I don't see any records in table.  When I execute the same statememt in SQL command line, it works just fine.  Thank you.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33448376
Ok.  I'm officially confused....

>> Ans: Currently I am not passing anything.  I have a hardcoded value, if that part work, I will pass some value.

You must be passing a value to the SP, even if the value is spaces.

0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33448472
Sorry to confuse you.  Lets remove the parameter (currently I don't have any in my code). Try now, I get the same result (still not working).  Thank you for your help Kent.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33448558
Ok.  What value are you giving to pItemType and how are you setting it?


Kent
0
 
LVL 27

Expert Comment

by:tliotta
ID: 33448665
I wrote:

Since it is a different data type than either vItemType or pItemType, you might try changing those also to be CHARACTER 7.

You replied:

No good.

I'm not sure what that means. Does it mean that you received an error? If so, what was it? Is the only problem that the UPDATE doesn't change any rows?

If the CALL is successful and no errors show up, and the only problem is that you don't see any updated rows, then commitment control is the most likely next cause.

Regardless, you later posted the stored proc code again, and it still had your variables as VARCHAR. Even if you can resolve commitment control issues, you still need to match the data types properly if you want this to work reliably. A VARCHAR(10) is inappropriate for matching a CHARACTER(7). You need to change the stored proc, either by changing the data types or by CASTing to CHARACTER(7).

BTW, why do you have vItemType? It's never used anywhere.

Tom

0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33448676
I have already removed the following lines.  

IN pItemType VARCHAR(10))
DECLARE vItemType VARCHAR (10);
SET vItemType = pItemType;          

I am not passing any parameter any more.  Here is my statement as below.  Still doesn't work.

INSERT INTO MyLib/TableOut
(SELECT ItemNum1, ItemName1 FROM MyLib/Table1 WHERE ItemNum1 IN
(SELECT ItemNum2  FROM MyLib/Table2 WHERE ItemType = '1001'));
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33448718
Just making sure, but if you run that same INSERT statement from a client application (command line interface or GUI), it works?


Kent
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33448759
Tom,
"No good" meaning still it doesn't work.  It compiles fine with no errors.  But the update doesn't insert any rows. If you think commitment control is most likely cause, what should I do?  Please let me know.

FYI, just for testing, I removed all the parameters, pItemType etc.  Now my query statement looks like this as below.  Still doen't work. Thank you.

INSERT INTO MyLib/TableOut
(SELECT ItemNum1, ItemName1 FROM MyLib/Table1 WHERE ItemNum1 IN
(SELECT ItemNum2  FROM MyLib/Table2 WHERE ItemType = '1001'));
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33448772
Kent, Yes, it works.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33448849
Ok.

Try putting a COMMIT statement after the INSERT in the procedure.

Also, check to make sure that the insert doesn't violate any foreign key or duplicate constraints.  (It might be that running the INSERT statement added the data and that the SP fails because the data is already there.)


Kent
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 33448930
Kent, Still same thing, not working.  It compiles ane executes fine, but no recordes inserted. Thank you.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33449025
To say that this is bizarre,is probably an understatement....

Tom -- you're better with the AS/400 flavor of DB2 than am I.  What are we missing?  This seems so trivial....


Kent
0
 
LVL 27

Expert Comment

by:tliotta
ID: 33457902
When I execute the same statememt in SQL command line, it works just fine.

What is your "SQL command line"? Can you copy/paste the window showing us that it actually worked?

The working INSERT statement that you showed last should still result in an error and/or warning because of the extra set of parentheses. Also, the semi-colon terminator should be flagged as a warning at the least in a "SQL command line".

So, I'm not at all clear on what you do when you say it "works", especially with no error messages.

How are you connecting to the stored proc? What client are you using?

What is your system OS version? What is your DB/2 group PTF level?

Tom
0
 

Accepted Solution

by:
RadhaKrishnaKiJaya earned 0 total points
ID: 33561547
As I have not received any proper reply for this question, I am going to close this case.  Thank you all for trying to help me.
0

Featured Post

WordPress Tutorial 4: Recommended Plugins

Now that you have WordPress installed, understand the interface, and know how to install new parts, let’s take a look at our recommended plugins.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Suggested Courses

635 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