Solved

sync db2 version 8 database that is remote!!

Posted on 2007-04-04
30
585 Views
Last Modified: 2011-08-18
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!!
0
Comment
Question by:mahjag
  • 17
  • 11
  • 2
30 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18855869
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
 

Author Comment

by:mahjag
ID: 18857506
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18857640
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
 
LVL 13

Expert Comment

by:ghp7000
ID: 18857971
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
 

Author Comment

by:mahjag
ID: 18857972
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
 

Author Comment

by:mahjag
ID: 18858001
Hi ghp7000

what is the difference between load and import? can you explain how load is faster?
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 18859694
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
 

Author Comment

by:mahjag
ID: 18859755
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18860869
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
 

Author Comment

by:mahjag
ID: 18861401
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18861445
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
 

Author Comment

by:mahjag
ID: 18863752
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18864648
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
 

Author Comment

by:mahjag
ID: 18864721
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18864783
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:mahjag
ID: 18878886
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18880938
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
 

Author Comment

by:mahjag
ID: 18891590
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
 

Author Comment

by:mahjag
ID: 18891599
same file worked for db2 import I just replace import to load, any ideas?
0
 

Author Comment

by:mahjag
ID: 18891656
BTW the script is being run in SUN OS and is a ksh file,
0
 

Author Comment

by:mahjag
ID: 18892868
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
 
LVL 37

Accepted Solution

by:
momi_sabag earned 125 total points
ID: 18893744
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
 

Author Comment

by:mahjag
ID: 18897893
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18897967
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
 

Author Comment

by:mahjag
ID: 18898074
$Myfolder = /var/users/mahjag

I tried `Myfolder`

I still see the same error

SQL3508N  Error in accessing a file or path
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18898140
try `$Myfolder`
0
 

Author Comment

by:mahjag
ID: 18898248
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
 

Author Comment

by:mahjag
ID: 18898383
I figured out this myself, I need semicolon if I have if statement and then in the same line,,Thanks !!
0
 

Author Comment

by:mahjag
ID: 18932056
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18932145
try to
alter table mytable alter column_name with default current timestamp
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now