Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: Can't view/drop table with 'Temp' Prefix

    Hi,

    I was just testing something out and found a weird problem --

    I tried to create a sample table in my main tablespace --

    CREATE TABLE TempTPeakData (
    "SITEID" INTEGER NOT NULL ,
    "PVAL" DOUBLE NOT NULL ,
    "PDATETIME" TIMESTAMP NOT NULL )
    DATA CAPTURE CHANGES
    IN "P_TBLSP" ;

    I can create the table just fine but have issues when trying to describe/drop this table... I keep getting this message :

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------

    0 record(s) selected.

    SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
    query is an empty table. SQLSTATE=02000


    If I give.. db2 "list tables", I see the table name in the list. Am I getting this error because the table is a mix of lowercase/uppercase letters? If so, how can I drop it? Also, if I use the All Caps version as the table name, I don't have any issues at all...thanks!
    Last edited by db2user; 09-22-08 at 19:46.

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    if the tablename consists of upper- and lowercase character, put it into double quotes.
    e.g.
    DROP TABLE "TempTPeakData" ;

  3. #3
    Join Date
    Dec 2002
    Posts
    123
    thanks much...i will try it out!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    To avoid guesswork, try this:
    Code:
    select tabschema, tabname from syscat.tables where lower(tabname) = 'temptpeakdata'
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Dec 2002
    Posts
    123
    great thanks! btw..umayer.. the command you gave worked..

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You should probably drop and recreate the table with a case insensitive name. Otherwise you are likely to lynched by a mob of angry developers and fellow DBA's.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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