Mas DB2 Notes: 2013

Friday, November 8, 2013

Useful DB2 Links

DB2 DBA Stuff : http://db2dbastuff.blogspot.sg

Difference between Oracle and DB2 : http://db2commerce.com/2011/05/16/newbie-differences-between-db2-and-oracle/ (good if you are familiar with Oracle and moving on to DB2)

Difference in data loading between Oracle and DB2 : http://www.ibm.com/developerworks/data/library/techarticle/dm-0505tham/

Wednesday, November 6, 2013

If you get the following error it means the tablespace has ran out of space:

CREATE TABLE UA_CampAttribute ( CampaignID           bigint NOT NULL, AttributeID          bigint NOT NULL, StringValue          varchar(1024), NumberValue          float, DatetimeValue        timestamp, CONSTRAINT cCampAttribute_PK PRIMARY KEY (CampaignID, AttributeID) )
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0289N Unable to allocate new pages in table space "UNICA". SQLSTATE=57011

Note: You may also get an error like this:
DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC

Easiest way is to turn on autoextend:
db2 => connect to unicadb
   Database Connection Information
Database server        = DB2/LINUXX8664 10.1.3
SQL authorization ID   = DB2INST1
Local database alias   = UNICADB
db2 => alter tablespace unica autoresize yes
DB20000I The SQL command completed successfully.
db2 => quit
And it should be OK now:
CREATE TABLE UA_CampAttribute ( CampaignID           bigint NOT NULL, AttributeID          bigint NOT NULL, StringValue          varchar(1024), NumberValue          float, DatetimeValue        timestamp, CONSTRAINT cCampAttribute_PK PRIMARY KEY (CampaignID, AttributeID) )
DB20000I The SQL command completed successfully.

Simple but comprehensive create database for DB2

See this link : https://pubs.vmware.com/vsphere-4-esx-vcenter/index.jsp?topic=/com.vmware.vsphere.installclassic.doc_41/install/prep_db/t_create_db2_script.html

Create database

Note: UPDATE DB CFG FOR unicadb USING APP_CTL_HEAP_SIZE 4000 --> optional

db2 => CREATE DATABASE unicadb USING CODESET UTF-8 TERRITORY US PAGESIZE 32K
SQL0104N An unexpected token "32K" was found following "PAGESIZE". Expected
tokens may include: "<unsigned-short-number>". SQLSTATE=42601
db2 => CREATE DATABASE unicadb USING CODESET UTF-8 TERRITORY US PAGESIZE 32 k
DB20000I The CREATE DATABASE command completed successfully.
db2 => get connection state
   Database Connection State
Connection state       = Connectable and Unconnected
Connection mode        =
Local database alias   =
Database name          =
Hostname               =
Service name           =
db2 => connect to unicadb
   Database Connection Information
Database server        = DB2/LINUXX8664 10.1.3
SQL authorization ID   = DB2INST1
Local database alias   = UNICADB
db2 => list tablespaces
           Tablespaces for Current Database
Tablespace ID                        = 0
Name                                 = SYSCATSPACE
Type                                 = Database managed space
Contents                             = All permanent data. Regular table space.
State                                = 0x0000
   Detailed explanation:
     Normal
Tablespace ID                        = 1
Name                                 = TEMPSPACE1
Type                                 = System managed space
Contents                             = System Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal
Tablespace ID                        = 2
Name                                 = USERSPACE1
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0000
   Detailed explanation:
     Normal
db2 => create regular tablespace unica pagesize 32k managed by database using (file 'home/db2inst1/db2inst1/NODE0000/UNICADB/UNICA01.TBS' 20000 )
DB20000I The SQL command completed successfully.
db2 => drop tablespace unica;
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token ";" was found following "rop tablespace unica".
Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601
db2 => drop tablespace unica
DB20000I The SQL command completed successfully.
db2 => create regular tablespace unica pagesize 32k managed by database using (file '/home/db2inst1/db2inst1/NODE0000/UNICADB/UNICA01.TBS' 20000 )
DB20000I The SQL command completed successfully.

Grants:
db2 => GRANT USE OF TABLESPACE UNICA TO USER UMP WITH GRANT OPTION
DB20000I The SQL command completed successfully.
db2 => GRANT USE OF TABLESPACE UNICA TO USER UC WITH GRANT OPTION
DB20000I The SQL command completed successfully.
db2 => GRANT USE OF TABLESPACE UNICA TO USER UMO WITH GRANT OPTION
DB20000I The SQL command completed successfully.
REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC

Thursday, October 17, 2013

Installing 10.1 FP3

[root@rhel-server-64-x86-64 universal]# ./installFixPack
Requirement not matched for DB2 database "Server" "". Version: "10.1.0.3".
Summary of prerequisites that are not met on the current system:
   DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "/lib/libpam.so*".

DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "libstdc++.so.6".

Enter the full path of the base installation directory:
-------------------------------------------------------
/apps/ibm/db2/V10.1

Do you want to choose a different installation directory for the fix pack? [yes/no]

------------------------------------------------------------------------------------
no

DBI1017I installFixPack is updating the DB2 product(s) installed in
      location /apps/ibm/db2/V10.1.

DB2 installation is being initialized.
Total number of tasks to be performed: 39
Total estimated time for all tasks to be performed: 1419 second(s)
Task #1 start
Description: Stopping DB2 Fault Monitor
Estimated time 10 second(s)
Task #1 end
Task #2 start
Description: Preparing the system
Estimated time 120 second(s)
Task #2 end
Task #3 start
Description: Base Client Support for installation with root privileges
Estimated time 3 second(s)
Task #3 end
Task #4 start
Description: Product Messages - English
Estimated time 13 second(s)
Task #4 end
Task #5 start
Description: Base client support
Estimated time 274 second(s)
Task #5 end
Task #6 start
Description: Java Runtime Support
Estimated time 153 second(s)
Task #6 end
Task #7 start
Description: Java Help (HTML) - English
Estimated time 7 second(s)
Task #7 end
Task #8 start
Description: Base server support for installation with root privileges
Estimated time 7 second(s)
Task #8 end
Task #9 start
Description: Global Secure ToolKit
Estimated time 60 second(s)
Task #9 end
Task #10 start
Description: Java support
Estimated time 12 second(s)
Task #10 end
Task #11 start
Description: SQL procedures
Estimated time 3 second(s)
Task #11 end
Task #12 start
Description: ICU Utilities
Estimated time 35 second(s)
Task #12 end
Task #13 start
Description: Java Common files
Estimated time 18 second(s)
Task #13 end
Task #14 start
Description: Base server support
Estimated time 362 second(s)
Task #14 end
Task #15 start
Description: Control Center Help (HTML) - English
Estimated time 13 second(s)
Task #15 end
Task #16 start
Description: Connect support
Estimated time 3 second(s)
Task #16 end
Task #17 start
Description: Relational wrappers common
Estimated time 3 second(s)
Task #17 end
Task #18 start
Description: DB2 data source support
Estimated time 6 second(s)
Task #18 end
Task #19 start
Description: IBM Software Development Kit (SDK) for Java(TM)
Estimated time 46 second(s)
Task #19 end
Task #20 start
Description: DB2 LDAP support
Estimated time 4 second(s)
Task #20 end
Task #21 start
Description: DB2 Instance Setup wizard
Estimated time 19 second(s)
Task #21 end
Task #22 start
Description: Integrated Flash Copy Support
Estimated time 3 second(s)
Task #22 end
Task #23 start
Description: Communication support - TCP/IP
Estimated time 3 second(s)
Task #23 end
Task #24 start
Description: DB2 Update Service
Estimated time 4 second(s)
Task #24 end
Task #25 start
Description: Parallel Extension
Estimated time 3 second(s)
Task #25 end
Task #26 start
Description: EnterpriseDB code
Estimated time 4 second(s)
Task #26 end
Task #27 start
Description: Replication tools
Estimated time 54 second(s)
Task #27 end
Task #28 start
Description: Sample database source
Estimated time 4 second(s)
Task #28 end
Task #29 start
Description: itlm
Estimated time 3 second(s)
Task #29 end
Task #30 start
Description: Command Line Processor Plus
Estimated time 6 second(s)
Task #30 end
Task #31 start
Description: Oracle data source support
Estimated time 4 second(s)
Task #31 end
Task #32 start
Description: Product Signature for DB2 Advanced Enterprise Server Edition
Estimated time 6 second(s)
Task #32 end
Task #33 start
Description: First Steps
Estimated time 3 second(s)
Task #33 end
Task #34 start
Description: Setting DB2 library path
Estimated time 180 second(s)
Task #34 end
Task #35 start
Description: Executing control tasks
Estimated time 20 second(s)
Task #35 end
Task #36 start
Description: Updating global registry
Estimated time 20 second(s)
Task #36 end
Task #37 start
Description: Starting DB2 Fault Monitor
Estimated time 10 second(s)
Task #37 end
Task #38 start
Description: Updating the db2ls link
Estimated time 1 second(s)
Task #38 end
Task #39 start
Description: Updating the DB2 Administration Server
Estimated time 40 second(s)
Task #39 end
Task #40 start
Description: Updating existing DB2 instances
Estimated time 60 second(s)
Task #40 end
A minor error occurred during the execution.
For more information see the DB2 installation log at
"/tmp/installFixPack.log.4490".

When I checked what was the minor error in the log file:
    Integrated Flash Copy Support
    Communication support - TCP/IP
    DB2 Update Service
    Replication tools
    Sample database source
    Oracle data source support
    First Steps
Languages:
    English (already installed )
Target directory:                               /apps/ibm/db2/V10.1
Space required:                                 930 MB

Stopping DB2 Fault Monitor :.......Failure
Preparing the system :.......Success
Backing up installed components.
Updating selected components.
Installing: BASE_CLIENT_R
Installing: DB2_PRODUCT_MESSAGES_EN
Installing: BASE_CLIENT
Installing: JAVA_RUNTIME_SUPPORT
Installing: DB2_JAVA_HELP_EN

Difference between DB2 servers and IBM data server clients

Here's the answer :-) :

A DB2® database system consists of a DB2 server and IBM® data server clients.

A DB2 server is a relational database management system (RDBMS) that delivers data to its IBM data server clients. You need to install a DB2 server if you plan to use a database that resides on this computer.

An IBM data server client is an application that allows you to run commands and SQL statements against a DB2 server, connect to a remote DB2 server and access its databases. The following types of IBM data server clients are available:

IBM Data Server Client
IBM Data Server Runtime Client
IBM Data Server Driver Package
IBM Data Server Driver for ODBC and CLI

Quick note on basic installation

Here's a simple one: http://www.fduran.com/blog/db2-linux-installation-notes/

Difference between db2 universal fix pack and db2 server fix pack

Well, here's the answer I got :

A universal fix pack

The universal fix pack services installations where more than one DB2 database product is installed.

The universal fix pack is not needed if the installed DB2 database products are only DB2 database server products or a Data Server Client. In this case, use the single server image fix pack.

See this url: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.qb.server.doc%2Fdoc%2Ft0006352.html

Anyway, being kiasu, I downloaded the universal fix pack. :-)

Installing DB2 10.1 on RedHat 6.1 (root)

Note, I am installing DB2 as root (less limitations, can run multiple instance if I need to)

1. Run pre-requisite checks:

[root@rhel-server-64-x86-64 aese]# ./db2prereqcheck
==========================================================================
Checking DB2 prerequisites for DB2 database version 10.1.0.0 on operating system "Linux"
Validating Linux distribution ...
   Required minimum operating system distribution: "RHEL"; Version: "5"; Service pack: "6".
   Actual operating system distribution Version: "6"; Service pack: "4".
   Requirement matched.
Validating kernel level ...
   Required minimum operating system kernel level : "2.6.16".
   Actual operating system kernel level: "2.6.32".
   Requirement matched.
Validating C++ Library version ...
   Required minimum C++ library: libstdc++.so.6
   Standard C++ library is located in the following directory: "/usr/lib64/libstdc++.so.6.0.13".
   Actual C++ library: CXXABI_1.3.1
   Requirement matched.
Validating 32 bit version of libstdc++.so.6 ...
   Found the 64 bit "/usr/lib64/libstdc++.so.6" in the following directory "/usr/lib64".
   DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "libstdc++.so.6".
Validating libaio.so version ...
   Loading the libaio.so.1 succeed.
   Requirement matched.
DBT3533I The db2prereqcheck utility has confirmed that all installation prerequisites were met for DB2 database server . Version: "10.1.0.0".

Note: the following is OK if fail because we are not running DB2 cluster:
==========================================================================
Checking DB2 prerequisites for DB2 database version 10.1.0.0 on operating system "Linux"
Validating Linux distribution ...
   Required minimum operating system distribution: "RHEL"; Version: "5"; Service pack: "6".
   Actual operating system distribution Version: "6"; Service pack: "4".
   Requirement matched.
Validating kernel level ...
   Required minimum operating system kernel level : "2.6.16".
   Actual operating system kernel level: "2.6.32".
   Requirement matched.
Validating SELinux status ...
   SELinux is enabled.
   ERROR:
   GPFS is not supported with SELinux enabled. Disable SELinux and then proceed to DB2 installation.
   ERROR: Requirement not matched.
Validating C++ Library version ...
   Required minimum C++ library: libstdc++.so.6
   Standard C++ library is located in the following directory: "/usr/lib64/libstdc++.so.6.0.13".
   Actual C++ library: CXXABI_1.3.1
   Requirement matched.
Validating 32 bit version of libstdc++.so.6 ...
   Found the 64 bit "/usr/lib64/libstdc++.so.6" in the following directory "/usr/lib64".
   DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "libstdc++.so.6".
Validating libc.so version ...
   glibc library is located in the following directory "/lib64/libc-2.12.so".
   Required minimum glibc library version: 2.4.0
   Actual glibc library version: 2.12.0
   Requirement matched.
Validating libaio.so version ...
   Loading the libaio.so.1 succeed.
   Requirement matched.
Validating dapl ...
   Required minimum version and release for dapl: 2.0.25-(null)
   DBT3507E The db2prereqcheck utility failed to verify installation prerequisites because the utility failed to find the following package or file: "dapl".
   ERROR: Requirement not matched.
Validating sg3_utils ...
   DBT3507E The db2prereqcheck utility failed to verify installation prerequisites because the utility failed to find the following package or file: "sg3_utils".
   ERROR: Requirement not matched.
Validating sg_persist ...
   DBT3507E The db2prereqcheck utility failed to verify installation prerequisites because the utility failed to find the following package or file: "sg_persist".
   ERROR: Requirement not matched.
Requirement not matched for DB2 database server with DB2 pureCluster feature. Version: "10.1.0.0".
Summary of prerequisites that are not met on the current system:
   DBT3507E The db2prereqcheck utility failed to verify installation prerequisites because the utility failed to find the following package or file: "sg_persist".
   DBT3507E The db2prereqcheck utility failed to verify installation prerequisites because the utility failed to find the following package or file: "sg3_utils".
   Required minimum version and release for dapl: 2.0.25-(null)
   DBT3507E The db2prereqcheck utility failed to verify installation prerequisites because the utility failed to find the following package or file: "dapl".
   ERROR:
   GPFS is not supported with SELinux enabled. Disable SELinux and then proceed to DB2 installation.

==========================================================================
2. Once pre-requisites checks are done, run db2setup:
(note please ensure you have the hostname in /etc/hosts)

3. Choose Custom

4. And then follow the screen shots:

5. If successful you will see the following screen:

6. Next, run db2val to validate the installation:
If you did not run db2chgpath, you will get the following error:
[root@rhel-server-64-x86-64 home]# /apps/ibm/db2/V10.1/bin/db2val
DBI1379I The db2val command is running. This can take several minutes.
DBI1334E Installation file validation failed for the DB2 copy installed
      at /apps/ibm/db2/V10.1. Reason code = 1.
Explanation:
1. Some features or components have missing files or files with the
   wrong size.
2. The installation image inside the install path has missing files.
3. The embedded runtime path for some DB2 library or executable files is
   not set properly.
User response:
Perform one of the following actions:
1. If your DB2 copy has the DB2 pureScale Feature installed, you must
   reinstall the DB2 copy to a new path by running the installFixPack
   command from the installation media (installFixPack -f level -p
   <new_path>), update your instance to the new copy using the db2iupdt
   command, then, rerun the db2val command.
   If your DB2 copy does not have DB2 pureScale installed, reinstall
   the DB2 copy to the same path by running the installFixPack command
   with the -f parameter (installFixPack -f level), then, rerun the
   db2val command.
2. Copy the installation image into <DB2-installation-path>/sd
   directory.
3. For root installations, run
   <DB2-installation-path>/install/db2chgpath as root. For non-root
   installations, run <DB2-installation-path>/install/db2chgpath as the
   copy owner. Rerun the db2val command.

   Related information
   installFixPack - Update installed DB2 database products command
DBI1344E The validation tasks of the db2val command failed. For
      details, see the log file /tmp/db2val-131017_162451.log.

7. Run db2chgpath (if needed):
[root@rhel-server-64-x86-64 install]# ./db2chgpath
The output will be saved in /tmp/db2chgpath.log.31900
Starting the update of runtime path ...
Thu Oct 17 16:27:16 SGT 2013
Changing the embedded runtime path for DB2 deliverables installed
under /apps/ibm/db2/V10.1 from /apps/ibm/db2/V10.1/ to /apps/ibm/db2/V10.1/
---> Please wait
Output was saved in the log file /tmp/db2chgpath.log.31900
Thu Oct 17 16:27:59 SGT 2013
Program db2chgpath completed successfully.

8, Re-run db2val.

Note: you might still get an error if you have PureScale option but did not install it.

Tuesday, July 30, 2013

How to execute sql script file in db2 command prompt

db2 -tvsf <script>.sql

Saturday, July 27, 2013

A temporary table could not be created because there is no available system temporary table space that has a compatible page size

com.ibm.db2.jcc.am.SqlException: A temporary table could not be created because there is no available system temporary table space that has a compatible page size.. SQLCODE=-1585, SQLSTATE=54048, DRIVER=4.13.127

Solution - Need to create a system temporary tablespace with page size of 32K.

create bufferpool unicasysbpool size 500 pagesize 32K

CREATE TEMPORARY TABLESPACE TEMPSPACE2 pagesize 32k MANAGED BY
DATABASE USING ((FILE 'D:\DB2\NODE0000\UNICADB\TEMPSPACE2.TBS' 20M)
BUFFERPOOL unicasysbpool

SQL1582N The PAGESIZE of the table space "UNICA" does not match the PAGESIZE of the bufferpool "IBMDEFAULTBP" associated with the table space.

If you get the above error, it means you need to create a new bufferpool with a different page size as you require.

CREATE BUFFERPOOL UNICABPOOL SIZE 3000 PAGESIZE 16K

Then use this bufferpool:

CREATE REGULAR TABLESPACE UNICA
    PAGESIZE 16K
    MANAGED BY DATABASE
    USING (FILE 'D:\DB2\NODE0000\UNICADB\UNICA.TBS' 80M)
    BUFFERPOOL UNICABPOOL AUTORESIZE YES

Prevent use of USERSPACE1

The default tablespace of any users in DB2 is USERSPACE1.

Even if you create a new tablespace and grant a user access to that tablespace, it will use USERSPACE1 first when creating a table (if you did not specify a tablespace).

To prevent the use USERSPACE1 tablespace. revoke the use of this tablespace:
REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC

Thursday, July 25, 2013

A table space could not be found with a page size of at least "4096" that authorization ID "COGNOS" is authorized to use

If you get the above error, it means the DB user does not have any privileges to use any tablespace.
See this link : http://www-01.ibm.com/support/docview.wss?uid=swg21339551

Solution Example:
db2 => drop tablespace cognos
DB20000I The SQL command completed successfully.
db2 => create regular tablespace cognos pagesize 4k managed by database using (file 'D:\DB2\NODE0000\UNICADB\COGNOS.TBS' 5000 ) AUTORESIZE YES
DB20000I The SQL command completed successfully.
db2 => grant use of cognos to cognos
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "of" was found following "grant use ". Expected
tokens may include: "ON". SQLSTATE=42601
db2 => grant use of tablespace cognos to user cognos
DB20000I The SQL command completed successfully.
db2 => CREATE USER TEMPORARY TABLESPACE USERTEMP MANAGED BY DATABASE USING (FILE 'D:\DB2\NODE0000\UNICADB\USERTEMP.TBS' 5000 ) AUTORESIZE YES
DB20000I The SQL command completed successfully.
db2 => grant use of tablespace usertemp to user cognos
DB20000I The SQL command completed successfully.
db2 =>

Tuesday, July 23, 2013

Reference Site

This site seems quite good :

DB2 Basics: Table spaces and buffer pools

This is a good read:
http://www.ibm.com/developerworks/data/library/techarticle/0212wieser/

Sunday, July 21, 2013

Create a new database

db2 => create db unicadb
DB20000I The CREATE DATABASE command completed successfully.

db2 => get connection state
   Database Connection State
Connection state       = Connectable and Unconnected
Connection mode        =
Local database alias   =
Database name          =
Hostname               =
Service name           =
db2 => connect to unicadb
   Database Connection Information
Database server        = DB2/NT64 10.1.0
SQL authorization ID   = ADMINIST...
Local database alias   = UNICADB
db2 => list tablespaces
           Tablespaces for Current Database
Tablespace ID                        = 0
Name                                 = SYSCATSPACE
Type                                 = Database managed space
Contents                             = All permanent data. Regular table space.
State                                = 0x0000
   Detailed explanation:
     Normal
Tablespace ID                        = 1
Name                                 = TEMPSPACE1
Type                                 = System managed space
Contents                             = System Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal
Tablespace ID                        = 2
Name                                 = USERSPACE1
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0000
   Detailed explanation:
     Normal
Tablespace ID                        = 3
Name                                 = SYSTOOLSPACE
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0000
   Detailed explanation:
     Normal

db2 => create regular tablespace unica01 pagesize 4k managed by database using (
file 'D:\DB2\NODE0000\UNICADB\UNICA01.TBS' 20000 )
DB20000I The SQL command completed successfully.