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

Technote (FAQ)


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 and 9.7

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)?


____________________________________________________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?



____________________________________________________Back to Top

C. 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?




____________________________________________________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 DB2 V9.5 and they can coexist - but have to be installed in different directories. The multi-copy installation feature introduced in DB2 V9 and above, lets you create multiple copies of these versions on the same machine.

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


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

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


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

Recommended fixes for all available versions of DB2 on LUW are listed in technote [ 1321001]


A.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?

Try running "db2iupdt instance_name"

You can find all the pre-installation and post-installation requirements in the readme.txt file that comes along with the Fix Pack. In case the Fix Pack does not contain any readme.txt, the file can be downloaded from the link listed in answer A3 by entering the Fix Pack number which you are just installed.

Note: For DB2 V 9.5 and later, this step is no longer required.


A.5 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 V9.5 ( TEXT | HTML )
DB2 LUW V9.1 ( TEXT | HTML )
DB2 UDB V8.2 ( TEXT | HTML )
DB2 UDB V7.2 ( TEXT )



A.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?

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



A.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?

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.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?

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: 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.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?

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 V 9 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.10 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.11 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 DB2 9 and DB2 9.5 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.


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

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.

http://www.ibm.com/software/support/lifecycle/


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

The following technote discusses the user private memory data limit and provides recommended values for when you're running DB2 on AIX. [ 1175377]


A.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?

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.15 Is DB2 supported on Microsoft Windows Vista?

Support for Vista was introduced from DB2 9 FP2. More information is in technote [ 1249760].


A.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?

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.17 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.18 In a HADR environment, what are the steps one should take to upgrade Fixpaks?

These are following steps you need to follow for roll in or roll out of Fixpaks on a primary and standby HADR setup:

-Take standby off-line
-Upgrade standby
-Bring standby back on-line, allowing it to catch up with primary
-Fail over. Primary will change role to standby but it should be brought down.
-Upgrade standby (or old primary)
-Fail back

For detailed information on this topic, please refer to the DB2 Information Center.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.ha.doc/doc/t0011766.html


A.19 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.20 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.21 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.22 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.

__________________________________________________Back to Top

B. Backup and Load


This section contains DB2 LUW Fequently Asked Questions (FAQ) reqarding Backup 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://www.ibm.com/developerworks/db2/library/techarticle/0212mulligan/0212mulligan.html

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 the table:

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

Just make sure you use NONRECOVERABLE or COPY YES to directoryname in
the LOAD command. This will make sure you don't have to backup the
tablespace after load.

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.

__________________________________________________Back to Top

C. Miscellaneous


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


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

The code page value is derived automatically from the Operating system during DB2 installation. 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 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.


C.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).


C.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.


C.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.


C.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.


C.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.


C.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


C.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


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

For DB2 8 DB2.Net provider you need 1.0 or 1.1 framework.

For DB2 9 DB2.Net provider you need 1.1 or 2.0 framework.


C.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] .


C.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, 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).


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

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


C.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.


C.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 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


C.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


C.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)


__________________________________________________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

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

8.2, 9.1, 9.5, 9.7

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1298716

Modified date:

2014-03-27

Translate my page

Machine Translation

Content navigation