How to use oracle hints in datastage

mchaves · Post by **mchaves** » Wed Aug 10, 2005 8:30 pm

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,

kduke · Post by **kduke** » Wed Aug 10, 2005 9:27 pm

Post the code and the syntax error. Not enough information here. It should work based on what you have stated.

chulett · Post by **chulett** » Wed Aug 10, 2005 9:43 pm

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.

mchaves · Post by **mchaves** » Wed Aug 10, 2005 10:26 pm

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.

Thanks guys, I have sorted it out. It's working. I might have typed something that ds couldn't compile.

the sql is like this:

insert into table /*+ append */ ( a, b ) values (:1,:2)

Cheers,

Sreenivasulu · Post by **Sreenivasulu** » Thu Aug 11, 2005 12:44 am

Hints work. I have used no_merge hint using user-defined sql

Regards
Sreenivasulu

rleishman · Post by **rleishman** » Sun Sep 25, 2005 10:58 pm

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

ray.wurlod · Post by **ray.wurlod** » Sun Sep 25, 2005 11:01 pm

I shun the expression builder, so won't have seen this. I've certainly used the /*+ APPEND */ hint to good effect here and there (but still prefer sqlldr).

chulett · Post by **chulett** » Sun Sep 25, 2005 11:26 pm

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.

rleishman · Post by **rleishman** » Sun Sep 25, 2005 11:42 pm

Ahhhh. The veil has been withdrawn!!! Column Generated is my new best friend.

It even lets you slip a DISTINCT into the derivation of the first column.

The SQL Builder should come with compulsory public health warnings.

Thanks for your help guys.

vmcburney · Post by **vmcburney** » Mon Sep 26, 2005 12:16 am

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.

chulett · Post by **chulett** » Mon Sep 26, 2005 6:42 am

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.