 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / October 2005How to decipher a SQLCODE
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 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  Signature-------------------- Larry Menard "Defender of Geese and of All Things Natural" > Hi, > [quoted text clipped - 23 lines] > SKB Vragenlijst Services > Amsterdam, The Netherlands 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 > Just enter "db2 ? <sqlcode>", as in: > [quoted text clipped - 55 lines] > > SKB Vragenlijst Services > > Amsterdam, The Netherlands 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. Signature-------------------- Larry Menard "Defender of Geese and of All Things Natural" > Does that work on ISeries? Remember, he is using AS/400, not DB2 for > Linux/unix/windows. [quoted text clipped - 65 lines] >> > SKB Vragenlijst Services >> > Amsterdam, The Netherlands 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 > Hey, Rhino. > [quoted text clipped - 70 lines] > >> > SKB Vragenlijst Services > >> > Amsterdam, The Netherlands > Hi Larry, > [quoted text clipped - 20 lines] >>sqlcode being reported at the client app should be queried on the client >>platform. 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 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/infocenter/iseries/v5r3/topic/rzala/rzalaprintthis.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/infocenter/iseries/v5r3/index.jsp?topic=/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/techdocs/400_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 SignatureGroeten, Twan SKB Vragenlijst Services, automatisering > > Hi Larry, > > [quoted text clipped - 39 lines] > -- > Karl Hanson > Hi, > [quoted text clipped - 35 lines] > 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 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. Signature-------------------- Larry Menard "Defender of Geese and of All Things Natural" >> Hi, >> [quoted text clipped - 57 lines] > -- > Karl Hanson 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/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/c0 007609.htm Signature-------------------- Larry Menard "Defender of Geese and of All Things Natural" > I'm not familiar with VB, but I see examples of error message handling > in "sqllib\samples\vb\ado\cliExeSQL.bas" and [quoted text clipped - 63 lines] >> -- >> Karl Hanson 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 > 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/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/c0 007609.htm > > I'm not familiar with VB, but I see examples of error message handling > > in "sqllib\samples\vb\ado\cliExeSQL.bas" and [quoted text clipped - 15 lines] > >>> According to the IBM whitepaper "SQL messages and codes.pdf", (available > >>> at: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/rzala/rzalaprintthis.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/infocenter/iseries/v5r3/index.jsp?topic=/cl/dspmsgd.htm > >>> Searching around how to execute a CL-command, I found the following > >>> command: "QSYS.QCMDEXC" (information available at: [quoted text clipped - 39 lines] > >> -- > >> Karl Hanson 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. Signature-------------------- Larry Menard "Defender of Geese and of All Things Natural" > Larry, > [quoted text clipped - 113 lines] >> >> -- >> >> Karl Hanson 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. Signature-------------------- Larry Menard "Defender of Geese and of All Things Natural" > Hi Larry, > [quoted text clipped - 102 lines] >> >> > SKB Vragenlijst Services >> >> > Amsterdam, The Netherlands 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 Rhino, As a pity, there is no such "db2" commandlinetool available on my workstation. :-( Twan Kennis SignatureGroeten, Twan SKB Vragenlijst Services, automatisering > Hi Larry, > [quoted text clipped - 95 lines] > > >> > SKB Vragenlijst Services > > >> > Amsterdam, The Netherlands 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 > Rhino, > > As a pity, there is no such "db2" commandlinetool available on my > workstation. :-( > > Twan Kennis SignatureKent Milligan, DB2 & BI team PartnerWorld for Developers, iSeries kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!! >>> www.iseries.ibm.com/db2 (opinions stated are not necessarily those of my employer) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.