By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

How to decipher a SQLCODE

P: n/a
Hi,

I have a DB2 database on the IBM iSeries platform, on which I created
several Stored Procedures with the SQLCODE as a return-parameter.
These Stored Procedures are called from a Windows application (build in
Visual Basic) using an ADO/OLE DB Provider on a IBM Client Access ODBC
driver.

On the Internet I found a whitepaper "SQL messages and codes" provided
by IBM which describes a complete list of all SQLSTATEs, SQLCODEs and
their corresponding translation.

They say all SQL-codes and messages are stored in the QSQLMSG-file, and
to translate a SQLCODE (i.e. SQLCODE -204), I have to execute the
following command from the iSeries commandline:
DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG)


Question is, how can I decipher my SQLcode from my windows application?
i.e. with a SQL-command (????) like:
select * from qsys2.qsqlmsg where code = 'SQL0204'

Thanks for your help.

Twan Kennis
SKB Vragenlijst Services
Amsterdam, The Netherlands


Nov 12 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
Just enter "db2 ? <sqlcode>", as in:
C:\>db2 ? sql0104
SQL0104N An unexpected token "<token>" was found following
"<text>". Expected tokens may include:
"<token-list>".

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.

As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>". This list assumes
the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Twan Kennis" <co******@skb.nl> wrote in message
news:11*************@corp.supernews.com...
Hi,

I have a DB2 database on the IBM iSeries platform, on which I created
several Stored Procedures with the SQLCODE as a return-parameter.
These Stored Procedures are called from a Windows application (build in
Visual Basic) using an ADO/OLE DB Provider on a IBM Client Access ODBC
driver.

On the Internet I found a whitepaper "SQL messages and codes" provided
by IBM which describes a complete list of all SQLSTATEs, SQLCODEs and
their corresponding translation.

They say all SQL-codes and messages are stored in the QSQLMSG-file, and
to translate a SQLCODE (i.e. SQLCODE -204), I have to execute the
following command from the iSeries commandline:
DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG)


Question is, how can I decipher my SQLcode from my windows application?
i.e. with a SQL-command (????) like:
select * from qsys2.qsqlmsg where code = 'SQL0204'

Thanks for your help.

Twan Kennis
SKB Vragenlijst Services
Amsterdam, The Netherlands

Nov 12 '05 #2

P: n/a
Does that work on ISeries? Remember, he is using AS/400, not DB2 for
Linux/unix/windows.

Unfortunately, I don't know nearly enough to answer this question for DB2 on
AS/400 and there are not a lot of AS/400 users lurking on this newsgroup....

Rhino

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:Ib********************@rogers.com...
Just enter "db2 ? <sqlcode>", as in:
C:\>db2 ? sql0104
SQL0104N An unexpected token "<token>" was found following
"<text>". Expected tokens may include:
"<token-list>".

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.

As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>". This list assumes
the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Twan Kennis" <co******@skb.nl> wrote in message
news:11*************@corp.supernews.com...
Hi,

I have a DB2 database on the IBM iSeries platform, on which I created
several Stored Procedures with the SQLCODE as a return-parameter.
These Stored Procedures are called from a Windows application (build in
Visual Basic) using an ADO/OLE DB Provider on a IBM Client Access ODBC
driver.

On the Internet I found a whitepaper "SQL messages and codes" provided
by IBM which describes a complete list of all SQLSTATEs, SQLCODEs and
their corresponding translation.

They say all SQL-codes and messages are stored in the QSQLMSG-file, and
to translate a SQLCODE (i.e. SQLCODE -204), I have to execute the
following command from the iSeries commandline:
DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG)


Question is, how can I decipher my SQLcode from my windows application?
i.e. with a SQL-command (????) like:
select * from qsys2.qsqlmsg where code = 'SQL0204'

Thanks for your help.

Twan Kennis
SKB Vragenlijst Services
Amsterdam, The Netherlands


Nov 12 '05 #3

P: n/a
Hey, Rhino.

The server is iSeries, but the application is running on Windows. The
sqlcode being reported at the client app should be queried on the client
platform.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message
news:Pt*******************@news20.bellglobal.com.. .
Does that work on ISeries? Remember, he is using AS/400, not DB2 for
Linux/unix/windows.

Unfortunately, I don't know nearly enough to answer this question for DB2
on
AS/400 and there are not a lot of AS/400 users lurking on this
newsgroup....

Rhino

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:Ib********************@rogers.com...
Just enter "db2 ? <sqlcode>", as in:
C:\>db2 ? sql0104
SQL0104N An unexpected token "<token>" was found following
"<text>". Expected tokens may include:
"<token-list>".

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.

As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>". This list assumes
the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Twan Kennis" <co******@skb.nl> wrote in message
news:11*************@corp.supernews.com...
> Hi,
>
> I have a DB2 database on the IBM iSeries platform, on which I created
> several Stored Procedures with the SQLCODE as a return-parameter.
> These Stored Procedures are called from a Windows application (build in
> Visual Basic) using an ADO/OLE DB Provider on a IBM Client Access ODBC
> driver.
>
> On the Internet I found a whitepaper "SQL messages and codes" provided
> by IBM which describes a complete list of all SQLSTATEs, SQLCODEs and
> their corresponding translation.
>
> They say all SQL-codes and messages are stored in the QSQLMSG-file, and
> to translate a SQLCODE (i.e. SQLCODE -204), I have to execute the
> following command from the iSeries commandline:
>
>>> DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG)
>
> Question is, how can I decipher my SQLcode from my windows application?
> i.e. with a SQL-command (????) like:
> select * from qsys2.qsqlmsg where code = 'SQL0204'
>
> Thanks for your help.
>
> Twan Kennis
> SKB Vragenlijst Services
> Amsterdam, The Netherlands
>
>
>
>



Nov 12 '05 #4

P: n/a
Hi Larry,

I've never tried accessing an AS/400 from a Windows client. It occurred to
me that an SQLCode sent by an AS/400 might be one that is unique to AS/400
and I wasn't sure if a Windows client would know what to do with those.
Therefore, it seemed safer to query the AS/400 directly. But, now that I
reread the original post, I see that the code is SQL0204 which is present in
DB2 for Linux/Unix/Windows and OS/390. I *assume* it has the same meaning on
AS/400 as it does on those other platforms. So, it doesn't much matter which
platform's manuals (or command line tool) he uses since it should give
approximately the same answer.

I still wonder what would happen if he specified a 'db2 ? sqlnnnn' if 'nnnn'
was unique to the platform where the data originated and did *not* occur on
the client. Oh well, one of these days I suppose I'll bump into that for
myself :-)

Rhino

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:Q4********************@rogers.com...
Hey, Rhino.

The server is iSeries, but the application is running on Windows. The
sqlcode being reported at the client app should be queried on the client
platform.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message
news:Pt*******************@news20.bellglobal.com.. .
Does that work on ISeries? Remember, he is using AS/400, not DB2 for
Linux/unix/windows.

Unfortunately, I don't know nearly enough to answer this question for DB2 on
AS/400 and there are not a lot of AS/400 users lurking on this
newsgroup....

Rhino

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:Ib********************@rogers.com...
Just enter "db2 ? <sqlcode>", as in:
C:\>db2 ? sql0104
SQL0104N An unexpected token "<token>" was found following
"<text>". Expected tokens may include:
"<token-list>".

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.

As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>". This list assumes
the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Twan Kennis" <co******@skb.nl> wrote in message
news:11*************@corp.supernews.com...
> Hi,
>
> I have a DB2 database on the IBM iSeries platform, on which I created
> several Stored Procedures with the SQLCODE as a return-parameter.
> These Stored Procedures are called from a Windows application (build in > Visual Basic) using an ADO/OLE DB Provider on a IBM Client Access ODBC > driver.
>
> On the Internet I found a whitepaper "SQL messages and codes" provided > by IBM which describes a complete list of all SQLSTATEs, SQLCODEs and
> their corresponding translation.
>
> They say all SQL-codes and messages are stored in the QSQLMSG-file, and > to translate a SQLCODE (i.e. SQLCODE -204), I have to execute the
> following command from the iSeries commandline:
>
>>> DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG)
>
> Question is, how can I decipher my SQLcode from my windows application? > i.e. with a SQL-command (????) like:
> select * from qsys2.qsqlmsg where code = 'SQL0204'
>
> Thanks for your help.
>
> Twan Kennis
> SKB Vragenlijst Services
> Amsterdam, The Netherlands
>
>
>
>



Nov 12 '05 #5

P: n/a
Rhino wrote:
Hi Larry,

I've never tried accessing an AS/400 from a Windows client. It occurred to
me that an SQLCode sent by an AS/400 might be one that is unique to AS/400
and I wasn't sure if a Windows client would know what to do with those.
Therefore, it seemed safer to query the AS/400 directly. But, now that I
reread the original post, I see that the code is SQL0204 which is present in
DB2 for Linux/Unix/Windows and OS/390. I *assume* it has the same meaning on
AS/400 as it does on those other platforms. So, it doesn't much matter which
platform's manuals (or command line tool) he uses since it should give
approximately the same answer.

I still wonder what would happen if he specified a 'db2 ? sqlnnnn' if 'nnnn'
was unique to the platform where the data originated and did *not* occur on
the client. Oh well, one of these days I suppose I'll bump into that for
myself :-)

Rhino

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:Q4********************@rogers.com...
Hey, Rhino.

The server is iSeries, but the application is running on Windows. The
sqlcode being reported at the client app should be queried on the client
platform.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

One misconception might be the assumption that the container of iSeries
messages corresponding to SQLCODEs is an SQL table:
Question is, how can I decipher my SQLcode from my windows application?
i.e. with a SQL-command (????) like:
select * from qsys2.qsqlmsg where code = 'SQL0204'


This is not possible (directly), because messages are stored in an
OS/400 Message File (*MSGF) object, which is *not* an SQL table.

If connected via an iSeries Access (aka Client Access) ODBC driver,
there would normally be a QZDASOINIT job running on iSeries to process
SQL requests from the client. It should be possible to locate this job
< eg OS/400 command: wrkactjob job(qzda*) > and see the SQL0204 message
in the job log.

--
Karl Hanson
Nov 12 '05 #6

P: n/a
There is a table of SQLCODEs that don't map across platforms, so by the
time an SQLCODE is reported at the client, it should have already undergone
any translation necessary. For example if SQLCODE "1234" on the iSeries
corresponds to SQLCODE "5678" on LUW, the app will report it as "5678".

I suppose there might be cases of SQLCODEs that are truly
platform-specific (no equivalent on other platforms). Not sure what would
happen in that case, but I'm sure it's a rare case.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message
news:q0*******************@news20.bellglobal.com.. .
Hi Larry,

I've never tried accessing an AS/400 from a Windows client. It occurred to
me that an SQLCode sent by an AS/400 might be one that is unique to AS/400
and I wasn't sure if a Windows client would know what to do with those.
Therefore, it seemed safer to query the AS/400 directly. But, now that I
reread the original post, I see that the code is SQL0204 which is present
in
DB2 for Linux/Unix/Windows and OS/390. I *assume* it has the same meaning
on
AS/400 as it does on those other platforms. So, it doesn't much matter
which
platform's manuals (or command line tool) he uses since it should give
approximately the same answer.

I still wonder what would happen if he specified a 'db2 ? sqlnnnn' if
'nnnn'
was unique to the platform where the data originated and did *not* occur
on
the client. Oh well, one of these days I suppose I'll bump into that for
myself :-)

Rhino

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:Q4********************@rogers.com...
Hey, Rhino.

The server is iSeries, but the application is running on Windows. The
sqlcode being reported at the client app should be queried on the client
platform.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message
news:Pt*******************@news20.bellglobal.com.. .
> Does that work on ISeries? Remember, he is using AS/400, not DB2 for
> Linux/unix/windows.
>
> Unfortunately, I don't know nearly enough to answer this question for DB2 > on
> AS/400 and there are not a lot of AS/400 users lurking on this
> newsgroup....
>
> Rhino
>
> "Larry Menard" <ro**@GoSpamYourself.com> wrote in message
> news:Ib********************@rogers.com...
>> Just enter "db2 ? <sqlcode>", as in:
>>
>>
>> C:\>db2 ? sql0104
>>
>>
>> SQL0104N An unexpected token "<token>" was found following
>> "<text>". Expected tokens may include:
>> "<token-list>".
>>
>> Explanation:
>>
>> A syntax error in the SQL statement or the input command string
>> for the SYSPROC.ADMIN_CMD procedure was detected at the specified
>> token following the text "<text>". The "<text>" field indicates
>> the 20 characters of the SQL statement or the input command
>> string for the SYSPROC.ADMIN_CMD procedure that preceded the
>> token that is not valid.
>>
>> As an aid, a partial list of valid tokens is provided in the
>> SQLERRM field of the SQLCA as "<token-list>". This list assumes
>> the statement is correct to that point.
>>
>> The statement cannot be processed.
>>
>> User Response:
>>
>> Examine and correct the statement in the area of the specified
>> token.
>>
>> sqlcode : -104
>>
>> sqlstate : 42601
>>
>> --
>> --------------------
>> Larry Menard
>> "Defender of Geese and of All Things Natural"
>>
>>
>> "Twan Kennis" <co******@skb.nl> wrote in message
>> news:11*************@corp.supernews.com...
>> > Hi,
>> >
>> > I have a DB2 database on the IBM iSeries platform, on which I
>> > created
>> > several Stored Procedures with the SQLCODE as a return-parameter.
>> > These Stored Procedures are called from a Windows application (build in >> > Visual Basic) using an ADO/OLE DB Provider on a IBM Client Access ODBC >> > driver.
>> >
>> > On the Internet I found a whitepaper "SQL messages and codes" provided >> > by IBM which describes a complete list of all SQLSTATEs, SQLCODEs
>> > and
>> > their corresponding translation.
>> >
>> > They say all SQL-codes and messages are stored in the QSQLMSG-file, and >> > to translate a SQLCODE (i.e. SQLCODE -204), I have to execute the
>> > following command from the iSeries commandline:
>> >
>> >>> DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG)
>> >
>> > Question is, how can I decipher my SQLcode from my windows application? >> > i.e. with a SQL-command (????) like:
>> > select * from qsys2.qsqlmsg where code = 'SQL0204'
>> >
>> > Thanks for your help.
>> >
>> > Twan Kennis
>> > SKB Vragenlijst Services
>> > Amsterdam, The Netherlands
>> >
>> >
>> >
>> >
>>
>>
>
>



Nov 12 '05 #7

P: n/a
Surely this is a reason for using SQLSTATE in new developments,
SQLCODEs were never standardised, while even different vendors should
use the same SQLSTATEs with perhaps the exception of a few vendor
variations for rather esoteric circumstances.

Robert

Nov 12 '05 #8

P: n/a
Rhino,

As a pity, there is no such "db2" commandlinetool available on my
workstation. :-(

Twan Kennis
--
Groeten, Twan
SKB Vragenlijst Services, automatisering
"Rhino" <no***********************@nospam.com> wrote in message
news:q0*******************@news20.bellglobal.com.. .
Hi Larry,

I've never tried accessing an AS/400 from a Windows client. It occurred to me that an SQLCode sent by an AS/400 might be one that is unique to AS/400 and I wasn't sure if a Windows client would know what to do with those. Therefore, it seemed safer to query the AS/400 directly. But, now that I reread the original post, I see that the code is SQL0204 which is present in DB2 for Linux/Unix/Windows and OS/390. I *assume* it has the same meaning on AS/400 as it does on those other platforms. So, it doesn't much matter which platform's manuals (or command line tool) he uses since it should give
approximately the same answer.

I still wonder what would happen if he specified a 'db2 ? sqlnnnn' if 'nnnn' was unique to the platform where the data originated and did *not* occur on the client. Oh well, one of these days I suppose I'll bump into that for myself :-)

Rhino

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:Q4********************@rogers.com...
Hey, Rhino.

The server is iSeries, but the application is running on Windows. The
sqlcode being reported at the client app should be queried on the client platform.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Rhino" <no***********************@nospam.com> wrote in message
news:Pt*******************@news20.bellglobal.com.. .
Does that work on ISeries? Remember, he is using AS/400, not DB2 for Linux/unix/windows.

Unfortunately, I don't know nearly enough to answer this question for
DB2 on
AS/400 and there are not a lot of AS/400 users lurking on this
newsgroup....

Rhino

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:Ib********************@rogers.com...
> Just enter "db2 ? <sqlcode>", as in:
>
>
> C:\>db2 ? sql0104
>
>
> SQL0104N An unexpected token "<token>" was found following
> "<text>". Expected tokens may include:
> "<token-list>".
>
> Explanation:
>
> A syntax error in the SQL statement or the input command string
> for the SYSPROC.ADMIN_CMD procedure was detected at the specified
> token following the text "<text>". The "<text>" field indicates
> the 20 characters of the SQL statement or the input command
> string for the SYSPROC.ADMIN_CMD procedure that preceded the
> token that is not valid.
>
> As an aid, a partial list of valid tokens is provided in the
> SQLERRM field of the SQLCA as "<token-list>". This list assumes
> the statement is correct to that point.
>
> The statement cannot be processed.
>
> User Response:
>
> Examine and correct the statement in the area of the specified
> token.
>
> sqlcode : -104
>
> sqlstate : 42601
>
> --
> --------------------
> Larry Menard
> "Defender of Geese and of All Things Natural"
>
>
> "Twan Kennis" <co******@skb.nl> wrote in message
> news:11*************@corp.supernews.com...
> > Hi,
> >
> > I have a DB2 database on the IBM iSeries platform, on which I
created> > several Stored Procedures with the SQLCODE as a return-parameter.> > These Stored Procedures are called from a Windows application (build
in> > Visual Basic) using an ADO/OLE DB Provider on a IBM Client
Access
ODBC> > driver.
> >
> > On the Internet I found a whitepaper "SQL messages and codes" provided> > by IBM which describes a complete list of all SQLSTATEs,
SQLCODEs and> > their corresponding translation.
> >
> > They say all SQL-codes and messages are stored in the QSQLMSG-file,
and> > to translate a SQLCODE (i.e. SQLCODE -204), I have to execute
the> > following command from the iSeries commandline:
> >
> >>> DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG)
> >
> > Question is, how can I decipher my SQLcode from my windows

application?> > i.e. with a SQL-command (????) like:
> > select * from qsys2.qsqlmsg where code = 'SQL0204'
> >
> > Thanks for your help.
> >
> > Twan Kennis
> > SKB Vragenlijst Services
> > Amsterdam, The Netherlands
> >
> >
> >
> >
>
>



Nov 12 '05 #9

P: n/a
Hi,

Thank you all very much so far for your cooperation.

The reason I'd like to have the SQL-error description within my Windows
application (Visual Basic) is to diplay this description as part of my
error-message when for example a Stored Procedure failed.

Searching around on the public.boulder.ibm.com site I found a trace to
my solution, but the puzzle is not finsihed yet. Maybe anyone can help
me finding the last piece.

According to the IBM whitepaper "SQL messages and codes.pdf", (available
at:
http://publib.boulder.ibm.com/infoce...aprintthis.htm) a
SQLCODE can be translated with the CL-command "DSPMSGD" (Display Message
Description). Specs of this command are available at
http://publib.boulder.ibm.com/infoce...cl/dspmsgd.htm

Searching around how to execute a CL-command, I found the following
command: "QSYS.QCMDEXC" (information available at:
http://www.starquest.com/Supportdocs...0_qcmdexc.html)

Assembling all together, I *should* get the error description of i.e.
SQLCODE -204 by executing the following command from my SQL ODBC
interface:

CALL QSYS.QCMDEXC('DSPMSGD RANGE(SQL0204)
MSGF(QSQLMSG)',0000000036.00000)

As a pity, all I get is "The command completed succesfully", and no
return paramater or SQL recordset containing the error description is
returned to me.

Thanks for your help again.

Twan Kennis,
SKB Vragenlijst Services
Amsterdam, The Netherlands

--
Groeten, Twan
SKB Vragenlijst Services, automatisering
"Karl Hanson" <kc******@youess.ibm.com> wrote in message
news:43********@news1.prserv.net...
Rhino wrote:
Hi Larry,

I've never tried accessing an AS/400 from a Windows client. It occurred to me that an SQLCode sent by an AS/400 might be one that is unique to AS/400 and I wasn't sure if a Windows client would know what to do with those. Therefore, it seemed safer to query the AS/400 directly. But, now that I reread the original post, I see that the code is SQL0204 which is present in DB2 for Linux/Unix/Windows and OS/390. I *assume* it has the same meaning on AS/400 as it does on those other platforms. So, it doesn't much matter which platform's manuals (or command line tool) he uses since it should give approximately the same answer.

I still wonder what would happen if he specified a 'db2 ? sqlnnnn' if 'nnnn' was unique to the platform where the data originated and did *not* occur on the client. Oh well, one of these days I suppose I'll bump into that for myself :-)

Rhino

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:Q4********************@rogers.com...
Hey, Rhino.

The server is iSeries, but the application is running on Windows. Thesqlcode being reported at the client app should be queried on the clientplatform.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

One misconception might be the assumption that the container of iSeries messages corresponding to SQLCODEs is an SQL table:
> Question is, how can I decipher my SQLcode from my windows
application? > i.e. with a SQL-command (????) like:
> select * from qsys2.qsqlmsg where code = 'SQL0204'


This is not possible (directly), because messages are stored in an
OS/400 Message File (*MSGF) object, which is *not* an SQL table.

If connected via an iSeries Access (aka Client Access) ODBC driver,
there would normally be a QZDASOINIT job running on iSeries to process
SQL requests from the client. It should be possible to locate this

job < eg OS/400 command: wrkactjob job(qzda*) > and see the SQL0204 message in the job log.

--
Karl Hanson

Nov 12 '05 #10

P: n/a
Twan Kennis wrote:
Hi,

Thank you all very much so far for your cooperation.

The reason I'd like to have the SQL-error description within my Windows
application (Visual Basic) is to diplay this description as part of my
error-message when for example a Stored Procedure failed.

Searching around on the public.boulder.ibm.com site I found a trace to
my solution, but the puzzle is not finsihed yet. Maybe anyone can help
me finding the last piece.

According to the IBM whitepaper "SQL messages and codes.pdf", (available
at:
http://publib.boulder.ibm.com/infoce...aprintthis.htm) a
SQLCODE can be translated with the CL-command "DSPMSGD" (Display Message
Description). Specs of this command are available at
http://publib.boulder.ibm.com/infoce...cl/dspmsgd.htm

Searching around how to execute a CL-command, I found the following
command: "QSYS.QCMDEXC" (information available at:
http://www.starquest.com/Supportdocs...0_qcmdexc.html)

Assembling all together, I *should* get the error description of i.e.
SQLCODE -204 by executing the following command from my SQL ODBC
interface:

CALL QSYS.QCMDEXC('DSPMSGD RANGE(SQL0204)
MSGF(QSQLMSG)',0000000036.00000)

As a pity, all I get is "The command completed succesfully", and no
return paramater or SQL recordset containing the error description is
returned to me.

Thanks for your help again.

Twan Kennis,
SKB Vragenlijst Services
Amsterdam, The Netherlands

When you call the QCMDEXC program from within an ODBC session, it is
running in what is considered an iSeries "batch" job (ie the
QZDASOINIT). This means there is no associated display session
associated with the job, so DSPMSGD produces a spooled file. If you try
this from an OS/400 command line: SBMJOB CMD(DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG))
WRKSBMJOB


... and select option 8 for the submitted job (Work with spooled files),
you will see a file named QPMSGD. Using 5=Display you can see the
message text. One thought would be to create a stored procedure (CL
programs can be external stored procedures) that accepts the SQLCODE
(equivalent MSGID such as 'SQL0204') as an input parameter. The program
could run:
1) DSPMSGD using the input MSGID
2) CRTPF to create a physical file for CPYSPLF target
3) CPYSPLF to copy the QPMSGD spooled data to the physical file
After the message description is in the physical file it can be accessed
by SQL.

--
Karl Hanson
Nov 12 '05 #11

P: n/a
I'm not familiar with VB, but I see examples of error message handling in
"sqllib\samples\vb\ado\cliExeSQL.bas" and "sqllib\samples\.net\vb\tbuse.vb".
The server platform from which the error comes should be irrelevant.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Karl Hanson" <kc******@youess.ibm.com> wrote in message
news:43********@news1.prserv.net...
Twan Kennis wrote:
Hi,

Thank you all very much so far for your cooperation.

The reason I'd like to have the SQL-error description within my Windows
application (Visual Basic) is to diplay this description as part of my
error-message when for example a Stored Procedure failed.

Searching around on the public.boulder.ibm.com site I found a trace to
my solution, but the puzzle is not finsihed yet. Maybe anyone can help
me finding the last piece.

According to the IBM whitepaper "SQL messages and codes.pdf", (available
at:
http://publib.boulder.ibm.com/infoce...aprintthis.htm) a
SQLCODE can be translated with the CL-command "DSPMSGD" (Display Message
Description). Specs of this command are available at
http://publib.boulder.ibm.com/infoce...cl/dspmsgd.htm

Searching around how to execute a CL-command, I found the following
command: "QSYS.QCMDEXC" (information available at:
http://www.starquest.com/Supportdocs...0_qcmdexc.html)

Assembling all together, I *should* get the error description of i.e.
SQLCODE -204 by executing the following command from my SQL ODBC
interface:

CALL QSYS.QCMDEXC('DSPMSGD RANGE(SQL0204)
MSGF(QSQLMSG)',0000000036.00000)

As a pity, all I get is "The command completed succesfully", and no
return paramater or SQL recordset containing the error description is
returned to me.

Thanks for your help again.

Twan Kennis,
SKB Vragenlijst Services
Amsterdam, The Netherlands


When you call the QCMDEXC program from within an ODBC session, it is
running in what is considered an iSeries "batch" job (ie the QZDASOINIT).
This means there is no associated display session associated with the job,
so DSPMSGD produces a spooled file. If you try this from an OS/400
command line:
SBMJOB CMD(DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG))
WRKSBMJOB


.. and select option 8 for the submitted job (Work with spooled files),
you will see a file named QPMSGD. Using 5=Display you can see the message
text. One thought would be to create a stored procedure (CL programs can
be external stored procedures) that accepts the SQLCODE (equivalent MSGID
such as 'SQL0204') as an input parameter. The program could run:
1) DSPMSGD using the input MSGID
2) CRTPF to create a physical file for CPYSPLF target
3) CPYSPLF to copy the QPMSGD spooled data to the physical file
After the message description is in the physical file it can be accessed
by SQL.

--
Karl Hanson

Nov 12 '05 #12

P: n/a
The Description method on the Connection object below should return the
message that you're looking for. Although you may need to have a handler
in the stored procedure.
Conn.Error(i).SQLSTATE & Conn.Error(i).Description

Twan Kennis wrote:
Rhino,

As a pity, there is no such "db2" commandlinetool available on my
workstation. :-(

Twan Kennis


--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries
km***@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
www.iseries.ibm.com/db2

(opinions stated are not necessarily those of my employer)
Nov 12 '05 #13

P: n/a
But then again, if you don't have the "db2" executable (the full DB2
client), then you probably don't have the Samples. Look here:

http://publib.boulder.ibm.com/infoce...d/c0007609.htm

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:If********************@rogers.com...
I'm not familiar with VB, but I see examples of error message handling
in "sqllib\samples\vb\ado\cliExeSQL.bas" and
"sqllib\samples\.net\vb\tbuse.vb". The server platform from which the
error comes should be irrelevant.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Karl Hanson" <kc******@youess.ibm.com> wrote in message
news:43********@news1.prserv.net...
Twan Kennis wrote:
Hi,

Thank you all very much so far for your cooperation.

The reason I'd like to have the SQL-error description within my Windows
application (Visual Basic) is to diplay this description as part of my
error-message when for example a Stored Procedure failed.

Searching around on the public.boulder.ibm.com site I found a trace to
my solution, but the puzzle is not finsihed yet. Maybe anyone can help
me finding the last piece.

According to the IBM whitepaper "SQL messages and codes.pdf", (available
at:
http://publib.boulder.ibm.com/infoce...aprintthis.htm) a
SQLCODE can be translated with the CL-command "DSPMSGD" (Display Message
Description). Specs of this command are available at
http://publib.boulder.ibm.com/infoce...cl/dspmsgd.htm

Searching around how to execute a CL-command, I found the following
command: "QSYS.QCMDEXC" (information available at:
http://www.starquest.com/Supportdocs...0_qcmdexc.html)

Assembling all together, I *should* get the error description of i.e.
SQLCODE -204 by executing the following command from my SQL ODBC
interface:

CALL QSYS.QCMDEXC('DSPMSGD RANGE(SQL0204)
MSGF(QSQLMSG)',0000000036.00000)

As a pity, all I get is "The command completed succesfully", and no
return paramater or SQL recordset containing the error description is
returned to me.

Thanks for your help again.

Twan Kennis,
SKB Vragenlijst Services
Amsterdam, The Netherlands


When you call the QCMDEXC program from within an ODBC session, it is
running in what is considered an iSeries "batch" job (ie the QZDASOINIT).
This means there is no associated display session associated with the
job, so DSPMSGD produces a spooled file. If you try this from an OS/400
command line:
> SBMJOB CMD(DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG))
> WRKSBMJOB


.. and select option 8 for the submitted job (Work with spooled files),
you will see a file named QPMSGD. Using 5=Display you can see the
message text. One thought would be to create a stored procedure (CL
programs can be external stored procedures) that accepts the SQLCODE
(equivalent MSGID such as 'SQL0204') as an input parameter. The program
could run:
1) DSPMSGD using the input MSGID
2) CRTPF to create a physical file for CPYSPLF target
3) CPYSPLF to copy the QPMSGD spooled data to the physical file
After the message description is in the physical file it can be accessed
by SQL.

--
Karl Hanson


Nov 12 '05 #14

P: n/a
Larry,

Thanx for for your help
I have the complete IBM iSeries Access for Windows Client installed, but
there is no "db2.exe" on my disc.

Main difference between the VB-examples you mentioned and my
application, is that I implemented (transactional) exception handling in
my Stored Procedures, which handles the error "within" my Stored
Procedure and does apply a rollback (or a commit when no error
occurred). Outside my Stored Procedure, there is nothing to remind me of
an error, beside my own result-parameter, which contains the SQLCODE if
an error occurred.

In the VB-examples you mentioned, there is no exception handling on DB2
applied. So when an error occurred by executing plain SQL or calling a
Stored Procedure, the error code and description come back with the
connection-object and can be read within the VB application.

Twan Kennis


"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:89********************@rogers.com...
But then again, if you don't have the "db2" executable (the full DB2 client), then you probably don't have the Samples. Look here:

http://publib.boulder.ibm.com/infoce...d/c0007609.htm
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:If********************@rogers.com...
I'm not familiar with VB, but I see examples of error message handling in "sqllib\samples\vb\ado\cliExeSQL.bas" and
"sqllib\samples\.net\vb\tbuse.vb". The server platform from which the error comes should be irrelevant.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Karl Hanson" <kc******@youess.ibm.com> wrote in message
news:43********@news1.prserv.net...
Twan Kennis wrote:
Hi,

Thank you all very much so far for your cooperation.

The reason I'd like to have the SQL-error description within my Windows application (Visual Basic) is to diplay this description as part of my error-message when for example a Stored Procedure failed.

Searching around on the public.boulder.ibm.com site I found a trace to my solution, but the puzzle is not finsihed yet. Maybe anyone can help me finding the last piece.

According to the IBM whitepaper "SQL messages and codes.pdf", (available at:
http://publib.boulder.ibm.com/infoce...aprintthis.htm) a SQLCODE can be translated with the CL-command "DSPMSGD" (Display Message Description). Specs of this command are available at
http://publib.boulder.ibm.com/infoce...cl/dspmsgd.htm
Searching around how to execute a CL-command, I found the following command: "QSYS.QCMDEXC" (information available at:
http://www.starquest.com/Supportdocs...0_qcmdexc.html)

Assembling all together, I *should* get the error description of i.e. SQLCODE -204 by executing the following command from my SQL ODBC
interface:

CALL QSYS.QCMDEXC('DSPMSGD RANGE(SQL0204)
MSGF(QSQLMSG)',0000000036.00000)

As a pity, all I get is "The command completed succesfully", and no return paramater or SQL recordset containing the error description is returned to me.

Thanks for your help again.

Twan Kennis,
SKB Vragenlijst Services
Amsterdam, The Netherlands
When you call the QCMDEXC program from within an ODBC session, it is running in what is considered an iSeries "batch" job (ie the QZDASOINIT). This means there is no associated display session associated with the job, so DSPMSGD produces a spooled file. If you try this from an OS/400 command line:
> SBMJOB CMD(DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG))
> WRKSBMJOB

.. and select option 8 for the submitted job (Work with spooled files), you will see a file named QPMSGD. Using 5=Display you can see the
message text. One thought would be to create a stored procedure (CL programs can be external stored procedures) that accepts the SQLCODE (equivalent MSGID such as 'SQL0204') as an input parameter. The program could run:
1) DSPMSGD using the input MSGID
2) CRTPF to create a physical file for CPYSPLF target
3) CPYSPLF to copy the QPMSGD spooled data to the physical file
After the message description is in the physical file it can be accessed by SQL.

--
Karl Hanson



Nov 12 '05 #15

P: n/a
Ah, I see now. You want to find the explanation of an *iSeries* SQLCODE
from your Windows system.

Sorry, I don't know of any way you can do it, other than what Karl
described.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Twan Kennis" <co******@skb.nl> wrote in message
news:11*************@corp.supernews.com...
Larry,

Thanx for for your help
I have the complete IBM iSeries Access for Windows Client installed, but
there is no "db2.exe" on my disc.

Main difference between the VB-examples you mentioned and my
application, is that I implemented (transactional) exception handling in
my Stored Procedures, which handles the error "within" my Stored
Procedure and does apply a rollback (or a commit when no error
occurred). Outside my Stored Procedure, there is nothing to remind me of
an error, beside my own result-parameter, which contains the SQLCODE if
an error occurred.

In the VB-examples you mentioned, there is no exception handling on DB2
applied. So when an error occurred by executing plain SQL or calling a
Stored Procedure, the error code and description come back with the
connection-object and can be read within the VB application.

Twan Kennis


"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:89********************@rogers.com...
But then again, if you don't have the "db2" executable (the full

DB2
client), then you probably don't have the Samples. Look here:

http://publib.boulder.ibm.com/infoce...d/c0007609.htm

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:If********************@rogers.com...
> I'm not familiar with VB, but I see examples of error message handling > in "sqllib\samples\vb\ado\cliExeSQL.bas" and
> "sqllib\samples\.net\vb\tbuse.vb". The server platform from which the > error comes should be irrelevant.
>
> --
> --------------------
> Larry Menard
> "Defender of Geese and of All Things Natural"
>
>
> "Karl Hanson" <kc******@youess.ibm.com> wrote in message
> news:43********@news1.prserv.net...
>> Twan Kennis wrote:
>>> Hi,
>>>
>>> Thank you all very much so far for your cooperation.
>>>
>>> The reason I'd like to have the SQL-error description within my Windows >>> application (Visual Basic) is to diplay this description as part of my >>> error-message when for example a Stored Procedure failed.
>>>
>>> Searching around on the public.boulder.ibm.com site I found a trace to >>> my solution, but the puzzle is not finsihed yet. Maybe anyone can help >>> me finding the last piece.
>>>
>>> According to the IBM whitepaper "SQL messages and codes.pdf", (available >>> at:
>>> http://publib.boulder.ibm.com/infoce...aprintthis.htm) a >>> SQLCODE can be translated with the CL-command "DSPMSGD" (Display Message >>> Description). Specs of this command are available at
>>> http://publib.boulder.ibm.com/infoce...cl/dspmsgd.htm >>>
>>> Searching around how to execute a CL-command, I found the following >>> command: "QSYS.QCMDEXC" (information available at:
>>> http://www.starquest.com/Supportdocs...0_qcmdexc.html)
>>>
>>> Assembling all together, I *should* get the error description of i.e. >>> SQLCODE -204 by executing the following command from my SQL ODBC
>>> interface:
>>>
>>> CALL QSYS.QCMDEXC('DSPMSGD RANGE(SQL0204)
>>> MSGF(QSQLMSG)',0000000036.00000)
>>>
>>> As a pity, all I get is "The command completed succesfully", and no >>> return paramater or SQL recordset containing the error description is >>> returned to me.
>>>
>>> Thanks for your help again.
>>>
>>> Twan Kennis,
>>> SKB Vragenlijst Services
>>> Amsterdam, The Netherlands
>>>
>>
>> When you call the QCMDEXC program from within an ODBC session, it is >> running in what is considered an iSeries "batch" job (ie the QZDASOINIT). >> This means there is no associated display session associated with the >> job, so DSPMSGD produces a spooled file. If you try this from an OS/400 >> command line:
>> > SBMJOB CMD(DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG))
>> > WRKSBMJOB
>>
>> .. and select option 8 for the submitted job (Work with spooled files), >> you will see a file named QPMSGD. Using 5=Display you can see the
>> message text. One thought would be to create a stored procedure (CL >> programs can be external stored procedures) that accepts the SQLCODE >> (equivalent MSGID such as 'SQL0204') as an input parameter. The program >> could run:
>> 1) DSPMSGD using the input MSGID
>> 2) CRTPF to create a physical file for CPYSPLF target
>> 3) CPYSPLF to copy the QPMSGD spooled data to the physical file
>> After the message description is in the physical file it can be accessed >> by SQL.
>>
>> --
>> Karl Hanson
>
>



Nov 12 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.