Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2011
    Posts
    6

    Unanswered: DB2 Replication Problems

    Hi All,

    Before I begin, I'd like to mention that I am entirely new with IBM DB2 and it is a platform that has literally been thrown in front of me. With little knowledge, I'm trying my absolute best to muddle around but I've run into some pretty daunting challenges. I tried looking through other forum posts within the DB2 forums but I couldn't find anything pertaining to my exact problem.

    I'm hoping someone here can tell me what I'm missing, because this has literally been giving me fits for over 2 weeks now.

    Background
    Essentially the company I'm working for has an application that queries a DB2 database to generate reports. Since they have taken on a lot of new business, they've incurred a rather large increase in transactions each day which has resulted in reports being run more frequently - thus crippling their production database. This was already in place when I joined the company, however I was tasked with coming up with a better solution so this would no longer be an issue. The idea was to simply replicate their production database to another DB2 server which their daily reports and jobs could be run off of, therefore it would not affect any transactions within the production database as they would be run off the target instead.

    Hardware/Software
    DB01 (Source)
    - Windows Server 2003 Standard
    - IBM DB2 v8.1

    DB02 (Target)
    - Windows Server 2003 Standard
    - IBM DB2 v8.1

    The Issue
    Being new to DB2, I decided the first place to start was on IBM's knowledge base. I followed their basic replication tutorial found here: and was able to successfully get replication working when my test databases resided on the same server. However, as soon as I applied the same tutorial in hopes of replicating to a separate server - nothing seemed to work. This is what I tried using the Sample database:

    1. Within Control Center on DB01, I connected to DB02 so it would be catalogued and I would be able to use it in Replication Center.

    2. In Replication Center (on DB01), I created my Capture Control Tables on DB01 and my Apply Control Tables on DB02 (accepting all defaults except where noted in the DB2 tutorial mentioned above).

    3. I registered the a table (Department) on the Capture Control Server and created a subscription (DEPTCOPY) on my Apply Control Server

    4. Since I was using Replication Center on the Source (DB01), using the asnpwd utility, I created a .aut file on DB01 and made sure it was in the same bin folder as the apply program (D:\IBM\SQLLIB\BIN\)

    5. I started the capture and apply programs and made sure when starting my apply program that my authentication file was properly referenced - seemed to start with no errors.

    6. After waiting a few minutes I checked to see if anything had replicated - but no joy. I opted to look at the Apply messages and found the following:

    Code:
    "2011-08-17 11:52:45.023000","ASN1003E","APPLY ""DEPTQUAL"". The Apply program could not connect to the server ""SAMPLE"". The error code is ""450320"". The SQLSTATE is "". The SQLCODE is ""0"". The SQLERRM is "". The SQLERRP is ""."
    
    "2011-08-17 11:52:45.023000","ASN0530E","Apply"" : ""DEPTQUAL"" : The program could not connect to database ""SAMPLE"" with USERID ""N/A"" . The SQLCODE is ""-1013""."
    
    "2011-08-17 11:52:45.023000","ASN0552E","Apply"" : ""DEPTQUAL"" : The program encountered an SQL error. The server name is ""SAMPLE"". The SQL request is ""CONNECT"". The table name is ""N/A"". The SQLCODE is ""-1013"". The SQLSTATE is ""42705"". The SQLERRMC is ""SAMPLE"". The SQLERRP is ""SQLEUCCM""."
    
    "2011-08-17 11:47:45.023000","ASN1003E","APPLY ""DEPTQUAL"". The Apply program could not connect to the server ""SAMPLE"". The error code is ""450320"". The SQLSTATE is "". The SQLCODE is ""0"". The SQLERRM is "". The SQLERRP is ""."
    I tried looking up these specific error codes, but documentation is so vague and sparse. I've tried hunting all over the internet for someone who's got the exact same problem as me, but I can't seem to find a solution - nor do I even know where to start looking. Also I was wondering if it's possible to replicate the exact table names from one database to another without having to append some form of suffix in front of the table name on the target - ie.

    I have a table on DB01 called department, could I also have the same table on DB02 named department? Or does it have to be something likt tgdepartment?

    Is there anyone out there that can give me a hand?

    Thanks in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    When you created the password file, did you specify the ID and password of a user that exists on the server of DB02? And that user has sufficient permissions to connect to the database?

    Try connecting to DB02 from the command line on DB01.

    And yes, you can have the same table names on the target as you have on the source.

  3. #3
    Join Date
    Aug 2011
    Posts
    6
    I was using the db2admin account I created as I figured that would have access to everything I needed...

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    using the asnpwd command you can check/list the file and all entries
    check if any problem - did you use the default name ? otherwise apply needs to know about this name..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Aug 2011
    Posts
    6
    Like I mentioned above, I used the db2admin account as I know it has administration access to both databases on both servers. I was sure to include it in the .aut file on the server that is running Replication Center (DB01) - but I`m still seeing the errors.

    I tried to connect to the remote database on DB02 via command line and was able to do so and list tables and insert values into them. I see the replicated tables in there but they haven`t been populated with any of the replication data and I still see the aforementioned errors in the Apply Messages section (when selecting to view errors).

    At this point, is there anyone who could provide me with a practical guide to getting this off the ground? As I mentioned before, this has been on my plate for weeks and I`m beyond frustrated with it, it`s just not making sense

  6. #6
    Join Date
    Aug 2011
    Posts
    6
    ... and also aside from the fact that I am a confessed newbie at this and really am over my head entirely.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    SQLCODE -1013 means the alias "SAMPLE" is not known. Can you post the output from "db2 list node directory" and "db2 list db directory"? If your actual source and target database names are DB01 and DB02, then somewhere in your configuration you are still using SAMPLE as the target.

  8. #8
    Join Date
    Aug 2011
    Posts
    6
    DB01 and DB02 are the names I assigned my actual DB2 servers as.

    When I was playing with replication between the two individual hosts, I was using the SAMPLE database (provided when you run the First Steps program). I didn`t want to goof around with a production database, so I opted to make this work with the SAMPLE before going forward with production.

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    try to start apply with trace option you will get more information about which user.. is being used...
    all details are over here
    SQL replication
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Aug 2011
    Posts
    6
    Honestly guys, thank you so much for the suggestions - you've given me some ground work to look into and I really appreciate the effort and the quick responses.


    So thank you very much for your patience and contribution n_i and przytula_guy. I'll give these tips a try and report back with my results.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •