RTFM, but which one?

[Jean-David Beyer]

I got this while reorganizing a database after doing perhaps a million
INSERTs. Not the best way to populate a relation, but the most practical.
Then I did a REORG and got this (looked up SQL2215N)

SQL2215N SQL error sqlcode occurred while committing previous work for the
database.

Explanation: The user was already connected to the database specified in
the Reorganize Table command. An error occurred while committing the
previous work in progress for the database. The utility stops processing
with no attempt to roll back the work or break the database connection.
User Response: Look at the SQLCODE (message number) in the message for
more information. Make changes and resubmit the command.

But where do I look up the SQLCODES? I got something like -1304 (but do
not believe this number: I am just guessing what it was). I thought there
was a place to look those up, but I can no longer find it.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 23:10:00 up 11 days, 12:06, 5 users, load average: 0.16, 0.12, 0.10

[Jan M. Nelken]

Jean-David Beyer wrote:

I got this while reorganizing a database after doing perhaps a million
INSERTs. Not the best way to populate a relation, but the most
practical. Then I did a REORG and got this (looked up SQL2215N)

SQL2215N SQL error sqlcode occurred while committing previous work for
the database.

Explanation: The user was already connected to the database specified
in the Reorganize Table command. An error occurred while committing the
previous work in progress for the database. The utility stops processing
with no attempt to roll back the work or break the database connection.
User Response: Look at the SQLCODE (message number) in the message for
more information. Make changes and resubmit the command.

But where do I look up the SQLCODES? I got something like -1304 (but do
not believe this number: I am just guessing what it was). I thought
there was a place to look those up, but I can no longer find it.

See the SQL2215N in the message above? This message is reporting SQL
Code -2215.
You can get the explanation you are quoting simply by issuing this:

db2 ? sql2215n

and to lookup "sqlcode" you are quoting

db2 ? sql1304

which tells us that this is probably not the number you think you had:

SQL1304N The TCP/IP security type SOCKS is not valid.

Explanation:

The TCP/IP security type SOCKS in the TCP/IP protocol structure
of the Catalog Node command is invalid with authentication type
DCE.

User Response:

Make sure you do not use the combination of TCP/IP protocol with
security type SOCKS and authentication type DCE.

sqlcode : -1304

sqlstate : 08001

All SQLnnnnnn messages are doumented in Messages and Codes manual
available online or as PDF file.

Jan M. Nelken

[Jean-David Beyer]

Jan M. Nelken wrote:

Jean-David Beyer wrote:

I got this while reorganizing a database after doing perhaps a million
INSERTs. Not the best way to populate a relation, but the most
practical. Then I did a REORG and got this (looked up SQL2215N)

SQL2215N SQL error sqlcode occurred while committing previous work for
the database.

Explanation: The user was already connected to the database specified
in the Reorganize Table command. An error occurred while committing
the previous work in progress for the database. The utility stops
processing with no attempt to roll back the work or break the database
connection. User Response: Look at the SQLCODE (message number) in the
message for more information. Make changes and resubmit the command.

But where do I look up the SQLCODES? I got something like -1304 (but
do not believe this number: I am just guessing what it was). I thought
there was a place to look those up, but I can no longer find it.

See the SQL2215N in the message above? This message is reporting SQL
Code -2215.
You can get the explanation you are quoting simply by issuing this:

db2 ? sql2215n

I looked the 2215N in the SQL manual.
and to lookup "sqlcode" you are quoting

db2 ? sql1304

which tells us that this is probably not the number you think you had:
I know it is not, but I did not know how to look that one up and it is now
long gone. I just ran the REORGANIZE over and it worked the second time.

But I did not know you could look up the codes the same way you looked up
the SQL2215N (typical) messages. Surely there is overlap between them? How
does it know the difference?
SQL1304N The TCP/IP security type SOCKS is not valid.

Explanation:

The TCP/IP security type SOCKS in the TCP/IP protocol structure
of the Catalog Node command is invalid with authentication type
DCE.

User Response:

Make sure you do not use the combination of TCP/IP protocol with
security type SOCKS and authentication type DCE.

sqlcode : -1304

sqlstate : 08001

All SQLnnnnnn messages are doumented in Messages and Codes manual
available online or as PDF file.
There are two whole PDF files for Messages and Codes and I could not find
the -nnnn (SQL codes) in there.
Jan M. Nelken

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 07:50:00 up 11 days, 20:46, 3 users, load average: 0.07, 0.07, 0.01

[Jan M. Nelken]

Jean-David Beyer wrote:

But I did not know you could look up the codes the same way you looked
up the SQL2215N (typical) messages. Surely there is overlap between
them? How does it know the difference?
Don't understand. db2 ? XXXnnnnn command is described in the Message
Reference manual.

From the Message Reference:

Message Structure
Message identifiers consist of a three character message prefix,
followed by a four or five digit message number, followed by a single
letter suffix. For example, SQL1042C. For a list of message prefixes,
see “Information Available Online” and “Other DB2 Messages” on page 3.
The single letter suffix describes the severity of the error message.

In general, message identifiers ending with a C are severe messages.
Those ending with an E are urgent messages. Message identifiers ending
with an N are error messages, those ending with a W are warning
messages and an I indicates an informational message.

For ADM messages, message identifiers ending with a C are severe
messages. Those ending with an E are urgent messages. Message
identifiers ending with a W are important messages and an I are
indicates an informational messages.

For SQL messages, message identifiers ending with an N are error
messages. Those ending with a W indicate warning or informational
messages. Message identifiers ending with a C indicate critical system
errors.
....
....
The message number is also referred to as the SQLCODE. The SQLCODE is
passed to the application as a positive or negative number, depending
on its message type (N, W, or C). N and C yield negative values,
whereas W yields a positive value.
There are two whole PDF files for Messages and Codes and I could not
find the -nnnn (SQL codes) in there.

Really - did you use Acrobat Reader?

Here is Cut and Paste from the Table of Contents of the scond manual
(the one I was referring to) - which contains SQLCODE and SQLSTATE
messages:

Chapter 2. SQL Messages . . . . . . . 5
SQL0000 - SQL0099 . . . . . . . . . . 5
SQL0100 - SQL0199 . . . . . . . . . 14
SQL0200 - SQL0299 . . . . . . . . . 31
SQL0300 - SQL0399 . . . . . . . . . 55
SQL0400 - SQL0499 . . . . . . . . . 72
SQL0500 - SQL0599 . . . . . . . . . 98
SQL0600 - SQL0699 . . . . . . . . . 119
SQL0700 - SQL0799 . . . . . . . . . 132
SQL0800 - SQL0899 . . . . . . . . . 140
SQL0900 - SQL0999 . . . . . . . . . 149
SQL1000 - SQL1099 . . . . . . . . . 168
SQL1100 - SQL1199 . . . . . . . . . 189
SQL1200 - SQL1299 . . . . . . . . . 207
SQL1300 - SQL1399 . . . . . . . . . 227
SQL1400 - SQL1499 . . . . . . . . . 241
SQL1500 - SQL1599 . . . . . . . . . 254
SQL1600 - SQL1699 . . . . . . . . . 260
SQL1700 - SQL1799 . . . . . . . . . 267
SQL1800 - SQL1899 . . . . . . . . . 274
SQL1900 - SQL1999 . . . . . . . . . 279
SQL2000 - SQL2099 . . . . . . . . . 280
SQL2100 - SQL2199 . . . . . . . . . 291
SQL2200 - SQL2299 . . . . . . . . . 293
SQL2300 - SQL2399 . . . . . . . . . 297
SQL2400 - SQL2499 . . . . . . . . . 299
SQL2500 - SQL2599 . . . . . . . . . 304
SQL2600 - SQL2699 . . . . . . . . . 317
SQL2700 - SQL2799 . . . . . . . . . 319
SQL2800 - SQL2899 . . . . . . . . . 326
SQL3000 - SQL3099 . . . . . . . . . 330
SQL3100 - SQL3199 . . . . . . . . . 345
SQL3200 - SQL3299 . . . . . . . . . 361
SQL3300 - SQL3399 . . . . . . . . . 369

Look it online in:
http://publib.boulder.ibm.com/infoce...help/index.jsp

Visit occasionally here:
http://www-306.ibm.com/software/data/db2/library/

[Jean-David Beyer]

Jan M. Nelken wrote:

Jean-David Beyer wrote:

But I did not know you could look up the codes the same way you looked
up the SQL2215N (typical) messages. Surely there is overlap between
them? How does it know the difference?

Don't understand. db2 ? XXXnnnnn command is described in the Message
Reference manual.

I know it is. I am not having trouble decoding the XXXnnnnA messages, but
the sub-message of certain XXXnnnnA messages that are characterized by SQL
CODES.
From the Message Reference:

Message Structure
Message identifiers consist of a three character message prefix,
followed by a four or five digit message number, followed by a single
letter suffix. For example, SQL1042C. For a list of message prefixes,
see “Information Available Online” and “Other DB2 Messages” on page 3.
The single letter suffix describes the severity of the error message.

In general, message identifiers ending with a C are severe messages.
Those ending with an E are urgent messages. Message identifiers ending
with an N are error messages, those ending with a W are warning messages
and an I indicates an informational message.

For ADM messages, message identifiers ending with a C are severe
messages. Those ending with an E are urgent messages. Message
identifiers ending with a W are important messages and an I are
indicates an informational messages.

For SQL messages, message identifiers ending with an N are error
messages. Those ending with a W indicate warning or informational
messages. Message identifiers ending with a C indicate critical system
errors.
...
...
The message number is also referred to as the SQLCODE. The SQLCODE is
passed to the application as a positive or negative number, depending on
its message type (N, W, or C). N and C yield negative values, whereas W
yields a positive value.
I KNOW ALL THAT. Where do I look for SQL CODES (i.e., the "positive or
negative numbers"?

There are two whole PDF files for Messages and Codes and I could not
find the -nnnn (SQL codes) in there.

Really - did you use Acrobat Reader?

Yes.
Here is Cut and Paste from the Table of Contents of the scond manual
(the one I was referring to) - which contains SQLCODE and SQLSTATE
messages:

Chapter 2. SQL Messages . . . . . . . 5
SQL0000 - SQL0099 . . . . . . . . . . 5
SQL0100 - SQL0199 . . . . . . . . . 14
SQL0200 - SQL0299 . . . . . . . . . 31
SQL0300 - SQL0399 . . . . . . . . . 55
SQL0400 - SQL0499 . . . . . . . . . 72
SQL0500 - SQL0599 . . . . . . . . . 98
SQL0600 - SQL0699 . . . . . . . . . 119
SQL0700 - SQL0799 . . . . . . . . . 132
SQL0800 - SQL0899 . . . . . . . . . 140
SQL0900 - SQL0999 . . . . . . . . . 149
SQL1000 - SQL1099 . . . . . . . . . 168
SQL1100 - SQL1199 . . . . . . . . . 189
SQL1200 - SQL1299 . . . . . . . . . 207
SQL1300 - SQL1399 . . . . . . . . . 227
SQL1400 - SQL1499 . . . . . . . . . 241
SQL1500 - SQL1599 . . . . . . . . . 254
SQL1600 - SQL1699 . . . . . . . . . 260
SQL1700 - SQL1799 . . . . . . . . . 267
SQL1800 - SQL1899 . . . . . . . . . 274
SQL1900 - SQL1999 . . . . . . . . . 279
SQL2000 - SQL2099 . . . . . . . . . 280
SQL2100 - SQL2199 . . . . . . . . . 291
SQL2200 - SQL2299 . . . . . . . . . 293
SQL2300 - SQL2399 . . . . . . . . . 297
SQL2400 - SQL2499 . . . . . . . . . 299
SQL2500 - SQL2599 . . . . . . . . . 304
SQL2600 - SQL2699 . . . . . . . . . 317
SQL2700 - SQL2799 . . . . . . . . . 319
SQL2800 - SQL2899 . . . . . . . . . 326
SQL3000 - SQL3099 . . . . . . . . . 330
SQL3100 - SQL3199 . . . . . . . . . 345
SQL3200 - SQL3299 . . . . . . . . . 361
SQL3300 - SQL3399 . . . . . . . . . 369

I have seen that table, and looked up SQL2215N in there. It is there. But
the explanation says:

SQL2215N SQL error sqlcode occurred while committing previous work for the
database.

Explanation: The user was already connected to the database specified in the
Reorganize Table command. An error occurred while committing the
previous work
in progress for the database. The utility stops processing with no
attempt to
roll back the work or break the database connection.

User Response: Look at the SQLCODE (message number) in the message for more
information. Make changes and resubmit the command.

We both agree that the correct SQLCODE I got could not have been -1304,
but I need a way to look that up, and it is not SQL1304N or anything like
it. I need a separate table for the SQL CODES. And I do not think it is an
SQLSTATE message (though it might be). Too bad it scrolled off the screen.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 15:05:00 up 12 days, 4:01, 3 users, load average: 1.13, 1.07, 0.97

[Jan M. Nelken]

Jean-David Beyer wrote:

I know it is. I am not having trouble decoding the XXXnnnnA messages,
but the sub-message of certain XXXnnnnA messages that are characterized
by SQL CODES. .... I KNOW ALL THAT. Where do I look for SQL CODES (i.e., the "positive or
negative numbers"? .... User Response: Look at the SQLCODE (message number) in the message for
more information. Make changes and resubmit the command.

We both agree that the correct SQLCODE I got could not have been -1304,
but I need a way to look that up, and it is not SQL1304N or anything
like it. I need a separate table for the SQL CODES. And I do not think
it is an SQLSTATE message (though it might be). Too bad it scrolled off
the screen.

OK let me try one more time:

IF you have a SQL Code of -nnnnn or +nnnnn) where n represent numeric
digit between 0 and 9 inclusive,

THEN
there are two cases:

Case 1: it is a valid SQL code;
Look up message SQLnnnnn (without - or + in front of nnnnnn
part - either in the Messages manual or by issuing
db2 ? sqlnnnnn
Case 2: it is invalid or internal SQL code;
You would not find corresponding SQLnnnnn message in the
Messages manual and
db2 ? sqlnnnnn
would return SQL CODE -10007

Here is an exercise for you:
using above algorithm look up SQL Code -10007.

Jan M. Nelken

[Jean-David Beyer]

Jan M. Nelken wrote:

Jean-David Beyer wrote:

I know it is. I am not having trouble decoding the XXXnnnnA messages,
but the sub-message of certain XXXnnnnA messages that are
characterized by SQL CODES.
...

I KNOW ALL THAT. Where do I look for SQL CODES (i.e., the "positive
or negative numbers"?

...

User Response: Look at the SQLCODE (message number) in the message
for more information. Make changes and resubmit the command.

We both agree that the correct SQLCODE I got could not have been
-1304, but I need a way to look that up, and it is not SQL1304N or
anything like it. I need a separate table for the SQL CODES. And I do
not think it is an SQLSTATE message (though it might be). Too bad it
scrolled off the screen.

OK let me try one more time:

IF you have a SQL Code of -nnnnn or +nnnnn) where n represent numeric
digit between 0 and 9 inclusive,

THEN there are two cases:

Case 1: it is a valid SQL code; Look up message SQLnnnnn (without - or
+ in front of nnnnnn part - either in the Messages manual or by issuing
db2 ? sqlnnnnn

Case 2: it is invalid or internal SQL code; You would
not find corresponding SQLnnnnn message in the Messages manual and db2
? sqlnnnnn would return SQL CODE -10007
For the purpose of the example, let us say I got error SQL2215N or
whatever it was and it included an SQL CODE -10007. I do not need it to
tell me -10007 again: I need the explanation of what the -10007 means so I
can fix the problem and retry it as the explanation for SQL2215N requires.
Here is an exercise for you: using above algorithm look up SQL Code
-10007.

Example does not seem to work well.

trillian:jdbeyer[~]$ echo $DB2INSTANCE
db2inst1
(This is a valid instance.)
trillian:jdbeyer[~]$ echo $LANG
POSIX
trillian:jdbeyer[~]$ echo $DB2CODEPAGE

[i.e., blank]
db2 => ? sql10007;
SQL10007N Message "<msgno>" could not be retrieved. Reason
code: "<code>".

Explanation:

The requested message <msgno> could not be retrieved from the
message file. Reason code <code> is one of the following:

1. The environment variable "DB2INSTANCE" is either not set, or
is set to an invalid instance. Correct it and try again.

2. The message file was found, but the file could not be opened
because of permissions. Check the file permissions of the
files under the message directory.

3. The message file could not be found. Either the file does
not exist, or the directory the message file should be in
does not exist. Check that a either a 'prime' directory (the
default) or a directory with the same name as the 'LANG'
environment variable exists under the message directory.

If I understand this correctly, by "message directory" they mean
/opt/IBM/db2/V8.1/msg/en_US.iso88591. Is this correct?

Now it is really impractical to change $LANG for this since it would trash
other programs that rely on its being POSIX.

4. The requested message does not exist in the message file.
Either the message file is outdated, or it is the wrong
one.

5. Either DB2CODEPAGE is set to a code page which the database
does not support, or the client's locale is not supported by
the database.

6. An unexpected system error occurred. Try execution again.
If problem persists, contact your IBM representative.

7. Not enough memory. An attempt to get private memory failed.
Try again.

I have 4 Gigabytes RAM, mostly available.

User Response:

Reissue the command after verifying the following:

o ensure that the DB2INSTANCE environment variable is set to
the correct literal string for the username attempting this
command

o ensure that correct home directory is specified for the
username attempting this command (i.e. in the /etc/passwd
file)

o ensure that the LANG environment variable is set to the
correct value for the installed language, or is set to 'C'
(defaults to whatever is in the 'prime' directory), on the
username attempting this command

If all of the above are correct and the error still occurs,
reinstall DB2.
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 22:15:00 up 12 days, 11:11, 4 users, load average: 4.36, 4.31, 4.19

[Jan M. Nelken]

Jean-David Beyer wrote:

Example does not seem to work well.

trillian:jdbeyer[~]$ echo $DB2INSTANCE
db2inst1
(This is a valid instance.)
trillian:jdbeyer[~]$ echo $LANG
POSIX
trillian:jdbeyer[~]$ echo $DB2CODEPAGE

[i.e., blank]
db2 => ? sql10007;
SQL10007N Message "<msgno>" could not be retrieved. Reason
code: "<code>".

Actually example is somehow working ... here is another part of this
puzzle:

D:\SQLLIB\BIN>db2 ? sql1234
SQL10007N Message "1234" could not be retrieved. Reason code: "4".

Reason code 4 combined with -10007 SQL code is sufficient to figure out
that there is no such thing like -1234 SQL Code.

A Db2 component X calls DB2 component Y - which can and frequently is -
another program called internally; when component Y returns to caller
SQL code -yyyy then component X has to return it's SQL Code -xxxx and
also pass -yyyy as part of the tokenized error message.

Jan M. Nelken

[Jean-David Beyer]

Jan M. Nelken wrote:

Jean-David Beyer wrote:

Example does not seem to work well.

trillian:jdbeyer[~]$ echo $DB2INSTANCE
db2inst1
(This is a valid instance.)
trillian:jdbeyer[~]$ echo $LANG
POSIX
trillian:jdbeyer[~]$ echo $DB2CODEPAGE

[i.e., blank]
db2 => ? sql10007;
SQL10007N Message "<msgno>" could not be retrieved. Reason
code: "<code>".

Actually example is somehow working ... here is another part of this
puzzle:

D:\SQLLIB\BIN>db2 ? sql1234
SQL10007N Message "1234" could not be retrieved. Reason code: "4".

Reason code 4 combined with -10007 SQL code is sufficient to figure out
that there is no such thing like -1234 SQL Code.

A Db2 component X calls DB2 component Y - which can and frequently is -
another program called internally; when component Y returns to caller
SQL code -yyyy then component X has to return it's SQL Code -xxxx and
also pass -yyyy as part of the tokenized error message.

Jan M. Nelken

Well, I actually restored the (test) database from a backup tape and redid
the operation that got the original message. I wanted to get the offending
SQL CODE instead of just guessing the number. Unfortunately, it just
worked as it was supposed to, so the problem, whatever it was, did not
repeat. I hate when that happens (which is extremely rare).

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 05:20:03 up 13 days, 18:16, 3 users, load average: 4.14, 4.17, 4.14