title: inserting mixed db2 field with both alpha strings and packed decimals
body: <p>Below is a table/file layout for "SH.PROM",  a db2 table in an AS/400 that is responsible for rendering monthly <strong>promotion</strong> items throughout our system. Anyone not familiar with the typical table layouts should know that there are usually three columns: F00001, K00001 and F00002. Sometimes a fourth appears in ours (K00002). In our system, F00002 usually holds multiple subfields which I have provided name, position, length and data types for. Anything in the notes column labeled 'Fluff' means zeroed out or 'spaced' out in EBCDIC characters so non-important from a data standpoint but still needs to be in there properly.</p>
  
  <pre><code>        Field   Pos.  Len.  Name      Type     Notes
          ---------------------------------------------
          F00001    1     2   MACT      Alpha    Activity Code, Always 1 or 9 (active/inactive)
          K00001    1    20   MITEM     Alpha    Item Number
          K00001   21     2   MP        Alpha    Unknown title, Always 'P'
          K00001   23     2   MPITCL    Alpha    Unknown title, Always 'P' or 'I'
          F00002    1     8   MSTDAT    Integer  Promotion Start Date, MMDDYY0F
          F00002    9     8   MEXDAT    Integer  Promotion End Date, MMDDYY0F
          F00002   17     8   MLIST     Decimal  Amount to be subtracted from original price
          F00002   25     8   MRETAL    Decimal  Fluff, 0000000f
          F00002   33     8   MCOST     Decimal  Fluff, 0000000f
          F00002   41    10   MQTY      Decimal  Fluff, 000000000f
          F00002   51     6   MPCHNG    Integer  Fluff, 00000f
          F00002   57    12   MNPEFT    Alpha    Fluff, 404040404040
          F00002   69     8   MNSTDT    Integer  Fluff, 0000000f
          F00002   77     8   MNEXDT    Integer  Fluff, 0000000f
          F00002   85     8   MNLST     Decimal  Fluff, 0000000f
          F00002   93     8   MPNRTL    Integer  Fluff, 0000000f
          F00002  101     8   MNCOST    Decimal  Fluff, 0000000f
          F00002  109     8   ZZ14      Alpha    Fluff, 40404040
          F00002  117     8   MALPHA    Alpha    Fluff, 40404040
          F00002  125   170   ZZ64      Alpha    Fluff, 40404040404040...
  </code></pre>
  
  <p>I am having issues Inserting those promotion start/end dates. </p>
  
  <p>If I insert data into column F00002 as a string, it automatically converts to EBCDIC numbers:</p>
  
  <pre><code>    INSERT INTO "SH.PROM" (F00001,K00001,F00002) VALUES ('1','0000620311PP','0201140F0228140F...')
  </code></pre>
  
  <p>( ***The trailing dots on F00002's insert data means the rest follows the file/table layout above. )</p>
  
  <p>And F00002 as an integer just to try it:</p>
  
  <pre><code>    INSERT INTO "SH.PROM" (F00001,K00001,F00002) VALUES ('1','0000620311PP',0201140...)
  </code></pre>
  
  <p>The column omits the left padded zero and converts it to EBCDIC characters again.</p>
  
  <p>Here is an example of an existing F00002 field as well as two helpful SquirrelShots :) (aka SQL Squirrel Screen Shot) <a href="http://i.imgur.com/9wj2TEt.jpg" rel="nofollow">First</a> <a href="http://i.imgur.com/hWCNJ9S.jpg" rel="nofollow">Second</a> :</p>
  
  <pre><code>  08  01  13  0f  01  31  14  0f  00  00  38  0f  00  00  00  0f  00  00  00  0f  00  00  06  80  0f  00  00  0f  f0  f0  f0  f0  f0  f0  00  00  00  0f  00  00  00  0f  00  00  00  0f  00  00  00  0f  00  00  00  0f  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  
  </code></pre>
  
  <p>How do I insert this F00002 field so that the data stores properly?</p>