How to decipher a SQLCODE

[Larry Menard]

Re: How to decipher a SQLCODE - Finding the last piece

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" <computer@skb.n l> wrote in message
news:11m3p4lj39 efsbf@corp.supe rnews.com...[color=blue]
> 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" <root@GoSpamYou rself.com> wrote in message
> news:892dnd5g09 66av3eRVn-rw@rogers.com.. .[color=green]
>> But then again, if you don't have the "db2" executable (the full[/color]
> DB2[color=green]
>> client), then you probably don't have the Samples. Look here:
>>
>>[/color]
> http://publib.boulder.ibm.com/infoce...d/c0007609.htm[color=green]
>>
>> --
>> --------------------
>> Larry Menard
>> "Defender of Geese and of All Things Natural"
>>
>>
>> "Larry Menard" <root@GoSpamYou rself.com> wrote in message
>> news:IfudnYFHso n3fv3eRVn-rA@rogers.com.. .[color=darkred]
>> > I'm not familiar with VB, but I see examples of error message[/color][/color]
> handling[color=green][color=darkred]
>> > in "sqllib\samples \vb\ado\cliExeS QL.bas" and
>> > "sqllib\samples \.net\vb\tbuse. vb". The server platform from which[/color][/color]
> the[color=green][color=darkred]
>> > error comes should be irrelevant.
>> >
>> > --
>> > --------------------
>> > Larry Menard
>> > "Defender of Geese and of All Things Natural"
>> >
>> >
>> > "Karl Hanson" <kchanson@youes s.ibm.com> wrote in message
>> > news:4360d69b_2 @news1.prserv.n et...
>> >> 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[/color][/color]
> Windows[color=green][color=darkred]
>> >>> application (Visual Basic) is to diplay this description as part[/color][/color]
> of my[color=green][color=darkred]
>> >>> error-message when for example a Stored Procedure failed.
>> >>>
>> >>> Searching around on the public.boulder. ibm.com site I found a[/color][/color]
> trace to[color=green][color=darkred]
>> >>> my solution, but the puzzle is not finsihed yet. Maybe anyone can[/color][/color]
> help[color=green][color=darkred]
>> >>> me finding the last piece.
>> >>>
>> >>> According to the IBM whitepaper "SQL messages and codes.pdf",[/color][/color]
> (available[color=green][color=darkred]
>> >>> at:
>> >>>[/color][/color]
> http://publib.boulder.ibm.com/infoce...aprintthis.htm) a[color=green][color=darkred]
>> >>> SQLCODE can be translated with the CL-command "DSPMSGD" (Display[/color][/color]
> Message[color=green][color=darkred]
>> >>> Description). Specs of this command are available at
>> >>>[/color][/color]
> http://publib.boulder.ibm.com/infoce...cl/dspmsgd.htm[color=green][color=darkred]
>> >>>
>> >>> Searching around how to execute a CL-command, I found the[/color][/color]
> following[color=green][color=darkred]
>> >>> command: "QSYS.QCMDE XC" (information available at:
>> >>> http://www.starquest.com/Supportdocs...0_qcmdexc.html)
>> >>>
>> >>> Assembling all together, I *should* get the error description of[/color][/color]
> i.e.[color=green][color=darkred]
>> >>> SQLCODE -204 by executing the following command from my SQL ODBC
>> >>> interface:
>> >>>
>> >>> CALL QSYS.QCMDEXC('D SPMSGD RANGE(SQL0204)
>> >>> MSGF(QSQLMSG)', 0000000036.0000 0)
>> >>>
>> >>> As a pity, all I get is "The command completed succesfully", and[/color][/color]
> no[color=green][color=darkred]
>> >>> return paramater or SQL recordset containing the error description[/color][/color]
> is[color=green][color=darkred]
>> >>> 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[/color][/color]
> is[color=green][color=darkred]
>> >> running in what is considered an iSeries "batch" job (ie the[/color][/color]
> QZDASOINIT).[color=green][color=darkred]
>> >> This means there is no associated display session associated with[/color][/color]
> the[color=green][color=darkred]
>> >> job, so DSPMSGD produces a spooled file. If you try this from an[/color][/color]
> OS/400[color=green][color=darkred]
>> >> command line:
>> >> > SBMJOB CMD(DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG))
>> >> > WRKSBMJOB
>> >>
>> >> .. and select option 8 for the submitted job (Work with spooled[/color][/color]
> files),[color=green][color=darkred]
>> >> 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[/color][/color]
> (CL[color=green][color=darkred]
>> >> programs can be external stored procedures) that accepts the[/color][/color]
> SQLCODE[color=green][color=darkred]
>> >> (equivalent MSGID such as 'SQL0204') as an input parameter. The[/color][/color]
> program[color=green][color=darkred]
>> >> 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[/color][/color]
> accessed[color=green][color=darkred]
>> >> by SQL.
>> >>
>> >> --
>> >> Karl Hanson
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]