--**GURUNANAK
--================
--================
--DEVELOPMENT RESOURCE
--Date 2008-10-03 - (started)
--
--Description : 
--===============
 
--**DB2LOOK -- see "Examples- DB2LOOK" doc  -- see "Db2LOOK-DDL and Statics" doc
    db2look -d <db_name> -a -e -l -x -f -o db2look.sql
    db2look -d <db_name> -u db2admin -e -l -x -f -o db2look.sql
    db2look -d test -e -z sgill -o sgill_tables.sql 
--**EXPORT
--**IMPORT
	export to myfile.del of del modified by chardel'' coldel; decpt, select * from staff
    import from myfile.del of del modified by chardel'' coldel; decpt, messages msgs.txt insert into staff

--**Arithmatic
	Modulus : MOD(num,2)=0 represents even numbers
	

--**CHANGE DB2 PORT 
    step1) open C:\WINDOWS\system32\drivers\etc\services file and add your favorite port name and port number.

		on Linux /etc/services
			myport 52000/tcp

    step2)	open db2 command window ,and change SVCENAME param to your port name.
	 		ex)
	     		db2 "update dbm cfg using SVCENAME myport"

    step3)  restart your db2 instance.
             (db2stop , db2start)
             
--**Open Port 50000

	# vi /etc/sysconfig/iptables
 		-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 50000 -j ACCEPT
 	# /etc/init.d/iptables restart

 	# iptables -A INPUT -p tcp --dport 50000 -j ACCEPT
    # /sbin/service iptables save
	# /etc/init.d/iptables restart

	
--**DB2Shell Linux
	
	#!/bin/sh
	
	#################################################
	## Sarbjit Singh Gill              DB2Shell.sh ##
	## OS : Linux Unix AIX                         ##
	## Syntax : DB2Shell db cmdFile [logFile]      ##
	##      Runs DB2 SQL commands from a file      ##
	##      optionally logs output to a log file   ##
	#################################################
	
	if [ "$2" ];then 
	       db=$1
	       file=$2
	  else 
	       echo
	       echo Syntax : DB2Shell db cmdFile [logFile] 
	       exit 1
	       echo
	fi
	
	log=$file.log
	
	if [ "$3" ];then
	    log=$3
	fi
	
	if [ "$4" ];then 
	     echo
	     echo ** Too many parameters
	     echo
	     exit 1
	  else
	     echo  | tee -a $log
	     echo DB2Shell : $*  | tee -a $log
	     echo Time    : `date` | tee -a $log
	     echo  | tee -a $log	     
	fi
	
	. /home/db2inst1/sqllib/db2profile
	
	db2 -v connect to $db | tee -a $log
	
	if (( $? == 0 )) 
	  then
		while read line
			do
			     date | tee -a $log
			     echo "$line" | tee -a $log
			     db2 "$line" | tee -a $log
			done < $file
		db2 -v connect reset | tee -a $log
		db2 -v terminate | tee -a $log
	else
	       echo Cannot connect to database $db
	       exit 4
	fi
	
	exit 0
	
--**DB2Shell Windows
	
	@echo off
	
	::#################################################::
	::## Sarbjit Singh Gill             DB2Shell.bat ##::
	::## OS : Windows                                ##::
	::## Syntax : DB2Shell db cmdFile [logFile]      ##::
	::##      Runs DB2 shell commands from a file    ##::
	::##      optinally logs output to a log file    ##::
	::#################################################::
	
	if "%2" neq "" ( set "db=%1"
	                 set "file=%2"
	               ) else (
	               	echo.
	       			echo Syntax : DB2Shell db cmdFile [logFile] 
	       			echo. 
					exit 1
				)
	set "log=%file%.log"
	if "%3" neq "" set "log=%3"
	if "%4" neq "" (echo.
	     			echo ** Too many parameters
	     			echo.
	     			exit 1
	     		)
	
	
--**Java Example : Select
import java.sql.*;

class Test
  {
  public static void main(String[] argv)
    {
		String url = "jdbc:db2://localhost:50000/SAMPLE:user=db2admin;password=wirefire;";
		//currentSchema=DB2ADMIN;currentFunctionPath=SYSFUN,SYSPROC,DB2ADMIN;";
		Connection con;
		Statement stmnt;
		ResultSet rs;
		String stmntStr="SELECT * FROM DEPT";
		System.out.println("\n\tDB2 Example : Connect and Query\n");
		
	try {
		Class.forName("com.ibm.db2.jcc.DB2Driver");
		System.out.println("--------------Pre-Connection");
		con=DriverManager.getConnection(url);
		stmnt=con.createStatement();

		System.out.println("--------------Pre-Query");
		rs= stmnt.executeQuery(stmntStr);
		System.out.println("--------------Post-Query");

		while (rs.next())
			{
			System.out.println("\t"+rs.getString(1)+"\t"+rs.getString(2));
 			}
 
		rs.close();
		stmnt.close();
 
		con.close();
		System.out.println("--------------Connection Closed");

		}catch (Exception e) { e.printStackTrace(); };
 	}
}


--**Java Example : Insert

import java.sql.*;

class Populate
  {
  public static void main(String[] argv)
    {
		String url = "jdbc:db2://localhost:50000/SAMPLE:user=db2admin;password=wirefire;";
		//currentSchema=DB2ADMIN;currentFunctionPath=SYSFUN,SYSPROC,DB2ADMIN;";
		Connection con;
		Statement stmnt;
		ResultSet rs;
		Integer id=0,cnt,ulmt;
		String stmntStr="select max(id) from test";
		System.out.println("\n\tDB2 Example : Connect and Query\n");
		
	try {
		Class.forName("com.ibm.db2.jcc.DB2Driver");
		System.out.print(">=");
		con=DriverManager.getConnection(url);
		stmnt=con.createStatement();

		System.out.println("=");
		rs= stmnt.executeQuery(stmntStr);
		if (rs.next()) id=rs.getInt(1);//id= rs.getInt(1);
		
		rs.close();
		
		stmntStr="Insert into test values(?,?,default)";
		PreparedStatement prest = con.prepareStatement(stmntStr);
		
		id+=1;
		ulmt=id+5;
		
		while ( id<ulmt)
		{
		prest.setInt(1,id);
		prest.setString(2,"////////");
		System.out.print("\n-----"+id.toString());
		cnt=prest.executeUpdate();  
	    Thread.sleep(20000); //20 secs
		id++;
	}
	
	    System.out.print("\n>=");
 
		rs.close();
		stmnt.close();
 
		con.close();
		System.out.println("=");

		}catch (Exception e) { e.printStackTrace(); };
 	}
}
 
--**CATALOG
    CATALOG TCPIP NODE <nodename> REMOTE <ip or servername> SERVER <port> REMOTE_INSTANCE DB2 OSTYPE WIN;
    CATALOG DATABASE <dbname> AS <alias> AT NODE <nodename>;
    CATALOG SYSTEM ODBC DATA SOURCE DTBS
    
   example: catalog tcpip node ids remote sarbjitsinghjgill.com server 50000
  			catalog database ids as ids at node ids
  			 
       example from TOAD
       
		CATALOG TCPIP NODE TRISTG
			REMOTE 10.70.2.55
			SERVER 50000
			REMOTE_INSTANCE DB2
			OSTYPE WIN;
		CATALOG DATABASE TRIUMPH
			AS TRIUMPHS
			AT NODE TRISTG
			AUTHENTICATION SERVER;

db2 connect to DBNAME
db2 catalog system odbc data source DBNAME
db2 terminate

--**reorgchk
--**Tables Need Reorg

	if clasterratio in sysibm.sysindexe is < 90 
	
	row overflow in sysibm.systables is > 5 
	
--**Timestampdiff
--**
db2 -x "values TIMESTAMPDIFF(4, CHAR(timestamp('2011-08-19-14.51.44.736219') - timestamp('2011-08-18-14.22.59.275953'))) * 60"

--**Most Active Tables
	select substr(table_schema,1,10) as tbschema,
	substr(table_name,1,30) as tbname,
	rows_read,
	rows_written,
	overflow_accesses,
	page_reorgs
	from table (SNAPSHOT_TABLE(' ',-1)) as snapshot_table
	order by rows_read desc
	fetch first 10 rows only
	

--**Memory Usage
		
		
		#!/usr/bin/ksh
		#
		#------------------------------------------------------------------------------------
		# Program      :  getmemoryusage.sh
		# Description  :  The script gets the following memory usage for the instance
		#              :  Max Limit: The DB2 server's upper bound of memory that can be consumed.
		#              :  Current usage: The amount of memory the server is currently consuming.
		#              :  HWM : The peak memory usage that has been consumed since the activation of the database partition
		#              :  Avail. in Current Cached memory: How much of the current usage is not currently being used.
		# Usage        : ./getmemoryusage.sh
		#
		# Author       :  Raju Pillai
		# Date         :  2009-06-05
		#-----------------------------------------------------------------------------------
		#
		maximum="0"
		current="0"
		highest="0"
		cached="0"
		keyword=""
		OutputDir="/home/db2inst5/report"
		Datetime="`date +%Y-%m-%d-%H:%M:%S`"
		if [ $(cat $OutputDir/memory_usage.out|wc -l) -lt 1 ]
		then
		print "==============================================" >> $OutputDir/memory_usage.out
		print "Total instance and database memory usage in KB" >> $OutputDir/memory_usage.out
		print "==============================================" >> $OutputDir/memory_usage.out
		print " " >> $OutputDir/memory_usage.out
		awk 'BEGIN { printf "%-20s%20s%20s%20s%20s\n", "Snapshot timestamp", "Max Limit", "Current Usage","High Water Mark",
		"Avail. in Current"
		    printf "%-20s%20s%20s%20s%20s\n", "==================", "=========", "=============","===============","========
		=========="}' print >> $OutputDir/memory_usage.out
		fi
		db2pd -dbptnmem |grep ' KB'| tr -s " " " " > $OutputDir/db2pd-memory.output
		while read currentline; do
		   keyword=`print "$currentline" |awk '{ print $1,$2 }'`
		  if [ "$keyword" = "Memory Limit:" ]
		       then
		             maximum=`print "$currentline" |awk '{ print $3 }'`
		  fi
		  if [ "$keyword" = "Current usage:" ]
		       then
		       
		             current=`print "$currentline" |awk '{ print $3 }'`
		  fi
		  if [ "$keyword" = "HWM usage:" ]
		       then
		             highest=`print "$currentline" |awk '{ print $3 }'`
		  fi
		  if [ "$keyword" = "Cached memory:" ]
		       then
		             cached=`print "$currentline" |awk '{ print $3 }'`
		  fi
		done < $OutputDir/db2pd-memory.output
		print "$Datetime" " " "$maximum" " " "$current" " " "$highest" " " "$cached" > $OutputDir/tmp_memory_usage.out
		awk '{printf "%-20s%20s%20s%20s%20s\n",$1,$2,$3,$4,$5}' $OutputDir/tmp_memory_usage.out >> $OutputDir/memory_usage.out
		rm $OutputDir/db2pd-memory.output
		rm $OutputDir/tmp_memory_usage.out
		

--**db2set

	Display all defined profiles (DB2 instances) pertaining to a particular installation : 
   db2set -l
   Display all supported registry variables: 
   db2set -lr
   Display all defined global variables which are visible by all instances pertaining to a particular installation: 
   db2set -g
   Display all defined variables for the current instance: 
   db2set
   Display all defined values for the current instance: 
   db2set -all
   Display all defined values for DB2COMM for the current instance: 
   db2set -all DB2COMM
   Reset all defined variables for the instance INST on node 3: 
   db2set -r -i INST 3
   Unset the variable DB2CHKPTR on the remote instance RMTINST through the DAS node RMTDAS using user ID MYID and password MYPASSWD: 
   db2set -i RMTINST -n RMTDAS -u MYID -p MYPASSWD DB2CHKPTR=
   Set the variable DB2COMM to be TCPIP globally for all instances pertaining to a particular installation: 
   db2set -g DB2COMM=TCPIP
   Set the variable DB2COMM to be only TCPIP for instance MYINST: 
   db2set -i MYINST DB2COMM=TCPIP
   Set the variable DB2COMM to null at the given instance level: 
   db2set -null DB2COMM
   Usage notes
	If no variable name is specified, the values of all defined variables are displayed. If a variable name is specified, only the value of that variable is displayed. To display all the defined values of a variable, specify variable -all. To display all the defined variables in all registries, specify -all.

	To modify the value of a variable, specify variable=, followed by its new value. To set the value of a variable to NULL, specify variable -null. Changes to settings take effect after the instance has been restarted.

	To delete a variable, specify variable=, followed by no value.


--**Exception
--**Stored Procedure
	
		CREATE PROCEDURE sample (IN sal DEC(8,2), IN dept CHAR(3))
		LANGUAGE SQL   MODIFIES SQL DATA
		BEGIN
		  DECLARE sqlstate   CHAR(5) DEFAULT '00000';
		  DECLARE errorLabel CHAR(80) DEFAULT '';
		  DECLARE v_name     VARCHAR(50);
		  DECLARE v_salary   DEC(8,2);
		  DECLARE at_end     CHAR(1) DEFAULT '0';
		  DECLARE c1 CURSOR FOR
		    SELECT name, salary FROM employee WHERE workdept = department  FOR UPDATE OF salary;
		  DECLARE CONTINUE HANDLER FOR NOT FOUND
		    SET at_end = '1';
		  DECLARE EXIT HANDLER FOR SQLEXCEPTION
		    SIGNAL SQLSTATE VALUE sqlstate SET MESSAGE_TEXT = errorLabel;
		  SET errorLabel = 'OPEN CURSOR';   OPEN c1;
		  SET errorLabel = 'FIRST FETCH';   FETCH c1 INTO v_name, v_salary;
		  WHILE (at_end = '0') DO
		    IF (v_salary < sal) THEN
		      SET errorLabel = 'UPDATE FOR '||v_name|| ' FROM '||CAST(v_salary AS varchar(9))||' TO '||CAST(sal AS varchar(9));
		      UPDATE employee SET salary = sal WHERE CURRENT OF c1;
		    END IF;
		    SET errorLabel = 'FETCH IN LOOP';   FETCH c1 INTO v_name, v_salary;
		  END WHILE;
		  SET errorLabel = 'CLOSE CURSOR';   CLOSE c1;
		END
		
	----
	 db2 "DESCRIBE TABLE  SYSCAT.ROUTINES"
	 db2 "SELECT ROUTINESCHEMA, ROUTINENAME FROM SYSCAT.ROUTINES WHERE ROUTINESCHEMA NOT LIKE 'SYS%'"
	 
	 db2 DESCRIBE TABLE SYSCAT.PROCEDURES
	 db2 "SELECT PROCSCHEMA, PROCNAME  FROM SYSCAT.PROCEDURES WHERE PROCSCHEMA = CURRENT USER "
	 
	 db2 DESCRIBE TABLE SYSCAT.FUNCTIONS
	 db2 "SELECT FUNCSCHEMA,FUNCNAME FROM SYSCAT.FUNCTIONS WHERE FUNCSCHEMA = CURRENT USER "
	 
	 
	 ---- where definer not in ('SYSIBM')
 
	  
--**FUNCTIONS
	CREATE FUNCTION SUM (num1 integer, num2 integer)
     RETURNS INTEGER
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN num1 + num2	
     
     CREATE FUNCTION SUM ( in num1 integer, in num2 integer)
     RETURNS INTEGER
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN num1 + num2		 
	 
     CREATE FUNCTION SUM ( num1 integer,num2 integer)
     RETURNS INTEGER
     RETURN num1 + num2		 


--**PRIVILEGES

	select * from syscat.tabauth where grantee='USERNAME'
	db2 select distinct grantee from sysibm.systabauth
	
	Table:
	db2 describe table syscat.tabauth
	db2 describe table sysibm.systabauth
	
	
	Database:
	db2 describe table syscat.dbauth
	db2 describe table sysibm.sysdbauth
	 
	Find more autorization catalogs:
	db2 "select substr(tabschema,1,20)schema,substr(tabname,1,30) tablename from syscat.tables where tabname like '%AUTH%'"
	db2 "select substr(tabschema,1,20)schema,substr(tabname,1,30) tablename from syscat.tables where tabname like '%ROLE%'"

--**Decommision DB2 AIX Box - Steps
	list db directory LIST DATABASE DIRECTORY
	drop database DROP DATABASE
	db2idrop (in root userid) db2idrop - Remove instance
	uninstall db2
db2 -x "select   int(total_log_used/1024/1024) as Log_Used_Meg
            , int(total_log_available/1024/1024) as Log_Space_Free_Meg
            , int((float(total_log_used) /float(total_log_used+total_log_available))*100) as Percent_Used
            , int(tot_log_used_top/1024/1024) as Max_Log_Used_Meg
            , int(sec_log_used_top/1024/1024) as Max_Secondary_Used_Meg
            , int(sec_logs_allocated) as Secondaries
 from sysibmadm.snapdb"

--**ALTER TABLESPACE EXTEND RESIZE
 ALTER TABLESPACE INDEX_TS EXTEND (ALL 100 M)
 ALTER TABLESPACE INDEX_TS EXTEND (ALL 1000)
 ALTER TABLESPACE INDEX_TS RESIZE (ALL 100 M)
 ALTER TABLESPACE TS0
     ADD (FILE 'cont2' 2000, FILE 'cont3' 2000)
     ADD (FILE 'cont4' 2000)
     EXTEND (FILE 'cont0' 100)
     RESIZE (FILE 'cont1' 3000) 
     
  ALTER TABLESPACE AUTOSTORE1
     AUTORESIZE YES INCREASESIZE 5 PERCENT 
  ALTER TABLESPACE MY_TS
     INCREASESIZE 512 K MAXSIZE NONE 
     
--**TABLESPACE CREATE ADD CONTAINER
	CREATE TABLESPACE CRAZY MANAGED BY DATABASE USING (FILE '/home/db2inst1/tbs/tbs1' 5000)
	ALTER TABLESPACE CRAZY ADD (file '/home/db2inst1/tbs/tbs2' 5000)

--**
	ALTER TABLESPACE USERSPACE1 AUTORESIZE NO
	
	
	
--**EXPORT TO	
	Example 1
The following example shows how to export information from the STAFF table in the SAMPLE database (to which the user must be connected) to myfile.ixf, with the output in IXF format. If the database connection is not through DB2 Connect™, the index definitions (if any) will be stored in the output file; otherwise, only the data will be stored: 
   db2 export to myfile.ixf of ixf messages msgs.txt select * from staffExample 2
The following example shows how to export the information about employees in Department 20 from the STAFF table in the SAMPLE database (to which the user must be connected) to awards.ixf, with the output in IXF format: 
   db2 export to awards.ixf of ixf messages msgs.txt select * from staff
      where dept = 20Example 3
The following example shows how to export LOBs to a DEL file: 
   db2 export to myfile.del of del lobs to mylobs/
      lobfile lobs1, lobs2 modified by lobsinfile
      select * from emp_photoExample 4
The following example shows how to export LOBs to a DEL file, specifying a second directory for files that might not fit into the first directory: 
   db2 export to myfile.del of del
      lobs to /db2exp1/, /db2exp2/ modified by lobsinfile
      select * from emp_photoExample 5
The following example shows how to export data to a DEL file, using a single quotation mark as the string delimiter, a semicolon as the column delimiter, and a comma as the decimal point. The same convention should be used when importing data back into the database: 
   db2 export to myfile.del of del
      modified by chardel'' coldel; decpt,
      select * from staff

      
--**java install jdk

			1. yum install libXp-1.0.0-8.1.el5.i386
			rpm -ivh IBMJava2-AMD64-142-SDK-1.4.2-13.9.x86_64.rpm
			rmp -ivh IBMJava2-AMD64-142-JRE-1.4.2-13.9.x86_64.rpm
			
			2. Make sure JDK_PATH is set to /opt/IBMJava2-amd64-142
			db2 get dbm cfg | grep JDK_PATH
			
			If not, then set it with the update dbm command --
			
			db2 update dbm cfg USING JDK_PATH /opt/IBMJava2-amd64-142
			db2stop
			db2start
			
			3. Go to /etc/profile and add the following lines above the line that says 
			export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE INPUTRC
			
			export PATH=$PATH:/opt/IBMJava2-amd64-142/jre/bin
			export JAVA_HOME=/opt/IBMJava2-amd64-142
			export CLASSPATH=.:$JAVA_HOME/lib/classes.zip:/$JAVA_HOME/lib
			
			
			4. Add a path to the shared libraries in /etc/ld.so.conf
			
			vi /etc/ld.so.conf
			Add the following line-- /opt/IBMJava2-amd64-142/jre/bin 
			
			Give the command ldconfig for changes to take effect
			exit out / log back in to test
--**REORG

   db2 "SELECT SUBSTR(TABNAME, 1, 15) AS TAB_NAME
             ,SUBSTR(TABSCHEMA, 1, 15) AS TAB_SCHEMA
             ,REORG_PHASE,SUBSTR(REORG_TYPE, 1, 20) AS REORG_TYPE
             ,REORG_STATUS,REORG_COMPLETION
             ,DBPARTITIONNUM FROM SYSIBMADM.SNAPTAB_REORG
        where REORG_STATUS='STARTED'
         ORDER BY DBPARTITIONNUM" 
         
   select snapshot_timestamp
         ,varchar(table_name,60) as table
         ,reorg_status
         ,reorg_start
         ,reorg_end
         ,productio n.udf_ts_difference(coalesce(reorg_end,snapshot_ti mestamp)
         ,reorg_start,'MINUTES') as minutes
         ,case when reorg_max_counter = 0 then 0 else (reorg_current_counter * 100)/reorg_max_counter end as percent
         ,reorg_current_counter
         ,reorg_max_counter
         ,reorg_phase
         ,reorg_max_phase
   from table(snapshot_tbreorg('MyDB',-1)) as x
      order by reorg_start

	---- for indexes
	db2 list utilities show detail ( get all utilities running) 
	db2 application show detail ( check status of application) 

	get application id , db2 snapshot for application agentid 
	<idUgotfromlistapplication> 

	U will find tables and index details 

--**Memory Usage
	
	as used: db2mtrk -i -d -p -v | grep Total | awk '{SUM+=$2}END{ print SUM }'
	
	db2mtrk -i -d ( I for instance D for database) 
	
	The instance total memory is about 50 mb, i didnt activate any database, after starting the instance the memory used from the RAM is different. For example refer to the below usage:
	 Instance configuration: 
	----------------------- 
	
	Database monitor heap size (4KB) (MON_HEAP_SZ) = 90 
	Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048 
	Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0 
	Size of instance shared memory (4KB) (INSTANCE_MEMORY) = 4000 
	Backup buffer default size (4KB) (BACKBUFSZ) = 1024 
	Restore buffer default size (4KB) (RESTBUFSZ) = 1024 
	Application support layer heap size (4KB) (ASLHEAPSZ) = 15 
	Max requester I/O block size (bytes) (RQRIOBLK) = 32767 
	Query heap size (4KB) (QUERY_HEAP_SZ) = 1000 
	No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC 
	
	CALCULATION: 
	------------ 
	
	KB MB 
	90 * 4 = 360 = 0.351 
	2048 * 4 = 8192 = 8 
	4000 * 4 = 16000 = 15.62 
	1024 * 4 = 4096 = 4 
	1024 * 4 = 4096 = 4 
	15 * 4 = 60 = 0.058 
	32767/1024 = 0.031 
	1000 * 4 = 4000 = 3.90 
	4096 * 4 = 16384 = 16 
	----------------------- 
	Total in MB = 51.96 
	----------------------- 
	
	so expected memory utilization will be like: 
	free (RAM) memory - db2 instance memory 
	405 - 51.96 = 352.04 - expected 
	
	U will get some info 
	by running following SQL statement 
	for Instance 
	select * from sysibmadm.SNAPDBM_MEMORY_POOL 
	for database 
	select * from sysibmadm.SNAPDB_MEMORY_POOL
	
	Prior to Viper II (9.5), db2mtrk using the high-water mark is your
	 best bet, but will not include all memory consumed by the instance.
	 As well, the results may be misleading - you will not necessarily hit
	 the high watermark for all heaps at the same time, and some heaps will
	 come and go during operation (i.e. each new connection that comes in
	 will require a new application heap).
	 
	If you're on Viper II or later though, there is a new table function
	 that will tell you exactly what you want - admin_get_dbp_mem_usage().
	 That will tell you the most memory the instance is allowed to consume,
	 the current memory that is being consumed by the instance, and the
	 peak/high watermark of memory consumed by the instance since db2start.
 
	Option 1: Using db2mtrk 
	MEMORY=`db2mtrk -i -d -p -v | grep Total | awk '{ print $2 }'`
	
	TOTAL_MEMORY=0
	for i in `echo $MEMORY`
	do
	TOTAL_MEMORY=`expr $TOTAL_MEMORY + $i`
	done
	
	Option 2: Using ps as instance owner.
	DB2_MEM_LIST=$( ps gux | grep db2 | awk '{ print $6 }' | tr '\012' ' ' )
	
	DB2MEM=0
	for db2_mem in $DB2_MEM_LIST
	do
	(( DB2MEM=DB2MEM + $db2_mem ))
	done

		
		Instance_memory is sum of
	mon_heap_sz + Audit_buffer_sz +FCM_NUM_BUFFERS+ Approx. 10% overhead
	
	you can see all the values set from
	get dbm cfg show detail

--**manage logs compress archived
	db2diag -A

	mkdir /dir/to/my/path/tmp
	
	for i in `find /dir/to/my/path -type f -mtime +10`
	do
	mv $i /dir/to/my/path/tmp
	done
	
	tar cvf /tmp/backup_file.tar /dir/to/my/path/tmp
	rm -r /dir/to/my/path/tmp
	

	
	To generate and run reorg and runstats scripts: 

Log in as the DB2 administrator, and connect to the database. 

To create a script for reorg table, run the following command on one line: 
db2inst1@sevenj:~> db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG TABLE /g' | sed 's/$/;/g' > /tmp/reorg_tbl.ddl 

To create a script for reorg index, run the following command on one line: 
db2inst1@sevenj:~> db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG INDEXES ALL FOR TABLE /g' | sed 's/$/;/g' > /tmp/reorg_idx.ddl 

To create a script for runstats, run the following command on one line: 
db2inst1@sevenj:~> db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/RUNSTATS ON TABLE /g' | sed 's/$/ WITH DISTRIBUTION AND DETAILED INDEXES ALL;/g' > /tmp/runstats_tbl.ddl 

Run reorg against tables and indexes, then runstats by entering the following lines: 
db2inst1@sevenj:~> db2 -tvf /tmp/reorg_tbl.ddl db2inst1@sevenj:~> db2 -tvf /tmp/reorg_idx.ddl db2inst1@sevenj:~> db2 -tvf /tmp/runstats_tbl.ddl 

Ignore the following message if you receive it: 
SQL2212N The specified table is a view. The Reorganize Table utility cannot be run against a view. 

To capture the message printed on screen, you can pipe the standard output into a file, such as with the following command: 
db2inst1@sevenj:~> db2 -tvf /tmp/runstats_tbl.ddl > filename.log 2>&1

--**foreign key
	ALTER TABLE TEST2
		ADD CONSTRAINT ID1
		    FOREIGN KEY (ID2)
		        REFERENCES TEST1

 db2 "alter table test2 add constraint id foreign key (idd) references  test1"
	CREATE TABLE EMP_ACT
     (EMPNO       CHAR(6) NOT NULL,
      PROJNO      CHAR(6) NOT NULL,
      ACTNO       SMALLINT NOT NULL,
      EMPTIME     DECIMAL(5,2),
      EMSTDATE    DATE,
      EMENDATE    DATE,
      CONSTRAINT EMP_ACT_UNIQ UNIQUE (EMPNO,PROJNO,ACTNO),
      CONSTRAINT FK_ACT_PROJ FOREIGN KEY (PROJNO)
                             REFERENCES PROJECT (PROJNO) ON DELETE CASCADE
     )
     IN SCHED 
     
     
    db2 "SELECT OS_NAME, HOST_NAME, TOTAL_CPUS, CONFIGURED_CPUS, TOTAL_MEMORY FROM TABLE(SYSPROC.ENV_GET_SYS_INFO()) AS SYSTEMINFO" 
    db2 "Select Inst_name From Table(sysproc.env_get_inst_info()) As Instanceinfo
 	db2 "SELECT * FROM table(SYSPROC.HEALTH_DBM_INFO(-2)) DATABASEMANAGER
 	
 	
 	
 	#!/bin/ksh
 	
  	# Source the db2 environment variables.
  	 
  	. @HOME@/sqllib/db2profile
  
   LIST1=/tmp/tempreorg1
   LIST2=/tmp/tempreorg2
   LOG=@HOME@/schema/utilities/reorg.log
   
   { rm $LIST1 $LIST2 2>> $LOG DATABASE=$1
   #
   
    db2 -v "connect to ${DATABASE}" 
    # 
    # get a list of tables
    db2 "select tabname from syscat.tables where tabschema='@SCHEMA@' and type='T'">$LIST1
     if [ $? -ne 0 ] then print "an error occurred on connect"
      exit 1
     fi
     
      #
      # delete the first three lines
      # delete the line that contains "record" 
      #
      sed '1,3d' $LIST1 |sed '/record(s)/d' > $LIST2 
      # 
      # 
      print "begin reorg" 
      # 
      for i in `cat $LIST2` {
      # 
      print reorg table $i
      db2 -v "reorg table @SCHEMA@.$i"
      if [ $? -ne 0 ] then 
      	print "An error occurred in reorg"
        exit 1
      fi
      db2 -v "runstats on table @SCHEMA@.$i with distribution and detailed indexes all"
      if [ $? -ne 0 ]
       then 
       print "an error occurred in runstats" 
       exit 1 
      fi
       } 
       
       print "reorg SUCCESSFUL" rm $LIST1 $LIST2 
       
       } 

       
       nohup db2 reorg .... &
reorg_pid=$!
wait $reorg_pid 

nohup db2 connect to ISQ user db2inst1 using password &
reorg_pid=$!
wait $reorg_pid
nohup db2 REORG TABLE ADM08.UPORABNIKI INPLACE ALLOW WRITE ACCESS START &
reorg_pid=$!
wait $reorg_pid


nohup db2 REORG TABLE CEV.CEVBLOK INPLACE ALLOW WRITE ACCESS START &
reorg_pid=$!
wait $reorg_pid


nohup db2 REORG TABLE CEV.CEVDEFORM INPLACE ALLOW WRITE ACCESS START &
reorg_pid=$!
wait $reorg_pid


db2 connect ...
db2 reorg ...
# check if reorg is running
db2 list applications | grep -q db2reorg
RC=$? 
# loop while it's running
while [[ $RC -eq 0 ]]
do
    sleep 300 #seconds
   db2 list applications | grep -q db2reorg
   RC=$? 
done
# continue with another reorg
db2 reorg ...

--**db2 ports aix

	#!/bin/ksh
	for svc in `lsof +M -i4 | grep db2sysc | grep LISTEN | awk '{printf "%-15s %-15s %-20s\n",$1,$3,$9}' | awk -F':' '{print $2}' | sort -u`
	do
	   grep $svc /etc/services | awk '{ printf "%-20s %-s\n",$1,$2}'
	done
	
--**db2 ports hp-ux
	
--**db2 tools

	db2mtrk			db2greg -dump
	db2pd			db2level
	db2_local_ps	db2ilist
	

--**Compare Schemas
	In answer to the OP, assuming you do not have time to research, install or buy tools,
	I would simply export key system catalog tables at both servers, ordered by the same columns,
	then run a sdiff -s against the two outputs. This would make a great training exercise for a junior.
	 Of course that will only compare DDL structure, not data content.
	 
--**.NET Provider
C:\Users\sgill>db2nmpsetup -l db2nmpsetup.log
C:\Users\sgill>type db2nmpsetup.log
C:\Users\sgill>testconn20.exe "User ID=sgill;Password=6yhn^YHN;Database=SAMPLE;Server=localhost;ServerType=db2;pooling=false"

--**INSTALL LINUX
	Install

	* Login as root.
	* Create groups:
	o groupadd -g 999 db2iadm1
	o groupadd -g 998 db2fadm1
	o groupadd -g 997 dasadm1 
	
	* Create users for each group:
	o useradd -u 1004 -g db2iadm1 -m -d /home/db2inst1 db2inst1
	o useradd -u 1003 -g db2fadm1 -m -d /home/db2fenc1 db2fenc1
	o useradd -u 1002 -g dasadm1 -m -d /home/dasusr1 dasusr1 
	
	* Set password for each users created:
	o passwd db2inst1
	o passwd db2fenc1
	o passwd dasusr1 
	
	* cd to installation file directory:
	o Example: /tmp/db2/exp/disk1 
	
	* Run installation script:
	o ./db2_install.sh 
	
	Post Install
	
	* Login as root.
	* Install license (example):
	o /opt/ibm/db2/V9.1/adm/db2licm –a /tmp/db2/exp/disk1/db2/license/db2exp_uw.lic 
	
	Create the DB2 Administration Server (DAS)
	
	* Login as root.
	* Create DAS with dasusr1
	o /opt/ibm/db2/V9.1/instance/dascrt -u dasusr1 
	
	* Login as dasuser1
	o Start the DAS: db2admin start 
	
	* Optional: to enable autostarting of the DAS upon reboot
	o /opt/ibm/db2/V9.1/instance/dascrt/dasauto –on 
	
	Create DB2 instance
	
	* Login as root.
	* Create instance with users db2fenc1 and db2inst1:
	o /opt/ibm/db2/V9.1/instance/db2icrt -a server -u db2fenc1 db2inst1 
	* Optional: enable autostarting of the db2inst1 instance
	o /opt/ibm/db2/V9.1/instance/db2iauto –on db2inst1 
	
	Update environment variables
	
	* Example:
	* Login as db2inst1
	* edit .bash_profile
	o vi /home/db2inst1/.bash_profile
	o insert the following line at the end of the file – “. /home/db2inst1/sqllib/db2profile” 
	
	* Do the same for dasusr1, using its corresponding directory. 
	
	Database service
	
	* Login as root:
	* Add new service entry:
	o vi etc/services
	o insert this line “DB2_TMINST 50000/tcp” at the end of the file 
	
	Verification
	
	* Login as db2inst1
	* List installed DB2 products and features: db2ls
	* Display the default instance: db2 get instance
	o Result: The current database manager instance is: db2inst1 
	* Start the database instance: db2start
	o Result: SQL1063N DB2START processing was successful. 
	* Stop the database instance: db2stop
	o Result: SQL1064N DB2STOP processing was successful. 

--**deadlock event monitor

db2 "select target from syscat.eventmonitors where evmonname='DB2DETAILDEADLOCK'"

db2 list active databases | grep "Database path"

find <path> -name <target> 

db2 CONNECT TO sample
db2 LIST ACTIVE DATABASES

                           Active Databases

Database name                    = SAMPLE
Applications connected currently = 1
Database path                    = /home/user1/user1/NODE0000/SQL00001/

find /home/user1/user1/NODE0000/SQL00001/ -name db2detaildeadlock 
/home/user1/user1/NODE0000/SQL00001/db2event/db2detaildeadlock


SELECT EVMONNAME,
CASE
WHEN EVENT_MON_STATE(EVMONNAME) = 0 THEN 'Inactive'
WHEN EVENT_MON_STATE(EVMONNAME) = 1 THEN 'Active'
END
FROM SYSCAT.EVENTMONITORS

db2_capture_locktimeout

--**CONNECTION type 2

	SET CLIENT CONNECT 2
	
	CONNECT TO TEST
	CONNECT TO SAMPLE
	CONNECT TO OTHER
	
	SET CONNECTION OTHER
	
	DISCONNECT OTHER
	
	SET CLIENT CONNECT 1

	
	
	
	
	
	
	
	
--**Find Long Running SQL:
	
	 
	
	SELECT ELAPSED_TIME_MIN,
	
	              SUBSTR(AUTHID,1,10) AS AUTH_ID, 
	
	              AGENT_ID,
	
	APPL_STATUS,
	
	SUBSTR(STMT_TEXT,1,20) AS SQL_TEXT
	FROM    SYSIBMADM.LONG_RUNNING_SQL
	WHERE ELAPSED_TIME_MIN > 0
	ORDER BY ELAPSED_TIME_MIN DESC
	


--**Show Bufferpool Hit Ratios:

 
	
	SELECT SUBSTR(BP_NAME,1,20) as BP_NAME,
	
	             TOTAL_HIT_RATIO_PERCENT as ALL_HR,
	
	             DATA_HIT_RATIO_PERCENT as DATA_HR,
	
	             INDEX_HIT_RATIO_PERCENT as INX_HR,
	
	             XDA_HIT_RATIO_PERCENT as XML_HR
	
	FROM SYSIBMADM.BP_HITRATIO;
	
	 

 

--**Show Queries and Statics from the Package Cache:

 
	
	SELECT 
	
	    SUBSTR(STMT_TEXT,1,20) AS STMT,
	
	    SECTION_TYPE AS TYPE,
	
	    NUM_EXECUTIONS,
	
	    TOTAL_ACT_TIME AS TOTAL_TIME,
	
	    TOTAL_ACT_WAIT_TIME AS WAIT_TIME
	
	FROM TABLE(MON_GET_PKG_CACHE_STMT('','','',-1))
	
	 

--**Show Lock Wait Chains:

 

	select substr(ai_h.appl_name,1,10) as "Hold App",
	
	    substr(ai_h.primary_auth_id,1,10) as "Holder",
	
	    substr(ai_w.appl_name,1,10) as "Wait App",
	
	    substr(ai_w.primary_auth_id,1,10) as "Waiter",
	
	    lw.lock_mode as "Hold Mode",
	
	    lw.lock_object_type as "Obj Type",
	
	    substr(lw.tabname,1,10) as "TabName",
	
	    substr(lw.tabschema,1,10) as "Schema",
	
	    timestampdiff(2,char(lw.snapshot_timestamp - lw.lock_wait_start_time)) 
	
	        as "waiting (s)"
	
	from  sysibmadm.snapappl_info ai_h, 

      sysibmadm.snapappl_info ai_w, sysibmadm.snaplockwait lw

where lw.agent_id = ai_w.agent_id

and   lw.agent_id_holding_lk = ai_h.agent_id

 

--**Show Excessive Sorting:

	 
	
	SELECT
	
	    APPLICATION_HANDLE AS APP_HDL,
	
	    SUBSTR(CLIENT_USERID,1,10) AS USERID,
	
	    TOTAL_SECTION_SORTS AS NUM_SORTS,
	
	    TOTAL_SECTION_SORT_TIME AS TOTAL_TIME,
	
	    TOTAL_SECTION_SORT_PROC_TIME AS SORT_TIME,
	
	    TOTAL_SECTION_SORT_TIME - TOTAL_SECTION_SORT_PROC_TIME AS WAIT_TIME
	
	FROM TABLE(MON_GET_CONNECTION(NULL,-1))
	
 

 

--**Show the transactions with the most CPU and most Wait Time:

 
	
	SELECT
	
	    APPLICATION_HANDLE AS APP_HDL,
	
	    SUBSTR(CLIENT_USERID,1,10) AS USERID,
	
	    TOTAL_RQST_TIME,
	
	    TOTAL_CPU_TIME,
	
	    TOTAL_WAIT_TIME,
	
	    CLIENT_IDLE_WAIT_TIME
	
	FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-1))
	
	 

--**Show the 5 most active tables:

 
	
	SELECT
	
	    SUBSTR(TABSCHEMA,1,10) AS SCHEMA,
	
	    SUBSTR(TABNAME,1,20) AS NAME,
	
	    TABLE_SCANS,
	
	    ROWS_READ,
	
	    ROWS_INSERTED, 
	
	    ROWS_DELETED  
	
	FROM TABLE(MON_GET_TABLE('','',-1))
	
	ORDER BY ROWS_READ DESC
	
	FETCH FIRST 5 ROWS ONLY

 

--**Show the Critical and Error messages in the last 24 hours:
	
	 
	
	SELECT TIMESTAMP, SUBSTR(MSG,1,400) AS MSG 
	FROM SYSIBMADM.PDLOGMSGS_LAST24HOURS
	WHERE MSGSEVERITY IN ('C','E') 
	ORDER BY TIMESTAMP DESC

 

--**Show the messages in the notify log from the last 3 days:

 
	
	SELECT TIMESTAMP, SUBSTR(MSG,1,400) AS MSG
	FROM TABLE 
	
	       ( PD_GET_LOG_MSGS( CURRENT TIMESTAMP - 3 DAYS) )        
	
	         AS PD 
	ORDER BY TIMESTAMP DESC 

 

 

--**Show the average and maximum time taken to perform full backups
	
	 
	
	SELECT AVG(TIMESTAMPDIFF(4,CHAR(
	
	             TIMESTAMP(END_TIME) - TIMESTAMP(START_TIME))))  AS AVG_BTIME,
	         MAX(TIMESTAMPDIFF(4,CHAR(
	
	             TIMESTAMP(END_TIME) - TIMESTAMP(START_TIME)))) AS MAX_BTIME
	FROM SYSIBMADM.DB_HISTORY
	WHERE OPERATION = 'B'
	      AND OPERATIONTYPE = 'F' 
	
	 

--**Show any commands in the recovery history file that failed:

     

	SELECT START_TIME, SQLCODE, SUBSTR(CMD_TEXT,1,50)
	   FROM SYSIBMADM.DB_HISTORY
	
	   WHERE SQLCODE < 0 

 

--**Display information about the application that currently has the oldest uncommitted unit of work.  This is useful if to know about transactions that are holding too much log space:

 
	
	SELECT AI.APPL_STATUS as Status, 
	
	        SUBSTR(AI.PRIMARY_AUTH_ID,1,10) AS "Authid",
	        SUBSTR(AI.APPL_NAME,1,15) AS "Appl Name",
	        INT(AP.UOW_LOG_SPACE_USED/1024/1024) 
	             AS "Log Used (M)", 
	        INT(AP.APPL_IDLE_TIME/60) AS "Idle for (min)", 
	        AP.APPL_CON_TIME AS "Connected Since"
	FROM    SYSIBMADM.SNAPDB DB, 
	        SYSIBMADM.SNAPAPPL  AP,
	        SYSIBMADM.SNAPAPPL_INFO AI
	WHERE   AI.AGENT_ID = DB.APPL_ID_OLDEST_XACT 
	      AND    AI.AGENT_ID = AP.AGENT_ID;