Db2 Sql Error 802
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn db2 sqlerror more about Stack Overflow the company Business Learn more about hiring developers or sqlstate 22023 postgresql posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow
Sql State=22023 Db Error Code=-802
Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Why am I getting “Data conversion or data mapping http://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/codes/src/tpc/p802.html error. SQLCODE=-802” on a simple DB2 select statement? up vote 3 down vote favorite I am accessing DB2 information on an IBM i (AS400) with PHP. With this code: $query = "SELECT * FROM QS36F.MYTABLE WHERE MYFIELD=120006"; $result = db2_prepare($conn, $query); db2_execute($result); $i=0; while($row = db2_fetch_assoc($result) or die(db2_stmt_errormsg())){ $i++; print "Row " . $i . " successful
"; } I get: SELECT * FROM QS36F.MYTABLE WHERE MYFIELD=120006 Row http://stackoverflow.com/questions/5009180/why-am-i-getting-data-conversion-or-data-mapping-error-sqlcode-802-on-a-simp 1 successful Row 2 successful Row 3 successful Row 4 successful Data conversion or data mapping error. SQLCODE=-802 There should be more than 4 results. Why might this error be occurring? More details: This same error seems to happen on any value I search for in MYFIELD although it may be after a different number of successful results MYFIELD is NUMERIC(7,0) I can search other fields in the table(including numeric ones) and it works fine. php db2 ibm-midrange share|improve this question edited Feb 16 '12 at 14:22 bluish 9,3591269126 asked Feb 15 '11 at 20:33 Mike 1,14332245 add a comment| 3 Answers 3 active oldest votes up vote 3 down vote accepted There was invalid decimal data in the table. There were blanks instead of zeros. I moved zeros into those blanks and that fixed the problem share|improve this answer answered Feb 16 '11 at 13:35 Mike 1,14332245 How did you find this? I have the same issue, but I can't find the bad data. –Eric Belair Oct 25 '12 at 19:08 add a comment| up vote 1 down vote If it can help someone else, I had the same error and found out I was making a join
the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that http://www.dbforums.com/showthread.php?789909-Help!-SQLCODE-802 you want to visit from the selection below. Results 1 to 2 of https://github.com/ibmdb/python-ibmdb/issues/95 2 Thread: Help! SQLCODE=-802 Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 05-20-03,02:47 #1 zhouhaiming View Profile View Forum Posts Registered User Join Date Jan 2003 Posts 74 Unanswered: Help! SQLCODE=-802 I invoke the SQL statement of sql error "insert into session.temp_tab select sdate,CDWBH,IHYLB1,sum(IFDL),sum(IGDL),sum(IPDL),s um(IDLHJ) from im.dl_origin_a group by cube(sdate,CDWBH,IHYLB1)" in my SQL procedure. DB2 returned the -802(SQLCODE) code. The structure of im.dl_origin_a is $ db2 describe table im.dl_origin_a Column Type Type name schema name Length Scale Nulls ------------------------------ --------- ------------------ -------- ----- ----- SDATE SYSIBM CHARACTER 7 0 No CDWBH SYSIBM CHARACTER 6 0 No IHYLB1 SYSIBM CHARACTER 3 0 No IFDL SYSIBM INTEGER 4 0 Yes db2 sql error IGDL SYSIBM INTEGER 4 0 Yes IPDL SYSIBM INTEGER 4 0 Yes IDLHJ SYSIBM INTEGER 4 0 Yes The table session.temp_tab is the same as im.dl_origin_a. After I change the type of IFDL...IDLHJ to bigint I invoke the SQL statement. DB2 return the -802 code again. Please tell me how to resolve this problem. Reply With Quote 05-20-03,10:31 #2 cchattoraj View Profile View Forum Posts Registered User Join Date Mar 2003 Posts 343 It looks like there's an arithmetic overflow on one of the functions used. Make sure the target columns can support the values resulting from the functions which are being used in the select statement. Here's how to check out these returns. db2 "? sql0802n" SQL0802N Arithmetic overflow or other arithmetic exception occurred. Explanation: The processing of a column function or arithmetic expression has resulted in an arithmetic overflow. The statement cannot be processed. For the INSERT, UPDATE, or DELETE statements, no inserts or updates are performed. User Response: Examine the SQL statement to determine the cause of the problem. If the problem is data dependent, it is necessary to examine the data processed when the error occurred. Refer to the SQL Reference to see the valid ranges for the data types. Th
Sign in Pricing Blog Support Search GitHub This repository Watch 26 Star 36 Fork 27 ibmdb/python-ibmdb Code Issues 22 Pull requests 2 Projects 0 Wiki Pulse Graphs New issue Errors -413 or -802 ignored by ibm_db_dbi #95 Closed GoogleCodeExporter opened this Issue Mar 16, 2015 · 10 comments Projects None yet Labels auto-migrated Milestone No milestone Assignees No one assigned 2 participants GoogleCodeExporter commented Mar 16, 2015 What steps will reproduce the problem? for error -802 execute the following from the command line db2-dev:~# python Python 2.7 (r27:82500, Aug 07 2010, 16:54:59) [GCC] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> import ibm_db_dbi; conn =ibm_db_dbi.connect("DB2"); cursor = conn.cursor() >>> res=cursor.execute("SELECT CAST (DAYS(CAST('0001-01-01 00:00:00' AS TIMESTAMP)) - DAYS('1970-01-01') AS INTEGER) * 86400 FROM SYSIBM.SYSDUMMY1") >>> rs=cursor.fetchone() >>> print res,rs True None >>> What is the expected output? Exception thrown and a message similar to or containing the following: SQLCODE=-802, SQLSTATE=22003 for error -413 execute the following from the command line >>> res=cursor.execute("SELECT CAST('2147483649' AS INTEGER) FROM SYSIBM.SYSDUMMY1") >>> rs=cursor.fetchone() >>> print res,rs True None Expected output: Exception and traceback. odbc trace ----------- [ Process: 20150, Thread: 140737353971456 ] [ Date & Time: 01/30/2012 09:03:10.594982 ] [ Product: QDB2/LINUXX8664 DB2 v9.7.0.4 ] [ Level Identifier: 08050107 ] [ CLI Driver Version: 09.02.0000 ] [ Informational Tokens: "DB2 v9.7.0.4","s110330","IP23243","Fixpack 4" ] [ Install Path: /opt/ibm/db2/V9.7 ] [ db2cli.ini Location: /home/db2inst1/sqllib/cfg/db2cli.ini ] [ CLI Driver Type: IBM Data Server Runtime Client ] [01/30/2012 09:03:10.710389] ( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue="TEST" - x'4353544154494F4E', pcbValue=8, piIndicatorPtr=8 ) [01/30/2012 09:03:10.710672] [01/30/2012 09:03:15.817640] SQLExecute( pszSqlStr="SELECT CAST('2147483649' AS INTEGER) FROM SYSIBM.SYSDUMMY1" ) [01/30/2012 09:03:15.818231] ---> Time elapsed - 0 seconds [01/30/2012 09:03:15.819779] SQLExecute( ) [01/30/2012 09:03:15.820019] <--- SQL_SUCCES