Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Red face Unanswered: Error when dynamically creating a table

    Hi, I am trying to create a table in db2 , but i get this error: SQLCODE: -601, SQLSTATE: 42710 - The name of the object to be created is identical to the existing name "Clients.Systems" , and that table doesnt exist at all, here is the code: Any help will be highly appreciated.
    CODE:

    OPEN TableCursor;
    WHILE (Done = 0) DO
    FETCH TableCursor INTO TableName;
    SET dyn_stmt = 'CREATE TABLE ' CONCAT '"' CONCAT SchemaName CONCAT '"' CONCAT '.'
    CONCAT '"' CONCAT TableName CONCAT '" ('
    CONCAT '"systemID" INTEGER WITH DEFAULT NULL, '
    CONCAT '"sytemname" VARCHAR(200) WITH DEFAULT NULL, '
    CONCAT '"SystemCreatedDate" TimeStamp '
    CONCAT ')';
    EXECUTE IMMEDIATE dyn_stmt;

    END WHILE;
    CLOSE TableCursor;

    Any help will be highly appreciated.


  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is done initialized with another value somewhere - otherwise done is equal to 0 and the loop is always true...
    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

  3. #3
    Join Date
    Jan 2012
    Posts
    3
    Yes done is initialized with 0.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    For debugging purpose, comment out EXECUTE IMMEDIATE statement and show dyn_stmt of each iteration.

  5. #5
    Join Date
    Jan 2012
    Posts
    3
    Thanks for the reply, Tonkuma, please tell me how to do that? does db2 have the SHOW function? how can I do the show??

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Anyway by which you can know the values of dyn_stmt is OK.
    For example, DBMS_OUTPUT.PUT routine.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by RebeccaMopailo View Post
    i get this error: SQLCODE: -601, SQLSTATE: 42710 - The name of the object to be created is identical to the existing name "Clients.Systems" , and that table doesnt exist at all
    How did you verify that the table does not exist? DB2 clearly thinks otherwise.

Posting Permissions

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