DB2 - L

 View Only

  • 1.  Insert rows with varchar column in unique index.

    Posted Mar 30, 2023 08:49 AM

    I'm trying to insert a row into a table with a varchar column in the unique index.  A row exists with a value of 'ABC'.  When I try to insert a row with a value in that column of 'ABC " (space after ABC), I'm getting a duplicate key error.  Is this working as designed?  Is there a ZPARM to modify this behavior?  Thanks in advance for help.



    ------------------------------
    RichardJanniWakefern Food Corp.
    ------------------------------


  • 2.  RE: Insert rows with varchar column in unique index.

    Posted Mar 30, 2023 09:08 AM
    Sounds like the index is define as PADDED then varchar are simply stored with no length bytes and padded with spaces. Drop and recreate as NOT PADDED and all will be well.
    However there *were* some programs decades ago that did not like varchar in indexes...

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich


    Original Message


  • 3.  RE: Insert rows with varchar column in unique index.

    Posted Mar 30, 2023 11:37 AM

    Roy,

     

    Oh, cool! 

     

    I've never used a VARCHAR field in an index before, so I didn't even know that was a choice. I was afraid Rich was out-of-luck and I had visions of suggesting that they would have to use a fieldproc, and cipher the spaces as a different value.

     

    ::waves at Rich and the Old Gang::

     

    /phil

     

     

    Philip Sevetson

    Computer Systems Manager

    FISA-OPA

    5 Manhattan West

    New York, NY 10001

    psevetson@fisa-opa.nyc.gov

    917-991-7052 m

    212-857-1659 f

    image001.png@01D261E4.BE68E970

     




    Original Message


  • 4.  RE: Insert rows with varchar column in unique index.

    Posted Mar 31, 2023 11:49 AM

    Whilst I like Roy's solution (NOT PADDED index)

    I would question why you even want to allow 2 rows "ABC" and "ABC " (with a space) in your unique key. 

    I'm thinking more of the downstream impact (application processing) , how are they going to handle / differentiate . . . . ABC vs ABC(space) ?

    Jack



    ------------------------------
    Jack Campbell
    Saxon Consulting, Inc.
    ------------------------------

    Original Message