Db2

Db2

Where DBAs and data experts come together to stop operating and start innovating. Connect, share, and shape the AI era with us.


#Data


#Data
#Databases
#Operatingsystems
#Db2
#Databasesolutions
 View Only
Back to discussions
Expand all | Collapse all

DB2 v20 INSERT Issue

  • 1.  DB2 v20 INSERT Issue

    Posted 03/06/20 08:38 AM

    Hi All,
          Not sure what I'm doing wrong here, since I believe it mimics examples in IBM docs. Somewhat baffling because same basic syntaxes are working with MySQL.

    So I think I just need someone to look over my shoulder and tell me what mistake I'm making.... that I'm blind to. (LOL!)

    I have a table defined as follows :

    CREATE TABLE CONTACTS (id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), account varchar(50), actprimary varchar(44), phone varchar(14), email varchar(100));

    Confirmed :

    db2 => DESCRIBE TABLE FLMASON.CONTACTS

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 No    
ACCOUNT                         SYSIBM    VARCHAR                     50     0 Yes   
ACTPRIMARY                      SYSIBM    VARCHAR                     44     0 Yes   
PHONE                           SYSIBM    VARCHAR                     14     0 Yes   
EMAIL                           SYSIBM    VARCHAR                    100     0 Yes

    Using the following SQL a the db2 CLI interface I get the listed error. I've probably tried 100 combinations of quotes, double quotes, etc... no idea why it's complaining...

    db2 => INSERT INTO CONTACTS (ACCOUNT, ACTPRIMARY, PHONE, EMAIL) VALUES ('IGA', 'Frank Mason', '1-949-243-1501', 'fmason@us.ibm.com');
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0103N  The numeric literal "1501'" is not valid.  SQLSTATE=42604
db2 =>

    So if the mistake jumps out at anyone, I'd surely appreciate the tip.



    ------------------------------
    Frank Mason
    fmason@us.ibm.com
    ------------------------------

    #Db2


  • 2.  RE: DB2 v20 INSERT Issue

    Posted 03/06/20 09:22 AM
    Hello Frank,
    Both table creation and INSERT statements are fine.
    By default, db2 clp does not use semicolons at the end of sentences.
    Below is the result of my execution. The Db2 in my test environment is 11.1, 11.5.
    db2 => CREATE TABLE CONTACTS (id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), account varchar(50), actprimary varchar(44), phone varchar(14), email varchar(100))
DB20000I  The SQL command completed successfully.
db2 => DESCRIBE TABLE CONTACTS

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 No
ACCOUNT                         SYSIBM    VARCHAR                     50     0 Yes
ACTPRIMARY                      SYSIBM    VARCHAR                     44     0 Yes
PHONE                           SYSIBM    VARCHAR                     14     0 Yes
EMAIL                           SYSIBM    VARCHAR                    100     0 Yes

  5 record(s) selected.

db2 => INSERT INTO CONTACTS (ACCOUNT, ACTPRIMARY, PHONE, EMAIL) VALUES ('IGA', 'Frank Mason', '1-949-243-1501', 'fmason@us.ibm.com')
DB20000I  The SQL command completed successfully.​
db2 => SELECT * FROM CONTACTS

ID          ACCOUNT                                            ACTPRIMARY                                   PHONE          EMAIL
----------- -------------------------------------------------- -------------------------------------------- -------------- ----------------------------------------------------------------------------------------------------
          1 IGA                                                Frank Mason                                  1-949-243-1501 fmason@us.ibm.com

  1 record(s) selected.


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 3.  RE: DB2 v20 INSERT Issue

    Posted 03/06/20 02:49 PM

    Hi SangGyu,
        That's interesting and strange... I tried without the ";" also. 

    I will have to start up the processes on my test server and try again.

    The one difference that jumps out at me is, I'm using the newest version V20... perhaps there is bug in the CLI?

    Also strangely... when I try to use a "BACKSPACE" key... the control codes show up on screen, rather than the cursor moving backwards. So I'm thinking there may be a deeper problem that is corrupting my requests enroute to DB2.



    ------------------------------
    Frank Mason
    ------------------------------

    Original Message


  • 4.  RE: DB2 v20 INSERT Issue

    Posted 03/06/20 02:58 PM

    Here is a screen clip of my results. Let me know if you (anyone) sees my error. 



    ------------------------------
    Frank Mason
    ------------------------------

    Original Message


  • 5.  RE: DB2 v20 INSERT Issue

    Posted 03/06/20 04:38 PM
    Hi All,
         It came down to cut and pastes from OpenOffice... hand typing the SQL in gedit and pasting was good... hand typing on the console was good... hand typing in OpenOffice and pasting to the CLI... fails...

    ------------------------------
    Frank Mason
    ------------------------------

    Original Message


  • 6.  RE: DB2 v20 INSERT Issue

    Posted 03/06/20 07:22 PM
    Edited by System Admin 01/20/23 04:19 PM
    I guess the quotes are recognized as full-width character (double bytes) in OpenOffice.
    When I ran the single quote in the INSERT statement with a full-width character, the same error occurred.
    db2 => INSERT INTO CONTACTS (ACCOUNT, ACTPRIMARY, PHONE, EMAIL) VALUES (´IGA´, ´Frank Mason´, ´1-949-243-1501´, ´fmason@us.ibm.com´)
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0103N  The numeric literal "1501´" is not valid.  SQLSTATE=42604​


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    Original Message


  • 7.  RE: DB2 v20 INSERT Issue

    Posted 03/06/20 07:49 PM

    I'm not 100% certain. I found that cut & paste of the "bad" string into a mainframe editor caused the quotes to completely disappear. I wish I had a way to be sure of what the hex values the quotes are at various points in the process. 

    That said, I have mentioned it to some of the IBM DB2 team.

    It would be nice if the error messages flagged the error such as, "Invalid code point x'nn' found in input at offset nnn" or similar, as it's very confusing to see what looks like good data on screen, and get errors.

    That said, I hand typed the SQL into gedit... and cut and paste the OpenOffice version into gedit... they look identical... But when I cut and paste both instances into DB2... only the one that originated in OpenOffice fails... so it's a very hard to see issue.

    IBM DB2 L2 stated they'd seen similar with us of MS-Word as a source of cut & paste. 



    ------------------------------
    Frank Mason
    ------------------------------

    Original Message


  • 8.  RE: DB2 v20 INSERT Issue

    Posted 03/09/20 05:35 AM
    I can see your word processor version is actually using a different single quote Extended ASCII 239 ´ rather than the ' code 39 which is correct syntax.

    ------------------------------
    Alan S Bluck
    Director
    ASB Software Development Limited
    Ringwood, Hampshire, England
    0044 7710612479
    ------------------------------

    Original Message


  • 9.  RE: DB2 v20 INSERT Issue

    Posted 03/09/20 02:40 PM

    Ah, code pages strike again, LOL!

    Thanks for confirming.

    Lost my ghex hex editor in an RHEL 7 upgrade. Tried to copy the string into a mainframe hex editor, but the quotes we simple removed and the string re-justified, strangely/interestingly enough. Same issue came from using double quotes as well. I suspect the hex would show something similar.



    ------------------------------
    Frank Mason
    ------------------------------

    Original Message


  • 10.  RE: DB2 v20 INSERT Issue

    Posted 03/09/20 05:31 PM

    As an aside, here's what we're talking about it anyone wants to see it...

    Here's a screen clip of the "bad" and the "good"...


    I then cut & pasted them into "bad_quotes.txt" and "good_quotes.txt" and ran them through xxd to see what Alan was pointing out...Interestingly... that gives even different looking results... in that a quote isn't displayed in the text side of the xxd output.





    ------------------------------
    Frank Mason
    ------------------------------

    Original Message


  • 11.  RE: DB2 v20 INSERT Issue

    Posted 03/09/20 09:45 PM
    Edited by System Admin 01/20/23 04:16 PM
    Hello Frank,
    I tested it with the same text as your hexadecimal code.
    But I don't know why the single quote turns into ascii code 145/146 when you type the text in openoffice.

    -- bad_quotes.txt
    ‘ (left single quotation mark)   : UTF-8 0xe2 0x80 0x98 / extended ASCII 0x91 (145)
    ’ (right single quotation mark) : UTF-8 0xe2 0x80 0x99 / extended ASCII 0x92 (146)

    -- good_quotes.txt
    ' (single quote)                        : ASCII 0x27 (39)


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    Original Message


  • 12.  RE: DB2 v20 INSERT Issue

    Posted 03/09/20 10:06 PM
    Hi SangGyu, I don't know why either. I can say that a DB2 product developer mentioned it to me. He said he had a similar problem using MS-Word. I'm using OpenOffice. So apparently it's not unique, an is know to DB2 developers. It was the first thing he suggested.

    ------------------------------
    Frank Mason
    ------------------------------

    Original Message


  • 13.  RE: DB2 v20 INSERT Issue

    Posted 03/10/20 06:28 AM
    You might find the following link useful, it describes how to leave single quotes as is , as an option in OpenOffice:
    AutoCorrect options
    Openoffice remove preview
    AutoCorrect options
    AutoCorrection covers a variety of things, like when Apache OpenOffice changes a "normal" quote to a "typographic" quote, it automatically changes the first letter of the first word to a capital. Another instant is if a user types these characters -> and Apache OpenOffice changes them to this arrow →.
    View this on Openoffice >


    ------------------------------
    Alan S Bluck
    Director
    ASB Software Development Limited
    Ringwood, Hampshire, England
    0044 7710612479
    ------------------------------

    Original Message