SQL Problem in Datastage

sambitsamantray · Post by **sambitsamantray** » Mon Jan 23, 2012 10:34 am

Hello,

I've defined a sql query in the "Before SQL" of my source DRS stage. I'm using SQL Server as my database. After executing the job, when i'm checking the director log of that job, i'm getting a different query in the log in the "BeforeSQL".

Query which i have defined in the job design: DELETE FROM A WHERE CONVERT(VARCHAR(10),DATE,101) IN (SELECT CONVERT(VARCHAR(10),DATE,101) FROM B)

Query found in the job log: DELETE FROM A WHERE DATE IN (SELECT DATE FROM B)

Is this a bug or this is just a case of compatibility issue of DRS stage with SQL Server?

chulett · Post by **chulett** » Mon Jan 23, 2012 10:54 am

There's no way for it to change one query into another like that. I'd suggest you really check that you are seeing what you think you are seeing. Is that logged query from an older run with an initial version of the query? Are you in fact looking at the correct job's log?

Try right clicking on the SQL Server stage in question and selecting 'Grid View', sometimes that makes odd settings easier to find.

chandra.shekhar@tcs.com · Mon Jan 23, 2012 11:57 pm

@Sambit
How can u get the SQL query in your job logs?
I am unable to see anything related to the query in my job logs.

pandeesh · Post by **pandeesh** » Tue Jan 24, 2012 1:56 am

I am not sure about DRS stage.
But i tries the same in OCI stage some months back and the query mentioned in before/after sql was not dispalyed in the log.

sambitsamantray · Post by **sambitsamantray** » Mon Feb 06, 2012 12:07 pm

chulett wrote:There's no way for it to change one query into another like that. I'd suggest you really check that you are seeing what you think you are seeing. Is that logged query from an older run with an initial version of the query? Are you in fact looking at the correct job's log?

Try right clicking on the SQL Server stage in question and selecting 'Grid View', sometimes that makes odd settings easier to find.

I've tried it several times and the log is showing me a different query as mentioned in my previous post. When the job is executing it's not considering the convert function i've used.

sambitsamantray · Post by **sambitsamantray** » Mon Feb 06, 2012 12:09 pm

chandra.shekhar@tcs.com wrote:@Sambit
How can u get the SQL query in your job logs?
I am unable to see anything related to the query in my job logs.

When you're using a DRS stage, after executing the job when you check the job logs you can see a log saying "Executing SQL". You can double click on the log following that to view the query.

I'm not sure about the stage you're using right now.

kandyshandy · Post by **kandyshandy** » Mon Feb 06, 2012 11:46 pm

PX DB stages don't show SQLs whereas Server DB stages show SQLs. This might be the reason.

Sambit, can you remove the CONVERT function from your SQL and see what is displayed in log? Infact, try replacing CONVERT with some other function and see what happens.

Kryt0n · Post by **Kryt0n** » Tue Feb 07, 2012 3:10 pm

Does the query showing in the log match what your before sql use to state? (i.e. you've since changed to include the convert). We have had a case of a job reverting to an old self... eventually we re-created the job.

The only other thing springing to mind is if you're running the job via a sequence but actually running a different job to what you think you're running.

sambitsamantray · Post by **sambitsamantray** » Mon Feb 20, 2012 11:13 am

In my job i was trying to remove the timestamp part of a datetime value by using convert function in the before sql. That didn't work out due to the issue which i had mentioned in my previous posts.

Instead of doing that i created another job which will load my source with dates only which eventually resoilved my problem, but the primary issue is still unresolved.

I'd verified earlier also if i was running a different job or my sequence is calling a different job, but it was always the correct job which i was running.