Migration from 8i to 9i

srinivasb · Post by **srinivasb** » Mon Feb 24, 2003 11:23 pm

Hi Everyone,

We have designed nearly 70+ job designs for loading a data warehouse using the ORA OCI 8 plug in.

Client is migrating from 8i to 9i Data base and they have requested us to redesign the jobs in 9i.

We had a input that the dsx could be modified to change all 8i occurrences in DSX to 9i.
We have done this and the job works when reimported. But the expert advise has been NOT TO DO IT this way.

We need some inputs from Ray and others as to whether this approach is OK or NOT OK (considering the ease of find and replace and also the pains of job misbehaving in PRODUCTION RUN Time)

Your suggestions will be valued..
Regards
Srinivas.B

Srinivas.B
India
Mobile :0091-44-2301101
Xtn 35

ray.wurlod · Post by **ray.wurlod** » Tue Feb 25, 2003 1:31 am

I believe you will find that your existing jobs will work with Oracle 9i without modification.
Search this forum (including the archives of datastage-users@oliver) to find posts concerning this.
Probably the best is topic ID 83503 "DataStage 6.0 OCI8 versus OCI9" in which at least two posters make the point that the OCI8 stage (with Oracle 8 client software) will happily communicate with Oracle 9i.
So don't let "them" upgrade your Oracle client software and your work is complete.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

chulett · Post by **chulett** » Tue Feb 25, 2003 8:06 am

There's no need to rush to OCI9. Heck, depending on your platform, it may not even work! For us folks on Tru64 (for example) running 64bit client software, we are definitely out of luck as the OCI9 stage only supports 32bit clients right now. As Ray mentions, we use OCI8 and the 8i client to work with our 9i databases with no issues and no loss of functionality that I am aware of.

Ray - how in the world do you search by Topic ID? Or even know what the ID is? [:I]

-craig

ray.wurlod · Post by **ray.wurlod** » Tue Feb 25, 2003 2:53 pm

There's no search by topic ID that I'm aware of. However, once you've found and displayed the topic you want, the topic ID is one of the arguments to the asp in the Address (URL) field of your browser; I find it a useful way to guarantee that my interlocutor is looking at the correct message.

chulett · Post by **chulett** » Tue Feb 25, 2003 2:56 pm

Argh! I *knew* it would be something simple that I was overlooking. [:(!] Thanks for the information.

-craig

beanieb · Post by **beanieb** » Tue Mar 25, 2003 8:47 pm

Hi srinivasb,

Could u advise how to modify the DSX to change all 8i occurrences to 9i?

Thanks

chinek · Post by **chinek** » Tue Mar 25, 2003 9:18 pm

Hi
I believe the dsx (Data Stage export file) is just a regular text
file and you can use Windows Notepad to make changes.
In this case,say if your database name is changed from 8i to 9i,then you do a Search and Replace all occurrences of that database name.

Nick

ray.wurlod · Post by **ray.wurlod** » Tue Mar 25, 2003 10:12 pm

Do you really believe it's that easy? [:0]

How do you manage the differences in structure between the OCI8 stage type and the OCI9 stage type? Luckily for Srinivas, there are no OCI8 properties that do not also exist in the OCI9 stage; this is the only way that what he did could have worked. But the couple of extra properties for OCI9 will have default values under his scenario.

As a general rule, hacking the DSX file can only ever bring grief, with the possible exception of the Character Set field.

chinek · Post by **chinek** » Wed Mar 26, 2003 4:24 pm

Hi ,
I am not advocating changing it to OCI 9 , but rather answering a question on how to modify the dsx file. I believe the user is still using OCI 8 but migrated to the 9i database. It is not a migration to OCI 9....
Sorry for any confusion caused.

Nick

beanieb · Post by **beanieb** » Thu Mar 27, 2003 2:30 am

Hi,

I'm sorry for the misunderstanding caused too. I'm just assessing the impact of changing ALL my jobs from OCI8i to OCI9i, and hope to find the best solution. My user insisted on using the OCI9i plugin once they have upgraded the Oracle Database to 9i.

Understand that modifying the dsx file is not a solution as it may cause unnecc probs, as Ray had pointed out. Looks like I've to go through the hard way of changing the OCI8i stage to OCI9i in every single job [:(] That is, if the OCI9i plugin is able to work on a 64bit Oracle 9i client software.

vmcburney · Post by **vmcburney** » Thu Mar 27, 2003 2:53 am

I've done it both ways. Modified a DSX with successful results and modified jobs manually to replace ODBC stages with oracle stages. When doing it manually you do not want to introduce defects, so stick to a cut and paste approach. I've found it fast to open Designer in the left 80% of the screen and open a notepad in the right portion. In each job copy your 8i values to notepad, replace the 8i stage with 9i and copy the values back from notepad. You cannot copy a password field but you can paste into it, just type the password into notepad. It's even faster if your database login details are the same across all stages. You should be able to knock over the 70 odd jobs in under an hour.

When replacing a stage make sure you add the new stage and relink to it before you delete the old stage or you will lose your column definitions and mapping.

Vincent McBurney
Data Integration Services
www.intramatix.com

chulett · Post by **chulett** » Thu Mar 27, 2003 2:21 pm

quote:Originally posted by beanieb
Looks like I've to go through the hard way of changing the OCI8i stage to OCI9i in every single job [:(] That is, if the OCI9i plugin is able to work on a 64bit Oracle 9i client software.

Double-check before you go too much further! 64bit support is non-existent at the moment as far as I know. For Tru64 users, OCI9 is not an option now as they don't support the 64bit client and that's all we've got. In the readme for 6.0.1 it mentions that 64bit HP-UX users can use the OCI9 plugin as long as you use the "32bit Oracle 9i libraries". So, besides the fact that you really don't need to go to all that pain (as mentioned above), you'll probably find it won't work for you if you do. [:(!]

-craig