Undefined name

Hi

I'm trying to set up an application that uses a DB2 database.
I know nothing at all about DB2 (or Java) and have simply followed a set of
bad instructions to try and run this application.

This is the error I get:

SQL=[select sharepar.choiceparam.item, sharepar.choiceparam.description from
sharepar, sharepar.choiceparam WHERE sharepar.choiceparam.lang = ? AND
sharepar.choiceparam.choicename = ? AND sharepar.choiceparam.status = ?
ORDER BY sharepar.choiceparam.description ASC]: Unable to run query:
[IBM][CLI Driver][DB2/NT] SQL0204N "DB2ADMIN.SHAREPAR" is an undefined name.
SQLSTATE=42704

I don't know where it gets "DB2ADMIN.SHAREPAR" from as it is not in the
query. I assume it is attaching the username which was the same username
used to create the tables and therefore (as I understand) should have all
the necessary rights over those tables.

How do I get it to either stop referring to the DB2ADMIN part or to
recognise it as a valid name?

Details:
The user set-up to access the database is DB2ADMIN.
It is a DB2 Universal Database Personal Edition Ver 7
The application accessing it a Java class JDK 1.2.2 (I do not have access to
the source)
The application server is Enhydra 3.0.1
I also had to install something called 'Database Links' - I have no idea
what this is or what it's purpose is
I'm using Win XP Prof

Thanks in advance, Stephen

re: Undefined name

"Laygo" <google@com-du-it.com> wrote in message
news:76e257e7.0406132333.68f50dc@posting.google.co m...[color=blue]
> Hi
>
> I'm trying to set up an application that uses a DB2 database.
> I know nothing at all about DB2 (or Java) and have simply followed a set[/color]
of[color=blue]
> bad instructions to try and run this application.
>
> This is the error I get:
>
> SQL=[select sharepar.choiceparam.item, sharepar.choiceparam.description[/color]
from[color=blue]
> sharepar, sharepar.choiceparam WHERE sharepar.choiceparam.lang = ? AND
> sharepar.choiceparam.choicename = ? AND sharepar.choiceparam.status = ?
> ORDER BY sharepar.choiceparam.description ASC]: Unable to run query:
> [IBM][CLI Driver][DB2/NT] SQL0204N "DB2ADMIN.SHAREPAR" is an undefined[/color]
name.[color=blue]
> SQLSTATE=42704
>
> I don't know where it gets "DB2ADMIN.SHAREPAR" from as it is not in the
> query. I assume it is attaching the username which was the same username
> used to create the tables and therefore (as I understand) should have all
> the necessary rights over those tables.
>
> How do I get it to either stop referring to the DB2ADMIN part or to
> recognise it as a valid name?
>
> Details:
> The user set-up to access the database is DB2ADMIN.
> It is a DB2 Universal Database Personal Edition Ver 7
> The application accessing it a Java class JDK 1.2.2 (I do not have access[/color]
to[color=blue]
> the source)
> The application server is Enhydra 3.0.1
> I also had to install something called 'Database Links' - I have no idea
> what this is or what it's purpose is
> I'm using Win XP Prof
>
> Thanks in advance, Stephen[/color]

DB2ADMIN is the user id you are using for dynamic SQL or the schema name
used during the bind of static SQL. I am not sure which is being used in
your application (dynamic or static SQL).

All tables have a tablename and schema name to uniquely identify them. Using
the Control Center and looking a the list of tables, is there a table name
SHAREPAR, and if so, what is the schema name(s) ?

re: Undefined name

"Mark A" <ma@switchboard.net> wrote in message
news:rJczc.356$Uf2.98135@news.uswest.net...[color=blue]
>
> DB2ADMIN is the user id you are using for dynamic SQL or the schema name
> used during the bind of static SQL. I am not sure which is being used in
> your application (dynamic or static SQL).
>
> All tables have a tablename and schema name to uniquely identify them.[/color]
Using[color=blue]
> the Control Center and looking a the list of tables, is there a table name
> SHAREPAR, and if so, what is the schema name(s) ?
>[/color]
If you don't know how to start the Control Center submit the following query
from a command prompt. This will list all the table names and views on your
system.

db2 "select tabschema, tabname from syscat.tables"

re: Undefined name

Stephen,
Can you issue a SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1
right before that query.
Your current user may be DB2ADMIN, but maybe previous code switched the
schema.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

re: Undefined name

"Mark A" <ma@switchboard.net> wrote in message news:<rJczc.356$Uf2.98135@news.uswest.net>...[color=blue]
> DB2ADMIN is the user id you are using for dynamic SQL or the schema name
> used during the bind of static SQL. I am not sure which is being used in
> your application (dynamic or static SQL).
>
> All tables have a tablename and schema name to uniquely identify them. Using
> the Control Center and looking a the list of tables, is there a table name
> SHAREPAR, and if so, what is the schema name(s) ?[/color]

The table is "choiceparam", "sharepar" is the schema name.

I have no idea whether it's static or dynamic SQL.

Executing "SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1" returned
"DB2ADMIN"

While I could see the tables when looking at the Control Center, they
didn't apprear to show up when executing "select tabschema, tabname
from syscat.tables".

The code is in what I understand to be a precomplied Java class and I
have no access to the source (unless someone can tell me how to
view/change the code in a class file? Looks like greek in a text
editor).

This is the SQL used to create that particular table (while connected
to the database as db2admin):

create table sharepar.choiceparam ( \
lang VARCHAR(10) DEFAULT ' ' NOT NULL , \
choicename VARCHAR(10) DEFAULT ' ' NOT NULL , \
item VARCHAR(10) DEFAULT ' ' NOT NULL , \
description VARCHAR(80) DEFAULT ' ' NOT NULL , \
status VARCHAR(10) DEFAULT ' ' , \
oid DECIMAL(19,0) NOT NULL PRIMARY KEY, \
version INTEGER NOT NULL )

-- Choiceparm indexes

CREATE INDEX I1_sharepar.choiceparam ON sharepar.choiceparam ( lang )
CREATE INDEX I2_sharepar.choiceparam ON sharepar.choiceparam (
choicename )
CREATE INDEX I3_sharepar.choiceparam ON sharepar.choiceparam ( item )

re: Undefined name

"Laygo" <google@com-du-it.com> wrote in message
news:76e257e7.0406140631.3eff80e5@posting.google.c om...[color=blue]
> The table is "choiceparam", "sharepar" is the schema name.
>
> I have no idea whether it's static or dynamic SQL.
>
> Executing "SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1" returned
> "DB2ADMIN"
>
> While I could see the tables when looking at the Control Center, they
> didn't apprear to show up when executing "select tabschema, tabname
> from syscat.tables".
>
> The code is in what I understand to be a precomplied Java class and I
> have no access to the source (unless someone can tell me how to
> view/change the code in a class file? Looks like greek in a text
> editor).
>
> This is the SQL used to create that particular table (while connected
> to the database as db2admin):
>
> create table sharepar.choiceparam ( \
> lang VARCHAR(10) DEFAULT ' ' NOT NULL , \
> choicename VARCHAR(10) DEFAULT ' ' NOT NULL , \
> item VARCHAR(10) DEFAULT ' ' NOT NULL , \
> description VARCHAR(80) DEFAULT ' ' NOT NULL , \
> status VARCHAR(10) DEFAULT ' ' , \
> oid DECIMAL(19,0) NOT NULL PRIMARY KEY, \
> version INTEGER NOT NULL )
>
> -- Choiceparm indexes
>
> CREATE INDEX I1_sharepar.choiceparam ON sharepar.choiceparam ( lang )
> CREATE INDEX I2_sharepar.choiceparam ON sharepar.choiceparam (
> choicename )
> CREATE INDEX I3_sharepar.choiceparam ON sharepar.choiceparam ( item )[/color]

Here is the SQL statement (after running through a formatter):

SELECT
sharepar.choiceparam.item,
sharepar.choiceparam.description
FROM sharepar,
sharepar.choiceparam
WHERE sharepar.choiceparam.lang = ?
AND sharepar.choiceparam.choicename = ?
AND sharepar.choiceparam.status = ?
ORDER BY sharepar.choiceparam.description ASC

This SQL statement has two tables in the FROM clause and the first one
(without a explicit schema name) is causing the problem. The table name is
sharepar and since the schema is not specified, DB2 used your userid
(DB2ADMIN). I think this is a mistake of some kind, and the table "sharepar"
should not exist in the above SQL statement.

You can have two tables in a SELECT, but the 2 tables would normally be
joined with a join predicates (which don't exist here). I would contact the
person who wrote the code, because it looks defective. I think the code FROM
clause should say:

FROM sharepar.choiceparam

Sorry I didn't notice this earlier.

re: Undefined name

No problem.

The developers long disappeared in a time when many people leave this
country for 'greener pastures'.

If I create a table called sharepar which does nothing... would this
allow that SQL statement to execute? I wonder... and will test it. If
it won't work I would appreciate any suggestions as to how else to
trick it.

Thanks for the assistance, Stephen

"Mark A" <ma@switchboard.net> wrote in message news:<ePjzc.12$im2.16549@news.uswest.net>...[color=blue]
> This SQL statement has two tables in the FROM clause and the first one
> (without a explicit schema name) is causing the problem. The table name is
> sharepar and since the schema is not specified, DB2 used your userid
> (DB2ADMIN). I think this is a mistake of some kind, and the table "sharepar"
> should not exist in the above SQL statement.
>
> You can have two tables in a SELECT, but the 2 tables would normally be
> joined with a join predicates (which don't exist here). I would contact the
> person who wrote the code, because it looks defective. I think the code FROM
> clause should say:
>
> FROM sharepar.choiceparam
>
> Sorry I didn't notice this earlier.[/color]

re: Undefined name

Creating the table to trick it worked.

But from one problem to the next... the '?' in the sql statement... I
had understood this to be a wildcard. I'm assuming now that it is in
fact a paramater placeholder. I can't figure out where it gets the
information to pass to the sql as parameters so it looks like I'm
going to have to hack in to get the information I need and then dump
the whole thing. *Groan*

Have a good one... Stephen

"Mark A" <ma@switchboard.net> wrote in message news:<ePjzc.12$im2.16549@news.uswest.net>...[color=blue]
> This SQL statement has two tables in the FROM clause and the first one
> (without a explicit schema name) is causing the problem. The table name is
> sharepar and since the schema is not specified, DB2 used your userid
> (DB2ADMIN). I think this is a mistake of some kind, and the table "sharepar"
> should not exist in the above SQL statement.
>
> You can have two tables in a SELECT, but the 2 tables would normally be
> joined with a join predicates (which don't exist here). I would contact the
> person who wrote the code, because it looks defective. I think the code FROM
> clause should say:
>
> FROM sharepar.choiceparam
>
> Sorry I didn't notice this earlier.[/color]