sync db2 version 8 database that is remote!!

we are having problem in trying to sync data between 2 ver8 db2 databases as the other DR database is in west coast, we are in east coast and always the data sync is dependent on network and it takes several hours to complete..We do a export and import db2 uitility

i want to know the following
1. what are the other options available that we can use
2. is there anyway we could avoid network issues for a solution..

Thanks!!
mahjagAsked:
Who is Participating?
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.

momi_sabagCommented:
hi

when you want to decide how to update your remote database, you need to define first what kind of function do you want it to perform (for example - disaster recovery only or do you want users to be able to select data from it, or to update it)
the second thing you need to decide is how much latency time you are whiling to have, or in other words, what is the greatest time difference you allow between the databases (for example, you can have a 6 hour difference between them)

every solution that  you will chose will depend eventually on the network since you won't be moving files physically with a delivery service, so what you want to do is actaully minimize the traffic over the network,
the best solution in order to achive this is to use db2 replication
replicatio allows you to shift to the other database only the changes that happen since the last time you syncronized the two databases, as appose to the export import method you are using which forces you to ship the entire tables every time
the replication solution will probably provide you with the minimum latency between the databases and will have minimum network usage

if you want to have a stand by database, that is a database that no one works with, you can just shift the logs to that server, and constantly roll it forward, keeping it up to date with the original database

i hope i was clear

momi
0
mahjagAuthor Commented:
we have to sync every day as it is DR database and also the data is real time, could you explain more about db2 replication will that help!! This data is not for update but users will query real time to see their reports and hence need to be updated, we have a problem in latency that the users will never see the data until the sync completes and it takes a lot of time now...

Hope this explains the problem!!
0
momi_sabagCommented:
db2 replication is a mechanims in which there are 2 components
a capture component in the source environment which tracks the tables you ask for, and every time something changes in one of these tables, it writes it to a special save area (can be a table, an mq queue etc)
the second component is called apply and it runs in the target envrionment,
it reads the data that the capture component wrote, and apply it (actually this data is the changes that happened in the source envrionement since the last apply) in the target system

this is basically how it works
you can define at the table level which table you want to replicate

0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

ghp7000Commented:
yes, replication is what you are looking for, either of the two solutions proposed by momi will be good. You will have to read up on replication extensivley to put the system in place.
However, you say that your remote db is 'real time', but that cant be true. Real time means it is up to date at the time the users is using it, but your remote db is real time to a 'point in time', the point in time being the last time you imported the files.
If you want to speed up your current process, do not use import to load the exported files, use load instead, it is much faster. The other thing you can do is export the tables according to some criteria (like last timestamp for a certain day) or any other table criteria which you can isolate so that you are only exporting rows that have not been prevoiously exported. This will reduce the size of the files and the time it takes to load them. Lastly, when moving files across the network, I would suggest you zip them all first and send the zip file only, then unzip at the receiving end
0
mahjagAuthor Commented:
we have count load of tables that needs the sync, do we have to setup replication on each of the tables?

Is there any other option available for sync?
0
mahjagAuthor Commented:
Hi ghp7000

what is the difference between load and import? can you explain how load is faster?
0
ghp7000Commented:
see appendix B of Data Movement Utilities Guide and Reference for differences between load and import
note that load is really only faster if you are inserting large amounts of data, if your tables are small, import will be faster and load will be slower. Another consideration is your system, if its MPP system forget about import and use load exclusively, unless your tables have less than 1000 rows, then use import.By the way, what does 'we have count load of tables' mean?
0
mahjagAuthor Commented:
Whtat is MPP system means?

I said count load of tables that I mean there are about 10 tables to sync and there are about average 500.000 rows on all of them..
0
momi_sabagCommented:
massively parallel processing (MPP)
 The coordinated  execution of a single request either by multiple single-processor computers  in a shared-nothing environment (in which each computer has its own memory and disks); or by symmetric multiprocessor (SMP) computers (in which multiple processors in each computer share memory and disks). Both environments require that all computers are linked together in a high-speed network.

or in simple words, when you have more than one computer running with the same db2 database, and the request you make for the database are processed in parallel

if you have 10 tables maybe setting up replication will be too much work for you
you can just add an update_timestamp column to these tables, and have a table that has the latest_timestamp that you sync
every time you want to move the data you can just select it from the tables using the update timestamp  and then send it to the other db
the problem with this solution (and the problem in ghp7000's  solution) is that
it is only good for inserts and updates
if a row gets deleted from the source table, you won't be able to detect it and delete it in the target
that will require a more sophisiticated solution
0
mahjagAuthor Commented:
Thanks momi_sabag

Yes the data that I need to sync needs to have the integrity meaning if source got deleted then the destination db should also be deleted, the reason being if customers are connected to DR when prod crashes they should see the same data that they saw few minutes back.,

I checked with dba and they said they do not have licenses for db2 replication and they said only thing we can try is direct load, not sure if that will help!!
0
momi_sabagCommented:
you can try to implement the replication on your own
it's not that complicated
all you have to do is :
define 3 trigger on each table - after insert, after delete, after update
and save the row that was affected and the operation code (if it was delete,insert,update) and the timestamp
then you need to write a program that will get data from that table and apply it in the target system, and after you applied it in the target system, delete it from the staging area
you can read how replication works and it would be much clearer
0
mahjagAuthor Commented:
I need some help to try direct load option, I have the script written in unix to import and export files from the source and destination and I wanted to remove this import to destination to a direct load, how do I start with..Thanks a lot all for your helpful suggestions!!
0
momi_sabagCommented:
hi
here you can find the syntax of the load command
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/core/r0008305.htm

do you have any specific questions ?
0
mahjagAuthor Commented:
Thanks for the link , here is my situtation , I have import from db2 source database to a flat file comma delimited and import command to destination db2 database

db2 import from $MyFold/output/output.del of del commitcount 500 replace into <destination table name>

how do I  change this to load command ..

I want to test to see if it works, we use to increase the commit count to get better results but still it is very very slow.......Thanks!!
0
momi_sabagCommented:
db2 load from $MyFold/output/output.del of del savecount 500 replace into <destination table name>

you probably need to change some other parameters but this can get you started
0
mahjagAuthor Commented:
Thanks Momi Sabag
I am waiting for dba access to do db2 load, meanwhile I wanted to get your opinion about performance enhancement parameters to be considered for db2 load.. let me know if you can share your experience on this as I have never tried, I looked up documentation it had some things but would like to consider what has been already worked!!
0
momi_sabagCommented:
well
one important thing is not to set the commit frequency too low
right now you are commiting every 500 rows, which is ok
but i would test to see if you can set it higher, as commit is a syncronious action that blocks the calling process until it finishes,
if i were you i would have checked what is the probability the load process would abend and set the commit interval higher.
on the other hand, don't set it to 99999
that won't be good either
you can just experiment with that and see where you get the best performance
0
mahjagAuthor Commented:
Hi Momi sabag

I tried this

db2 load from output.del of del messages msg.txt replace into <destination table name>

I got
SQL3025N  A parameter specifying a filename or path is not valid.

I did not use any path name in my file, I copied the from file in the same directory where I am executing..

Any ideas..
0
mahjagAuthor Commented:
same file worked for db2 import I just replace import to load, any ideas?
0
mahjagAuthor Commented:
BTW the script is being run in SUN OS and is a ksh file,
0
mahjagAuthor Commented:
I figured this out as it need client keyword before from statement, I needed some ideas to built exception table, as the table that I am writing to has unique index and I want to evaluate what rows failed after the load is done.., any suggestions?
0
momi_sabagCommented:
you can read about exception tables here
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0001111.htm
basically you can just use
create exception_table like original_table

that should do the job
0

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
mahjagAuthor Commented:
Hello momi sabag

I know you are helping me a lot here, I also have another question related, for load I have to give the absolute path in the syntax as in unix folder for where my source file is residing, this will not work in production as I have to hardcode the path, is there a solution to this, I am refering to

db2 load client from $Myfolder/output/<source file>  -I get SQL3508N  Error in accessing a file or path
instead I have to do this

db2 load client from /var/users/mahjag/output/<source file> - this works ..

any ideas?
0
momi_sabagCommented:
what does $Myfolder contains ?
can you post the output of
echo $Myfolder/output/<source file> ?

maybe you should try
db2 load client from `$Myfolder`/output/<source file>  
0
mahjagAuthor Commented:
$Myfolder = /var/users/mahjag

I tried `Myfolder`

I still see the same error

SQL3508N  Error in accessing a file or path
0
momi_sabagCommented:
try `$Myfolder`
0
mahjagAuthor Commented:
It worked now without tilda, I did not set env path for $Myfolder.. my mistake.. I have another error in the script I use simple if then else and I am getting else unexpected error.. here is the code snippet..
db2 load client from `$Myfolder`/output/<source file>  replace into <dest table>
if [$? -gt 0 ] then
              echo "Fatal Error on load to destination table"
 else
              echo "Load completed successfully"
fi

what is wrong?
0
mahjagAuthor Commented:
I figured out this myself, I need semicolon if I have if statement and then in the same line,,Thanks !!
0
mahjagAuthor Commented:
Thanks momi_sabag

I was taking some time to test the solution and I did create exception table simiar to the table that I am loading but added timestamp column to see when a problem occur,

I want this field to populate date time when a problem occurs how do I default it?
0
momi_sabagCommented:
try to
alter table mytable alter column_name with default current timestamp
0
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
DB2

From novice to tech pro — start learning today.