RE: I have setup the DBLink but one of the Jobflow was failing that is requires the DBLink
The job flow that requires the dblink is failing however I am able to run the query with dblink manually.
ERROR:Looper sql execution failed with exception :: ERROR: function dblink(unknown, unknown) does not existHint: No function matches the given name and argument types. You might need to add explicit type casts.Position: 1011
SQL QUERY:
select
CONTROL_TABLE.CONTROL_ID||’,’|| CONTROL_MASTER.MASTER_ID||’,’|| CONTROL_TABLE.DIYOTTA_RUN_ID||’,’|| CONTROL_TABLE.PARENT_DIYOTTA_RUN_ID||’,’|| CONTROL_MASTER.SOURCE_DATABASE||’,’|| CONTROL_MASTER.SOURCE_TABLE||’,’ || CONTROL_TABLE.LOW_WATERMARK||’,’ || CONTROL_MASTER.DATA_MIGRATION||’,’|| CONTROL_TABLE.STATUS
from
dictrl.CONTROL_TABLE
join
dictrl.CONTROL_MASTER on CONTROL_TABLE.MASTER_ID = CONTROL_MASTER.MASTER_ID
and CONTROL_MASTER.ACTIVE_FLAG = ‘Y’
where
project = ‘Diyotta_POC’ and
layer in (‘edw’, ‘edw_lg’) and
data_migration = ‘byDate’ and
load_type != ‘One Time’ and
status = ‘Extract_A’ and
CONTROL_TABLE.active_flag = ‘Y’ and
extract(epoch from current_timestamp at time zone ‘UTC’ – start_time)/3600::Integer > 1 and
extract(epoch from current_timestamp at time zone ‘UTC’ – start_time)/3600::Integer < 24 and
CONTROL_TABLE.diyotta_run_id in (SELECT * FROM dblink (‘fs_direp’,’select run_id from direp.vw_ops_run_job_dtl WHERE status = ”FAILURE”’) AS RUN_ID(id INT8))
LIMIT 10 ;
Can you please perform the below checks
- Did we create the server ‘fs_direp’ cause earlier we were mentioning about the error accessing the ‘svrcontroller’ server?
- This error would occur when the dblink was not created on public schema, so to overcome this we have to qualify with someschema.dblink(…)
Which is dictrl.dblink in our case.
Can you perform the step2 and let us know if you’re still facing the same issue.