Topic
  • 4 replies
  • Latest Post - ‏2013-05-01T13:43:52Z by LinhLe
LinhLe
LinhLe
8 Posts

Pinned topic Cannot mapping table from AS 400 to Oracle database

‏2013-04-26T07:45:03Z |

Dear all,

I have an issue when I'm working with CDC, like the title of the topic.

 

I have some environment information:

  • Access Server, Management Console, InfoSphere CDC for Oracle trigger: installed in Windows Server 2008 R2
  • CDC Agent: installed in AS 400 (iSeries), V5R3 version.

I also create Oracle Instance, started in 11001 port.

I logged in Management Console, create Datastore for AS 400 (port 2222).

I can make subscriptions and following issue here:

  1. Source: AS 400, Target: AS 400: ok.
  2. Source: Oracle, Target: Oracle: ok.
  3. Source: Oracle, Target: AS 400: ok.
  4. Source: AS 400, Target: Oracle: failed.

 

The error show in case 4 is: 

 

Updating the tables available for replication...

Notifying target of changes to the subscription...

Locking TEST_ERROR on target...

Reloading table mappings...

Mapping source table AAA [1/1]...

 

ERROR:

Could not complete the mapping for target table AAA because the source table is missing on the target datastore. This may be caused by an incomplete describe. Check the event log for related events and a possible cause.

When I checked Event Log of the Subscription in Management Console, I saw some message here: Failed to connect to Communication Line, error code = 4.

When I checked Event Log of the DataStore in Management Console, some messages here:

  • Failure to connect to remote TCP/IP port, error code = 3425.
  • Source Communication job failed to establish link.
  • Communication link to LOCALHOST:11001 abnormally terminated by local system

 

Anyone help me with this issue?

Thanks so much!!

  • Anil_Abraham
    Anil_Abraham
    3 Posts
    ACCEPTED ANSWER

    Re: Cannot mapping table from AS 400 to Oracle database

    ‏2013-04-26T18:48:40Z  

    What is your table structure in Source & Target  , do you have any LOB/CLOB/BLOB kind of columns ?   If you do then unmap those and try it.

    What is your System parameters in Source & Target  ?

    Also Check your Oracle Host name is added in the  iSeries  .

    3425  error relates to various errors , Duplicates /Comm failure / etc.,  Unless I have the Job log and the trace info for the target I can only assume the above.

     

  • Rphilo
    Rphilo
    391 Posts
    ACCEPTED ANSWER

    Re: Cannot mapping table from AS 400 to Oracle database

    ‏2013-04-30T04:59:53Z  
    • LinhLe
    • ‏2013-04-27T02:03:34Z

    Thanks for your reply.

    The table has only one field (Name varchar2(255)) and don't have any LOB columns.

    Also, I have already add Oracle Hostname in the iSeries by using ADDTCPHTE command.

    What is the System parameters in Source & Target that you can suggest me?

    Where I can see the Job log and trace for detail?

     

    Thanks for your help.

    Hi

    When you configured the Oracle datastore in the access manager what host name did you use? This hostname will be used on the AS/400 to connect to the Oracle server. You can verify that the host name is working:

    1) Log on to the iSeries

    2) Execute STRSQL to start a SQL Session

    3) Enter the statement SELECT TGTSYSNME, TCPRMTNME FROM <prodlib>/dmfs505p

    <prodlib> is the name of the library where you installed CDC on the iSeries

    4) The query will show the subscriptions you have created and the target host name

    5) F3 to exit

    6) Enter the command PING <hostname>

    where <hostname> is the remote hostname you got in the query for the Oracle subscription

    To answer your other questions:

    To get the job logs

    1) On the AS/400 execute the command WRKJOB <subscription>

    where <subscription> is the subscription in question

    2) You will have a list of jobs

    3) Enter 1 against the first one

    4) Enter 4 to work with spool files

    5) Enter 5 to display the QPJOBLOG file

    6) If the screen you are using is only defined to 80 chars width,enter w35 in the control field to scroll to the right to see the whole messages

    7) F3 to exit

    8) If you need to look at more job logs for your subscription, press F9 enter and repeat the process

    Trace files

    Trace files can be obtained on the Oracle side:

    1) In Management console select the configuration perspective

    2) Click on the Oracle datastore

    3 Select properties, then system values

    4) If you are using CDC 6.5.x

                Add the parameter global_trace_until

                Enter the value as the date you would like to end tracing in mm/dd/yy format - I would suggest the next day e.g.05/01/13

    5) If you are using IIDR 10.2

                locate the parameter global_trace_hours

                modify the current value 0 to say 6 t- keep tracing on for the next 6 hours

    6) Go into <CDC home directory\instance\instance name\log directory to view trace files. I would do this in Windows explorer and sort by date as the file names are not in any sequence.

    7) If you want to turn tracing off before the expiry,modify the global_trace_util to a past date or the global_trace_hours back to 0

    8) Note that unlike most datastore parameters, the parameters are changed dynamically, i.e no need to restart replication or instance for the new setting to take effect.

    regards

    Robert

     

     

     

     

  • Anil_Abraham
    Anil_Abraham
    3 Posts

    Re: Cannot mapping table from AS 400 to Oracle database

    ‏2013-04-26T18:48:40Z  

    What is your table structure in Source & Target  , do you have any LOB/CLOB/BLOB kind of columns ?   If you do then unmap those and try it.

    What is your System parameters in Source & Target  ?

    Also Check your Oracle Host name is added in the  iSeries  .

    3425  error relates to various errors , Duplicates /Comm failure / etc.,  Unless I have the Job log and the trace info for the target I can only assume the above.

     

  • LinhLe
    LinhLe
    8 Posts

    Re: Cannot mapping table from AS 400 to Oracle database

    ‏2013-04-27T02:03:34Z  

    What is your table structure in Source & Target  , do you have any LOB/CLOB/BLOB kind of columns ?   If you do then unmap those and try it.

    What is your System parameters in Source & Target  ?

    Also Check your Oracle Host name is added in the  iSeries  .

    3425  error relates to various errors , Duplicates /Comm failure / etc.,  Unless I have the Job log and the trace info for the target I can only assume the above.

     

    Thanks for your reply.

    The table has only one field (Name varchar2(255)) and don't have any LOB columns.

    Also, I have already add Oracle Hostname in the iSeries by using ADDTCPHTE command.

    What is the System parameters in Source & Target that you can suggest me?

    Where I can see the Job log and trace for detail?

     

    Thanks for your help.

  • Rphilo
    Rphilo
    391 Posts

    Re: Cannot mapping table from AS 400 to Oracle database

    ‏2013-04-30T04:59:53Z  
    • LinhLe
    • ‏2013-04-27T02:03:34Z

    Thanks for your reply.

    The table has only one field (Name varchar2(255)) and don't have any LOB columns.

    Also, I have already add Oracle Hostname in the iSeries by using ADDTCPHTE command.

    What is the System parameters in Source & Target that you can suggest me?

    Where I can see the Job log and trace for detail?

     

    Thanks for your help.

    Hi

    When you configured the Oracle datastore in the access manager what host name did you use? This hostname will be used on the AS/400 to connect to the Oracle server. You can verify that the host name is working:

    1) Log on to the iSeries

    2) Execute STRSQL to start a SQL Session

    3) Enter the statement SELECT TGTSYSNME, TCPRMTNME FROM <prodlib>/dmfs505p

    <prodlib> is the name of the library where you installed CDC on the iSeries

    4) The query will show the subscriptions you have created and the target host name

    5) F3 to exit

    6) Enter the command PING <hostname>

    where <hostname> is the remote hostname you got in the query for the Oracle subscription

    To answer your other questions:

    To get the job logs

    1) On the AS/400 execute the command WRKJOB <subscription>

    where <subscription> is the subscription in question

    2) You will have a list of jobs

    3) Enter 1 against the first one

    4) Enter 4 to work with spool files

    5) Enter 5 to display the QPJOBLOG file

    6) If the screen you are using is only defined to 80 chars width,enter w35 in the control field to scroll to the right to see the whole messages

    7) F3 to exit

    8) If you need to look at more job logs for your subscription, press F9 enter and repeat the process

    Trace files

    Trace files can be obtained on the Oracle side:

    1) In Management console select the configuration perspective

    2) Click on the Oracle datastore

    3 Select properties, then system values

    4) If you are using CDC 6.5.x

                Add the parameter global_trace_until

                Enter the value as the date you would like to end tracing in mm/dd/yy format - I would suggest the next day e.g.05/01/13

    5) If you are using IIDR 10.2

                locate the parameter global_trace_hours

                modify the current value 0 to say 6 t- keep tracing on for the next 6 hours

    6) Go into <CDC home directory\instance\instance name\log directory to view trace files. I would do this in Windows explorer and sort by date as the file names are not in any sequence.

    7) If you want to turn tracing off before the expiry,modify the global_trace_util to a past date or the global_trace_hours back to 0

    8) Note that unlike most datastore parameters, the parameters are changed dynamically, i.e no need to restart replication or instance for the new setting to take effect.

    regards

    Robert

     

     

     

     

  • LinhLe
    LinhLe
    8 Posts

    Re: Cannot mapping table from AS 400 to Oracle database

    ‏2013-05-01T13:43:52Z  
    • Rphilo
    • ‏2013-04-30T04:59:53Z

    Hi

    When you configured the Oracle datastore in the access manager what host name did you use? This hostname will be used on the AS/400 to connect to the Oracle server. You can verify that the host name is working:

    1) Log on to the iSeries

    2) Execute STRSQL to start a SQL Session

    3) Enter the statement SELECT TGTSYSNME, TCPRMTNME FROM <prodlib>/dmfs505p

    <prodlib> is the name of the library where you installed CDC on the iSeries

    4) The query will show the subscriptions you have created and the target host name

    5) F3 to exit

    6) Enter the command PING <hostname>

    where <hostname> is the remote hostname you got in the query for the Oracle subscription

    To answer your other questions:

    To get the job logs

    1) On the AS/400 execute the command WRKJOB <subscription>

    where <subscription> is the subscription in question

    2) You will have a list of jobs

    3) Enter 1 against the first one

    4) Enter 4 to work with spool files

    5) Enter 5 to display the QPJOBLOG file

    6) If the screen you are using is only defined to 80 chars width,enter w35 in the control field to scroll to the right to see the whole messages

    7) F3 to exit

    8) If you need to look at more job logs for your subscription, press F9 enter and repeat the process

    Trace files

    Trace files can be obtained on the Oracle side:

    1) In Management console select the configuration perspective

    2) Click on the Oracle datastore

    3 Select properties, then system values

    4) If you are using CDC 6.5.x

                Add the parameter global_trace_until

                Enter the value as the date you would like to end tracing in mm/dd/yy format - I would suggest the next day e.g.05/01/13

    5) If you are using IIDR 10.2

                locate the parameter global_trace_hours

                modify the current value 0 to say 6 t- keep tracing on for the next 6 hours

    6) Go into <CDC home directory\instance\instance name\log directory to view trace files. I would do this in Windows explorer and sort by date as the file names are not in any sequence.

    7) If you want to turn tracing off before the expiry,modify the global_trace_util to a past date or the global_trace_hours back to 0

    8) Note that unlike most datastore parameters, the parameters are changed dynamically, i.e no need to restart replication or instance for the new setting to take effect.

    regards

    Robert

     

     

     

     

    Thanks to all response.

     

    I have already fixed the problem. In the Windows Server 2008 machine, when I created Oracle Instance I need IP address for hostname parameter (I use localhost previously).

    Thanks to any idea, it will really useful in other situations that I can use.