The developerWorks Connections platform will be sunset on January 2, 2020. This blog will no longer be available unless an extension has been requested. More details available on our FAQ.
Allocated Shared Sort Heap size is reported in MON_GET_DATABASE, NOT in database snapshot
There are several ways you can check the current size of "Shared Sort Heap". Let me list some of them below. 1. database snapshot 2. db2mtrk 3. sysibmadm.snapdb 4. MON_GET_DATABASE table function When you check the data for each, however, you will find out that #1, #2 and #3, #4 have the different value. Let me show actual data from a test machine. -------------------------------- 1. database snapshot $ db2 get snapshot for database on tstv105 | gnugrep -A3 "Shared Sort Heap" Memory... [More]
|
Db2: How to drop database partitions
When you drop database partitions from your DPF instance, you should not edit the db2nodes.cfg file manually but should use db2stop drop partitionnum command. If you edit the db2nodes.cfg file manually to drop database partitions and there is a database partition group using the dropped partitions, it will cause a problem. Here is an example when editing the db2nodes.cfg file manually(bad case): $ cat ~/sqllib/db2nodes.cfg 0 alexx 0 1 alexx 1 2 alexx 2 3 alexx 3 $ db2start $ db2 create db test on $PWD $ db2 connect to... [More]
Tags: db2 |
[Db2 LUW] No more update for Db2 LUW V9.7/V9.8/V10.1/V10.5 online manuals
QUESTION: ========= Here is an example, V10.5 vs V11.1. * IBM DB2 10.5 for Linux, Unix and Windows documentation * IBM DB2 11.1 for Linux, Unix and Windows documentation Browsing pages between V11.1 and V10.5 above. We may find V11.1 page has newer information than V10.5. Here is one. * V11.1, fcm_parallelism - Internode communication parallelism configuration parameter ... Default [range]... [More]
Tags: db2 db2support db2luw |
Approximation error in decimal data type conversion in oracle mode
When oracle mode is used in db2, you might see the approximation error when you convert a value to decimal data type while original DB2 does not make this error. This is because the oracle mode follows the decflt_rounding from database cfg which would have an approximation error. Please check the following example which shows difference between original db2 and oracle mode. ------------------------------------------- SQL: db2 -x "values decimal(double(0.1),31,20)" oracle mode output: 0.10000000000000001000 db2 mode... [More]
|
How to Configure DSM User Authentication Using LDAP or Repository
Webinar! How to Configure DSM User Authentication Using LDAP or Repository Please join Jason Sizto, InfoSphere Optim Tools Development Senior Engineer, for a demo on How to Configure DSM User Authentication Using LDAP or Repository Date: Wednesday, September 20 Time: 12:00pm - 1:00pm EDT Register: https://ibm.co/DSMAugWebinar In this talk, you will learn how to: setup DSM to use LDAP for authentication setup DSM user privilege when using LDAP authentication setup DSM to use repository for authentication setup DSM user... [More]
|
DB2 China Newsletter August Edition(DB2 中国月刊）
The subject of this edition is "DB2 pureScale". Please check the link below to find more http://content.emm.ibmmail.com/frontend/dynamicAssets?e=tC42635C995AD9A0CDC8CB7BEFD638B2B44F8F925ECFB43291AEBAF3F869DB04C5591957300630E4900A467C15C2F9679F345BD9330928CC7E7679FB64C8432374ADEA7E888AF3EF5DA087CC57C7087C5F4C13C563393ACD51AEFBFF9B0229DB3502FDA80BA565411
Tags: purescale db2 |
Slow LOAD jobs in a DPF DB2LUW setup? Don’t forget to check DIAGLEVEL setting
In DB2LUW there are many reasons why loads might be slower. It might be a config issue at Db2 or, even OS level. Or, might be a tuning need for the load command itself. But, one thing commonly overlooked is what kind of diagnostic dumps are enabled when loads are running. It’s observed when multiple Db2 partitions are sharing a same physical host and sharing same db2diag.log file there might be severe contentions around db2diag.log file if large amount of messages are dumped into the... [More]
Tags: slow db2diag.log db2luw diaglevel load |
In DB2LUW how to restart a vendor archiving mechanism without restarting the whole database
In DB2LUW for some reason we faced some issues with the archive mechanism and we want to restart the archive mechanism ONLY and not the whole database or instance. So, how to restart the archiving mechanism without restarting a database. There is a way to restart the archiving mechanism when it uses vendor code. It’s, db2pd -fvp It’s documented in the Knowledge Center, https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0011729.html ... [More]
Tags: db2pd restart -fvp db2luw archiving |
SQL1229 during activation of a member after hard crash of a DB2LUW DPF host
There was a situation faced lately by a customer where a hard crash happened in one physical hosts of a DPF setup. There were some hardware issues. They brought back the DPF instance in all the partitions. But, trying to activate the database was continuously getting SQL1229 from one of the members. SQL1229N The current transaction has been rolled back because of a system error. The database was restarted with, db2_all "db2 restart database DW_AUDIT" Sill problem was... [More]
Tags: db2luw sql1229 -1229 activate deactivate |
Decimal data type conversion differences between DB2 ORACLE mode and ORACLE
When you convert a value to decimal data type in DB2 ORACLE mode, you will see that the conversion follows different rule from ORACLE. 1. DB2 ORACLE mode : decflt_rounding in database cfg 2. ORACLE : ROUND_HALF_UP And default value for decflt_rounding is ROUND_HALF_EVEN in DB2. So you will see different results if you just set DB2_COMPATIBILITY_VECTOR to ORA in DB2. For better understanding, let me compare the same examples between two. 1. DB2 ORACLE mode DB2 ORACLE mode will follow... [More]
|
In Db2 LUW if unknown hangs are being experienced better to check if DB2SLEEP registry is set or not
I am sharing this as we had a situation lately in a big 3 member 2 CF Purescale setup where there were hangs in the members being experienced without much notable reasons. When checked the setup closely it was found out the DB2SLEEP registry was turned ON in this instance for some reason and it was left that way accidentally. DB2SLEEP is an internal undocumented registry setting used only by Db2 Support team for advanced level of diagnostics collection. The registry will put the instance in the sleep state so that more... [More]
Tags: db2luw db2sleep hang |
PROBEVUE: EXECVE(), WAITPID(), _EXIT() [TRACK CHILDREN]
This small script will show you how you can track a process' children activity. It tracks all children that use 'exec()' for a given executable. Then for each one of those children it will print the 'exit()' code and for the parent it will track the 'waitpid()' status after a child is gone. This script can be used for example to track the activity from TSA related to db2 on a purescale system. It will show all the monitor commands executed by TSA to monitor the purescale system. The script should be... [More]
|
PROBEVUE: SIGACTION() [WHO CHANGED MY SIGNAL HANDLER?]
This small script will track calls to 'sigaction()' for a given signal and executable. This might become useful when a program does not react as expected when receiving a signal. This might be because somewhere along the way, something might have reset the signal disposition using a call to 'sigaction()'. Since such call can be also performed from an external API loaded via a dynamic library, this script will catch as well the 'dlopen()' and 'dlclose()'. Also it will print the address at which the... [More]
Tags: probevue sigaction |
"sqlri_ddl_common: unexpected sqlcode for DDL!" when revoking role from a user
If you upgrade your database from v9.7 to upper level and revoke a role from a user, you might see the following error message due to orphan rows in system tables. ------------------------------------------------ $ db2 revoke role developer_role from user db2user1 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0901N The SQL statement or command failed because of a database system error. (Reason... [More]
|
How to drop CF from cluster when the Operating System on the resided physical machine wrongly removed?
Customer wrongly removed the Operating System on one cf host , now they need to stop the service and drop it from cluster , how to do next ? Following is the scenario in customer's real case , the procedure contains how to achieve the purpose and how to solve the problem we hit during the process , record here for you to follow or refer to . 1. We can use "db2stop cf 129 force" and "db2stop instance on p780k8 force"to stop the service 2. db2sdin1@p780k5_KEHUA:/home/db2sdin1> db2instance -list... [More]
|
How to offline change IP address on pureScale ?
(We have technote below to online change IP address , customer followed the steps , it's not progress unfortunately .) http://www-01.ibm.com/support/docview.wss?uid=swg21594983 Following procedure is got by working with GPFS team and has already validated by customer , note that this is a completely offline operation . 1. As an instance user, perform the following steps: 1). Stop the database manager on all hosts by issuing the following command on a single host: su -iname ... [More]
|
'STMTID' the 2nd episode - Understanding by examples
Some years ago, I wrote a blog about using 'STMTID' to track long term performance changes for SQLs. ( Blog link : 'STMTID' - The key for long term performance tracking of an SQL ) Has anyone of you ever applied to your system ? Intermittently, I have gotten personal contacts asking to give some examples showing how it works. So here let me introduce very simple examples just to share what I meant. To remind, ' mon_get_pkg_cache_stmt ' function has kinds of ID columns. - EXECUTABLE_ID : It always changes whenever the... [More]
Tags: stmtid ibm-blog performance |
Decimal data type conversion differences between db2 and oracle mode
When float/double data type number is converted to decimal data type, some digits might be truncated by the precision and scale parameter in decimal data type definition. In this situation, db2 mode and oracle mode use different policies like as follows. 1. db2 mode : digits are just truncated from the end of the decimal number if the number of digits to the right of the decimal separator character is greater than the scale scale . 2. oracle mode : digits are truncated based on the DECFLT_ROUNDING in db cfg. For... [More]
|
[DB2 LUW] Sample administrative task schduler ADMIN_TASK_ADD and ADMIN_TASK_REMOVE usage
QUESTION: Could you guide us how to use administrative task scheduler ADMIN_TASK_ADD and ADMIN_TASK_REMOVE? We found each usage at KC but could not locate any sample usage case at the internet. ANSWER: Before using it, it is suggested to review below KC page for finding out the outline. The administrative task scheduler https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.gui.doc/doc/c0054380.html ... The administrative task scheduler enables DB2R database servers to automate the execution of tasks. It also provides a... [More]
Tags: db2 db2luw db2support |
[DB2 LUW] When will DB2 LUW V11.1 PDF manuals be available?
QUESTION: ========= There is V11.1 online manual available at: DB2 database product documentation http://www-01.ibm.com/support/docview.wss?uid=swg27009474 but could not find any link for PDF manuals as V10.5, V10,1, V9,8. Here is an example, V10.5 for your reference: DB2 database product documentation http://www-01.ibm.com/support/docview.wss?uid=swg27038430 ... DB2 documentation available online and downloads DB2 documentation updates DB2 documentation as PDF manuals Best practices for DB2 for Linux, UNIX, and Windows ... When will V11.1 PDF... [More]
Tags: db2luw db2support db2 |
