DB2 Export and Import Oracle

 
Thread Tools Search this Thread
Homework and Emergencies Homework & Coursework Questions DB2 Export and Import Oracle
# 1  
Old 01-20-2017
DB2 Export and Import Oracle

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted!

1. The problem statement, all variables and given/known data:
is this enough to make the data perfect export into delimited file? there are some posted that i read, they included the table field names.

what is the different of this 2 (with table field names and without?

can anyone suggest for the import script into oracle?



2. Relevant commands, code, scripts, algorithms:
Code:
db2 connect to Tablename user id using psswrd

db2 "EXPORT TO '/cardpro/brac/v5/dev/dat/AAAAA.DEL' OF DEL select * FROM AAAAA"
db2 "EXPORT TO '/cardpro/brac/v5/dev/dat/BBBBB.DEL' OF DEL select * FROM BBBBB"
db2 "EXPORT TO '/cardpro/brac/v5/dev/dat/CCCCC.DEL' OF DEL select * FROM CCCCC"
...
.. 

db2 terminate


3. The attempts at a solution (include all code and scripts):
Code:
db2 connect to Tablename user id using psswrd

db2 "EXPORT TO '/cardpro/brac/v5/dev/dat/AAAAA.DEL' OF DEL select * FROM AAAAA"
db2 "EXPORT TO '/cardpro/brac/v5/dev/dat/BBBBB.DEL' OF DEL select * FROM BBBBB"
db2 "EXPORT TO '/cardpro/brac/v5/dev/dat/CCCCC.DEL' OF DEL select * FROM CCCCC"
...
.. 

db2 terminate


4. Complete Name of School (University), City (State), Country, Name of Professor, and Course Number (Link to Course):
Xavier university, Philippines, lincaro, computer engineering

Note: Without school/professor/course information, you will be banned if you post here! You must complete the entire template (not just parts of it).


Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by RudiC; 01-20-2017 at 05:56 AM.. Reason: Added CODE tags.
# 2  
Old 01-20-2017
You might be able to get the data into Oracle in a number of ways.
  • The Oracle command sqlldr to load the data from an input file
  • The Oracle command imp if the file format is compatible with Oracle exports (usually written with Oracle command exp)
  • Use the file as a table directly from the database. You would use CREATE DIRECTORY, however this requires elevated privileges and is a risk because the file is left in plain sight. You could mitigate against this by subsequently running a CREATE TABLE new_table AS SELECT * FROM my_temporary_table ; and then dropping the plain file on disk.
  • If the file is not too large, read the file use a shell script and run a set of commands:-
    • CREATE TABLE with the correct structure
    • INSERT statements for each record of the input file
    • CREATE INDEX if appropriate
You might get away with writing to/reading from a pipe rather than a regular file. The command mknod p /path/to/file will create the pipe. It's a special type of file that is a sort of buffer for data. If you then start your importing process (one of the first two options) reading it and then in another session run the export to it, you might not only save disk space but you will be doing both in parallel so you will save on the elapse time, i.e. 30 minutes to export plus 40 minutes to import (harder work for the database) might become 42 minutes for both together.

Of course this is dependant on the volume of data, disk structure (contention) and CPU load to a small extent however if these databases are on two separate servers, you can extend this trick even more. Consider the process actually requires three steps totalling 90 minutes:-
  1. Export - 30 minutes
  2. File transfer - 20 minutes
  3. Import - 40 minutes
You can:-
  1. Create pipe for export
  2. Create pipe for import
  3. Start import
  4. Start propagation process (I've used a dd & ssh pipeline but there are other options)
  5. Start export
You might find that this all runs in 40 minutes because there is less disk contention on the import side. Of course, it's just theory and is entirely dependant on what hardware is in play and what contention there actually is at the time.


Robin
This User Gave Thanks to rbatte1 For This Post:
# 3  
Old 01-20-2017
Thank you very much Robin (as rbatte1), I appreciated your replied.
# 4  
Old 01-20-2017
Another option would be to use oracle heterogeneous connectivity to connect to DB2. Use an ERD tool like Erwin to reverse engineer the DB2 database and convert it to Oracle. Then create the new tables in Oracle without indexes, constraints or triggers, then you can just run insert into table as select commands to directly move your data to the new tables. Then generate the script for indexes and constraints and write any triggers and or stored procedures that you need. Finally automate the process and do many test runs.
This User Gave Thanks to gandolf989 For This Post:
# 5  
Old 01-24-2017
Thanks gandolf989

---------- Post updated at 01:25 PM ---------- Previous update was at 01:17 PM ----------

Hi All,

Anyone please help? I run my import script in unix but im not sure why its running to long? so I cancel the job.

can anyone tell me what is the problem of my script?

Code:

Code:
sqlldr userid/psswrd control=/export/home/user/Import_Script/Table.ctl


Code:
LOAD DATA
INFILE '/export/home/user/Export_Data/XTABLE.DEL'
APPEND
INTO TABLE CP_TABLE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
( TABLE_NO, TABLE_NO_CARD, TABLE_OPEN_DATE, TABLE_AUTO_STOP, TABLE_MOD_DATE, TABLE_USER_ID, CHECKSUM )


my data (XTABLE.DEL)

Code:
+01.,+00000002.,"20130101","N","        ","root      "," "
+02.,+00000001.,"20130101","N","        ","root      "," "
+03.,+00000001.,"20130101","N","        ","root      "," "
+04.,+00000001.,"20130101","N","        ","root      "," "
+05.,+00000003.,"20130101","N","        ","root      ",""
+06.,+00000001.,"20130101","N","        ","root      "," "
+07.,+00000000.,"20130101","N","        ","root      "," "
+08.,+00000001.,"20130101","N","        ","root      "," "
+09.,+00000001.,"20130101","N","        ","root      "," "
+10.,+00000001.,"20130101","N","        ","root      "," "
+11.,+00000001.,"20130101","N","        ","root      "," "
+12.,+00000001.,"20130101","N","        ","root      "," "
+13.,+00000001.,"20130101","N","        ","root      "," "
+14.,+00000000.,"20130101","N","        ","root      "," "
+15.,+00000001.,"20130101","N","        ","root      ",""

do i need to add commit in my script? is it require?

---------- Post updated at 03:33 PM ---------- Previous update was at 01:25 PM ----------

Thank you Don Cragun.. That things will guide me for my next problem encounter.

Last edited by Don Cragun; 01-24-2017 at 02:42 AM.. Reason: Add CODE tags.
# 6  
Old 01-24-2017
The import will probably take longer than a plain export because it is writing. Not only does this require the data to be committed (i.e. to disk, not a query commit) but this will write to redo logs, perhaps extend tables etc. beware that cancelling your loader with do one of two things:-
  • Commit an incomplete load (which you might need to tidy up)
  • Roll-back, which can also take a long time.

For clarity,
  • is this an extraordinarily long delay?
  • can you still connect to the database with another session?
It might be that the redo/undo areas are full or if redo logs are moved to disk, the filesystem that holds them might be full. Have a look at the oracle logs to see if that's the case.


The sqlldr will commit at the end anyway. I think you can use the parameter ROWS=1000 or a suitable number on sqlldr to force more regular commits, at which point you can use another session to count the rows imported into your table so far.



Does that help?
Robin
# 7  
Old 01-24-2017
Sonny,

How long is too long to run? How many indexes do you have on the table? How many constraints, including foreign keys do you have? How many row level triggers do you have and what do they do? How many rows are you inserting? Can you use grid to trace what is happening in the import?

I have not seen enough information to give you a meaningful answer. Also this seems like a question better served on an Oracle Forum than a Unix/Linux forum.
This User Gave Thanks to gandolf989 For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Array - Export/Import in global environment variables.

Hello. During startup /etc/bash.bashrc.local generates some array ..... source /.../.../system_common_general_array_env_var ..... The file system_common_general_array_env_var contains : LEAP_VERSION='42.3' ARRAY_MAIN_REPO_LEAP=('zypper_local' 'openSUSE-Leap-'"$LEAP_VERSION"'-Non-Oss' ... (2 Replies)
Discussion started by: jcdole
2 Replies

2. Homework & Coursework Questions

Oracle dump file (del format) import into db2

1. The problem statement, all variables and given/known data: are the oracle dump files compatible to direct import into db2? I already tried many times but it always truncated results. anyone can help/ advice or suggest? 2. Relevant commands, code, scripts, algorithms: exp... (3 Replies)
Discussion started by: Sonny_103024
3 Replies

3. Homework & Coursework Questions

DB2 Export and Import Oracle

Hi Guys, I Just wanted your opinion/ suggestion/ Help on my unix script about db2 export data with deli file and import into oracle. db2 connect to Tablename user id using psswrd db2 "EXPORT TO '/cardpro/brac/v5/dev/dat/AAAAA.DEL' OF DEL select * FROM AAAAA" db2 "EXPORT TO... (3 Replies)
Discussion started by: Sonny_103024
3 Replies

4. Solaris

Zpool import/export error

A backup/clone script of ours was recently ran. It normally only clones the rpool and renames in rpoolA. Something must've changed as it found another one of our pools that it shouldn't have. It exported that pool unbeknownst to us. Later on when a coworker realized the other pool was missing he... (2 Replies)
Discussion started by: beantownmp
2 Replies

5. UNIX for Dummies Questions & Answers

Import and export PGP/GnuPG keys

Hi, I need to export an existing PGP key and import it into GnuPG on a different machine. This is how I did the export: pgp -kx myuser _myuser_public pgp -kx myuser _myuser_private secring.skr (this is from the pgp installation directory that contains secring.skr). This produced two... (0 Replies)
Discussion started by: imchi
0 Replies

6. Shell Programming and Scripting

export / import

I have a calling script which consists of calls to other scripts via the sh command. ie vi callscript.sh sh smallscript1.sh extra unix commands sh smallscript2.sh exit In smallscript1, I prompt for a filename, which I handle via :- read f1 export f1 I then need... (5 Replies)
Discussion started by: malts18
5 Replies

7. Shell Programming and Scripting

Error: SQL3100W while export in DB2

Problem has been resolved (7 Replies)
Discussion started by: ustechie
7 Replies

8. Shell Programming and Scripting

manipulate text for openldap import/export question.

Hey guys.. I am not sure if this is the right place to post this - but here goes. I need to manipulate an openldap export to match a different schema so that I can import into that system. Basically - its just text manipulation. I have gotten alot of it done just by using simple sed, but I am sorta... (0 Replies)
Discussion started by: i2ambler
0 Replies

9. Solaris

Export/import ZFS ACL's

I've been wondering about this one, is there any way to do the following with ZFS ACL's (i.e. "copy" the ACL over to another file)? getfacl /bla/dir1 | setfacl -f - /bla/dir2 I know about inheritence on dirs, it doesn't work in this scenario I'm working on. Just looking to copy the ACL's. ... (3 Replies)
Discussion started by: vimes
3 Replies

10. Filesystems, Disks and Memory

Local NFS import / re-export using automount?

Hi, Can I do the following: On SunOS 5.8 /etc/vfstab: remote-host:/Volumes/webdata - /export/home/webdata nfs - yes rw,vers=3,soft,intr,bg,timeo=600 In /etc/auto_direct: /home/science $HOST:/export/home/webdata/science /home/science-edu ... (2 Replies)
Discussion started by: bloyall
2 Replies
Login or Register to Ask a Question