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



Joined: 24 May 2005
Posts: 99

Points: 1322

Post Posted: Fri Nov 10, 2017 4:55 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Additional info: Unable to execute multiple queries
Hello,

I am using ODBC connector stage to connect to Azure SQL Server and execute the 2 update statement in sequence as below:

UPDATE HWP.WRK_VHEW_VOL
SET EXP_DT = '2017-11-09' , STG_FL='D'
FROM HWP.WRK_VHEW_VOL A
WHERE EXISTS
(
SELECT 1 FROM HWP.WRK_VHEW_VOL B
WHERE A.ID=B.ID
AND B.STG_FL='D'
)
AND A.Start_DT < '2017-11-09'
AND A.EXP_DT = '9999-12-31';


UPDATE HWP.WRK_VHEW_VOL
SET EXP_DT = DATEAdd(Day, -1,'2017-11-09')
FROM HWP.WRK_VHEW_VOL A
WHERE EXISTS
(
SELECT 1 FROM HWP.WRK_VHEW_VOL B
WHERE A.ID=B.ID
AND B.STG_FL='U'
)
AND A.Start_DT < '2017-11-09'
AND A.EXP_DT = '9999-12-31';


The Job is finishing successfully but only 1st query is getting executed. The 2nd query does not trigger.

I executed both the queries using SQL server management Studio and it executes both the queries one after another.

Can you please let me know if there is a limitation in the ODBC connector(User-defined SQL) that I can pass only 1 query in the Statements Box or have done anything wrong.

Regards
Neeraj

[Edit: removed the orange highlighting so the red part would stand out more - Andy (especially since I'm colorblind!)]
asorrell
Site Admin

Group memberships:
Premium Members, DSXchange Team, Inner Circle, Server to Parallel Transition Group

Joined: 04 Apr 2003
Posts: 1636
Location: Colleyville, Texas
Points: 22245

Post Posted: Sun Nov 12, 2017 3:51 pm Reply with quote    Back to top    

As far as I know the Connector can only handle one SQL statement per update. Though the documentation refers to "SQL - Specifies one or more SQL statements to use to write data." it is really referring to having one for Inserts, one for Updates, etc.

_________________
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2017
Rate this response:  
Not yet rated
sriven786
Participant



Joined: 08 Nov 2017
Posts: 21

Points: 158

Post Posted: Mon Nov 13, 2017 9:10 am Reply with quote    Back to top    

Where are you specifying these update statements?
Is it Before/After SQL?. As per documentation, we can execute multiple statements

After SQL
If you set the Before/After SQL property to Yes and specify an SQL statement in the After SQL property, the connector runs the statement. If you specify multiple statements in the After SQL property, the connector runs each additional statement in sequence.

_________________
Venkata Srini
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42217
Location: Denver, CO
Points: 216760

Post Posted: Mon Nov 13, 2017 10:20 am Reply with quote    Back to top    

neeraj wrote:
ODBC connector (User-defined SQL)

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
sriven786
Participant



Joined: 08 Nov 2017
Posts: 21

Points: 158

Post Posted: Mon Nov 13, 2017 10:38 am Reply with quote    Back to top    

User Defined SQL is disable if we use ODBC Connector as Output.

I guess the user is trying to use Update statement and as per documentation, only 1 update statement can be executed

Please clarify

_________________
Venkata Srini
Rate this response:  
Not yet rated
Thomas.B
Participant



Joined: 09 Apr 2015
Posts: 57
Location: France - Nantes
Points: 369

Post Posted: Thu Nov 16, 2017 11:28 am Reply with quote    Back to top    

sriven786 wrote:
User Defined SQL is disable if we use ODBC Connector as Output.


Are you sure about that? I often use the "user defined SQL" section to execute instructions using the input columns with the "Teradata Connector" stage (I did not try the ODBC stage).

@neeraj Did you try to add a COMMIT instruction between the 2 UPDATE statements?

_________________
BI Consultant
Business & Decision
Rate this response:  
Not yet rated
sriven786
Participant



Joined: 08 Nov 2017
Posts: 21

Points: 158

Post Posted: Thu Nov 16, 2017 11:36 am Reply with quote    Back to top    

Yes. Teradata connector write mode allows for User Defined but not the odbc connector (Only Insert/Update/Delete)

_________________
Venkata Srini
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42217
Location: Denver, CO
Points: 216760

Post Posted: Thu Nov 16, 2017 12:49 pm Reply with quote    Back to top    

Answer is still the same, only a single DML statement is supported.

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
sriven786
Participant



Joined: 08 Nov 2017
Posts: 21

Points: 158

Post Posted: Thu Nov 16, 2017 12:55 pm Reply with quote    Back to top    

chulett wrote:
Answer is still the same, only a single DML statement is supported.

yes. After sql can support multiple statements

_________________
Venkata Srini
Rate this response:  
Not yet rated
Thomas.B
Participant



Joined: 09 Apr 2015
Posts: 57
Location: France - Nantes
Points: 369

Post Posted: Thu Nov 16, 2017 2:01 pm Reply with quote    Back to top    

sriven786 wrote:
Yes. Teradata connector write mode allows for User Defined but not the odbc connector (Only Insert/Update/Delete)

I am going a little off-topic here but you can definitely do it with an ODBC Connector, i just did it on DataStage 11.5 using Windows.

Image

_________________
BI Consultant
Business & Decision
Rate this response:  
Not yet rated
Thomas.B
Participant



Joined: 09 Apr 2015
Posts: 57
Location: France - Nantes
Points: 369

Post Posted: Thu Nov 16, 2017 2:22 pm Reply with quote    Back to top    

I also execute those statements using the same job (in the "user defined SQL" section) :
Code:
update THOMAS.DATA1
set INT02 = ORCHESTRATE.INT02
where INT01 = ORCHESTRATE.INT01;

update THOMAS.DATA1
set INT03 = ORCHESTRATE.INT03
where INT01 = ORCHESTRATE.INT01;


They both update the DATA1 table.
Is there something i don't get ? I also use SQL Server on Azure.

_________________
BI Consultant
Business & Decision
Rate this response:  
Not yet rated
sriven786
Participant



Joined: 08 Nov 2017
Posts: 21

Points: 158

Post Posted: Thu Nov 16, 2017 2:31 pm Reply with quote    Back to top    

User is using Datastage 11 on Unix and ODBC connector stage on Unix did not have the option of User Defined Sql.

Job Type: Parallel
OS: Unix

Note:Not sure how to attach image. so could not attach the screen snapshot

_________________
Venkata Srini
Rate this response:  
Not yet rated
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