Make changes to LDAS database
Some minor changes to the tables can be done online via db2 client, e.g. drop or add a foreign keys, add new tables, update configuration parameters.
Changes that require the database to be offline, e.g. rebuilding it: follow the following steps:
- Check if applications are connected to the database you need to change by entering via db2 client:
- Shutdown metadataAPI via cmonClient, API control page.
- If there are applications currently connected to the database, you can wait for them to disconnect
or force them off if safe to do so by issuing the following db2 command:
db2 force applications ( 91, 97, 101 )
where 91, 97 and 101 are the application handles shown by 'list applications'.Check that there are no clients connected to DB2 server:
db2 'list applications' SQL1611W No data was returned by Database System Monitor. SQLSTATE=00000
- Back up the database
To ensure the database can be restored in case of catastrophy e.g. accidentally dropping a table and losing all the data, make a temporary backup of the database as follows: e.g. backup to a partition /usr1/databases/backup
db2 'backup database tst_test to /usr1/databases/backup' Backup successful. The timestamp for this backup image is : 20040223132847
- Make the necessary changes to the database.
Connect to the database you desire to change via db2 client.
ldasdb@metaserver ~]$ db2 'connect to ldas_tst' Database Connection Information Database server = DB2/LINUX 8.2.1 SQL authorization ID = LDASDB Local database alias = LDAS_TST
- Add a new column insertion_time to table process
db2 'alter table process add column insertion_time TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP;
- Drop a foreign key SEGMENT_FK_SEGDEF from table segment
db2 'alter table segment drop FOREIGN KEY SEGMENT_FK_SEGDEF ;'
- Add a new foreign key SEGMENT_FK_PID to table segment
db2 'alter table add foreign key SEGMENT_FK_PID (process_id,creator_db) references process(process_id,creator_db);
- Replace an empty existing table calib_info schema by dropping and adding back the table
and reinstating the table permissions.
Caution: DO NOT DROP AND ADD A TABLE IF THERE IS EXISTING DATA IN THE TABLE
db2 'drop table calib_info' db2 '-tf /ldas/doc/db2/doc/text/calib_info.sql' db2 '-tf grant.sql'
- Update a database configuration parameter locklist to 400
db2 'update database configuration for ldas_tst using locklist 400'
- If some error occurs and you need to restore the database data from the backup, here is
an example:
db2 'restore database tst_test from /usr1/databases/backup taken at 20040223132847' SQL2539W Warning! Restoring to an existing database that is the same as the backup image database. The database files will be deleted. Do you want to continue ? (y/n) y DB20000I The RESTORE DATABASE command completed successfully.
- If new databases have been added or database user and passwords have changed, regenerate
the metadataAPI LDASdsnames.ini file:
cd /ldas_outgoing/metadataAPI /ldas/bin/dbDsnames
Enter the user names and passwords foreach database and hit APPLY to create the new LDASdsnames.ini file.
- If you have created a new database and would like it to be the default database, you need to edit
/ldas_outgoing/LDASapi.rsc to set the default database name:
set ::DATABASE_NAME cit_test
See Create Database for details on linking LDAS to the database.
- Bootstrap metadataAPI via cmonClient, API control page.
- Data Fragmentation
- Symptom: insertion deadlock errors, slow insertion rates
- Cause: table or index data is fragmented.
- Fix: Run reorg to compact data and eliminate fragmentation.
- Diagnostic: run reorg on the database in question as database user ldasdb:
db2 'connect to ldas_tst' db2 'reorgchk update statistics on table all' > out & tail -f out Table statistics: F1: 100 * OVERFLOW / CARD < 5 F2: 100 * (Effective Space Utilization of Data Pages) > 70 F3: 100 * (Required Pages / Total Pages) > 80 SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG ---------------------------------------------------------------------------------------- LDASDB CALIB_INFO - - - - - - - - - --- LDASDB COINC_SNGL - - - - - - - - - --- LDASDB EXTTRIG_SEARCH - - - - - - - - - --- LDASDB FILTER - - - - - - - - - --- LDASDB FILTER_PARAMS - - - - - - - - - --- LDASDB FRAMESET - - - - - - - - - --- LDASDB FRAMESET_CHANLIST - - - - - - - - - --- LDASDB FRAMESET_LOC - - - - - - - - - --- LDASDB FRAMESET_WRITER - - - - - - - - - --- LDASDB GDS_TRIGGER 47 0 3 1920 - 8554 0 0 0 -** ...
For tables with ** in the REORG column, they can be reorganized as follows:
Reorganizing a table:
ldasdb:ldas:metaserver.ldas-dev:~> db2 'reorg table search_summary' DB20000I The REORG command completed successfully.
Reorganizing indexes of a table
ldasdb:ldas:metaserver.ldas-dev:~> db2 'reorg indexes all for table gds_trigger' DB20000I The REORG command completed successfully.
Reorganizing a system table:
ldasdb:ldas:metaserver.ldas-dev:~> db2 'reorg table sysibm.SYSINDEXES' DB20000I The REORG command completed successfully.
See DB2 documentation
db2 => list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- ------------------------------ -------- -----
LDASDB metadataAPI 97 *LOCAL.ldasdb.020522160516 LDAS_TST 1
LDASDBRO metadataAPI 101 *LOCAL.ldasdb.020522160517 LDAS_TST 1
LDASDB metadataAPI 96 *LOCAL.ldasdb.020522160503 CIT_TEST 1
LDASDBRO metadataAPI 100 *LOCAL.ldasdb.020522160504 CIT_TEST 1
LDASDB db2bp 91 *LOCAL.ldasdb.020522160531 CIT_TEST 1
To remove these applications off the database, you can:
Below are some examples of changing the table schema or database configuration. For more details of DB2 commands, check the IBM DB2 command reference on the web.
Performance tuning
This section describes some performance tuning we have successfully applied to the LDAS database.