SQLCODE -204 using a jdbc-driver to UDB DB2 v8.2

[David H. Cook]

Hi -

I just installed the express-edition of DB2 server (on a Win-XP
machine),
and then I setup a client JDBC driver to access it. The query is a
simple
'select * from EMPLOYEE' against their SAMPLE database.

(This query works fine from within 'Control Center'...there is data in
this table, etc.)

The 'connect' using JDBC works fine. It is just the SQL-query that
gives the error.
(I'm using what appears to be the latest 'type-4' (thin) driver, with
'fix patch 11'.)

So, since I'm a novice at the DB2 server, I have no CLUE where to find
a list
of SQLCODEs. (e.g. to look up value -204). Or, maybe I should be
learning
how to examine the server's log files?!?

Please advise.

TIA...

Dave

[Knut Stolze]

David H. Cook wrote:

Hi -

I just installed the express-edition of DB2 server (on a Win-XP
machine),
and then I setup a client JDBC driver to access it. The query is a
simple
'select * from EMPLOYEE' against their SAMPLE database.

(This query works fine from within 'Control Center'...there is data in
this table, etc.)

The 'connect' using JDBC works fine. It is just the SQL-query that
gives the error.
(I'm using what appears to be the latest 'type-4' (thin) driver, with
'fix patch 11'.)

So, since I'm a novice at the DB2 server, I have no CLUE where to find
a list
of SQLCODEs. (e.g. to look up value -204). Or, maybe I should be
learning
how to examine the server's log files?!?

Take the SQLCODE value (-204), remove the sign and prepend a SQL. This will
give you the message number. Then do this:

$ db2 "? <msgnum>"

$ db2 "? SQL204"

gives:

SQL0204N "<name>" is an undefined name.
Most probably the issue is that you have not provided the proper schema
name/qualifier for the EMPLOYEE table. Note that _all_ objects in DB2 are
placed in a schema and if you don't include the schema name explicitly
(which is my general recommendation to avoid problems), various rules apply
to find an object.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[David H. Cook]

Thanks, Knut. I finally figured it out.

Another older thread had the answer:
"If the userid you are using is db2admin, and you don't supply a high
level
qualifier on the table name, the DB2 assumes that DB2ADMIN is the
qualifier
(schema) name. You can use the SET SCHEMA statement before hand, use an
alias, or use the a userid with the same name as your schema."

My 'fix' was to change my query from:
select * from employee
to the form:
select * from yaker.employee

where 'yaker' just happens to be my user-ACCOUNT name on Win-XP.

[I don't fully understand WHY one needs to add these 'schema'
named things, but I also encountered similar concepts under
OTHER vendor's DB servers. So, I'll blindly henceforth learn to
prepend this 'schema-name-thing' onto my table names, when
using DB2 server. Luckily, I'm done with my evaluation of the
various most-common database-SERVERS, so I'll go back to
mostly just using my favorite 'MySQL' server now, and mostly
forget about this little tweak.]

Cheers and thanks again...

Dave

[Knut Stolze]

David H. Cook wrote:

[I don't fully understand WHY one needs to add these 'schema'
named things,
A schema as a namespace. It is essential to uniquely identify an object
like a table.

If you don't provide the schema name, DB2 will use some defaults.
so I'll go back to
mostly just using my favorite 'MySQL' server now, and mostly
forget about this little tweak.]

One could also say that you just leave such (quite useful) features
behind. ;-)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bernd Hohmann]

David H. Cook wrote:

Luckily, I'm done with my evaluation of the
various most-common database-SERVERS, so I'll go back to
mostly just using my favorite 'MySQL' server now, and mostly
forget about this little tweak.]

Why driving VW when you can afford a Porsche? :-)

Bernd

[David H. Cook]

Oh, the light dawns a little brighter!

Now I think I understand a bit BETTER what is going on.
(And, it seems like maybe the 'DB2' server's and tool's
approach is unnecessarily complicating things!?)

Among the FOUR db-servers I've been evaluating, there
are certainly others that also follow this '<schema.dbname >'
way of architecting things. M$'s SQLServer also comes to
mind, and I think Oracle does this too.

But, the Oracle and M$ products seem to do a much BETTER
job of 'automagically' doing the right thing.

It seems noteworthy that DB2 was the ONLY vendor that
auto-creates a 'user-ACCT' (named DB2ADMIN) on the server
(Win-XP in my case).

But, I did NOT choose to fully switch over and login under the
DB2ADMIN when I did the 'create SAMPLE' database in the
tutorial steps. Instead, I authenticated (or so I thought) as
DB2ADMIN when I did do the tutorial 'create SAMPLE database'
step. Yet, seemingly 'behind my back', DB2 grabbed my underlying
user name of 'yaker' and then created a schema of that name
OVER the top of the SAMPLE database. Hence my later
confusion.

If I'm understanding this all correctly, this seems mis-guided.
To me, this is analogous to becoming 'root' on Linux or becoming
'administrator privs' on Win-NT/XP while still remaining logged
in from a user-name different from that. Once I authenticate
somehow (e.g. using Linux's SU cmd-line pgm), the OS then
names/associates all created objects with the identity of 'root',
as we want.

So, I'd argue that DB2 could/should do the same. Forget that
(underneath) I am in an acct named 'yaker'...once I authenticate
in Control Center as 'DB2ADMIN', it should use THAT identity
for all the created stuff.

Or, maybe I'm missing something. At any rate, it SEEMS to
me that the associated DB2 software is (unnecessarily) making
the 'hole' larger so that it becomes too easy to fall into, and then
the 'automagical' doesn't happen, and we instead have to learn
to pre-pend our acct-named-schema names on the front of
everything (or else learn other 'rules', as Knut mentioned?)

I guess I can say for sure is that _I_ certainly fell into the hole.
(Sigh. Maybe I just should 'fuhgetaboutit' the experience
and move on!?)

My 2-cents...

Dave

[Knut Stolze]

David H. Cook wrote:

It seems noteworthy that DB2 was the ONLY vendor that
auto-creates a 'user-ACCT' (named DB2ADMIN) on the server
(Win-XP in my case).
If you install Oracle on an AIX system, you need a dedicated user. Same
thing for Informix. And DB2 does need an instance owner. Usually, you
don't work as that user on any of those systems.
But, I did NOT choose to fully switch over and login under the
DB2ADMIN when I did the 'create SAMPLE' database in the
tutorial steps. Instead, I authenticated (or so I thought) as
DB2ADMIN when I did do the tutorial 'create SAMPLE database'
step. Yet, seemingly 'behind my back', DB2 grabbed my underlying
user name of 'yaker' and then created a schema of that name
OVER the top of the SAMPLE database. Hence my later
confusion.
I don't know exactly how you created the sample database. If the "db2sampl"
executable was used, then the following sentence from the DB2 documentation
regarding this executable applies:
---------------------------------------------
The qualifiers for the tables in SAMPLE are determined by the user ID
issuing the command.
---------------------------------------------
where qualifiers are the schema names. (I have no clue why a different
terminology was adopted there.)
If I'm understanding this all correctly, this seems mis-guided.
To me, this is analogous to becoming 'root' on Linux or becoming
'administrator privs' on Win-NT/XP while still remaining logged
in from a user-name different from that.
I don't understand you there.

It seems that you were still logged in as "yaker" at the OS level. Given
that DB2 uses the OS for authentication purposes (unless you're using your
own security plugin), the user ID will be "yaker" if you don't specify
anything else during a CONNECT operation. Unfortunately, "db2sampl"
doesn't offer a way to specify different credentials.
Once I authenticate
somehow (e.g. using Linux's SU cmd-line pgm), the OS then
names/associates all created objects with the identity of 'root',
as we want.
No. The schema of the objects depend on the credentials that you used when
you connected to a DB2 database.
So, I'd argue that DB2 could/should do the same. Forget that
(underneath) I am in an acct named 'yaker'...once I authenticate
in Control Center as 'DB2ADMIN', it should use THAT identity
for all the created stuff.
This is what's happening - usually.
Or, maybe I'm missing something. At any rate, it SEEMS to
me that the associated DB2 software is (unnecessarily) making
the 'hole' larger so that it becomes too easy to fall into, and then
the 'automagical' doesn't happen, and we instead have to learn
to pre-pend our acct-named-schema names on the front of
everything (or else learn other 'rules', as Knut mentioned?)
The rules are quite simple:

The CURRENT SCHEMA special register is consulted when an object is created.
At query-time, the CURRENT FUNCTION PATH special register determines which
schemata are to be searched (in which order) to find matching functions. I
think that's about all.
I guess I can say for sure is that _I_ certainly fell into the hole.
(Sigh. Maybe I just should 'fuhgetaboutit' the experience
and move on!?)

You're not the only one. :-(

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Rhino]

"David H. Cook" <Da************ ***@gmail.com> wrote in message
news:11******** *************@u 72g2000cwu.goog legroups.com...

Oh, the light dawns a little brighter!

Now I think I understand a bit BETTER what is going on.
(And, it seems like maybe the 'DB2' server's and tool's
approach is unnecessarily complicating things!?)

Among the FOUR db-servers I've been evaluating, there
are certainly others that also follow this '<schema.dbname >'
way of architecting things. M$'s SQLServer also comes to
mind, and I think Oracle does this too.

But, the Oracle and M$ products seem to do a much BETTER
job of 'automagically' doing the right thing.

It seems noteworthy that DB2 was the ONLY vendor that
auto-creates a 'user-ACCT' (named DB2ADMIN) on the server
(Win-XP in my case).

But, I did NOT choose to fully switch over and login under the
DB2ADMIN when I did the 'create SAMPLE' database in the
tutorial steps. Instead, I authenticated (or so I thought) as
DB2ADMIN when I did do the tutorial 'create SAMPLE database'
step. Yet, seemingly 'behind my back', DB2 grabbed my underlying
user name of 'yaker' and then created a schema of that name
OVER the top of the SAMPLE database. Hence my later
confusion.

If I'm understanding this all correctly, this seems mis-guided.
To me, this is analogous to becoming 'root' on Linux or becoming
'administrator privs' on Win-NT/XP while still remaining logged
in from a user-name different from that. Once I authenticate
somehow (e.g. using Linux's SU cmd-line pgm), the OS then
names/associates all created objects with the identity of 'root',
as we want.

So, I'd argue that DB2 could/should do the same. Forget that
(underneath) I am in an acct named 'yaker'...once I authenticate
in Control Center as 'DB2ADMIN', it should use THAT identity
for all the created stuff.

Or, maybe I'm missing something. At any rate, it SEEMS to
me that the associated DB2 software is (unnecessarily) making
the 'hole' larger so that it becomes too easy to fall into, and then
the 'automagical' doesn't happen, and we instead have to learn
to pre-pend our acct-named-schema names on the front of
everything (or else learn other 'rules', as Knut mentioned?)

I guess I can say for sure is that _I_ certainly fell into the hole.
(Sigh. Maybe I just should 'fuhgetaboutit' the experience
and move on!?)

My 2-cents...

And here's mine.

If you're going to rule out DB2 simply on the grounds of having a slightly
different default behaviour that some of its competitors in one small aspect
and not even consider all the strengths of DB2, then you're not doing a very
thorough evaluation.

It's as if you are testing the world's most exotic sports cars to see which
is the best, then ruling one out simply because you don't like the shape of
the shift knob, even if it is "best of breed" in many categories.

A discerning evaluation would focus on the _important_ aspects of what the
different database servers offer, not the trivial ones, and would note _all_
the strengths and weaknesses of each product, not just dismiss one
categorically over a minor point.

If you are legally liable for the opinion you're rendering, i.e. if you've
been hired by someone to choose the best of the four database servers for
them, you should really rethink your evaluation method. Otherwise, your
customer is like you to sue for your very dubious approach to product
evaluations. I know I'd be pretty unhappy if I bought the product you
recommended and then found out how you'd come up with the "winner". Even if
you are simply an employee delegated to choose the product, you should think
very carefully because your management is not going to be thrilled if they
find out how you chose the best server and that could hurt your prospects
for advancement, even if it doesn't provoke lawsuits against you.

I'm not saying DB2 is unquestionably the best of the products you are
comparing - I have never used some of the databases you are examining - but
it certainly deserves more thorough consideration than you've given it.

--
Rhino

[Serge Rielau]

David H. Cook wrote:

Oh, the light dawns a little brighter!

Now I think I understand a bit BETTER what is going on.
(And, it seems like maybe the 'DB2' server's and tool's
approach is unnecessarily complicating things!?) It works just like your any Unix OS (and nearly like DOS)
good company.
It seems noteworthy that DB2 was the ONLY vendor that
auto-creates a 'user-ACCT' (named DB2ADMIN) on the server
(Win-XP in my case). No it doesn't. When you installed DB2 proposed DB2ADMIN and you
apparently hit the OK button. On my laptop DB2 is owned by my normal ID
because I typed in my id at install.

Or, maybe I'm missing something.

Yes you are CURRENT SCHEMA = current working directory.
The SCHEMA is as orthogonal from your id as your directory.
You can cd into any directory as long as you have permissions.
The ONLY correlation between USER and SCHEMA is that after you connect
by default the schema is equal to the user under which you authenticated.
This default can be overridden with connection properties that are
client specific.

Now before you create your first function please look up PATH.
Hint: It works just like PATH in your OS.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[David H. Cook]

Ah, I guess that I inadvertently mis-led, when I
mentioned that I was 'evaluating' DB2.

What I was IN FACT doing was installing versions
of 4 DB packages (Oracle, SQLServer, DB2, and MySQL)
so that I could TEST a Java JDBC application against
each of them. As you may know, each JDBC driver
interface has slightly different URL syntax, etc, etc,
and I wanted to make sure my Java app would talk
to each DB server. (So, my goal was NOT to see
which DB server I perfer.) Thus, I am NOT
accepting or rejecting DB2...rather, just commenting
on some aspects that seemed different or troublesome
as I setup to carry out my tests.

Cheers...
Dave