Start Free Trial

RadhaKrishnaKiJaya

asked on

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

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

Member_2_276102

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

RadhaKrishnaKiJaya

ASKER

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.

Member_2_276102

Please show us the database definition of column ItemType.

Tom

RadhaKrishnaKiJaya

ASKER

It is CHARACTER 7, Thanks.

RadhaKrishnaKiJaya

ASKER

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.

Member_2_276102

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

RadhaKrishnaKiJaya

ASKER

tliotta, No good. Do you have a simple working Stored Procedure with you? Thanks.

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

RadhaKrishnaKiJaya

ASKER

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.

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

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

RadhaKrishnaKiJaya

ASKER

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.

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

RadhaKrishnaKiJaya

ASKER

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

RadhaKrishnaKiJaya

ASKER

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 ;

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

RadhaKrishnaKiJaya

ASKER

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.

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.

RadhaKrishnaKiJaya

ASKER

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.

Ok. What value are you giving to pItemType and how are you setting it?

Kent

Member_2_276102

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

RadhaKrishnaKiJaya

ASKER

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'));

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

Kent

RadhaKrishnaKiJaya

ASKER

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'));

RadhaKrishnaKiJaya

ASKER

Kent, Yes, it works.

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

RadhaKrishnaKiJaya

ASKER

Kent, Still same thing, not working. It compiles ane executes fine, but no recordes inserted. Thank you.

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

Member_2_276102

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

ASKER CERTIFIED SOLUTION

RadhaKrishnaKiJaya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial