SEO

Hi!
I'm a newbie at DB2 but have 13 years of SQL Server. I need to convert over
100 SQL Server procs to DB2. I haven't had much luck with the IBM
Integration Toolkit and want to avoid ER/win templates. Any suggestions.
Thanks.
Adam

Not sure what toolkit you are referring to have you tried the migration
toolkit?
http://www-306.ibm.com/software/data/db2/migration/mtk/
The toolkit does a fairly good job with a usual conversion rate around
80%-90%. I doubt you will find a toll doing better than that.
Given that any tool will deliver an "emulation" of the source DBMS you
then need to hand tune those procedures that are performance critical.
Don't be affraid of order-of-magnitude improvement requirements.
A migration tool produces so much fluff to achieve correctness that it's
easy to improve upon.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge,
Thanks for your reponse. I am using that toolkit and actually, it is quite
good. It doesn't seem to handle user-defined datatypes even though they
exist in db2 but I can manually tweak the code. I'm more concerned, however,
about the fact that the converted db2 stored procedures use cursors while
the MS SQL procs do not. I've always tried to avoid cursors in SQL Server as
they run much slower than result-set oriented code. Is the same thing not
true in db2? Your help is appreciated.

Cheers,
Adam
"Serge Rielau" <srielau@ca.eye-bee-m.com> wrote in message
news:bu8tn9$25a$1@hanover.torolab.ibm.com...
> Not sure what toolkit you are referring to have you tried the migration
> toolkit?
> http://www-306.ibm.com/software/data/db2/migration/mtk/
> The toolkit does a fairly good job with a usual conversion rate around
> 80%-90%. I doubt you will find a toll doing better than that.
> Given that any tool will deliver an "emulation" of the source DBMS you
> then need to hand tune those procedures that are performance critical.
> Don't be affraid of order-of-magnitude improvement requirements.
> A migration tool produces so much fluff to achieve correctness that it's
> easy to improve upon.
>
> Cheers
> Serge
>
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab
>

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

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
>

Adam,

> good. It doesn't seem to handle user-defined datatypes even though they
> exist in db2 but I can manually tweak the code.

please be careful: user-defined datatypes in DB2 are very different
from user-defined datatypes in MS SQL Server.

> I'm more concerned, however,
> about the fact that the converted db2 stored procedures use cursors while
> the MS SQL procs do not.

In SQL Server you can just write
SELECT * FROM SOME_TABLE
in an SP, the client will get a result set

To accomplish the same in DB2 you have to open a cursor, that's
normal.
Are you speaking about that?

There is a great book:


DB2 SQL Procedural Language for Linux, Unix and Windows
Paul Yip, Drew Bradstock, Hana Curtis, Michael X. Gao, Zamil
Janmohamed

Thanks for your reply. The udts will be going so they won't be a problem.
The cursor I referred was the same as the one that you say is "normal" in
DB2. That's a relief. I have the very book you mentioned in front of me!

Have you had any success using ER/win templates to convert stored procs?

Adam
"AK" <ak_tiredofspam@yahoo.com> wrote in message
news:46e627da.0401161512.4f804abc@posting.google.c om...
> Adam,
>
> > good. It doesn't seem to handle user-defined datatypes even though they
> > exist in db2 but I can manually tweak the code.
>
> please be careful: user-defined datatypes in DB2 are very different
> from user-defined datatypes in MS SQL Server.
>
> > I'm more concerned, however,
> > about the fact that the converted db2 stored procedures use cursors
while
> > the MS SQL procs do not.
>
> In SQL Server you can just write
> SELECT * FROM SOME_TABLE
> in an SP, the client will get a result set
>
> To accomplish the same in DB2 you have to open a cursor, that's
> normal.
> Are you speaking about that?
>
> There is a great book:
>
>
> DB2 SQL Procedural Language for Linux, Unix and Windows
> Paul Yip, Drew Bradstock, Hana Curtis, Michael X. Gao, Zamil
> Janmohamed

That code is fine. It's how the SQL Standard handles passing of result
sets. Implicitly MS SQL Server/Sybase will also have to pass a resultset.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

>
> Have you had any success using ER/win templates to convert stored procs?
>

No, there was a decision to re-write them manually.
The reason: error handling in SQL/PL is entirely different.