Serge,

Thanks for your quick reply. Attached are examples of the T-SQL code vs. the
SQL PL code.

TSQL:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE GetApplDirectiveByAgency_SP
(
@AgencyID udt_Agency = NULL,
@ApplicationID1 udt_Application = NULL
)
AS
BEGIN
DECLARE
@ReturnValue udt_ReturnValue,
@Exist udt_Exist,
@Row udt_Row,
@Sequence udt_Sequence,
@errorNumber udt_ErrorNumber,
@errorText udt_ErrorText,
@AgencyID1 udt_Application
SELECT @AgencyID1 = Agency_Network.AgencyID1
FROM Agency_Network INNER JOIN Agency_Network Agency_Network_1
ON Agency_Network.AgencyID1 = Agency_Network_1.AgencyID2
WHERE (Agency_Network.RelationshipID = 'Includes')
AND (Agency_Network_1.AgencyID1 = 'User Roles')
AND (Agency_Network_1.RelationshipID = 'Includes')
AND (Agency_Network.AgencyID2 = @AgencyID)
SELECT DISTINCT @AgencyID1 'AgencyID',
Application_Directive.ApplicationID,
Application_Directive.DirectiveType,
Application_Directive.Directive,
Application_Directive.Note,
Application_Directive.HistoryRevisionCount
FROM [Application_Network],
[Application_Directive],
[Agency_Application]
WHERE Application_Network.ApplicationID2 =
Application_Directive.ApplicationID
AND Agency_Application.ApplicationID =
Application_Directive.ApplicationID
AND (Agency_Application.AgencyID = @AgencyID
OR Agency_Application.AgencyID IN (
SELECT Agency_Network.AgencyID1
FROM Agency_Network INNER JOIN Agency_Network Agency_Network_1
ON Agency_Network.AgencyID1 = Agency_Network_1.AgencyID2
WHERE (Agency_Network.RelationshipID = 'Includes')
AND (Agency_Network_1.AgencyID1 = 'User Roles')
AND (Agency_Network_1.RelationshipID = 'Includes')
AND (Agency_Network.AgencyID2 = @AgencyID)))
AND Application_Network.ApplicationID1 = @ApplicationID1
ORDER BY
AgencyID,
Application_Directive.ApplicationID,
Application_Directive.DirectiveType,
Application_Directive.Directive
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SQL PL:

CREATE PROCEDURE db2admin.GetApplDirectiveByAgency1_SP(
IN v_AgencyID VARCHAR(75),
IN v_ApplicationID1 VARCHAR(75))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT;
DECLARE v_ReturnValue INT;
DECLARE v_Exist INT;
DECLARE v_Row INT;
DECLARE v_Sequence INT;
DECLARE v_errorNumber INT;
DECLARE v_errorText VARCHAR(64);
DECLARE v_AgencyID1 VARCHAR(75);
DECLARE l_sqlcode INT DEFAULT 0;
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT DISTINCT v_AgencyID1 AS AgencyID,
db2admin.Application_Directive.ApplicationID,
db2admin.Application_Directive.DirectiveType,
db2admin.Application_Directive.Directive,
db2admin.Application_Directive.Note,
db2admin.Application_Directive.HistoryRevisionCoun t
FROM db2admin.Application_Network, db2admin.Application_Directive,
db2admin.Agency_Application
WHERE db2admin.Application_Network.ApplicationID2 =
db2admin.Application_Directive.ApplicationID AND
db2admin.Agency_Application.ApplicationID =
db2admin.Application_Directive.ApplicationID AND
(CAST(db2admin.Agency_Application.AgencyID AS VARCHAR(75)) =
v_AgencyID
OR
db2admin.Agency_Application.AgencyID IN
(SELECT db2admin.Agency_Network.AgencyID1
FROM db2admin.Agency_Network INNER JOIN db2admin.Agency_Network AS
Agency_Network_11 ON
db2admin.Agency_Network.AgencyID1 = Agency_Network_11.AgencyID2
WHERE CAST(db2admin.Agency_Network.RelationshipID AS VARCHAR(75)) =
'Includes' AND
CAST(Agency_Network_11.AgencyID1 AS VARCHAR(75)) =
'User Roles' AND
CAST(Agency_Network_11.RelationshipID AS VARCHAR(75)) =
'Includes' AND
CAST(db2admin.Agency_Network.AgencyID2 AS VARCHAR(75)) =
v_AgencyID))
AND
CAST(db2admin.Application_Network.ApplicationID1 AS VARCHAR(75)) =
v_ApplicationID1
ORDER BY AgencyID, db2admin.Application_Directive.ApplicationID,
db2admin.Application_Directive.DirectiveType,
db2admin.Application_Directive.Directive ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET l_sqlcode = SQLCODE;

SELECT db2admin.Agency_Network.AgencyID1
INTO v_AgencyID1
FROM db2admin.Agency_Network INNER JOIN db2admin.Agency_Network AS
Agency_Network_1 ON
db2admin.Agency_Network.AgencyID1 = Agency_Network_1.AgencyID2
WHERE CAST(db2admin.Agency_Network.RelationshipID AS VARCHAR(75)) =
'Includes' AND
CAST(Agency_Network_1.AgencyID1 AS VARCHAR(75)) = 'User Roles' AND
CAST(Agency_Network_1.RelationshipID AS VARCHAR(75)) = 'Includes'
AND
CAST(db2admin.Agency_Network.AgencyID2 AS VARCHAR(75)) = v_AgencyID
FETCH FIRST 1 ROWS ONLY;
OPEN temp_cursor;
END

Any insights you may have would be greatly appreciated. I'll jot down mtk's
email address. Thanks.

Adam

"Serge Rielau" <srielau@ca.eye-bee-m.com> wrote in message
news:bu9b38$3eo$1@hanover.torolab.ibm.com...
> Adam,
>
> The same is true in DB2. However MTK's first concern is with correctness.
> There may be some behavior or feature in SQL Server that is different
> enough from DB2 so the MTK felt the need to transpose to cursors.
> Possibly there are function calls that were mapped to procedures?
> If you show me an example (T-SQL vs generated SQL PL) I may be able to
> tell you why the MTK worked the way it did.
> Also not ethat you can send comments and requests to mtk@us.ibm.com
> The MTK team is very eager to improve the tool and they have a quick
> turnaround.
>
> Cheers
> Serge
>
>
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab
>