ODBC stage - Enter SQL question
Moderators: chulett, rschirm, roy
ODBC stage - Enter SQL question
I select User-Defined SQL from the update action field in the ODBC stage action. I want to enter a SQL statement that will delete the selected record that is being read. I need to match up three fields in the layout to know which row to delete. What would the syntax be in the Enter SQL section? This is what I have right now but I am not sure if it will work:
"DELETE FROM AMCOLL WHERE FE = DSLink11_FE and COL_CODE = DSLink11_COL_CODE and COL_LINE = DSLink11_COL_LINE"
Please be gentle I am new at DataStage and SQL!!!
Thanks
"DELETE FROM AMCOLL WHERE FE = DSLink11_FE and COL_CODE = DSLink11_COL_CODE and COL_LINE = DSLink11_COL_LINE"
Please be gentle I am new at DataStage and SQL!!!
Thanks
Re: ODBC stage - Enter SQL question
Please rethink your design of the job, as you want to delete the record which you are reading.
I will assume that ur doing it for learning DS, then:
Then do this, write ur source into a Sequential file or Hash file and then do the delete. I believe the syntax would be something like:
DELETE FROM AMCOLL WHERE FE = :1 and COL_CODE = :2 and COL_LINE = :3
where
1 = DSLink11_FE
2 = DSLink11_COL_CODE
3 = DSLink11_COL_LINE
in the Transformer Target columns list.
But do a little bit more testing before you even move this job to Production.
Good Luck !!
I will assume that ur doing it for learning DS, then:
Then do this, write ur source into a Sequential file or Hash file and then do the delete. I believe the syntax would be something like:
DELETE FROM AMCOLL WHERE FE = :1 and COL_CODE = :2 and COL_LINE = :3
where
1 = DSLink11_FE
2 = DSLink11_COL_CODE
3 = DSLink11_COL_LINE
in the Transformer Target columns list.
But do a little bit more testing before you even move this job to Production.
Good Luck !!
Actually, for the ODBC stage it would be:
The parameters are substituted into your statement in order based on your input columns. The numbered parameter format is for stages like the OCI stage, btw.
Code: Select all
DELETE FROM AMCOLL WHERE FE = ? and COL_CODE = ? and COL_LINE = ?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Let me try and throw some more info into my question. We have a database with millions of rows. The file that I am reading is either new rows for that database or the key of a row to be deleted. The input file is a complex flat file (just so you would know).
The adding of the new row is no problem but we want DS to do the delete for us also. The reason is so that we can have the entire process done in one DS job. That might be dreaming but I think if I can get the SQL right it just might work.
Well if anybody has anymore thoughts on the subject I am all ears and I hope this additional info did not muddy the waters even more.
Thanks again for ANY help.
The adding of the new row is no problem but we want DS to do the delete for us also. The reason is so that we can have the entire process done in one DS job. That might be dreaming but I think if I can get the SQL right it just might work.
Well if anybody has anymore thoughts on the subject I am all ears and I hope this additional info did not muddy the waters even more.
Thanks again for ANY help.
Yes, I would like things done in one job also. In fact, if I could have all of my work in one job, what a dream when putting it into the enterprise schedule. Imagine, one job!
One job to rule them all, one job to find them. One job to bring them all and in the darkness bind them! Bwahhhahahaaa Bwahhhahahaaa
Excuse me, Evil Ken came out there.
Here's some friendly advice. Get over the pretty picture sales pitch of one job. Small, modular, easy to use jobs. Trust us on this one. If you look at the people belonging to the Inner Circle, you could probably find 10,000 jobs developed collectively. We know a little bit about using this product.
So, not only are you stacking a ton of database work in a single job, you also are going to have to deal with performance. You've already been told you're going to have locking issues, as well as inter-table contention.
(Sounds of my chest puffing out)
In my expert opinion, you should separate your insert, update, and delete file into three separate files. Then, bulk load your inserts. Then, update your updates. Then, delete your deletes. You can have one job to separate the complex file. Wow, here's an idea, instantiate that job so that you have N jobs instances separating out the file using N cpus, instead of 1 job using 1 cpu. Then, concatenate your insert files together and bulk load that. Gee, I bet that's really fast. Then, concatenate your update files together and run those in. Then, concatenate your deletes together and run those in. Use a job sequencer to run these in the right steps, with the concatenations as command stages.
One thing to consider: bulk load your updates into a work table and use a SQL statement to perform the update using parallel dml. Same trick works for the delete.
Bawango...
One job to rule them all, one job to find them. One job to bring them all and in the darkness bind them! Bwahhhahahaaa Bwahhhahahaaa
Excuse me, Evil Ken came out there.
Here's some friendly advice. Get over the pretty picture sales pitch of one job. Small, modular, easy to use jobs. Trust us on this one. If you look at the people belonging to the Inner Circle, you could probably find 10,000 jobs developed collectively. We know a little bit about using this product.
So, not only are you stacking a ton of database work in a single job, you also are going to have to deal with performance. You've already been told you're going to have locking issues, as well as inter-table contention.
(Sounds of my chest puffing out)
In my expert opinion, you should separate your insert, update, and delete file into three separate files. Then, bulk load your inserts. Then, update your updates. Then, delete your deletes. You can have one job to separate the complex file. Wow, here's an idea, instantiate that job so that you have N jobs instances separating out the file using N cpus, instead of 1 job using 1 cpu. Then, concatenate your insert files together and bulk load that. Gee, I bet that's really fast. Then, concatenate your update files together and run those in. Then, concatenate your deletes together and run those in. Use a job sequencer to run these in the right steps, with the concatenations as command stages.
One thing to consider: bulk load your updates into a work table and use a SQL statement to perform the update using parallel dml. Same trick works for the delete.
Bawango...
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
The easiest way to get your custom SQL is to set the Update Action to "Replace existing rows completely" and then have a look at the SQL that this produces. You will see a delete statement followed by an insert statement. Now copy the delete statement and change your stage to use custom SQL and paste down the delete statement. As Craig mentioned earlier you will need ? characters to indicate where the key fields appear in the statement.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
If my attempts at humor came across as rude , well, alas, the emoticons did not do the trick. My apologies for the perceived rudeness.kwt10 wrote:Thanks for the help. I do appreciate any advice that I can get (no matter how rude it is.......kcbland).
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle