During RUNSTATS command execution: If collecting statistics using all data in the table is taking too long, specify an option to scan just a sample of the data in the table. Although the statistics are not as accurate as if you had taken a full scan, using a sample can provide a good compromise between RUNSTATS command execution time and better statistics. The RUNSTATS command includes an optional parameter, the TABLESAMPLE option, which allows you to select the percentage of the table you want it to scan. The following sample command collects... [More]

Abstract: On primary database, if some tables were altered with not logged initially, after takeover, how to figure out which tables are currently under inaccessible state? DB2 *does not * record the status of tables altered with not logged initially in catalog tables and query for status in syscat.tables is always NORMAL, thus the status of these tables cannot be queried by SQL. Solution: One solution is using " load query table " command to check the status of the table. Below is a shell script to check all the inaccessible table... [More]

Tags:  db2_techtip; hadr; not_logged_initially_tabl... 75_db2demystify;

Commands: CREATE TABLE TAB1 ( COLTIME TIMESTAMP NOT NULL WITH DEFAULT, COLVAR VARCHAR(40) GENERATED ALWAYS AS (VARCHAR(RIGHT(DIGITS (YEAR( TAB1 .COLTIME)), 4) || RIGHT (DIGITS(MONTH( TAB1 .COLTIME)), 2)|| RIGHT (DIGITS(DAY( TAB1 .COLTIME)), 2), 40) ) ) IN USERSPACE1 DB20000I  The SQL command completed successfully.   CREATE INDEX Z1_COLVAR ON  TAB1 (COLVAR ASC) DB20000I  The SQL command completed successfully.   CALL SYSPROC.ADMIN_MOVE_TABLE(CURRENT... [More]

Tags:  admin_move_table db2 sql0205n db2_techtip 75_db2demystify

Checking "parallelism" is one of the key factor when it comes to the performance scenario lets say for database backup. The algorithms behind DB2s self-tuning strategy does not select parallelism = 1 by default.  Instead, it is based on many parameters including the number of available CPUs, the number of backup target devices, whether compression or encryption is in use, and the number of tablespaces being processed.  However, there are only four possible reasons why parallelism of 1 would be selected when backing up... [More]

One of the common question DBAs ask related to their mission critical task of Database Backup using DB2 native encryption is - How does setting of ENCRLIB and ENCROPTS impact the database backup? The following chart summarizes the combination of settings of ENCRLIB and ENCROPTS that result in encrypted backups:   – If ENCRLIB is set, backups will always be encrypted – DBAs can only override the level of backup encryption if ENCROPTS is set to NULL – A backup will be decrypted when ENCRLIB and ENCROPTS are NULL  ... [More]

As of now, many of our customers are using IBM DB2 JDBC Driver(db2jcc.jar/db2jcc4.jar) to connect to DB2 server and configured SSL & normal database connectivity with end products such as WAS, OPM, Cloud and DataStudio etc. There are also a lot of you who are continuing to use SSL connection of JDBC for accessing your database server. Some of the tips in this blog will help to simplify the SSL connectivity with DB2 server. SSL Simplification: Many of you who prefer to configure the CA certificate instead of self signed certificate. If DB2... [More]

Tags:  db2 ssl driver 75_db2demystify jdbc jcc

Is enableSysplexWLB (Sysplex Workload balancing JDBC driver property) a data source property or a driver wide (global) property?   Answer) enableSysplexWLB is not a driver-wide or a global property, so you cannot set it in the JCC properties file. These are properties starting with "db2.jcc.*" and "db2.jcc.override.*". The "db2.jcc." properties provide a driver wide default value.  It is a Data source or connection level property, so it can only be enabled programmatically inside the application via a... [More]

Tags:  db2 75_db2demystify driver jdbc enablesysplexwlb

I have recently dealt with an invalid DB2 package problem, ofcourse in the past too :-), I now take this opportunity to share the steps used to diagnose and resolve it. Hope it helps. A bit background from my understanding, when a package can become invalid. Below are some situations: - If a package is dependent on a database object (such as a table, view, trigger, and so on), and that object is dropped, the package is placed into an invalid state. - During database upgrade, all packages stored in the database will be invalidated by the UPGRADE... [More]

Tags:  75_db2demystify syscat.packages package invalid

Hi there!   Recently I had an opportunity to implement SSL connection from DB2 client's CLP. DB2 Server was already setup with SSL. Just wanted to share the steps I followed to implement SSL connectivity successfully from the client side:   1> Set LD_LIBRARY_PATH, PATH and LIBPATH to point to GSKit libraries (present at $INSTHOME/sqllib/lib64/gskit)  and tools needed to add SSL certificate (present at $INSTHOME/sqllib/gskit/bin) [db2v10@ts-jkgera new]$ echo $LD_LIBRARY_PATH... [More]

Tags:  connection ssl db2_techtip 75_db2demystify

Nowadays, DB2 is running on many hypervisors based on various cloud and virtualized environment. And virtualised environment has various types including bare metal or operating system(O/S) hosted architecture. So sometimes there can be a confusion when we interpret a compatibility document in terms of Virtualisation technology experts. The point is that hypervisor itself is also a kind of O/S technology basis. For example, PowerVM is actually working on AIX O/S and we should be able to differentiate this from the target O/S where DB2 will be... [More]

Tags:  virtualize hypervisor metal baremetal ibm-blog db2 cloud bare

  Are you getting the following error when you try to register Visual Studio Add-Ins for DB2? "An error occurred while modifying IBM Data server provider for .NET FW 4.0 registry keys. Contact technical support team."                     Here are the tips to resolve this issue: 1) Check the VSAI logs created under: C:\Users\<USER>\appdata\Roaming\IBM\vsnet2010   If it has the following entries at the end: The 'DbProviderFactories' section can only appear once... [More]

Tags:  75_db2demystify db2_techtip vsaii

Hello DB2 mates, Hope all is going well for you. In a multiple databases environment, we sometimes configure remote catalogue information for target databases like followings. $ db2 catalog tcpip node <nodename> remote <IP or hostname> server <Port number> $ db2 catalog db <dbname> as <db alias> at node <nodename> And in some cases, we need to reconfigure this information. For example, if you  regularly change the port number of target system DB2 instance, the client catalog information should be changed as... [More]

Tags:  uncatalog catalog hang slow ibm-blog performance db2

Are you trying find out length of XML data ? Here is the example:   create table tab1(c1 int, c2 xml) DB20000I  The SQL command completed successfully.   insert into tab1 values (2,'<ABC>123</ABC>') DB20000I  The SQL command completed successfully.   insert into tab1 values (1,'<ABC>1234</ABC>') DB20000I  The SQL command completed successfully.   select length(xmlserialize(c2 as clob(20m))) as xml_length from tab1 XML_LENGTH -----------... [More]

Tags:  xmlserialize 75_db2demystify db2_techtip xml length

Hello!   On Windows, updating logpath or mirror logpath with path pointing to a mapped drive letter for a network drive may return SQL5099 RC 9.   C:\Program Files\IBM\SQLLIB\BIN>db2 update db cfg for sample using mirrorlogpath  "U:\pmrs\03298.082.000"   SQL5099N  The value "U:\pmrs\03298.082.000" indicated by the database configuration parameter "MIRRORLOGPATH" is not valid, reason code "9". SQLSTATE=08004   Here U:\pmrs\ is mapped driver letter for my network drive:... [More]

Tags:  db2_techtip swatithorve sql5099 logpath mirror 75_db2demystify

hadr_monitor.ksh is a shell script which TSA calls to monitor HADR database resources. There could be some requirement that we need to trace this shell script e.g.if we need to find some problematic db2 or rsct commands in this script.   To enable the debug function for the script, following the steps: 1. collect lsrsrc -Ab IBM.Application 2. Identify the resource name and the monitor script commnand from lsrsrc output. e.g. Name                  = " db2_DB2ADMIN_DB2ADMIN_SAMPLE-rs "... [More]

Tags:  db2_techtip hadr hadr_monitor_script tsa 75_db2demystify

The 4470 “Object closed” message is returned when the object that the application attempted to use was already closed.   The message covers many types of objects from connections to LOBs.  The object closed error is a little different than other error messages in that it occurs after the error causing event.  When the IBM Data Server Driver for JDBC and SQLJ (also called the JCC driver) is asked to close an object it marks it as closed.  When an object is referenced by an application, the driver checks to... [More]

Run steps as instance owner : db2cluster -cfs -list -tiebreaker db2cluster -cm -list -tiebreaker Make sure the CFS and CM level tibreakers are type disk. Stop all the applications and deactive the databases. db2stop db2stop instance on <member-host>  [repeat for all member hosts] db2stop instance on <cf-host>      [repeat for all CF hosts]   Run steps as cluster service admin (root) : db2cluster -cm -enter -maintenance -all db2cluster -cfs -enter -maintenance -all db2cluster -cfs -set -tiebreaker... [More]

Tags:  75_db2demystify purescale db2_techtip

There are many customers who have migrated applications from Oracle to DB2. So the expectations are to match or compare the behavior at many places. This blog explains one of the different behaviors which affect such wide range of users. DB2 throws an error SQL20496N if there is an attempt made to re-create package  without re-creating its package body. In Oracle user don't have to worry about re-creating the package body. Unfortunately, this is the current DB2 limitation and re-creating Package Specification does drop the Package... [More]

  A CLI based application can get  Idle Thread timeout error if that application takes time to process the huge amount of data returned by a select query. Until a COMMIT or ROLLBACK is being sent by the application after the query, the thread stays idle on the DB2/zOS server. When the thread stays idle while processing the result sets more than the IDLE THREAD TIMEOUT ( IDTHTOIN ) setting in DB2/zOS server,  the timeout error is expected ( SQL1224N )   In order to prevent this issue, use commitOnEOF   CLI Keyword. This... [More]

Tags:  db2_techtip 75_db2demystify cli

On Linux Platforms, DB2 FCM threads (db2fmcs and db2fcmr) may hang in select() system call due to possibly reaching file descriptor limit. The symptom would be hard to confirm via a stack trace of the FCM threads since the stack generation tools (db2pd -stack, pstack, gstack, gcore) will all clear up the hang. With APAR IC81639, db2fcm (s and r) threads will have the ability to use epoll() instead of select() since epoll() does not have the file descriptor limit that select() has. This change will not be by default but instead enabled via a... [More]

Tags:  db2_techtip 75_db2demystify

When troubleshooting db2start related issues, we often hear this question - what exactly happens at DB2 instance startup? db2start User command to start the db2 instance Checks for authorization by forking/exec db2chkau Checks for existence of db2nodes.cfg file Forks/exec db2star2 and waits for completion db2star2 Actual start process Checks authorization Creates the DBMS Memory set Checks licenses Forks/exec db2sysc and waits for completion db2sysc – DB2 system controller Connects to the Instance shared memory already allocated Forks a... [More]

Tags:  db2_techtip 75_db2demystify

Users can use the DB2 Trace Facility (db2trc) in DB2 BLU ( BLU aka CDE - Columnar Data Engine) environment to collect the critical performance diagnostics information using new component, CDE_PERF_TRACE.   To collect performance diagnostics, one should run: db2trc on –m CDE_PERF_TRACE ... run workload ... db2trc dmp perftrc.dmp db2trc off db2trc fmt perftrc.dmp perftrc.fmt There were reports on "db2trc on –m CDE_PERF_TRACE" not working due to some yet unidentified environment problem.... [More]

Tags:  75_db2demystify db2_techtip

Wondering how LOB DATA plays with HADR ? The answer is - Not all LOBs are created equal. There are different types of LOBs:   INLINE-LOBs: The data for an INLINE LOB column is contained in the base table. This feature is enabled through the INLINE LENGTH option on the CREATE TABLE statement or the ALTER TABLE statement. The limit for inline lobs is  32,673 bytes. LOGGED-LOBs : Specifies that changes made to the column are to be written to the log. The data in such columns is then recoverable with database utilities (such as RESTORE... [More]

Tags:  75_db2demystify db2_techtip

Do you know how to get the status of the section of a package in DB2, here's an easy command to retrieve this information by querying the system catalogs:   DB2 on zos: Verify the status of the section from DB2 Client against DB2 zos by issuing the below statement: (Normally this  is handy when SQ0525N bind error occurs)  db2 "select contoken, SEQNO, STMTNO , SECTNO, STATUS  FROM SYSIBM.SYSPACKSTMT WHERE COLLID = 'NULLID'  AND NAME = 'SQLUAK20'"   C     Compiled -... [More]

Tags:  db2_techtip status package bind consistency db2 75_db2demystify token

Abstract: In some circumstances, there is a requirement to recover multiple dropped tables. DB2 provides a function in rollforward command which can help to recover a single dropped table in one command. In this article we present a hands-on exercise to illustrate how to achieve this goal.    Here is the scenario: Suppose the tables( t1,t2 ) are dropped at time T1< T2 . In rollforward command, using rollfoward to PIT without the complete option to T1+1 for t1 and T2+1 for t2. There is a backup image before dropping the tables t1... [More]

Tags:  rollforward multi-dropped-tables 75_db2demystify db2_techtip

DB2 LUW is capable of running stored procedures and functions coded in several languages. Among these languages is Java. Since Java stored procedures and functions, (collectively referred to as routines) require a JVM there are some configuration settings that are specific to this type of routine. DB2 comes with a JDK and by default it is this JDK that is used to run Java routines. It is possible to alter DB2 to use another JDK to run Java routines. JDKs that are supported to run routines are documented in the DB2 infocenter for each release.... [More]

Tags:  50_db2_nuggets #db2 db2_techtip

DB2 LUW has two basic edition classifications Clients and Servers. Clients include drivers and full DB2 clients. Servers include both DB2 and DB2 Connect servers. DB2 Clients do not require a license to use when connecting to DB2 LUW servers. DB2 servers include DB2 client code and if no permanent license is installed the server functionality will be unavailable once the trial period expires but the client code may still be used. To license purchased DB2 LUW server products two licenses will need to be installed. One license referred to as the... [More]

Tags:  db2 50_db2_nuggets luw db2_resource licensing #db2

Hi all! Recently I came across a PMR, customer was using sysproc.automaint_set_policyfile to perform reorg check. More details about sysproc.automaint_set_policyfile here . After performing reorg, customer wanted to remove the policy file. We can set automatic maintenance off using different ways i.e set db cfg parameter auto_maint off, or turn auto_reorg to off in db cfg. However this is not what customer wanted to do. Customer was interested in removing the policy file than just turning off automatic maintenance. Even with automatic... [More]

Tags:  db2_techtip sysinstallobjects sysproc.automaint_set_pol... 50_db2_nuggets

Do you know the instance memory limitations for DB2 Workgroup Server Restricted License for different DB2 versions? First of all, I would like to explain what DB2 Workgroup Server Edition (WSE) Restricted License (db2wse_o.lic) means. Its nothing but that your DB2 database product was obtained as part of another IBM® product. In this case, the license terms of the bundling product takes precedence over the usual DB2 Version Workgroup Server Edition license terms. The DB2 license command ' db2licm -l ' will display the following... [More]

Tags:  50_db2_nuggets wse db2_techtip limitations workgroup memory