| Author |
Message
|
| nab054371 |
 Posted: Mon Jan 08, 2007 10:10 am Post subject: Oracle hints in compute node |
 |
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
Has anyone used oracle hints inside of compute node,if yes how do i get it to work?
for example:
SET InputBody.test[]=(SELECT /*+ index(T,T_PK) */ * from Database.testname AS T) |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Mon Jan 08, 2007 10:15 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Any database specific commands have to be done using PASSTHRU, not SELECT.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| nab054371 |
| Posted: Mon Jan 08, 2007 12:18 pm Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
| Is this available in 6.0 only.I do not see it in 5.0? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Jan 08, 2007 12:21 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| nab054371 wrote: |
| Is this available in 6.0 only.I do not see it in 5.0? |
It's an ESQL statement. It's been available since 2.1.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| nab054371 |
| Posted: Mon Jan 08, 2007 12:59 pm Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
| Can I change datasourcename property dynamically on compute node in 5.0? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Jan 08, 2007 1:04 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
no.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| nab054371 |
| Posted: Mon Jan 08, 2007 1:18 pm Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
OK here's what I found with PASSTHRU in V5.0:
It does not let you have TO Database.<dbname> values('abc') in 5.0
Here's what I need to get working though:
DECLARE SCHEMANAME CHAR 'DEVELOPMENT';
set OutputRoot.XML.rsername = ('SELECT T.USERID from Database.{SCHEMANAME}.users where T.userid = 'abc');
It does not seem to recognize SCHEMANAME.How can I get this to work? |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Jan 09, 2007 1:22 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
As Jeff said, use PASSTHRU
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| nab054371 |
| Posted: Tue Jan 09, 2007 7:33 am Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
PASSTHRU does not work.Here's what it does:
PASSTHRU('SELECT * from Database.{SCHEMANAME}.testtbl')
It does nto do any subsitituion for SCHEMANAME.
In otherwords the sql query when it gets to Oracle looks something like this:
'SELECT * from Database.{SCHEMANAME}.testtbl
Resulting in Oracle exception. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Jan 09, 2007 7:47 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You're correct. PASSTHRU does not do any ESQL evaluation of the statement. In ESQL, the statement is just text.
| Code: |
SET mySQLStatement = 'SELECT * from '||SCHEMANAME||'.testtbl';
PASSTHRU(mySQLStatement) |
Notice that I did not put in "Database." - this is because the statement needs to be valid Oracle SQL and therefore can't be valid ESQL.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| nab054371 |
| Posted: Tue Jan 09, 2007 8:33 am Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
|
| Back to top |
|
|
| nab054371 |
| Posted: Tue Jan 09, 2007 8:47 am Post subject: |
|
|
Disciple
Joined: 15 Nov 2006
Posts: 173
|
How do I get this to work:
DECLARE SCHEMANAME CHAR 'CTE'
IF EXISTS(SELECT * FROM Database.{SCHEMANAME}.USER_TBL AS T WHERE T.USER_ID = 'TGB056006') THEN
It looks like it does not find {SCHEMANAME} ? |
|
| Back to top |
|
|
|
|
