IBM Support

Restore database error with SQL1005N

Troubleshooting


Problem

Attempt to restore a database fails with SQL1005N complaining that the database already exist - even though db2 list db does not show the database.

Cause

Inconsistencies between list db directory vs list db directory on /path. This problem can be due to missing file system, corrupted file system, or partial drop of a database.

Environment

All platforms and versions

Diagnosing The Problem

db2 restore db sample to <path> from /tmp =>

SQL1005N The database alias "SAMPLE" already exists in either the local
database directory or system database directory.

db2 list db directory =>

SQL1057W The system database directory is empty. SQLSTATE=01606

db2 list db directory on <path> ( or whichever path you are trying to restore to )

Local Database Directory on /home/v97_06

Number of entries in the directory = 1

Database 1 entry:

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

Resolving The Problem

db2 catalog the database using "db2 catalog db SAMPLE on <path>"
db2 terminate
db2 drop database SAMPLE

Run the restore command again.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Routines (SP & UDF) - SQL","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"Version Independent","Edition":"All Editions","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21665868