Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    7

    Red face Unanswered: HELP !! How to add Alias to Local Dir Entry for DB2 when System Dir Entry exists

    Hi,

    I am working with DB2 v9.7.600 Fix Pack 6 and DB2 Lic = DB2ESE


    I am new to DB2 and accidententally have overwritten the Local Directory entries when trying to move a large database from one path to another.

    Now I have an entry for the (missing_db) in the system directory but none in the Local Path Directory and unable to do anything at all with the database.

    Can somebody please help me fix this issue so that I can add the entrry back to the Local Directory and re-use the database as it has a large amount of data that I do not want to re-create.


    Thanks in advance,
    SK
    Last edited by sk01; 11-01-13 at 05:31. Reason: Adding HELP to my msg

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Did you try catalog db whatever on /path/to/wherever/it/is?

    Please show the output of list db directory and list db directory on /wherever/you/think/it/is.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by sk01 View Post
    Now I have an entry for the (missing_db) in the system directory but none in the Local Path Directory and unable to do anything at all with the database.
    What's the exact error message you're getting when listing local db directory or connecting to the db?

    Quote Originally Posted by sk01 View Post
    Can somebody please help me fix this issue so that I can add the entrry back to the Local Directory and re-use the database as it has a large amount of data that I do not want to re-create.
    What files do you currently have in the local db directory? In addition to what n_i asked for, please include the listing of all files in the local db dir for missing_db (if there are any). What about your tablespace files? Do you still have them all in place?


    There is no easy way to add the entry back to the local db directory. Restore db will fix this. If you can't/don't want to restore it and all tablespace files are ok, then it's best to open a pmr and DB2 support should be able to assist you with rebuilding your local db directory.

  4. #4
    Join Date
    Sep 2011
    Posts
    7

    List DB Dir Output and List DB DIR on E output

    Hi All, thanks you so much for your help.

    Please find below the two outputs: I am looking to access the database with name HEI_PROD.


    The error I am getting when I try to access the database in the control center is as below:


    [IBM][CLI Driver] SQL1013N The database alias name or database
    name "HEI_PROD" could not be found. SQLSTATE=42705
    Explanation:

    The database name or alias specified in the command is not an existing
    database or the database could not be found in the (client or server)
    database directories or the db2dsdriver.cfg configuration file.

    User response:

    Ensure that the specified database name exists in the system database
    directory. If the database name does not exist in the system database
    directory, then the database either does not exist or the database name
    has not been cataloged.

    If the database name appears in the system database directory and the
    entry type is INDIRECT, ensure that the database exists in the specified
    local database directory. If the entry type is REMOTE, then ensure that
    the database exists and is cataloged on the database directories of the
    server node.

    For CREATE DATABASE with the AT NODE clause, ensure that the database
    name is in the system database directory with an entry type of INDIRECT
    and with a catalog node number that does not equal -1.

    Federated system users: in addition to the above, verify that the
    database names specified in SYSCAT.SERVERS are all valid. Correct any
    SYSCAT.SERVERS entry for which the database specified in that entry does
    not exist.

    sqlcode: -1013

    sqlstate: 42705




    LIST DB DIRECTORY
    System Database Directory

    Number of entries in the directory = 9

    Database 1 entry:

    Database alias = ICFFED
    Database name = ICFFED
    Local database directory = E:
    Database release level = d.00
    Comment =
    Directory entry type = Indirect
    Catalog database partition number = 0
    Alternate server hostname =
    Alternate server port number =

    Database 2 entry:

    Database alias = HEI_TDM
    Database name = DCS12A3B
    Node name = NDEC5F3
    Database release level = d.00
    Comment =
    Directory entry type = Remote
    Authentication = SERVER
    Catalog database partition number = -1
    Alternate server hostname =
    Alternate server port number =

    Database 3 entry:

    Database alias = ICFFEI
    Database name = ICFFEI
    Local database directory = D:
    Database release level = d.00
    Comment =
    Directory entry type = Indirect
    Catalog database partition number = 0
    Alternate server hostname =
    Alternate server port number =

    Database 4 entry:

    Database alias = FOXDV
    Database name = DCS46E86
    Node name = NDE4105D
    Database release level = d.00
    Comment =
    Directory entry type = Remote
    Authentication = SERVER
    Catalog database partition number = -1
    Alternate server hostname =
    Alternate server port number =

    Database 5 entry:

    Database alias = OPTIMDIR
    Database name = OPTIMDIR
    Local database directory = E:
    Database release level = d.00
    Comment =
    Directory entry type = Indirect
    Catalog database partition number = 0
    Alternate server hostname =
    Alternate server port number =

    Database 6 entry:

    Database alias = FOXTEST
    Database name = FOXTEST
    Local database directory = D:
    Database release level = d.00
    Comment =
    Directory entry type = Indirect
    Catalog database partition number = 0
    Alternate server hostname =
    Alternate server port number =

    Database 7 entry:

    Database alias = FOXICFPD
    Database name = DCS80D41
    Node name = NDE7B409
    Database release level = d.00
    Comment =
    Directory entry type = Remote
    Authentication = SERVER
    Catalog database partition number = -1
    Alternate server hostname =
    Alternate server port number =

    Database 8 entry:

    Database alias = FOXDVFED
    Database name = FOXDVFED
    Local database directory = E:
    Database release level = d.00
    Comment =
    Directory entry type = Indirect
    Catalog database partition number = 0
    Alternate server hostname =
    Alternate server port number =

    Database 9 entry:

    Database alias = FOXICFDV
    Database name = DCSD38B
    Node name = NDE768B
    Database release level = d.00
    Comment =
    Directory entry type = Remote
    Authentication = SERVER
    Catalog database partition number = -1
    Alternate server hostname =
    Alternate server port number =


    LIST DB DIRECTORY on E:

    list db directory;
    ------------------------------------------------------------------------------
    LIST DB DIRECTORY on E;
    ------------------------------------------------------------------------------
    LIST DB DIRECTORY on E

    Local Database Directory on E

    Number of entries in the directory = 3

    Database 1 entry:

    Database alias = HEI_TDM
    Database name = HEI_TDM
    Database directory = SQL00003
    Database release level = d.00
    Comment =
    Directory entry type = Home
    Catalog database partition number = 0
    Database partition number = 0

    Database 2 entry:

    Database alias = ICFFEI
    Database name = ICFFEI
    Database directory = SQL00002
    Database release level = d.00
    Comment =
    Directory entry type = Home
    Catalog database partition number = 0
    Database partition number = 0

    Database 3 entry:

    Database alias = FOXTEST
    Database name = FOXTEST
    Database directory = SQL00001
    Database release level = d.00
    Comment =
    Directory entry type = Home
    Catalog database partition number = 0
    Database partition number = 0

  5. #5
    Join Date
    Sep 2011
    Posts
    7

    Issue fixed...thanks

    Thank you db2girl....I got it fixed with a little bit of ingenuity and pointers from you all!! saw that it was also missing the entry in the system dir. I renamed the d/b name in the local file system, re-created db with same name and then switched the names back






    Quote Originally Posted by db2girl View Post
    What's the exact error message you're getting when listing local db directory or connecting to the db?



    What files do you currently have in the local db directory? In addition to what n_i asked for, please include the listing of all files in the local db dir for missing_db (if there are any). What about your tablespace files? Do you still have them all in place?


    There is no easy way to add the entry back to the local db directory. Restore db will fix this. If you can't/don't want to restore it and all tablespace files are ok, then it's best to open a pmr and DB2 support should be able to assist you with rebuilding your local db directory.

  6. #6
    Join Date
    Sep 2011
    Posts
    7

    Issue fixed...thanks

    Thank you n_i....I got it fixed with a little bit of ingenuity and pointers from you all!! saw that it was also missing the entry in the system dir. I renamed the d/b name in the local file system, re-created db with same name and then switched the names back


    Quote Originally Posted by n_i View Post
    Did you try catalog db whatever on /path/to/wherever/it/is?

    Please show the output of list db directory and list db directory on /wherever/you/think/it/is.

  7. #7
    Join Date
    Sep 2011
    Posts
    7

    Oops !! same problem but with different database

    Hi, sorry about that. got that one fixed where there were no entries for the database in booth system and local directories.

    But now I have the issue for another database, please see above outputs, with database OPTIMDIR. It's entry exists in the system but not in the Local.

    Can you please help fix it?

    Thanks in advance,
    SK

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    As per local db dir output, there are 3 databases on E:
    FOXTEST - SQL00001
    ICFFEI - SQL00002
    HEI_TDM - SQL00003



    As per system db dir output:

    3 db are catalogued on E:
    ICFFED
    OPTIMDIR
    FOXDVFED


    and 2 on D:
    ICFFEI
    FOXTEST



    Your local and system dir are not in sync.

    Your current issue is with OPTIMDIR which is catalogued on E: but is not listed in the local db dir on E:. You can remove it from the system dir using "uncatalog db optimdir" command if that's going to help you fix the problem (using your method of renaming/recreating...).


    What do you have in E:\<instance name>\<NODE0000> ? How many SQL0000x directories do you have and what else is in there?


    The way to "fix" the local db directory is to rename local db dir for all db's on E: (ie. rename NODE0000), recreate db's with the same name and in the same order as they were originally created and then switch old with new (kind of similar to what you had done). You have to know the name of all databases and the order in which they were created. It's best to open a pmr and get help from support.

    If you decide to do it, be very careful. You can fix one thing and break another.

    The easiest and safest way to resolve this mess is to restore the databases.
    Last edited by db2girl; 11-01-13 at 21:28.

  9. #9
    Join Date
    Sep 2011
    Posts
    7
    Thanks for the inputs db2girl. Appreciate it. I have in the E: Node0000 folder many database folders the folder it is not in sync. which is causing all these issues.

    Unfortunately, with this one database, I am still stuck. I was able to fix the entries with the same process and now have it synced-up but still cannot access the database as it gives me the message is that it cannot access container/tablespace (“SQL0293N Error accessing a table space container,. SQL State=57048).

    I have been trying for like 2 days and nights now to fix it but I have run out of ideas. Unfortunately, I have data in that database that I cannot loose and I am at my wit's end as to how to get it back. Do you have nay tips/suggestions?

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Do you have a backup image for this db?

    Please perform the following:

    -> db2diag -A
    -> reproduce sql0293n


    Attach newly created db2diag.log here.

Posting Permissions

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