You Are With Mahesh Blog: 01/08/10

Friday, January 8, 2010

Complete db2 commands from Pratical tested

#########################################################################
db2 get snapshot for all on $DB
db2 get snapshot for locks on $DB
db2 get snapshot for dynamic sql on $DB
db2 get snapshot for all applications

#######################################################################################
If you issue the following command locally on the UNIX box, you'll see that there is no listen for 60004.
netstat -an | grep LIS
#########################################################################################
db2evmon -db patrven3 -evm 'DB2DETAILDEADLOCK'
#########################################################################################
reorg indexes all for table "GCCM"."PMT_STATUS_HISTORY_HISTPH1" allow write access
reorg table "ASN"."IBMQREP_APPLYENQ" inplace allow read access
#########################################################################################
Stop governer as follows :-
db2gov stop PAGLMF1 dbpartitionnum 0
start governer as :-

1)USER=paulb026
export USER;
2) db2gov start PAGLMF1 dbpartitionnum 0 /local/udb/PAULB026/home/sqllib/cfg/PAGLMF1_gov.cfg PAGLMF1_gov
3) Log file will be under $HOME/sqllib/log
#########################################################################################
Getting root access.
Login as id
rsh to host
njdbu3pudb3-m% /usr/seos/bin/sesudo root
#
########################################################################################
http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html
########################################################################################
To check governor is running or no

ps -ef | grep db2gov
########################################################################################
This script gets users info. From ADB.

njdbu2pudb4-m:/home/sybasesw/utils> ./dbMcontact DAULB003
#######################################################################################
Command to Check reorg status.

db2pd -db PAFOFXHI -reorgs
########################################################################################
/usr/bin/ldapsearch -h amldap -b "ou=intranet,ou=people,dc=lehman,dc=com" uid=kkodlapa dn employeeNumber=10234609,ou=intranet,ou=people,dc=lehman,dc=com

########################################################################################
DATACOMPASS ENTRY

cd /home/dbapps
cd DC
./DataCompass

BUSINESS LINE --F1
HOST - Host name
Role--/primary/develop/stage
SERVER -- Instance name
VENDER -- IBM

#########################################################################################
SELECTING SCHEMA AND TABLES IN THAT SCHEMA

db2 "select distinct substr(SCHEMANAME,1,20) from syscat.schemata"
db2 list tables for schema ETLDEV
db2 list tables for schema ETLDEV | more

##########################################################################################
./iwatch --servers "/tmp/serverlist.txt" --user vkuratah --password leh11man --remove --debug
./iwatch --servers "/tmp/serverlist.txt" --user vkuratah --password leh11man --add --dbv 5.3 --debug

grep 'dnudbdev1' /home/unix/opsware/conf/DBA-Hosts-US

grep 'dnudbdev1' /home/unix/opsware/conf/DBA-Hosts-US > /tmp/serverlist.txt

cd /home/unix/opsware/scripts
##########################################################################################
"REF_ID_8CHAR" VARCHAR(8) NOT NULL GENERATED ALWAYS AS ( (CAST((SUBSTR(REFERENCE_ID,1,8)) AS SYSIBM.VARCHAR(8))) ) ,

use generatedoverride clause to load

db2 "declare c1 cursor for select * from blues.blue_sheet_detail_qtr1_05"
db2 "load from c1 of cursor modified by generatedoverride insert into BLUES.QTR1_05_TEMP1 nonrecoverable";
db2 commit;
-----------------------------------------------------------------------------
For normal table

db2 "declare c1 cursor for select * from blues.blue_sheet_detail_qtr1_05"
db2 "load from c1 of cursor insert into QTR1_05_TEMP1 nonrecoverable";
db2 commit;
-----------------------------------------------------------------------------
db2look -d pabluest -e -z blues -u paulb038 -o blues.ddl
db2look -d pabluest -e -z blues -t blue_sheet_detail_qtr1_05 -u paulb038 -o one.ddl
db2look -d pabluest -e -z blues -t blue_sheet_detail_qtr2_05 -u paulb038 -o two.ddl
db2look -d pabluest -x -u paulb038 -o grant.ddl
------------------------------------------------------------------------------
db2look -d PALEADS -x -u LLUSAGE -o grant.ddl
db2look -d PALEADS -e -a -u paulb072 -o all.ddl

db2 list tables for all |grep 2006-01
#########################################################################################
db2 "select STATS_TIME,TABNAME from syscat.tables where TABSCHEMA = 'TRADERT'"
#########################################################################################
nypudbgtwy1-m% ypcat group | grep dba_opsware | grep vkuratah
#########################################################################################
db2 "select DBADMAUTH,CONNECTAUTH from syscat.dbauth where GRANTEE='PRS_RW'"
#########################################################################################
Any pending backups that are still running can be checked as follows:

tkdbududb1-m:/local/udb/DTULB000/home/dba/scripts/UTEUCLSP> db2 list utilities show detail
##########################################################################################
nyfofxdev1:/home/saulb036> ypcat passwd | grep daulb027
daulb027:PNQJysQHjq7zU:52159:1872:new UDB development instances__rratanab:/local/udb/DAULB027/home:/bin/ksh
##########################################################################################
db2 REGISTER DB2 SERVER IN LDAP AS PAULB063 PROTOCOL TCPIP HOSTNAME paulb063 SVCENAME 60000 REMOTE PAULB063 INSTANCE PAULB063 NODETYPE SERVER OSTYPE SUN USER 'employeeNumber=10232226,ou=intranet,ou=people,dc=lehman,dc=com' PASSWORD 123wipro

db2 CATALOG LDAP DATABASE PAFOFX AT NODE paulb063 USER 'employeeNumber=10232226,ou=intranet,ou=people,dc=lehman,dc=com' PASSWORD 123wipro

db2 REGISTER DB2 SERVER IN LDAP AS PAULB064 PROTOCOL TCPIP HOSTNAME paulb064 SVCENAME 60004 REMOTE PAULB064 INSTANCE PAULB064 NODETYPE SERVER OSTYPE SUN USER 'employeeNumber=10232226,ou=intranet,ou=people,dc=lehman,dc=com' PASSWORD 123wipro

db2 CATALOG LDAP DATABASE DACOUGCG AT NODE daulb015 USER 'employeeNumber=10232226,ou=intranet,ou=people,dc=lehman,dc=com' PASSWORD 123wipro

db2 UNCATALOG LDAP DATABASE SAPRS USER 'employeeNumber=10232226,ou=intranet,ou=people,dc=lehman,dc=com' PASSWORD wipro123

db2 DEREGISTER LDAP node SAULB044 USER 'employeeNumber=10232226,ou=intranet,ou=people,dc=lehman,dc=com' PASSWORD wipro123
##########################################################################################
db2evmon -db DAINFREP -evm dlmon2 stop

db2evmon -path /local/udb/SAULB009/archive/DAINFREP/event_monitor > deadlock_mar02.out

db2evmon -path /local/udb/SAULB005/archive/SAACCNT/event_monitor/$file(s) > deadlock_$file.out

njdbu2pudb4-m:/local/udb/SAULB009/archive/DAINFREP/event_monitor> cat creevm2
CONNECT TO DAINFREP;

DROP EVENT MONITOR dlmon2;

CREATE EVENT MONITOR dlmon2 FOR DEADLOCKS WITH DETAILS WRITE TO FILE '/local/udb/SAULB009/archive/DAINFREP/event_monitor/' BUFFERSIZE 8 BLOCKED MANUALSTART;
njdbu2pudb4-m:/local/udb/SAULB009/archive/DAINFREP/event_monitor> cat startmon
db2 connect to saaccnt > /dev/null
echo "Event monitor dlmon2 started at `date`" >> eventmon.log
db2 "set event monitor dlmon2 state 1"        >> eventmon.log
db2 connect reset > /dev/null
njdbu2pudb4-m:/local/udb/SAULB009/archive/DAINFREP/event_monitor> cat db2event.ctl
DLMON2              DAINFREP            /local/udb/SAULB009/catalog/DAINFREP/saulb009/NODE0000/SQL00001/                                                                                                                                                                                                njdbu2pudb4-m:/local/udb/SAULB009/archive/DAINFREP/event_monitor> cat c.eventmonitor
connect to dainfrep;
drop event monitor evedainfrep;
--create event monitor evedainfrep
for statements
where appl_name='pmrepagent' and AUTH_ID='ETLDEV'
write to file '/local/udb/SAULB009/archive/DAINFREP/event_monitor/' ;

##########################################################################################
db2 connect to DALAS
db2 prune history 200512 ( pruning history till dec 1 2005 )
##########################################################################################
How to know what archive logs are safe to prune:

1. select the log from ibmsnap_restart:

select MIN_INFLIGHTSEQ from asn.ibmsnap_restart

E.g.

nydbupudb3-m:/local/udb/PAULB001/home> db2 "select MIN_INFLIGHTSEQ from asn.ibmsnap_restart"

MIN_INFLIGHTSEQ
-------------------------------------------------------------------------------------------
x'000000000650AECEE8AC'

1 record(s) selected.

2.Using the lsn from the command above - translate that into the archive log number.
i) Change to the log catalog directory:

cd /local/udb/<INSTANCE>/databases/<DATABASE>/catalog/<instance>/NODE0000/SQL00001

E.g. cd /local/udb/PAULB001/databases/PAEUCLSP/catalog/paulb001/NODE0000/SQL00001

(Note - for commodity storage systems, such as staging and development, it is /local/udb/PAULB001/catalog/<DATABASE>/<instance>/NODE0000/SQL00001)

ii) Run the db2flsn command with the last 16 digits of the hexidecemal sequence number

db2flsn 00000650AECEE8AC

Given LSN is contained in log page 30031 in log file S0057704.LOG

#############################################################################################
I have put commands to start and stop DB2 governer of PAGLMF1:

nydbu2pudb4-m:/local/udb/PAULB026/home/start_stop_db2_governer

To start as instance owner
-----------------------------------------------------
1)USER=paulb026
export USER;

2)db2gov start PAGLMF1 dbpartitionnum 0 /local/udb/PAULB026/home/sqllib/cfg/PAGLMF1_gov.cfg PAGLMF1_gov

3)Log file will be under $HOME/sqllib/log

To stop as instance owner
-----------------------------------------------------

db2gov stop PAGLMF1 dbpartitionnum 0
##############################################################################################
To check Data compass entry on host.

nydbu2pudb4-m:/local/udb/PAULB026/home/log> ServerOnHost nydbu2pudb4-m
##############################################################################################
I have given the requested users Kerberos root access on hosts nydbu2pudb5-m and njdbu2pudb5-m.

Please follow the below steps -

login to any host using your unix id
$ cd /opt/krb5/bin
$ ./kinit -A <your unix id>
$ ./proxy-rsh.pl -l root -x <hostname>

You should get # promot for root.
###########################################################################################
Load db2support file to PMR

db2support . -d database_name -c -s

nyudbdev4:/local/udb/DAULB006/archive/DAGCCM> ftp firewall
Connected to ustelnetproxy.lehman.com.
220-Usage: USER username@hostname
220        PASS userpassword
Name (firewall:vkuratah): anonymous@testcase.boulder.ibm.com
331 Guest login ok, send your e-mail address as password.
Password:
230 Virtual user vftp logged in.
ftp> cd /ps/toibm/db2
250 CWD command successful.
ftp> mkdir 03348
257 MKD command successful.
ftp> cd 03348
250 CWD command successful.
ftp> bin
200 Command okay.
ftp> put db2support.zip
200 PORT command successful.
150 File status okay; about to open data connection.
226 Transfer complete, closing data connection.
local: db2support.zip remote: db2support.zip
22252308 bytes sent in 15 seconds (1415.87 Kbytes/s)
############################################################################################
Start db2cmd from windows start->Run> and issue the following commands
     db2 refresh ldap node directory
     db2 refresh ldap db directory
     db2 connect to DAOVMTX
############################################################################################
db2 REGISTER DB2 SERVER IN LDAP AS PAULB058 PROTOCOL TCPIP HOSTNAME paulb058
############################################################################################
db2 backup db SAACCNT tablespace TS01_4K_ACCNT online to /dev/null

############################################################################################
If any body asks you the tablespace size report in MB's
Run this SQL and send the report to them…

select substr(tablespace_name,1,20) TBSPACE,case(tablespace_type) when 0 then 'DMS'else 'SMS'end as "TYPE",int((total_pages*page_size)/1024/1024) as "Total Allocated(MB)",int((used_pages*
page_size)/1024/1024) as "Total Used(MB)",int((free_pages*page_size)/1024/1024)as "Free Space(MB)" from table(snapshot_tbs_cfg(' ',-1)) as tbs order by 4 asc;

#############################################################################################
Adding containers

db2 "ALTER TABLESPACE TS01_8K_STG_DATA ADD (FILE '/local/udb/PAULB000/databases/PALEADS/data/ts01_8K_stg_data.2' 1310720)"

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

Perform RUNSTATS of 15% (15% sampling)

db2 "RUNSTATS ON TABLE LLUSAGE.F_WEBLOG_INT_0509 WITH DISTRIBUTION TABLESAMPLE BERNOULLI(15)"

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

/opt/krb5/bin/kinit -A userid
/opt/krb5/bin//proxy-rlogin.pl -l root -x nydbu2pudb1-m or 2-m

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

db2 " catalog tcpip node paulb000 remote paulb000 server 60012"
db2 " catalog db paleads at node paulb000"
db2 connect to paleads user paulb000

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

njdbu3pudb3-m:/local/udb/PAULB038/home> cat updatev8.sh
db2 deactivate db PABLUEST;
db2updv8 -d PABLUEST;
db2 activate db PABLUEST;
cd $HOME/sqllib/bnd
db2 connect to PABLUEST;
db2 bind @db2ubind.lst blocking all grant public;
db2 bind @db2cli.lst blocking all grant public;
db2 bind $HOME/sqllib/bnd/db2schema.bnd BLOCKING ALL GRANT PUBLIC sqlerror continue;
db2 terminate;

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

db2 "alter table PNS.STEP_OUTS alter column STEP_SQN set cycle"

db2 "select max(FAILS_ID) from CLIENTSEG.FAILS";

db2 "ALTER TABLE CLIENTSEG.FAILS ALTER COLUMN FAILS_ID RESTART WITH 11597";

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

db2 "select tabname from syscat.tables where status='C' with ur"

db2 "select count(*) from blues.blue_sheet_detail_udb where blues_load_id > 1950 and blues_load_id < 1975 with ur"

db2 set integrity for blues.blue_sheet_detail_udb immediate checked

db2 "load from 2000.ixf of ixf modified by generatedmissing messages 2000.mesg insert into blues.blue_sheet_detail_udb nonrecoverable indexing mode incremental"

db2 "select count(*) from blues.blue_sheet_detail_udb where blues_load_id > 1975 and blues_load_id < 2000 with ur"

db2 "load from 2025.ixf of ixf modified by generatedmissing messages 2025.mesg insert into blues.blue_sheet_detail_udb nonrecoverable indexing mode incremental"

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

db2 "set integrity for ETGLASQ.HDI_CATEGORY off";
db2 "set integrity for ETGLASQ.HDI_LINKS off";
db2 "set integrity for ETGLASQ.HDI_QUESANS off";

db2 "load from HDI_CATEGORY.ixf of ixf modified by identityoverride replace into ETGLASQ.HDI_CATEGORY nonrecoverable";
db2 "load from HDI_LINKS.ixf of ixf modified by identityoverride replace into ETGLASQ.HDI_LINKS nonrecoverable";
db2 "load from HDI_QUESANS.ixf of ixf modified by identityoverride replace into ETGLASQ.HDI_QUESANS nonrecoverable" ;

db2 "set integrity for ETGLASQ.HDI_CATEGORY immediate checked";
db2 "set integrity for ETGLASQ.HDI_QUESANS immediate checked";
db2 "set integrity for ETGLASQ.HDI_LINKS immediate checked";

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

db2 " select ' export to '|| TABNAME || '.ixf of ixf messages ' || TABNAME || '.txt select * from '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || ';' from syscat.tables where TABSCHEMA ='ACCOUNT'" >export_account.sh

#####################################################################################################
Clearing Tables check pending state.

db2 "select tabname from syscat.tables where status='C' with ur"

set integrity for account.$TABLE_NAME immediate unchecked.

db2 set integrity for m3user7.lbtrans_p13 immediate checked

run set integrity with checked for exception several times

set integrity for account.$TABLE_NAME immediate checked for exception in account.$TABLE_NAME use accex.$TABLE_NAME

######################################################################################################
verify the backup command.

db2ckbkp SAPNS.0.saulb001.NODE0000.CATN0000.20050925045203.001
#######################################################################################################
db2 "export to HSE_EXEC.ixf of ixf messages HSE_EXEC.txt select * from PNS.HSE_EXEC";

db2 "load from HSE_EXEC.ixf of ixf messages HSE_EXEC.mes replace into PNS.HSE_EXEC nonrecoverable"

db2 connect to <database>

db2 load query table PNS.HSE_EXEC to red.lst

db2 "runstats on table PNS.HSE_EXEC with distribution and indexes all allow write access"
####################################################################################################
db2 " select ' reorg indexes all for table '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || ';' from syscat.tables where TABSCHEMA ='ETGLASP'" >reorg_indexes.sh

db2 " select ' export to '|| TABNAME || '.ixf of ixf messages ' || TABNAME || '.txt select * from '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || '; from syscat.tables where TABSCHEMA ='ACCOUNT ";

db2 " select ' export to '|| TABNAME || '.ixf of ixf messages ' || TABNAME || '.txt select * from '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || ';' from syscat.tables where TABSCHEMA ='ACCOUNT'" >export_account.sh

db2 " select ' load from '|| TABNAME || '.ixf of ixf savecount 100000 messages ' || TABNAME || '.msg create replace '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || ';' from syscat.tables where TABSCHEMA ='ACCOUNT'" >load_account.sh
####################################################################################################
select ' load client from /local/udb/DAULB012/archive/export_paccnt/ '|| TABNAME || '.ixf of ixf savecount 100000 messages ' || TABNAME || '.msg replace into '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || ';' from syscat.tables where TABSCHEMA ='ACCOUNT' and type='T';

import from DIM_SCEN_ASIA.ixf of ixf insert into esbase.DIM_SCEN_ASIA

db2 " import from HSE_EXEC.ixf of ixf commitcount 50000 messages HSE_EXEC_imp.txt insert into PNS.HSE_EXEC";

db2 "export to hbash_HSE_EXEC.ixf of ixf messages hbash_HSE_EXEC.txt select * from HBASCH.HSE_EXEC";

db2 runstats on table HBASCH.HSE_EXEC" and indexes all;

db2 "load client from /local/udb/DAULB012/archive/export/HSE_EXEC.ixf of ixf savecount 1000000 messages HSE_EXEC.mes replace into PNS.HSE_EXEC nonrecoverable"

db2 "load client from /local/udb/DAULB012/archive/export/HSE_EXEC.ixf of ixf savecount 1000000 messages HSE_EXEC.mes restart into PNS.HSE_EXEC nonrecoverable"

connect to paglmf1;

export to LBTRANS_P14_p1.del of del select * from M3USER7.LBTRANS_P14 where PYRPER <= '200603' with ur;
export to LBTRANS_P14_p2.del of del select * from M3USER7.LBTRANS_P14 where PYRPER > '200603' with ur;
export to LBTRANS_P15_p1.del of del select count(*) from M3USER7.LBTRANS_P15 where PYRPER <= '200609' with ur;
export to LBTRANS_P15_p2.del of del select count(*) from M3USER7.LBTRANS_P15 where PYRPER > '200609' with ur;

connect to daglmf1;

load client from LBTRANS_P14_p1.del of del replace into M3USER7.LBTRANS_P14 nonrecoverable;
load client from LBTRANS_P14_p2.del of del insert into M3USER7.LBTRANS_P14 nonrecoverable;
load client from LBTRANS_P15_p1.del of del replace into M3USER7.LBTRANS_P15 nonrecoverable;
load client from LBTRANS_P15_p2.del of del insert into M3USER7.LBTRANS_P15 nonrecoverable;

######################################################################################################
njudbeng1:/local/udb/EAULB004/catalog/EALNQREP> db2 "CALL GET_DBSIZE_INFO(?, ?, ?,0)"
######################################################################################################
db2 "grant usage on sequence BLUES.BLUE_SHEET_LOAD_SEQ to group BLUEST_ADM";
db2 "grant usage on sequence BLUES.BLUE_SHEET_LOAD_SEQ to group BLUEST_RW";
db2 "grant usage on sequence BLUES.BLUE_SHEET_LOAD_SEQ to group BLUEST_RO";
######################################################################################################
select 'export to ' || strip(name) || '.ixf of ixf messages ' || strip(name) || '.msg select * from LOANIQS.' || strip(name)
|| ';' from sysibm.systables where creator = 'LOANIQS' and type = 'T' order by card desc;

select 'export to ' || strip(name) || '.ixf of ixf messages ' || strip(name) || '.msg select * from ' || strip(creator) || '.'
|| strip(name) || ' ;'
from sysibm.systables where dbname = 'PESBASED';

######################################################################################################
db2 archive log for db palas
######################################################################################################
db2 restore db DACCR from /local/udb/DAULB002/archive/jul29/DACCR taken at 20050730090634 into DACCR without prompting

db2 rollforward db DACCR to end of logs and complete overflow log path'(/local/udb/DAULB002/archive/jul29/DACCR)'
######################################################################################################
db2 "load from loadfile.txt of asc method L (1 4, 5 14) messages TEST_LEON.mes insert into ERM.TEST_LEON nonrecoverable"

db2 "load from postbpm_budget.txt of del modified by coldel| messages test.mes insert into oesdba.POSTBPM_BUDGET nonrecoverable"

db2 "load from postbpm_budget.txt of del modified by coldel| messages test.mes terminate into oesdba.POSTBPM_BUDGET nonrecoverable"

db2 "runstats on table oesdba.POSTBPM_BUDGET with distribution and indexes all allow write access"

db2 load query table oesdba.POSTBPM_BUDGET summaryonly
#####################################################################################################
ALTER TABLE <table_name> ADD <column_name> <data_type> <null_attribute> ;

ALTER TABLE IACCS.CUST_ACCT ADD CACCT_B_COMM_CD CHAR(2) FOR SBCS DATA NOT NULL WITH DEFAULT;

ALTER TABLE IACCS.CUST_ACCT ADD CACCT_S_COMM_CD CHAR(2) FOR SBCS DATA NOT NULL WITH DEFAULT;

ALTER TABLE IACCS.CUST_ACCT_HST ADD CACCT_B_COMM_CD CHAR(2) FOR SBCS DATA NOT NULL WITH DEFAULT;

ALTER TABLE IACCS.CUST_ACCT_HST ADD CACCT_S_COMM_CD CHAR(2) FOR SBCS DATA NOT NULL WITH DEFAULT;

CREATE INDEX IACCT.T2880021 ON IACCT.ACCT_MTS_REL (ACCT_REL_TMS_ID ASC) USING STOGROUP TIACCTG PRIQTY 720 SECQTY 720 ERASE NO FREEPAGE 10 PCTFREE 10 GBPCACHE CHANGED BUFFERPOOL BP2 CLOSE YES COPY NO PIECESIZE 512 M;

Create Index "ACCOUNT"."ACCT_MTS_REL_I" On "ACCOUNT"."ACCT_MTS_REL" ("ACCT_REL_TMS_ID" ASC);
######################################################################################################
db2look -d DATRMQA -z TRM -u TRM -tw -o SAPHNIX_tables_ddl.sql -p

db2look -d pabluest -e -z blues -t blue_sheet_detail_qtr1_05 -u paulb038 -o re.lst --- To extract single table DDL

db2look -d pabluest -x -u paulb038 -o grant.lst --- Authorization Statements on Tables/Views

db2look -d pabluest -e -z blues -u paulb038 -o blues.lst -- DDL for tables,indexes and views for blues schema
####################################################################################################
db2 list tables for schema $schema_name

db2 describe table $table_name

db2 describe indexes for table $table_name

db2 describe indexes for table $table_name show detail
####################################################################################################
db2 "import from GLBL_ACCT_DFLT.ixf of ixf method N(DFLT_LGL_SECR_ID,DFLT_CNTRY_ISO_CD,DFLT_CUST_CAT_CD,DFLT_FIRM_ACCT_TY,DFLT_REL_OWN_NM,DFLT_REL_TY,DFLT_SRC_SYS_CD,DFLT_ST_CD,DFLT_ADDR_RL_TY,DFLT_ADDR_LN_TY,DFLT_DSCR_CMDY_IND,DFLT_DSCR_SEC_IND,DFLT_DSCR_MGR_TY,DFLT_DVP_RVP_IND,DFLT_TEL_TY,DFLT_PRME_BKR_IND,DFLT_ACCT_RSTR_TY ,DFLT_TAX_TY,DFLT_CLS_TY,DFLT_PRME_BKRG_TY,DFLT_TEFRA_RPT_CD,DFLT_WITHTEFRA_CD,DFLT_CUST_TIER_CD,DFLT_1001_STAT ,DFLT_FIRMACCT_SBTY,DFLT_AMSTERDAM_CD ,DFLT_CACCT_CASHIND,DFLT_CACCT_MRGNIND,DFLT_PAY_OUT_CD,DFLT_DVD_INST_ID,DFLT_INT_INST_ID,DFLT_MNY_INST_ID,DFLT_SEC_INST_ID,DFLT_FIN_CMP_IND,DFLT_NYSE_CLS_CD,DFLT_INTR_CMP_IND ,DFLT_TAX_FRMT_CD,DFLT_ABDN_PRPY_ST,DFLT_STY_SEC_TY,DFLT_BUY_CALL_ID,DFLT_BUY_PUT_ID,DFLT_SELL_PUT_ID,DFLT_SELL_CALL_ID,DFLT_SPRD_AUTH_ID ,DFLT_RISK_LVL_CD,DFLT_ALLW_IND,DFLT_MGR_NOTIF_IND,DFLT_LTR_FILE_IND,DFLT_LGL_LANG_IND,DFLT_BRCH_MGR_IND,DFLT_CMPLN_IND,DFLT_ADVSR_IND,DFLT_REL_RL_TY,DFLT_TAX_DFR_CD) messages msg1.txt insert into account.GLBL_ACCT_DFLT";

db2 " import from INST_HDR.ixf of ixf method N(INST_REF_ID,ACCT_REF_ID,PRTY_REF_ID,CUR_CD,INST_TY,NST_EFF_DT_TY,INST_EFF_DT,STY_SEC_TY,SBT_SEC_SBTY,INST_TRNS_TY,INST_DLV_CD,INST_STAT,INST_APPR_OPR_ID,INST_CLR_METH_ID,INST_APPR_TS,LST_MOD_OPR_ID,LST_MOD_CHG_CD,LST_MOD_PRCS_ID,LST_MOD_TS,INST_CONV_ID,INST_OWN_NM,INST_MOD_OPR_ID,INST_MOD_CHG_CD,INST_MOD_TS,INST_STAT_REAS_CD,INST_PRIM_SCRY_CD,INST_END_DT,INST_SWIFT_MSG_CD,INST_ORIG_REF_ID,INST_RPLC_REF_ID,INST_MKT_CD,SRC_SYS_CD,ALT_CUR_CD,INST_EXTR_CSH_ACCT,INST_EXTR_SEC_ACCT) messages msg2.txt insert into account.INST_HDR";

db2 " import from INST_HDR_HST.ixf of ixf method N (INST_REF_ID,ACCT_REF_ID,PRTY_REF_ID,CUR_CD,INST_TY,INST_EFF_DT_TY,INST_EFF_DT,STY_SEC_TY,SBT_SEC_SBTY,INST_TRNS_TY,INST_DLV_CD,INST_STAT,INST_APPR_OPR_ID,INST_CLR_METH_ID,INST_APPR_TS,LST_MOD_OPR_ID,LST_MOD_CHG_CD,LST_MOD_PRCS_ID,LST_MOD_TS,INST_CONV_ID,INST_OWN_NM,INST_MOD_OPR_ID,INST_MOD_CHG_CD,INST_MOD_TS,INST_STAT_REAS_CD,INST_PRIM_SCRY_CD,INST_HDR_HST_TS,INST_END_DT,INST_SWIFT_MSG_CD,INST_ORIG_REF_ID,INST_RPLC_REF_ID,INST_MKT_CD,SRC_SYS_CD,ALT_CUR_CD,INST_EXTR_CSH_ACCT,INST_EXTR_SEC_ACCT) commitcount 1000 messages msg3.txt insert into account.INST_HDR_HST";

db2 " import from LGL_ENT_XREF.ixf of ixf method N(SRC_SYS_VL_ID,SRC_SYS_CD,LGL_ENT_SECR_ID,LST_MOD_TS,LST_MOD_CHG_CD,LST_MOD_PRCS_ID,LST_MOD_OPR_ID,LGL_XREF_ID) commitcount 1000 messages msg4.txt insert into account.LGL_ENT_XREF";

db2 " import from ACCT_XREF.ixf of ixf method N(ACCT_XREF_SRC_ID,ACCT_XREF_KEY_TY,SRC_SYS_CD,ACCT_XREF_SRCMP_ID,ACCT_REF_ID,LST_MOD_TS,LST_MOD_PRCS_ID,LST_MOD_OPR_ID,LST_MOD_CHG_CD,ACCT_XREF_STRT_DT,ACCT_XREF_END_DT,ACCT_TRGT_SYS_STAT) commitcount 5000 messages ACCT_XREF1.txt insert into account.ACCT_XREF";

import from DIM_SCEN_ASIA.ixf of ixf insert into esbase.DIM_SCEN_ASIA ;

import from DIM_SRC_CD.ixf of ixf insert into esbase.DIM_SRC_CD ;

import from DIM_SRC_CD_ASIA.ixf of ixf insert into esbase.DIM_SRC_CD_ASIA ;

import from LOOKUP_PYRPER.ixf of ixf insert into esbase.LOOKUP_PYRPER ;
####################################################################################################
db2 connect to daglmf1 user daulb012 using db2admin;

db2 load from /local/udb/DAULB012/archive/EXPORT_RUBEN/GL/M3USER4.LBTRANS45 of del modified by usedefaults replace into M3USER4.LBTRANS45 ;

db2 load from /local/udb/DAULB012/archive/EXPORT_RUBEN/GL/M3USERG.LBTRANS of del modified by usedefaults replace into M3USERG.LBTRANS nonrecoverable ;

db2 load from /local/udb/DAULB012/archive/EXPORT_RUBEN/GL/M3USERG.LBTRANSH of del modified by usedefaults replace into M3USERG.LBTRANSH nonrecoverable;

db2 "load from postbpm_budget.txt of del messages test.mes insert into postbpm_budget;

db2 "load from /opt/eurodump/DYNAMIX/DMX_TRADE_DETAIL.ixf of ixf LOBS from /opt/eurodump/DYNAMIX/ savecount 100000 messages xyz.mes replace into LNDMXPRD.DMX_TRADE_DETAIL nonrecoverable";
####################################################################################################
import from CPM_SUM_NODES.ixf of ixf insert into esbase.CPM_SUM_NODES ;
import from DIM_ACCT.ixf of ixf insert into esbase.DIM_ACCT ;
####################################################################################
select a.TABNAME, a.npages * b.PAGESIZE/1024/1024 , a.fpages * b.PAGESIZE/1024/1024 from syscat.tables a, syscat.tablespaces b
where a.TBSPACEID=b.TBSPACEID and a.TABSCHEMA='LLUSAGE'and a.TABNAME in ('F_WEBLOG_INT_0506','F_WEBLOG_EXT_0506');
######################################################################################
db2 "export to FIRM_ACCT_HST.del of del messages FIRM_ACCT_HST.txt select * from account.FIRM_ACCT"
db2 "export to FIRM_ACCT_HST.del of del messages FIRM_ACCT_HST.txt select * from account.FIRM_ACCT_HST"
######################################################################################
db2 get snapshot for application agentid 110 | grep 'logical reads'
db2 get snapshot for application agentid 110 | grep 'Rows read'

db2 get snapshot for all on <db>
db2 get snapshot for locks on <db>
db2 get snapshot for dynamic sql on <db>

db2 get db cfg for inf_repo show detail|grep MAXAPPLS
######################################################################################
db2trc on -f /tmp/traces/patrm.trace

check space used from trace during 5 mins interval

*** check with Ruben and myself to decide to turn of the trace

3) If TRM team update that they have issue again, dump and turn off the trace

db2trc dmp /tmp/traces/patrm.dmp

db2trc flw /tmp/traces/patrm.dmp /tmp/traces/patrm.flw

db2trc off
#######################################################################################
Instacne cration.

./db2icrt -u daflb017 -w64 daulb017
#######################################################################################
db2 " select ' export to '|| TABNAME || ' of ixf messages ' || TABNAME from syscat.tables where TBSPACE = 'TS01_4K' "
db2 " select ' export to '|| TABNAME || '.ixf of ixf messages ' || TABNAME || '.txt select * from '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || ';' from syscat.tables where TBSPACE = 'TS01_4K' "
db2 " select 'import from '|| TABNAME || '.ixf of ixf messages ' || TABNAME || '.msg commitcount 50000 create into '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || ' IN TS01_4K index in IX01_4K ;' from syscat.tables where TBSPACE = 'TS01_4K' "
db2 "export to CRYSTAL_REPORTS.ixf of ixf messages CRYSTAL_REPORTS.txt select * from PNS.CRYSTAL_REPORTS";
db2 "select TABNAME from syscat.tables where TBSPACE='TS01_4K'"
db2 "select 'CREATE ALIAS TRACDEV.' || ltrim(rtrim(tabname)) || ' FOR ' || ltrim(rtrim(TABSCHEMA>|| '.' || ltrim(rtrim(TABNAME)) from syscat.tables where tabschema = 'TRACPROD'" > alias.sql

select 'CREATE ALIAS FOR TRACDEV.' || ltrim(rtrim(tabname)) || 'FOR ' ltrim(rtrim(TABSCHEMA)) || '.' || ltrim(rtrim(TABNAME)) from syscat.tables where tabschema ='TRACPROD'

db2 " select ' export to '|| TABNAME '.' ixf of ixf messages ' || TABNAME from syscat.tables where TBSPACE = 'TS01_4K' "
db2 " select 'import from '|| TABNAME || '.ixf of ixf messages ' || TABNAME || '.msg create into '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || IN TS01_4K index in IX01_4K";
db2 " select ' export to '|| TABNAME || ' of ixf messages ' || TABNAME from syscat.tables where TBSPACE = 'TS01_4K' "
db2 " select ' export to '|| TABNAME || '.ixf of ixf messages ' || TABNAME || '.txt select * from '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || ';' from syscat.tables where TBSPACE = 'TS01_4K' "
db2 " select 'import from '|| TABNAME || '.ixf of ixf messages ' || TABNAME || '.msg commitcount 50000 create into '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || ' IN TS01_4K index in IX01_4K ;' from syscat.tables where TBSPACE = 'TS01_4K' "
db2 "export to CRYSTAL_REPORTS.ixf of ixf messages CRYSTAL_REPORTS.txt select * from PNS.CRYSTAL_REPORTS";
db2 "select TABNAME from syscat.tables where TBSPACE='TS01_4K'"
db2 "select 'CREATE ALIAS TRACDEV.' || ltrim(rtrim(tabname)) || ' FOR ' || ltrim(rtrim(TABSCHEMA> || '.' || ltrim(rtrim(TABNAME)) from syscat.tables where tabschema = 'TRACPROD'" > alias.sql


select 'CREATE ALIAS FOR TRACDEV.' || ltrim(rtrim(tabname)) || 'FOR ' ltrim(rtrim(TABSCHEMA)) || '.' || ltrim(rtrim(TABNAME)) from syscat.tables where tabschema ='TRACPROD'


db2 " select ' export to '|| TABNAME '.' ixf of ixf messages ' || TABNAME from syscat.tables where TBSPACE = 'TS01_4K' "
db2 " select 'import from '|| TABNAME || '.ixf of ixf messages ' || TABNAME || '.msg create into '|| ltrim(rtrim(TABSCHEMA)) || '.' || TABNAME || IN TS01_4K index in IX01_4K";

#############################################################################################
nyudbdev3:/local/udb/DAULB002/archive/SAVCAS01> db2 backup db SAVCAS01 online compress include logs

Backup successful. The timestamp for this backup image is : 20050729232342
##############################################################################################
db2 "CREATE REGULAR TABLESPACE TS01_4K PAGESIZE 4 K MANAGED BY DATABASE USING ( FILE '/local/udb/DAULB002/data/SAVCAS01/ts01_4K_vcas01.1' 1572864 ) EXTENTSIZE 16 OVERHEAD 8.0 PREFETCHSIZE 32 TRANSFERRATE 0.1 BUFFERPOOL userbp_4K DROPPED TABLE RECOVERY OFF";
##############################################################################################
db2 "select 'CREATE ALIAS TRACDEV.' || ltrim(rtrim(tabname)) || ' FOR ' || ltrim(rtrim(TABSCHEMA> || '.' || ltrim(rtrim(TABNAME)) from syscat.tables where tabschema = 'TRACPROD'" > alias.sql


select 'CREATE ALIAS FOR TRACDEV.' || ltrim(rtrim(tabname)) || 'FOR ' ltrim(rtrim(TABSCHEMA)) || '.' || ltrim(rtrim(TABNAME)) from syscat.tables where tabschema ='TRACPROD'

#################################################################################################
H:\>db2 refresh ldap node directory
DB20000I The REFRESH LDAP NODE DIRECTORY command completed successfully.

H:\>db2 refresh ldap db directory
DB20000I The REFRESH LDAP DATABASE DIRECTORY command completed successfully.
###################################################################################################
Changing instance password.

/opt/nsmanager/1.0/bin/chpasswd
##################################################################################################
db2expln -d DAPEROES -t -q "select ComplianceID from DAULB000.finalStateOrders where date='06/28/2005' and Symbol like 'A%'" -noenv -g
###################################################################################################
bupdate dba_udb_glb night 06/09/2005 vkuratah akarmaka hlakshmi,vkuratah akarmaka hlakshmi pdakined,pdakined rratanab
####################################################################################################
1) Disable Monitoring;

Command fmsdisable is available at:

% which fmsdisable
/usr/local/bin/fmsdisable - please use the full path

fmsdisable -s njdbu3pudb:PAULB045_SG -hours 120 -note Decommission;

fmsdisable -s PAULB022 -m "DB Process" -r asncap_PAACCNT -ti 10080;

fmsdisable -s DAULB014 -days 3
fmsdisable -s PAULB092 -days 3

g2disbale -so PAULB092:PAYNAMX2 -days 1

g2disable -so PAULB092 -days 1;

fmsdisable -s njdbupudb2-m:PAULB092_datadg -hours 2 -note Test;

ti ==> mins.

g2disable -so PAULB000 -ti 120;

fmsdisable -s njdbu3pudb:PAULB000_SG -hours 2 -note Test;

Please disable monitoring of this DB and DABZOBJA for 5 hours.

g2disbale -so DAULB015:DAREPTST -ti 300

fmsdisable -s 'nydbu2pudb2-m:/local/udb/PAULB021/databases/PARTRADE/data' -m 'Disk Usage' -r '/local/udb/PAULB021/databases/PARTRADE/data' -t critical -days 1

To disable alert
BTELL<INFO>[DAULB007]: FMS-HPOV:DAULB007: Capture process for replicated DAEUCLSP is down

nybo-xsrv2:/home/nsrihari> fmsdisable -s 'DAULB007' -m 'DB Process' -r 'asncap_DAEUCLSP' -t critical -days 3

Step1:-
   fmshist |grep <any word in the alert you had received>

nybo-xsrv2:/home/nsrihari> fmshist |grep DAULB001

12/08/05 15:17 nyudbdev3:/local/udb/DAULB001/data|Disk Usage./local/udb/DAULB001/data.major                                                                                                                 |HPOV: at 94% full exceeds 90 and 1081M free less than 2500

Step2: From the output of the above command pls run the following command

To disable for 1 day

fmsdisable -s '<1>' -m '<2>' -r '<3>' -t <4> -days 1

To Disable for 3 hours
fmsdisable -s '<1>' -m '<2>' -r '<3>' -t <4> -hours 3

If your don't specify the time, the default is 2 hours

Ex:-

nybo-xsrv2:/home/nsrihari> fmsdisable -s 'nyudbdev3:/local/udb/DAULB001/data' -m 'Disk Usage' -r '/local/udb/DAULB001/data' -t major -days 1

% which fmsdisable
/usr/local/bin/fmsdisable - please use the full path

g2disable -so $INSTANCE -ti $TIME (mins);
fmsdisable -s nydbu2pudb:PAULB024_SG -hours 1 -note FailoverTest ;
##########################################################################################
2) Updating Instance parameters

db2 update dbm cfg using numdb 12

3) Uncataloging the database.

db2 uncatalog db SACRSTLR
###############################################################################
db2 backup db SACRSTLA online compress include logs

Backup successful. The timestamp for this backup image is : 20050422172425
Taking tablespace backup online

db2 backup database <Database name> tablespace(tablespace-name) online

Exmple -- db2 backup db DATRM tablespace TS01_4K_TRM online

db2 list history backup all for sample
####################################################################################
Recycling Instance

recycle_instance.ksh saulb003 $HOME/cfg/SAULB003_recycle_instance.cfg
###########################################################################
To find previous btells
blist dba_udb log 4/21/05 | grep PAULB001 -- to find out previous Bteels
#################################################################################
nyudbdev1:/home/daulb012> ls -l /home/daulb012/sqllib/sqlnodir/sqlnodir
-rw-rw-r-- 1 root other 1898 Mar 29 14:51 /home/daulb012/sqllib/sqlnodir/sqlnodir
Owned by root ... should be owned by daulb012:daulb012
#################################################################################
runstats on table LOANIQS.TLS_PROD_GUARANTEE and indexes all;
runstats on table LOANIQS.TLS_PROD_LOAN_PURP and indexes all;

db2 "runstats on table BCU.BCUCASHDEPOSITACCOUNT with distribution and indexes all allow write access"

6553600 -25Gb 4KB pages

1048576 4gb   4kb pages
#####################################################################################3
If you already have DB2 client installed: (which you will need for DBArtisan to work)

Call db2cmd from XP command line:

db2cmd> db2 connect to $DBNAME user $USERNAME
Password:

Go to DBArtisan

Discover DataSource

You should see your database which you try to connect in db2cmd before in the bottom of the list.

XP client: LEC: IBM/UDBDB2connectdev8.1FP7

Please ask Desktop group top install client on your XP if you need to access DB from XP.
######################################################################################################
recycle_instance.ksh daulb014 $HOME/cfg/DAULB014_recycle_instance.cfg
######################################################################################333
Dropping a Database

1) Check the applications connected to the Database.

db2 list applications for database DAVSTDR

2) Do the force application to kill the application connected to the database

db2 "force application (401,189)"--401&189 are Application handler.

db2 force applications all -- will kill all the database applications.

3) Deactivate the database

db2 deactivate db DAVSTDR

4) Drop the database.

db2 drop db <Db-name>.
#########################################################################################################
to Disable the ldap entries shown while querying db2 list db directory

set the below command
Switch:
DB2_ENABLE_LDAP=OFF

nyudbdev1:/home/saulb001/sqllib> db2set -all
[i] FASTGROUPS=TRUE
[i] DB2_ENABLE_LDAP=YES
[i] DB2LDAP_BASEDN=ou=db2,ou=dbms,ou=services,dc=lehman,dc=com
[i] DB2LDAPHOST=amldap.lehman.com
[i] DB2_HASH_JOIN=ON
[i] DB2ENVLIST=KRB5_CONFIG DB2GPLUGINCONFIG
[i] DB2COMM=TCPIP
[i] DB2_PARALLEL_IO=*
[g] DB2_EEE_LICENSE_POLICY=844429225164800
[g] DB2ADMINSERVER=dasadmin
########################################################################################################
Listing instances
_______________________________

To get a list of all the instances that are available on a system using the command line, enter:

db2ilist
db2ilist -a

db2ilist -w 64 -p

Setting the current instance
_______________________________________

To display help information for the command, use:

db2set ?

Updating instance configuration on UNIX
___________________________________________

Running the db2iupdt command updates the specified instance by performing the following:

--Replaces the files in the sqllib subdirectory under the instance owner's home directory.
--If the node type is changed, then a new database manager configuration file is created. This is done by merging relevant values from the existing database manager configuration file with the default database manager configuration file for the new node type. If a new database manager configuration file is created, the old file is backed up to the backup subdirectory of the sqllib subdirectory under the instance owner's home directory.
Procedure
-- The db2iupdt command is found in 2 /usr/opt/db2_08_01/instance/ directory on AIX. 2 The db2iupdt command is found in 2 /opt/IBM/db2/V8.1/instance/ directory on HP-UX, 2 Solaris Operating Environment, or Linux.

--The command is used as shown:

---->db2iupdt InstName

Examples:

If you installed DB2 Universal Database Workgroup Server Edition or DB2 Universal Database Enterprise Server Edition after the instance was created, enter the following command to update that instance:
   db2iupdt -u db2fenc1 db2inst1
If you installed the DB2 Connect Enterprise Edition after creating the instance, you can use the instance name as the Fenced ID also:
   db2iupdt -u db2inst1 db2inst1
To update client instances, you can use the following command:
   db2iupdt db2inst1
##################################################################################################
Check the schemas existing

db2 "select distinct tabschema from syscat.tables"

db2 list tables for schema ACCOUNT |more
##################################################################################################
Increasing tablespace size

db2 "alter tablespace TS01_4K_PNS extend (all 588800)"
###################################################################################################
db2 get snapshot for locks on datrm >red.lst
###################################################################################################
select char(tabschema,10), char(tabname,40), char(tbspace,15) from syscat.tables where tabschema NOT IN ('SYSIBM','SYSCAT','SYSSTAT' ,'SYSTOOLS') order by 3

Hi ,
You appear to be creating your tables in the wrong tablespace, I have just been paged for the DBA tablespace filling up USERSPACE1.

You should use the following syntax for table creation.

e.g. CREATE TABLE <schema>.<table_name>
        (cols…..) IN TS01_4K INDEX IN IX01_4K;

Here is a list of your tables:

select char(tabschema,10), char(tabname,40), char(tbspace,15) from syscat.tables where tabschema NOT IN ('SYSIBM','SYSCAT','SYSSTAT' ,'SYSTOOLS') order by 3

1          2                                        3
---------- ---------------------------------------- ------------------------------------------------
TTOYRA     CURRENT_IOI                              TS01_16K
TTOYRA     AUDIT_IOI                                TS01_16K
#####################################################################################################
db2 "load from test.del of del messages test.mes insert into daulb000.temp2"
#####################################################################################################
db2 get snapshot for application agentid 809 | more

db2 get snapshot for all application APPLID 809 on DALEC > app.lst

db2 get snapshot for application agentid 110 | grep 'logical reads'

db2 get snapshot for application agentid 110 | grep 'Rows read'
####################################################################################################
Talking backup of schema

db2move sajira export -sn etgjras
####################################################################################################
Calculating Table size

select a.TABNAME, a.npages * b.PAGESIZE/1024/1024 , a.fpages * b.PAGESIZE/1024/1024 from syscat.tables a, syscat.tablespaces b
where a.TBSPACEID=b.TBSPACEID
and a.TABSCHEMA='LLUSAGE'
and a.TABNAME in ('F_WEBLOG_INT_0506','F_WEBLOG_EXT_0506');
#####################################################################################################
extract the DDL either by db2look or dbartisan(whichever you feel ok

If you use db2look use the following command..

db2look -d SAACCNT -a -e -m -l -x -f -o db2look.sql

db2look -d SALEADS -z LLUSAGE -u LLUSAGE -x -e -p -o out.sql(this will extract the data fro LLUSAGE only)
######################################################################################################
Please set-up recurring nightly copy of paulb019 PACWL.NYMVSP schema to saulb002 SACWL.NYMVSP

db2 restore db PACWL from /local/udb/SAULB002/archive/SACWL taken at 20050718002254 to /local/udb/SAULB002/catalog/SACWL into SACWL logtarget /local/udb/SAULB002/archive/SACWL newlogpath /local/udb/SAULB002/log/SACWL redirect WITHOUT PROMPTING;
db2 'set tablespace containers for 0 using (path /local/udb/SAULB002/catalog/SACWL/syscatspace)';
db2 'set tablespace containers for 1 using (path /local/udb/SAULB002/temp/SACWL/stemp_4K)';
db2 'set tablespace containers for 2 using (file '/local/udb/SAULB002/data/SACWL/userspace1_4K.1' 2000)';
db2 'set tablespace containers for 3 using (file '/local/udb/SAULB002/data/SACWL/ts01_4K_cwl.1' 21264)';
db2 'set tablespace containers for 4 using (file '/local/udb/SAULB002/index/SACWL/ix01_4k_cwl.1' 15000)';
db2 'set tablespace containers for 5 using (path /local/udb/SAULB002/data/SACWL/systoolspace)';
db2 'set tablespace containers for 6 using (file '/local/udb/SAULB002/data/SACWL/ts01_4K_bmc.1' 2000)';
db2 'set tablespace containers for 7 using (path /local/udb/SAULB002/temp/SACWL/systoolstmpspace)';
db2 restore db PACWL continue;
db2 rollforward db SACWL to end of logs and complete overflow log path '(/local/udb/SAULB002/archive/SACWL)';
########################################################################################################
running scripts:

/local/udb/PAULB019/databases/PACWL/archive> ftp SAULB002
Connected to nyudbdev1.lehman.com.
220 nyudbdev1 FTP server (Version 5.60) ready.
Name (SAULB002:sduggira): saulb002
331 Password required for saulb002.
Password:
230 User saulb002 logged in.
ftp> pwd
257 "/home/saulb002" is current directory.
ftp> cd /local/udb/SAULB002/archive/SACWL
250 CWD command successful.
ftp> pwd
257 "/local/udb/SAULB002/archive/SACWL" is current directory.
ftp> hash
Hash mark printing on (8192 bytes/hash mark).
ftp> put PACWL.0.paulb019.NODE0000.CATN0000.20050718002254.001
########################################################################################################
If you are facing any connectivity problems.pls do the following

1) Unregister the database in DB artisan & other applications.
2) Close DB artisan and all other applications using the database
3) Start db2cmd from windows start->Run> and issue the following commands
     db2 refresh ldap node directory
     db2 refresh ldap db directory
     db2 connect to <db name>
     db2 get connection state (in the output you should see the new interface details)
4) If you have hardcoded port details in applications,pls change accordingly
5) Open DB artisan & other applications and register the database
#########################################################################################################
How to know what archive logs are safe to prune:

1. select the log from ibmsnap_restart:

select MIN_INFLIGHTSEQ from asn.ibmsnap_restart

E.g.

nydbupudb3-m:/local/udb/PAULB001/home> db2 "select MIN_INFLIGHTSEQ from asn.ibmsnap_restart"

MIN_INFLIGHTSEQ
-----------------------------------------------------------------------------------------------------
x'000000000650AECEE8AC'

1 record(s) selected.

2. Using the lsn from the command above - translate that into the archive log number.
i) Change to the log catalog directory:

cd /local/udb/<INSTANCE>/databases/<DATABASE>/catalog/<instance>/NODE0000/SQL00001

E.g. cd /local/udb/PAULB001/databases/PAEUCLSP/catalog/paulb001/NODE0000/SQL00001

(Note - for commodity storage systems, such as staging and development, it is /local/udb/PAULB001/catalog/<DATABASE>/<instance>/NODE0000/SQL00001)

ii) Run the db2flsn command with the last 16 digits of the hexidecemal sequence number

db2flsn 00000650AECEE8AC

Given LSN is contained in log page 30031 in log file S0057704.LOG
###########################################################################################################
I have changed the job definition .It will run fine from next time... In future, while defining the cross instance dependant jobs , Pls follow this syntax

condition: s(DBAPAULB007_Backup) and s(PNSONLINEEOD_Box^BOS)
###########################################################################################################
db2 list indoubt transactions
#############################################################################################################
db2 "select count(*) from M3USERg.chartval with ur";
db2 "select count(*) from M3USERg.jrnldtl with ur";

daulb016 --- FRAMEbag!
############################################################################################################
nyudbdev4:/home/daulb007> db2pd -agent(gives the process agent)

Database Partition 0 -- Active -- Up 0 days 00:23:40

Agents:
Current agents:     25
Idle agents:        7
Active agents:      18
Coordinator agents: 18

db2ptree --Gives the agents/process running in the databases

db2pd -d DAEUCLSP - applications(list the applications)

db2ptree(Its process tree)

db2pd -everything(gives the details of the process running)
-----------------------------------------------------------------------------------------------------
db2ptree
*********
ps command on Sun's operating system does not provide the real process name of the DB2 processes (instead ps shows only "db2sysc") a new tool db2ptree was developed.
With db2ptree the DBA is able to see all DB2 processes of one instance with their real names (e.g. db2wdog, db2pfchr etc.). The result is in an easy-to-read tree structure. db2ptree is part of the standard DB2 installation image on Sun. It is located in the sqllib/bin subdirectory.

Usage: db2ptree
-V <debug level> Set debug level (Should not be used).
-i <instance> Override DB2INSTANCE environment variable.
-p <pid> Show tree from pid down.
-s Show db2watch status for each process.
-c Show process uid credentials
-v Show process arguments as well.
db2ptree called without any parameters shows the DB2 processes of your current instance.

db2pd -stacks <<== this will dump trap files under DIAGPATH
db2pd -alldbp -alldbs > alldbp.out
################################################################################################################
If the instance crashed for the In-doubt transaction means,Pls follow the below steps
DB2stop
db2start
db2 activate database <dbname>
Note : There should not be any other applications connecting to the database. If you do have these, you can disable the TCP/IP environment.
Once crash recovery has completed, you should issue the following:

db2 LIST INDOUBT TRANSACTIONS with prompting

DB2stop
db2start
db2 activate database <dbname>
Note : There should not be any other applications connecting to the database. If you do have these, you can disable the TCP/IP environment.
Once crash recovery has completed, you should issue the following:

db2 LIST INDOUBT TRANSACTIONS with prompting

nydbu5pudb1-m:/local/udb/PAULB087/home> db2 list indoubt transactions with prompting

1. originator: XA
appl_id: GA0C567B.H99E.028E46174413 sequence_no: 1 status: i
timestamp: 05/26/2006 13:45:08 auth_id: HJM_DEV
log_full: n type: RM
xid: 0000BEA10000000A 00000019145A5E6E A6B8E6A313D75841 20436F6E6E656374
696F6E20506F6F6C 20547970652032

2. originator: XA
appl_id: GA0C567B.JB9E.028E46174605 sequence_no: 1 status: i
timestamp: 05/26/2006 13:45:08 auth_id: HJM_DEV
log_full: n type: RM
xid: 0000BEA10000000A 00000019145E5E6E A6B8E6A313D75841 20436F6E6E656374
696F6E20506F6F6C 20547970652032

Enter in-doubt transaction command or 'q' to quit.
e.g. 'c 1' heuristically commits transaction 1.
c/r/f/l/q: c
DB21042E You must specify a transaction number.

Enter in-doubt transaction command or 'q' to quit.
e.g. 'c 1' heuristically commits transaction 1.
c/r/f/l/q: c 1 and tehn r 1 and then f 1

-------------------------------------------------

Please be informed that when we use the db2relocatedb cmd the tablespace might go into rollforward pending state

Please use the following cmd to bring the tablespace back to normal

db2 " rollforward db dbname to end of logs tablespace (tablsp1,tabsp2… ) online "
#####################################################################################################
No,We don't have option in UDB

There is way to drop

ALTER TABLE <old_name> RENAME TO <another_name>

Then recreate the old table without the deleted column(s):
SELECT col1, col2, col4 INTO TABLE <old_name> FROM <another_name>

Then delete the old table:
DROP TABLE <another_name>
########################################################################################################
nydbu2pudb0-m:/local/udb/PAULB021/databases/PARTRADE/archive> fuser PARTRADE.0.paulb021.NODE0000.CATN0000.20060624100940.001PARTRADE.0.paulb021.NODE0000.CATN0000.20060624100940.001:    16042o
nydbu2pudb0-m:/local/udb/PAULB021/databases/PARTRADE/archive> ps -ef | grep 16042
paulb021 16042 29835 18 11:52:52 ?       13:35 db2ckbkp -h /local/udb/PAULB021/databases/PARTRADE/archive/PARTRADE.0.paulb021.
paulb021 2592 6628 0 12:13:15 pts/2    0:00 grep 16042
########################################################################################################
ktum8634
########################################################################################################
If you get the PACKAGE names..and wants to see which SP's are associated with that Stored Procedure
Please use this

db2 "select distinct a.routinename, b.bname from syscat.routines a,syscat.routinedep b, syscat.packages c where c.valid = 'Y' and b.routinename = a.specificname and b.bname = c.pkgname"
########################################################################################################
1) Add hotsname and download license from Nice Website.

1.1) ID:             dba_udb@lehman.com
Password:       LIC_LM_010206

1.2) http://www.nice.de/
1.3) Save license under: \\leh\corp\groups\itd\DBA-Documents\UDB Documents\license_key
(shared point)

2) Submit a TKT to Systems Management - AM for DBSPI deployment on the new host once at least one instance is built on the host

Example:

TKT CHG000000349812

Please deploy DBSPI license on new UDB production host njfofxprd2

\\leh\corp\groups\itd\DBA-Documents\UDB Documents\license_key

the license key was downloaded 2/24/2006 10:30am

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

Selecting Top Sql's
---------------
SELECT num_executions,SUBSTR(STMT_TEXT,1,60) AS STMT_TEXT FROM TABLE (SNAPSHOT_DYN_SQL ('$DATABASE',-1)) as number_exec order by num_executions desc FETCH FIRST 15 ROWS ONLY;

SELECT TOTAL_USR_CPU_TIME,SUBSTR(STMT_TEXT,1,60) AS STMT_TEXT FROM TABLE (SNAPSHOT_DYN_SQL ('$DATABASE',-1)) as number_exec order by TOTAL_USR_CPU_TIME desc FETCH FIRST 15 ROWS ONLY;

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

select current timestamp from sysibm.sysdummy1 with ur

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

cd $HOME/sqllib/bnd    ;
db2 connect to <DATABASE>;
db2 bind @db2ubind.lst blocking all grant public ;
db2 bind @db2cli.lst blocking all grant public ;
db2 bind $HOME/sqllib/bnd/db2schema.bnd BLOCKING ALL GRANT PUBLIC sqlerror continue;
db2 terminate;

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

Cescon Solutions Pvt. Ltd. Aradhya GRC 29/2, 2nd Cross, Malleswaram Bangalore - 560 003 Ph: 080-23462950,
23345129 e-mail: cescon@bgl.vsnl.net.in ...

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

kasish26

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

08:22PM Venkatachalam, Srinivasan: 'Create Date' >= "10/30/06" AND 'Create Date' <= "11/3/06"--For CHG request
08:24PM Venkatachalam, Srinivasan: 'Planned Start Date' >= "10/30/06" AND 'Planned End Date' <= "11/3/06"--CHG requset

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

To get this done, please submit a remedy ticket to your unix support teams asking for the following to be deployed to your hosts from opsware:

Solaris 2.8: Other Applications / Lehman / Global Arch and Engineering / DBEng / IBM DB2 v8 Client / SunOS 5.8 sparc
Linux AS 2.1: Other Applications / Lehman / Global Arch and Engineering / DBEng / IBM DB2 v8 Client / Linux AS3.0 x86 / 8.1.10-1
Linux AS 3.0: Other Applications / Lehman / Global Arch and Engineering / DBEng / IBM DB2 v8 Client / Linux AS2.1 x86 / 8.1.10-1

Once that is done' the db2 client state and profile will be available under /home/db2clnt8. The binaries are held in /opt/IBM/db2, but referencing them directly in v8 is not advised.

For sh,ksh,bash and zsh, please source /home/db2clnt8/sqllib/db2profile to set up your environment to use the client.
For csh, please source /home/db2clnt8/sqllib/db2cshrc

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

db2 "rollforward db DAINFBAK to end of logs and complete overflow log path ('/local/udb/DAULB021/archive/DAINFBAK')"

db2 update dbm cfg using CLNT_KRB_PLUGIN NULL
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
njdbu4pudb4-m:/local/udb/PAULB045/home> db2 update dbm cfg using GROUP_PLUGIN NULL
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
---------------------------------

Replication latency:

db2 "select MONITOR_TIME,END2END_LATENCY from ASN.IBMQREP_APPLYMON order by MONITOR_TIME desc fetch first 5 rows only"

######################################################################################################################
To know the database size

nyudbdev3:/home/daulb000> db2 connect to DAICE2

   Database Connection Information

Database server        = DB2/SUN64 8.2.3
SQL authorization ID   = DAULB000
Local database alias   = DAICE2

nyudbdev3:/home/daulb000> db2 "call sysproc.get_dbsize_info(?,?,?,-1)"

Value of output parameters
--------------------------
Parameter Name : SNAPSHOTTIMESTAMP
Parameter Value : 2007-04-02-00.36.17.544874

Parameter Name : DATABASESIZE
Parameter Value : 52162560

Parameter Name : DATABASECAPACITY
Parameter Value : 34082968064

Return Status = 0

########################################################################################################################33
to check when the last backup was taken

db2 "select LAST_BACKUP from table(SNAP_GET_DB('PALEADHI',0)) as ee"

LAST_BACKUP
--------------------------
2007-04-07-18.00.46.000000

1 record(s) selected.

##########################################################################################################################
To check whether Reorg is running

db2pd -db UDBPUO3 -reorg | grep "Online Started"

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

to check the logspace used by application

db2 get snapshot for all application |egrep 'Application handle|UOW log space used'

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

to update licence manger for no of processors

db2licm -n "DB2ESE" 4

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

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

Migrate_V7(32)_to_V8(64).doc

Migrate DB2 UDB from V7 to V8 64 bits

INDEX

Purpose
Scope
Process

DB2 V8 32 bit instance creation
Offline back of V7 and use of DB2CKBKP utility
Preparation of ‘set tablespace container’ script
Re-direct restore of V7 database backup into DB2 V8 32 bit instance
Verification of restore re-direct
Migrate instance from V8 32 bits to V8 64 bits (db2iupdb)
Post migration activities

Rebinding DB2 Packages
Verify packages are migrated into V8 sqllib directory
Updating your System Catalog Statistics
Converting all Indexes from TYPE-1 to TYPE-2

Perform offline backup of database (V8 64 bit)

Purpose: This document provides standard way of migrating database DB2 UDB from V7 to V8 64 bits.

Scope: DBA and System admin

It is not possible to restore database backup image from V7 32 bit to V8 64 Bit directly, but it is possible to migrate by restoring a V7 32-bit backup, restore it to v8 32-bit before updating the instance to 64-bit.

Process:

DB2 V8 32 bit instance creation:

Create db2 32 bits instance sandbox (test server).

Below document describe brief way to create V8 32 bit instance.

Offline back of V7 and use of DB2CKBKP utility:

Take full offline back up of DB2 V7

Check whether backup is restorable or not and also extract tablespace information and other object information from backup image.

FTP backup image to the sandbox (test server where you have created 32 bit instance)

Below document provides way to perform the same task.

Preparation of ‘set tablespace container’ script:

Base on tablespace information extracted from db2ckbkp command; prepare script to set tablespace containers which will be use in redirect restore process.

Below document has ‘set tablespace container’ script

Don’t execute the script

Re-direct restore of V7 database backup into DB2 V8 32 bit instance:

Now be ready for redirect restore.

Find out the file system where you can have space double or more then size of the backup image as restore command will dump data there in that file system.

db2inst2@mcdb-ha17:~> db2 RESTORE DATABASE AC202 FROM /home/db2inst1/nfsbackup/db200/ac202/db2backupfiles TAKEN AT 20070510091206 NEWLOGPATH /db2data1/db2inst2/data/ac202/logs REDIRECT

SQL1032N No start database manager command was issued.

Make sure instance is up and running. If not start db2 instance.

db2inst2@mcdb-ha17:~> db2start

SQL1063N DB2START processing was successful.

db2inst2@mcdb-ha17:~> db2 RESTORE DATABASE AC202 FROM /home/db2inst1/nfsbackup/db200/ac202/db2backupfiles TAKEN AT 20070510091206 NEWLOGPATH /db2data1/db2inst2/data/ac202/logs REDIRECT

SQL1277N Restore has detected that one or more table space containers are

inaccessible, or has set their state to 'storage must be defined'.

DB20000I The RESTORE DATABASE command completed successfully.

As shown in “SQL1277N” you need to set tablespace container for each tablespace as restore is not able to identify path for the tablespace containers in new server (test server).

So need to define storage path of each tablespace container.

Now run script for set tablespace container.

db2inst2@mcdb-ha17:~> db2 -td~ -vf set_tab_cont.sh

set tablespace containers for 0 using (path '/db2data1/db2inst2/data/ac202/SYSCATSPACE')