Db2 for z/OS & Db2ZAI

 View Only
Expand all | Collapse all

Db2 trimming trailing blanks in table names in -601 SQLcode processing

  • 1.  Db2 trimming trailing blanks in table names in -601 SQLcode processing

    Posted Mon August 24, 2020 01:57 PM
    Good day,
        I am looking to find where it is documented that the CREATE TABLE process considers table
    "DJS"."STAFF    " the same as "DJS"."STAFF"

    Creating "DJS"."STAFF    " 
    and then creating "DJS"."STAFF"  results in -601 SQLcode - table already exists.

    I don't recall that behavior and I can't find it documented anywhere.

    Please advise.
    thank you
    dave seibert

    ------------------------------
    Dave Seibert
    ------------------------------

    #Db2forz/OS


  • 2.  RE: Db2 trimming trailing blanks in table names in -601 SQLcode processing

    Posted Tue August 25, 2020 01:51 AM

    SQL identifiers, "A delimited identifier is ... Trailing blanks in the sequence are not significant, although they are stored with the identifier."
    https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000720.html
    and repeated up to 11.5 .

    Similar wording, except for the 'stored with' bit,  for Db2 for z/OS

    https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_delimitedidentifiers.html





    ------------------------------
    JAMES CAMPBELL
    ------------------------------



  • 3.  RE: Db2 trimming trailing blanks in table names in -601 SQLcode processing

    Posted Tue August 25, 2020 08:22 AM
    Thank you James.
    That is exactly what I hoped to find.

    dave

    ------------------------------
    Dave Seibert
    ------------------------------



  • 4.  RE: Db2 trimming trailing blanks in table names in -601 SQLcode processing

    Posted Tue August 25, 2020 08:38 AM
    your welcome

    ------------------------------
    David Williams
    ------------------------------



  • 5.  RE: Db2 trimming trailing blanks in table names in -601 SQLcode processing

    Posted Tue August 25, 2020 09:58 AM
    Fyi table-name is a sql identifier.

    In any case space or spaces or any unprintable character is a bad idea in any table, column, view name, etc.  Not easy to tell 1 from 2 spaces in a non proportional font.

    Even mixing upper and lower case of use of accented characters can cause difficulties in using tables. Linux is case sensitive windows isn't. As can overly long names, the 128 character length limit is not a challenge.

    Far too easy for a programmer, data analyst or user to get it wrong. So keep things simple. 

    Oh and the pdf versions of the SQL reference make it easier to find answers to this kind of question. 


    ------------------------------
    Chris Andrews
    ------------------------------



  • 6.  RE: Db2 trimming trailing blanks in table names in -601 SQLcode processing

    Posted Wed September 09, 2020 06:09 PM

    Your second link above won't work anymore. The content that it refers to has now been consolidated here:
    https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_sqlidentifiers.html

    That was partly an attempt to improve the KC search results for this question.



    ------------------------------
    Paul McWilliams
    ------------------------------



  • 7.  RE: Db2 trimming trailing blanks in table names in -601 SQLcode processing

    Posted Tue August 25, 2020 06:51 AM
    Db2 will treat the table names you typed as being the same. it would do the same if removed the quotes also

    ------------------------------
    David Williams
    ------------------------------



  • 8.  RE: Db2 trimming trailing blanks in table names in -601 SQLcode processing

    Posted Tue August 25, 2020 09:57 AM
    There's also the ZPARM that affects SQL:

    LIKE_BLANK_INSIGNIFICANT

    ------------------------------
    DB2 DBA Team
    JobTitle
    Nationwide Building Society
    Swindon
    United Kingdom
    ------------------------------



  • 9.  RE: Db2 trimming trailing blanks in table names in -601 SQLcode processing

    Posted Tue August 25, 2020 09:58 AM
    In the SQL reference you find syntax 'create table table-name'. Table-name is further defined as 'sql identifier'  which comes in 2 basic forms undelimited and delimited (in quotes).  In delimited you'll find
    'Leading and embedded blanks in the sequence are significant. Trailing blanks in the sequence are not significant. The length of a delimited identifier does not include the starting and ending escape characters. Embedded escape characters (that appear as two characters) are counted in the length as a single character.'

    Btw find the pdf of the SQL reference and search don't try this in the knowledge centre you'll never find it.

    However ignore that for one minute. Why on earth would anyone put not printable or blank characters in a table name? It is a recipe for problems, even mixing upper case and lower case is asking for difficulty as some CLIs and interfaces are case sensitive and some are not. Windows v Linux. There is no reason to make life difficult, so why do it. It is why by and large table names are not 128 characters long in practice (though could be) because it makes it tough for programmers and more open to mistakes. 

    Just because you can doesn't mean you should. Data and information professionals should be aiming for clarity and simplicity.

    It is a pity the knowledge centre fails to provide the simplicity of the SQL reference pdf in this case, because I don't think you could answer the question without the pdf. Search on table-name in the knowledge centre produces more hits than you could search and nothing immediately useful to this question.  Needle in a haystack is an appropriate phrase.



    ------------------------------
    Chris Andrews
    ------------------------------



  • 10.  RE: Db2 trimming trailing blanks in table names in -601 SQLcode processing

    Posted Tue August 25, 2020 10:16 AM
    Thank you Chris.
    I agree.  This came from a Compuware customer with trailing blanks in their table name.

    Decades ago when 128-character names came along, I had a QA tester create a table with 120 * (asterisks) in the table creator and table name. Ok for QA testers to do that but sure annoying....

    Thank all for the comments.

    david seibert
    database philosopher king
    Compuware  a BMC company

    ------------------------------
    Dave Seibert
    ------------------------------