Working with write files

ASA Database Administration Guide
Working with Database Files

Working with write files

If you have a read-only database file (for example, if you distribute a database on a CD-ROM), you can use a write file to make local changes to the database.

You create a write file using the Create Write File utility or using the CREATE WRITEFILE statement. In this section, the examples use the utility.

For a description of the CREATE WRITEFILE statement, see CREATE WRITEFILE statement.

For more information about opening a database as read-only to prevent local changes to the database, see -r server option.

To use a write file

Create the write file for your database.

For example, to create a write file for the demo database, execute the following command in a directory containing a copy of the demo database file asademo.db:
```
dbwrite -c asademo.db
```
This command creates a write file named asademo.wrt, with a transaction log named asademo.wlg.
Start a database server and load the write file. By default, the server locates files with the extension .wrt first, so the following command starts the personal server running the demo database write file:
```
dbeng9 asademo
```
Messages on the server window indicate which file starts.
Connect to the database using Interactive SQL. You can use the user ID DBA and the password SQL, as the demo database is the default.
Execute queries as usual. For example, the following query lists the contents of the department table.
```
SELECT *
FROM department
```
The data for the department table is obtained from the database file asademo.db.
Try inserting a row. The following statement inserts a row into the department table:
```
INSERT
INTO department (dept_id, dept_name)
VALUES (202, 'Eastern Sales')
```
If you committed this change, it would be written to the asademo.wlg transaction log, and when the database checkpoints, the changes are written to the asademo.wrt write file.

If you now query the department table, the results come from the write file and the database file.
Try deleting a row. Set the WAIT_FOR_COMMIT option to avoid referential integrity complaints here:
```
SET TEMPORARY OPTION wait_for_commit = 'on' ;
DELETE
FROM department
WHERE dept_id = 100
```
If you committed this change, the deletion would be marked in the write file. No changes occur to the database file.

For some purposes, it is useful to use a write file with a shared database. If, for example, you have a read-only database on a network server, each user could have their own write file. In this way, they could add local information, which would be stored on their own machine, without affecting the shared database. This approach can be useful for application development also.

Deleting a write file

You can use the dberase utility to delete a write file and its associated transaction log.