How to use oracle hints in datastage
Moderators: chulett, rschirm, roy
How to use oracle hints in datastage
Hi,
I am trying to use the hint /*+ append */ on a user-defined sql (insert) but datastage complains about the syntaxe. Any ideas on how to work around this?
Cheers,
I am trying to use the hint /*+ append */ on a user-defined sql (insert) but datastage complains about the syntaxe. Any ideas on how to work around this?
Cheers,
You don't even need to use User Defined sql if you are doing that just to add the hint. With Column Generated I add it in the Derivation of the first field selected... works like a champ. For select statements anyway. ![Wink :wink:](./images/smilies/icon_wink.gif)
Post the complete sql and the complaint.
![Wink :wink:](./images/smilies/icon_wink.gif)
Post the complete sql and the complaint.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks guys, I have sorted it out. It's working. I might have typed something that ds couldn't compile.chulett wrote:You don't even need to use User Defined sql if you are doing that just to add the hint. With Column Generated I add it in the Derivation of the first field selected... works like a champ. For select statements anyway.
Post the complete sql and the complaint.
the sql is like this:
insert into table /*+ append */ ( a, b ) values (:1,:2)
Cheers,
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
-
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
Has anyone tried Craig's technique of including the Hint in the expression for the first column using Designer 7.5.1.A. They've upgraded the OCI SQL-builder tool and it's a bit of a syntax-Nazi.
I actually wanted to use the same technique to place a DISTINCT before the first column name, but I cannot get it to work for hints either.
The only things the expression-builder allows you to do are functions that you select from a list (eg. ABS(col)) and arithmetic calculations (eg. col + 10).
I actually wanted to use the same technique to place a DISTINCT before the first column name, but I cannot get it to work for hints either.
The only things the expression-builder allows you to do are functions that you select from a list (eg. ABS(col)) and arithmetic calculations (eg. col + 10).
Ross Leishman
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I don't use the 'Fully Generated SQL' option now and I wouldn't touch the 'SQL Builder tool' with a thirty-nine and a half foot pole.
However, putting hints and other goodies in the first column's derivation works fine with the 'Column Generated' option in earlier versions and with the 'Generate SELECT clause from column list; enter other clauses' option in 7.5.1A too from what little I've been able to play with it.
![Wink :wink:](./images/smilies/icon_wink.gif)
However, putting hints and other goodies in the first column's derivation works fine with the 'Column Generated' option in earlier versions and with the 'Generate SELECT clause from column list; enter other clauses' option in 7.5.1A too from what little I've been able to play with it.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
If I have a lot of columns from multiple tables I start with the SQL builder to build a column list and then switch across to generated or user-defined SQL straight afterwards. The SQL builder may be poor at table joins and where clauses but it is very fast for finding multiple tables and choosing the columns from those tables. When you are done it loads those columns into the column list. Faster then running the load table definition several times over.
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
That's an interesting thought, Vincent. I'll have to play around with it.
At this point, I still prefer to 'seed' things by either dragging the column metadata in from the Repository browser onto a link, or directly via the Load button inside the stage. The method depends on if I want blank derivations or not. And since I've typically built and tested the query from TOAD first, I can then just paste in the 'other' clauses the stage needs.
![Wink :wink:](./images/smilies/icon_wink.gif)
At this point, I still prefer to 'seed' things by either dragging the column metadata in from the Repository browser onto a link, or directly via the Load button inside the stage. The method depends on if I want blank derivations or not. And since I've typically built and tested the query from TOAD first, I can then just paste in the 'other' clauses the stage needs.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers