Generating UUIDs or GUIDs in DataStage

alanwms · Post by **alanwms** » Fri Jul 23, 2004 8:41 am

I have a need to generate UUIDs in DataStage 7.x to be used as the key column for new rows in a target Oracle database. Has anyone worked with UUIDs before? The Web has various information on UUIDs and GUIDs, but nothing specific to my AIX 5.1 environment. I can generate a GUID using the SYS_GUID() function in Oracle, but I would rather use a shell script wrapped in a routine to call a Unix function without having to make the connection to Oracle for each iteration. Any help would be appreciated!

Thanks.
Alan

kduke · Post by **kduke** » Fri Jul 23, 2004 8:49 am

Alan

What is a UUID or a GUID?

By the way hope all is well. In Big D this weekend. Life is good.

alanwms · Post by **alanwms** » Fri Jul 23, 2004 9:08 am

Kim,

UUID is a Universal Unique IDentifier. You can do a Google search and browse the topics. The target database I'm loading uses UUIDs as surrogate keys. The Siebel application generates its own UUIDs when loading data via XML. I have to emulate the process for the initial (history) load of the data and for any new transactions that come in. You might think of UUIDs as a guaranteed unique surrogate keys based on current time/date, MAC address, and some other fuzzy logic.

Have fun back home,
Alan

ray.wurlod · Post by **ray.wurlod** » Fri Jul 23, 2004 5:37 pm

Some time back I posted a stored procedure for generating a GUID in SQL Server and returning it to DataStage. Perhaps you could adapt this.

kduke · Post by **kduke** » Fri Jul 23, 2004 6:52 pm

Hey Alan, you had some cool shell scripts to ftp files and archive them afterwards. Can you post those? Make sure you Alt-C around your code.

Answer a few questions some time. You know this stuff as well as anyone. Been doing this as long as I have.

alanwms · Post by **alanwms** » Fri Jul 23, 2004 8:37 pm

Thanks, Ray. I read through what you wrote a few months ago, but I couldn't really apply it to my project. I can generate a UUID via Oracle, but I'd rather not make the database call. I'd like to execute a Unix command, but the ones I found on the Web are not what's available to me right now on my client's server. I figured there may be a C program or Unix script out there that someone has used before that I could take advantage of. Barring that, I'm probably just going to use Oracle to generate the UUIDs (GUIDs) for me.

Ray, your responses are always welcomed and informative, and those of us that have been doing this kind of work for a long time really appreciate the time and effort you exert in responding to questions, no matter how trivial or complex. I hope to be able to present a solution to my problem to the forum for future reference.

Thanks,
Alan

kduke · Post by **kduke** » Sat Jul 24, 2004 8:13 am

Alan

Have you tried sourceforge.com? They may have a solution which is open source. You may need to compile it to run it but it is worth the look. They have lots of cool stuff for free.