IBM Support

DB2 FAQ - Frequently Asked Questions about DB2 for Linux, UNIX and Windows

Question & Answer


Question

DB2 FAQ: This document contains Frequently Asked Questions (FAQ) about the product DB2 for Linux UNIX and Windows. This FAQ covers DB2 Versions 8.x, 9.1, 9.5,9.7,10.1 & 10.5

Answer



For further discussion on this topic, visit this developerWorks forum thread:
https://www.ibm.com/developerworks/community/forums/html/topic?id=a938457c-4428-40c7-9128-c3f46663bb26


Read about DB2 Version 9.1 End of Support
Read about: DB2 UDB Version 8 End of Support


Table of Contents for DB2 FAQ



A. Install and Upgrading
  1. Can I have different versions of DB2® installed on one server?

  2. If I have one instance of DB2 on version 8.2, can I use the same instance on DB2 9.5?

  3. Where can I find DB2 Fix Packs for Linux, UNIX and Windows?

  4. I recently applied a DB2 Fix Pack on my system. After which I am unable to start the instance. What could be the possible problem?

  5. Is there any link where I could find list of APAR's?

  6. I installed DB2 LUW on AIX platform recently. Are there any known issues with DB2 on AIX which I need to be aware of? OR I am planning to upgrade AIX. Is the new version of AIX supported with DB2?

  7. I had DB2 V 8.2 on Windows after which I uninstalled it and then installed DB2 V 9. When I try to restore the backup, I get SQL1005N. What could be the reason?

  8. I was on an earlier Fix Pack of DB2, I upgraded to the latest Fix Pack (example - was on DB2 8 FP 12 and then upgraded to DB2 8 FP 16) Now I want to go back to the earlier Fix Pack. Can I just uninstall the later Fix Pack?

  9. How do I verify what DB2 Product I have installed on Windows/UNIX/Linux? And how do I find out the exact DB2 version I am running?

  10. How long are DB2 Fix Packs supported? or Can I get a specific fix put on an old Fix Pack?

  11. My license for DB2 9 expired after 90 days from the date of the product installation. Where to find the license key?

  12. What is the End of Service date for DB2 8?

  13. Are there any general recommendations on AIX ulimit settings for DB2?

  14. Why do I receive "License not found" exception when I try to connect from my Java application using Universal JCC driver to DB2 on zOS?

  15. Is DB2 supported on Microsoft Windows Vista?

  16. During instance creation DB2 creates several directories and files with permission that are open to all users. Can I manually change such file permission to not allow access to all users?

  17. Why does instance creation in DB2 V9.5 require more space that previous versions (around 250MB per instance)?

  18. In a HADR environment, what are the steps one should take to upgrade Fixpaks?

  19. Why is a DIA3201E error reported in the db2diag.log when trying to run db2start, after successfully installing DB2?

  20. Can you install DB2 V9.x fixpaks for DB2 Express-C product?

  21. Can you install Data Studio on a machine which already has DB2 installed?

  22. Can you have multiple DB2 instances/databases created under the same DB2 installation (DB2 copy)?

  23.  In a pureScale HADR environment, which DB2 Version 10.5 Fix Packs can be upgraded to Version 11.1 without the need for HADR standby reinitialization?


____________________________________________________Back to Top

B. Backup and Load
  1. I run an online backup which sometimes errors out with SQL0911 RC 68. How can I prevent this?

  2. Can I backup a SMS tablespace and restore it into a DMS tablespace or vice versa?

  3. I have a DB2 backup of a database on UNIX platform, can I use this image to restore into a Windows platform, or vice versa?

  4. Can I copy instance/database directories and files from one box to another as an effective backup strategy?

  5. Can you restore a database from a backup image taken on a 32 bit level into a 64 bit level or vice versa?

  6. I have taken a backup of database A and want to restore this backup on some different path. Can I do that in DB2?

  7. I want to LOAD a table and want to make sure that table is accessible after load. How can I do that?

  8. Can I include logs while taking a backup? OR I have an online database backup image that was taken with the include logs option. How can I restore and only apply the logs stored in this backup image?

  9. Can DB2 V8x ixf files from an export operation be used as a source to load tables into DB2 V9X ?

  10. In DB2 V9x what is the default path for the DB2 backups when automatic backup is enabled?

  11. When using circular logging when are the secondary log files freed or re-used?

  12. Can I pause and restart an inplace (online) reorganization?

  13. How do I identify the type of DB2 backup?

  14. Can I change the name of a database?

  15. Is archival logging required to do an incremental backup ?

  16. Can I restore a DB2 database by using a file system backup?

  17. Are there certain utilities that I cannot run at the same time as an online backup?

  18. Can I use the db2relocatedb command to relocate a database on one operating system to a different operating system?

  19. How do I identify the state of a table space?

  20. How do I enable automatic reorganization?

  21. How do I disable automatic reorganization>

  22. How can I determine whether a DB2 database is configured for rollforward recovery?

  23. Can I reorganize a view?

  24. Does an inplace reorganization (online reorganization) rebuild indexes?

  25. Does the REORGCHK utility display statistical information for declared temporary tables or created temporary tables?

  26. Can I use the db2relocatedb command to relocate a database that has a load in progress?

  27. Is the db2move command available on DB2 clients?

  28. How do I check the reorganization status of a DB2 table?

  29. How long is a backup kept? Is there a way to automatically purge older ones?

  30. How can I check whether a table space is enabled for reclaimable storage?

  31. How do I determine whether a DB2 backup image is compressed?

  32. What happens when an offline reorganization is interrupted?

  33. Can I issue the db2ckbkp command against a backup image with multiple files?

  34. What happens if I rename the db2rhist.asc and db2rhist.bak files?


____________________________________________________Back to Top

C. HADR
  1. Are the HADR configuration parameters dynamic?

  2. How do I identify the current role of a database in a HADR environment?

  3. How do I monitor the table space status on the standby?

  4. Is HADR supported in a DB2 pureScale environment?


____________________________________________________Back to Top

D. Memory
  1. Why do I see self-tuning memory manager (STMM) log messages even after disabling STMM (that is, by setting the self_tuning_mem configuration parameter to OFF in the database configuration file)?

  2. What is the self-tuning memory manager (STMM) enhancement for the DB2 pureScale Feature, starting in DB2 10.5?

  3. Do I need to flush the package cache after I alter a table to make it volatile?

  4. Does the STMM tuner work on the HADR standby server?


____________________________________________________Back to Top

E. Optimizer
  1. Do I need to run RUNSTATS on volatile tables?

  2. What tool do I use to convert row-organized tables into column-organized tables in DB2 10.5?

  3. My dynamic SQL query performance has deteriorated, What can I check for?

  4. How do I check whether the tables are column organized or row organized in DB2 10.5?

  5. What happens if I alter a DB2 table so that it is volatile?

  6. Can I enable compression for column-organized tables?

  7. How do I create a column-organized table in DB2 10.5?


____________________________________________________Back to Top

F. Miscellaneous
  1. Can I change the codepage of an existing database in DB2?

  2. How does DB2 calculate the CPU speed of the system (CPUSPEED dbm cfg parameter value) ?

  3. If I drop my instances, will I loose my databases and the data with it?

  4. I have a table created in tablespace A with index in tablespace B. Can I drop just one of the two tablespaces and then rebuild that one alone?

  5. I added a database partition to a database partition group. Can I redistribute data for only one table in my partition group?

  6. What kind of striping do you recommend - DB2 striping or disk striping?

  7. Can you give me a simple command that would allow me to calculate the size of my database?

  8. Does DB2 have functionality similar to Oracle's truncate command?

  9. What are the .NET framework versions, DB2.Net data provider support?

  10. I am getting TCP/IP communication error (SQL30081) when I try to connect from my client.

  11. Why don't I see Development center in DB2 9?

  12. I cannot find IBM DB2 data Sources when trying to add a new Data connection in VS 2005 after installing DB2 9.

  13. How do I retrieve the current time and date via a DB2 command?

  14. I issued an alter table statement to extend the size of my tablespace containers and the DB2 re-balancer kicked in. Is there a way to find out information on progress of the re-balancer, like the number of extents remaining ?

  15. In DB2 V9 are all automatic storage tablespaces Database Managed Spaces (DMS)?

  16. When using a RAID device for tablespaces, what should I set the EXTENTSIZE of the tablespace to?

  17. What is a lock?

  18. What is a lock wait?

  19. What is a lock timeout?

  20. What is a deadlock?

  21. What command can I issue to force off all users with connections to a database?

  22. What is the quickest way to determine which user has DBADM authority?

  23. What is the quickest way to determine which user has SECADM authority?

  24. How do I determine whether the root user installed the DB2 product?

  25. Is DB2 for Linux, UNIX, and Windows supported on CentOS?

  26. How do I find the object ID of a range-partitioned table?

  27. What does a negative value in the TABLEID column of the SYSCAT.TABLES catalog view mean?

  28. How can I check whether an instance is using DB2 Advanced Enterprise Server Edition or DB2 Enterprise Server Edition?

  29. What command do I issue to find locking and deadlock information?

  30. Can I use the UPGRADE DATABASE command to convert an upgraded database back to its previous version?

  31. What is an indoubt transaction, and how do I get a list of indoubt transactions?

  32. How can I resolve indoubt transactions in a partitioned database environment?

  33. Do I need to create a user at the operating system level before creating an instance by issuing the db2icrt command?

  34. Does the db2idrop command remove the databases that are associated with an instance?

  35. Can I use the load utility to load XML documents between databases?

  36. What are the important differences between the ingest utility and the load and import utilities?

  37. What command can I issue to find the number of rows in each data partition of a partitioned table?

  38. How to set COLUMN as the default table organization for a database ?



____________________________________________________Back to Top

A. Install and Upgrading




A.1 Can I have different versions of DB2® installed on one server?

Yes, you can. But please keep in mind the following limitations, especially on Windows.

On Windows: DB2® Universal Database V8.1 and DB2 Universal Database V8.2 cannot coexist on the same machine. You can install either version before you install DB2 V9.1 or above and they can coexist - but have to be installed in different directories. The multi-copy installation feature introduced in DB2 V9.1 and above, lets you create multiple copies of these versions on the same machine.

On UNIX: The above limitations do not apply. For DB2 V9.1/9.5/later (including Windows) or for UNIX/Linux, you can simply install on different locations. For example, you can install DB2 V9.5 first, and then install DB2 V10.1 to a different location, and so on.


A.2 If I have one instance of DB2 V9.5 can I use the same instance on DB2 V10.1?

No, you cannot share the instances across various DB2 versions. You can upgrade from DB2 V 9.5 to later releases but cannot share an instance between the releases without an upgrade.


A.3 I recently applied a DB2 Fix Pack on my system. After which I am unable to start the instance. What could be the possible problem?

Try running "db2iupdt instance_name"

Refer: Post-installation tasks for fix packs (Linux and UNIX)


A.4 Is there any link where I could find list of APAR's?

View the APARs for a specific version by selecting the TEXT or HTML option from below:
DB2 LUW V10.5 ( HTML )
DB2 LUW V10.1 ( HTML )
DB2 LUW V9.5 ( HTML )
DB2 LUW V9.1 ( HTML )
DB2 UDB V8.2 ( TEXT )
DB2 UDB V7.2 ( TEXT )


A.5 I installed DB2 LUW on AIX platform recently. Are there any known issues with DB2 on AIX which I need to be aware of? OR I am planning to upgrade AIX. Is the new version of AIX supported with DB2?

There is a list of known issues of DB2 on AIX is in technote [1165448]


A.6 I had DB2 V 8.2 on Windows after which I uninstalled it and then installed DB2 V 9. When I try to restore the backup, I get SQL1005N. What could be the reason?

An instance can be upgraded from DB2 V 8 to DB2 V 9 directly, instead of uninstalling DB2 8.2 first, then install DB2 V 9 (and then try to get the previous databases back). For the same scenario on windows, you can install DB2 V 9 right on top of DB2 8. Choose the "Migrate" option on the install launchpad if it is a GUI install.

However, if DB2 V 8 is uninstalled first and DB2 V 9 is installed, try running the following command:

db2 list db directory

If this is empty, run:

db2 list db directory on <drivename/pathname>

This should list all the databases present in the system before you uninstalled DB2.

Catalog the database again and run migrate db command. This should let you connect to the databases and access the data.

Note: Uninstalling or dropping the instance does not drop the database and their directories.

For information on cataloging a database, refer to the following online documentation:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/start/t0005622.htm

And for upgrading (aka migration), refer to technote [1200005]


A.7 I was on an earlier Fix Pack of DB2, I upgraded to the latest Fix Pack (example - was on DB2 V8 FP 12 and then upgraded to DB2 V8 FP 16) Now I want to go back to the earlier Fix Pack. Can I just uninstall the later Fix Pack?

For DB2 V 8:
" On Solaris, there is a tool called /var/sadm/patch/backoutallpatch* that can back out a Fix Pack
" On HP, simply just uninstall the PDB2* file sets that are part of the latest Fix Pack
" On Linux, just apply the old Fix Pack over the new one.
" On Windows, there is no way to back out a Fix Pack
" On AIX, if you APPLIED the Fix Pack without COMMITING it (using the SMIT tool or installP) then you can go back to the old Fix Pack

For DB2 V 9.1 or above: Just install the old fix pack over top of the new one. Using the installFixPack tool, ensure that you indicate that that you're downgrading on. Windows would have the same limitation as in DB2 V 8 in that you cannot go back to an earlier Fix Pack.


A.8 How do I verify what DB2 Product I have installed on Windows/UNIX/Linux? And how do I find out the exact DB2 version I am running?

On windows platforms you can view the list of installed products using Add/Remove Programs Control Panel Applet (appwiz.cpl). Product names begin with either DB2 or IBM Data Server.

For finding out the exact DB2 version, run 'db2level' from a DB2 Command Window on Windows machines and from a normal UNIX prompt on UNIX. The 'db2level' output will tell you the exact DB2 Fix Pack you are running, the bit level DB2 is running as, whether or not any special builds are being used, and other information DB2 support may require.

Windows Example:

C:>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09013" with level identifier "01040107". Informational tokens are "DB2 v9.1.300.257", "s070719", "WR21392", and Fix Pack "3". Product is installed at "C:PROGRA~1IBMSQLLIB" with DB2 Copy Name "DB2COPY1".

UNIX Example:

$ db2level
DB21085I Instance "v8inst1" uses "64" bits and DB2 code release "SQL09050" with level identifier "03010107". Informational tokens are "DB2 v9.5.0.0", "s071001", "AIX6495", and Fix Pack "0". Product is installed at "/view/DB2_v10_aix64_s071001/vbs/INST".

For DB2 V9.1 and above, there is a new command line tool called 'db2ls' (UNIX platforms only) that prints out the currently installed DB2 software. Some sample output is included below:

[root@hansolo wsdb]# db2ls -q -b /opt/mydb2 -a

Install Path: /opt/mydb2

Feature Response File ID Level Fix Pack Feature Description
--------------------------------------------------------------------------------------------
DB2_PRODUCT_MESSAGES_EN 9.1.0.0 0 Product Messages - English
BASE_CLIENT 9.1.0.0 0 Base client support
.
.
XML_EXTENDER_SAMPLES 9.1.0.0 0 XML Extender samples
DB2_SAMPLE_APPLICATIONS 9.1.0.0 0 ADT sample programs
DB2_SAMPLE_DATABASE 9.1.0.0 0 Sample database source
SPATIAL_EXTENDER_SAMPLES 9.1.0.0 0 Spatial Extender samples
INFORMIX_DATA_SOURCE_SUPPORT 9.1.0.0 0 Informix data source support


A.9 How long are DB2 Fix Packs supported? or Can I get a specific fix put on an old Fix Pack?

The Fix Pack policy statement for DB2 LUW is located in technote [1180416].

The Information Management product lifecycle dates can be found here:
http://www.ibm.com/software/data/support/lifecycle/


A.10 My license for DB2 9 expired after 90 days from the date of the product installation. Where to find the license key?

If you bought the product on Media then the license key should be a part of the installation image and should be located under db2/license subdirectory. Please add this license certificate as an instance owner using either the License Center GUI tool or the DB2 command 'db2licm -a <license certificate file name>'.

The license files shipped with the product CDs in DB2 9 are called "base" licenses. A "base" license does not actually confer any usage rights at all. These are shipped so that DB2 will at least start (though it starts with a warning message). Versions DB2V 9.1, DB2 9.5 and above, come with "activation CDs" that contain actual license certificates. There is one for every possibly type of license policy (CPU, authorized user, developer, etc) and these are the license certificates that must be added. An installation which is not yet licensed will show up as having a license type of "Unlicensed base" in either the License Center or 'db2licm -l' output.

If you downloaded your product from the passport advantage site then it will be valid for 90 days after the product installation and will be shown as "Try & Buy" license in the output of the command db2licm -l. In order to have a permanent license please download it from the passport advantage site. The part numbers for the license key can be found in technote[1267176].

This link is for part numbers for the license key for DB2 9. The search terms for the license key for DB2 V 9.5 should be similar: CPU Option - Activation or Authorized User Option

If you downloaded your product from the PartnerWorld site, then please contact PartnerWorld for the license key.

If you downloaded the product from the DB2 support site or from the fix central (http://www.ibm.com/support/fixcentral) and do not have the purchased media for the product with the license key, please contact your IBM Sales representative.

Also refer: License Files


A.11 What is the End of Service date for DB2 V8 and DB2 V9.1? Where can I find information about DB2 Product lifecycles?

End of support for DB2 UDB V8.x products is April 30, 2009. Extended support may be purchased to extend your support until April 30, 2012.

End of support for DB2 V9.1 products is April 30, 2012. Extended support may be purchased to extend your support until April 30, 2015.

"End of support" indicates the last date on which you are entitled to base support for your DB2 UDB product. You can extend the support period beyond the end of support date for an additional fee. "Extended support" indicates the final date in which IBM shall end all support for the DB2 UDB products.

The IBM Software Support Lifecycle page specifies the length of time support will be available for IBM software from when the product is available for purchase to the time the product is no longer supported.


A.12 Are there any general recommendations on AIX ulimit settings for DB2?

Refer URL http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.qb.server.doc/doc/r0052441.html


A.13 Why do I receive "License not found" exception when I try to connect from my Java application using Universal JCC driver to DB2 on z/OS?

To connect to DB2 on ZOS using Type 4 JCC driver, you need db2jcc_license_cisuz.jar license. Make sure you have a valid licensed DB2Connect product and add the license file to your classpath. The file is usually located under /SQLLIB/java/db2jcc_license_cisuz.jar


A.14 Is DB2 supported on Microsoft Windows Vista?

Support for Vista was available from DB2 V9.1 FP2 until DB2 V10.1 but was discontinued from DB2 V10.5


A.15 During instance creation DB2 creates several directories and files with permission that are open to all users. Can I manually change such file permission to not allow access to all users?

During instance creation time DB2 automatically creates directories and files for its use. Permission of such files and directories should not be changed manually. Any attempt to manually change these could result in unexpected file access errors. Hence its not recommended to change permission on files/directories created by DB2.


A.16 Why does instance creation in DB2 V9.5 require more space that previous versions (around 250MB per instance)?

In DB2 V9.5 the ~/sqllib/acs directory has Tivoli specific libraries and configuration data. Installing Tivoli configuration files is not optional when creating an instance. This requires around 250MB space for the ~/sqllib directory alone. You should consider this when planning on installing DB2V9.5 and creating a DB2V9.5 instance.


A.17 In a HADR environment, what are the steps one should take to upgrade Fixpaks?

Please refer:
Installing online fix pack updates to a higher code level in a HADR environment
Performing rolling updates in a DB2 high availability disaster recovery (HADR) environment
Installing online fix pack updates to a higher code level in a HADR environment


A.18 Why is a DIA3201E error reported in the db2diag.log when trying to run db2start, after successfully installing DB2?

The error message looks similar to..
'DIA3201E The service name "DB2_db2v95" specified in the database manager configuration file cannot be found in the TCP/IP services file.'

The servers /etc/services file has an entry with port number for the DB2 service. If this is missing DB2 reports a DIA3201E error in the db2diag.log. This can be rectified by manually adding the DB2 entry to the /etc/services file such as "DB2_db2v95 60000/tcp" for example. After this entry is made, you may also want to make sure that the registry variable DB2COMM is set to TCPIP. The db2 instance should be recycled (stopped and started) following these changes.

On Windows the services file is found in this path ==> windows\system32\drivers\etc\services


A.19 Can you install DB2 V9.x fixpaks for DB2 Express-C product?

Regular DB2 fix packs can not be used with the Free version of DB2 Express-C. The regular DB2 fix packs (Univseral or Server) can only be used with DB2 Express-C if you purchase the optional 12 Months License and Subscription license (also known as the Fixed Term License or FTL). This subscription allows you to use regular DB2 fix packs. In addition to the abiilty to apply fix packs you also get access to IBM's world class 24/7 customer support.

For details on adding support to your DB2 Express-C product, please refer to the following link.

http://www-01.ibm.com/software/data/db2/express/support.html


A.20 Can you install Data Studio on a machine which already has DB2 installed?

Data Studio can be installed on a machine which already has DB2 installed. Both products can coexist; you do not need to remove the base DB2 product to install Data Studio.


A.21 Can you have more than one DB2 instance created under the same DB2 installation (DB2 copy)?

Yes, you can.

Linux, Unix -- It is possible to have more than one instance on a Linux or UNIX operating system if the DB2® product was installed with root privileges. Each instance can run simultaneously, though they are independent of the other.

Windows -- It is possible to have more than one instance and you can run multiple instances concurrently on windows. Each instance of the database manager maintains its own databases and has its own database manager configuration parameters.

You can also have multiple databases created under each instance on all OS platforms.

A. 23.  In a pureScale HADR environment, which DB2 Version 10.5 Fix Packs can be upgraded to Version 11.1 without the need for HADR standby reinitialization?
Upgrading a single partition ESE (non-pureScale) DB2 Version 10.5 primary and standby database to DB2 Version 11.1 is supported without having to change the database role and without needing to reinitialize HADR. This is supported in databases at DB2 Version 10.5 Fix Pack 7 or later. For more information, see "Upgrading DB2 servers in HADR environments (without standby reinitialization)" within the v11.1 DB2 LUW Knowledge Center.

Upgrading a pureScale DB2 Version 10.5 primary and standby database to Version 11.1 is now supported without the need to reinitialize HADR.
This is not supported in databases at DB2 Version 9.7 (all Fix Packs), or Version 10.1 (all Fix Packs), or Version 10.5 Fixpack 8 or earlier. This is supported starting in Db2 10.5 Fix Pack 9. For more information, see "Upgrading DB2 servers in HADR pureScale environments (without standby reinitialization)" within the v11.1 DB2 LUW Knowledge Center.

This technote will be updated when the supporting 10.5 Fix Pack is available.

__________________________________________________Back to Top

B. Backup and Load


This section contains DB2 LUW Frequently Asked Questions (FAQ) regarding Backup/Recovery issues and Load issues.


B.1 I run an online backup which sometimes errors out with SQL0911 RC 68. How can I prevent this?

Check if you ran the online backup along with other utilities like load, runstats, reorg, etc. Online backup is not compatible with some utilities like reorg, restore, rollforward and offline load. For more details on utilities that an online backup is compatible with, please refer to technote [1214717].



B.2 Can I backup a SMS tablespace and restore it into a DMS tablespace or vice versa?

You can backup a SMS tablespace and restore into into a SMS tablespace ONLY. You cannot restore it into a DMS tablespace or vice versa. Also you may want to note that you can add a container to a DMS tablespace but not to a SMS tablespace.


B.3 I have a DB2 backup of a database on UNIX platform, can I use this image to restore into a Windows platform, or vice versa?

No. You can backup/restore DB2 databases from and to the same Operating System. Cross-platform backup/restore is not possible and is not supported. This is due to complications with the big-endian and little-endian byte orders on Windows and UNIX Operating Systems. Between little-endian platforms such as Linux and Windows, it is not supported due since the paths in various config files would be different between the platforms.

You can however, cross-restore across UNIX platforms that have the same endianess.


B.4 Can I copy instance/database directories and files from one box to another as an effective backup strategy?

It is recommended you use the DB2 backup/restore utilities to backup and restore your databases. It is not recommend moving DB2 filesets from one machine to another as this may compromise the integrity of the database.


B.5 Can you restore a database from a backup image taken on a 32 bit level into a 64 bit level or vice versa?

You can restore a database from a backup image taken on a 32 bit level into a 64 bit level, but NOT vice versa.

32 bit backup ==> 64 bit restore ==> YES
64 bit backup ==> 32 bit restore ==> NO


B.6 I have taken a backup of database A and want to restore this backup on some different path. Can I do that in DB2?

Yes, you can. With DB2 you can "redirect restore" the DB. The
following document maybe is helpful for you.

Here are some DB2 Redirected Restore Scripts for reference. These are for UNIX environments only:

http://public.dhe.ibm.com/software/dw/data/0212mulligan/db2redirected.restore.ksh

Cloning DB2 Databases using Redirected Restore:

http://www.ibm.com/developerworks/db2/library/techarticle/0211melnyk/0211melnyk.html

DB2 Relocate db command:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0004500.htm


B.7 I want to LOAD a table and want to make sure that table is accessible after load. How can I do that?

The following link outlines how to load a table:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dm.doc/doc/c0004587.html

Just make sure you use NONRECOVERABLE or COPY YES to directoryname in
the LOAD command.

Also after LOAD, the table may be put into CHECK PENDING state. You may
need to do as follow:

db2 set integrity for <tablesname> immediate checked


B.8 Can I include logs while taking a backup? OR I have an online database backup image that was taken with the include logs option. How can I restore and only apply the logs stored in this backup image?

While taking backup you can specify the option INCLUDE LOGS. When you specify this option, the backup utility will truncate the currently active log file and copy the necessary set of log extents into the backup image. More information on this here:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0011559.htm

You will need to create an empty directory and extract the log to this directory during the restore. Then, issue the rollfoward command and point DB2 to this directory using the overflow log path option. Here is an example:

> DB2 backup db test1 online include logs Backup successful. The timestamp for this backup image is : 20080318084101
> mkdir rest_logs
> DB2 restore db test1 logtarget /home/bkogan/rest_logs SQL2539W Warning! Restoring to an existing database that is the same as the backup image database. The database files will be deleted. Do you want to continue ? (y/n) y DB20000I The RESTORE DATABASE command completed successfully.
> DB2 "rollforward db test1 to end of logs and stop overflow log path (/home/bkogan/rest_logs) noretreive"
Rollforward Status
Input database alias = test1 Number of nodes have returned status = 1
Node number = 0 Rollforward status = not pending Next log file to be read = Log files processed = S0000018.LOG - S0000018.LOG Last committed transaction = 2008-03-18-12.41.14.000000
DB20000I The ROLLFORWARD command completed successfully.


B.9 Can DB2 V8x ixf files from an export operation be used as a source to load tables into DB2 V9X ?

IXF file format is supported across all versions of DB2. One can use an ixf file created by an export in DB2 V8x as a source to load/import data into tables in DB2 V9x


B.10 In DB2 V9x what is the default path for the DB2 backups when automatic backup is enabled?

Windows:
C:\DB2\NODE0000\SQL0000x\DB2AUTOBACKUPS
on the windows platform and

UNIX/LINUX:
/path/to/db/NODE0000/SQL0000x/DB2AUTOBACKUPS


B.11 When using circular logging when are the secondary log files freed or re-used?

The secondary log files are deleted when the database is deactivated or when more space is required for the active log files.


B.12 Can I pause and restart an inplace (online) reorganization?

Yes, you can pause and restart an online table reorganization.

To pause, issue the following command:
reorg table <table_name> inplace pause

To restart, issue the following command:
reorg table <table_name> inplace resume


B.13 How do I identify the type of DB2 backup?

You can use the db2ckbkp utility with the -h parameter, which displays media header information, including the value of the Backup Mode parameter. If the Backup Mode parameter is set to 0, the backup was taken offline; if it is set to 1, the backup was taken online.


B.14 Can I change the name of a database?

Yes, you can change the name of a database by using db2relocatedb utility. For details, see the following technote:
http://www-01.ibm.com/support/docview.wss?uid=swg21673630


B.15 Is archival logging required to do an incremental backup ?

There is no need to enable archival logging to take incremental backups. Instead, you must update the value of the trackmod parameter.
Here is an example:

db2 update db cfg for <database name> using trackmod on

The trackmod parameter specifies whether the database manager tracks database modifications so that the backup utility can detect which subsets of the database pages must be examined by an incremental backup and potentially included in the backup image.


B.16 Can I restore a DB2 database by using a file system backup?

No, this method is unsupported.


B.17 Are there certain utilities that I cannot run at the same time as an online backup?

The following utilities are not compatible with an online backup:

REORG TABLE
RESTORE DATABASE
ROLLFORWARD DATABASE
ALLOW NO ACCESS LOAD
SET WRITE
BACKUP DATABASE with the ONLINE parameter

For more information , see the below link :
Compatibility of online backup and other utilities
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.ha.doc/doc/c0021554.html


B.18 Can I use the db2relocatedb command to relocate a database on one operating system to a different operating system?

No, the db2relocatedb command does not work across operating systems.
See the following link for more information :
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dm.doc/doc/r0024482.html?lang=en.


B.19 How do I identify the state of a table space?

You can use the db2tbst command to find the state of a table space. For example, issue the following command:

db2tbst 0x20020

State = Backup Pending
+ Load in Progress


B.20 How do I enable automatic reorganization?

Issue the following commands:

db2 connect to <database_name>
db2 update db cfg using auto_maint on auto_tbl_maint on auto_reorg on
db2 connect reset

The auto_maint database configuration parameter is configurable online. Hence, the change is dynamic.


B.21 How do I disable automatic reorganization?

Issue the following commands:

db2 connect to <database_name>
db2 update db cfg using auto_reorg off
db2 connect reset


B.22 How can I determine whether a DB2 database is configured for rollforward recovery?

If you set either the logarchmeth1 or logarchmeth2 database configuration parameter to a value other than OFF, that means the database is configured for rollforward recovery.


B.23 Can I reorganize a view?

You cannot use the REORG TABLE command on views.


B.24 Does an inplace reorganization (online reorganization) rebuild indexes?

No, an inplace table reorganization does not rebuild the indexes. A classic table reorganization (offline reorganization) rebuilds the indexes during the last phase of the reorganization.


B.25 Does the REORGCHK utility display statistical information for declared temporary tables or created temporary tables?

No, the REORGCHK utility does not display statistical information for declared temporary tables or created temporary tables.


B.26 Can I use the db2relocatedb command to relocate a database that has a load in progress?

No, you cannot use the db2relocatedb command to relocate a database that has a load in progress or is waiting for the completion of a LOAD RESTART or LOAD TERMINATE command.


B.27 Is the db2move command available on DB2 clients?

The db2move command is not available on DB2 clients. If you issue the db2move command from a client machine, you will receive an error message: “db2move is not recognized as an internal or external command, operable program or batch file.”


B.28 How do I check the reorganization status of a DB2 table?

Here are some of the commands that you can use:

db2pd -db <dbname> -reorgs
db2 list history reorg all for <database name>
db2 "select * from sysibmadmin.snaptab_reorg


B.29 How long is a backup kept? Is there a way to automatically purge older ones?

The rec_his_retentn (recovery history retention period) configuration parameter specifies the number of days that historical information about backups is retained. For more information, see
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.admin.config.doc/doc/r0000335.html?cp=SSEPGG_10.1.0%2F2-2-4-8-116.

You can purge the backups by using the db2adutl command if you backed up the database by using Tivoli Storage Manager. For information about the db2adutl command, which you can use to manage DB2 objects within Tivoli Storage Manager, see
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002077.html?cp=SSEPGG_9.7.0%2F3-6-2-6-11&lang=en.


B.30 How can I check whether a table space is enabled for reclaimable storage?

You can check by issuing the following command:

db2 "select substr(TBSP_NAME,1,20) TBSP_NAME,RECLAIMABLE_SPACE_ENABLED from table(mon_get_tablespace('',-1))"

The values of the RECLAIMABLE_SPACE_ENABLED column are as follows:
1 - Yes
0 – No


B.31 How do I determine whether a DB2 backup image is compressed?

You can use the db2ckbkp utility to get the compression details for a backup image. In the db2ckbkp command output, check for “Compression.” These are the values for the “Compression” attribute:

0 - Uncompressed
1 – Compressed


B.32 What happens when an offline reorganization is interrupted?

You may expect to see the SQL0952N message (“Processing was cancelled due to an interrupt”) in the db2diag.log file. Also, you will see the SQL1279W message (“Some indexes may not have been recreated”) if the reorganization is interrupted before the last phase because the offline reorganization process rebuilds the indexes during the last phase. The failed indexes are rebuilt based on the value of the indexrec configuration parameter.


B.33 Can I issue the db2ckbkp command against a backup image with multiple files?

Yes, but when you check multiple parts of an image, you must specify the first backup image object (.001) first. An example follows:

db2ckbkp TEST2.0.db2inst1.NODE0000.CATN0000.20140501045519.001 TEST2.0.db2inst1.NODE0000.CATN0000.20140501045519.002


B.34 What happens if I rename the db2rhist.asc and db2rhist.bak files?

The db2rhist.asc and db2rhist.bak files are automatically created the next time that you back up, restore, or reorganize tables, alter a table space, or make any other changes to a database.

__________________________________________________Back to Top

C. HADR


This section contains DB2 LUW Frequently Asked Questions (FAQ) regarding HADR issues.


C.1 Are the HADR configuration parameters dynamic?

No. If you change a parameter while the database is online, the change is visible if you specify the db2 get db cfg for db <dbName> command, but the change is not effective until you shut down and restart the database.


C.2 How do I identify the current role of a database in a HADR environment?

Check the value of the hadr_db_role database configuration parameter. The value can be PRIMARY, STANDBY, or STANDARD (not an HADR database).


C.3 How do I monitor the table space status on the standby?

The DB2 LIST TABLESPACES CLP command requires a database connection, but the standby database does not accept client connections. However, you can use the db2pd command to monitor table spaces on the standby. Use the following command:
db2pd -tablespaces -db <dbName>


C.4 Is HADR supported in a DB2 pureScale environment?

Starting DB2 V10.5, HADR is supported in a DB2 pureScale environment.

__________________________________________________Back to Top

D. Memory



This section contains DB2 LUW Frequently Asked Questions (FAQ) regarding DB2 Memory issues.


D.1 Why do I see self-tuning memory manager (STMM) log messages even after disabling STMM (that is, by setting the self_tuning_mem configuration parameter to OFF in the database configuration file)?

Setting the database configuration parameter to OFF disables the STMM feature; however, it does not disable STMM logging. The STMM log file is still updated with messages every 3 minutes. To completely disable STMM, issue the following commands:

db2set DB2STMM=OFF
db2stop
db2start


D.2 What is the self-tuning memory manager (STMM) enhancement for the DB2 pureScale Feature, starting in DB2 10.5?

Starting DB2 10.5, there is an independent STMM tuner for each member. Each STMM tuner reacts appropriately to the changes in available memory on each member.


D.3 Do I need to flush the package cache after I alter a table to make it volatile?

If you think that you might have statements in the package cache that reference your volatile table, you should flush the package cache. An example follows:

db2 flush package cache dynamic


D.4 Does the STMM tuner work on the HADR standby server?

No, the STMM tuner works on the primary only.

__________________________________________________Back to Top

E. Optimizer


This section contains DB2 LUW Frequently Asked Questions (FAQ) regarding DB2 Optimizer issues.


E.1 Do I need to run RUNSTATS on volatile tables?

Yes, runstats is needed even for volatile tables.


E.2 What tool do I use to convert row-organized tables into column-organized tables in DB2 10.5?

The db2convert command converts one or all row-organized user tables in a specified database into column-organized tables. The tables remain online during command processing.

The following command converts all the tables in the SAMPLE database:

db2convert -d sample


E.3 My dynamic SQL query performance has deteriorated, What can I check for?

Issue one of the following commands:
Single-partitioned:
db2 get snapshot for dynamic sql on <dbname>

DPF:
db2 get snapshot for dynamic sql on <dbname> global

You can issue the snapshot commands a couple of times as its a cumulative. In the snapshot, the “Total user cpu time (sec.ms)” and “Number of executions” fields provide the execution time of each query. If the execution time of the query is too high, you can use the db2advis (Design Advisor) command against the problematic query to see whether there are any recommendations.


E.4 How do I check whether the tables are column organized or row organized in DB2 10.5?

The SYSCAT.TABLES catalog view contains a new column, TABLEORG, that provides the details. Here is an example of how to check the value of the column:

SELECT tabname, tableorg FROM SYSCAT.TABLES WHERE tabname like 'SALES%'

In the TABLEORG column in the output, a value of C means that the table is column organized, and a value of R means that the table is row organized, as shown in the following sample output:

TABNAME TABLEORG
------------------------------- --------
SALES_COL C
SALES_ROW R

2 record(s) selected.


E.5 What happens if I alter a DB2 table so that it is volatile?

If you alter a table so that it is volatile, you are telling the DB2 optimizer not to trust the catalog statistics. You might want to specify that a table is volatile if the table frequently grows and shrinks. An example follows:

db2 alter table SCHEMA.TABLENAME volatile

After you alter the table to make it volatile, the optimizer uses an index scan rather than a table scan, regardless of the statistics.


E.6 Can I enable compression for column-organized tables?

No, you cannot enable compression for column-organized tables, which is why the COMPRESSION column in the SYSCAT.TABLES catalog view is always blank.

The following example shows how to query the value of the COMPRESSION column and other columns in the SYSCAT.TABLES catalog view:

SELECT tabname, tableorg, compression FROM syscat.tables WHERE tabname like 'SALES%'

Sample output follows:

TABNAME TABLEORG COMPRESSION
------------------------------- -------- -----------
SALES_COL C
SALES_ROW R N

2 record(s) selected.



E.7 How do I create a column-organized table in DB2 10.5?

To create a column-organized table, you must specify the ORGANIZE BY COLUMN parameter. Here is an example:

CREATE TABLE sales_col (c1 INTEGER NOT NULL, c2 INTEGER, PRIMARY KEY (c1) ) ORGANIZE BY COLUMN

__________________________________________________Back to Top

F. Miscellaneous


This section contains DB2 LUW Fequently Asked Questions (FAQ) reqarding Miscellaneous issues.


F.1 Can I change the codepage of an existing database in DB2?

You CANNOT change a code page of a database once it is created. You have to drop and recreate the database in desired codepage. The code page value is derived automatically from the Operating system during DB2 installation. Starting in DB2® 9.5, if you do not specify the database code page when you create the database, the database code page defaults to Unicode. If you specify the database code page, you must also specify the territory.

The DB2CODEPAGE registry variable will ONLY specify the code page of the data presented to DB2 for database client application. It is not recommended to use this variable unless explicitly stated in DB2 documents, or asked to do so by DB2 service. Setting DB2CODEPAGE to a value not supported by the operating system can produce unexpected results.
Refer:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.nls.doc/doc/t0004617.html


F.2 How does DB2 calculate the CPU speed of the system (CPUSPEED dbm cfg parameter value) ?

When creating a DB2 instance, DB2 runs a small calibration test multiple times to assess the CPU speed. The average time from these tests are used to calculate the CPU speed.

There is a bit of science to these tests. The tests are run within the duration of a quantum of time that the operating system scheduler provides to ensure the CPU calibration tests are valid regardless of how busy the system is when they run.

Customers can change the CPU SPEED value to a desired value or let DB2 recalculate it by setting the value to -1 (negative 1).


F.3 If I drop my instances, will I loose my databases and the data with it?

Dropping an instance will not drop your databases, so you will not loose data. You may drop your instance, recreate it and then catalog the databases to make them available. You can use the db2cfexp and db2cfimp commands to export and import instance profiles in such situations.


F.4 I have a table created in tablespace A with index in tablespace B. Can I drop just one of the two tablespaces and then rebuild that one alone?

No. You cannot drop a tablespace without dropping all tablespaces that are associated with it. Example, in this specific case, you have to drop both index and data tablespace in one drop command.


F.5 I added a database partition to a database partition group. Can I redistribute data for only one table in my partition group?

The redistribute database partition group command redistributes data across all partitions in a database partition group. This affects all objects present in the database partition group and hence cannot be restricted to one object alone.


F.6 What kind of striping do you recommend - DB2 striping or disk striping?

It is recommended to use DB2 striping (where is written into multiple containers in a round robin fashion). But if you choose to implement disk striping along with DB2 striping, it issuggest that the extent size of the tablespace and the strip size of the disk (example raid arrays) match.


F.7 Can you give me a simple command that would allow me to calculate the size of my database?

From the command line, connect to your database and issue the following command. (sample output is included below).

> db2 "call get_dbsize_info(?,?,?,0)"

 Value of output parameters
  --------------------------
  Parameter Name  : SNAPSHOTTIMESTAMP
  Parameter Value : 2008-03-18-17.15.17.919242

  Parameter Name  : DATABASESIZE
  Parameter Value : 23130112    

  Parameter Name  : DATABASECAPACITY
  Parameter Value : 16075184640

  Return Status = 0


F.8 Does DB2 have functionality similar to Oracle's truncate command?

Yes. A delete from a table will delete one row at a time and could cause extensive logging. To avoid this run a "import from /dev/null.. replace into" statement, which basically truncates the entire table without logging. This is similar to Oracle's truncate functionality. A sample truncate script called truncate.db2, which is available through DB2 Information center, can also be run to truncate a table.

The following URL has a link for truncate.db2:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.sample.doc/doc/admin_scripts/s-truncate-db2.htm


F.9 What are the .NET framework versions, DB2.Net data provider support?

Supported .NET Frameworks are listed in following links:

DB2 V10.5: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.swg.im.dbclient.adonet.doc/doc/c0020056.html

DB2 V10.1
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.swg.im.dbclient.adonet.doc/doc/c0020056.html?lang=en

DB2 V9.7:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.dbclient.adonet.doc/doc/c0020056.html?lang=en


F.10 I am getting TCP/IP communication error (SQL30081) when I try to connect from my client.

More information about the error and depending upon the reason code, possible solutions can be found in technote [1164785] .


F.11 Why don't I see Development center in DB2 9.1?

Development center has been replaced by Developers Workbench in DB2 9.1. In DB2 9.5 and above, Developers Workbench has been enhanced and renamed to IBM Data Studio (which is a separate download. Trial versions can be downloaded from: http://www14.software.ibm.com/webapp/download/search.jsp?go=y&rs=swg-ids).


F.12 I cannot find IBM DB2 data Sources when trying to add a new Data connection in Microsoft Visual Studio 200x after installing DB2.

Make sure IBM Database add-ins for Visual Studio are installed. Visual Studio add ins can be installed from the VSAI subdirectory of the DB2 product install image using issetup.exe or .msi. ( Example: db2_v9.5_ese_win_32imageVSAI)


F.13 How do I retrieve the current time and date via a DB2 command?

Run the following commands from a DB2 command line window

For the current time:

$ db2 values current time

1
--------
14:52:46

  1 record(s) selected.

For the current date:

$ db2 values current date

1
----------
04/10/2008

  1 record(s) selected.

For the current timestamp:

$ db2 values current timestamp

1
--------------------------
2008-04-10-14.53.02.562000

  1 record(s) selected.


F.14 I issued an alter table statement to extend the size of my tablespace containers and the DB2 re-balancer kicked in. Is there a way to find out information on progress of the re-balancer, like the number of extents remaining ?

You can use the MON_GET_REBALANCE_STATUS table function to monitor the progress of rebalance operations.
You can also issue a db2 list utilities show detail command to see the status of the re-balancer.

db2 list utilities show detail

ID                               = 1
Type                             = REBALANCE
Database Name                    = SAMPLE
Partition Number                 = 0
Description                      = Tablespace ID: 6
Start Time                       = 05/05/2008 01:22:36.014403
Throttling:
   Priority                      = Unthrottled
Progress Monitoring:
   Estimated Percentage Complete = 61
   Total Work                    = 101 extents
   Completed Work                = 62 extents
   Start Time                    = 05/05/2008 01:22:36.014403


F.15 In DB2 V9 are all automatic storage tablespaces Database Managed Spaces (DMS)?

Depending on what type of tablespace you wish to create (User, Large, System/User Temporary) an automatic storage space can either be SMS or DMS.

Example:-
db2 "create REGULAR tablespace REGTBS managed by automatic storage" ==> DMS
db2 "create LARGE tablespace LARGTBS managed by automatic storage" ==> DMS
db2 "create USER TEMPORARY tablespace USRTMPTBS managed by automatic storage" ==> SMS
db2 "create SYSTEM TEMPORARY tablespace STMPTBS managed by automatic storage" ==> SMS


F.16 When using a RAID device for tablespaces, what should I set the EXTENTSIZE of the tablespace to?

It is recommended that you set the EXTENTSIZE to match the stripe set of the RAID device for efficient data retrieval. Further tuning may be required depending on benchmark tests run on your individual systems.

Example:-
If you have a RAID 5 (4+1) system with 128k strip size you could set your EXTENTSIZE at 64k and your PAGESIZE at 8k. (as a rule of thumb, EXTENTSIZE X PAGESIZE - 64X8, should equal to strip size X no of disks, - 128X4)


F.17 What is a lock?

A lock is a mechanism that is used to associate a data resource with a single transaction. The lock controls how other transactions interact with the resource while it is associated with the owning transaction.


F.18 What is a lock wait?

If one transaction attempts to access a data resource in a way that is incompatible with the lock being held by another transaction, the transaction attempting to access the resource must wait until the owning transaction ends. When a lock wait event occurs, the transaction attempting to access the data resource stops executing until the owning transaction terminates and the incompatible lock is released.


F.19 What is a lock timeout?

A lock wait can lead to a lock timeout, depending on how you set the locktimeout database configuration parameter. If you set the locktimeout parameter to a value other than -1, the transaction times out after the specified time. The following diagram explains the lock timeout condition.


F.20 What is a deadlock?

A deadlock occurs when two or more transactions are in a lock wait cycle for each other and a lock timeout does not break the cycle.


F.21 What command can I issue to force off all users with connections to a database?

Issue the following command:
db2 quiesce db immediate


F.22 What is the quickest way to determine which user has DBADM authority?

Issue the following command:

db2 "SELECT DISTINCT GRANTEETYPE, GRANTEE, DBADMAUTH from SYSCAT.DBAUTH"


F.23 What is the quickest way to determine which user has SECADM authority?

Issue the following command:

db2 " SELECT DISTINCT GRANTEETYPE, GRANTEE, SECURITYADMAUTH from SYSCAT.DBAUTH"


F.24 How do I determine whether the root user installed the DB2 product?

You can use the db2ls command to identify the user. Sample output follows:

Install Path Level Fix Pack Special Install Number Install Date Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V9.7 9.7.0.7 7 Thu Aug 1 12:25:53 2013 CDT 0

If the value of the Installer UID column is not 0, the installation is a non-root installation and the UID corresponds to a user ID.


F.25 Is DB2 for Linux, UNIX, and Windows supported on CentOS?

No, it is not supported.


F.26 How do I find the object ID of a range-partitioned table?

The PARTITIONOBJECTID column in the SYSCAT.DATAPARTITIONS catalog view provides the object ID of a range-partitioned table.

Example:

db2 "select tabname from SYSCAT.DATAPARTITIONS where PARTITIONOBJECTID=1233"

Sample output follows:

TABNAME
--------------
TAB_RANGE_PART

The object ID that is referred to in the output of the db2dart command is the value of the PARTITIONOBJECTID column, not the actual object ID.


F.27 What does a negative value in the TABLEID column of the SYSCAT.TABLES catalog view mean?

A negative value means that the table is a range-partitioned table.

For example, assume that a table was created by issuing the following command:

CREATE TABLE range_test(a INT) PARTITION BY RANGE (a) (STARTING FROM (1) ENDING AT (100) EVERY (20))

The following command queries the value of the TABLEID column for the previously created table:

db2 "select tabname,tableid from syscat.tables where tabname='RANGE_TEST'"

Sample output follows:

TABNAME TABLEID
-------------------------------------------------------------
RANGE_TEST -32768

1 record(s) selected.



F.28 How can I check whether an instance is using DB2 Advanced Enterprise Server Edition or DB2 Enterprise Server Edition?

To identify the server edition, issue the db2licm command as follows:

db2licm -l

Sample output follows:

Product name: "DB2 Enterprise Server Edition"
License type: "CPU Option"
Expiry date: "Permanent"
Product identifier: "db2ese"
Version information: "10.1"
Enforcement policy: "Soft Stop"
Features:
DB2 Storage Optimization: "Not licensed"
DB2 pureScale: "Not licensed"



F.29 What command do I issue to find locking and deadlock information?

Issue the following db2pd command before calling IBM support:
db2pd -db <dbname> -inst -locks showlock -applications -transactions -dynamic


F.30 Can I use the UPGRADE DATABASE command to convert an upgraded database back to its previous version?

No, the UPGRADE DATABASE command only upgrades a database to a newer version.


F.31 What is an indoubt transaction, and how do I get a list of indoubt transactions?

An indoubt transaction is one that is prepared but is not yet committed or rolled back. To get the list of indoubt transactions, you can issue the following command:

db2 list indoubt transactions with prompting


F.32 How can I resolve indoubt transactions in a partitioned database environment?

Issue the following command:

db2_all "db2 restart database database-alias"


F.33 Do I need to create a user at the operating system level before creating an instance by issuing the db2icrt command?

Yes, you must create a user at the operating system level before you can use it as an instance user using the db2icrt command.


F.34 Does the db2idrop command remove the databases that are associated with an instance?

The db2idrop command does not remove any databases. You must remove the databases before dropping the instance.


F.35 Can I use the load utility to load XML documents between databases?

No, loading XML documents between databases is not supported and returns error message SQL1407N.


F.36 What are the important differences between the ingest utility and the load and import utilities?

The ingest utility has the following functionality:
It allows the input records to contain extra fields between the fields that correspond to columns.
It supports updates, deletes, and merges.
It supports constructing column values from expressions containing field values.
While it is running, it allows other applications to update the target table.


F.37 What command can I issue to find the number of rows in each data partition of a partitioned table?

Issue the following command:

db2 "SELECT DATAPARTITIONNUM(column name)Partition, COUNT(*)Total_Rows FROM Table_name group by DATAPARTITIONNUM(column_name ) order by DATAPARTITIONNUM(column_name)"


F.38 How do I check the table organization in DB2 10.5?

The dft_table_org database configuration parameter specifies whether a user table is created as a column-organized table or a row-organized table.

You can use the below command to set Column as defualt table organization :

update db cfg for sample using dft_table_org column



__________________________________________________Back to Top


If you did not find your question about DB2 listed here then please use the 'Rate this page' section below to send the team a question to add to the DB2 Fequently Asked Question list. All comments are read by the DB2 FAQ page owner and the page reviewer.





For further discussion on this topic, visit this developerWorks forum thread:
https://www.ibm.com/developerworks/community/forums/html/topic?id=a938457c-4428-40c7-9128-c3f46663bb26

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"OTHER - Uncategorised","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1;8.2","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21298716