Append Hint in Oracle Insert from Datastage Connector

sangi1981 · Post by **sangi1981** » Thu Apr 16, 2015 3:07 pm

Hi all,
I have a question about use of append hint from Oracle Connector.

I configured Connector with insert option and auto generated query.

Is there some properties that can be used to add /*+ append*/ hint?

Thanks for your answer.

Best regards
Sandro

chulett · Post by **chulett** » Thu Apr 16, 2015 3:21 pm

I'm not aware of any way for auto-generated queries to include hints.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 16, 2015 4:19 pm

Set the Table Action property value to Append.
Note that this may use function calls via the OCI to specify to append to an existing table, rather than using hints in the generated SQL.

sangi1981 · Post by **sangi1981** » Fri Apr 17, 2015 5:07 am

Hi,
so using Oracle Connector Stage, configured as:

Code: Select all

auto-generated query = YES
table = table_name
Table Action = Append

is equivalent to:

Code: Select all

auto_generated query = NO
query = insert /*+append*/ into table_name ....

?

Is it right?
Thanks,
Sandro

chulett · Post by **chulett** » Fri Apr 17, 2015 7:38 am

I don't know... they use the word 'append' in a pretty generic sense which leads me to believe they are not equivalent:

by default the IBM InfoSphere DataStage appends to existing tables, you can also decide to create a new table, replace an existing table, or keep existing table details but replace all the rows.

This rather than Create, Replace or Truncate. Best to check with your official support provider. Or work with your DBA and run a small test, see if you can confirm/deny yourself. I would think it's more about the Write Mode than Table Action but the only 'direct path' equivalent there would be a Bulk Load.

Don't forget that Oracle will happily ignore the hint if you don't meet the requirements it needs as noted here.

sangi1981 · Post by **sangi1981** » Fri Apr 17, 2015 10:35 am

Thanks.
I will try to verify with support.
Thank you
Sandro