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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David H. Cook - 02 May 2006 14:26 GMT

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

Reply to this Message

Knut Stolze - 02 May 2006 16:17 GMT

> Hi -
>
[quoted text clipped - 17 lines]
> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

David H. Cook - 02 May 2006 17:11 GMT

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

Reply to this Message

Knut Stolze - 02 May 2006 17:25 GMT

> [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. ;-)

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Bernd Hohmann - 02 May 2006 18:32 GMT

> 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

Reply to this Message

David H. Cook - 02 May 2006 19:05 GMT

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

Reply to this Message

Rhino - 02 May 2006 20:29 GMT

> Oh, the light dawns a little brighter!
>
[quoted text clipped - 48 lines]
>
> 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

Reply to this Message

David H. Cook - 03 May 2006 04:26 GMT

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

Reply to this Message

Knut Stolze - 02 May 2006 20:42 GMT

> 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
[quoted text clipped - 4 lines]
> 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. :-(

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Serge Rielau - 02 May 2006 22:15 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

David H. Cook - 03 May 2006 04:38 GMT

Hmmm...not sure if I understand about looking up 'PATH'. Are
you saying the DB2 has a PATH object, that is similar in concept
to the OS 'PATH'?

At any rate, what I NOW think might have happened, is that
back when I installed DB2 (which is somewhat hazy to me now),
is that maybe in ADDITION to creating the DB2ADMIN user/pwd,
it also mentioned that it could create an identity 'yaker' and I
accepted and used the same password for that as I did for
DB2ADMIN. And, that I then authenticated in explicitly under
'yaker' rather than under 'DB2ADMIN' when I created the SAMPLE
database. At least, that might explain how the SAMPLE database
got MY credentials for the name of the 'schema', rather than
the name 'DB2ADMIN'.

Anyway, I think we've beat this horse to death.

Sorry if my comments sound like I'm being over-critical of
DB2's workings. That was NOT my intent.

Cheers...

Dave

Reply to this Message

Knut Stolze - 04 May 2006 11:12 GMT

> Hmmm...not sure if I understand about looking up 'PATH'. Are
> you saying the DB2 has a PATH object, that is similar in concept
> to the OS 'PATH'?

Exactly. In DB2, this is the "CURRENT PATH" special register:
http://tinyurl.com/k7lxq

> Sorry if my comments sound like I'm being over-critical of
> DB2's workings. That was NOT my intent.

That's not a problem. We just try to explain the underlying concepts of DB2
and where you might have done a step that caused your confusion. I'm glad
that you accept such explanations. ;-)

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message