Solved

AS/400 Stored Procedure

Posted on 2010-08-12
31
827 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
  • 15
  • 10
  • 5
  • +1
31 Comments
 
LVL 45

Expert Comment

by:Kdo
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
 
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:Kdo
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:Kdo
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:Kdo
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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:Kdo
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:Kdo
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:Kdo
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:Kdo
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:Kdo
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:Kdo
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Just about everyone has an old PC laying around.  Ask anyone in the IT industry, whether they are a professional or play in it as a hobby.  From outdated Desktops to cheap "throwaway" laptops, they are all around and not as hard to "fix up" as you m…
In this article we will discuss all things related to StageFright bug, the most vulnerable bug of android devices.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

707 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

17 Experts available now in Live!

Get 1:1 Help Now