Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2012
    Posts
    13

    Unanswered: Row filter in DB2 replication

    Dear all,
    I would like to check whether the following row filter is valid for the APPLY program in DB2 replication.

    (Col1, Col2) IN (SELECT ColA, ColB, ColC FROM SCHEMA.TableA WHERE ColD IN ('Value1', 'Value2') OR ColE IN ('Value1', 'Value2'))

    I noticed that when I add the above for row filter in one of the replication table, replication will not work although APPLY program can be started up. And the above is a valid SQL where I have tested it on the source table. Does anyone have any idea how should I put the above condition as the replication criteria.

    p.s: I am using DB2 Replication Center to setup replication.

    Thanks in advance !!

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    (Col1, Col2) IN (SELECT ColA, ColB, ColC.. --> 2 versus 3
    have you tried to compare 2 cols with 2 cols from select..
    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

  3. #3
    Join Date
    Mar 2012
    Posts
    13
    Sorry, it should be:

    (Col1, Col2, Col3) IN (SELECT ColA, ColB, ColC FROM SCHEMA.TableA WHERE ColD IN ('Value1', 'Value2') OR ColE IN ('Value1', 'Value2'))

    Whenever I add the above row filter, the APPLY will stop replicating data to all target tables. If I take out then APPLY works. As mentioned, there is no error message and the APPLY program can be started up but it's not replicating.

    Thanks in advance !!

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    try activating the trace for apply and see in tracefile what message is returned
    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
    Mar 2012
    Posts
    13
    Hi,
    I attached herewith two trc logs. "apply_trc_success.txt" is the log before I add in the row filter whereas "apply_trc_failed.txt" is the one after applying the mentioned row filter. I also noticed the following error in apply log when I add in the mentioned row filter:

    :
    :
    2012-07-18-17.01.04.322000 <CPCIMPC(08/07)> ASN1045I APPLY "SGNQUAL" : "Initial" : The Apply version "9.1.0" program was started using database "R7DB2SGN".
    2012-07-18-17.01.04.947000 <CPGPTST(4A/02)> ASN1051W APPLY "SGNQUAL" : "WorkerThread" : The Apply program detected a gap in changed data between the source table "DB2ADMIN.CRS500BM" and the target table. The error code is "4A5102".
    2012-07-18-17.01.43.104000 <CPUSRX(72/0A)> ASN1053E APPLY "SGNQUAL" : "WorkerThread". The execution of the ASNLOAD exit routine failed. The return code is "109".
    2012-07-18-17.01.43.432000 <CPREST(01/00)> ASN1044I APPLY "SGNQUAL" : "WorkerThread". The Apply program will become inactive for "5" minutes and "0" seconds.

    and subsequently below error in asnload trace:

    *** ASNLOAD ERROR ***: SQL0973N Not enough storage is available in the "UTIL_HEAP_SZ" heap to process the statement. SQLSTATE=57011

    FYI, the setting for UTIL_HEAP_SZ is 319534 which should be big enough.

    p.s: You may refer to the affected table called CRS500BM in the log file.

    Thanks again for your assistance.
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2012
    Posts
    13
    Hi,
    I also noticed the following errors logged in ASNLOAD trace file:

    SQL3022N An SQL error "-204" occurred while processing the SELECT string in the Action String parameter.

    SQL3022N An SQL error "-10" occurred while processing the SELECT string in the
    Action String parameter.

    SQL3022N An SQL error "-104" occurred while processing the SELECT string in the Action String parameter.

    SQL3022N An SQL error "-412" occurred while processing the SELECT string in the Action String parameter.

    Can I say that the row filter SQL is invalid thus causing the above errors.

    Thanks in advance !!

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    there is a message that you have a gap and trying todo full refresh - problem with the full refresh is also indicated.
    try todo a manual full refresh and start the trace again
    104 syntax error : check the generated sql
    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

  8. #8
    Join Date
    Mar 2012
    Posts
    13
    OK thanks..I will try to do a manual full refresh then. About the syntax, I have verified and confirmed that the SQL is correct and I was able to run the same SQL against the source table from DB2 prompt. But if I put it as row filter, it failed. You may refer to the attached ASNLOAD trace file and look for table name CRS500BM. Thanks for your support !!
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    which is your schema ?
    "DB2ADMIN"."CRS500BM" WHERE ( (BMSS
    TN, BMBKNO) IN (SELECT BCSSTN, BCBKNO FROM CRS578BC...
    some tables are qualified some not.....
    204 = object notfound
    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
    Mar 2012
    Posts
    13
    Schema is "DB2ADMIN" and all the tables are under the same schema. Since I am running the APPLY program with userid "DB2ADMIN", is it a must that I have to specify the schema name before table name. If you refer to my row filter, I did specify the schema in my statement as below:

    ((BMSSTN, BMBKNO) IN (SELECT BCSSTN, BCBKNO FROM DB2ADMIN.CRS578BC WHERE BCCUSTCD IN ('SGVCASIND','SGRTLSINR') OR BCCOMPCD IN ('SGVCASIND', 'SGRTLSINR')))

    Maybe you were referring to the older log entry where I didn't specify the schema name.

  11. #11
    Join Date
    Mar 2012
    Posts
    13
    I am getting the following message in asnload trace after doing a manual full refresh:

    SQL2043N Unable to start a child process or thread

Posting Permissions

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