How do I populate a DB2 database using ColdFusion.

Hello,

I developed a CF application using MS Access as backend database.  Now, our DBAs build the database in DB2 so we could migrate from Access to DB2.  The DB2 database for now, only contains the tables.  The DBA told me that I am supposed to be the one to populate the tables by using coldfusion.

Let say I have 3 tables in  MS Access that currently have records of 60, 300, 600.  I want to move all of these records at the same time to the tables in DB2, Should I do a select first and then do an insert?  Please guide me.   I already have the datasource in DB2.

Thanks
mdbboundAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kkhippleCommented:
1.  create another source in MS Access
2.  Create the necassary tables in MS Access (use the CREATE TABLE syntax)
3.  Like you said, use select statement to query the db2 table and put that into the MS Access
4.  Then using that data, do an insert into the MS Access

that is basically what you need to do and your right, you do have the idea.
mdbboundAuthor Commented:
no wait, the data is coming from MS Access and going to IBM/DB2.  The tables in IBM/DB2 are already done but has no data.  Now my job, is to move the records from Access tables to the tables in IBM/DB2.

How do i do that using coldfusion. I mean do I have to do a SELECT then INSERT.  Please, if there is anyone out there, I need help.
mrichmonCommented:
1st ensure that no one else is using the DB2 system

2nd set up a datasource in CF admin to the DB2 system

3) set up tables in DB2 that mirror the tables in access

4) Determine any relationships between tables in acces  (such as if one is orders and another order details)

5) Move over any tables that are not related to the others using a simple SELECT and INSERT process

6) Select each record that does have multiple relations in other tables and write an insert to create those as new records in DB2.
This needs to be done with a loop over one record at a time to ensure that keys match back up since they may end up different in your DB2 if they were autonumber columns...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

mdbboundAuthor Commented:
Thanks Mrichmon.  I'm still working on this
mdbboundAuthor Commented:
Hi Mrichmon,

I don't know what to do, is it possible to show me an example of the solution please.

The tables/structures are already setup in DB2.  I just have to put the data that is still sitting in MSAccess.

For Example: the Appt table has
AppointID(autono)
CoID(FK)
CrID(FK)
AppointDate
TermEnd

<cfquery name="fromMSData" datasource="MSdatasource">
SELECT * from Appt
ORDER BY ApptID
</cfquery>

<cfquery datasource="ComStat">
      INSERT INTO tAppointment(
            ApptID,
            CoID,
            CrID
            AppointDate,
            TermEnd)
      VALUES #Trim(ApptID)#,
         #Trim(CoID)#,
         '#Trim(CrID)#',
         #CreateODBCDate(AppointDate)#,
         #CreateODBCDate(TermEnd)#)
      </cfquery>

Thanks
mrichmonCommented:
Basically you have it.  Just wrap the query inside a cfloop to go through all of the rows in the query pulled from access.


THe other thing as I mentioned is that if you have foreign keys or other relationships - make sure you join those tables, pull the complete records , and reinsert since the UID fields may change if they are autonumbers in access...
mdbboundAuthor Commented:
Hi Mrichmon,

What does it mean when they say "set up a native datasource"?  Would you please give an example?

Thanks
mrichmonCommented:
When who says that?  Who is the "they" and what database are we talking about the access one or the db2 one?
mdbboundAuthor Commented:
Hello Mrichmon,

My IT Dept requires me to use a native datasource, well i have been reading i think they mean to put it in the Application.cfm.  But how many datasources can you include in the Application.cfm?

Thanks.
mrichmonCommented:
I am not sure what they mean by that....
mdbboundAuthor Commented:
Hello Mrichmon,

I got it!  I created a native DB2 data source (in CFAdmin).  I do have a problem connecting though and sadly I can't find a solution.  Sometimes it connects but 80% it is not connected and it is very hard to test my queries.  The database is in linux.  Based on reading other problems posted over the Internet, it is a CFMX issue and there is no solution from macromedia.  

If you know anything, please help me.
mrichmonCommented:
Sorry I don't.....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.