Posted: Fri Dec 07, 2007 7:48 am Post subject: -311 on a BATCH SPUFI
I'm running a BATCH SQL query using DBG2TEP2, and am getting the following error (NOTE: there are over 5M rows on 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 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 only a total of 175 were retrieved, and it's possible that many more exist.
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)
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: 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.
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;
;
The lrecl of this col is VARCHAR(650) NOT NULL
I'll try your suggestion replacing the 100 with 650.
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
Posted: Fri Dec 07, 2007 9:22 am Post subject:
I reproduced the -311 error using DSNTEP2 by coding 2 end of statement delimiters. I suspect your DBG2TEP2 is a copy of, or an alias of DSNTEP2. Get rid of the second delimiter and your -311 will go away.
jsharon1248 is right. Removing the extra delimiter would solve the problem. Try that out. _________________ Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay)
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
