I seem to be driving myself nuts. Thank you Geoff for the great articles about inserting direct SQL. There seems to be a bug with the JDBC shim and time tokens that is driving me nuts. I'm calling it a bug because it doesn't error consistently either.

I have a stored procedure that says:
If a record doesn't exist then insert with these values
Else update with these values

The stored procedure works fine from the DB server console (this is the wonderful DB2) for both an insert or the update result.

When I call it from a policy set, I'm using the Time token to but in a string of yyyyMMdd. I've tried:
Straight time token - works for an update
local variable of time token - works for the insert
local variable of time token with uppercase wrapper - works for insert but not update
time token with uppercase wrapper - works with update but not insert

this seems bizarre to me. the shim\engine is obviously holding the format where you can't see it in the trace, but it is clearly different (looks the same either way).

Here is where it gets bizarre, if I use a time token = local variable with upper case wrapper and then start the driver the insert works. Then I try an update one and it fails, but if I just RESTART the driver the update then works.

Turning to trace level 5 it looks like the shim caches the metadata for the procedure and restarting the driver causes it to refresh. It still looks the same either way in the formatting, but something is definitely awry.

Here is the XML that is calling the procedure:

<input>
<jdbc:statement event-id="0" jdbc:transaction-id="2" jdbc:transaction-type="manual" jdbc:type="update" xmlns:jdbc="urn:dirxml:jdbc">
<jdbc:call-procedure jdbc:name="IDM.SP_EMAIL_PROCESS">
<jdbcaram>
<jdbc:value>A000658121</jdbc:value>
</jdbcaram>
<jdbcaram>
<jdbc:value>20080804</jdbc:value>
</jdbcaram>
<jdbcaram>
<jdbc:value>endof@uth.tmc.edu</jdbc:value>
</jdbcaram>
</jdbc:call-procedure>
</jdbc:statement>
</input>

the suspect value is the 20080804

here is the trace metadata:

20:18:40 96684BA0 Drvrs: SIS ST:Fetching metadata for routine 'IDMSIS.SP_EMAIL_PROCESS'.
20:18:40 96684BA0 Drvrs: SIS ST:BEGIN Get metadata for procedure/function 'IDMSIS.SP_EMAIL_PROCESS'.
20:18:40 96684BA0 Drvrs: SIS ST: Allocated result set e02fc4.
20:18:40 96684BA0 Drvrs: SIS ST: BEGIN Column.
20:18:40 96684BA0 Drvrs: SIS ST: Name: 'PERSONID'
20:18:40 96684BA0 Drvrs: SIS ST: SQL Type: java.sql.Types.CHAR
20:18:40 96684BA0 Drvrs: SIS ST: Native Type: CHAR
20:18:40 96684BA0 Drvrs: SIS ST: Parameter Type: IN
20:18:40 96684BA0 Drvrs: SIS ST: Length: 10
20:18:40 96684BA0 Drvrs: SIS ST: Position: 1
20:18:40 96684BA0 Drvrs: SIS ST: Nullable: YES
20:18:40 96684BA0 Drvrs: SIS ST: END Column.
20:18:40 96684BA0 Drvrs: SIS ST: BEGIN Column.
20:18:40 96684BA0 Drvrs: SIS ST: Name: 'EVTDATE'
20:18:40 96684BA0 Drvrs: SIS ST: SQL Type: java.sql.Types.INTEGER
20:18:40 96684BA0 Drvrs: SIS ST: Native Type: INTEGER
20:18:40 96684BA0 Drvrs: SIS ST: Parameter Type: IN
20:18:40 96684BA0 Drvrs: SIS ST: Precision: 10
20:18:40 96684BA0 Drvrs: SIS ST: Scale: 0
20:18:40 96684BA0 Drvrs: SIS ST: Position: 2
20:18:40 96684BA0 Drvrs: SIS ST: Nullable: YES
20:18:40 96684BA0 Drvrs: SIS ST: END Column.
20:18:40 96684BA0 Drvrs: SIS ST: BEGIN Column.
20:18:40 96684BA0 Drvrs: SIS ST: Name: 'EMAIL'
20:18:40 96684BA0 Drvrs: SIS ST: SQL Type: java.sql.Types.CHAR
20:18:40 96684BA0 Drvrs: SIS ST: Native Type: CHAR
20:18:40 96684BA0 Drvrs: SIS ST: Parameter Type: IN
20:18:40 96684BA0 Drvrs: SIS ST: Length: 60
20:18:40 96684BA0 Drvrs: SIS ST: Position: 3
20:18:40 96684BA0 Drvrs: SIS ST: Nullable: YES
20:18:40 96684BA0 Drvrs: SIS ST: END Column.
20:18:40 96684BA0 Drvrs: SIS ST: Closed result set e02fc4.
20:18:40 96684BA0 Drvrs: SIS ST:END Get metadata for procedure/function 'IDMSIS.SP_EMAIL_PROCESS'.
20:18:40 96684BA0 Drvrs: SIS ST:BEGIN Transaction 2
20:18:40 96684BA0 Drvrs: SIS ST: Old auto commit: true
20:18:40 96684BA0 Drvrs: SIS ST: New auto commit: false
20:18:40 96684BA0 Drvrs: SIS ST: BEGIN Call stored procedure 'IDMSIS.SP_EMAIL_PROCESS'.
20:18:40 96684BA0 Drvrs: SIS ST: {call IDMSIS.SP_EMAIL_PROCESS(?,?,?)}
20:18:40 96684BA0 Drvrs: SIS ST: BEGIN Instantiate callable statement.
20:18:40 96684BA0 Drvrs: SIS ST: Allocating statement from connection 132ca2b.
20:18:40 96684BA0 Drvrs: SIS ST: Allocated statement 600a08.
20:18:40 96684BA0 Drvrs: SIS ST: END Instantiate callable statement.
20:18:40 96684BA0 Drvrs: SIS ST: BEGIN Set IN, Register OUT parameter(s).
20:18:40 96684BA0 Drvrs: SIS ST: IN @ index 1, param 'PERSONID', field '$1', length: 10, value = 'A000658121'
20:18:40 96684BA0 Drvrs: SIS ST: IN @ index 2, param 'EVTDATE', field '$2', value = 20080804
20:18:40 96684BA0 Drvrs: SIS ST: IN @ index 3, param 'EMAIL', field '$3', length: 17, value = 'endof@uth.tmc.edu'
20:18:40 96684BA0 Drvrs: SIS ST: END Set IN, Register OUT parameter(s).
20:18:40 96684BA0 Drvrs: SIS ST: BEGIN Execute procedure.
20:18:40 96684BA0 Drvrs: SIS ST: END Execute procedure.
20:18:40 96684BA0 Drvrs: SIS ST: BEGIN Get OUT parameter values.
20:18:40 96684BA0 Drvrs: SIS ST: END Get OUT parameter values.
20:18:40 96684BA0 Drvrs: SIS ST: END Call stored procedure 'IDMSIS.SP_EMAIL_PROCESS'.
20:18:40 96684BA0 Drvrs: SIS ST: COMMIT
20:18:40 96684BA0 Drvrs: SIS ST:END Transaction 2
20:18:40 96684BA0 Drvrs: SIS ST:Released connection lock.
20:18:40 96684BA0 Drvrs: SIS ST:Number of statements allocated on connection 'dedicated2': 1.
20:18:40 96684BA0 Drvrs: SIS ST:SubscriptionShim.execute() returned:
20:18:40 96684BA0 Drvrs: SIS ST:
<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20070918_0743" instance="SIS" version="3.5.2">DirXML Driver for JDBC</product>
<contact>Novell, Inc.</contact>
</source>
<output>
<status level="warning">Primary key values taken from an event are useful only before insertion. Overriding key generation timing from 'after' to 'before' for table/view 'IDMSIS.V_IDM_STUDENT'.</status>
<status event-id="0" level="success"/>
</output>
</nds>


and then the failure:

<input>
<jdbc:statement event-id="0" jdbc:transaction-id="2" jdbc:transaction-type="manual" jdbc:type="update" xmlns:jdbc="urn:dirxml:jdbc">
<jdbc:call-procedure jdbc:name="IDMSIS.SP_EMAIL_PROCESS">
<jdbcaram>
<jdbc:value>A000658121</jdbc:value>
</jdbcaram>
<jdbcaram>
<jdbc:value>20080804</jdbc:value>
</jdbcaram>
<jdbcaram>
<jdbc:value>ending@uth.tmc.edu</jdbc:value>
</jdbcaram>
</jdbc:call-procedure>
</jdbc:statement>
</input>
</nds>
20:24:29 96684BA0 Drvrs: SIS ST:Acquired lock on connection 'dedicated2'.
20:24:29 96684BA0 Drvrs: SIS ST:BEGIN Transaction 2
20:24:29 96684BA0 Drvrs: SIS ST: BEGIN Call stored procedure 'IDMSIS.SP_EMAIL_PROCESS'.
20:24:29 96684BA0 Drvrs: SIS ST: {call IDMSIS.SP_EMAIL_PROCESS(?,?,?)}
20:24:29 96684BA0 Drvrs: SIS ST: BEGIN Set IN, Register OUT parameter(s).
20:24:29 96684BA0 Drvrs: SIS ST: IN @ index 1, param 'PERSONID', field '$1', length: 10, value = 'A000658121'
20:24:29 96684BA0 Drvrs: SIS ST: IN @ index 2, param 'EVTDATE', field '$2', value = 20080804
20:24:29 96684BA0 Drvrs: SIS ST: IN @ index 3, param 'EMAIL', field '$3', length: 18, value = 'ending@uth.tmc.edu'
20:24:29 96684BA0 Drvrs: SIS ST: END Set IN, Register OUT parameter(s).
20:24:29 96684BA0 Drvrs: SIS ST: BEGIN Execute procedure.
20:24:29 96684BA0 Drvrs: SIS ST: END Execute procedure.
20:24:29 96684BA0 Drvrs: SIS ST: BEGIN rollback.
20:24:29 96684BA0 Drvrs: SIS ST: ROLLBACK
20:24:29 96684BA0 Drvrs: SIS ST: END rollback.
20:24:29 96684BA0 Drvrs: SIS ST: Number of statements allocated on connection 'dedicated2': 1.
20:24:29 96684BA0 Drvrs: SIS ST:SubscriptionShim.execute() returned:
20:24:29 96684BA0 Drvrs: SIS ST:
<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20070918_0743" instance="SIS" version="3.5.2">DirXML Driver for JDBC</product>
<contact>Novell, Inc.</contact>
</source>
<output>
<status event-id="0" level="error" type="app-general">
<description>Unable to obtain value(s) from stored procedure 'IDMSIS.SP_EMAIL_PROCESS'. Unable to retrieve statement results.</description>
<jdbc:exception jdbc:class="java.lang.NullPointerException"/>
<jdbc:document xml:space="preserve"><nds dtdversion="3.5" ndsversion="8.x">
<source>
<product version="3.5.10.20070918 ">DirXML</product>
<contact>Novell, Inc.</contact>
</source>
<input>
<jdbc:statement event-id="0" jdbc:transaction-id="2" jdbc:transaction-type="manual" jdbc:type="update" xmlns:jdbc="urn:dirxml:jdbc">
<jdbc:call-procedure jdbc:name="IDMSIS.SP_EMAIL_PROCESS">
<jdbcaram>
<jdbc:value>A000658121</jdbc:value>
</jdbcaram>
<jdbcaram>
<jdbc:value>20080804</jdbc:value>
</jdbcaram>
<jdbcaram>
<jdbc:value>ending@uth.tmc.edu</jdbc:value>
</jdbcaram>
</jdbc:call-procedure>
</jdbc:statement>
</input>
</nds></jdbc:document>
</status>
</output>
</nds>