Deleting attachments from the database

You can manually delete attachments from the database to create more space for new data.

Before you begin

Before you delete any attachments review the following list of tables that can be affected:

ATCHREL - Attachment Relation
ATCHRELDSC - Attachment Relation Description
ATCHTGT - Attachment Target
ATCHTGTDSC - Attachment Target Description
ATCHAST - Attachment Asset
ATCHASTLG - Attachment Asset Language
CMFILE - Content Managed File
CMSMALLFILE - Content of CMFILE
CMLARGEFILE - Content of CMFILE
ATCHRLUS - Attachment Relation Usage
ATCHRLUSDS - Attachment Relation Usage Description

Note: Do not delete ATCHOBJTYP, Attachment Object Type, as this is part of the bootstrap.

About this task

To delete attachments:

Procedure

Backup your database. Refer to your database manual for instructions.
Delete the attachment relation by typing in the following statement at a command line:
```
db2 delete from ATCHREL where ATCHOBJTYP_ID='objectype' and
OBJECT_ID='XXX'  
```
where objectype can be one of the following values:
- 1; indicates that the attachment is associated with a catalog object type, catalog.
- 2; indicates that the attachment is associated with a category, catgroup.
- 3; indicates that the attachment is associated with the catalog entry, catentry.
- 4; indicates that the attachment is associated to an e-Marketing Spot, collateral.
This will cascade delete to the ATCHRELDSC table. OBJECT_ID is the primary key of the associated table. If ATCHOBJTYP_ID=3 (catentry), then this corresponds to a CATENTRY identifier for example, @catentry_product_id_kitchenware_coffeemaker_1. If ATCHOBJTYP_ID=4 (e-Marketing Spot), then this corresponds to the primary key in the COLLATERAL table
Note: If you only delete the relation, the attachment is prevented from showing up in the Accelerator however, it still exists in the database.
Delete the attachment target by typing the following statement at a command line:
```
db2 delete from ATCHTGT where ATCHTGT_ID in (select ATCHTGT_ID from
ATCHAST where ATCHASTPATH like '%subdirectory/filename%') 
```
where :
- filename - the name of the attachment.
- subdirectory - the subdirectory where the file is located.
This will cascade delete to the to ATCHTGTDSC, ATCHAST, and ATCHASTLG tables.
Delete the attachment object. There is no cascade delete relationship. First delete the CMSMALLFILE and CMLARGEFILE tables and then delete the CMFILE table. Delete the attachment object by typing in the following statements on a command line:
```
db2 delete from CMSMALLFILE where CMFILE_ID in (select CMFILE_ID from CMFILE where CMFILEPATH like '%StoreDirectoryName/subdirectory/filename%') 
```
```
db2 delete from CMLARGEFILE where CMFILE_ID in (select CMFILE_ID from CMFILE where CMFILEPATH like '%StoreDirectoryName/subdirectory/filename%') 
```
```
db2 delete from CMFILE where CMFILEPATH like '%StoreDirectoryName/subdirectory/filename%'
```
where
- filename - the name of the attachment.
- subdirectory - the subdirectory where the file is located.
- StoreDirectoryName - the name of the store directory.

Results

The attachment is deleted from the database.