SQL -302 on OPEN cursor in SQLRPGLE

Question

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:

1. removing the ORDER BY clause (on OPEN, data is fetched and ordered when there is an ORDER BY clause)
2. changing all LEFT JOIN's to INNER JOIN's (did this to make sure there were no NULL's in the result records from the right side)
3. adding " AND orapdt IS NOT NULL" to the WHERE clause
4. many more things that I've forgotten

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

Answer 1

It's not at all easy to find out what the actual error is. I tend to copy statements like these into IBM i Navigator and use Visual Explain to try to get a grasp of what decisions the optimiser is making. Another way to do this is to do a STRDBG and look at the job log. When STRDBG is in effect, the optimiser puts informational messages into the job log. But even then, it cam be tough to puzzle out.

In this case, there's only one numeric column, orapdt. Try the query without that column and see if that's the culprit.

Answer 2

Since ORAPDT is your only numeric column, so the problem must lie there.

The issue is in the way DDS defined files work. The validity of values is not checked when being written into DDS defined files, so it appears you have non-numeric data in ORAPDT on one or more records. SQL does not like this, and throws an error.

SQL (DDL) defined tables validate the values before they are written, thus protecting the integrity of your database better.

To solve your problem, find the offending record(s) and fix them or delete them.

Answer 3

Assuming error comes from orapdt, you could monitor it by creating new varible or replacing null or garbage values with other number e.g. null = 9999999, non-numeric = 8888888

SELECT case when orapdt is null 
               then 9999999
            when TRANSLATE(SUBSTR(orapdt,1,LENGTH(orapdt)-1),' ','0123456789',' ') <>' '
               then 8888888
            else orapdt 
       end
       , oraptm,

or check thru strsql or run sql script for offending records

SELECT orapdt, oraptm, orodr#,
 ...
WHERE ( orapdt is null or TRANSLATE(SUBSTR(orapdt,1,LENGTH(orapdt)-1),' ','0123456789',' ') <>' ' ) AND
orstat != 'C' AND 
......

Answer 4

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 :-)