• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1665
  • Last Modified:

DB2 Migrating toolkit SQLServer uniqueidentifiers to DB2

The problem is very easy and I just need confirmation of my thoughts.

A little while ago I asked a question for a similar datatype in DB2 for the SQLServer UniqueIdentifier type ( http://www.experts-exchange.com/Databases/IBM_UDB/Q_21752797.html ). The conclusion was that it's not possible to do a 1 - 1 conversion.

Today when I was browsing around too see if we could do some stuff automaticly to make up some time we will be loosing when changing from uniqueidentifiers to a db2 unique type and I came accross the IBM DB2 Migration Toolkit which allows you to migrate from MSSQL to DB2 quite easily.

So I gave it a try with one of our .sql scripts, but the result was not as I expected. The example is fictional though:

Input:
CREATE TABLE [Users]
(
   [ID] uniqueidentifier PRIMARY KEY ROWGUIDCOL DEFAULT (newid()) NOT NULL,
   [FirstName] nvarchar(256) NULL,
   [LastName] nvarchar(256) NULL,
   [Age] int NULL
)

Output:
CREATE TABLE Users(
    ID CHAR(16) FOR BIT DATA NOT NULL  PRIMARY KEY,
    FirstName VARGRAPHIC(256),
    LastName VARGRAPHIC(256),
    Age INTEGER,
)!

CREATE TRIGGER "[Users1" NO CASCADE BEFORE INSERT ON Users
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC
   
--|  (newid())

    IF NEW.ID IS NULL THEN
       
        SET NEW.ID = MSSQL.NEWID();
   
    END IF;

END!

--------

This basicly suggests that in DB2 there is a function/trigger (whatever) called MSSQL.NEWID() which can generate uniqueidentifiers.

I highly doubt DB2 will ever have a function/method/trigger called after a competetitor's database. But I assume that this is not correct and that the Migration kit thinks they are 'homemade' functions/triggers which were not added in the .sql file, so the migration kit thinks it's a homemad/available function/trigger in DB2?

Am I correct in these thoughts?
0
existenz2
Asked:
existenz2
2 Solutions
 
sachinwadhwaCommented:
AFAIK, there is no function  MSSQL.NEWID() in DB2 by default.

Migration kit might have this function, which is added to your database or may be a federated object...
0
 
ghp7000Commented:
if you want a unique identifier for the kind of table data you indicate you have, which I understand to be a first name, a last name and an age, you can assign the row the value of the row_number() over() function, cast the result to char if need be, or add leading zeros or do whatever you need to do to get the unique id in the format you want.
This seems to me to be alot simpler than what you are contemplating.
0
 
Kent OlsenData Warehouse Architect / DBACommented:

And even easier is to simply define an IDENTITY column in the table.  DB2 will do all of the heavy lifting for you.


Kent

0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
existenz2Author Commented:
The question was not about how to use a unique identifier or something similair, we have found an alternative for that, besides the posted table is as stated in my question fictional. It's not a real table, but I can't paste those because of company restrictions.

The question is:
Is this actually available in DB2:

      SET NEW.ID = MSSQL.NEWID();

This was a result from some tests I did to see how much work we have to do if we are going to use the IBM DB2 Migration kit.
   
0
 
existenz2Author Commented:
In addition:

In my opinion/knowledge that generated line is wrong and it's just how DB2 interpreteds the "DEFAULT (newid())" code from the MSSQL table. Most likely it sees that as a function written by us. Is that thought correct?
0
 
ghp7000Commented:
go to db2 control center, click functions, filter your search for mssql if necessary, see if the function is listed there.
As for naming conventions, I dont think IBM has any problem acknowledging that sql server is out there, I think the schema naming is on purpose to make it clear what is supposed to happen.
0
 
existenz2Author Commented:
Hmm good idea, I'll have a look tomorrow.
0
 
existenz2Author Commented:
As I expected it's something the Migration toolkit thought up from the MSSQL code, thanks for for the acknowledgement. Points for sachinwadhwa and ghp7000, both helped me with it.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now