Use the command to delete the DB2 database, and then create

Use the command to delete the DB2 database, and then create a new
When we create a new database, we often encounter the error that the database alias already exists. There may be two reasons for this error:
1. This database exists in the system database directory.
Can be seen in the Control Center and can be seen using LIST DB DIRECTORY in the DB2CLP window.
2. The database is "removed" rather than "deleted".
Another possibility, this is because we used the "Remove" database function instead of the "Delete" database function in the control center (or use the "UNCATALOG DB database name" command in the DB2CLP window). The "Remove" database is only in the Logically deleting the database's catalog information does not actually delete the database. Just when we use LIST DB DIRECTORY or check in the control center, we can't see the database. When we create a new database with this database name, SQL1005N error is reported.
The specific steps to solve this problem are as follows:
1. First check whether the database exists in the system database directory by issuing the "DB2 LIST DB DIRECTORY" command in the DB2CLP window. If so, see if this database is still useful, if not, delete the database and re-create it.
2. If the database does not exist in the system database directory, check whether the database exists in the local database directory through db2 list database directory on location. Location specifies the location of the database (only drive letters under Windows, such as C: Or D :, it can be a specific file directory under UNIX). If there is, use the db2 catalog database database name on location to catalog the database to the node, then delete the database and re-create it.
Let's take a look at a specific example. Continue in the DB2CLP window and issue the LIST DB DIRECTORY command to see which databases we currently have in the system database directory, as shown in Listing 22
As shown:
Listing 22. Looking at the system database directory, what databases are there
C: \> DB2 LIST DB DIRECTORY System database directory Number of entries in the directory = 7
Database 1 entries:
Database alias = EMR
Database name = EMR
Local database directory = C:
Database release level = c.00
Comment =
Directory entry type = indirect
Directory database partition number = 0
Alternate server host name =
Standby server port number =
Database 2 entries:
Database alias = DBSAMPL2
Database name = DBSAMPL2
Local database directory = C:
Database release level = c.00
Comment =
Directory entry type = indirect
Directory database partition number = 0
Alternate server host name =
Standby server port number =
Database 3 entries:
Database alias = ABC
Database name = SAMPLE
Local database directory = C:
Database release level = c.00
Comment =
Directory entry type = indirect
Directory database partition number = 0
Alternate server host name =
Standby server port number =
Database 4 entries:
Database alias = TEST
Database name = EMR
Node name = NEW
Database release level = c.00
Comment =
Directory entry type = remote
Directory database partition number = -1
Alternate server host name =
Standby server port number =
Database 5 entries:
Database alias = MYABC
Database name = ABC
Node name = MYREMOTE
Database release level = c.00
Comment =
Directory entry type = remote
Directory database partition number = -1
Alternate server host name =
Standby server port number =
Database 6 entries:
Database alias = SAMPLE_1
Database name = SAMPLE_1
Local database directory = C:
Database release level = c.00
Comment =
Directory entry type = indirect
Directory database partition number = 0
Alternate server host name =
Standby server port number =
Database 7 entries:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = C:
Database release level = c.00
Comment =
Directory entry type = indirect
Directory database partition number = 0
Alternate server host name =
Standby server port number =
The command completed successfully. We can see that there are currently 7 records in the system database directory, that is, there are 7 databases or aliases. At this point we "remove" the database SAMPLE_1 in the control center, as shown in Figure 5:
Figure 5. Remove the database SAMPLE_1 in the Control Center
At this time, in the DB2CLP window, issue the "CREATE DB SAMPLE_1" command, and we will get the "SQL1005N database alias" SAMPLE_1 "already exists in the local database directory or the system database directory." Listing 24 shows:
Listing 24. Creating the database SAMPLE_1
C: \> DB2 CREATE DB SAMPLE_1
SQL1005N The database alias "SAMPLE_1" already exists in the local database directory or the system database directory.
The solution to this problem is to re-enter the existing database on CATALOG, and then "delete" instead of "remove". After processing, you can use this database name to create a new database successfully.
Next, we continue to issue the "LIST DB DIRECTORY ON C:" command in the current DB2CLP window. You can see that the database SAMPLE_1 still exists in the local directory. Refer to the solution at the beginning of this section. The second step is to first CATALOG the database, DROP the database before it can be recreated, as shown in Listing 25:
Listing 25. Creating the database SAMPLE_1
C: \> db2 list db directory on c:
local database directory on c: number of entries in directory = 4
Database 1 entries:
Database alias = EMR
Database name = EMR
Database directory = SQL00001
Database release level = c.00
Comment =
Directory entry type = local
Directory database partition number = 0
Database partition number = 0
Database 2 entries:
Database alias = DBSAMPL2
Database name = DBSAMPL2
Database directory = SQL00004
Database release level = c.00
Comment =
Directory entry type = local
Directory database partition number = 0
Database partition number = 0
Database 3 entries:
Database alias
= SAMPLE_1 database name
= SAMPLE_1 database directory
= SQL00003 Database release level
= c.00 comment
= Directory entry type = local
Directory database partition number
= 0 database partition number
= 0 Database 4 entries:
Database alias = SAMPLE
Database name = SAMPLE
Database directory = SQL00002
Database release level = c.00
Comment =
Directory entry type = local
Directory database partition number = 0
Database partition number = 0
C: \> db2 catalog db sample_1
DB20000I CATALOG DATABASE command completed successfully.
DB21056W The directory changes do not take effect until the directory cache is refreshed.
C: \> db2 terminate
DB20000I TERMINATE command completed successfully.
C: \> db2 drop db sample_1
DB20000I DROP DATABASE command completed successfully.
C: \> db2 create db sample_1
DB20000I CREATE DATABASE command completed successfully.
The command completed successfully.