By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,008 Members | 2,963 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,008 IT Pros & Developers. It's quick & easy.

Cannot create procedure with create cluster index in it

P: n/a
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".

Feb 2 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Feb 2, 8:06 am, "JohnnyDeep" <jean-guy....@desjardins.comwrote:
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

Feb 2 '07 #2

P: n/a
On Feb 2, 1:27 pm, "jefftyzzer" <jefftyz...@sbcglobal.netwrote:
On Feb 2, 8:06 am, "JohnnyDeep" <jean-guy....@desjardins.comwrote:


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,
Feb 5 '07 #3

P: n/a
On Feb 5, 7:29 am, "JohnnyDeep" <jean-guy....@desjardins.comwrote:
On Feb 2, 1:27 pm, "jefftyzzer" <jefftyz...@sbcglobal.netwrote:


On Feb 2, 8:06 am, "JohnnyDeep" <jean-guy....@desjardins.comwrote:
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

Feb 5 '07 #4

P: n/a
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.
Feb 6 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.