Search

Ramchandra's Weblog

Just ……everything about DB2 LUW

DB2 V10 Highlights

http://www-05.ibm.com/de/events/im-forum/pdf/HummelGonzales_Breakout_1_DB2-10_Highlights.pdf

SSH tunneling/ port forwarding

This is one of the powerful feature which one can use, when there is no more ports open to a server other then ssh port ie port 22.

Let say i need to access use MQ browser from my local client for browsing MQ objects on the server, and you have only ssh connection open to the server in other words we have only port 22 open to the server rest all the ports are blocked from the firewall.

let say MQ server is ruinning on port 4040 and the server ip is 10.10.40.40, you need to access these server via MQ browser, since port 4040 on the server will not be accessible from your local machine. to solve this issue we need any ssh client. let say we have putty (commonly used) or cywin.

we need to run any of these commands depending on  the ssh clinet you have.

putty:-

C:\Users\Administrator>C:\Users\Administrator\Desktop\putty.exe  -T mqm@10.10.11.97 -L 127.0.0.1:4040:10.10.40.40:4040

cywin:-

“C:\cygwin\bin\ssh” -T mqm@10.10.11.97 -L 127.0.0.1:4040:10.10.40.40:4040

 

it will promt for the password, once you enter the password, the port will be available on your local machine , as if the service is hosted on your local machine.

SSH tunneling/ port forwarding

This is one of the powerful feature which one can use, when there is no more ports open to a server other then ssh port ie port 22.

Let say i need to access use MQ browser from my local clinet for browsing MQ objects on the server, and you have only ssh connection open to the server in other words we have only port 22 open to the server rest all the ports are blocked from the firewall.

let say MQ server is ruinning on port 4040 and the server ip is 10.10.40.40, you need to access these server via MQ browser, since port 4040 on the server will not be accessible from your local machine. to solve this issue we need any ssh client. let say we have putty (commonly used) or cywin.

we need to run any of these commands depending on  the ssh clinet you have.

putty:-

C:\Users\Administrator>C:\Users\Administrator\Desktop\putty.exe  -T mqm@10.10.11.97 -L 127.0.0.1:4040:10.10.40.40:4040

cywin:-

“C:\cygwin\bin\ssh” -T mqm@10.10.11.97 -L 127.0.0.1:4040:10.10.40.40:4040

 

it will promt for the password, once you enter the password, the port will be available on your local machine , as if the service is hosted on your local machine.

Script to Reset Identity Columns

db2 connect to <database>
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
db2 “select ‘select ”ALTER TABLE ‘|| rtrim(TBCREATOR) ||’.’||TBNAME ||’ ALTER COLUMN ‘||NAME ||’ RESTART WITH ”’ ||’||cast( max(‘||NAME||’)+1 as char(15)) ||”;” from ‘|| rtrim(TBCREATOR) ||’.’||TBNAME ||’;’ from   SYSIBM.SYSCOLUMNS     where IDENTITY=’Y'” > temp_identity1.sql
db2 -tvf temp_identity1.sql|grep ‘ALTER TABLE’  >  temp_identity2.sql
cat temp_identity2.sql|grep ‘;’$ > temp_identity3.sql
db2 -tvf temp_identity3.sql > temp_identity4.sql
db2 UNQUIESCE DATABASE

SQL Server DBA database management checklist

SQL Server DBA database management checklist

SQL Server Best Practices

SQL Server – Best Practices

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

 

Blog at WordPress.com.

Up ↑