Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 855
  • Last Modified:

AS/400 Stored Procedure

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
RadhaKrishnaKiJaya
Asked:
RadhaKrishnaKiJaya
  • 15
  • 10
  • 5
  • +1
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
tliottaCommented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
tliottaCommented:
Please show us the database definition of column ItemType.

Tom
0
 
RadhaKrishnaKiJayaAuthor Commented:
It is CHARACTER 7,  Thanks.
0
 
RadhaKrishnaKiJayaAuthor Commented:
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
 
tliottaCommented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
tliotta, No good.  Do you have a simple working Stored Procedure with you?  Thanks.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
daveslaterCommented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  What value are you giving to pItemType and how are you setting it?


Kent
0
 
tliottaCommented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
Just making sure, but if you run that same INSERT statement from a client application (command line interface or GUI), it works?


Kent
0
 
RadhaKrishnaKiJayaAuthor Commented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
Kent, Yes, it works.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
Kent, Still same thing, not working.  It compiles ane executes fine, but no recordes inserted. Thank you.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
tliottaCommented:
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
 
RadhaKrishnaKiJayaAuthor Commented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 15
  • 10
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now