Db2 Sql Error 311
Contents |
Tag: Error: DB2 SQL Error: SQLCODE=-311 SQLSTATE=22501 DB2 SQL Errors Codes and Error Messages and Warnings from Error -309 to -311 February 6, 2015 SQLServerF1 Leave a comment Error: DB2 SQL Error: SQLCODE=-309, SQLSTATE=22512, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER= A PREDICATE IS INVALID BECAUSE A REFERENCED HOST VARIABLE HAS THE NULL VALUE Explanation: The statement could not be processed because a host variable appearing in a predicate such as column-name = host-variable had the NULL value. Such http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/n311.html a predicate is not permitted when the host variable contains the NULL value even though the object column might contain nulls. System action: The statement cannot be executed. Programmer response: Rebind the plan or package containing the statement. The condition described is not an error in DB2 Version 2 Release 3 and later releases. SQLSTATE: 22512 http://www.sqlserverf1.com/tag/error-db2-sql-error-sqlcode-311-sqlstate22501/ Error: DB2 SQL Error: SQLCODE=-310, SQLSTATE=22023, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER= DECIMAL HOST VARIABLE OR PARAMETER number CONTAINS NON-DECIMAL DATA Explanation: DB2 received nondecimal data from either an application (in the form of a host variable), function or a stored procedure (in the form of a parameter that was passed to or from function or a stored procedure). number Identifies either the host variable number (if the message is issued as a result of a FETCH, INSERT, UPDATE, MERGE, DELETE, SELECT, VALUES INTO, or SET assignment statement), or the parameter number (if the message is issued as the result of the invocation of a function, or a CALL statement). System action: The statement cannot be processed. Programmer response: Correct the application program or stored procedure. Ensure that all decimal variables or parameters contain valid System/370 packed decimal numbers. SQLSTATE: 22023 Error: DB2 SQL Error: SQLCODE=-311, SQLSTATE=22501, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER= THE LENGTH OF INPUT HOST VARIABLE NUMBER position-number IS NEGATIVE OR GREATER THAN THE MA
topic Author Message MainframeGalBeginnerJoined: 11 Oct 2007Posts: 3Topics: 1Location: Columbus, OH Posted: Fri Dec 07, 2007 7:48 am Post subject: -311 on a BATCH SPUFI I'm running a BATCH SQL query using http://www.mvsforums.com/helpboards/viewtopic.php?t=9283&sid=cb05b89864c43485bb71a4ea1cb33e1a DBG2TEP2, and am getting the following error (NOTE: there are over 5M rows on http://www.allinterview.com/showanswers/152380/how-2-resolve-the-311-sqlcode.html the DB): SQLCODE = -311, ERROR: THE LENGTH OF INPUT HOST VARIABLE NUMBER 001 IS NEGATIVE OR GREATER THAN THE MAXIMUM Here's the SQL: //STEP1 EXEC DBG2TEP2 //DBG2TEP2.SYSIN DD * SELECT A.REPORT_ID, A.REQ_DATE, A.POLICY_ID, A.POLICY_ID_PFX_B, A.POLICY_ID_NUM FROM DB1.TCOR_RPT_REQUEST A, DB1.TCOR_REPORT_REC B WHERE A.REQ_DATE > '2007-11-10-00.01.00.000000' AND A.STATE_CODE = 'IN' AND A.REPORT_ID = B.REPORT_ID AND B.RECORD_TYPE = 'MVR' AND db2 sql LENGTH(B.RECORD_INFO) > 22 AND SUBSTR(B.RECORD_INFO,18,5) IN ( '11111', '22222', '24242', '32351' ) WITH UR FOR FETCH ONLY; ; /* It was suggested that the "SUBSTR function is the culprit. It is possible that a record that you are looking at does not have 23 bytes in the record; therefore it is exceeding the boundary." Thus, I added the LENGTH statement to the query. Doing this, allowed retrieval of 5 more rows, but db2 sql error only a total of 175 were retrieved, and it's possible that many more exist. Any help on this would be appreciated! Thanks! Back to top vivek1983IntermediateJoined: 20 Apr 2006Posts: 222Topics: 24 Posted: Fri Dec 07, 2007 8:21 am Post subject: MainframeGal, What is the allowable length of field B.RECORD_INFO? I am assuming that the defn of RECORD_INFO is VARCHAR(100) Use this untested SQL Code: SELECT A.REPORT_ID, A.REQ_DATE, A.POLICY_ID, A.POLICY_ID_PFX_B, A.POLICY_ID_NUM FROM DB1.TCOR_RPT_REQUEST A, DB1.TCOR_REPORT_REC B WHERE A.REQ_DATE > '2007-11-10-00.01.00.000000' AND A.STATE_CODE = 'IN' AND A.REPORT_ID = B.REPORT_ID AND B.RECORD_TYPE = 'MVR' AND SUBSTR(CHAR(B.RECORD_INFO,100),18,5) IN ( '11111', '22222', '24242', '32351' ) WITH UR FOR FETCH ONLY; ; _________________Vivek G -------------------------------------- A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay) Back to top jsharon1248IntermediateJoined: 08 Aug 2007Posts: 291Topics: 2Location: Chicago Posted: Fri Dec 07, 2007 8:55 am Post subject: You have 2 end of statement delimiters ';'. My bet is the second delimiter is confusing the DBG2TEP2 program. It's trying to prepare a second statement with 0 length and the -311 is for the empty string, not the SELECT you're looking at. Back to top MainframeGalBeginnerJoined: 11 Oct 2007Posts: 3Topics: 1Location: Columbus, OH Posted: Fri Dec 07, 2007 8:58 am Post subject: vivek1983 wrote:
MainFrame >> DB2 Suggest New Category how 2 resolve the -311 sqlcode Question Posted / guest 1 Answers 10769 Views I also Faced E-Mail Answers Answer / shravan kumar.p If Length is not declared for varchar, sql code -311 will appears... by giving the varchar lenth in cobol+db2 pgm we can resolve this -311 sql code.. if wrong correct me.. thakyou... Is This Answer Correct ? 15 Yes 3 No Post New Answer Categories COBOL (884)JCL (728)CICS (459)DB2 (684)IMS (80)IDMS (42)Natural (43)ADABAS (9)REXX (23)Assembler (31)CLIST (6)QMF (30)MVS (12)OS390 (8)OS 2 (6)VSAM (177)QSAM (4)Sysplex (5)IBM MainFrame AllOther (160) More DB2 Interview Questions Compare a subselect to a join? 1 Answers What are the three lock types? 2 Answers Name some fields from SQLCA. 2 Answers What are delete-connected tables? 1 Answers What are the three types of page locks that can be held? 1 Answers what is the use of commit ? and what is the syntax? 2 Answers Deloitte, Why use RUNSTAT Utility? 3 Answers Merrill Lynch, I have a table which has thousand of records i want to fetch only record num 100 to record num 200. Write a query that satisfies this criteria.(Cant use any keys) Anyone please reply ASAP! 17 Answers Tech Mahindra, KNIT, what is Static and dynamic linking 1 Answers Virtusa, what is main use of table space and index object? please its urgent 5 Answers TCS, IBM, Cap Gemini, Syntel, EDS, CA, Satyam, CGI, HTC, CTS, Wipro, Can a array declared with an index be displayed(readable format) in spool? 4 Answers Syntel, UST, when we are trying to update db2 rows. if the program abends . how we will know that the last successful update row was 5 Answers IBM, For more DB2 Interview Questions Click Here Copyright Policy | Terms of Service | Site Map | Contact Us Copyright © 2016 ALLInterview.com. All Rights Reserved.