DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message

Joined: 14 Apr 2014
Posts: 3
Location: Seattle
Points: 74

Post Posted: Thu May 14, 2020 5:06 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hello- Seems to be a few threads on here related to this topic tho I think my use-case is unique.
I am loading data from SQL server DB (source) into a Netezza DB (target).
Very little transformation (most of the time none) is being done.

Our vendor has supplied us with an 'API' in the form of a Stored Proc that will generate a global temp table [##tmp_tbl] with data indicating what rows in the 'source' table are either new/altered/deleted.
I can then use the results from the temp table to update the target on Netezza.

Job Design: ODBC Connector ---> Peek

I am to call the SP giving several arguments: begin_dt, end_dt, source_tbl_nm, temp_tbl_nm.

I am thinking that I can execute the SP in the 'before sql' portion of the ODBC stage and then utilize a 'select * from [##tmp_tbl]' in the SQL Select statement section of the same stage.

When I run my job and look at the log, I get a couple initial warnings:
* Invalid object name ##tmp_tbl
* The metadata could not be determined because every code path results in an error. see previous errors for some of these
note:there are no previous errors...

The log then tells me:
* Before SQL command executed successfully
* Connected to Microsoft SQL Server, version...blah blah blah

then it hangs.
While the job is 'running' I am unable to browse the ##temp table DB objects via SSMS; where normally I can... I get a timeout when attempting to refresh the temp table folder list.
As soon as I stop the process from running the lock is released and I am then able to browse these temp table objects (although the one I created with the SP, if there ever was one) is not there.

I have tried various configurations of this: using a CALL instead of EXEC which produces an error. Wrapping the EXEC with BEGIN/END, which makes no difference. Attempted to put the EXEC and SELECT all in the primary Select Statement field, which gives me "invalid cursor state" error.
I have also attempted to use the Stored Procedure Stage- which appears to successfully call the SP, but since the result of the SP is a temp table and not Data, I still need another step to capture the data- which I couldn't figure out how to accomplish with the SP Stage.

Does anyone have insight on how to call a SP in the Before SQL and then reference a resulting global temp table- or have any ideas on another approach?

Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours