Conference orarep::nomahs::dbintegrator_public_public

First off, the DB2 Server runs fine with DB2 V3.1.  There has never been a
problem reported with the server and this DB2 Version.

In your base note you said it worked from dynamic SQL but failed with a zero
return code from a program.  This is almost certainly due to some datatype
incompatibility.  The answer will be something like dynamic SQL will work
because the whole WHERE clause on the DELETE gets pushed to DB2 and the rows
get deleted.  But with the program there is a datatype mismatch in one of the
parameters and DBI won't push the mismatched datatype to DB2, therefore a SELECT
is performed by DBI (with the intention of DELETEing the returned rows) but
some aspect of datatype incompatibility (for instance, FLOAT representations)
causes the SELECT to return no rows, therefore the DELETE works because no rows
matched the WHERE clause.

I'm sure if you look in here you'll find a very similar note that turned out to
be DBI not pushing DATE compares to DB2 because the program used some COBOL
datatype which was incompatible with DB2.  When the customer tried it with
dynamic SQL it worked fine (since DBI pushed the whole WHERE clause down).

The way to debug this is to run the dynamic SQL and the program with DB2_REQ_SQL
tracing turned on and find how the programs predicates in the WHERE clause
are being turned parameter markers.  Then investigate if you can change the
programs representation of the offending datatype.

    Hi,
    
    Thanks for the reply.
    
    The customer is curios to know why he could delete data written to the 
    database when using DB2 V2.3 and why he cannot do it now using DB2 V3.1.
    Has anything changed between these two versions, has he missed something.
    He claims to be doing nothing different now, to what he was doing before
    the upgrade.
    
    The customer has faxed me, saying that rows entered in the DB2 database
    from the IBM side cannot be deleted by the DBI-DB2 Server/Gateway under
    program control, yet rows entered from the VMS side by DBI-DB2 Server/
    Gateway (via interactive SQL on the VMS system) can be deleted under
    program control.
    
    The customer is unclear as to how a data-type mismatch problem would
    exhibit this behaviour.
    
    I will post the tracing as soon as the customer supplies it to me.
    
    Thanks,
    Mario. (on behalf of Dave)
    
    
    

>    The customer is curios to know why he could delete data written to the 
>    database when using DB2 V2.3 and why he cannot do it now using DB2 V3.1.
>    Has anything changed between these two versions, has he missed something.
>    He claims to be doing nothing different now, to what he was doing before
>    the upgrade.
    
I can't think of anything in DB2 V3.1 to cause this change, but I've been wrong
before.  Still, if I had to bet, I'd bet something else changed too and the
customer doesn't think that it's relevent (Oh, you mean it matters that I also
changed the scale on the packed decimal field?)

That "something" is almost certainly related to the handling of the datatypes.
I'm sure because in order to get a Success indicator on that DELETE statement
it must mean no rows matched the WHERE clause, and that is a very common
datatype mismatch symptom when DBI uses parameter markers to perform
the statement.

I guess tracing is the only way to figure this one out.

    Hi,
    
    I recieved the following reply from the customer:-
    
    I think you were right about the datatype mismatch, but i'm still
    puzzled as to why?
    
    The trace flag DB2_REQ_SQL didn't generate a lot of useful information
    other than the actual SQL statements. However, when I looked at results
    from a trace using the trace flag DB2_CXT_REQ (among others) I noticed
    that a timestamp - that we were using as part of a DBKEY - was being
    passed across as a 26 character entity. I removed the timestamp field
    from the DBKEY_MODE grouping for the problem table in my configuration
    file and the deletes worked.
    
    This is rather annoying as the data was initially retrieved from the
    DB2 database in VMS *BINARY* timestamp format which means - in my mind,
    anyway - that there is provision for the extra precision which seems to
    be displayed on the DB2 side. It appears that the client/server may be
    devolving the date/timestamp into a VMS *ASCII* string the length of
    which is only 23 characters which would imply that the extra 3
    characters in the fractions of second are lost.
    According to table 10-4, p 10-14 of the DEC DB Integrator Product
    Family Users Guide the magnitude for Date conversion is N/A which
    implies to me that the Server and Gateway should maintain the
    precision.
    
    I would be very interested in knowing whether any form of truncation
    occurs in DATE VMS datatypes when transferring to-and-fro via the
    DBI/DB2 Server and the client. I dont see why there should be, but my
    actions - removing the timestamp from the DBKEY list - have produced a
    different result which is more acceptable and implies that something
    screwy is going on.
    
    I had user-defined DBKEY assignments in the client DB2 attach
    configuration file because we had no indexes on the table we wished to
    delete from; now we know what to avoid we are looking at changing the
    table definition to allow a unique key of some sort so we can use
    /DBKEY_MODE=PRIMARY for the particular table.
    
    Thanks for the help so far. I'd be interested to know whether the
    above assertions are correct or otherwise.

the short answer is yes, there is truncation when converting from DB2
timestamp to DBI DATE VMS. although the vms date datatype allows for
sufficient precision, RdbSQL defines date/time datatypes with from 0 to 2
digits of fractional seconds, so the last 4 or 5 digits of the fractional
seconds from DB2 get lost in the conversion. 

in general it should be more efficient to use primary_key as a dbkey, so your
proposed change is the right way to go.

sorry about the problem,
linda kipnes
    

    Hi Linda,
    
    Thanks for the info, I'll let my customer know.
    
    Cheers,
    Mario