The problem
I've got a SQLRPGLE program that executes queries that look like this:
SELECT orapdt, oraptm, orodr#, c.ccctls, orbill, b.cuslmn, b.cusvrp, orocty, orost, o.cubzip, o.cucnty, ordcty, ordst, d.cubzip, d.cucnty
FROM order
LEFT JOIN cmtctlf c ON orbill = c.cccode
LEFT JOIN custmast b ON orbill = b.cucode
LEFT JOIN custmast o ON orldat = o.cucode
LEFT JOIN custmast d ON orcons = d.cucode
WHERE
orstat != 'C' AND
orbill IN ('ABCDE', 'VWXYZ', 'JKFRTE') AND
orapdt BETWEEN 2012365 AND 2013362 AND
o.cucnty = 'USA' AND
(o.cubzip LIKE '760%' OR o.cubzip LIKE '761%' OR o.cubzip LIKE '762%') AND
d.cubzip = '38652' AND
ordcty = 'NA' AND
ordst = 'MS' AND
d.cucnty = 'USA'
ORDER BY orapdt, oraptm, orodr#
Field definitions:
orapdt 7 0
oraptm 4a
orodr# 7a
c.ccctls 6a
orbill 6a
b.cuslmn 2a
b.cusvrp 3a
orocty 4a
orost 2a
o.cubzip 5a
o.cucnty 3a
ordcty 4a
ordst 2a
d.cubzip 5a
d.cucnty 3a
c.cccode 6a
b.cucode 6a
o.cucode 6a
d.cucode 6a
I see the following errors in my job log:
Field HVR0001 and value 1 not compatible. Reason 7.
Conversion error on host variable or parameter *N.
When I prompt for additional message information I'm told:
The attributes of variable field HVR0001 in query record format FORMAT0001 are not compatible with the attributes of value number 1. The value is *N. The reason code is 7.
7 -- Value contains numeric data that is not valid
and
Host variable or parameter *N or entry 1 in a descriptor area contains a value that cannot be converted to the attributes required by the statement. Error type 6 occurred.
6 -- Numeric data that is not valid.
These errors are triggered by opening the cursor:
...
exec sql PREPARE S1 FROM :sql_stmt;
exec sql DECLARE C1 SCROLL CURSOR FOR S1;
exec sql OPEN C1;
...
I also have QSQSVCDMP files in my outq filled with dump information. The only useful thing I see in there is a reference to CPF4278 and CPD4374
CPF4278 means Query definition template &1 not valid.
CPD4374 means Field &1 and value &3 not compatible. Reason &5.
Unfortunately the error message itself isn't there, only the strings "CPF4278" and "CPD4374".
In the program I monitor for SQL error codes and they are all the same:
SQLSTATE: 22023
SQLCODE: -302
SQLERRMC: <non-displayable character>*N
The error state/code means "A parameter or variable value is invalid."
What I've tried...
After much Googling I've tried:
What I'm asking...
How do I find out which field has bad data in it? I know that HVR0001 is invalid but which field is represented by HVR0001? I tried SELECTing fields in a different order but it's always HVR0001 that has an invalid value.
Ideally I'd like to be able to print out all HVR* fields/values so I can inspect them.
When I look at the compile listing there are no HVR* fields listed. There are some SQL_* fields listed and I can see that SQL_00011 is used to temporarily hold data that gets put into orapdt. SQL_00011 is defined exactly like orapdt (7,0 packed). That's the only numeric field in my query...
I feel like my problem is being caused by how the files are being joined, that somehow an invalid value (probably NULL) is being placed into my orapdt field.
I also think my problem has something to do with executing many of these queries one after the other (some of the WHERE specifics change for each query) because I can take one of the queries that fail and put it into it's own program and run it and it works fine.
This is on DB2 for i (V6R1) and all files involved were created using DDS
Edit: Here is the host variable (data structure) and the two external data structures needed for the LIKE statements:
d eds_custmast e ds extname('CUSTMAST') inz
d eds_order e ds extname('ORDER') inz
d o ds
d orapdt like(ORAPDT)
d oraptm like(ORAPTM)
d orodr# like(ORODR#)
d orctls like(CUCODE)
d orbill like(ORBILL)
d orslmn like(CUSLMN)
d orcsr like(CUSVRP)
d orocty like(OROCTY)
d orost like(OROST)
d orozip like(CUBZIP)
d orocntry like(CUCNTY)
d ordcty like(ORDCTY)
d ordst like(ORDST)
d ordzip like(CUBZIP)
d ordcntry like(CUCNTY)
// Define an array to indicate nulls...
d o1nv s 3i 0 dim(15)
And here's the fetch statement that actually gets the data:
dow sqlcode = *zeros;
exec sql FETCH NEXT FROM C1 INTO :o :o1nv;
if sqlcode = *zeros;
// process the data.
endif;
enddo;
exec sql CLOSE C1;
I didn't include this before simply because the error occurs when I'm OPENing the cursor, not FETCHing a row. The OPEN statement shouldn't know anything about the o data structure.
As for what changes in the WHERE clause - all of it is dynamically built (and thus can change) other than:
orstat != 'C' AND orapdt BETWEEN 2012365 AND 2013362
What seems to be the issue...
The code I posted in my question is in program A. Program A calls (via CALLP) program B. Nothing out of the ordinary there.
Program A uses embedded SQL declaring a prepared statement called S1 and a scrollable cursor called C1. Program B also happens to declare a prepared statement called S1 and a scrollable cursor called C1.
What appears to be happening is the cursor's are interfering with each other because they have the same name. My belief is the query being executed in program B is fetching data that is valid for itself – but is invalid for the query defined in program A. So when program A scrolls through the results of it's query and calls program B the query executed by program B attempts to put invalid values in fields associated with program A – and this only happens when the cursor names are the same in both programs.
All I did was give the cursors in both programs unique names (PGMA_C1 and PGMB_C1 for instance) and the errors stopped happening. Nothing else changed, just the cursor names. This goes against the information I found here (http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/rzala/rzalaccl.htm)
“Scope of a cursor: The scope of cursor-name is the source program in which it is defined; that is, the program submitted to the precompiler. Thus, a cursor can only be referenced by statements that are precompiled with the cursor declaration. For example, a program called from another separately compiled program cannot use a cursor that was opened by the calling program.”
Of course that statement seems to be contradicted by this one:
A cursor can only be referred to in the same instance of the program in the program stack unless CLOSQLCSR(*ENDJOB), CLOSQLCSR(*ENDSQL), or CLOSQLCSR(*ENDACTGRP) is specified on the CRTSQLxxx commands.
- If CLOSQLCSR(*ENDJOB) is specified, the cursor can be referred to by any instance of the program on the program stack.
- If CLOSQLCSR(*ENDSQL) is specified, the cursor can be referred to by any instance of the program on the program stack until the last SQL program on the program stack ends.
- If CLOSQLCSR(*ENDACTGRP) is specified, the cursor can be referred to by all instances of the module in the activation group until the activation group ends.
But in our case both program A and B have CLOSQLCSR(*ENDMOD) – so the two cursors shouldn't be aware of each other.
Unfortunately I don't have the time to dig into this any deeper. I have confirmed that simply giving each program a unique cursor name solves our problem.
Before I figured out that using unique cursor names would fix our problem I did comprehensive testing of all our data. Every field in every record in every file used by these two programs contains valid data. Based on the error message I was expecting there to be a NULL or some other invalid character somewhere but that wasn't the case.
I appreciate your replies and suggestions, +1 all around :-)