Search

Ramchandra's Weblog

Just ……everything about DB2 LUW

Category

Uncategorized

A simple cheat sheet that shows commands every sysadmin managing WebSphere MQ could use

 

http://bencane.com/2013/04/22/websphere-mq-cheat-sheet-for-system-administrators/

Recover deleted SQL Server data and tables with the help of Transaction Log and LSNs

some good articles to recover dropped data

http://www.sqlskills.com/blogs/paul/finding-out-who-dropped-a-table-using-the-transaction-log/

http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

Registering the Mapped Network Path on SQL Server Management Studio

You, you need to register the mapped network path on the SQL Server Management Studio in order to be able to access the mapped drive from SQL Server Management Studio File Explorer. To do so, open SQL Server Management Studio and execute the query below.

EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
EXEC sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE
GO

EXEC XP_CMDSHELL ‘net use N: /delete’
EXEC XP_CMDSHELL ‘net use N: \\ServerName\DB-Backup-ShareName’
EXEC XP_CMDSHELL ‘Dir N:’

How to Leverage DB2’s Automated Time Travel Queries and Temporal Tables

How to Leverage DB2’s Automated Time Travel Queries and Temporal Tables | MainframeZone.com

Best practices: Physical database design for online transaction processing (OLTP) environments

Best practices: Physical database design for online transaction processing (OLTP) environments

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

 

DB2 v9.1 FP5 or later probelm on LDAP 6.1

IBM – IO09826: Full support for DB2 v9.1 FP5 or later

10 Tips for Success at Review Time for DBA

DBTA January 2009 – Trends and Applications: DBAs: Get a Raise! 10 Tips for Success at Review Time

DBAs: Get a Raise!
10 Tips for Success at Review Tim

On Becoming a DBA

Data Management Today by Craig Mullins : On Becoming a DBA

Blog at WordPress.com.

Up ↑