Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2012
    Posts
    3

    Unhappy Unanswered: error :SQL0204N "DB2ADMIN1.STORE" is an undefined name. SQLSTATE=42704

    hi i am new to Db2 ,i m using DB2 Enterprise Server Edition Version 9.7 with fixpack v9.7fp7_nt32_server on a windows vista machine

    we are using db2 as a backend for ibm - web sphere commerce suite[wcs].
    by default wcs uses " derby database",but for our need we migrated the database to db2.

    while migrating from derby to db2 , In db2 it created a schema name same as the username "db2admin" now there is only one user db2admin for this database instance(sampleinst).

    if i just add user to the db2 database(sampleinst) say "db2admin1", and give him admin privileges, he is able to connect with database but unable to perform any operations even select operation.

    the error i m getting when i execute a select query select * from store is

    SQL0204N "DB2ADMIN1.STORE" is an undefined name. SQLSTATE=42704

    SQL0204N "DB2ADMIN1.STORE" is an undefined name.

    Explanation:
    This error is caused by one of the following:

    * The object identified by "<name>" is not defined in the database.
    * The object identified by "<name>" is defined in a module and is not a
    published module object and it was referenced from outside the
    module.
    * The data partition identified by "<name>" is not defined on the
    table.
    * A data type is being used. This error can occur for the following
    reasons:
    * If "<name>" is qualified, then a data type with this name does not
    exist in either the schema that matches the qualifier or the
    module that matches the qualifier that was found first based on
    user's SQL path.
    * If "<name>" is unqualified, then the user's path does not contain
    the schema to which the desired data type belongs or the data type
    is not defined in the module if the reference is within a module
    routine.
    * The data type does not exist in the database with a create
    timestamp earlier than the time the package was bound (applies to
    static statements).
    * If the data type is in the UNDER clause of a CREATE TYPE
    statement, the type name may be the same as the type being
    defined, which is not valid.

    * A function is being referenced in one of:
    * a DROP FUNCTION statement
    * a COMMENT ON FUNCTION statement
    * the SOURCE clause of a CREATE FUNCTION statement

    If "<name>" is qualified, then the function does not exist. If
    "<name>" is unqualified, then a function of this name does not
    exist in any schema of the current path.

    Note that a function cannot be sourced on the COALESCE,
    DBPARTITIONNUM, GREATEST, HASHEDVALUE, LEAST, MAX (scalar), MIN
    (scalar), NULLIF, RID, NVL, RAISE_ERROR, TYPE_ID, TYPE_NAME,
    TYPE_SCHEMA, or VALUE built-in functions.

    * The element named "<name>" is used on the right side of the UNDER
    clause in CREATE SECURITY LABEL COMPONENT statement but has not yet
    been defined as being ROOT or being UNDER some other element
    * The security label component element named "<name>" has not yet been
    defined.
    * One of the following scalar functions specified a security policy
    identified by "<name>" which is not defined in the database.
    * SECLABEL
    * SECLABEL_TO_CHAR
    * SECLABEL_BY_NAME

    This return code can be generated for any type of database object.

    Federated system users: the object identified by "<name>" is not defined
    in the database or "<name>" is not a nickname in a DROP NICKNAME
    statement.
    Some data sources do not provide the appropriate values for "<name>". In
    these cases, the message token will have the following format:
    "OBJECT:<data source> TABLE/VIEW", indicating that the actual value for
    the specified data source is unknown.

    The statement cannot be processed.

    User response:

    Ensure that the object name (including any required qualifiers) is
    correctly specified in the SQL statement and it exists. If the name
    refers to a data partition, query the catalog table
    SYSCAT.DATAPARTITIONS to find the names of all the data partitions for a
    table. For missing data type or function in SOURCE clause, it may be
    that the object does not exist, OR it may be that the object does exist
    in some schema, but the schema is not present in your path.

    For the CREATE or ALTER SECURITY LABEL COMPONENT statement, make sure
    that each element specified as a reference element value for positioning
    the location of a new element value already exists in the security label
    component.

    For the CREATE SECURITY LABEL COMPONENT statement, make sure that each
    element is specified as either ROOT or as the child in an UNDER clause
    before specifying it as the parent in an UNDER clause.

    For the scalar functions SECLABEL, SECLABEL_TO_CHAR or SECLABEL_BY_NAME,
    ensure that a valid security policy was specified for the argument
    security-policy-name.

    Federated system users: if the statement is DROP NICKNAME, make sure the
    object is actually a nickname. The object might not exist in the
    federated database or at the data source. Verify the existence of the
    federated database objects (if any) and the data source objects (if
    any).

    sqlcode: -204

    sqlstate: 42704


    can you please give me steps to add a multiple user(atleast another user) to access this existing database sampleinst ?

    any suggestions will be very helpful...
    Last edited by manojkumar arul; 11-28-12 at 01:48.

  2. #2
    Join Date
    Sep 2012
    Posts
    177
    Hello manoj,

    Can you try like this "DB2ADMIN1"."STORE"

    Thanks,
    laxman.....

  3. #3
    Join Date
    Nov 2012
    Posts
    3
    hello laxman thanks for your reply

    when i execute the query
    select * from db2admin1.store

    geeting error "SQL0204N "DB2ADMIN1.STORE" is an undefined name. SQLSTATE=42704 "

    can you suggest me how to add users and grant them privileges to access the existing schema tables.

    my table schema name is "db2admin"
    ??


    i just added user db2admin1 through control center -> userand group objects->dbuser-> addusers

    and gave him connect to database, create tables , create packackes, database administrator authority privileges.

    i m new to databases and db2
    please help me

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    always qualify tablenames : otherwise the connectid will be used for schema
    try a select creator,name from sysibm.systables where name='STORE' to find the schema
    db2 list tables for schema xxxx -- will list all tables for user/schema
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Nov 2012
    Posts
    3
    i want to add a group of users
    they must have the priviliege to to connect, select,insert, update and delete..
    privilege

    i have a database "sampleinst"i have list of tables under the schema "db2admin" i have only one user(administrator) - "db2admin"
    how can i add user and how can i grant him all the privileges ?????????


    please help me with steps

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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