SQL Problem in Datastage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 24
- Joined: Wed Sep 10, 2008 7:27 am
- Location: Houston
- Contact:
SQL Problem in Datastage
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?
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?
Sambit Samantray
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.
Try right clicking on the SQL Server stage in question and selecting 'Grid View', sometimes that makes odd settings easier to find.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India
-
- Premium Member
- Posts: 24
- Joined: Wed Sep 10, 2008 7:27 am
- Location: Houston
- Contact:
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.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.
Sambit Samantray
-
- Premium Member
- Posts: 24
- Joined: Wed Sep 10, 2008 7:27 am
- Location: Houston
- Contact:
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.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.
I'm not sure about the stage you're using right now.
Sambit Samantray
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
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.
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.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
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.
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.
-
- Premium Member
- Posts: 24
- Joined: Wed Sep 10, 2008 7:27 am
- Location: Houston
- Contact:
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.
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.
Sambit Samantray