Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2014
    Posts
    2

    Unanswered: SQL0204N large table

    I got called into a strange problem that I have never seen before.

    DB2 v10.5.100.63, s130816, IP23521 and fix pack 1
    Server AWSE
    Windows

    They are using SPSS Modeler to create tables and load them but were getting some errors on the import. When I tried to access the table let us say USER1.TABLE1 I get:
    SQL0204N "TABLE1" is an undefined name. SQLSTATE=42704

    If I do a list tables for schema USER1 I see TABLE1 listed as an entry.
    I have also tried to drop the table, select from the table and always get the same answer.
    I then took the DDL for the table that was generated by MODELER and created it under a different schema and had no error when the table was created but I can't access that table either.
    I did a db2look on the database and all the ddl's were created for the tables that I can't access.

    The table has 145 columns in it and I think it is using the EXTENDED ROW SIZE available now.
    Is there something you have to do to tables that use that so they are ready to use? Shorter tables, with less columns, are working fine on the database.

    Any help would be appreciated.

    Steven

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by skblack View Post
    If I do a list tables for schema USER1 I see TABLE1 listed as an entry.
    Could we take a peek at what it looks like?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    can you try to do

    db2 "describe table USER1.TABLE1" see if you are getting an output
    - Ananth
    DB2 DBA LUW
    "coming second, just means you are the first loser"

  4. #4
    Join Date
    Sep 2014
    Posts
    2
    OK I created new table with the actual table name and I also gave the creation statement. I thought it was something about the way that Modeler was creating the table but I tried using the below statement on a command line and I still have the problem.


    C:\>db2 list tables for schema dev

    Table/View Schema Type Creation time
    ------------------------------- --------------- ----- --------------------------
    CAND_GBS_25Sept_2014 DEV T 2014-09-25-16.11.48.377001

    1 record(s) selected.


    C:\>db2 select count(*) From DEV.CAND_GBS_25Sept_2014
    SQL0204N "DEV.CAND_GBS_25SEPT_2014" is an undefined name. SQLSTATE=42704

    C:\>db2 select count(*) From "DEV.CAND_GBS_25Sept_2014"
    SQL0204N "DEV.CAND_GBS_25SEPT_2014" is an undefined name. SQLSTATE=42704

    C:\>db2 "Select count(*) from dev.cand_gbs_25Sept_2014"
    SQL0204N "DEV.CAND_GBS_25SEPT_2014" is an undefined name. SQLSTATE=42704

    C:\>db2 describe table DEV.CAND_GBS_25Sept_2014

    Data type Column
    Column name schema Data type 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

    C:\>db2 "describe table DEV.CAND_GBS_25Sept_2014"

    Data type Column
    Column name schema Data type 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


    Here is the creation statement I used for the table.
    CREATE TABLE "DEV"."CAND_GBS_25Sept_2014" (
    "CandJRSS" VARCHAR(255 OCTETS) ,
    "PERSONUID" DOUBLE ,
    "RCNUM" VARCHAR(255 OCTETS) ,
    "DATEMONTHEND" DATE ,
    "NAMELAST" VARCHAR(255 OCTETS) ,
    "NAMEFIRST" VARCHAR(255 OCTETS) ,
    "EMPEMAIL" VARCHAR(255 OCTETS) ,
    "MGRNAME" VARCHAR(255 OCTETS) ,
    "MGRRCNUM" VARCHAR(255 OCTETS) ,
    "MGREMAIL" VARCHAR(255 OCTETS) ,
    "AGREEMENTCODE" VARCHAR(8 OCTETS) ,
    "STATUSCODE" VARCHAR(4 OCTETS) ,
    "ISCOMMISSION" VARCHAR(4 OCTETS) ,
    "GEOGRAPHYCODE" VARCHAR(12 OCTETS) ,
    "REGIONID" VARCHAR(44 OCTETS) ,
    "SUBREGIONID" VARCHAR(56 OCTETS) ,
    "COUNTRYID" VARCHAR(60 OCTETS) ,
    "CITY" VARCHAR(255 OCTETS) ,
    "GROUPID" VARCHAR(12 OCTETS) ,
    "BUSINESSUNITID" VARCHAR(28 OCTETS) ,
    "ORGID" VARCHAR(255 OCTETS) ,
    "ENTITYCODE" VARCHAR(8 OCTETS) ,
    "ENTITYID" VARCHAR(60 OCTETS) ,
    "ENTITYGROUP" VARCHAR(28 OCTETS) ,
    "MGRLEVEL" DOUBLE ,
    "DIRREPORTS" DOUBLE ,
    "ORGREPORTS" DOUBLE ,
    "MANAGER_CODE" VARCHAR(4 OCTETS) ,
    "JOBCATEGORYNAME" VARCHAR(196 OCTETS) ,
    "JC_HASH" VARCHAR(52 OCTETS) ,
    "JOBCATEGORYCODE" VARCHAR(12 OCTETS) ,
    "JOBCATEGORYPRIMARYNAME" VARCHAR(152 OCTETS) ,
    "SERVICEINYEARSDOH" DOUBLE ,
    "SALARYBAND" VARCHAR(8 OCTETS) ,
    "PERFORMANCEASSESSMENTDATE" DATE ,
    "PERFORMANCERATINGCODE" VARCHAR(4 OCTETS) ,
    "PRIMARYJOBROLE" VARCHAR(255 OCTETS) ,
    "PRIMARYSKILLSET" VARCHAR(255 OCTETS) ,
    "PREVPBCASSESSDATE" DATE ,
    "PREVPBCRATINGCODE" VARCHAR(4 OCTETS) ,
    "PRIORPREVPBCDATE" DATE ,
    "PRIORPREVPBCRATINGCODE" VARCHAR(4 OCTETS) ,
    "JOBROLEASSESSMNT" DOUBLE ,
    "SKILLSETASSESSMNT" DOUBLE ,
    "BTLR" VARCHAR(12 OCTETS) ,
    "EDUCATIONLEVELCODE" VARCHAR(4 OCTETS) ,
    "EDUCATIONLEVELNAME" VARCHAR(84 OCTETS) ,
    "DATEINPOSITIONCD" DATE ,
    "SALARYBANDDATE" DATE ,
    "DATEOFHIRE" DATE ,
    "DATESERVICEREF" DATE ,
    "SERVICEINYEARSSRD" DOUBLE ,
    "EQUITYBAND" VARCHAR(255 OCTETS) ,
    "EQPOOL" VARCHAR(8 OCTETS) ,
    "EQDECISION" VARCHAR(8 OCTETS) ,
    "EQSTATUS" VARCHAR(4 OCTETS) ,
    "JOBTITLE" VARCHAR(255 OCTETS) ,
    "LAYERCODE" VARCHAR(4 OCTETS) ,
    "EQFLAG" DOUBLE ,
    "EMPCT" DOUBLE ,
    "MANAGER_R" DOUBLE ,
    "TENURE0" DOUBLE ,
    "TENURE1" DOUBLE ,
    "TENURE2" DOUBLE ,
    "TENURE3" DOUBLE ,
    "TENURE4" DOUBLE ,
    "TENURE5" DOUBLE ,
    "TENURE7" DOUBLE ,
    "TENURE8" DOUBLE ,
    "TENURE9" DOUBLE ,
    "ISCOMM" DOUBLE ,
    "BandLevel" DOUBLE ,
    "MonthsInCurrentBand" DOUBLE ,
    "YearsInCurrentBand" DOUBLE ,
    "PBC" DOUBLE ,
    "TopC" DOUBLE ,
    "MARKETID" VARCHAR(24 OCTETS) ,
    "GIESS" DOUBLE ,
    "edLevR2" DOUBLE ,
    "HIRETYPE" DOUBLE ,
    "GIEFLAG" DOUBLE ,
    "GIE_Flag" DOUBLE ,
    "SALARYBANDCODE" VARCHAR(8 OCTETS) ,
    "HRFLAG" DOUBLE ,
    "HREXEC" DOUBLE ,
    "EXECFLAG" DOUBLE ,
    "MonthTotal" DOUBLE ,
    "ScoreTotal" DOUBLE ,
    "SCORED_PBC" DOUBLE ,
    "V5" DOUBLE ,
    "V6" DOUBLE ,
    "BU_EVENTS" DOUBLE ,
    "ENTITYGROUP_LAST" VARCHAR(28 OCTETS) ,
    "SJC_EVENTS" DOUBLE ,
    "FIELDNAME" VARCHAR(12 OCTETS) ,
    "FIELDLASTVALUE" VARCHAR(12 OCTETS) ,
    "CAREERFIRST" DOUBLE ,
    "CAREERLAST" DOUBLE ,
    "CAREERGAP" DOUBLE ,
    "SALARYBANDCODE_EVENTS" DOUBLE ,
    "SALARYBANDCODE_LAST" VARCHAR(8 OCTETS) ,
    "CAREERMONTHS" DOUBLE ,
    "EMPLOYEEFLAG" VARCHAR(4 OCTETS) ,
    "Band_change" DOUBLE ,
    "SJC_change" DOUBLE ,
    "BU_change" DOUBLE ,
    "Vspeed" DOUBLE ,
    "CurrentPBC" DOUBLE ,
    "PreviousPBC" DOUBLE ,
    "PriorPreviousPBC" DOUBLE ,
    "PBC_Avg" DOUBLE ,
    "PBC_Hist_Avg" DOUBLE ,
    "InferredLang" VARCHAR(76 OCTETS) ,
    "ScoreToBand_1" INTEGER ,
    "ScoreToBand_2" INTEGER ,
    "ScoreToBand_3" INTEGER ,
    "ScoreToBand_4" INTEGER ,
    "ScoreToBand_5" INTEGER ,
    "ScoreToBand_6" INTEGER ,
    "ScoreToBand_7" INTEGER ,
    "ScoreToBand_8" INTEGER ,
    "ScoreToBand_9" INTEGER ,
    "ScoreToBand_10" INTEGER ,
    "ScoreToBand_D" INTEGER ,
    "ScoreToBand_C" INTEGER ,
    "ScoreToBand_B" INTEGER ,
    "ScoreToBand_A" INTEGER ,
    "PBC_score" BIGINT ,
    "Vspeed_score" INTEGER ,
    "TenureToBand_1_to_6" INTEGER ,
    "TenureToBand_7_to_8" INTEGER ,
    "TenureToBand_9_to_10" INTEGER ,
    "TenureToBand_D_to_A" INTEGER ,
    "EduToBand_1_to_5" INTEGER ,
    "EduToBand_6_to_7" INTEGER ,
    "EduToBand_8_to_10" INTEGER ,
    "EduToBand_D_to_A" INTEGER ,
    "BU_score" INTEGER ,
    "SJC_score" INTEGER ,
    "CandJRSS_Hash" BIGINT ,
    "XSkillJRSS" VARCHAR(255 OCTETS) ,
    "Cost" DOUBLE ,
    "Input" VARCHAR(20 OCTETS) )
    IN "USERSPACE1"
    ORGANIZE BY ROW;

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by skblack View Post
    CREATE TABLE "DEV"."CAND_GBS_25Sept_2014"
    You have created your table with the mixed case name "CAND_GBS_25Sept_2014", so you must use the same exact case and quote the name every time you reference it:

    Code:
    db2 'Select count(*) from dev."CAND_GBS_25Sept_2014"'
    Unquoted names are converted to upper case by default.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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