ODBC Connector stage for stored procedures execution

Developer9 · Post by **Developer9** » Wed Jun 01, 2016 9:23 am

Hi,

I am advised to use ODBC connector for SP Execution .

Oracle connector ---->Peek

Before Sql statements

Code: Select all

exec schemaname.dbo.TestSP ;commit;

What is the SQL statement to use in the Select statement ?Can I use the above statement is SQL section ?What I have to specify in the columns tab ?

SQL Server SP Does not require any parameters (only returns value)..it does all processing in the DB itself

Please advise me on the usage of connector stage

Thanks,
Mahesh

chulett · Post by **chulett** » Wed Jun 01, 2016 10:00 am

So... an Oracle connector to run a SQL Server procedure using ODBC?

Developer9 · Post by **Developer9** » Wed Jun 01, 2016 10:04 am

My Bad its ODBC ..

Code: Select all

ODBC connector >>>peek

Thanks

chulett · Post by **chulett** » Wed Jun 01, 2016 10:43 am

So, it is your version that doesn't allow you to use the Stored Procedure stage, as in SQL Server is not supported? I seem to recall it supporting ODBC, however, even way back in 8.

That being said, what kind of 'values' does it return - something that needs to be check or processed by the job or are you just literally using DataStage to run the stored procedure? The answer would change depending on if you are just executing it or if you will run it and actually need to feed these return values into the job. Guessing it's the former from what you've posted so far but wanted to double-check.

And just as an FYI if this comes down to 'SQL Server specific issues', someone else would have to help, me no do SQL Server.

Developer9 · Post by **Developer9** » Wed Jun 01, 2016 11:09 am

I am using version 8.7 ,Admin is saying SP stage is obsolete and use connector stage .next I am using DataStage to literally Run SP ..Expected value is an integer

Thanks for the suggestions

chulett · Post by **chulett** » Wed Jun 01, 2016 12:26 pm

Your Admin is... incorrect, I'm afraid. The Stored Procedure stage exists all the way up to the most current 11.x version and is what you should be using IMHO. Here is a documentation link for 8.7 which shows support for SQL Server and shows where you should set it to a "Source" for use as per your diagram.

Teej · Post by **Teej** » Wed Jun 01, 2016 1:25 pm

Craig is correct. We continue to support the Stored Procedure stage, even in 11.5, even though it's a dscapiop operator, not a pxbridge operator.

To confirm to the administrator, here's the link to the documentation for the Stored Procedure stage for 11.5:

http://www.ibm.com/support/knowledgecen ... pic21.html

There have been a large number of stages depreciated for 11.5, and this was not one of them.

Developer9 · Post by **Developer9** » Wed Jun 01, 2016 2:25 pm

Thank you for the suggestions on using SP stage ..I would like to test using ODBC connector stage as SP stage is not going to be the option for me as it is advised.

I am getting below error when I use the Exec statement in SQL ..Please advice me on the usage

Code: Select all

ODBC_Conn,0: ODBC function "SQLFetch" reported:  SQLSTATE = 24000: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Wire Protocol driver]Invalid cursor state. (CC_OdbcDBStatement::dbsFetch, file CC_OdbcDBStatement.cpp, line 1,595)

SURA · Post by **SURA** » Wed Jun 01, 2016 7:27 pm

I am not sure why you need a workaround when you have a direct option to trigger the SP?

If you want to execute a SP using select statement, then you need to know about OPENROWSET.

Google it! you will get some idea.

But..................use the SP stage and that's purpose of that stage.

chulett · Post by **chulett** » Wed Jun 01, 2016 9:29 pm

You really should push back, use the right tool for the job rather than just say that it's "not an option" for you.

Developer9 wrote:I am getting below error when I use the Exec statement in SQL

Where exactly in the stage are you trying to execute the procedure? How exactly do you have everything set? Without details people won't be able to provide cogent help. And recognize you cannot execute a procedure that returns data in either the Before or After SQL areas as there's basically nowhere for the data to go. As a source, you need to select data for delivery into the job, only the Stored Procedure stage allows a procedure to be a source and to drive records into a job.

Read the Connectivity Guide for ODBC manual, in particular where it discusses the Connector stage and where you should use something else. For example:

The following table lists the scenarios where you might want to use one of the stages other than the ODBC Connector stage.

Table 4. Scenarios where you might use a stage other than the ODBC Connector stage

Code: Select all

Goal                            Stage to use instead of the ODBC Connector stage
Use stored procedures.          Stored Procedure (preferred)
                                ODBC

Then swap the ODBC Connector for the Stored Procedure stage.

Teej · Post by **Teej** » Thu Jun 02, 2016 11:05 am

Please have your administrator come here and see our responses. We have many decades of experience using DataStage (and I am one of the actual developers of DataStage Parallel Engine). If we are saying the Stored Procedure stage is actively supported, by darn, maybe we are right.

True, it would be nice to set up a pxbridge version of the Stored Procedure stage (instead of dsapiop). Maybe someday soon, especially if there is a sufficient demand for this (hint, hint! We do look at the Request for Enhancement requests.)

-T.J.

Developer9 · Post by **Developer9** » Mon Jun 06, 2016 11:27 am

I tried below design and worked

Code: Select all

rowgen --->ODBC connector

In ODBC ,Gave user defined SQL :statements

Code: Select all

exec SP

Job ran successful and SP executed .

Thanks

chulett · Post by **chulett** » Mon Jun 06, 2016 11:40 am

That's fine... you've lost the ability to capture the value it was allegedly returning that you allegedly needed when you use it as a target like that but if that's working for you. [shrug]

Still doesn't change any of the advice given here, however. Keep that in mind for the future.

Developer9 · Post by **Developer9** » Wed Jun 08, 2016 12:47 pm

Thanks Chulett,

When I say working means Job ran successful but I do not have a detailed log of Stored Procedure after the execution ..

Given In Row generator no.of records =1
Fail on Error :Set Option Yes (ODBC connector stage)

Below are informational messages in Director logs

Odbc_SPCall,0: Number of rows consumed by the user-defined SQL statement: 1
Odbc_SPCall,0:Number of rows rejected: 0
Seq_Reject_File,0: Export complete; 0 records exported successfully, 0 rejected.

Currently Stored Procedure is called through Sql Server call

exec SP ;
Messages:
10000 rows affected
15000 rows affected
0 rows affected

Is there anyway to capture the results of the stored procedure execution using data stage?

Thanks

chulett · Post by **chulett** » Wed Jun 08, 2016 1:17 pm

Are we still restricting answers to only the ODBC Connector stage?