DB2

DS400 Storage Performance with Oracle

Perallis — 2008-03-26T03:07:02Z

Performance sizing guideline for Storage SubSystems

Re: How to catalog a remote database

perallis — 2008-04-01T20:02:15Z

Correct, those are the two simple steps to catalog a remote database on AIX. Just to remember,the value of parameter "SERVER ..." must the same value of SVCENAME parameter of the instance where it is the local database. To know what is the right port number used by insatance, you have to go to local database, and issue the following command: db2 get dbm cfg And look to SVCENAME parameter, so now you know the port number used by instance.

Re: New user creation on linux but not able to access db2

perallis — 2008-07-28T04:02:24Z

Add the db2profile path in the home directory of the new user $ vi .profile Hello Pawan, You just need to add the db2profile path in the home directory of the new user the db2profile, see the steps below: $ vi .profile Add in you .profile the lines below: if [ -f /home/db2inst1/sqllib/db2profile ]; then . /home/db2inst1/sqllib/db2profile --//this is the db2profile path check your instance home directory fi When you login next time, you will get to execute the db2 commands

db2start not working - Error SQL6048N

eduhl — 2008-04-23T15:55:40Z

Error SQL6048N: db2 instance doesn't start

"SQLCA has already been built"

db2dba — 2008-04-08T05:31:04Z

Hi, I am seeing below error in db2diag.log file.Whats this and how to remove??? Its urgent plz. db2diag -time 2008-04-07-00.00.00.230520| db2diag -g instance=carsdb |db2diag -l error 2008-04-07-13.24.27.944754+330 I12982248G433 LEVEL: Error PID : 13696 TID : 3086546624 PROC : db2agent (EVENTXML) 0 INSTANCE: carsdb NODE : 000 DB : EVENTXML APPHDL : 0-56 APPID: *LOCAL.carsdb.080407075232 FUNCTION: DB2 UDB, runtime interpreter, sqlricls_complex, probe:140 RETCODE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT "SQLCA has already been built" 2008-04-07-13.24.28.039721+330 I12982682G989 LEVEL: Error PID : 13696 TID : 3086546624 PROC : db2agent (EVENTXML) 0 INSTANCE: carsdb NODE : 000 DB : EVENTXML APPHDL : 0-56 APPID: *LOCAL.carsdb.080407075232 MESSAGE : Reporting -1476; old SQLCA: DATA #1 : Hexdump, 136 bytes 0xB7F4FCE4 : 5351 4C43 4120 2020 8800 0000 DFFC FFFF SQLCA ........ 0xB7F4FCF4 : 0000 2020 2020 2020 2020 2020 2020 2020 .. 0xB7F4FD04 : 2020 2020 2020 2020 2020 2020 2020 2020 0xB7F4FD14 : 2020 2020 2020 2020 2020 2020 2020 2020 0xB7F4FD24 : 2020 2020 2020 2020 2020 2020 2020 2020 0xB7F4FD34 : 2020 2020 2020 2020 5351 4C52 4930 3638 SQLRI068 0xB7F4FD44 : 4900 1680 4900 0000 0000 0000 0000 0000 I...I........... 0xB7F4FD54 : 9CFF FFFF 0000 0000 2020 2020 2020 2020 ........ 0xB7F4FD64 : 2020 2020 2020 2020 Thanks Pawan

Re: severe error in db2diag

db2dba — 2008-05-07T05:44:38Z

2008-05-06-13.41.42.456490+330 I13436459G435 LEVEL: Severe PID : 18375 TID : 3086812864 PROC : db2agent (instance) 0 INSTANCE: smsdb NODE : 000 DB : TAMLOGIN APPHDL : 0-825 APPID: A0010A84.OE05.004146081833 FUNCTION: DB2 UDB, base sys utilities, sqleattach_agent, probe:60 RETCODE : ZRC=0x81360012=-2127167470=SQLZ_RC_CMERR, SQLT_SQLJC "External Comm error" 2008-05-06-13.46.42.475417+330 I13436895G435 LEVEL: Severe PID : 18367 TID : 3086812864 PROC : db2agent (instance) 0 INSTANCE: smsdb NODE : 000 DB : TAMLOGIN APPHDL : 0-961 APPID: A0010A84.C205.004146082333 FUNCTION: DB2 UDB, base sys utilities, sqleattach_agent, probe:60 RETCODE : ZRC=0x81360012=-2127167470=SQLZ_RC_CMERR, SQLT_SQLJC "External Comm error" 2008-05-06-13.51.42.515180+330 I13437331G435 LEVEL: Severe PID : 18538 TID : 3086812864 PROC : db2agent (instance) 0 INSTANCE: smsdb NODE : 000 DB : TAMLOGIN APPHDL : 0-885 APPID: A0010A84.F805.004146082833 FUNCTION: DB2 UDB, base sys utilities, sqleattach_agent, probe:60 RETCODE : ZRC=0x81360012=-2127167470=SQLZ_RC_CMERR, SQLT_SQLJC "External Comm error" Again I am getting above errors...what should I do???? not able to cause of errors..... Thanks & Regards Pawan

How to Stop One Database Under Single Instance??

db2dba — 2008-05-12T05:47:47Z

I have around 30 databases in single instance running How can I stop only one database so that the users can't access that database? I have already deactivated the database in my server like below, still the users are able to connect to it db2 deactivate db so how i can stop the users??? Thanks & Regards Pawan

How to Start the LDAP server (daemon)

goudelly — 2010-07-23T11:17:43Z

idsdiradm -I

Re: new db on existing instance or on new instace???best way?

db2dba — 2008-06-03T04:58:33Z

no reply?

package cache hit ratio is so less????

db2dba — 2008-07-21T11:22:15Z

Hi , i am facing problem in package cache hit ratio its between 37-45% which is very less. here are my database configuration snapshot Database Configuration for Database tamlogin Database configuration release level = 0x0a00 Database release level = 0x0a00 Database territory = US Database code page = 1208 Database code set = UTF-8 Database country/region code = 1 Database collating sequence = BINARY Alternate collating sequence (ALT_COLLATE) = Database page size = 4096 Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE Discovery support for this database (DISCOVER_DB) = ENABLE Default query optimization class (DFT_QUERYOPT) = 5 Degree of parallelism (DFT_DEGREE) = 1 Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO Default refresh age (DFT_REFRESH_AGE) = 0 Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM Number of frequent values retained (NUM_FREQVALUES) = 10 Number of quantiles retained (NUM_QUANTILES) = 20 Backup pending = NO Database is consistent = NO Rollforward pending = NO Restore pending = NO Multi-page file allocation enabled = YES Log retain for recovery status = RECOVERY User exit for logging status = NO Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60 Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60 Data Links Number of Copies (DL_NUM_COPIES) = 1 Data Links Time after Drop (days) (DL_TIME_DROP) = 1 Data Links Token in Uppercase (DL_UPPER) = NO Database heap (4KB) (DBHEAP) = 1200 Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4) Log buffer size (4KB) (LOGBUFSZ) = 128 Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000 Buffer pool size (pages) (BUFFPAGE) = 1000 Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000 Number of extended storage segments (NUM_ESTORE_SEGS) = 0 Max storage for lock list (4KB) (LOCKLIST) = 8096 Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000 Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70 Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128 Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES) Sort list heap (4KB) (SORTHEAP) = 256 SQL statement heap (4KB) (STMTHEAP) = 2048 Default application heap (4KB) (APPLHEAPSZ) = 256 Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8) Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384 Interval for checking deadlock (ms) (DLCHKTIME) = 10000 Percent. of lock lists per application (MAXLOCKS) = 15 Lock timeout (sec) (LOCKTIMEOUT) = -1 Changed pages threshold (CHNGPGS_THRESH) = 60 Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1 Number of I/O servers (NUM_IOSERVERS) = 3 Index sort flag (INDEXSORT) = YES Sequential detect flag (SEQDETECT) = YES Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC Track modified pages (TRACKMOD) = OFF Default number of containers = 1 Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32 Max number of active applications (MAXAPPLS) = AUTOMATIC Average number of active applications (AVG_APPLS) = 1 Max DB files open per application (MAXFILOP) = 64 Log file size (4KB) (LOGFILSIZ) = 1000 Number of primary log files (LOGPRIMARY) = 3 Number of secondary log files (LOGSECOND) = 2 Changed path to log files (NEWLOGPATH) = Path to log files = /san/db/smsdb/smsdb/NODE0000/SQL00001/SQLOGDIR/ Overflow log path (OVERFLOWLOGPATH) = Mirror log path (MIRRORLOGPATH) = First active log file = S0000858.LOG Block log on disk full (BLK_LOG_DSK_FUL) = NO Percent of max active log space by transaction(MAX_LOG) = 0 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0 Group commit count (MINCOMMIT) = 1 Percent log file reclaimed before soft chckpt (SOFTMAX) = 100 Log retain for recovery enabled (LOGRETAIN) = RECOVERY User exit for logging enabled (USEREXIT) = OFF HADR database role = STANDARD HADR local host name (HADR_LOCAL_HOST) = HADR local service name (HADR_LOCAL_SVC) = HADR remote host name (HADR_REMOTE_HOST) = HADR remote service name (HADR_REMOTE_SVC) = HADR instance name of remote server (HADR_REMOTE_INST) = HADR timeout value (HADR_TIMEOUT) = 120 HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC First log archive method (LOGARCHMETH1) = LOGRETAIN Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = Failover log archive path (FAILARCHPATH) = Number of log archive retries on error (NUMARCHRETRY) = 5 Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20 Vendor options (VENDOROPT) = Auto restart enabled (AUTORESTART) = ON Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART) Log pages during index build (LOGINDEXBUILD) = OFF Default number of loadrec sessions (DFT_LOADREC_SES) = 1 Number of database backups to retain (NUM_DB_BACKUPS) = 12 Recovery history retention (days) (REC_HIS_RETENTN) = 366 TSM management class (TSM_MGMTCLASS) = TSM node name (TSM_NODENAME) = I update parameter PCKCACHESZ by 2000 but hit ratio same then by 4096 and again then by 8096 but the hit ration is still same...please look the snapshot and suggest what to do for increasing package hit ratio.... waiting for the reply... __________________ Regards Pawan Kumar

Re: Any one help?

kanakaraju — 2010-09-14T16:59:10Z

Hello Mr Oliver, Do u have any sample code to do such kind of selection? or do u have any sample SQLs for this case ?(just two independent databases like for instancejust two independent databases like for instance) if so please provide those i am just checking the different options for starting point. Regards kanaka

getting too much locks and lock escalation??????????

db2dba — 2008-04-25T05:41:43Z

Below are the parameters related to locks... Max storage for lock list (4KB) (LOCKLIST) = 100 Interval for checking deadlock (ms) (DLCHKTIME) = 10000 Percent. of lock lists per application (MAXLOCKS) = 10 Lock timeout (sec) (LOCKTIMEOUT) = -1 I am getting locks snapshot below.... Locks held currently = 7 Lock waits = 1940 Time database waited on locks (ms) = Not Collected Lock list memory in use (Bytes) = 24960 Deadlocks detected = 2 Lock escalations = 174 Exclusive lock escalations = 174 Agents currently waiting on locks = 0 Lock Timeouts = 0 Number of indoubt transactions = 0 below warning are getting in notification log file... ADM5500W DB2 is performing lock escalation. The total number of locks currently held is "1034", and the target number of locks to hold is "517". 2008-04-24-22.01.03.801820 Instance:idsldap Node:000 PID:12926(db2agent (IDSLDAP) 0) TID:3086919360 Appid:*LOCAL.idsldap.080424163103 data management sqldEscalateLocks Probe:3 Database:IDSLDAP ADM5502W The escalation of "987" locks on table "SYSIBM .SYSCOLDIST" to lock intent "X" was successful. 2008-04-24-22.01.09.922015 Instance:idsldap Node:000 PID:12926(db2agent (IDSLDAP) 0) TID:3086919360 Appid:*LOCAL.idsldap.080424163103 data management sqldEscalateLocks Probe:2 Database:IDSLDAP ADM5500W DB2 is performing lock escalation. The total number of locks currently held is "1031", and the target number of locks to hold is "515". 2008-04-24-22.01.09.922235 Instance:idsldap Node:000 PID:12926(db2agent (IDSLDAP) 0) TID:3086919360 Appid:*LOCAL.idsldap.080424163103 data management sqldEscalateLocks Probe:3 Database:IDSLDAP ADM5502W The escalation of "509" locks on table "SYSIBM .SYSCOLUMNS" to lock intent "X" was successful. 2008-04-24-22.01.09.922671 Instance:idsldap Node:000 PID:12926(db2agent (IDSLDAP) 0) TID:3086919360 Appid:*LOCAL.idsldap.08042416310 I am getting daily these warning.... How we can remove these warnings???what action I should take???? Is reorg related to locks????? If we reorg those tables then chances to remove those locks???? Thanks Pawan

database security

db2dba — 2008-07-08T09:14:00Z

Hi, There are two instances A and B on same linux server under A and B users respectively. I create one more user C on same linux server. 1. After creating user C is there anthing required to add for accessing db2? which type permissions required if? 2. I want to access databases under instances A and B by user C? How? 3. I want to give only select privileges to this User C for databases running under instances A and B? How? Please reply ASAP......waiting......... Thanks & Regards Pawan

reorgchk issue.....with deadlock

db2dba — 2008-07-24T07:17:37Z

Dear All, Please look in my issue. I am running below command... db2 reorgchk update statistics on table all but throwing error SQL2310N The utility could not generate statistics. Error "-911" was returned. when i look in db2diag file ..there are also some errors and severe ..mentioned below... 2008-07-24-12.11.15.169185+330 I59560472G626 LEVEL: Severe PID : 31841 TID : 3086919360 PROC : db2bp INSTANCE: idsldap NODE : 000 APPID : *LOCAL.idsldap.080724063522 DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -911 sqlerrml: 1 sqlerrmc: 2 sqlerrp : SQLRC029 sqlerrd : (1) 0x80100002 (2) 0x00000002 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 40001 2008-07-24-12.11.15.251551+330 I59561099G1055 LEVEL: Severe PID : 31841 TID : 3086919360 PROC : db2bp INSTANCE: idsldap NODE : 000 APPID : *LOCAL.idsldap.080724063522 MESSAGE : DB2RA DATA #1 : Hexdump, 176 bytes 0xB77BDDF0 : 5351 4C44 4232 5241 B000 0000 0601 0200 SQLDB2RA........ 0xB77BDE00 : 0D00 0D00 8800 0000 0900 0080 E4D3 1608 ................ 0xB77BDE10 : 0000 0000 0000 0000 0000 0000 0B00 0000 ................ 0xB77BDE20 : 0400 0040 D46A FFBF 0000 0000 0000 0000 ...@.j.......... 0xB77BDE30 : 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0xB77BDE40 : 0800 0000 0100 0040 786A FFBF 0000 0000 .......@xj...... 0xB77BDE50 : 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0xB77BDE60 : 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0xB77BDE70 : 0200 0000 0100 0040 E46A FFBF 0900 0000 .......@.j...... 0xB77BDE80 : 0100 0040 F468 FFBF 0200 0000 0800 0040 ...@.h.........@ 0xB77BDE90 : 286B FFBF 0000 0000 0400 0040 0000 0000 (k.........@.... 2008-07-24-12.11.15.275235+330 I59564045G944 LEVEL: Error PID : 31841 TID : 3086919360 PROC : db2bp INSTANCE: idsldap NODE : 000 APPID : *LOCAL.idsldap.080724063522 FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10 DATA #1 : Hexdump, 136 bytes 0xBFFF6AE4 : 5351 4C43 4120 2020 8800 0000 0BFE FFFF SQLCA ........ 0xBFFF6AF4 : 0000 2020 2020 2020 2020 2020 2020 2020 .. 0xBFFF6B04 : 2020 2020 2020 2020 2020 2020 2020 2020 0xBFFF6B14 : 2020 2020 2020 2020 2020 2020 2020 2020 0xBFFF6B24 : 2020 2020 2020 2020 2020 2020 2020 2020 0xBFFF6B34 : 2020 2020 2020 2020 5351 4C41 4341 4C4C SQLACALL 0xBFFF6B44 : 8C00 0080 8C00 0000 0000 0000 0000 0000 ................ 0xBFFF6B54 : 0000 0000 0000 0000 2020 2020 2020 2020 ........ 0xBFFF6B64 : 2020 2032 3435 3031 24501 2008-07-24-12.11.15.275378+330 I59564990G944 LEVEL: Error PID : 31841 TID : 3086919360 PROC : db2bp INSTANCE: idsldap NODE : 000 APPID : *LOCAL.idsldap.080724063522 FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10 DATA #1 : Hexdump, 136 bytes 0xBFFF6AE4 : 5351 4C43 4120 2020 8800 0000 0BFE FFFF SQLCA ........ 0xBFFF6AF4 : 0000 2020 2020 2020 2020 2020 2020 2020 .. 0xBFFF6B04 : 2020 2020 2020 2020 2020 2020 2020 2020 0xBFFF6B14 : 2020 2020 2020 2020 2020 2020 2020 2020 0xBFFF6B24 : 2020 2020 2020 2020 2020 2020 2020 2020 0xBFFF6B34 : 2020 2020 2020 2020 5351 4C41 4341 4C4C SQLACALL 0xBFFF6B44 : 8C00 0080 8C00 0000 0000 0000 0000 0000 ................ 0xBFFF6B54 : 0000 0000 0000 0000 2020 2020 2020 2020 ........ 0xBFFF6B64 : 2020 2032 3435 3031 24501 Also throwing deadlock error...increasing deadlock counter when i run reorgchk command.... Why this type issue is coming now ..few days back it was working fine.

Re: difference between db2diag.log and instance.nfy file

perallis — 2008-04-01T08:36:41Z

Administration notification log - instance.nfy When significant events occur, DB2 writes information to the administration notification log. The information is intended for use by database and system administrators. Many notification messages provide additional information to supplement the SQLCODE that is provided. The type of event and the level of detail of the information gathered are determined by the NOTIFYLEVEL configuration parameter. However, detailed diagnostic information is not written to this log. db2diag.log Diagnostic information about errors is recorded in this text log file. This information is used for problem determination and is intended for DB2 customer support. The level of detail of the information is determined by the DIAGLEVEL configuration parameter. There are two types of entries in the db2diag.log file: 1- Administrative events. These entries are valuable, since they indicate whether events such as backups and restores started and finished. 2- Error information. This information is only useful if you are trying to diagnose an external symptom, or if you have already determined what the error is and are looking for more information. For example, when an application receives an unexpected SQL code, or when a database crashes, the file can contain error information, including pointers to dump files.