ODBC Connector Multiple update Queries

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
neeraj
Participant
Posts: 107
Joined: Tue May 24, 2005 4:09 am

ODBC Connector Multiple update Queries

Post by neeraj »

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
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

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 - 2020
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Re: ODBC Connector Multiple update Queries

Post by sriven786 »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

neeraj wrote:ODBC connector (User-defined SQL)
-craig

"You can never have too many knives" -- Logan Nine Fingers
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Post by sriven786 »

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
Thomas.B
Participant
Posts: 63
Joined: Thu Apr 09, 2015 6:40 am
Location: France - Nantes

Post by Thomas.B »

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
DSXConsult
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Post by sriven786 »

Yes. Teradata connector write mode allows for User Defined but not the odbc connector (Only Insert/Update/Delete)
Venkata Srini
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Post by sriven786 »

chulett wrote:Answer is still the same, only a single DML statement is supported.
yes. After sql can support multiple statements
Venkata Srini
Thomas.B
Participant
Posts: 63
Joined: Thu Apr 09, 2015 6:40 am
Location: France - Nantes

Post by Thomas.B »

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
DSXConsult
Thomas.B
Participant
Posts: 63
Joined: Thu Apr 09, 2015 6:40 am
Location: France - Nantes

Post by Thomas.B »

I also execute those statements using the same job (in the "user defined SQL" section) :

Code: Select all

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
DSXConsult
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Post by sriven786 »

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
Post Reply