Create index filled up transaction log

[yongsing]

I tried to create an index on a column of a table. It failed because
the creation caused the transaction log to be full. What can I do?
Should I export out the data in the table, then recreate the table
with the index, and finally import back the data?

Thanks in advance.

Yong Sing

[Mark A]

"yongsing" <oh********@yahoo.com.sg> wrote in message
news:59*************************@posting.google.co m...

I tried to create an index on a column of a table. It failed because
the creation caused the transaction log to be full. What can I do?
Should I export out the data in the table, then recreate the table
with the index, and finally import back the data?

Thanks in advance.

Yong Sing

Why not increase the number and size of the log files?

[Jing Li]

clean the log (it's better to enlarge it)
and then rebuild the index (or maybe recreate the index)

Charlie
"yongsing" <oh********@yahoo.com.sg> wrote in message
news:59*************************@posting.google.co m...

I tried to create an index on a column of a table. It failed because
the creation caused the transaction log to be full. What can I do?
Should I export out the data in the table, then recreate the table
with the index, and finally import back the data?

Thanks in advance.

Yong Sing

[Mauro Cazzari]

You can increase the number and/or the size of your logs. For future
references, you may want to look at creating a table with the 'NOT LOGGED
INITIALLY' feature. When adding a new index, the table can be altered to
activate the above feature so that logging will not occur. Look at the
documentation for more details.
Mauro.

"yongsing" <oh********@yahoo.com.sg> wrote in message
news:59*************************@posting.google.co m...

I tried to create an index on a column of a table. It failed because
the creation caused the transaction log to be full. What can I do?
Should I export out the data in the table, then recreate the table
with the index, and finally import back the data?

Thanks in advance.

Yong Sing

[yongsing]

Thanks guys. How do I actually increase the log file size?

TIA
Yong Sing

[Mark A]

"yongsing" <oh********@yahoo.com.sg> wrote in message
news:59**************************@posting.google.c om...

Thanks guys. How do I actually increase the log file size?

TIA

Yong Sing

The easiest way is to use the Control Center. Right click on the database
(sample, etc) and select Configure...

You will see a tab there for "Logs." Increase the file size, number of
primary logs, and number of secondary logs. When you click on the parameter
there are hints on the lower right to assist you. The default values are
extremely low and need to be increased significantly for any large
production database.

Note that this is for Version 7 Control Center, version 8 Control Center is
slightly different, but same concept.

To change the parms using the command line, see chapter called Configuring
DB2 in the Administration Guide: Performance. The command line format is:
db2 connect to <dbname>
db2 update db cfg using <parameter-name> <parameter-value>
db2 connect reset