IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 13 replies
  • Latest Post - ‏2012-07-08T16:49:22Z by jemz
jemz
jemz
16 Posts

Pinned topic Error in restoring database

‏2012-07-05T16:11:30Z |
Hi,

I need some help please, after i restored my database which i made back up before, it gives me in this error,

SQL1005N The database alias "MYDB" already exists in either the local
database directory or system database directory. SQLSTATE=00000

Can you please help me how can i restore back my database.

Thank you in advance.
Updated on 2012-07-08T16:49:22Z at 2012-07-08T16:49:22Z by jemz
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Error in restoring database

    ‏2012-07-05T16:30:08Z  
    Looks like the catalog entry is still there. Try below command and run the restore command again. See if it works.
    
    db2 uncatalog db mydb
    


    Thanks
  • jemz
    jemz
    16 Posts

    Re: Error in restoring database

    ‏2012-07-05T16:39:31Z  
    Looks like the catalog entry is still there. Try below command and run the restore command again. See if it works.
    <pre class="jive-pre"> db2 uncatalog db mydb </pre>

    Thanks
    hi, thank you for the reply...

    I tried the command this is the output..

    DB20000I The UNCATALOG DATABASE command completed successfully.
    DB21056W Directory changes may not be effective until the directory cache is refreshed.

    what should i do now?please help me.
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Error in restoring database

    ‏2012-07-05T16:43:50Z  
    • jemz
    • ‏2012-07-05T16:39:31Z
    hi, thank you for the reply...

    I tried the command this is the output..

    DB20000I The UNCATALOG DATABASE command completed successfully.
    DB21056W Directory changes may not be effective until the directory cache is refreshed.

    what should i do now?please help me.
    Now run the restore command and see if it works. If it does not work then paste the o/p of db2 list db directory command.

    Thanks
  • jemz
    jemz
    16 Posts

    Re: Error in restoring database

    ‏2012-07-05T16:47:49Z  
    Now run the restore command and see if it works. If it does not work then paste the o/p of db2 list db directory command.

    Thanks
    This is the output after i execute the restore command.

    restore db mydb from c:\backup;

    restore db mydb from c:\backup
    SQL1005N The database alias "mydb" already exists in either the local
    database directory or system database directory.
  • jemz
    jemz
    16 Posts

    Re: Error in restoring database

    ‏2012-07-05T16:53:15Z  
    Now run the restore command and see if it works. If it does not work then paste the o/p of db2 list db directory command.

    Thanks
    list db directory;

    list db directory
    SQL1057W The system database directory is empty. SQLSTATE=01606
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Error in restoring database

    ‏2012-07-05T17:08:16Z  
    • jemz
    • ‏2012-07-05T16:53:15Z
    list db directory;

    list db directory
    SQL1057W The system database directory is empty. SQLSTATE=01606
    Then your machine must have "mydb" exist. Try below steps and see if it works.

    
    db2 catalog db mydb db2 drop db mydb
    


    Now execute restore db command.

    Thanks
  • jemz
    jemz
    16 Posts

    Re: Error in restoring database

    ‏2012-07-05T17:17:38Z  
    Then your machine must have "mydb" exist. Try below steps and see if it works.

    <pre class="jive-pre"> db2 catalog db mydb db2 drop db mydb </pre>

    Now execute restore db command.

    Thanks

    Commands Entered
    catalog db mydb;

    catalog db mydb
    DB20000I The CATALOG DATABASE command completed successfully.
    DB21056W Directory changes may not be effective until the directory cache is
    refreshed.


    Commands Entered
    drop db mydb;

    drop db mydb
    DB20000I The DROP DATABASE command completed successfully.

    What should i do now?
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Error in restoring database

    ‏2012-07-05T17:19:42Z  
    • jemz
    • ‏2012-07-05T17:17:38Z

    Commands Entered
    catalog db mydb;

    catalog db mydb
    DB20000I The CATALOG DATABASE command completed successfully.
    DB21056W Directory changes may not be effective until the directory cache is
    refreshed.


    Commands Entered
    drop db mydb;

    drop db mydb
    DB20000I The DROP DATABASE command completed successfully.

    What should i do now?
    Now execute restore command.
  • jemz
    jemz
    16 Posts

    Re: Error in restoring database

    ‏2012-07-05T17:28:15Z  
    Now execute restore command.

    Commands Entered
    restore db mydb from c:\backup;

    restore db mydb from c:\backup
    DB20000I The RESTORE DATABASE command completed successfully.


    list tables

    Table/View Schema Type Creation time
    ---------------
    --------------------------

    0 record(s) selected.
    Hi, i restored now but my table is 0,how can i get back my tables?
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Error in restoring database

    ‏2012-07-05T19:49:48Z  
    • jemz
    • ‏2012-07-05T17:28:15Z

    Commands Entered
    restore db mydb from c:\backup;

    restore db mydb from c:\backup
    DB20000I The RESTORE DATABASE command completed successfully.


    list tables

    Table/View Schema Type Creation time
    ---------------
    --------------------------

    0 record(s) selected.
    Hi, i restored now but my table is 0,how can i get back my tables?
    Are you using the right schema? Probably your tables are in another schema and you are querying the table of the default schema.

    Try:

    db2 list tables for all

    This will return all tables, including those of the catalog.
  • jemz
    jemz
    16 Posts

    Re: Error in restoring database

    ‏2012-07-06T03:20:04Z  
    Are you using the right schema? Probably your tables are in another schema and you are querying the table of the default schema.

    Try:

    db2 list tables for all

    This will return all tables, including those of the catalog.
    After execute the list tables for all,My tables is in the schema DB2ADMIN.
    I am in the Admin account where i installed the db2 express c...
    please help me,is it possible to see or to use my tables without using the DB2ADMIN account?
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: Error in restoring database

    ‏2012-07-06T23:11:44Z  
    • jemz
    • ‏2012-07-06T03:20:04Z
    After execute the list tables for all,My tables is in the schema DB2ADMIN.
    I am in the Admin account where i installed the db2 express c...
    please help me,is it possible to see or to use my tables without using the DB2ADMIN account?
    It depends on your security schema.
    Probably, you can do select or DML operations (insert, delete, update) on the tables.
    Imagine you have a table T1 that was created with the user USER_A, and it took the implicit schema. The complete name of that table is USER_A.T1.
    When you connect with user USER_A, you can use that table without a schema, just T1: Select * from T1.
    But also, you can execute: Select * from USER_A.T1
    As you can see, the schema, which is the same user name, is noot necessary to do the previous examples.
    However, if you connect to the table with a user called USER_B, and you issue this query: Select * from T1, DB2 will return an error, saying that USER_B.T1 does not exists. This is due to the implicit schema, that is the same username.
    To prevent this error, you qualify the table with the right schema when connecting with USER_B, in this case: Select * from USER_A.T1
    For the moment, we saw how to issue the queries. However, the security schema could influence here, and when the table USER_A.T1 was created, it only allows to USER_A to use it, or probably due to other permissions (grants)
    If you can execute a query like this: Select * from USER_A.T1 without problem, you could probably want to change the default schema in order to prevent the use of that schema:
    db2 set schema USER_A
    That means, that if you connect with USER_B, and change the default schema, the queries will use the other schema, instead of the username of the connection.
  • jemz
    jemz
    16 Posts

    Re: Error in restoring database

    ‏2012-07-08T16:49:22Z  
    It depends on your security schema.
    Probably, you can do select or DML operations (insert, delete, update) on the tables.
    Imagine you have a table T1 that was created with the user USER_A, and it took the implicit schema. The complete name of that table is USER_A.T1.
    When you connect with user USER_A, you can use that table without a schema, just T1: Select * from T1.
    But also, you can execute: Select * from USER_A.T1
    As you can see, the schema, which is the same user name, is noot necessary to do the previous examples.
    However, if you connect to the table with a user called USER_B, and you issue this query: Select * from T1, DB2 will return an error, saying that USER_B.T1 does not exists. This is due to the implicit schema, that is the same username.
    To prevent this error, you qualify the table with the right schema when connecting with USER_B, in this case: Select * from USER_A.T1
    For the moment, we saw how to issue the queries. However, the security schema could influence here, and when the table USER_A.T1 was created, it only allows to USER_A to use it, or probably due to other permissions (grants)
    If you can execute a query like this: Select * from USER_A.T1 without problem, you could probably want to change the default schema in order to prevent the use of that schema:
    db2 set schema USER_A
    That means, that if you connect with USER_B, and change the default schema, the queries will use the other schema, instead of the username of the connection.
    Hi, thank you for the reply and thank you also for explaining me on this.okay i will try this and i will write back to you if i have doubt.

    Thank you so much again.