Recursive programming in DB2 UDB v8.2 Stored Procedure

[purushneel]

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_MasterProductName
VARCHAR(100)
,IN p_MasterProductRev
VARCHAR(10)
,IN p_ComponentLevel INT )
LANGUAGE SQL
BEGIN
--
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE n_Sequence INTEGER;
DECLARE n_ErrLocator INTEGER;
DECLARE n_ComponentLevel INTEGER;
--
DECLARE dbl_ComponentQty DOUBLE;
--
DECLARE v_ComponentLevel 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.ProductRevision AS ProductRev
-- ,PRDBASE1.ProductName AS ProductName
SELECT PRDBASE2.ProductName AS ComponentName
,PRD2.ProductRevision AS ComponentRevision
,DECIMAL(PMLI1.QtyRequired) AS ComponentQty
,UOM.UOMName AS ComponentUOM
-- ,PRD1.BOMBaseId AS BOMBaseId1
-- ,PRD2.BOMBaseId AS BOMBaseId2
-- ,PMLI1.ProductBaseId AS PMLIProdBaseId1
-- ,PMLI1.ProductId AS PMLIProdId1
-- ,PRD1.ProductId AS PRD1ProdId
-- ,PRD2.ProductId AS PRD2ProdId
-- ,PRDBASE2.RevOfRcdId AS PRDBS2RevOfRcdId
-- ,BOMBASE.RevOfRcdId AS BOMBASERevOfRcdId
FROM PRODUCTBASE PRDBASE1 INNER JOIN PRODUCT PRD1 ON
PRDBASE1.ProductBaseId = PRD1.ProductBaseId
FULL OUTER JOIN BOMBASE ON
PRD1.BOMBaseId = BOMBASE.BOMBaseId
FULL OUTER JOIN BOM ON PRD1.BOMId =
BOM.BOMId OR BOMBASE.RevOfRcdId = BOM.BOMId
FULL OUTER JOIN
ProductMaterialListItem PMLI1 ON BOM.BOMId = PMLI1.BOMId
FULL OUTER JOIN ProductBase PRDBASE2
ON PMLI1.ProductBaseId = PRDBASE2.ProductBaseId
FULL OUTER JOIN Product PRD2 ON
PMLI1.ProductId = PRD2.ProductId OR PRDBASE2.RevOfRcdId =
PRD2.ProductId
FULL OUTER JOIN UOM ON UOM.UOMId =
PMLI1.UOMId
WHERE PRDBASE1.ProductName = p_MasterProductName
AND PRD1.ProductRevision = p_MasterProductRev;
--
BEGIN
--
SET n_ComponentLevel = p_ComponentLevel;
--
IF ( n_ComponentLevel = 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_ComponentLevel = CHAR(p_ComponentLevel);
--
INSERT INTO EXPLODEDBOM ( ComponentLevel
,Material
,ComponentRev
,ComponentQty
,ComponentUOM )
VALUES( v_ComponentLevel
,p_MasterProductName
,'1'
,''
,'' );
--
END;
--
-- Initialize component level.
--
SET n_ComponentLevel = 1;
--
ELSE
--
SET n_ComponentLevel = n_ComponentLevel + 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_Material,'XXX') <> 'XXX' ) THEN
--
SET n_Sequence = COALESCE(n_Sequence,0) + 1;
--
BEGIN
--
SET n_ErrLocator = 15;
--
-- SET v_ComponentQty = CHAR(dbl_ComponentQty);
SET v_ComponentLevel = CHAR(n_ComponentLevel);
SET v_LevelString = CONCAT(REPEAT('
',n_ComponentLevel * 2),v_ComponentLevel);
--
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_Material||''','''||v_ComponentRev||''',''' ||n_ComponentLevel||')';
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

[Serge Rielau]

pu********@gmail.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_MasterProductName
VARCHAR(100)
,IN p_MasterProductRev
VARCHAR(10)
,IN p_ComponentLevel INT )
LANGUAGE SQL
BEGIN
--
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE n_Sequence INTEGER;
DECLARE n_ErrLocator INTEGER;
DECLARE n_ComponentLevel INTEGER;
--
DECLARE dbl_ComponentQty DOUBLE;
--
DECLARE v_ComponentLevel 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.ProductRevision AS ProductRev
-- ,PRDBASE1.ProductName AS ProductName
SELECT PRDBASE2.ProductName AS ComponentName
,PRD2.ProductRevision AS ComponentRevision
,DECIMAL(PMLI1.QtyRequired) AS ComponentQty
,UOM.UOMName AS ComponentUOM
-- ,PRD1.BOMBaseId AS BOMBaseId1
-- ,PRD2.BOMBaseId AS BOMBaseId2
-- ,PMLI1.ProductBaseId AS PMLIProdBaseId1
-- ,PMLI1.ProductId AS PMLIProdId1
-- ,PRD1.ProductId AS PRD1ProdId
-- ,PRD2.ProductId AS PRD2ProdId
-- ,PRDBASE2.RevOfRcdId AS PRDBS2RevOfRcdId
-- ,BOMBASE.RevOfRcdId AS BOMBASERevOfRcdId
FROM PRODUCTBASE PRDBASE1 INNER JOIN PRODUCT PRD1 ON
PRDBASE1.ProductBaseId = PRD1.ProductBaseId
FULL OUTER JOIN BOMBASE ON
PRD1.BOMBaseId = BOMBASE.BOMBaseId
FULL OUTER JOIN BOM ON PRD1.BOMId =
BOM.BOMId OR BOMBASE.RevOfRcdId = BOM.BOMId
FULL OUTER JOIN
ProductMaterialListItem PMLI1 ON BOM.BOMId = PMLI1.BOMId
FULL OUTER JOIN ProductBase PRDBASE2
ON PMLI1.ProductBaseId = PRDBASE2.ProductBaseId
FULL OUTER JOIN Product PRD2 ON
PMLI1.ProductId = PRD2.ProductId OR PRDBASE2.RevOfRcdId =
PRD2.ProductId
FULL OUTER JOIN UOM ON UOM.UOMId =
PMLI1.UOMId
WHERE PRDBASE1.ProductName = p_MasterProductName
AND PRD1.ProductRevision = p_MasterProductRev;
--
BEGIN
--
SET n_ComponentLevel = p_ComponentLevel;
--
IF ( n_ComponentLevel = 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_ComponentLevel = CHAR(p_ComponentLevel);
--
INSERT INTO EXPLODEDBOM ( ComponentLevel
,Material
,ComponentRev
,ComponentQty
,ComponentUOM )
VALUES( v_ComponentLevel
,p_MasterProductName
,'1'
,''
,'' );
--
END;
--
-- Initialize component level.
--
SET n_ComponentLevel = 1;
--
ELSE
--
SET n_ComponentLevel = n_ComponentLevel + 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_Material,'XXX') <> 'XXX' ) THEN
--
SET n_Sequence = COALESCE(n_Sequence,0) + 1;
--
BEGIN
--
SET n_ErrLocator = 15;
--
-- SET v_ComponentQty = CHAR(dbl_ComponentQty);
SET v_ComponentLevel = CHAR(n_ComponentLevel);
SET v_LevelString = CONCAT(REPEAT('
',n_ComponentLevel * 2),v_ComponentLevel);
--
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_Material||''','''||v_ComponentRev||''',''' ||n_ComponentLevel||')';
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_ComponentLevel;

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

[Brian Tkatch]

Thanx for the link Serge. And there's a picture of you too. :)

B.

[--CELKO--]

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.

[purushneel]

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

[Serge Rielau]

pu********@gmail.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