db2 "select substr(tabname,1,20) as TABLE, alter_time from syscat.tables where tabname='<table_name>'"
db2 "select substr(tabname,1,20) as TABLE, invalidate_time from syscat.tables where tabname='<table_name>'"
db2 " select TABSCHEMA, TABNAME from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y'"
db2 -x "select 'reorg table', substr(rtrim(TABSCHEMA)||'.'||rtrim(TABNAME),1,20), ';' \ from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y'"
db2 "select substr(a.tabname,1,30), (a.fpages*PAGESIZE/1024) as size_k, a.card from syscat.tables a, \ syscat.tablespaces b where a.TBSPACEID=b.TBSPACEID and a.tabschema='<schema_name>"
db2 "SELECT tabname,TABSCHEMA, SUM(DATA_OBJECT_P_SIZE)+ SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+ SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM SYSIBMADM.ADMINTABINFO where TABSCHEMA=<tabschema_name> group by tabname,tabschema"
db2 "select substr(a.tabname,1,30), (a.fpages*PAGESIZE/1024) as size_k, a.card from syscat.tables a, syscat.tablespaces b where a.TBSPACEID=b.TBSPACEID and a.tabname='<tab_name>' and tabschema='<schema_name>' "
db2 "SELECT TABSCHEMA, TABNAME, SUM(DATA_OBJECT_P_SIZE)+ SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+ SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM SYSIBMADM.ADMINTABINFO where tabschema='<tabschema_name>' and tabname='<table_name>' group by tabschema,tabname"
db2 "select sum(a.fpages*PAGESIZE/1024) as size_k_of_schemaName from syscat.tables a, \ syscat.tablespaces b where a.TBSPACEID=b.TBSPACEID and a.tabschema='<schema_name>' group by a.tabschema"
db2 "SELECT TABSCHEMA, SUM(DATA_OBJECT_P_SIZE)+ SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+ SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM SYSIBMADM.ADMINTABINFO GROUP BY TABSCHEMA"
db2 "with rs as (select distinct(substr(t.tabname,1,35)) as table, t.card as card, \ t.type as type , t.fpages as fpages, case when ind.nleaf is null then 0 else \ ind.nleaf end as nleaf, ts.pagesize as pagesize, t.tabname tab2, t.tabschema as schema \ from syscat.tables t inner join syscat.tablespaces ts on t.tbspaceid=ts.tbspaceid \ left join syscat.indexes ind on t.tabname=ind.tabname) select table,\ (sum(nleaf)+fpages)*pagesize as size,card from rs where schema='<schema_name>' and \ type='T' and table='<table_name>' group by card,table,pagesize,fpages"
db2 "with rs as (select distinct(substr(t.tabname,1,35)) as table, t.card as card, t.type as type , \ t.fpages as fpages, case when ind.nleaf is null then 0 else ind.nleaf end as nleaf, \ ts.pagesize as pagesize, t.tabname tab2, t.tabschema as schema from \ syscat.tables t inner join syscat.tablespaces ts on t.tbspaceid=ts.tbspaceid left \ join syscat.indexes ind on t.tabname=ind.tabname) select table,(sum(nleaf)+fpages)*pagesize as size,\ card from rs where schema='<schema_name>' and type='T' group by card,table,pagesize,fpages"
db2 "select ((select sum(fpages*pagesize) from syscat.tablespaces as a, syscat.tables as b where \ a.TBSPACEID=b.TBSPACEID and tabschema='<schema_name>' group by tabschema)+(select sum(nleaf*pagesize) \ from syscat.tablespaces as a, syscat.indexes as b where a.TBSPACEID=b.TBSPACEID and \ tabschema='<schema_name>' group by tabschema))/1024 as SCHEMA_SIZE_KB from sysibm.sysdummy1"
db2 "select sum(TBSP_USED_SIZE_KB) as DATABASE_SIZE from sysibmadm.TBSP_UTILIZATION"
db2 force applications all db2stop cd DB2DIR//das/bin$ ./db2admin stop db2_kill ps -ef | grep -i <instance_name> #If there is any DB2 process kill <PID of DB2 process>
./installFixPack -b DB2DIR -f db2lib -f update
./installFixPack -f db2lib -f update -b /opt/ibm/db2/V9.5/
db2 force applications all db2 terminate db2stop db2licd -end # run at each physical partition . $HOME/das/dasprofile db2admin stop
ps -ef
db2fmcu -d
db2fm -i iname -D
$HOME/sqllib/bin/ipclean
./installFixPack -b DB2DIRwhere DB2DIR is the location of the DB2 products that you want to update.
db2start
db2 terminate db2 CONNECT TO dbname db2 BIND path\db2schema.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE db2 BIND path\@db2ubind.lst BLOCKING ALL GRANT PUBLIC ACTION ADD db2 BIND path\@db2cli.lst BLOCKING ALL GRANT PUBLIC ACTION ADD db2 terminate
db2 +c alter table <table_name> activate not logged initially
db2 +c delete from <table_name> where <column_name>='<value>' OR db2 +c insert into table <table_name> (select * from table_name) OR db2 +c update table_name set <column_name>='value' where <column_name>='value'
db2 commit
db2 alter table <table_name> activate not logged initially
db2 delete from <table_name> where <column_name>='<pattern>' OR db2 insert into table <table_name> (select * from table_name) OR db2 update table_name set <column_name>='value' where <column_name>='value'
db2 commit
SQL0289N Unable to allocate new pages in table space "<tablespace name>"
2008-03-13-17.24.13.390000-180 I890568H416 LEVEL: Error PID : 2760 TID : 4408 PROC : db2syscs.exe INSTANCE: DB2 NODE : 000 DB : SAMPLE APPHDL : 0-853 APPID: *LOCAL.DB2.080313201909 FUNCTION: DB2 UDB, Common Trace API, sqlbfix, probe:2 RETCODE :ZRC=0x85020021=-2063466463=SQLB_END_OF_CONTAINER "DMS Container space full"
db2 "list tablespace containers show detail"To check if your ran out of space on your file system you could issue the command on unix, then look for occupation indicators:
df -k
ALTER TABLESPACE <TS_NAME> ADD (FILE '<path>' <number_of_pages>)
ALTER TABLESPACE <TS_NAME> ADD (FILE '<path>' <number_of_pages>)
db2 "select substr(tbsp_name,1,30) as Tablespace_Name, tbsp_type as Type, \ substr(tbsp_state,1,20) as Status, (tbsp_total_size_kb / 1024 ) as Size_Meg, \ smallint((float(tbsp_free_size_kb)/ float(tbsp_total_size_kb))*100)as Percent_Free_Space, \ int((tbsp_free_size_kb) / 1024 )as Meg_Free_Space from sysibmadm.tbsp_utilization"
db2 "select substr(tbsp_name,1,30) as Tablespace_Name, tbsp_type as Type, \ substr(tbsp_state,1,20) as Status, (tbsp_total_size_kb / 1024 ) as Size_Meg, \ decimal((float(tbsp_total_size_kb - tbsp_free_size_kb)/ float(tbsp_total_size_kb))*100,3,1)as Percent_used_Space, \ int((tbsp_free_size_kb) / 1024 )as Meg_Free_Space from sysibmadm.tbsp_utilization where tbsp_type='DMS'"
Output
TABLESPACE_NAME TYPE STATUS SIZE_MEG PERCENT_USED_SPACE MEG_FREE_SPACE
------------------------------------------ ---------- -------------------- ----------- -------------------- ---------------------------------- ----------------------------------
SYSCATSPACE DMS NORMAL 64 61.1 24
USERSPACE1 DMS NORMAL 32 53.1 15
TBS32K DMS NORMAL 69996 77.8 15507
IDX_TBS DMS NORMAL 33619 99.0 315
SYSTOOLSPACE DMS NORMAL 32 0.2 31
db2 connect to <db_name>
db2 select "TABLESPACE_ID as id, SUBSTR(TABLESPACE_NAME, 1, 20) as name, \
INT(PAGE_SIZE) as PAGE_SIZE, INT(TOTAL_PAGES) as TOTAL_PAGES, \
INT(USED_PAGES) as USED_PAGES, \
INT(DECIMAL(TOTAL_PAGES,10,2)-DECIMAL(USED_PAGES,10,2)) as FREE_PAGES,\
' ' CONCAT (SUBSTR(CHAR(DECIMAL(USED_PAGES, 10, 2)/ \
DECIMAL(TOTAL_PAGES,10,2)*100),9,5)) CONCAT '%' as PERCENT_USED, \
INT(NUM_CONTAINERS) as N_CONTAINERS \
from table (snapshot_tbs_cfg('<db_name>', 0)) as t \
where TABLESPACE_TYPE=0 order by PERCENT_USED desc"
Output example:NAME PAGE_SIZE TOTAL_PAGES USED_PAGES FREE_PAGES PERCENT_USED N_CONTAINERS ---------- ----------- ---------- ---------- ---------- ------------ ------------ TS_INDEXES 4096 111542 80160 31382 71.86% 3 TS_EVT_REP 16384 19000 7104 11896 37.38% 2 TS_SLOTS 16384 64800 21920 42880 33.82% 3 TS_REC_LOG 16384 12800 2816 9984 22.00% 4 TS_LONG 32768 1280 110 1170 08.59% 3 TS_REST 4096 4500 288 4212 06.40% 3
db2 select "SUBSTR(TABLESPACE_NAME, 1, 20) as name, \
INT(PAGE_SIZE) as PAGE_SIZE, INT(TOTAL_PAGES) as TOTAL_PAGES, \
INT(USED_PAGES) as USED_PAGES, \
INT(DECIMAL(TOTAL_PAGES,10,2)-DECIMAL(USED_PAGES,10,2)) as FREE_PAGES,\
' ' CONCAT (SUBSTR(CHAR(DECIMAL(USED_PAGES, 10, 2)/ \
DECIMAL(TOTAL_PAGES,10,2)*100),9,5)) CONCAT '%' as PERCENT_USED, \
INT(NUM_CONTAINERS) as N_CONTAINERS \
from table (snapshot_tbs_cfg('<db_name>', 0)) as t \
where TABLESPACE_TYPE=0 and TABLESPACE_NAME='TS_INDEXES'"
NAME PAGE_SIZE TOTAL_PAGES USED_PAGES FREE_PAGES PERCENT_USED N_CONTAINERS ---------- ----------- ---------- ---------- ---------- ------------ ------------ TS_INDEXES 4096 111542 80160 31382 71.86% 3 1 record(s) selected.
SELECT *
FROM EMPLOYEE
WHERE DEPTNO IN ('D11', 'D21', 'E21')
could be written as:
SELECT *
FROM EMPLOYEE, (VALUES 'D11', 'D21', 'E21) AS V(DNO)
WHERE DEPTNO = V.DNO
db2 list tablespaces show detailIt will shows you a list with all tablespaces and you can identify the specific tablespace with issues.
db2 list tablespaces show detail Tablespaces for Current Database Tablespace ID = 0 Name = SYSCATSPACE Type = Database managed space Contents = All permanent data. Regular table space. State = 0x0000 Detailed explanation: Normal Total pages = 8192 Useable pages = 8188 Used pages = 7736 Free pages = 452 High water mark (pages) = 7736 Page size (bytes) = 8192 Extent size (pages) = 4 Prefetch size (pages) = 4 Number of containers = 1 Tablespace ID = 1 Name = TEMPSPACE1 Type = System managed space Contents = System Temporary data State = 0x0000 Detailed explanation: Normal Total pages = 1 Useable pages = 1 Used pages = 1 Free pages = Not applicable High water mark (pages) = Not applicable Page size (bytes) = 8192 Extent size (pages) = 32 Prefetch size (pages) = 32 Number of containers = 1 Tablespace ID = 2 Name = USERSPACE1 Type = Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal Total pages = 4096 Useable pages = 4064 Used pages = 1760 Free pages = 2304 High water mark (pages) = 1824 Page size (bytes) = 8192 Extent size (pages) = 32 Prefetch size (pages) = 32 Number of containers = 1 Tablespace ID = 3 Name = IBMDB2SAMPLEREL Type = Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal Total pages = 4096 Useable pages = 4064 Used pages = 608 Free pages = 3456 High water mark (pages) = 608 Page size (bytes) = 8192 Extent size (pages) = 32 Prefetch size (pages) = 32 Number of containers = 1
db2 list tablespace containers for <tablespace_id> show detailNow you can identify the total number of containers, where the tablespace is stored, the total number of pages and the total number of used pages.
db2 list tablespace containers for 3 show detail Tablespace Containers for Tablespace 3 Container ID = 0 Name = C:\DB2\NODE0000\SAMPLE\T0000003\C0000000.LRG Type = File Total pages = 4096 Useable pages = 4064 Accessible = Yes
df -k <tablespace.path>And you'll be able to verify if it's enough space to extend the tablespace. Remember you can check the page size in the output of "db2 list tablespaces show detail" command.
db2 "alter tablespace <tablespace_name> extend (all <page_numbers>)"This will extend the size of your tablespace. Be careful when using EXTEND ALL clause when the tablespace has more than one container; in cases where the tablespace has more than one container, divide the total number of pages you intent to increase by the number of total containers.
db2 alter "tablespace IBMDB2SAMPLEREL extend (all 1000)"Alternatively, if it's not possible to extend the actual containers, a possibility is to add new containers to the tablespace. For that, you should enter the following command:
db2 "alter tablespace <tablespace_name> add (FILE '<new_container_file>' <size>)"Where size can be in pages (only using the numbers you want), in KBytes (using a K following the number), in MBytes (using a M following the number) or in GBytes (using a G following the number). Also, please note to use single quotes around the name of the file you'll use as a new container.
db2 "select FIRSTNME from db2inst1.employee "
db2 "select FIRSTNME from db2inst1.employee where upper(FIRSTNME) like 'EVA' "
db2pd utility is generally used for problem
determination and monitoring purposes, but
you can also use it to query information about database activity.
One piece of information that you can obtain with the db2pd utility is the number of times indexes have been accessed since
the database was activated.
Listing 7 shows how to use the db2pd utility to get this
information for all indexes on all tables in a database.db2pd utility
db2pd -db sample -tcbstats all -file db2pd_tab_all.txt |
db2pd utility have the following
meanings:-db Database name-tcbstats all Show all table and index metrics-file Output filedb2pd utility to show metrics for
only a certain table and its indexes, use the -tcbstats
option to specify the tablespace ID and table ID.
To do this, you first need to determine the tablespace ID and table ID of the table
by executing a SELECT statement on the SYSCAT.TABLES catalog view, as shown
in Listing 8.
db2 "SELECT TBSPACEID, TABLEID
FROM SYSCAT.TABLES
WHERE TABSCHEMA = 'FECHNER' AND TABNAME = 'DEPARTMENT'"
|
TBSPACEID TABLEID
--------- -------
2 5
1 record(s) selected.
|
db2pd output to just that table.
You do this by including the tbspaceid and tableid suboptions following
the -tcbstats option, as shown in Listing 10.db2pd -tcbstats output to a
specific table
db2pd -db sample -tcbstats all tbspaceid=2 tableid=5 -file db2pd_tab_dept.txt |
db2pd query such as the
one above.db2pd utility to DB2 9.7
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:20:34 TCB Table Information: Address TbspaceID TableID PartID MasterTbs MasterTab TableName SchemaNm ObjClass DataSize LfSize LobSize XMLSize 0x797DF2B8 2 5 n/a 2 5 DEPARTMENT FECHNER Perm 1 0 0 0 TCB Table Stats: Address TableName SchemaNm Scans UDI RTSUDI PgReorgs NoChgUpdts Reads FscrUpdates Inserts Updates Deletes OvFlReads OvFlCrtes RowsComp RowsUncomp CCLogReads StoreBytes BytesSaved 0x797DF2B8 DEPARTMENT FECHNER 0 0 0 0 0 951 0 0 0 0 0 0 0 0 0 - - TCB Index Information: Address InxTbspace ObjectID PartID TbspaceID TableID MasterTbs MasterTab TableName SchemaNm IID IndexObjSize 0x797E0330 2 5 n/a 2 5 2 5 DEPARTMENT FECHNER 3 8 0x797E0330 2 5 n/a 2 5 2 5 DEPARTMENT FECHNER 2 8 0x797E0330 2 5 n/a 2 5 2 5 DEPARTMENT FECHNER 1 8 TCB Index Stats: Address TableName IID PartID EmpPgDel RootSplits BndrySplts PseuEmptPg EmPgMkdUsd Scans IxOnlyScns KeyUpdates InclUpdats NonBndSpts PgAllocs Merges PseuDels DelClean IntNodSpl 0x797E0330 DEPARTMENT 3 n/a 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0x797E0330 DEPARTMENT 2 n/a 0 0 0 0 0 678 0 0 0 0 1 0 0 0 0 0x797E0330 DEPARTMENT 1 n/a 0 0 0 0 0 245 0 0 0 0 1 0 0 0 0 |
SELECT statement, as shown in Listing 12.
db2 "SELECT INDSCHEMA, INDNAME
FROM SYSCAT.INDEXES
WHERE TABSCHEMA = 'FECHNER' AND TABNAME = 'DEPARTMENT' AND IID = 3"
|
SELECT statement is shown in Listing 13.
INDSCHEMA INDNAME ------------------------------ ------------------------------ FECHNER XDEPT3 1 record(s) selected. |
db2 describe indexes for table FECHNER.DEPARTMENT show detail |
db2pd utility
results has a IxOnlyScns column
that shows the number of index only scans.
An Index only scan is an index access that is not followed by a table access because
the index itself contains all the requested data.
So if the IxOnlyScns counter is not zero, you have to add the numbers of both the
Scans column and the IxOnlyScns column to get the total number of times the index has
been accessed.db2pd approach.
The fact that a certain index has not been used
up until a certain point in time,
does not necessarily mean that the index will never be used.
So be cautious when deciding whether or not to drop an index that appears not to be used.
If you do decide to drop an index, you should first save its CREATE
INDEX statement so that you can easily recreate it if necessary.db2pd utility to DB2 9.1 and 9.5
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:20:34 Database Partition -1 -- Database MNTTBPP -- Active -- Up 8 days 22:53:29 TCB Table Information: Address TbspaceID TableID PartID MasterTbs MasterTab TableName SchemaNm ObjClass DataSize LfSize LobSize XMLSize 0x0000000134C8E6F8 2 17 n/a 2 17 TMPVALORITEMCOLETA MNT Perm 4300 0 0 0 TCB Table Stats: Address TableName Scans UDI PgReorgs NoChgUpdts Reads FscrUpdates Inserts Updates Deletes OvFlReads OvFlCrtes 0x0000000134C8E6F8 TMPVALORITEMCOLETA 389 7666 0 0 4361401580 0 5086480 0 0 0 0 TCB Index Information: Address InxTbspace ObjectID TbspaceID TableID MasterTbs MasterTab TableName SchemaNm IID IndexObjSize 0x0000000134F2F1C0 2 17 2 17 2 17 TMPVALORITEMCOLETA MNT 3 2092 0x0000000134F2F1C0 2 17 2 17 2 17 TMPVALORITEMCOLETA MNT 2 2092 0x0000000134F2F1C0 2 17 2 17 2 17 TMPVALORITEMCOLETA MNT 1 2092 TCB Index Stats: Address TableName IID EmpPgDel RootSplits BndrySplts PseuEmptPg Scans KeyUpdates InclUpdats NonBndSpts PgAllocs Merges PseuDels DelClean IntNodSpl 0x0000000134F2F1C0 TMPVALORITEMCOLETA 3 0 0 20 0 501 0 0 68 0 0 0 0 1 0x0000000134F2F1C0 TMPVALORITEMCOLETA 2 0 1 0 0 1071 0 0 176 136 0 0 0 0 0x0000000134F2F1C0 TMPVALORITEMCOLETA 1 0 2 268 0 0 0 0 0 207 0 0 |
db2 "CREATE TABLE TABLE_X( COD INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, NAME CHAR(30))"
db2 "insert into table_x (name) values ('Rose')"
db2 'insert into table_x (name) values ('Mary')"
db2 "insert into table_x (name) values ('Alyson')"
db2 "select IDENTITY_VAL_LOCAL() as last_cod from sysibm.sysdummy1"OUTPUT
LAST_COD
---------------------------------
3.
1 record(s) selected.
| Monitor level | CLP command | Information returned |
|---|---|---|
| Connections list | list applications [show detail] | Application identification information for all applications currently connected to a database that is managed by the DB2(R) instance on the partition where snapshot is taken. |
| Connections list | list applications for database dbname[show detail] | Application identification information for each application currently connected to the specified database. |
| Connections list | list dcs applications | Application identification information for all DCS applications currently connected to a database that is managed by the DB2 instance on the partition where snapshot is taken. |
| Database manager | get snapshot for dbm |
Database manager level information, including instance-level monitor switch settings. |
| Database manager | get dbm monitor switches | Instance-level monitor switch settings. |
| Database | get snapshot for database on dbname | Database level information and counters for a database. Information
is returned only if there is at least one application connected to the
database. |
| Database | get snapshot for all databases | Database level information and counters for each database active on the partition. Information is returned only if there is at least one application connected to the database. |
| Database | list active databases | The number of connections to each active database. Includes databases that were started using the ACTIVATE DATABASE command, but have no connections. |
| Database | get snapshot for dcs database on dbname | Database level information and counters for a specific DCS database. Information is returned only if there is at least one application connected to the database. |
| Database | get snapshot for remote database on dbname | Database level information and counters for a specific federated system database. Information is returned only if there is at least one application connected to the database. |
| Database | get snapshot for all remote databases | Database level information and counters for each active federated system database on the partition. Information is returned only if there is at least one application connected to the database. |
| Application | get snapshot for application applid appl-id | Application level information, including cumulative counters, status information, and most recent SQL statement executed (if statement switch is set). |
| Application | get snapshot for application agentid appl-handle | Application level information, includes cumulative counters, status information, and most recent SQL statement executed (if statement switch is set). |
| Application | get snapshot for applications on dbname | Application level information for each application that is connected
to the database on the partition. This includes cumulative counters,
status information, and most recent SQL statement executed (if statement
switch is set). |
| Application | get snapshot for all applications | Application level information for each application that is active on the partition. This includes cumulative counters, status information, and most recent SQL statement executed (if statement switch is set). |
| Application | get snapshot for dcs application applid appl-id | Application level information, including cumulative counters, status information, and most recent SQL statement executed (if statement switch is set). |
| Application | get snapshot for all dcs applications | Application level information for each DCS application that is active on the partition. This includes cumulative counters, status information, and most recent SQL statement executed (if statement switch is set). |
| Application | get snapshot for dcs application agentid appl-handle | Application level information, including cumulative counters, status information, and most recent SQL statement executed (if statement switch is set). |
| Application | get snapshot for dcs applications on dbname | Application level information for each DCS application that is connected to the database on the partition. This includes cumulative counters, status information, and most recent SQL statement executed (if statement switch is set). |
| Application | get snapshot for remote applications on dbname |
Application level information, includes cumulative counters, status information, and most recent SQL statement executed (if statement switch is set). |
| Application | get snapshot for all remote applications |
Application level information for each federated system application that is active on the partition. This includes cumulative counters, status information, and most recent SQL statement executed (if statement switch is set). |
| Table | get snapshot for tables on dbname | Table activity information at the database and application level for each application connected to the database. Table activity information at the table level for each table that was accessed by an application connected to the database. Requires the table switch. |
| Lock | get snapshot for locks for application applid appl-id | List of locks held by the application. Lock wait information requires the lock switch. |
| Lock | get snapshot for locks for application agentid appl-handle | List of locks held by the application. Lock wait information requires the lock switch. |
| Lock | get snapshot for locks on dbname | Lock information at the database level, and application level for each application connected to the database. Requires the lock switch. |
| Table space | get snapshot for tablespaces on dbname | Information about table space activity for a database. Requires the
buffer pool switch. Also included is information on containers,
quiescers, and ranges. This information is not under switch control. |
| Buffer pool | get snapshot for all bufferpools | Buffer pool activity counters. Requires the buffer pool switch. |
| Buffer pool | get snapshot for bufferpools on dbname | Buffer pool activity counters for the specified database. Requires the buffer pool switch. |
| Dynamic SQL | get snapshot for dynamic sql on dbname | Point-in-time statement information from the SQL statement cache for the database. The information can also be from a remote data source. |
$db2 values current schema
$ db2 values current schema 1 ------------------------------------------- DB2INST2 1 record(s) selected.
db2 set schema <SchemaName>
create table table_identity (cod1 int not null generated always as identity, cod2 int, cod3 int);
select * from final table (insert into table_identity (cod2,cod3) values (20,20),(30,30));
db2pdcfg -db sample -flushbp
select substr(tabname,1,20) table_name,substr(constname,1,20) fk_name,substr(REFTABNAME,1,12) parent_table,substr(refkeyname,1,20) pk_orig_table,fk_colnames from syscat.references where tabname = 'TABLE_NAME'
db2look -d <database_name> -e -t <table_name>Look for 'DDL Statements for foreign keys on Table table_name in the output.
begin atomic declare x char; set x=(select nome from db2inst1.x0 where nome='a'); insert into db2inst1.x0 (nome) values (x); end@
begin atomic
insert into db2inst1.x0 (nome) values ('cd');
insert into db2inst1.x1 (cod) values (select identity_val_local() from sysibm.sysdummy1);
end@
db2 -td@ -vf <scriptname>
db2set DB2LINUXAIO=true
db2 "alter table <schema_name>.<tab_name> alter column <column_name> drop default"
db2 "alter table <schema_name>.<tab_name> alter column <column_name> drop not null"
CALL SYSPROC.ADMIN_DROP_SCHEMA('SCHNAME', NULL, 'ERRORSCHEMA', 'ERRORTABLE')
Replace 'SCHNAME' to schema name, example for DEPT schema :CALL SYSPROC.ADMIN_DROP_SCHEMA('DEPT', NULL, 'ERRORSCHEMA', 'ERRORTABLE');
db2 -x "select 'drop table ' || rtrim(tabschema) || '.' || rtrim(tabname) || ' '
|| ';'from syscat.tables where type = 'T' and tabschema='<schema_name>'"
db2 -x "select 'drop table ' || rtrim(tabschema) || '.' || rtrim(tabname) || ' '
|| ';'from syscat.tables where type = 'V' and
tabschema='<schema_name>'"
db2 -x "select 'drop table ' || rtrim(tabschema) || '.' ||
rtrim(tabname) || ' ' || ';'from syscat.tables where tabschema='<schema_name>'"
db2 set schema group1
db2 'alter table group1.employee drop constraint "key1"'
db2 list applications show detail
db2 force applications all db2 terminate db2 deactivate db <db_name> db2stop
db2stop force
db2_kill ipclean –a
ps –ef | grep <instance_name>
ipcs –a | grep <instance_name>
sudo ipcrm –m resource_id (for m resources) sudo ipcrm –s resource_id (for s resources) sudo ipcrm –q resource_id (for q resources)
db2start
db2pd -d <database> -locks showlocks
db2pd -alldbs - locks showlocksDatabase Partition 0 -- Database SAMPLE -- Active -- Up 00:00:40
db2pd -d <database> -tansactions transaction= < TranHdl >Database Partition 0 -- Database SAMPLE -- Active -- Up 00:05:06
db2pd -agents application=< AppHandl>Database Partition 0 -- Database SAMPLE -- Active -- Up 00:06:15
db2 connect to <db_name>To find out the maximum numbers of active connections that a database support type:
db2 get db cfg | grep -i MAXAPPLSthe output will be something like that:
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4) Max number of active applications (MAXAPPLS) = 250
db2 get dbm cfg | grep -i maxthe output will be something like that:
Max number of existing agents (MAXAGENTS) = 400 Max number of coordinating agents (MAX_COORDAGENTS) = (MAXAGENTS - NUM_INITAGENTS) Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS
db2 get snapshot for dbm | grep -i highthe output will be something like that:
Private Sort heap high water mark = 38238 High water mark for agents registered = 129 High water mark for agents waiting for a token = 0 High water mark for coordinating agents = 126 High water mark (bytes) = 1114112 High water mark (bytes) = 2342912In this example you can notice the database supports 250 active applications and the High water mark was 129 of the active connections.
$ ls -l ~/sqllib/adm/.fenced -r--r--r-- 1 vikram vikram 0 Feb 13 19:02 /home/vikram/sqllib/adm/.fencedIn the above example, the instance id is also the fenced user id.
C:\>db2pd -fmp | grep -i fenced Trusted Path: C:\PROGRA~1\IBM\SQLLIB\\function\unfenced Fenced User: DB2 $ db2pd -fmp | grep -i fenced Trusted Path: /home/vikram/sqllib/function/unfenced Fenced User: vikramThe concept of fenced user-id is really for the Unix platforms and for Windows, the fenced user id is same as that of db2 instance owner. The output of db2pd -fmp for Windows shows the name of the instance as fenced user id.
db2 delete from employee where col1 in (select col1 from employee fetch first 1000 rows only )with the statement above you can apply in loop to insert in many blocks
TBSP_NAME RECLAIMABLE_SPACE_ENABLED TBSP_FREE_PAGES TBSP_PAGE_TOP TBSP_USABLE_PAGES ------------------------------ ------------------------- -------------------- -------------------- -------------------- TEMPSPACE1 0 0 0 1 USERSPACE1 1 0 1568 1568 SYSTOOLSTMPSPACE 0 0 0 1 TBSP1 1 0 1632 1632 SMSDEMO 0 0 0 1 SYSCATSPACE 1 2012 10272 12284 IBMDB2SAMPLEREL 1 3328 736 4064 TS1 1 3584 480 4064 TS2 1 3968 96 4064 TBSP2 1 3968 96 4064 TBSAUTO 1 3968 96 4064 SYSTOOLSPACE 1 3976 116 4092 12 record(s) selected.