ControlM and Oracle 1

[newbieDev]

Hi everyone:

I need some help with some data migration related issues.
I am receiving a couple of flat files from a DB2 database. These flat files will be placed in a designated folder. I was told this folder will have to be on a server that has COntrolM so that it could be picked up by ORACLE for loading. I am not sure how this works. Can anybody help explain what I need to do?

I know I have to write a script to load the tables in question. I can do this with sql loader but I want this process automated - I want the files automatically picked up and loaded every two weeks.


Any help will be appreciated.

Thanks.

 

[Thargy]

newbie,

thread759-1420823 will show you what you need. Oracle external tables to the rescue!

If you need any more help, then of course, we'll do our best.

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

I was told this folder will have to be on a server that has COntrolM

By "COntrolM", are you talking about a server that uses a <Ctrl-M> as an end-of-line character? This should not (necessarily) be an issue for you.

I agree with John (Tharg) that Oracle's EXTERNAL TABLES are just the ticket for your need.

If you tell us about the incoming DB2 data (i.e., column names and datatypes, delimiters, flat-file name and the path in which it resides, et cetera), we can propose code to make the data instantly accessible via SQL as an Oracle table.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hoinz]

... or, by ControlM, do you mean this:

http://www.bmc.com/products/products_services_detail/0,,0_0_0_2,00.html

??

 

[newbieDev]

Hi Guys,

Thanks for the reply!
thargtheslayer - Let me see if I get this:
After doing everything needed to create the external table, I will not need to create source tables in Oracle, but query the external tables (which is really a text data file) for records that need to be inserted into my Oracle tables. Right? I am still not sure this will work for me because at some point I will need to update one of the source tables to include the newly assigned studentID so that I can later write a query to check other source tables and use the new ID where the old Id equals the old ID in the first updated source table when inserting records. Will external tables allow this?



SantaMufasa and hoinz - ControlM is actually the program/software that will be used to schedule when I want to load the DB2 flat data files. A program/softawre called file watcher will monitor when the file is present and I think inform controlM so that it can do what it needs to do.


Thanks again

 

[Thargy]

newbie,

no they won't. External tables are read only.
What you might attempt it to datapump out a new version, and then erase the old one, but this sounds a bit dangerous to me.

When you mention adding a new and old student id, it sounds as though you're adding some sort of uniques identifier, if not a primary key.

Can you let us know what you're trying to achieve here (I mean the business requirement) so that we can offer some informed advice. For example, we might be able to suggest a work-around for your problem.

Regards

Tharg

Grinding away at things Oracular

 

[newbieDev]

Thargtheslayer,

Thanks for the response.

This is the problem I am trying to solve:

We have this application created using an access database that is now being transfered to an Oracle Database which will have more tables than existed in the original Database.

We are trying to assign new IDs(primary key) to the students(it is a training application) while still making sure that every student is related to everything they were related to in the old application - for example if student 1234 took classes A and B in the access database, and the student was given a new id in oracle say 9999, student 9999 should have the same records only a different ID. This is why I was going to create some source tables with a blank field for the new student ID, and a field with the old ID. Once all students are added and issued new IDs,source table1 will be updated the the new ID where the last, first, and emails are equal. All my other source tables that have the old student ID can now be updated with the new ID, and I can now use this ID when loading data into the main oracle tables. I am also looking into assigning new IDs to classes.
I hope my explanation was good enough.

Thanks!

 

[Thargy]

er newbie,

any reason why you are not using Oracle migration workbench to do the job for you? It has specific import tools for Access to Oracle migrations, and will take care of the tables for you.

Regards

T


Grinding away at things Oracular

 

[newbieDev]

thargtheslayer -
What do you mean by take care of the tables for me? Do you mean I shouldn't worry about students being related to all the classes etc that they currently have in the access database? And that the tables will be loaded with new ID but relationships will be kept intact?

I am not a DBA and may not get access to the Oracle Workbench - I will find out though. I work in one of those places where the DBAs are not actually incharge of data migration, so I am trying to figure this out with hopefully some help.

Thanks again!

 

[Thargy]

newbie,

oracle migration workbench is a free download from Oracle.
It runs on windows and converts access databases into Oracle. There are also migration tools for sql server etc.

If you have a target database available, it will extract the Access structure and data and create the same in Oracle. Where necessary, all names will be uniquely truncated to 30 characters to comply with Oracle's limitations. There is also a separate tool in Application Express, which will import the forms into a web app for you.

No, it won't alter anything. If relationships don't exist in access, they won't exist in the Oracle equivalent. You still have to sort out the relational problems yourself.

You don't need a dba to do this for you. Provided you have read/write on a schema somewhere, you ought to be able to do it.

Regards

Tharg

Grinding away at things Oracular

 

[newbieDev]

Thanks! I will give this a shot and let you know how it works out.