DB2 Command Daquan

Check archiving processing

View Log Archive status

DB2 "Select Date (CAST (start_time as TIMESTAMP)) as DATE,

COUNT (*) as Number_of_logs_per_day,

(Count (*) *23.4375) as AMOUNT_LOGS_DAY_MB,

Dbpartitionnum as Dbpart

From Sysibmadm. Db_history

WHERE operation = ' X '--Archive logs

and Operationtype = ' 1 '--1 = First Log archive method

and TIMESTAMP (end_time) > Current_timestamp-10 days

GROUP by DATE (CAST (start_time as TIMESTAMP)), Dbpartitionnum

ORDER by DATE DESC "

See if backups have been made in the last 24 hours

[Email protected] ~]$]db2 "select substr (comment,1,30) as comment, timestamp (start_time) as start_time, timestamp (end_ Time) as End_time, substr (firstlog,1,25) as Firstlog, substr (lastlog,1,25) as Lastlog, Seqnum, substr (location,1,50) as Lo cation from sysibmadm.db_history where operation = ' B ' and timestamp (start_time) > current_timestamp-24 hours and SQL Code is NULL "

Dprop Check

Capture side:

DB2 "Select Synchtime, Current TIMESTAMP as Current_timestamp from ASN. Ibmsnap_register WHERE global_record= ' Y ' with ur "

Apply side:

DB2 "Select Apply_qual, Set_name, Source_alias, Target_alias, ACTIVATE, STATUS, Lastrun, lastsuccess, Synchtime, Sleep_mi Nutes,refresh_type from ASN. Ibmsnap_subs_set "

# # # View HADR Environment

$ db2pd-alldbs-hadr

Db2pd-db Sfa-hadr

# # # # # # # on Server side query node usage space

Select Node_name,cast (FLOAT (SUM (PHYSICAL_MB))/1024/1024 as DEC (8,2)) as "Space in TB", SUM (Num_files) as "number of files "From occupancy GROUP by Node_name ORDER by" Space in TB "DESC

DB2 is not the same as Oracle, the user is created by the operating system

And the user does not have the so-called default tablespace, Default temp table space, etc.

The default tablespace for the entire database is the default table space created by the database, Usertablespace

No view is specifically logged for all users, but there is a sysibmadm.privileges record all user rights

So you can think of it as a special record of the user's view.

Db2move when exporting data, you can db2move export

However, there are still some problems when setting the export character set.

At this point, export db2codepage=1208 can be set at the user's environment variable level to make it effective.

# # # Get Root permissions

[Email protected]:/> sudo-l|grep-i Auto

YANGMSU ' s Password:

(root)/usr/opt/db2*/instance*/, (Root)/usr/opt/db2*/adm/*, (Root)/usr/opt/db2*/bin/*, (Root)/tmp/auto_ Db2install.sh,

(root)/usr/opt/db2*/instance*/, (Root)/usr/opt/db2*/adm/*, (Root)/usr/opt/db2*/bin/*, (Root)/tmp/auto_ Db2install.sh,

[Email protected]:/>

[Email protected]:/tempdb2> sudo/tmp/auto_db2install.sh # # Then we have root priviledges

b03zeddbc002i:~ #

b03zeddbc002i:~ # ID

Uid=0 (Root) gid=0 (root) groups=0 (root), (PKCS11)

b03zeddbc002i:~ #

There are 2 user temporary tablespace in DB2, which is used by default in database? Can you change it?

1) If they is different page size, use the one with larger bufferpool (actually there is more logic behind there, but us Ually The result would be pointing to the one with larger bufferpool)

2) if they is with same page size, it's round-robin then

query Table Size

DB2 "SELECT SUBSTR (tabschema,1,15), TabName, Tabtype, (data_object_p_size + index_object_p_size + long_object_p_size + Lo B_object_p_size + xml_object_p_size)/1024 as TOTAL_P_SIZE_MB from Sysibmadm.admintabinfo ORDER by total_p_size_mb Desc "& Gt;table_size.log

DB2 "SELECT SUBSTR (tabschema,1,15), TabName, Tabtype, (data_object_p_size + index_object_p_size + long_object_p_size + Lo B_object_p_size + xml_object_p_size)/1024 as TOTAL_P_SIZE_MB from sysibmadm.admintabinfo where tabname = ' T_CHANGE_ DETAIL ' "

View database size, unit byte

1. DB2 "Call Get_dbsize_info (?,?,?, 0)"

2. Query database size by table Snaphot_tbs_cfg

DB2 "SELECT (SUM (total_pages))/1024.0/1024 total_allocated_space_in_gb from table (snapshot_tbs_cfg (' dbname ',-1)) Tbs_spce "

To view the table size:

DB2 "SELECT SUBSTR (tabschema,1,15), TabName, Tabtype, (data_object_p_size + index_object_p_size + long_object_p_size + Lo B_object_p_size + xml_object_p_size)/1024 as TOTAL_P_SIZE_MB from Sysibmadm.admintabinfo ORDER by total_p_size_mb Desc "& Gt;table_size.log

DB2 Execute SQL Script

DB2-TVF SQL file name

Du-sg

Du-amx| SORT-NR |more Linux under File System file size

lsof command

grep two fields/can also grep more than two fields

= DB2 List DB Directory | Grep-e "Alias|type"

Query the contents of the stored procedure:

DB2 "Select Procname,text from syscat.procedures where procname = ' sp_a_tskres '"

Team function ID

[Email protected]

View the current database name

DB2 "Select Current server from Sysibm.sysdummy1"

Method of substitution in VI editor

:%s/vivian/sky/g (equivalent to: g/vivian/s//sky/g) replaces all Vivian in each row as Sky

:%s/^/#/g &&:%s/^#//g Disable crontab You can take this method to replace the beginning of the line

grep under standard Unix/linux controls the context with the following parameters

Grep-c 5 foo file shows the line with the Foo string and the upper and lower 5 lines in the file

Grep-b 5 foo file shows Foo and the first 5 rows

Grep-a 5 foo file shows Foo and the following 5 rows

View User Permissions

DB2 "SELECT * from Syscat.dbauth where grantee= ' Chubala '"

DB2 "SELECT * from Syscat.tabauth where grantee= ' Chubala '"

DB2 "SELECT SUBSTR (grantor,1,30), Grantortype, substr (grantee,1,30), Granteetype, substr (tabschema,1,30), substr ( tabname,1,30), Controlauth, Alterauth, Deleteauth, Indexauth, Insertauth, Refauth, Selectauth, UPDATEAUTH from Syscat.tabauth where grantee= ' AIXDBA3 ' "

DB2 "SELECT SUBSTR (grantor,1,30), Grantortype, substr (grantee,1,30), Granteetype, substr (tabschema,1,30), substr ( tabname,1,30), Controlauth from Syscat.tabauth where grantee= ' AIXDBA3 ' "

View all users under AIX

cat/etc/passwd

View all group under AIX

Cat/etc/group

View DB2 diagnostic logs

507 DB2 get dbm cfg|grep-i diag

508 cd/db/lsinst/db2diag/

509 LS-ALRT

510 Tail Db2diag.log

Db2diag-time 2013-08-21-21.00|more

DB2 "? sql-514 "# # Error

DB2 "? sql0805n "

Transfer backup set to TSM (IBM Tivoli Storage Manager)

The backup file for the query TSM does not exist and must be in the same directory as the backup file, which has been tested:

= = DSMC Query backup file name

DSMC Query Backup/db2inst5/backup/smiwsla.0.db2inst5.node0000.catn0000.20130824231022.001-ina

Nohup DSMC Restore/db2inst5/backup/smiwsla.0.db2inst5.node0000.catn0000.20130824231022.001-ina &

= DSMC Incremental/db/inst2/db2backup/tmp.adsm.lpdb.output

= = DSMC DELETE backup/db2inst5/backup/smiwsla.0.db2inst5.node0000.catn0000.20130629121219.001-deltype=inactive

Oslevel-s

Db2level

There is an ITM process that shows that Tivoli is running

# # # DB2 's archive does not use DSMC to do active log backup but to invoke the TSM API

# # # Query and extract, detailed reference infocenter db2adutl command

Db2adutl query logs between S0002650 and S0002650 DB Blogs

Db2adutl extract logs between S0002650 and S0002655 DB Blogs # # extract to current directory

Db2adutl upload logs between S0168147 and S0168147 DB Blogs

$HOME/SQLLIB/ADSM/DSMAPIPW

TSM Client Reset Password

# # Check the server information for TSM's client configuration

Cat/usr/tivoli/tsm/client/api/bin64/dsm.opt

Cat/usr/tivoli/tsm/client/api/bin64/dsm.sys

Cat/usr/tivoli/tsm/client/ba/bin/dsm.sys

[Email protected]:/db/db2data/db2inst5/db2inst5/node0000> env|grep DSM

Dsmi_config=/usr/tivoli/tsm/client/api/bin64/dsm.opt

Dsm_log=/db2inst5/sqllib/db2dump

Dsmi_log=/db2inst5/sqllib/db2dump

Dsm_dir=/usr/tivoli/tsm/client/api/bin64

Dsm_config=/usr/tivoli/tsm/client/api/bin64/dsm.opt

Dsmi_dir=/usr/tivoli/tsm/client/api/bin64

DB2 catalog TCPIP Node nodewin1 remote 9.1.39.85 server 50000

DB2 catalog Database sample (as Alias) at node Nodewin1

DB2 Uncatalog DB Database Alias

DB2 Uncatalog Nodes node name

###############################################################

################################################################

By ID owner to determine whether the server is maintained by the team, the following ID is the team

################################################################

################################################################

View the largest file in the current directory

DU-AMX |sort-nr|more

################################################################

1. We can delete the ^m in the VI editor:

Switch vi to command line mode, input (note input This ^m, this is not shift+^ plus M, should be Ctrl + V plus ctrl+m)

:%s/^m//g--The command copy is invalid, note the input of ^m

Execute the above command in the VI command line mode to remove all ^m at the end of the line

2. You can use the Dos2unix command

Dos2unix filename

################################################################

Extended table Space Steps

################################################################

View Table Space usage

View Table Space usage

DB2 "SELECT TBSP_ID,SUBSTR (tbsp_name,1,30) as Tbsp_name, Tbsp_type, tbsp_total_size_kb/1024 as Total_m,tbsp_used_size_ kb/1024 as used_m,tbsp_utilization_percent as Pct, Tbsp_using_auto_storage, tbsp_auto_resize_enabled from SYSIBMADM. Tbsp_utilization "|grep clobs_tsp

View Table Space Properties (whether auto-expanding, see Ar/auto resize field)

Db2pd-db Pmor11-tablespace 5

See how much capacity expansion to target usage needs to scale

DB2 "SELECT SUBSTR (tbsp_name,1,30) as Tbsp_name,round ((tbsp_used_size_kb/1024/0.75), 0) as After_extend_m,tbsp_used_ size_kb/1024/0.75-tbsp_total_size_kb/1024 should_extend_m from Sysibmadm. tbsp_utilization where tbsp_name= ' clobs_tsp ' "

To see if the target server is a highly available schema

Db2pd-db Cwamapp-hadr

To see if a backup recovery task is in progress

DB2 List Utilities Show Detail

Extending table Space

Alter Tablespace CLOBS_TSP extend (all 30M)

HADR Basic Maintenance operations

1. Start-stop operation of HADR

From HADR

Preparation:

DB2 Deactivate Database sample

DB2 start HADR on database sample as Standby

Main:

DB2 Deactivate Database sample

DB2 start HADR on database sample as Primary

# #启动完成后, check hadr status

1. DB2 get snapshot for DB on sample

2. Db2pd-db Sample-hadr

Stop HADR

Main:

DB2 Deactivate Database sample

DB2 stop HADR on database sample

Preparation:

DB2 Deactivate database sample/[[email protected] logtarget]$ DB2 deactivate database sample user Db2inst1 using Db2inst1

DB2 stop HADR on database sample

# # # Sometimes deactivate database does not add user name password, when the stop Hadr will error

[[Email protected] logtarget]$ DB2 stop HADR on database sample

sql1769n Stop HADR cannot complete. Reason code = "2".

[Email protected] logtarget]$

2. Perform a database takeover on the standby server

DB2 takeover HADR on database sample

###################################################################

AIX Group:nus_w_dnaheaix

[Email protected]

Tivoli monitoring Team (NUS_N_SSEASMI)

Tivoli Database Support Team (NUS_N_ASTIVDB)

Linux Team Group:nus_n_linux

OPS funcation Id:[email protected]

CC: [Email protected],[email protected],[email protected]

7:01:37 Pm:wei SHI:BCC: [email protected], SUZHOU DBA

NUS_W_SSAHEDBA for AHE DBA team

NUS_N_SSUDBDBA for OOP DBA team

Nus_n_ssbrconn for Connect Team

Nus_w_dnaheaix

Nus_w_sstivoli Tivoli

Nus_n_

3:30:54 PM: [email protected]-Kimi M YANG/CHINA/IBM:TSM:

[Email protected] Nus_n_adsm

[Email protected] NUS_N_UDTSM, I-msv-us-brbld

DB2 Command Daquan