473,847 Members | 1,542 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Recursive programming in DB2 UDB v8.2 Stored Procedure

Hi,

I work primarily on Oracle databases. I am trying to convert a
recursive stored procedure written in Oracle to DB2.

Does DB2 UDB v8.2 (Windows/AIX) supports recursive stored procedures ??
After some research, I found out that to call recursively in DB2, the
stored procedure should be CALLed using dynamic SQL. I am not sure
whether it is the right way. Am I missing something ?? Please let me
know...
Here is the DB2 code... I have been getting the following compilation
error:

SQL0440N No authorized routine named "||" of type "FUNCTION" having
compatible arguments was found LINE NUMBER=129 SQLSTATE = 42884
CREATE PROCEDURE EXPLODE_BOM_PRC ( IN p_MasterProduct Name
VARCHAR(100)
,IN p_MasterProduct Rev
VARCHAR(10)
,IN p_ComponentLeve l INT )
LANGUAGE SQL
BEGIN
--
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE n_Sequence INTEGER;
DECLARE n_ErrLocator INTEGER;
DECLARE n_ComponentLeve l INTEGER;
--
DECLARE dbl_ComponentQt y DOUBLE;
--
DECLARE v_ComponentLeve l VARCHAR(50);
DECLARE v_Material VARCHAR(30);
DECLARE v_ComponentRev VARCHAR(15);
DECLARE v_ComponentQty VARCHAR(50);
DECLARE v_ComponentUOM VARCHAR(50);
DECLARE v_ErrMsg VARCHAR(512);
DECLARE v_LevelString VARCHAR(100);
DECLARE v_SQLStmt VARCHAR(256);
--
DECLARE BOM_Cur CURSOR WITH HOLD FOR
-- SELECT PRD1.ProductRev ision AS ProductRev
-- ,PRDBASE1.Produ ctName AS ProductName
SELECT PRDBASE2.Produc tName AS ComponentName
,PRD2.ProductRe vision AS ComponentRevisi on
,DECIMAL(PMLI1. QtyRequired) AS ComponentQty
,UOM.UOMName AS ComponentUOM
-- ,PRD1.BOMBaseId AS BOMBaseId1
-- ,PRD2.BOMBaseId AS BOMBaseId2
-- ,PMLI1.ProductB aseId AS PMLIProdBaseId1
-- ,PMLI1.ProductI d AS PMLIProdId1
-- ,PRD1.ProductId AS PRD1ProdId
-- ,PRD2.ProductId AS PRD2ProdId
-- ,PRDBASE2.RevOf RcdId AS PRDBS2RevOfRcdI d
-- ,BOMBASE.RevOfR cdId AS BOMBASERevOfRcd Id
FROM PRODUCTBASE PRDBASE1 INNER JOIN PRODUCT PRD1 ON
PRDBASE1.Produc tBaseId = PRD1.ProductBas eId
FULL OUTER JOIN BOMBASE ON
PRD1.BOMBaseId = BOMBASE.BOMBase Id
FULL OUTER JOIN BOM ON PRD1.BOMId =
BOM.BOMId OR BOMBASE.RevOfRc dId = BOM.BOMId
FULL OUTER JOIN
ProductMaterial ListItem PMLI1 ON BOM.BOMId = PMLI1.BOMId
FULL OUTER JOIN ProductBase PRDBASE2
ON PMLI1.ProductBa seId = PRDBASE2.Produc tBaseId
FULL OUTER JOIN Product PRD2 ON
PMLI1.ProductId = PRD2.ProductId OR PRDBASE2.RevOfR cdId =
PRD2.ProductId
FULL OUTER JOIN UOM ON UOM.UOMId =
PMLI1.UOMId
WHERE PRDBASE1.Produc tName = p_MasterProduct Name
AND PRD1.ProductRev ision = p_MasterProduct Rev;
--
BEGIN
--
SET n_ComponentLeve l = p_ComponentLeve l;
--
IF ( n_ComponentLeve l = 0 ) THEN
--
-- Delete the previous run BOM Explosion data from the
temporary table
--
SET n_ErrLocator = 5;
--
DELETE FROM EXPLODEDBOM;
--
-- Insert the Master Product at the top of the BOM Tree.
--
BEGIN
--
SET n_ErrLocator = 10;
--
SET v_ComponentLeve l = CHAR(p_Componen tLevel);
--
INSERT INTO EXPLODEDBOM ( ComponentLevel
,Material
,ComponentRev
,ComponentQty
,ComponentUOM )
VALUES( v_ComponentLeve l
,p_MasterProduc tName
,'1'
,''
,'' );
--
END;
--
-- Initialize component level.
--
SET n_ComponentLeve l = 1;
--
ELSE
--
SET n_ComponentLeve l = n_ComponentLeve l + 1;
--
END IF;
--
-- Explode this Master Product and also its children.
--
OPEN BOM_Cur;
--
FETCH FROM BOM_Cur INTO v_Material
,v_ComponentRev
,v_ComponentQty
,v_ComponentUOM ;
--
WHILE ( SQLCODE = 0 ) DO
--
IF ( COALESCE(v_Mate rial,'XXX') <> 'XXX' ) THEN
--
SET n_Sequence = COALESCE(n_Sequ ence,0) + 1;
--
BEGIN
--
SET n_ErrLocator = 15;
--
-- SET v_ComponentQty = CHAR(dbl_Compon entQty);
SET v_ComponentLeve l = CHAR(n_Componen tLevel);
SET v_LevelString = CONCAT(REPEAT('
',n_ComponentLe vel * 2),v_ComponentL evel);
--
INSERT INTO EXPLODEDBOM ( ComponentLevel
,Material
,ComponentRev
,ComponentQty
,ComponentUOM )
VALUES( v_LevelString
,v_Material
,v_ComponentRev
,v_ComponentQty
,v_ComponentUOM );
--
END;
--
SET n_ErrLocator = 20;
--
SET v_SQLStmt = 'CALL EXPLODE_BOM_PRC
('''||v_Materia l||''','''||v_C omponentRev||'' ','''||n_Compon entLevel||')';
EXECUTE IMMEDIATE v_SQLStmt;
--
END IF;
--
-- Get the next row.
--
FETCH FROM BOM_Cur INTO v_Material
,v_ComponentRev
,v_ComponentQty
,v_ComponentUOM ;
--
END WHILE;
--
CLOSE BOM_Cur;
--
COMMIT;
--
END;
END
Thanks in advance,
Purush

Nov 29 '05 #1
5 5042
pu********@gmai l.com wrote:
Hi,

I work primarily on Oracle databases. I am trying to convert a
recursive stored procedure written in Oracle to DB2.

Does DB2 UDB v8.2 (Windows/AIX) supports recursive stored procedures ??
After some research, I found out that to call recursively in DB2, the
stored procedure should be CALLed using dynamic SQL. I am not sure
whether it is the right way. Am I missing something ?? Please let me
know...
Here is the DB2 code... I have been getting the following compilation
error:

SQL0440N No authorized routine named "||" of type "FUNCTION" having
compatible arguments was found LINE NUMBER=129 SQLSTATE = 42884
CREATE PROCEDURE EXPLODE_BOM_PRC ( IN p_MasterProduct Name
VARCHAR(100)
,IN p_MasterProduct Rev
VARCHAR(10)
,IN p_ComponentLeve l INT )
LANGUAGE SQL
BEGIN
--
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE n_Sequence INTEGER;
DECLARE n_ErrLocator INTEGER;
DECLARE n_ComponentLeve l INTEGER;
--
DECLARE dbl_ComponentQt y DOUBLE;
--
DECLARE v_ComponentLeve l VARCHAR(50);
DECLARE v_Material VARCHAR(30);
DECLARE v_ComponentRev VARCHAR(15);
DECLARE v_ComponentQty VARCHAR(50);
DECLARE v_ComponentUOM VARCHAR(50);
DECLARE v_ErrMsg VARCHAR(512);
DECLARE v_LevelString VARCHAR(100);
DECLARE v_SQLStmt VARCHAR(256);
--
DECLARE BOM_Cur CURSOR WITH HOLD FOR
-- SELECT PRD1.ProductRev ision AS ProductRev
-- ,PRDBASE1.Produ ctName AS ProductName
SELECT PRDBASE2.Produc tName AS ComponentName
,PRD2.ProductRe vision AS ComponentRevisi on
,DECIMAL(PMLI1. QtyRequired) AS ComponentQty
,UOM.UOMName AS ComponentUOM
-- ,PRD1.BOMBaseId AS BOMBaseId1
-- ,PRD2.BOMBaseId AS BOMBaseId2
-- ,PMLI1.ProductB aseId AS PMLIProdBaseId1
-- ,PMLI1.ProductI d AS PMLIProdId1
-- ,PRD1.ProductId AS PRD1ProdId
-- ,PRD2.ProductId AS PRD2ProdId
-- ,PRDBASE2.RevOf RcdId AS PRDBS2RevOfRcdI d
-- ,BOMBASE.RevOfR cdId AS BOMBASERevOfRcd Id
FROM PRODUCTBASE PRDBASE1 INNER JOIN PRODUCT PRD1 ON
PRDBASE1.Produc tBaseId = PRD1.ProductBas eId
FULL OUTER JOIN BOMBASE ON
PRD1.BOMBaseId = BOMBASE.BOMBase Id
FULL OUTER JOIN BOM ON PRD1.BOMId =
BOM.BOMId OR BOMBASE.RevOfRc dId = BOM.BOMId
FULL OUTER JOIN
ProductMaterial ListItem PMLI1 ON BOM.BOMId = PMLI1.BOMId
FULL OUTER JOIN ProductBase PRDBASE2
ON PMLI1.ProductBa seId = PRDBASE2.Produc tBaseId
FULL OUTER JOIN Product PRD2 ON
PMLI1.ProductId = PRD2.ProductId OR PRDBASE2.RevOfR cdId =
PRD2.ProductId
FULL OUTER JOIN UOM ON UOM.UOMId =
PMLI1.UOMId
WHERE PRDBASE1.Produc tName = p_MasterProduct Name
AND PRD1.ProductRev ision = p_MasterProduct Rev;
--
BEGIN
--
SET n_ComponentLeve l = p_ComponentLeve l;
--
IF ( n_ComponentLeve l = 0 ) THEN
--
-- Delete the previous run BOM Explosion data from the
temporary table
--
SET n_ErrLocator = 5;
--
DELETE FROM EXPLODEDBOM;
--
-- Insert the Master Product at the top of the BOM Tree.
--
BEGIN
--
SET n_ErrLocator = 10;
--
SET v_ComponentLeve l = CHAR(p_Componen tLevel);
--
INSERT INTO EXPLODEDBOM ( ComponentLevel
,Material
,ComponentRev
,ComponentQty
,ComponentUOM )
VALUES( v_ComponentLeve l
,p_MasterProduc tName
,'1'
,''
,'' );
--
END;
--
-- Initialize component level.
--
SET n_ComponentLeve l = 1;
--
ELSE
--
SET n_ComponentLeve l = n_ComponentLeve l + 1;
--
END IF;
--
-- Explode this Master Product and also its children.
--
OPEN BOM_Cur;
--
FETCH FROM BOM_Cur INTO v_Material
,v_ComponentRev
,v_ComponentQty
,v_ComponentUOM ;
--
WHILE ( SQLCODE = 0 ) DO
--
IF ( COALESCE(v_Mate rial,'XXX') <> 'XXX' ) THEN
--
SET n_Sequence = COALESCE(n_Sequ ence,0) + 1;
--
BEGIN
--
SET n_ErrLocator = 15;
--
-- SET v_ComponentQty = CHAR(dbl_Compon entQty);
SET v_ComponentLeve l = CHAR(n_Componen tLevel);
SET v_LevelString = CONCAT(REPEAT('
',n_ComponentLe vel * 2),v_ComponentL evel);
--
INSERT INTO EXPLODEDBOM ( ComponentLevel
,Material
,ComponentRev
,ComponentQty
,ComponentUOM )
VALUES( v_LevelString
,v_Material
,v_ComponentRev
,v_ComponentQty
,v_ComponentUOM );
--
END;
--
SET n_ErrLocator = 20;
--
SET v_SQLStmt = 'CALL EXPLODE_BOM_PRC
('''||v_Materia l||''','''||v_C omponentRev||'' ','''||n_Compon entLevel||')';
EXECUTE IMMEDIATE v_SQLStmt;
--
END IF;
--
-- Get the next row.
--
FETCH FROM BOM_Cur INTO v_Material
,v_ComponentRev
,v_ComponentQty
,v_ComponentUOM ;
--
END WHILE;
--
CLOSE BOM_Cur;
--
COMMIT;
--
END;
END

You problem is that component_level is an INTEGER.
You cannot concat integers.
Bur rather than fixing that (and then learning that you are now passing
a component level as a string (since you added quotes ;-) I propose teh
usage of parameter markers:

SET v_SQLStmt = 'CALL EXPLODE_BOM_PRC (?,?)';
EXECUTE IMMEDIATE v_SQLStmt
USING v_ComponentRev, n_ComponentLeve l;

Now... having said that..
Do you use a recursive procedure for this BOM in Oracle as well?
Both Oracle and DB2 for LUW support "native" recursion.
In Oracle you use CONNECT BY, in DB2 recursive common table expressions.
Check out this article for starters (it kind of explains both Oracle and
DB2):
http://www-128.ibm.com/developerwork...dm-0510rielau/

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 29 '05 #2
Thanx for the link Serge. And there's a picture of you too. :)

B.

Nov 29 '05 #3
You might want to look at do a Bill of Materials with a nested set
model so that all your work can be done with single, pure SQL
statements instead of slow procedural code. Get a copy of TREES &
HIERARCHIES IN SQL from Amazon.com. There is a section on BOM
problems.

Nov 30 '05 #4
Serge,

Thanks for the suggestion to the above code. It works after some
modification. I had to keep it this way instead of using 'WITH' clause
because of a call being made by an application. This application will
call a function with the same name irrespective of any DB flavour.(DB2,
SQL Server, Oracle) apart from other dependencies. We will change it to
WITH clause for DB2 and CONNECT BY once all the dependencies are
removed!!!!

Thanks,
Purush

Dec 8 '05 #5
pu********@gmai l.com wrote:
Serge,

Thanks for the suggestion to the above code. It works after some
modification. I had to keep it this way instead of using 'WITH' clause
because of a call being made by an application. This application will
call a function with the same name irrespective of any DB flavour.(DB2,
SQL Server, Oracle) apart from other dependencies. We will change it to
WITH clause for DB2 and CONNECT BY once all the dependencies are
removed!!!!

Once you move to SQL Server 2005 you can share. it supports recursive WITH.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Dec 8 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
6167
by: Robert Song | last post by:
Hi all I am implementing a stored procedure which needs to recursively call itself until specific condition is reached, Could anyone give some advice about that? Thanks a lot Robert Song
4
9277
by: Rodusa | last post by:
I am having problem to apply updates into this function below. I tried using cursor for updates, etc. but no success. Sql server keeps telling me that I cannot execute insert or update from inside a function and it gives me an option that I could write an extended stored procedure, but I don't have a clue of how to do it. To quickly fix the problem the only solution left in my case is to convert this recursive function into one recursive...
4
1359
by: Maciek | last post by:
I've got this question regarding programming and design practices. I'm designing Newsletter module for my WebApp and I'm greenhorn in programming. There's a stored procedure which adds a subscriber to a DB. It outputs subscriberID (uniqueidetifier) if it succeeds to add them to the database and return value of "0". When the subscriber already exists but hasn't activated/confirmed their account it returns null for subscriberID and return...
4
12979
by: Henrik Juul | last post by:
How do I call my Stored Procedure recursively: CREATE PROCEDURE dbo.GetParentIONode ( @IONodeID int, @FullNodeAddress char(100) OUTPUT ) AS BEGIN
3
3198
by: oregondba | last post by:
I have a database I have inherited (new job). I am trying to baseline the code and have done a DB2Look to get the code out of an existing db. The DB2Look produced a SP with the following form: Create SP1 (various code) call sp1()
0
1963
by: champ1979 | last post by:
I wrote an algorithm to get all the relatives of a person in a family tree. I'm basically getting all the users from the DB and am doing the recursive logic in code, so that there is only 1 call made to the DB. However, I am trying to do the same thing within a stored procedure in SQL using recursive CTEs (I think the performance might be better) but I'm finding it really tough to craft the CTE. I would really appreciate if someone could...
3
5010
by: jzdoh | last post by:
I am trying to write a stored procedure that could create a table called tblManagerHierarchy. It is a table that contains recursive data. The data is coming from tblEmployee where it contains that EmpNum and ManagerEmpNum. tblEmployee (EmpNum, ManagerEmpNum) The tblManagerHierarchy should contain Manager's EmpNum plus direct and indirect subordinates' EmpNum. Basically it should contain everybody who reports to a Manager directly and...
3
2387
by: fabiomoggi | last post by:
Hello Guys, I am developing a web application to manage Active Directory resources, and one of my tasks is to map Organizational Units hierarchy into a SQL Server database. Let's suppose that I have the following OU hierarchy in my Active Direcoty: 1. NewYork 1.1 HR_Department 1.1.1 Computers 1.1.2 Users 1.2 SALES_Department 1.1.1 Computers
1
3907
by: bipinskulkarni | last post by:
How to write recursive stored procedure in mysql?
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10658
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10347
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7889
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7062
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5915
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4133
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3171
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.