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 ;

 

Payal Beginner Asked on March 6, 2020 in Diyotta Monitor.
Add Comment
1 Answers

Can you please perform the below checks

  1. Did we create the server ‘fs_direp’ cause earlier we were mentioning about the error accessing the ‘svrcontroller’ server?
  2. 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.

 

Expert Answered on March 6, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.