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.

Share tech news, updates, or what's on your mind.

Sign up to Post

DB2 10.5 on Windows

I have a query I am trying to optimize and I want to test a couple of different things.  The issue is that after I run it for the first time the data is in cache so first run is 5 minutes and next run is 15 seconds.  In MS SQL I would clear the execution plan for the query before each run to compensate for that.  Is there something similar in DB2?

Free Tool: ZipGrep
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

I require direction. I have never had to create a script before so I have no clue how to create it and run it but essentially I need a script to automate running a query and exporting to a .txt file.

I'm working with DB2 SQL on an IBM Power 8. I need a script that will run the following query:

select vend_num, vend_name
from iasp.vendor
order by vend_num

then output the records to vendor.txt. I have a Windows Server that connects to the DB2 database via ODBC. Seems simple enough but I'm not sure where to start.
DB2 10.5 on Windows

Is there anyway to create an index in an online mode?  I want to create a composite index on a large production table and I don't want to lock the table.  The table has about  29,063,052 million rows and is heavily used.

Hi everyone,

I would like to list all the tables which are in production and not in test.
I tried with  
          select * from QSYS2.SYSSCHEMAS
but I can't find a way to get the attribute.

Thank You !!
DB2 10.5 on Windows using TOAD for DB2 6.3

I am trying to get a list of users and their permissions, especially SYSADM users.  I was told I have SYSADM but I hit an error while trying to create a stored proc so I ran this...


Open in new window

I get this.

I would expect to see SYSADM as one of my privileges if I were SYSADM.  Am I making a bad assumption?  How can get a definitive list of users with SYSADM permissions.  My thought is no one on the server may have SYSADM level permissions because ever query I run from what I find on Google returns nothing.

Thank you!
DB2 10.5 on Windows

We moved some large tables to new tablespaces in production and then restarted the server.  Now we can't log into our HADR server due to reason code 4.  From what I see reason code 4 is

Reason Code 4 = Connection requests to an HADR standby database are not allowed while the replay-only window is active. The replay-only window is active when DDL or maintenance operations are being replayed on the standby.

If the primary db was rebooted before the HADR changes were complete, could it get stuck like this?  I would assume it would just start up again when the primary came back online.

I am trying to write a function in DB2 and the function will parse a certain string based on delimiter and return as seperate fields .
The string I should pass will come from another query ,

example :
  RETURNS TABLE or row  ( string1 VARCHAR(100),string2 VARCHAR(100))
   RETURN values (tempstring,tempstring1);--

Now can I call this function and pass a set of strings like select somestring from table ? or use the same query select somestring from table in the function and iterate through it ?

right  now need a study on any limitation on Oracle Golengate replication and MS SQL AOG for business case that need to setup 2 x site,  active active (not multi master). Then we try to POC on which one is better to use in terms of stability.

how about DB2's active active solution? any idea?

please present your idea.
Running DB2 10.5 on Windows

We have a DB2 10.5 HADR server set up that I should be able to use for read only queries.  I can connect to it (using TOAD), but when I do any selects from any table I get the following.

Lookup Error - DB2 Database Error: ERROR [] [IBM][DB2/NT64] SQL1773N  The statement or command failed because it requires functionality that is not supported on a read-enabled HADR standby database. Reason code = "1".

I checked the DB2_HADR_ROS registry entry and it is set to ON as it should be.

Has anyone else run into this?

Thank you!

Is there a general script library for DB2?  I am looking for examples of how to set up and run regular maintenance, do restores and backups, and so on.  I can, of course, dig up the info piece by piece and put things together but it would be so much easier to see examples of working scripts.  

Also, is there a general script I can run that will look for common issues and setting in my DB2 instance and give me feedback?  Like this but for DB2:  https://www.brentozar.com/blitz/

Thank you!

I am running DB2 10.5 on Windows.

I have a process that will capture the REORGCHK data for indexes to table so I can go through and pick out the indexes that need reorg based on the REORG column.  My question is, when is it best to just reorg all indexes on a given table?

If I have a very large table with 5 indexes and 3 or them need to be reorged, should I do a reorg all or 3 separate reorg statements for indexes indicated?  Is reorg all faster than one at a time or slower or the same?  

Thank you!

I am running DB2 10.5 on windows.

When I run


Open in new window

I get the error

Lookup Error - DB2 Database Error: ERROR [22011] [IBM][DB2/NT64] SQL0138N  The statement was not executed because a numeric argument of a scalar function is out of range.

According to the IBN Knowledgecenter  I should be able to do this.

If scope has a value of 'T', use this argument to specify one of the following values:
A fully qualified table name
The value ALL for all tables
The value USER for all user-defined tables
The value SYSTEM for system-defined tables

Can anyone explain what I am doing wrong?  Thank you!

This is on DB2 10.5 for Windows using TOAD.

How can I save the output of CALL SYSPROC.REORGCHK_IX_STATS('S', 'Schema1');  to a table?

My background is as a MS SQL Server DBA, very new to DB2.

I have a report query that I have in a file and I want to analyse it using db2advis.  I run this

C:\Windows\system32>db2advis -d depsvcs -n MainSchema -i c:\db2advis\sqlfile.in -t  5

And I get this.

execution started at timestamp 2017-07-26-
Error.  Could not open this input file [c:\db2advis\sqlfile.in]

Error.  No valid statements were found in this input file or in the
workload table.

In windows I set the security to the db2advis folder to full control by everyone.  Same for the sqlfile.in file.

So how do I get db2advis to actually open and use the file?

Thank you!
Hi Experts,

I try to check a value in a SQL where clause, but get the error: CPD4374

On the  Where xxxx in() of the next statement

Exec SQL Select * from my file
Where Field1 = :Parm1
  and Field2 = in(:Parm2)

Field2 is numeric(15,0)
Parm2 is Character(128) and contains  '12,13,45,16,32  '

What is missing?
Hi All,

We are doing a small POC to migrate MF screens to JAVA based webpages. As a matter of fact, we have created COBOL SPs to call relevant MF DC online programs. In case we need to call COBOL SP, what approach do JAVA team has to follow.

NOTE : IBM MQ is not supported by customer
              IMS DC (IMS is the online component of my customer. IMS and DB2 databases are used by administration system) is not suggested as it needs additional work
              Webservice / business services help should not be used.

What could be the approach do we have to follow?

P.s : I am neither JAVA expert nor Mainframe expert. Since I am managing a project, trying to get suggestions from various areas.

Moreover I heard something like wrapping COBOL stored procedure with DB2 so that it can be converted to DB2 SP(Do we have any approaches like that). In my customer's IT landscape they can call DB2 SP by using ODBC driver.

I am looking forward to see responses from experts.

windows server 2012
11.PNG 22.PNG
My website sometime will show this error in the web server .

A process serving application pool 'XXX' suffered a fatal communication error with the Windows Process Activation Service. The process id was '6052'. The data field contains the error number.

please help.
Thanks for reading.

I have an SQL database with a linked DB2 server. My client software interfaces with SQL.

The DB2 server contains a parts table, which contains pricing and availability information.

I read a linked view from the DB2 server which is presented as a SQL view at the SQL end. It takes a few seconds to come over in its entirety, which isn't a problem unless I want to run some heavy interrogation on it. Which, I do.

Realistically this information shouldn't be more than 10 minutes behind. So, I thought a trigger might be the answer. I have a table I update every 5 minutes for a completely separate reason. I thought I could attach a trigger to this table to drop/insert a temp parts table, and then merge the information to a locally held table.

I've not got as far as the merge yet, just the first bit! Here is the trigger:


CREATE TRIGGER dbo.UpdateParts
ON dbo.T1

IF OBJECT_ID('dbo.TempParts', 'U') IS NOT NULL

SELECT     DB2PartsView.*
INTO            TempParts
FROM         DB2PartsView


Open in new window

I can create the trigger fine, and if I run the SQL outside of the trigger the table is created successfully.

However, when I fire the trigger, I get the error:

"The row value(s) updated or deleted either do not make the row unique or they alter multiple rows"

I understand that triggers have hidden 'Inserted' and 'Deleted' tables, and that perhaps this might be something to do with it.

Is a trigger the right thing to use?


We have problem with the application run DB2ResultSet.Read(). sometime will get EROR [24000] [IBM] CLI0115E  Invalid cursor state. SQLSTATE=24000.

Database : DB2 for Linux, UNIX and Windows V10.5
Client: Windows 7 64bit

public int EventGetEvSegmentCnt(string SegmentID, string strEvntGroup)
            int strGroupCnt = 0;
            string strSQL = string.Empty;
            DB2ResultSet objRs;
            if (string.IsNullOrEmpty(SegmentID) || string.IsNullOrEmpty(strEvntGroup))
                strGroupCnt = 0;
                Common.DatabaseHelper helper = new Common.DatabaseHelper();
                objRs = helper.ExecuteResultSet(strSQL);
                if (objRs.Read())
                    strGroupCnt = 0;
                    strGroupCnt = int.Parse(objRs["EVNT_GROUP_COUNT"].ToString());
            return strGroupCnt;


Error Message:
[Information]      System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> IBM.Data.DB2.DB2Exception (0x80004005): ERROR [24000] [IBM] CLI0115E  Invalid cursor state. SQLSTATE=24000
   at …
What is SQL Server and how does it work?
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Microsoft provides a development SQL server that can run on your laptop or PC for developers to work against. I can put a Northwind database on such for example, and devise queries against it. I am looking for similar tools that I can do so for these database formats, if there is such an animal...

"OF DEL MODIFIED BY COLDEL,REPLACE" works fine and was able to load the records into the database
modified the same to
"OF DEL MODIFIED BY COLDEL'0x7c'REPLACE" as my original file is pipe delimited, now I'm getting below error for the same input file which worked for the "," delimited ( just did replace all for "," with "|") and there are no other ,

SQL3125W  The character data in row "F0-1" and column "1" was truncated
because the data is longer than the target database column.
I have the need to get a BOM with all components , currently using Tc. CK86 , but this doesn't give enough information, so I want to do this by SQL, I come from oracle background and dont know how could I get this done in DB2 R/3, I do not have access to query builder or quickview , but i do have read access via SQL, I'm currently trying to figure out a way of getting this info using tables :

MAST Material to BOM Link

Does any of you  have a solution for this?

In Oracle I did something like the following:
	,sys_connect_by_path(msil.segment1, ' @ ') AS "BOM TREE"
	,lpad(' ', LEVEL, '') || msil.segment1 Cod_Component
	,msil.description Desc_Component
FROM mtl_system_items msi
	,bom_bill_of_materials bom
WHERE msi.organization_id = 332
	AND bom.bill_sequence_id = bic.bill_sequence_id
	AND nvl(bic.disable_date, sysdate) >= SYSDATE
	AND msil.inventory_item_status_code = 'Active'
	AND msi.inventory_item_status_code = 'Active' connect BY prior bic.component_item_id = bom.assembly_item_id
                  WITH msi.segment1 = trim(:parte)
        ORDER BY 2

Open in new window

Hi All,

I could be going about this completely wrong; however, I'm working with a field that is originally a number; however, I need to use the Like command in a where clause to isolate certain results.  To further compound this, I need to use the left command to have the where clause only look at the first 5 characters.  

Here is what I have so far:


Open in new window

I don't always use sql with db2 so I might be slightly off on my logic, but any help or suggestions here would be greatly appreciated.

I need to combine address fields(addr_1, addr_2, City, St_cd, zip) into new Mail_Address field where most of the time the addr_2 contains null using DB2

In sql server I can do
rtrim(ltrim(a.addr_1)) + ' ' + rtrim(ltrim(isnull(a.addr_2,''))) + ', '  + rtrim(ltrim(a.City)) + ', ' + rtrim(ltrim(a. St_cd)) + ', ' + rtrim(ltrim(a.zip))
but unable to use in DB2.

I've tried a.addr_1 || '' '' || a.addr_2 || '', '' ||  a.City || '', '' ||  a.St_cd  || '', '' || a.zip  which only return a value in the new field if all fields are not null

I've also tried
Coalesce(a.addr_1,'') || '' '' || Coalesce(a.addr_2,'') . This only returned what was in a.addr_1 even if a.addr_2 wasn't null and I couldn't add the city, st_cd or zip to the coalesce
I had this question after viewing Number of Weeks in a particular month.

This works great for Saturday but I my week starts on Monday (day 1).  How do I change this so that I can pick a different day, example, Monday (first day) or Sunday (last day) of the week.
Thank you!


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.