Error in DROP INDEX statement?

[Franco Lombardo]

Hi all,

I'm running this DB2 version: DB2/LINUXPPC64 10.1.0

I run this script:

db2 => create schema "S"
DB20000I The SQL command completed successfully.
db2 => CREATE TABLE "S"."T" ("C1" CHAR(10))
DB20000I The SQL command completed successfully.
db2 => CREATE INDEX "I" ON "S"."T" ("C1")
DB20000I The SQL command completed successfully.
db2 => drop index "S"."I"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "S.I" is an undefined name. SQLSTATE=42704

I see that there was another discussion about this problem, but there wasn't
any solution.
http://www.dbforums.com/showthread.php?1632059-Problem-in-Drop-Index-DB2-8-2

Any hint?

Thanks in advance.

Bye

Franco

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.francolombardo.net
Scala, Java, As400.....
http://twitter.com/f_lombardo
http://www.linkedin.com/in/francolombardo
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[Mark A]

On Tue, 28 Oct 2014 12:32:37 +0100, Franco Lombardo wrote:

> Hi all,
>
> I'm running this DB2 version: DB2/LINUXPPC64 10.1.0
>
> I run this script:
>
> db2 => create schema "S"
> DB20000I The SQL command completed successfully. db2 => CREATE TABLE
> "S"."T" ("C1" CHAR(10)) DB20000I The SQL command completed
> successfully. db2 => CREATE INDEX "I" ON "S"."T" ("C1") DB20000I The
> SQL command completed successfully. db2 => drop index "S"."I"
> DB21034E The command was processed as an SQL statement because it was
> not a valid Command Line Processor command. During SQL processing it
> returned: SQL0204N "S.I" is an undefined name. SQLSTATE=42704
>
> I see that there was another discussion about this problem, but there
> wasn't any solution.
> http://www.dbforums.com/showthread.php?1632059-Problem-in-Drop-Index-DB2-8-2
>
> Any hint?
>
> Thanks in advance.
>
> Bye
>
> Franco

1. If you acutally using 10.1.0 (GA code) please upgrade to Fix Pack 4 before proceeding.

2. DB2 is not case senstive, so don't use any double-quotes. You will reget it if you continue that.

3. db2 list tables for all

4. db2 describe indexes for table x.x (copy and paste name from step 3).

[Franco Lombardo]

"Mark A" wrote

> 1. If you acutally using 10.1.0 (GA code) please upgrade to Fix Pack 4
> before proceeding.

I'm running the Express edition on Power Linux, so I don't know if it is
possible to upgrade it. If it is possible, could you explain how to do
that?

>2. DB2 is not case senstive, so don't use any double-quotes. You will reget
>it if you continue that.

Well, I'm actually writing a program that converts SQL table from other DBs
into DB2. Since the source tables could possibly contain strange characters,
I thougt to quote these names.
So, my question is: is quoting tables and indexes names admitted in DB2? If
it is, why shouldn't I use it?

> 3. db2 list tables for all
> 4. db2 describe indexes for table x.x (copy and paste name from step 3).

Command at step 3 shows table s.t, but it is not found by command at step 4.
Anyway, if I select data with
db2 "select * from syscat.indexes where tabname = 't'"

I can see my index.

By the way, have you tried my script? Does it work on your DB2?

Thank you.

Bye

Franco

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.francolombardo.net

[Franco Lombardo]

Here is the problem in my script

CREATE INDEX "I" ON "S"."T" ("C1")

instead it should be

CREATE INDEX "S". "I" ON "S"."T" ("C1")

Sorry for the inconvenience.

Thanks.

Bye

Franco


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.francolombardo.net

[Mark A]

On Tue, 28 Oct 2014 16:19:36 +0100, Franco Lombardo wrote:

> Well, I'm actually writing a program that converts SQL table from other
> DBs into DB2. Since the source tables could possibly contain strange
> characters, I thougt to quote these names.
> So, my question is: is quoting tables and indexes names admitted in DB2?
> If it is, why shouldn't I use it?

DB2 is case-insensitive IF you don't use quotes. If you quote a lower case character, then it will only
accept lower case and will no longer be case-insensitive. This has been known to casue suicides
among DBA's or developers who are trying to figure out why their SQL does not work.

[Franco Lombardo]

"Mark A" wrote:

>[...] This has been known to casue suicides

> among DBA's or developers who are trying to figure out why their SQL does
> not work.

:-O

I need to write my testament.....

:-)

Thanks.

Bye

[Luiz da Silva]

Franco,

If you don't fully qualify your object(in this case an index), DB2 use the user id connected to the database as the default schema.

I see 2 options that would work in this situation: or you use "set current schema <schema_name>" at the beginning to set your default schema so all unqualified objects will use such schema OR you add your schema everywhere(my preferred choice as it make it clear).

Regards,

Luiz

[Franco Lombardo]

Luiz,

thank you for your answer. I will change the script to use a full qualified
index name.

[Alexander Veremyev]

On Tuesday, October 28, 2014 10:29:39 PM UTC+3, Mark A wrote:
> DB2 is case-insensitive IF you don't use quotes. If you quote a lower case
> character, then it will only accept lower case and will no longer be
> case-insensitive. This has been known to casue suicides among DBA's or
> developers who are trying to figure out why their SQL does not work.

You never heard about Cyrillic 'C' which is on the same keyboard key as ASCII 'C' (so it may be typed by mistake using different keyboard layout).
It may give you several unforgettable hours even if you know about quotation :)

The interesting fact is that DB2 objects may be named using national languages by the use of quotes, but it's definitely not a good practice.

Best regards, Alex.