SET CURRENT OPTIMIZATION PROFILE

[m***@in.tum.de]

hi guys,
the following article
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0612chen/index.html
discuss among other things the optimization profiles.

But when i try to execute the following command:
SET CURRENT OPTIMIZATION PROFILE="/home/ntap/mayrm/overall_experiments/dummy1/XMARK_0_958/g_0_958/q14_prof"'

I get this one:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "OPTIMIZATION" was found following "CURRENT".
Expected tokens may include: "DEGREE". SQLSTATE=42601

Any Suggestions?

Thx in advance.

PS.: I even set the DB2_OPTPROFILE to yes.

[Ian]

Post by m***@in.tum.de
hi guys,
the following article
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0612chen/index.html
discuss among other things the optimization profiles.
SET CURRENT OPTIMIZATION PROFILE="/home/ntap/mayrm/overall_experiments/dummy1/XMARK_0_958/g_0_958/q14_prof"'

The "current optimization profile" special register should be set to the
optimization profile's schema.name (not a file).

The profile needs to exist in the SYSTOOLS.OPT_PROFILE table.

[m***@in.tum.de]

Ok that's for sure one of the reasons.
But now after i set up the profile table,
the command doesn't work anyway.
It breaks up with the same error message.

It seems that db2 has parsing problems, due to
the following observations:

db2 'SET CURRENT OPTIMIZATION PROFILE=XMARK_0_958.profile1'
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "OPTIMIZATION" was found following "CURRENT".
Expected tokens may include: "DEGREE". SQLSTATE=42601

db2 'SET CURRENT OPTIMIZATION XMARK_0_958.profile1'
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "XMARK_0_958" was found following "CURRENT
OPTIMIZATION". Expected tokens may include: "PROFILE". SQLSTATE=42601

db2 'SET CURRENT PROFILE=XMARK_0_958.profile1'
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "PROFILE" was found following "SET CURRENT ".
Expected tokens may include: "OPTIMIZATION". SQLSTATE=42601

Best regards,
Manuel

[Thomas Ritter]

Hi,

which version of DB2 are you using?

Regards,
Thomas

[m***@in.tum.de]

I was using DB 9.1, but without FixPacks.
Installing FixPack 3 solved the problem.

Thx for your suggestions,
Manuel

[sIvAsWaMi]

Quotes like this.
C:\Documents and Settings\Administrator>DB2 "SET CURRENT OPTIMIZATION PROFILE='X
.PROFILE'"
DB20000I The SQL command completed successfully.

C:\Documents and Settings\Administrator>

Sivaswami
IBM DB2 Dev

Post by Thomas Ritter
Hi,
which version of DB2 are you using?
Regards,
Thomas