Search

Ramchandra's Weblog

Just ……everything about DB2 LUW

Month

March 2009

DB2 Viper 2 compatibility features

DB2 Viper 2 compatibility features

DB2 Viper 2 compatibility features

Determining performance problems with DB2 Java applications

Determining performance problems with DB2 Java applications

Determining performance problems with DB2 Java applications

Recovering a dropped table in DB2

you may some times drop a db2 table accidental. To restore a full database backup it would take more time if the database size is big.

here is how you recover the dropped table:-

  • create a database to test table recovery

#db2 create db testdt using codepage 1256 country AA
DB20000I  The CREATE DATABASE command completed successfully.

  • Check if tablespace are enabled for drop table recovery

#db2 “select TBSPACE, DROP_RECOVERY from SYSCAT.TABLESPACES
TBSPACE                                                                                                                          DROP_RECOVERY
——————————————————————————————————————————– ————-
SYSCATSPACE                                                                                                                      N
TEMPSPACE1                                                                                                                       N
USERSPACE1                                                                                                                       Y
  3 record(s) selected.

 

  • Enable database for rollforwad recovery

#db2 “update database configuration for testdt using  logretain on”
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.

  • take a backup of the database

#db2 backup db testdt
Backup successful. The timestamp for this backup image is : 20090311121103

  • create some tables in the database

#db2 connect to testdt

Database Connection Information

 Database server        = DB2/NT 9.5.0
 SQL authorization ID   = ADMINIST…
 Local database alias   = TESTDT

db2 “create table TORECOVER(NAME varchar(55) not null primary key)”
DB20000I  The SQL command completed successfully.

#db2 “insert into TORECOVER values(‘one’) “
DB20000I  The SQL command completed successfully.

#db2 “insert into TORECOVER values(‘two’) “
DB20000I  The SQL command completed successfully.

#db2 “insert into TORECOVER values(‘three’) “
DB20000I  The SQL command completed successfully.

#db2 “insert into TORECOVER values(‘four’) “
DB20000I  The SQL command completed successfully.

  • drop the table

#db2 “drop table TORECOVER”
DB20000I  The SQL command completed successfully.

  • check the database history to get the droped table details

#db2 “list history dropped table all for testdt”

                    List History File for testdt

Number of matching file entries = 1

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 — — —————— —- — ———— ———— ————–
  D  T  20090311121524                                        000000000000e60000020004
 —————————————————————————-
  “ADMINISTRATOR”.”TORECOVER” resides in 1 tablespace(s):

  00001 USERSPACE1
 —————————————————————————-
    Comment: DROP TABLE
 Start Time: 20090311121524
   End Time: 20090311121524
     Status: A
 —————————————————————————-
  EID: 2

 DDL: CREATE TABLE “ADMINISTRATOR”.”TORECOVER” ( “NAME” VARCHAR(55) NOT NULL )  IN “USERSPACE1” ;
 —————————————————————————-
 

  •  restore the tablespace which had the droped table

#db2  “restore database testdt tablespace (USERSPACE1) “
DB20000I  The RESTORE DATABASE command completed successfully.

  • roll forward the database with recover dropped table option

#db2 “rollforward database testdt to end of logs and complete recover dropped table 000000000000e60000020004 to ‘C:\Documents and Settings\Administrator\work\'”

                                 Rollforward Status

 Input database alias                   = testdt
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    =  –
 Last committed transaction             = 1970-01-01-00.00.00.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

  • recreate the droped table

#db2 “create table TORECOVER(NAME varchar(55) not null primary key)”
DB20000I  The SQL command completed successfully.

  • import the data to the table

#db2 “IMPORT FROM ‘C:\Documents and Settings\Administrator\work\NODE0000\data’ OF DEL METHOD P(1) MESSAGES ‘C:\Documents and Settings\Administrator\work\importlog.msg’ INSERT INTO  TORECOVER (NAME)”

Number of rows read         = 4
Number of rows skipped      = 0
Number of rows inserted     = 4
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 4

  • check if the rows are imported properly

#db2 “select * from TORECOVER”

NAME
——————————————————-
one
two
three
four

 

Use trusted context in DB2 client applications

Use trusted context in DB2 client applications

DB2 Audit facility in V9.5

http://download.boulder.ibm.com/ibmdl/pub/software/data/sw-library/db2/labchats/20080619-slides.pdf

Converting non-Unicode databases to Unicode

Converting non-Unicode databases to Unicode

There are some cases where you might need to convert an existing non-Unicode database to a Unicode database.
Before you begin
You must have enough free disk space to export the data from the non-Unicode database. Also, if you are not reusing the existing table spaces, you will need enough free disk space to create new table spaces for the data.
About this task
The following steps illustrate how to convert an existing non-Unicode database to a Unicode database:

1. Export your data using the db2move command:

cd <export-dir>
db2move sample export

where is the directory to which you want to export your data and SAMPLE is the existing database name.
2. Generate a DDL script for your existing database using the db2look command:

db2look -d sample -e -o unidb.ddl -l -x -f

where SAMPLE is the existing database name and unidb.ddl is the file name for the generated DDL script. The -l option generates DDL for user defined table spaces, database partition groups and buffer pools, the -x option generates authorization DDL, and the -f option generates an update command for database configuration parameters.
3. Create the Unicode database:

CREATE DATABASE UNIDB COLLATE USING SYSTEM_codepage_territory

where UNIDB is the name of the Unicode database and SYSTEM_codepage_territory is a language-aware collation based on the weight table used for collating your non-Unicode data. This ensures that the data in the new Unicode database will be sorted in the same order.
4. Edit the unidb.ddl script:
1. Change all occurrences of the database name to the new Unicode database name:

CONNECT TO UNIDB

2. Increase the column lengths for character columns in your tables. When characters are converted to Unicode, there may be an expansion in the number of bytes. It is recommended that you increase the length of the character columns to compensate for this expansion.
3. To keep the existing database, you must also change the file name specification for table spaces in the unidb.ddl file. Otherwise, you can drop the existing database and use the same table space files:

DROP DATABASE SAMPLE

5. Recreate your database structure by running the DDL script that you edited:

db2 -tvf unidb.ddl

6. Import your data into the new Unicode database using the db2move command:

cd <export-dir>
db2move unidb import

where is the directory where you exported your data and UNIDB is the Unicode database name.

DB2 9.5 Upgrade Benefits & Best Practices (V2) – ChannelDB2

DB2 9.5 Upgrade Benefits & Best Practices (V2) – ChannelDB2

Create a free website or blog at WordPress.com.

Up ↑