Concerning implementing an equivalent of the ORA / DB2 Sequence Object in Teradata

Database

Concerning implementing an equivalent of the ORA / DB2 Sequence Object in Teradata

Hello All , Good Day,

Our client has given us a mandate to migrate their artefacts from one platform (DB2) and into Teradata v14.0.

During the ongoing data analysis phase, we have come across (apart from tables, views, indexes etc),  sequences as well as identity clauses. We feel that identity clauses are not an issue (for the migration ), but wanted to have an idea / general consensus in this forum as to what would be the best approach to migrate the stand alone sequence objects to the Teradata platform and in what manner. As per my understanding, Teradata does not have a direct equivalent for Sequence objects.

One important point to make here is that the client has a large number of stored procedures that have I/U/D statements, that utilize these sequence objects. We do not wish to drastically modify them, its best if these SPs are the least modified of the lot.

Thinking aloud, I was wondering if we could have Tables created in TD in place of these sequences in DB2, having columns equivalent to the Sequence parameters (INITVAL, NEXTVAL, MAXVAL, MINVAL etc..), and create a script (another SP) that knows how to utilize this Table appropriately, and call that script in these I/U/D statements -> That way I have least impact on the existing client SP scripts.

What do you think ? Is there a better way ? If so, kindly advise.

Regards,

Karan

Tags (2)
3 REPLIES
Enthusiast

Re: Concerning implementing an equivalent of the ORA / DB2 Sequence Object in Teradata

Hi Karan,

Look from the functionality, end requirement. Dont go line by line code . Oracle and some DBs have pseudo cols like nextval, currval etc . Simplify sequence and build a logic if required. I see that many DBs have many psedudo cols in sequence. It is fine so long as there are no failures in jobs. They work well under ideal cases. But when it comes to reloading and correction of data, then it is a nightmare :). In all projects I worked so far, there are none that work ideally, without support :). You can think of row_num() and build your logic.

It is my suggestion.

Cheers,

Teradata Employee

Re: Concerning implementing an equivalent of the ORA / DB2 Sequence Object in Teradata

Using tables instead of sequences can work. But while migrating from Oracle/DB2/etc DBs one must take care of the logic .... like in PL/SQL .... everything is executed via cursor .... implicit or explicit .... while in Teradata we dont recommend using cursors and we believe almost any logic can be changed to set logic.

Hence, do take care of the performance before-hand so you dont end-up in a situation when project is close to its end and you are facing performance issues.

HTH!

Re: Concerning implementing an equivalent of the ORA / DB2 Sequence Object in Teradata

Hello ,

Thank you for your replies.

@Raja: Point noted about the ETLs and the corrections ! Thank you !

Adeel, when you mention Set logic, can you give an example as to what exactly you have in mind ?

Just so that I am clear, an small example would be great ... and thank you ..

@Adeel

while in Teradata we dont recommend using cursors and we believe almost any logic can be changed to set logic