ODBC stage - Enter SQL question

[kwt10]

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

[raju_chvr]

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 !!

[chulett]

Actually, for the ODBC stage it would be:

Code: Select all

DELETE FROM AMCOLL WHERE FE = ? and COL_CODE = ? and COL_LINE = ?

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.

[kwt10]

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.

[kcbland]

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...

[vmcburney]

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.

[kwt10]

Thanks for the help. I do appreciate any advice that I can get (no matter how rude it is.......kcbland).

[kcbland]

Thanks for the help. I do appreciate any advice that I can get (no matter how rude it is.......kcbland).

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]

Thanks kcbland and Ditto!!