LogFile Query Interpretation of the parameters %s, :1, :2

[lqthinguyen]

Good evening 

Good evening

Please enlighten me as to where to find the values for %s, :1, and :2 in the Qlik Replicate Log or ? for the below query.
Because we received the following Oracle error ORA-00942: table or view does not exist

select object_id, data_object_id 
from %s 
where 
owner=:1 and 
object_name = :2 and 
data_object_id is not null and 
object_type in ('INDEX PARTITION', 'INDEX SUBPARTITION')' for 'BAIM.CU_PHASE_JB_HIST_PK'


Based on reverse engineering &&
queried against the dba_tab_columns table for entities that have object_id, data_object_id, owner, and object_type columns 
I assume :1 => BAIM
:2 => CU_PHASE_JB_HIST_PK
%s => dba_objects, all_objects, etc


Thank you and take care,


Theresa

[john_wang]

Hello @lqthinguyen ,

Thanks for reaching out to Qlik Community!

I did not find a similar query in my task log files however you are right - Qlik Replicate utilizes these queries to verify if the tables/views exist. The system view name should be ALL_OBJECTS (%s). The schema name and table/view name (parameters :1 & :2) can be found in the string:

for 'BAIM.CU_PHASE_JB_HIST_PK'

Besides that, the task log file context (eg the previous line(s)) may indicate the object identifier as well if you enable the components  (eg source_capture/source_unload/target_apply/target_load) logging to Trace or Verbose.

In general Oracle error ORA-00942: table or view does not exist. meaning the account have not privilege to access the table/view, or it does not exist indeed. you may verify that by executing the query with the same account:

select * from BAIM.CU_PHASE_JB_HIST_PK where 1=0;

 Hope this helps.

John.

[lqthinguyen]

Good evening Mr. Wang,

Thank you for the quick reply; meanwhile CU_PHASE_JB_HIST_PK is a primary key but the following queries were executed prior to logging this case

1) CU_PHASE_JB_HIST_PK is a partitioned index

SQL> select index_name, partitioned from all_indexes where index_name = 'CU_PHASE_JB_HIST_PK';

INDEX_NAME

--------------------------------------------------------------------------------

PAR

---

CU_PHASE_JB_HIST_PK

YES

2) There is NO object_id value based on the Qlik Replicate log

Changes to partitioned IOT 'BAIM.CU_PHASE_JB_HIST' cannot be captured as its object ids cannot be retrieved 

3) There is NO data_object_id value also  RETURNED for this index

SQL> select object_name, data_object_id from all_objects where owner='BAIM' and object_name in ('CU_PHASE_JB_HIST_PK');

OBJECT_NAME

--------------------------------------------------------------------------------

DATA_OBJECT_ID

--------------

CU_PHASE_JB_HIST_PK

You have confirmed for me that %s is ALL_OBJECTS; therefore I will need to investigate on my end when it comes to the OBJECT_ID for this query.

Thank you and take care,

Theresa

[lqthinguyen]

Good evening

Clarification:  I need to run the query again to make sure that there is a object_id for the CU_PHASE_JB_HIST_PK primary key.   data_object_id signifies the foreign key value 

I also need to educate myself how Qlik Replicate handles IOT partitioned tables and indices.

Once again you did answered my question that %s = ALL_OBJECTS/

Thank you

Theresa

[john_wang]

Hello @lqthinguyen ,

Thank you so much for your feedback. BTW, looks like there is about 12 hours' time difference between us, reply delay is expected sometimes.

From the information you provided, seems the table type is IOT:

Changes to partitioned IOT 'BAIM.CU_PHASE_JB_HIST' cannot be captured as its object ids
 cannot be retrieved 

please take note there are some limitations of the index relative tables:

1.  Function-based indexes are not supported
2. Index-organized tables with an overflow segment are not supported in Change Processing (CDC) tasks
3. Changes to Index-organized tables with key compression are not supported

Please confirm if the table is in above limitation scope.

Best Regards,

John.

[lqthinguyen]

Good morning Mr. Wang,

THANK YOU!! for the reminder with "Limitations and considerations when using an Oracle source" when it comes to an Index-Organized tables with an overflow segment.

Do you have a suggestion how to bypass it?

Unless we have to manually export from SOURCE and then import into TARGET.

Take care,

Theresa Nguyen

[john_wang]

Hello @lqthinguyen ,

Thanks for your verification. 

The limitation applies to CDC stage. We may use a "Full Load ONLY" task (both of the "Apply Changes" & "Store Changes" are turned off in Task Change Processing setting) to replicate these types of tables. And also, we can run the Full Load ONLY task in a period loop eg running it once per day etc. I'm attaching a task setting sample:

Feel free to let me know if you need any additional assistance.

Best Regards,

John.

[lqthinguyen]

Good morning Mr. Wang,

We performed another "Full Load ONLY"; yet still encounter the same error.   Please advise sir; meanwhile I am doing my research also.  THANK YOU!

The exact messages coming out of the log file

00004892: 2023-11-03T15:43:07 [METADATA_MANAGE ]E:  ORA-00942: table or view does not exist  [1020416]  (oracle_endpoint_table.c:2707)

00004892: 2023-11-03T15:43:07 [METADATA_MANAGE ]E:  Failed to execute statement 'select object_id, data_object_id from %s where owner=:1 and object_name = :2 and data_object_id is not null and object_type in ('INDEX PARTITION', 'INDEX SUBPARTITION')' for 'BAIM.CU_PHASE_JB_HIST_PK' [1020416]  (oracle_endpoint_table.c:2708)

00004892: 2023-11-03T15:43:07 [METADATA_MANAGE ]W:  Changes to partitioned IOT 'BAIM.CU_PHASE_JB_HIST' cannot be captured as its object ids cannot be retrieved  (oracle_endpoint_table.c:2817)

00004892: 2023-11-03T15:43:07 [METADATA_MANAGE ]E:  Failed to get the capture list from the endpoint [1020416]  (metadatamanager.c:4389)

00004892: 2023-11-03T15:43:07 [TABLES_MANAGER  ]E:  Cannot get captured tables list [1020416]  (tasktablesmanager.c:1040)

00004892: 2023-11-03T15:43:07 [TASK_MANAGER    ]E:  Build tables list failed  

[lqthinguyen]

Good evening Mr. Wang,

It turned out that the interested that I inquired at the very beginning of this thread was querying against the 'dba_objects' view; instead the 'all_objects' one.  This showed up on our Oracle trace file 

select object_id, data_object_id from dba_objects where owner=:1 and object_name = :2 and data_object_id is not null and object_type in ('INDEX PARTITION', 'INDEX SUBPARTITION')

We will perform a Full Load Replicate again; once the DBA team execute the GRANT SELECT ON dba_objects TO <username>

Thank you, Mr. Wang

[john_wang]

Thank you so much for the feedback @lqthinguyen