7 Replies Latest reply: Feb 4, 2013 10:31 AM by 982808 RSS

    Initial load failing between identical tables. DEFGEN skewed and fixable?

    987658
      Initial load failing between identical tables. DEFGEN skewed and fixable?

      Error seen:
      2013-01-28 15:23:46 WARNING OGG-00869 [SQL error 0 (0x0)][HP][ODBC/MX Driver] DATETIME FIELD OVERFLOW. Incorrect Format or Data. Row: 1 Column: 11.


      Then compared the discard record against a select * on the key column.

      Mapping problem with insert record (target format)...
      **** Comparing Discard contents to Select * display
      ABCHID = 3431100001357760616974974003012 = 3431100001357760616974974003012
      *!!! ABCHSTEPCD = 909129785 <> 9 ???*
      ABCHCREATEDDATE = 2013-01-09 13:43:36 = 2013-01-09 13:43:36
      ABCHMODIFIEDDATE = 2013-01-09 13:43:36 =2013-01-09 13:43:36
      ABCHNRTPUSHED = 0 = 0
      ABCHPRISMRESULTISEVALUATED = 0 = 0
      SABCHPSEUDOTERM = 005340 = 005340
      ABCHTERMID = TERM05 = TERM05
      ABCHTXNSEQNUM = 300911112224 = 300911112224
      ABCHTIMERQSTRECVFROMACQR = 1357799914310 = 1357799914310
      *!!! ABCTHDATE = 1357-61-24 00:43:34 <> 2013-01-09 13:43:34*
      ABCHABCDATETIME = 2013-01-09 13:43:34.310000 = 2013-01-09 13:43:34.310000
      ABCHACCOUNTABCBER =123ABC = 123ABC
      ABCHMESSAGETYPECODE = 1210 = 1210
      ABCHPROCCDETRANTYPE = 00 = 00
      ABCHPROCCDEFROMACCT = 00 = 00
      ABCHPROCCDETOACCT = 00 = 00
      ABCHRESPONSECODE = 00 = 00
      …. <snipped>

      Defgen comes out same when run against either table.
      Also have copied over and tried both outputs from DEFGEN.

      +- Defgen version 2.0, Encoding ISO-8859-1
      *
      * Definitions created/modified 2013-01-28 15:00
      *
      * Field descriptions for each column entry:
      *
      * 1 Name
      * 2 Data Type
      * 3 External Length
      * 4 Fetch Offset
      * 5 Scale
      * 6 Level
      * 7 Null
      * 8 Bump if Odd
      * 9 Internal Length
      * 10 Binary Length
      * 11 Table Length
      * 12 Most Significant DT
      * 13 Least Significant DT
      * 14 High Precision
      * 15 Low Precision
      * 16 Elementary Item
      * 17 Occurs
      * 18 Key Column
      * 19 Sub Data Type
      *
      Database type: SQLMX
      Character set ID: ISO-8859-1
      National character set ID: UTF-16
      Locale: en_EN_US
      Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
      *
      Definition for table RT.ABC
      Record length: 1311
      Syskey: 0
      Columns: 106
      ABCHID 64 34 0 0 0 0 0 34 34 34 0 0 32 32 1 0 1 3
      ABCHSTEPCD 132 4 39 0 0 0 0 4 4 4 0 0 0 0 1 0 0 0
      ABCHCREATEDDATE 192 19 46 0 0 0 0 19 19 19 0 5 0 0 1 0 0 0
      ABCHMODIFIEDDATE 192 19 68 0 0 0 0 19 19 19 0 5 0 0 1 0 0 0
      ABCHNRTPUSHED 130 2 90 0 0 0 0 2 2 2 0 0 0 0 1 0 0 0
      ABCHPRISMRESULTISEVALUATED 130 2 95 0 0 0 0 2 2 2 0 0 0 0 1 0 0 0
      ABCHPSEUDOTERM 0 8 100 0 0 0 0 8 8 8 0 0 0 0 1 0 0 0
      ABCTERMID 0 16 111 0 0 0 0 16 16 16 0 0 0 0 1 0 0 0
      ABCHTXNSEQNUM 0 12 130 0 0 0 0 12 12 12 0 0 0 0 1 0 0 0
      ABCHTIMERQSTRECVFROMACQR 64 24 145 0 0 0 0 24 24 24 0 0 22 22 1 0 0 3
      ABCTHDATE 192 19 174 0 0 0 0 19 19 19 0 5 0 0 1 0 0 0
      ABCHABCDATETIME 192 26 196 0 0 1 0 26 26 26 0 6 0 0 1 0 0 0
      ABCHACCOUNTABCER 0 19 225 0 0 1 0 19 19 19 0 0 0 0 1 0 0 0
      ABCHMESSAGETYPECODE 0 4 247 0 0 1 0 4 4 4 0 0 0 0 1 0 0 0
      ABCHPROCCDETRANTYPE 0 2 254 0 0 1 0 2 2 2 0 0 0 0 1 0 0 0
      ABCHPROCCDEFROMACCT 0 2 259 0 0 1 0 2 2 2 0 0 0 0 1 0 0 0
      ABCHPROCCDETOACCT 0 2 264 0 0 1 0 2 2 2 0 0 0 0 1 0 0 0
      ABCHRESPONSECODE 0 5 269 0 0 1 0 5 5 5 0 0 0 0 1 0 0 0
      … <snipped>

      The physical table shows a PACKED REC 1078

      And table invoke is:
      -- Definition of table ABC3.RT.ABC
      -- Definition current Mon Jan 28 18:20:02 2013

      (
      ABCHID NUMERIC(32, 0) NO DEFAULT HEADING '' NOT
      NULL NOT DROPPABLE
      , ABCHSTEPCD INT NO DEFAULT HEADING '' NOT NULL NOT
      DROPPABLE
      , ABCHCREATEDDATE TIMESTAMP(0) NO DEFAULT HEADING '' NOT
      NULL NOT DROPPABLE
      , ABCHMODIFIEDDATE TIMESTAMP(0) NO DEFAULT HEADING '' NOT
      NULL NOT DROPPABLE
      , ABCHNRTPUSHED SMALLINT DEFAULT 0 HEADING '' NOT NULL NOT
      DROPPABLE
      , ABCHPRISMRESULTISEVALUATED SMALLINT DEFAULT 0 HEADING '' NOT NULL NOT
      DROPPABLE
      , ABCHPSEUDOTERM CHAR(8) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT HEADING '' NOT NULL NOT DROPPABLE
      , ABCHTERMID CHAR(16) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT HEADING '' NOT NULL NOT DROPPABLE
      , ABCHTXNSEQNUM CHAR(12) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT HEADING '' NOT NULL NOT DROPPABLE
      , ABCHTIMERQSTRECVFROMACQR NUMERIC(22, 0) NO DEFAULT HEADING '' NOT
      NULL NOT DROPPABLE
      , ABCTHDATE TIMESTAMP(0) NO DEFAULT HEADING '' NOT
      NULL NOT DROPPABLE
      , ABCHABCDATETIME TIMESTAMP(6) DEFAULT NULL HEADING ''
      , ABCHACCOUNTNABCBER CHAR(19) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL HEADING ''
      , ABCHMESSAGETYPECODE CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL HEADING ''
      , ABCHPROCCDETRANTYPE CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL HEADING ''
      , ABCHPROCCDEFROMACCT CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL HEADING ''
      , ABCHPROCCDETOACCT CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL HEADING ''
      , ABCHRESPONSECODE CHAR(5) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL HEADING ''

      …. Snipped

      I suspect that the fields having subtype 3 just before the garbled columns is a clue, but not sure what to replace with or adjust.

      Any and all help mighty appreciated.