Calling Postgres Stored procedure from datastage
Moderators: chulett, rschirm, roy
Calling Postgres Stored procedure from datastage
Im trying to execute a postgres stored procedure having input and output parameters from datastage. I am doing this using the stored procedure component. In the General tab of this component, there is an option to select the Database vendor and I am not able to see Postgres in the dropdown list. Is there any way to add Postgres in the list of database vendors or is there any other way to execute a postgres function from datastage?
GM
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Possibly not in such an old version of DataStage. Version 8.x has been out of support for five years already.
Can you use an ODBC connection?
Is there a mechanism that you can incorporate a call to a stored procedure within a regular SELECT query?
Can you use an ODBC connection?
Is there a mechanism that you can incorporate a call to a stored procedure within a regular SELECT query?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Possibly not in such an old version of DataStage. Version 8.x has been out of support for five years already.
>>>>> So is this functionality available in higher versions of Datastage?
Can you use an ODBC connection?
>>>>> Yes i am able to connect to postgres DB using ODBC Connection. However, I am not able to connect to postgres functions.
Is there a mechanism that you can incorporate a call to a stored procedure within a regular SELECT query?
>>>>>My postgres function is inserting values in a table, so how do i call this function using SELECT query?
>>>>> So is this functionality available in higher versions of Datastage?
Can you use an ODBC connection?
>>>>> Yes i am able to connect to postgres DB using ODBC Connection. However, I am not able to connect to postgres functions.
Is there a mechanism that you can incorporate a call to a stored procedure within a regular SELECT query?
>>>>>My postgres function is inserting values in a table, so how do i call this function using SELECT query?
GM
Hi,
I tried calling the postgres function using the select query from the odbc stage as suggested by you, and it does run without any errors. But the output is not as expected. The same function gives correct results when I run it on postgres server. So the function is fine. Let me explain it in detail.
This is my function:
So when the max run_date in the table is less than the current date and the status is completed, then a new row should be inserted and param should be set to 1.
Now when i run this function in postgres, it does as expected.
And when i run this from datastage, it gives me the output: param = 0 and run_key is some random value and not the max run_key + 1. Also no row gets inserted in the postgres table.
Eg:
If the data in Ctrl_run table is:
run_key Status
30 Completed
When i run the procedure, a new run_key should be inserted:
run_key Status
30 Completed
31 Running
But, it does not insert a row and when i collect the ouput in a sequential file, it shows me that run_key = 35
I hope you have understood my issue.
I am not able to understand from where is this run_key with the incremented value is getting populated. Also, why is the record not getting inserted into the database.
Thanks. I appreciate your help.
I tried calling the postgres function using the select query from the odbc stage as suggested by you, and it does run without any errors. But the output is not as expected. The same function gives correct results when I run it on postgres server. So the function is fine. Let me explain it in detail.
This is my function:
Code: Select all
CREATE OR REPLACE FUNCTION public.ctrl_run_check_proc(OUT param integer, OUT run_key integer)
RETURNS record
LANGUAGE plpgsql
AS $function$
declare
runn_date integer;
status varchar(50);
flag integer;
Begin
select run_date
from ctrl_run
where dw_run_key = (select MAX(dw_run_key) from ctrl_run) into runn_date;
select Status_Code
from ctrl_run
where dw_run_key = (select MAX(dw_run_key) from ctrl_run) into status;
IF (cast(to_char(current_date, 'YYYYMMDD') as integer) = runn_date ) Then
IF status ='Completed' or status ='Running' Then
flag:= 1;
ELSE
flag:= 2;
End If;
ElsIf (CAST(to_char(current_date, 'YYYYMMDD') as INTEGER) > runn_date) Then
flag := 3;
Else
flag := 4;
End If;
--Part 2
IF flag = 3 Then
param := 1;
insert into Ctrl_run(run_date,run_eff_dt,run_end_dt,run_type,status_code,dw_create_dtm,dw_updt_dtm) values
(
cast(to_char(current_date, 'YYYYMMDD') as Integer),
(select run_end_dt from Ctrl_run where DW_RUN_KEY = (select max(DW_Run_Key)
from Ctrl_Run where Status_Code = 'Completed')),
current_timestamp,
'Daily',
'Running',
current_timestamp,
current_timestamp
);
select MAX(dw_run_key) from ctrl_run into run_key ;
elsif flag = 2 Then
param := 1;
update ctrl_run set Status_Code = 'Running', DW_Updt_Dtm = current_timestamp
where dw_run_key = (select MAX(dw_run_key) from ctrl_run);
select MAX(dw_run_key) from ctrl_run into run_key ;
elsif flag in (1,4) Then
param := 0;
select MAX(dw_run_key) from ctrl_run into run_key;
End IF;
END;
$function$
Now when i run this function in postgres, it does as expected.
And when i run this from datastage, it gives me the output: param = 0 and run_key is some random value and not the max run_key + 1. Also no row gets inserted in the postgres table.
Eg:
If the data in Ctrl_run table is:
run_key Status
30 Completed
When i run the procedure, a new run_key should be inserted:
run_key Status
30 Completed
31 Running
But, it does not insert a row and when i collect the ouput in a sequential file, it shows me that run_key = 35
I hope you have understood my issue.
I am not able to understand from where is this run_key with the incremented value is getting populated. Also, why is the record not getting inserted into the database.
Thanks. I appreciate your help.
GM