Hi,
Need to extract data from a view.
Want to create this view using the Before SQL option of DB2Connector stage by reading the create the statement from a file.
The job fails saying The object name(here the view) is undefined.
My understanding with BeforeSQL statement is, the statements defined in the beforesql statement(here creation of the view) would be executed first before processing of any data(here reading the data from the view).
Please help me to understand how this functions.
Running Datastage 8.7 version on unix.
Before SQL statement in DB2 Connector not working
Moderators: chulett, rschirm, roy
Before SQL statement in DB2 Connector not working
Thanks,
HK
*Go GREEN..Save Earth*
HK
*Go GREEN..Save Earth*
The metadata for the select from your view would be checked before execution... at which time your view does not exist.
I agree with Craig... Before SQL is not the place for this view creation.
Is there a reason for creating the view for every job run instead of a one time creation?
If yes, I'd probably create it using the DB2 CLI from an execute command activity in a job sequence.
Mike
I agree with Craig... Before SQL is not the place for this view creation.
Is there a reason for creating the view for every job run instead of a one time creation?
If yes, I'd probably create it using the DB2 CLI from an execute command activity in a job sequence.
Mike
Craig,
There are no parameters in the file.
Mike,
I create the view at the run time because there is a subquery within the view creation which has a where clause for range of dynamic dates, which currently I am taking care from a script to change these dates and create the sql statement file. This file I am using in the before Sql part.
I understand from your statement "The metadata for the select from your view would be checked before execution... at which time your view does not exist", this is not the right place.
There are no parameters in the file.
Mike,
I create the view at the run time because there is a subquery within the view creation which has a where clause for range of dynamic dates, which currently I am taking care from a script to change these dates and create the sql statement file. This file I am using in the before Sql part.
I understand from your statement "The metadata for the select from your view would be checked before execution... at which time your view does not exist", this is not the right place.
Thanks,
HK
*Go GREEN..Save Earth*
HK
*Go GREEN..Save Earth*
You didn't answer the syntax question.
And I don't recall ever seeing a view that needed to be constantly recreated with a dynamic date range, rather you create something more 'generic' and then constrain the data when you select from the view. I understand it's in a subquery but still... hard to say however without seeing the actual view definition and knowing the structures/data. [shrug]
If the view literally does not exist before the first run (i.e. this is only a first run issue) - create it. Then let your job do the "CREATE OR REPLACE" part when the job runs to firm up your date range.
And I don't recall ever seeing a view that needed to be constantly recreated with a dynamic date range, rather you create something more 'generic' and then constrain the data when you select from the view. I understand it's in a subquery but still... hard to say however without seeing the actual view definition and knowing the structures/data. [shrug]
If the view literally does not exist before the first run (i.e. this is only a first run issue) - create it. Then let your job do the "CREATE OR REPLACE" part when the job runs to firm up your date range.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers