Cannot create procedure with create cluster index in it

[JohnnyDeep]

I am trying to create a store proc that contain a create index with
the cluster option and I receive

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0623N A clustering index already exists on table "PGIR.TF_RRCE".
LINE
NUMBER=35. SQLSTATE=55012

I did try to use
CALL SET_ROUTINE_OPTS('DYNAMICRULES RUN VALIDATE RUN')
before calling the create store proc but I still get the error.

If I skip the cluster option in the create index, then the create proc
work!!!

Any idea how to create the store proc in db2 without object
validation?

Thanks in advance

$ db2level
DB21085I Instance "db2dev6" uses "64" bits and DB2 code release
"SQL08024"
with level identifier "03050106".
Informational tokens are "DB2 v8.1.1.104", "s060120", "U805924", and
FixPak
"11".
Product is installed at "/usr/opt/db2_08_FP11".

[jefftyzzer]

On Feb 2, 8:06 am, "JohnnyDeep" <[e-mail]:

I am trying to create a store proc that contain a create index with
the cluster option and I receive

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0623N A clustering index already exists on table "PGIR.TF_RRCE".
LINE
NUMBER=35. SQLSTATE=55012

I did try to use
CALL SET_ROUTINE_OPTS('DYNAMICRULES RUN VALIDATE RUN')
before calling the create store proc but I still get the error.

If I skip the cluster option in the create index, then the create proc
work!!!

Any idea how to create the store proc in db2 without object
validation?

Thanks in advance

$ db2level
DB21085I Instance "db2dev6" uses "64" bits and DB2 code release
"SQL08024"
with level identifier "03050106".
Informational tokens are "DB2 v8.1.1.104", "s060120", "U805924", and
FixPak
"11".
Product is installed at "/usr/opt/db2_08_FP11".

Hi, Johnny:

I'm sure you're aware that only one clustering index can exist on a
table at any one time. There's no way around this. So, is there a step
in your procedure where you're deleting this "other" clustering index
first, and your issue/question is really one of timing? Are you
getting the error when trying to create (as opposed to run) the SP?

--Jeff

[JohnnyDeep]

On Feb 2, 1:27 pm, "jefftyzzer" <[e-mail]:

On Feb 2, 8:06 am, "JohnnyDeep" <[e-mail]:

I am trying to create a store proc that contain a create index with
the cluster option and I receive

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0623N A clustering index already exists on table "PGIR.TF_RRCE".
LINE
NUMBER=35. SQLSTATE=55012

I did try to use
CALL SET_ROUTINE_OPTS('DYNAMICRULES RUN VALIDATE RUN')
before calling the create store proc but I still get the error.

If I skip the cluster option in the create index, then the create proc
work!!!

Any idea how to create the store proc in db2 without object
validation?

Thanks in advance

$ db2level
DB21085I Instance "db2dev6" uses "64" bits and DB2 code release
"SQL08024"
with level identifier "03050106".
Informational tokens are "DB2 v8.1.1.104", "s060120", "U805924", and
FixPak
"11".
Product is installed at "/usr/opt/db2_08_FP11".

Hi, Johnny:

I'm sure you're aware that only one clustering index can exist on a
table at any one time. There's no way around this. So, is there a step
in your procedure where you're deleting this "other" clustering index
first, and your issue/question is really one of timing? Are you
getting the error when trying to create (as opposed to run) the SP?

--Jeff- Hide quoted text -

- Show quoted text -

Hi Jeff,

I am receiving the error while trying to create the store procedure.

I am aware that only one cluster index should be create for a table.
I just want to create the store procedure without dropping the index
first.

The funny part is that if I create the store procedure containing a
create index on a table currently having the index, it work. It`s only
the cluster option that seem to be validate at the creation of the
store procedure (and not the fact that the index already exists)

There should be a way to create a store procedure without checking the
status of the objects within the store procedure at bind time. I just
can find the way to solve that issue.

Regards,

[jefftyzzer]

On Feb 5, 7:29 am, "JohnnyDeep" <[e-mail]:

On Feb 2, 1:27 pm, "jefftyzzer" <[e-mail]:

On Feb 2, 8:06 am, "JohnnyDeep" <[e-mail]:

I am trying to create a store proc that contain a create index with
the cluster option and I receive

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0623N A clustering index already exists on table "PGIR.TF_RRCE".
LINE
NUMBER=35. SQLSTATE=55012

I did try to use
CALL SET_ROUTINE_OPTS('DYNAMICRULES RUN VALIDATE RUN')
before calling the create store proc but I still get the error.

If I skip the cluster option in the create index, then the create proc
work!!!

Any idea how to create the store proc in db2 without object
validation?

Thanks in advance

$ db2level
DB21085I Instance "db2dev6" uses "64" bits and DB2 code release
"SQL08024"
with level identifier "03050106".
Informational tokens are "DB2 v8.1.1.104", "s060120", "U805924", and
FixPak
"11".
Product is installed at "/usr/opt/db2_08_FP11".

Hi, Johnny:

I'm sure you're aware that only one clustering index can exist on a
table at any one time. There's no way around this. So, is there a step
in your procedure where you're deleting this "other" clustering index
first, and your issue/question is really one of timing? Are you
getting the error when trying to create (as opposed to run) the SP?

--Jeff- Hide quoted text -

- Show quoted text -

Hi Jeff,

I am receiving the error while trying to create the store procedure.

I am aware that only one cluster index should be create for a table.
I just want to create the store procedure without dropping the index
first.

The funny part is that if I create the store procedure containing a
create index on a table currently having the index, it work. It`s only
the cluster option that seem to be validate at the creation of the
store procedure (and not the fact that the index already exists)

There should be a way to create a store procedure without checking the
status of the objects within the store procedure at bind time. I just
can find the way to solve that issue.

Regards,- Hide quoted text -

- Show quoted text -

One thing you might want to try is to rewrite the CREATE INDEX
statement in the stored proc as dynamic SQL.

--Jeff

[Otto Carl Marte]

This error is produced when DB2 compiles /(checks the correctness of)
the stored procedure. It notices that you already have a clustered
index on the table, and tells you so.
Like Jeff mentioned, if you turn the CREATE INDEX statement into
dynamic SQL then you shouldn't have a problem.