We help IT Professionals succeed at work.


IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

We have several Crystal Reports that use DB2 (AIX) queries as the backend of the report.  They are year-to-date reports that, when run, show all days from 01/01 of that year through the date the report is run.  I use something simple like this:

YEAR(column) = YEAR(current_date)

Open in new window

The problem is that, if the report is run on 01/01 of the next year, it has no data because  it's only reporting on the first day of that new year.  How can I get it to report on the previous year's data if run on the first week of the new year instead?
I am using Data Studio to write a query for a DB2 AIX database.  I have a small issue I'm running into.  

I have a column that appears like this:


Open in new window

I need to extract all characters, starting from the "SBN", all the way up to, but not including, the equal sign.  The numbers after "SBN" can be of varying length, so I just need the beginning of the string up until the equal sign.  What is the easiest way to accomplish this?
I need to run a query on the results of another query.

First I have a query that gives me a list of customers and the products they own from my company, if those products were opened in 2019.  I need to take that list of customers and find out, of those, which ones had late fees and how much the late fees were.  Here's an example of the query I wrote that gives me the list of customers:




AC.OPEN_DATE <= '2019-01-01' AND
(AC.CLOSE_DATE >= '2019-01-01' OR

Open in new window

I need to now take the list of customers that query products and join in another FEES table to see which of those customers if any had fees and what the fee amount was.  What would be the easiest way to do this?
DB2 10.5 on Windows Server 2012 R2

I have a table that use to contain photos, but was recently changed to hold links to photos now stored on a file system.  The table is currently at about 490 GB due to the photos being embedded.  Now that they are not, I want to run a REORG with LONGLOBDATA and reclaim a lot of that empty space.

My issue is I have to do online REORG since the system is 24/7 but it is part of a HADR system.  I know that online REORG causes a whole lot of logs and I don't want to crash the HADR server or have the primary freeze because the secondary is so far behind.  We do use SUPER ASYNC mode and I know that is suppose to help.

My question is, what do others in my position normally do?  If this was SQL Server I would disable replication, do my REORG, and then reinitialize it.  But not real sure how to do that with HADR that does not include me having to restore the entire large database to the standby afterwards.

Any suggestions?

Thank you!


   We are on DB2 UDB Linux 9.7 version.
I'm trying a user defined function, which takes a table name and return the counts.
Below is the function i'm trying, but it keeps erroring somewhere or the other.
Can someone kindly help get it to work?

CREATE FUNCTION f_tbl_counts (P_tbl_nm VARCHAR(200))
 RETURNS integer
 SPECIFIC f_tbl_counts
 DECLARE f_final VARCHAR(5000)          default '';--
 DECLARE f_count INT;

 SET f_final = (SELECT 'select COUNT(1) from '||p_tbl_nm
                    FROM SYSIBM.SYSDUMMY1

Open in new window

DB2 on windows server 2012 R2
I have a very large table (400 GB) that is large because of a BLOB field that holds a photo.  We made a change to store the photo on a secure file share and then just have the table point to that location.

On my test system this reduced the table from 400 GB to 5 GB.  When I tried that in prod, it blew out my data drives and crashed.  System is up now but how can I have it do a REORG using a different drive to hold the working data?  The main data drive is D: and we added a temp drive T: for this.

Or would it be faster/easier to just create a new BLOB tablespace and move the data from the existing to a new table space?  Any help would be greatly appreciated.  Thank you!!!

DB2 10.5 on Windows Server 2012

I can have a query that takes 3 seconds to run and then when I look at the explain plan, it takes 2-5 minutes to generate.  And it seems to be getting slower.  

How can I speed this up?  In my QA environment the same operation takes like 20 seconds to generate.

DB2 10.5 on Windows Server 2012

Is there a way to view query history in DB2?  I have found many examples of using the MON_XXX_XXX views but none seem to show me history per statement.  What am I missing?


How can I get data for the last full week (Monday to Sunday) in DB2?  I'm writing a Crystal Report with this DB2 query as the back end, but rather than pulling all of the data and then narrowing it down with a Crystal formula, I'd rather limit what data I get at the query level.  

Just FYI, this is for AIX and I'm using Data Studio 4.1.3.  Thanks.

is there anyway to move IBM WebSphere Application Server, Oracle WebLogic Application Server and Apache Tomcat to Azure?

what is the solution in Azure for it?
Hello Experts,

Please see the below query in DB2 and let me know what is wrong. I'm planning to update a random record in the same table. I have no Primary/Unique key on the table, but the DateTime record has distinct records

UPDATE Table1 tbl1  
   SET tbl1.FIELD1 = 'TEST' 
   WHERE tbl1.FIELD1 = tbl1.FIELD1
   AND tbl1.FIELD2 = tbl2.FIELD1

Open in new window

I get the below error.

[UPDATE - 0 rows, 0.020 secs]  [Code: -199, SQL State: 42601]  DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=FROM;<END-OF-STATEMENT> QUERYNO WHERE SKIP WITH ??( [ CONCAT || /, DRIVER=4.19.49
... 1 statement(s) executed, 0 rows affected, exec/fetch time: 0.020/0.000 sec  [0 successful, 1 errors]

I need to extract a set of numbers from a string in a DB2 query.  The string length will vary, but the numbers I will need to extract will always be the last 3 numbers, and they will always follow the same text pattern.  For example, I have a string that looks like this:


Open in new window

The "CustomerNumber" itself could be anywhere from 3 to 20 numbers (5555555 in this case), but the "IndividualCode" will always be 3 numbers (999 in this case), and will always be preceded by the pipe and the string "IndividualNumber=".

So I need to be able to write a SELECT query in DB2 that will just give me the last 3 numbers of that entire string.  I might have given too much info here to say "how do I always extract the last 3 numbers of this string", but I wanted to err on the side of caution.

Just FYI, I am using an AIX database and IBM Data Studio 4.1.3.
I am writing a DB2 query that will be run bi-monthly - once on the 16th of the month and once on the 1st of the following month:

  • When the report runs on the 16th of the month, it should pull all data from the 1st to the 15th of that same month.
  • When the report runs on the 1st of the following month, it should pull all data from the 16th of the previous month to the last day of the previous month.

I've found examples giving hints on how to accomplish this in T-SQL, but not necessarily in DB2.  I know a lot of T-SQL date functions don't translate well.  Can anyone assist with this piece of code?  Thanks.
DB2 10.5 on Windows Server 2012 R2

I have a BLOB field in a table that has a photo stored in it.  There are 7 million rows and the table size is 490 GB.

We recently made changes to the system to store the photos on a file share instead and have the table hold a URL to that photo instead of the photo itself.

The developers re-purposed the BLOB field to hold the URL instead of creating a new VARCHAR field to do that.

I had assumed that the table size would shrink as the photos were replaced but so far we have replace 2.5 million photos with URL and I don't see any changes in the table size or the tablespace.

Is there a minimum  size that a BLOB will not go below?  Or do I have to REORG the table in order to see a change?

DB2 10.5 on Windows Server 2012 R2

I have a HADR setup with 2 servers.  Right now if my primary server crashed, I would log into DR server and make it the primary, then change the DNS alias to point to the DR site.  This would take between 10 and 30 minutes (roughly).

My boss has suggested that we should look at Automatic Client Reroute instead to make this quicker and more efficient.

From what I have read this has the following steps.

1. Update the alternate server setting on production to point to DR.
2. Update the alternate server setting on DR to point to production.
3. Set up time out criteria on both to control fail over.

The way I understand this is that when this is configured, the DB2 server will set up a config file on the clients with this information and when there it a communication failure, the client will start trying to connect to the alternate database.  There are also settings on the client that help to define when to switch.

So, do I have the gist of it?

Now for my questions.

1. Do any of you have experience with HADR and ACR?  If so do you recommend it?
2. Since I have HADR set up, I would still need to issue the takeover commands manually to bring up the standby server as the primary.  Is that correct?
3. Although it seems like I can control the failover timing, what I have read it not real clear.  I'm not sure I want a client to redirect to the DR server after just one communication error.  How controllable is this?
4. I …
DB2 9.7 and 10.5 on Windows 2012 Server
I am trying to restore a 9.7 database to a new server with new directories that is running 10.5 and am about to pull my hair out.  I have read the IBM references about restore with redirect and they do not make much sense to me and of course none have good examples for Windows.  

Here is the example given
db2 restore db sample redirect without prompting
   SQL1277W A redirected restore operation is being performed. 
   During a table space restore, only table spaces being restored can 
   have their paths reconfigured. During a database restore, storage 
   group storage paths and DMS table space containers can be reconfigured. 

   DB20000I The RESTORE DATABASE command completed successfully.

   db2 set tablespace containers for 2 using (path 'userspace1.0', path    
   DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

   db2 restore db sample continue
   DB20000I The RESTORE DATABASE command completed successfully.

Open in new window

Nowhere does this specify the backup file for the database and it does not explain what the set tablespace for 2 means?  Shouldn't I have to give it the tablespace names?  
On the server it is running on now the data is on D:\db2\data\... and the logs are on L:\db2\logs.
On the new server I want them all on the X: drive.
Then I need to run the upgrade for 10.5.
This is a small database and not used much but it is killing me to get this moved over.
Can anyone point me at an example of how to do this (and please don't reference IBM knowledge pages, I have read them and they are not helpful).

Thank you!!!
We are running DB2 LUW v9.7 on Windows.   I am struggling to understand how the "PUBLIC" group access works.    We Grant Select access to PUBLIC on most of our tables but how does that grant a Domain User access?    Is there a specific AD (active directory) group that the user has to be a member of????
DB2 10.5 on Windows Server 2012

I need to write a stored proc to search a customer database on multiple, optional parameters.  Lets say firstname, lastname, cust_num.  So here is what I have..

     IN p_first_name VARCHAR(50),
     IN p_last_name VARCHAR (50),
     IN p_cust_num INTEGER)
SPECIFIC  DBAUtil.Jim_Search


DECLARE v_dynamicSql varchar(5000);

SET v_dynamicSql = 

IF p_first_name IS NOT NULL THEN
    SET v_dynamicSql = v_dynamicSql || ' AND UPPER(FIRST_NAME) = UPPER(' || p_first_name || ')';
END IF ;   
 IF p_last_name IS NOT NULL THEN
     SET v_dynamicSql = v_dynamicSql || ' AND UPPER(LAST_NAME) = UPPER(' || p_last_name || ')'  ;  

IF p_cust_num IS NOT NULL THEN
     SET v_dynamicSql = v_dynamicSql || ' AND CUST_NUM = ' || cust_num || ' ';

SET v_dynamicSql = v_dynamicSql || ' ORDER BY LAST_NAME, FIRST_NAME';

PREPARE v_dynamicSql FROM v_dynamicSql;
open c_search;


Open in new window

It compiles but when I run it like so
CALL DBAUtil.Jim_Search (NULL,'YOUMANS',123)

Open in new window

I get this
Category	Timestamp	Duration	Message	Line	Position
Error	12/27/2019 10:53:15 AM	0:00:00.020	<link> - DB2 Database Error: ERROR [42703] [IBM][DB2/NT64] SQL0206N  "YOUMANS" is not valid in the context where it is used.	11	0

Open in new window

So I know my sql string is getting messed up but not sure how.  Is there a way to print the v_dynamicSql string that it is trying to run?  I have tried a couple of ways but no luck.

Is there a better way to do this?

DB2 on Windows Server 2012

We recently started getting this error message.
 CLI0129E  An attempt to allocate a handle failed because there are no more handles to allocate. 

Open in new window

From what I have read, this is an issue with .NET Entity Framework not releasing connections to the database.  The developers are searching the application code to see if they can find the issue but in the mean time, is there anything I can do on the DB side to help with this?

I read this:  https://www.ibm.com/support/pages/cli0129e-no-more-handles  
and this: https://www.ibm.com/support/pages/how-increase-number-statment-handles-available-your-application

but not sure I understand what they are saying.  This is the first exposure I have had to DB2 handles and not sure I understand them as well as I could.  Any insight would be much appreciated.
DB2 10.5 on Windows.
I did normal windows patching and reboot this morning and now I am getting this error in the DIAG Log.

2019-11-07- I6616701F603         LEVEL: Severe
PID     : 1768                 TID : 9092           PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000
APPHDL  : 0-15306
EDUID   : 9092                 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, base sys utilities, sqeAppServices::InterruptAppByIndex, probe:2861
          "Agent index is invalid"
DATA #1 : String, 55 bytes
Ignoring user interrupt that targets system application
DATA #2 : unsigned integer, 2 bytes

Open in new window

When I google it I don't get any results.  Anyone have any thoughts?
I need to calculate number of days, adjust it, and then use it to compare on embedded SQL statement. Details attached.  

Thank You in Advance!
Lynn days-calc-with-caveat.docx
Hi, I'm trying to retrieve order number, status, customer#, customer name from our order master and customer master when the customer is on our preferred customer file.  This runs but returns nothing when it should.  Where am I going wrong?   Note: CXSPRF is a preferred level then multiple customer are assigned to it.  So I want all custest1 level where the customer number from the order master is assigned to it and active.

Select a.CHONUM,a.CHOSTS,a.CHOCUS,b.CCNAME                              
from CORDER a, CUSTMST b                                        
where a.CHOCUS = b.CCNUM                                        
  and (a.CHODTE between 20190101 and 20190931)                  
  and EXISTS                                                    
                                                                        a.CHOCUS = c.CXSCUS)  AND  
                                                                        c.CXSSTS = 'A'))          

Thank you in advance!!
   We are on DB2 UDB 9.7
I have a simple sql like below.
The alias names are static and so want to know if these can be made dynamic, to print the date at the run time.
Please help

SELECT SUM(CASE WHEN START_DT = current_date - 2 days THEN 1 ELSE 0 END ) AS as_of_09212019
             ,SUM(CASE WHEN START_DT = current_date - 1 day THEN 1 ELSE 0 END ) AS as_of_09222019
            ,SUM(CASE WHEN START_DT = current_date  THEN 1 ELSE 0 END ) AS as_of_09232019

Open in new window

DB2 10.5 on Windows Server 2012.

I am trying to do a simple backup and restore of a small database from one server to another and I can't get it to work.  Here is what I am doing.

Server 1 has a small database called TOOLSDB I run this.
DB2 backup db TOOLSDB ONLINE to X:\DBBackups include logs

Open in new window

And I get this a backup completed with timestamp xxx and a backup file in the X:\DBBackups folder

I copy the backup file to Server2 and run
db2 restore db TOOLSDB FROM X:\DBBackups

Open in new window

It completes with RESTORE DATABASE comm completeld successfully, so then I run
db2 rollforward db TOOLSDB to end of backup

Open in new window

And I get missing log file S0000427.LOG error.
Why??  when you do a backup with include logs shouldn't everything you need be in the backup file?  What am I doing wrong?  

What will happen in the following situation, I can't force this nor test this.

In a process a recordset is selected with SQL with a selection e.g. WHERE CODE = 1
(The result can contain a few hundred records.)
each record will be processed (within 15-30 seconds) and flagged as "CODE = 2"

Now a second job (same program) is started.
This program will also select a recordset with SQL with a selection e.g. WHERE CODE = 1.

What will happen when:
Job 1 select a records with code=1
(e.g result 250 records)

Job 2 select a records with code=1
(e.g result 240 records, 10 already processd by job 1)

Job 1 change value of a record to CODE = 2.
Is this record still in the recordset from Job 2?


IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

Top Experts In