Calling Postgres Stored procedure from datastage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
gourim
Participant
Posts: 3
Joined: Tue Aug 28, 2018 12:42 am

Calling Postgres Stored procedure from datastage

Post by gourim »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
gourim
Participant
Posts: 3
Joined: Tue Aug 28, 2018 12:42 am

Post by gourim »

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?
GM
gourim
Participant
Posts: 3
Joined: Tue Aug 28, 2018 12:42 am

Post by gourim »

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:

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$
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.
GM
Post Reply