Migration from SQL SERVER to DB2 8.1

Migration from SQL SERVER to DB2 8.1

Post by soum » Wed, 14 May 2003 03:32:54



Hi,
We are planning a migration from SQL SERVER 2000 to DB2 8.1

Do you have any good resources for planning this migration?
What are some of the bottlenecks that we need to be aware off?

Where do we start and what should be the realistic expections from
such a migrtation?
Are you aware of any best practices recommendations for such a migration?
Thanks for you help.
-Soumil

 
 
 

Migration from SQL SERVER to DB2 8.1

Post by Blair Kenneth Adamach » Wed, 14 May 2003 03:43:55


IBM has  some resources here:
http://www-3.ibm.com/software/data/db2/migration/mtk/

> Hi,
> We are planning a migration from SQL SERVER 2000 to DB2 8.1

> Do you have any good resources for planning this migration?
> What are some of the bottlenecks that we need to be aware off?

> Where do we start and what should be the realistic expections from
> such a migrtation?
> Are you aware of any best practices recommendations for such a migration?
> Thanks for you help.
> -Soumil


 
 
 

Migration from SQL SERVER to DB2 8.1

Post by Pablo Sanche » Wed, 14 May 2003 04:52:52




Quote:> Hi,
> We are planning a migration from SQL SERVER 2000 to DB2 8.1

> Do you have any good resources for planning this migration?
> What are some of the bottlenecks that we need to be aware off?

> Where do we start and what should be the realistic expections from
> such a migrtation?
> Are you aware of any best practices recommendations for such a migration?
> Thanks for you help.

Blair already provided the link to IBM's porting tool.  If I were you,
I'd run it against your instance and review the report created.  It's
a pretty nifty tool.  

Some things you'll see is that DB2 is case-sensitive whereas SQL

but not in DB2.  The error report the DB2 Wizard tool will identify
all the issues.

My company is about to start a port from SQL Server to DB2 for a
client and in order to cost the effort, we ran the Wizard against a
test SQL Server instance.  I suggest you do the same so you can get a
'feel' for the effort.

HTH!
--
Pablo Sanchez, Blueoak Database Engineering
http://www.blueoakdb.com

 
 
 

Migration from SQL SERVER to DB2 8.1

Post by Serge Riela » Wed, 14 May 2003 07:04:22


One more thing to note is that the migration tool is aimed at semantic
correctness.
As such it will try to emulate  SQL Server on DB2, As with all emulators
some loss has to be expected.
So you will need to identity your performance critical procedures and
DB2-ize them more thoroughly.

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Visit DB2 Developer Domain at
http://www7b.software.ibm.com/dmdd/

 
 
 

Migration from SQL SERVER to DB2 8.1

Post by Dmitr » Wed, 14 May 2003 18:34:12


Hi,

SQL Server may use longer object names that allowed in DB2. Also SQL Server
index names are unique within a table while DB2 index names are unique
within a database. There are also differencies in reserved words and SQL
Server object and column names may use words that are reserved in DB2.

Have a look at
http://www7b.software.ibm.com/dmdd/zones/porting/resources.html

Best regards, Dmitry
--
SQLWays - Database migration software for IBM DB2, Oracle, SQL Server,
Sybase and MySQL
http://www.ispirer.com/products




> > Hi,
> > We are planning a migration from SQL SERVER 2000 to DB2 8.1

> > Do you have any good resources for planning this migration?
> > What are some of the bottlenecks that we need to be aware off?

> > Where do we start and what should be the realistic expections from
> > such a migrtation?
> > Are you aware of any best practices recommendations for such a
migration?
> > Thanks for you help.

> Blair already provided the link to IBM's porting tool.  If I were you,
> I'd run it against your instance and review the report created.  It's
> a pretty nifty tool.

> Some things you'll see is that DB2 is case-sensitive whereas SQL
> Server isn't for objects/sp variables.  For example, if you DECLARE'd

> but not in DB2.  The error report the DB2 Wizard tool will identify
> all the issues.

> My company is about to start a port from SQL Server to DB2 for a
> client and in order to cost the effort, we ran the Wizard against a
> test SQL Server instance.  I suggest you do the same so you can get a
> 'feel' for the effort.

> HTH!
> --
> Pablo Sanchez, Blueoak Database Engineering
> http://www.blueoakdb.com

 
 
 

Migration from SQL SERVER to DB2 8.1

Post by A » Thu, 15 May 2003 00:23:40


real story:

1. Schema conversion.
I downloaded Migration Toolkit from
http://www-3.ibm.com/software/data/db2/migration/mtk/?isrc_d=99999999
Migration from SQL Server seems to be in better shape, migration from
Oracle is in beta yet.
I wasn't able to have Migration toolkit move data properly.

2. Connectivity
DB2 uses only operating system autentication (similar to Windows
authenication for MS SQL Server ), it does not have its own
authenication.
... but we need to convert schema name to uppercase when selecting
from catalog views, like htis: WHERE TABSCHEMA=UPPER('schemaname')


in Appendix D of SQL Reference)

Functions:
SUBSTR - interface same as in Oracle. Documened in SQL Reference
Oracle: TO_NUMBER
MS SQL Server: CONVERT(INT,
DB2: INT(

4. Replaced DELETE <TableName> with DELETE FROM <TableName>. DELETE
FROM works against Oracle and SQL Server too.

5. Fetching first N rows.
In Oracle: RowNum<10
In SQL Server: TOP 10
In DB2: SELECT * fFROM SOME_TABLE WHERE some condition FETCH FIRST 10
ROWS ONLY

6. Replaced INSERT <TableName> with INSERT INTO <TableName>. INSERT
INTO works against Oracle and SQL Server too.

7. Replaced INSERT INTO TableName SELECT Column1, Column2, NULL, NULL
FROM ...
with
INSERT INTO TableName(Column1, Column2) SELECT Column1, Column2 FROM
This works against Oracle and SQL Server too

8. Constraint name length cannot exceed 18 characters, so I changed
all the DROP/ADD CONSTRAINT statements, so that constraint names are
system generated, and retrieved from catalog view (because you need to
know constraint's name to drop it):
Same about index names

9. There are many differences in error messages, such as when index to
be dropped does not exist. They are all (or almost all)
vendor specific.

10. TRUNCATE TABLE won't work against DB2. Used DELETE FROM instead. I
think DROP/CREATE TABLE might work much much faster

11. Since schema name is in lower case, I needed to explicitly convert
it to uppercase when retrieveing from system (catalog) views:
SELECT TYPE FROM SYSCAT.TABCONST WHERE TABSCHEMA = SYSIBM.UPPER('...')
AND TYPE = 'P' AND TABNAME = '...'

12. When an INSERT/UPDATE/DELETE/SELECT statement actually
modifies/fetches no rows, DB2 issues an error ... . Error message
looks like "No row was found for".

Resources:

Porting to DB2 Web site
http://www7b.boulder.ibm.com/dmdd/zones/porting/

IBM DB2 Universal Database Porting Guide: Oracle to DB2 Version 7.2
http://www7b.software.ibm.com/dmdd/library/techarticle/0205wilkins/02...

 
 
 

Migration from SQL SERVER to DB2 8.1

Post by Anton Verstee » Thu, 15 May 2003 00:51:25



>real story:

>12. When an INSERT/UPDATE/DELETE/SELECT statement actually
>modifies/fetches no rows, DB2 issues an error ... . Error message
>looks like "No row was found for".

Not an error but a warning (SQLCODE +100):

SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table.  SQLSTATE=02000

--
Anton Versteeg
IBM Netherlands

 
 
 

Migration from SQL SERVER to DB2 8.1

Post by soum » Thu, 15 May 2003 01:58:14



> Hi,

> SQL Server may use longer object names that allowed in DB2. Also SQL Server
> index names are unique within a table while DB2 index names are unique
> within a database. There are also differencies in reserved words and SQL
> Server object and column names may use words that are reserved in DB2.

> Have a look at
> http://www7b.software.ibm.com/dmdd/zones/porting/resources.html

> Best regards, Dmitry
> --
> SQLWays - Database migration software for IBM DB2, Oracle, SQL Server,
> Sybase and MySQL
> http://www.ispirer.com/products





> > > Hi,
> > > We are planning a migration from SQL SERVER 2000 to DB2 8.1

> > > Do you have any good resources for planning this migration?
> > > What are some of the bottlenecks that we need to be aware off?

> > > Where do we start and what should be the realistic expections from
> > > such a migrtation?
> > > Are you aware of any best practices recommendations for such a
>  migration?
> > > Thanks for you help.

> > Blair already provided the link to IBM's porting tool.  If I were you,
> > I'd run it against your instance and review the report created.  It's
> > a pretty nifty tool.

> > Some things you'll see is that DB2 is case-sensitive whereas SQL
> > Server isn't for objects/sp variables.  For example, if you DECLARE'd

> > but not in DB2.  The error report the DB2 Wizard tool will identify
> > all the issues.

> > My company is about to start a port from SQL Server to DB2 for a
> > client and in order to cost the effort, we ran the Wizard against a
> > test SQL Server instance.  I suggest you do the same so you can get a
> > 'feel' for the effort.

> > HTH!
> > --
> > Pablo Sanchez, Blueoak Database Engineering
> > http://www.blueoakdb.com

Hi TEAM,
Thanks for all the input that you have provided.
Would you happen to have any migration project plan template that I
can use as a check list for this migration.
I want to make sure that we check all the variables in this equation
of migration.
Thanks for all the good work.
-Soumil

 
 
 

Migration from SQL SERVER to DB2 8.1

Post by A » Thu, 15 May 2003 06:07:21



> Not an error but a warning (SQLCODE +100):

> SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a
> query is an empty table.  SQLSTATE=02000

AGREED
 
 
 

1. anybody sucessful with suse 8.1 and db2 8.1?

I've done two separate installs of db2 8.1 on two seperate computers
with the same Linux distro - suse 8.1 and I get the same results each
time - It runs fine locally, I can connect with the control center and
also can run queries with the db2 command line client against the sample
database.

But, no luck connecting with the control center from a windoze 2000 box
on the same local subnet.  I can connect from control center on Suse 8.1
to an instance running on the same windoze 2000 box, but not the other
way around.  I did a little testing with JDBC too.  I can connect fine
to DB2 on windoze but not on the linux box.  Some of the control center
discovery seems to work (the server is discovered - but the type is set
to windows) and I have solid network connectivity between the boxes
otherwise (ssh, samba, http, etc...).

Seems to me that it's a DB2 network configuration issue, but the
messages and logs from DB2 leave me stumped - no idea what I'm supposed
to do with the following (from the Control Center on the windoze box):

<snip>
SQL22205C The DB2 Administration Server encountered an unexpected error
while executing a request.
</snip>

I read the IBM DB2 compatibility guides and it indicated that Suse 8.1
Pro was supported - maybe only in a non-networked configuration?

Any ideas are welcome and appreciated.

Thanks,

-Mark

2. How to override identity in bulk copy app?

3. ArrayIndexOutOfBoundsException with db2jcc and db2 8.1 server

4. ORACLE DBA with 16yrs exp (12 yrs Oracle & 7 yrs mainframe)

5. Need help installing DB2 8.1 on Linux Redhat Advanced Server 2.1

6. sqlserver 2000 in xp pro

7. Federate with Informix IDS7.3 from DB2 8.1

8. DAO Support Services Check

9. Problems with installation DB2 UDB 8.1 WE for Linux

10. DB2 PE 8.1 JRE Pathing Problem on RH8

11. Problems with installation DB2 UDB 8.1 WE for Linux

12. jdbc connection error with DB2 8.1 64-bit client

13. JDBC Driver type 4 for DB2 8.1