Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2009
    Posts
    9

    Unanswered: Db2 select statement

    Hello Expert,

    I have a table with the name /SCM/DF_D_TYPE.

    I can execute the command to describe the table.

    db2 describe table SAPDEV./SCMB/DF_D_TYPE

    Data type Column
    Column name schema Data type name Length Scale N
    ulls
    ------------------------------- --------- ------------------- ---------- ----- -
    -----
    CLIENT SYSIBM CHARACTER 3 0 N
    o
    CATEGORY SYSIBM VARCHAR 4 0 N
    o
    DF_LEVEL SYSIBM CHARACTER 1 0 N
    o

    3 record(s) selected.

    When I tried to execute the following select statement:

    db2 "select * from SAPDEV./SCMB/DF_D_TYPE"
    SQL0104N An unexpected token "* from SAPDEV." was found following "select ".
    Expected tokens may include: "<term>". SQLSTATE=42601

    What is the correct way to referencing the table name with '/'?

    Thanks in Advance!

    Pollyanna

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Try:

    db2 "select * from SAPDEV.\"/SCMB/DF_D_TYPE\""

  3. #3
    Join Date
    Feb 2009
    Posts
    9
    Bella,

    Thanks for your response,

    When I executed your suggested statement:

    db2 "select * from SAPDEV.\"/SCMB/DF_D_TYPE\""
    Unmatched ".

    It seems that the characters \" around the table name /SCMB/DF_D_TYPE confused db2 interpreter.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Works for me:


    test@p6db2serv /home/test > db2 list tables for schema SAPDEV

    Table/View Schema Type Creation time
    ------------------------------- --------------- ----- --------------------------
    /SCMB/DF_D_TYPE SAPDEV T 2009-10-23-09.25.49.231186

    1 record(s) selected.

    test@p6db2serv /home/test > db2 "select * from SAPDEV.\"/SCMB/DF_D_TYPE\""

    COL1
    -----------
    132

    1 record(s) selected.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you use special characters in identifiers, you need so-called "delimited identifiers". Those are described in the manual.

    The major issue with such identifiers is usually not DB2 but rather your shell. For example, on UNIX systems, the shell interprets single and double quotes, interfering with the SQL statement. You can avoid this by properly escaping characters with special meaning to a shell (e.g. *, ?, ", ') or you can work with the DB2 CLP to take the shell out of the picture.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Feb 2009
    Posts
    9
    I resolved the problem by using single qote:

    db2 'select * from SAPDEV."/SCMB/DF_D_TYPE"'

    I used the single quote to wrap the SQL select statement. I used the double quote to qualify the special character /.

    Thank you for your response.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Where I come from, people who use special characters (other than underscore) in table and/or column names are tarred and feathered. In Singapore, you get 100 lashes.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Don't know the reason why... but SAP loves to put '/' in their table names.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess they don't use SAP much in Singapore...
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl
    Don't know the reason why... but SAP loves to put '/' in their table names.
    Must be a German thing. Maybe Stolze knows.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I think it has something to do with "SAP namespaces".

    From http://www.ibm.com/developerworks/fo...geID=13979880:

    "In an SAP system there are so called name spaces for dictionary objects like database tables. The name space identifier consists of a forward slash, the name space name and another forward slash. The identifier is then part of the table name. For example the table that holds the service download customizing is called "/bdl/cust", with "bdl" being the name space."

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    Must be a German thing. Maybe Stolze knows.
    To, I don't. Usually, I have to consider delimited identifiers because what I'm working with is rather close to the database engine and I don't know what customers are doing.

    My guess on this thing is that SAP supports a wide variety of different DBMS as backend. Maybe there is even a file-based storage system possible.And because all common operating systems use '/' as directory separator, it be a way to organize things on disk without getting too big directories.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •