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 am getting ready to copy the production database to our test server and then scrub all the sensitive data.  What I would like to do is to script out all the permissions on the test database before I restore the production database.

Using TOAD, I can click on a user and view details and it will script all the grants for me, but there are about 3 dozen users in test and I don't want to do each one by hand.  I would love to capture the SQL that TOAD is generating to get that script and then I could run if for all the users.

Does anyone have a script like this?  That will give me the grant statements for all the users on a given database?  I could do the one at a time method but that will be a real pain.

Thank you!

Get expert help—faster!
LVL 12
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

db2 convert time to string
Hi I want to convert a time files (NOT Date, NOR timestamp) to a string representation with the format: 'HH24MISS'

I've tried:
1. TO_CHAR(mytime, 'HH24MISS')
3. SUBSTR(LPAD(mytime, 6, '0'), 1, 2) ...

None of it worked
Many thanks for your support
tsql vs regular sql syntax


i am going though above syntax

is oracle ms sql db2 etc syntax same or differnt?
DB2 10.5 on Windows

So I am trying to teach myself how to install DB2 and restore a dev database.  So I install DB2 10.5 on windows without issue.  I then run the sample database setup and I can connect to it no problem.

When I copy over the DEV database backup and try to restore it, problem.  I get this.

C:\Windows\system32>db2 restore database DEV FROM X:\dbbackups
SQL1092N  The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation.  User
ID: "xxx".  SQLSTATE=00000

Now XXX is in the Admin group on the windows server and it is running the DB2 instance service.  It should have full control of the server.  I looked at the sys roles (sysmon, sysadm,sysctrl, and sysmaint group names and set them all to Administrators which is the admin group name on the windows server.  I just don't get it.  What am I missing?


I wrote the below query against a DB2 database.
It returns good results, but is incredibly slow (90 seconds).
What can I do to make the query more efficient?

GROUP BY Deliver_by_end ORDER BY 1

Open in new window

DB2 10.5 on Windows

I need to do some load testing for our system and am having issues setting up DB2 to record what is going on.  In MSSQL Server it is pretty simple using SQL Profiler but I can't seem to find a similar thing for DB2.  Here is what I would like to do.

1. Capture all SQL Statements ran during the test.
2. Capture all the response times associated with the SQL Statements from #1.
3. Be able to see the explain plans for any of the SQL Statements from #1.
4. Capture all the stats for DB2 during the test (memory usage, buffer hits, hard reads, etc).
5. Display this all in a nice format that can be used to explain what was happening to DB2 during the test (not a must as I can always whip that up myself).

Since this is on a windows platform, I would be using Perfmon in conjunction to watch the windows parameters like IO, CPU, etc.

So how do most people do this in DB2?  I am hoping there is a performance command that is built in that will give me what I need but so far I do not see one.

Thank you!!

Hi All

I am facing the below Error message in my Application logs.

 Invalid operation: Connection is closed. ERRORCODE=-4470, SQLSTATE=08003

Can any one give inputs to solve this.

Does increasing LOGFILSZ help to solve this.

Application is not able to connect to DB.
DB2 10.5 on Windows.

Does anyone know of a good tool to schedule SQL scripts with DB2 on windows?  Currently, I am using bat files that get run from the Windows Task Scheduler but that make me really nervous.  Would love to have like a SQL Agent (from Microsoft SQL Server) app to do this.  Thanks.!

DB2 10.5 on Windows

I am having issues with timeouts in my dev system.  The lock timeout parameter is set to 55 seconds.  The way I understand it is that if the operation cannot acquire the lock it is trying for within 55 seconds it will error out with the message...

ERROR [40001] [IBM][DB2/NT64] SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".

The RC 68 is "The transaction was rolled back due to a lock timeout."

I have this happen when I am trying to create an index or an FK constant, which I can understand.  It can also happen when I do


So my question is two-fold.

1. How can I see what is preventing me from acquiring the lock?
2. How can if fix this to make it stop happening?

I have tried to use MON_GET_APPL_LOCKWAIT to figure it out but not sure what is being returned.

It is very frustrating.  Any suggestions?


DB2 10.5 on Windows

Here is another strange one.  I needed to load a large text file so I created a table and started the Load procedure when the server got rebooted (automatic window update).  The database came up ok after the reboot but now I can't drop the table I was loading into.  The table is empty.  I get this error.

ERROR [55039] [IBM][DB2/NT64] SQL0290N  Table space access is not allowed.

So I google this and it is normally linked to one of these.

1. Database crash and recovery
2. Table is in load pending state
3. Tablespace is in not normal state

Well, none of these apply to me.  The database came back fine.  I can run queries and do everything just fine.  The table is not in load pending state.  And the tablespace is in a normal state.  Other tables in this tablespace are acting normally.  It is just this one table.

I can select from the table but that is it.  I can't insert, or reorg or drop.  I just want to drop it.

Any suggestions?


Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

RPGLE programmer using IBMi v7r1, need to insert/update data on a MS SQL database easily. I'm familiar reading and processing ifs file using bnddir('QC2LE') in my RPGLE programs.
Surely there's a way to push records to an MS SQL server?
DB2 10.5 on Windows

I had a large table that I moved to a different tablespace by doing the following.

  1. Copy data into new table called MYTABLE_NEW that was created in the new tablespace (exactly matches original table).
  2. Drop all FK constraints on MYTABLE
  3. Drop MYTABLE
  5. Recreate all FK constraints

When I tried to recreate the FK constraints I get this.

Lookup Error - DB2 Database Error: ERROR [23520] [IBM][DB2/NT64] SQL0667N  The FOREIGN KEY "SomeFKName" cannot be created because the table contains rows with foreign key values that cannot be found in the parent key of the parent table.

When I look for invalid Objects there are now a whole bunch (MYTABLE was very connected to other tables) of invalid procedures.

When I try to revalidate these procedures I get this.

DB2 Database Error: ERROR [5UA03] [IBM][DB2/NT64] SQL20508N  An error occurred during the revalidation of object "SCHEMA1.PROCEDURE". Operation "REVALIDATION" failed with SQLCODE "-501", SQLSTATE "24501", and message tokens "".

I am guessing that when I dropped the original MYTABLE and then renamed MYTABLE_NEW some internal data got messed up.  The question is how do I fix this?  I am tempted to stop the DB2 Service and restart it but I am afraid this might make things worse.  This is a dev server so not a world-stopping event but still, I need to get this fixed.  

Any suggestions?

DB2 10.5 on Windows

I want to restore my test database on a windows server to test2 database on the same server but I am a bit confused by the RESTORE WITH REDIRECT method.

So the test database is on the D: drive and I want to put the test2 database on the Z: drive.  I ran the RESTORE ... REDIRECT GENERATE SCRIPT so I have a script to work with.  The script has all the tablespaces for the test db in it but it is all commented out.  Like this.

-- *****************************************************************************
-- ** Tablespace name                                  = SYSCATSPACE
-- **   Tablespace ID                                      = 0
-- **   Tablespace Type                                 = Database managed space                      
-- **   Tablespace Content Type                  = All permanent data. Regular table space.    
-- **   Tablespace Page size (bytes)            = 4096
-- **   Tablespace Extent size (pages)        = 4
-- **   Using automatic storage                  = Yes    
-- **   Storage group ID                                = 0
-- **   Source storage group ID                   = -1
-- **   Data tag                                                = None
-- **   Auto-resize enabled                           = Yes    
-- **   Total number of pages                      = 501844
-- **   Number of usable pages                   = 501840
-- **   High water mark (pages)                   = 113376
-- …

  I need to create a Link Server between MS SQL Server and DB2.

 The DB2 version is and runs in a Windows 2000 Server.

 The MS SQL Server version is 2008 R2 and runs in a Windows 2008 Server R2 Standard.

 How can i create a link servers with this two databases ?

I'm not trained in AS400.
We don't use tapes anymore but backup to a remote Data Domain.

AS400's audit journal receiver of our developmt AS400 is taking too much space in our current weekly backups with retention of 6 years.

a) Without compromising on the 6 years' retention, how can we reduce the space taken by the backups in the Data Domain?

b) I suppose weekly backup is taken because the receiver journal is rotated weekly: is there such thing as incremental or
    differential backup in AS400 for audit journals?

c) can we do it monthly with differential backups or what other strategy can we do it?  

d) can it be compressed?   the Data Domain solution we used is from EMC.  Anything on deduplication??

We are on OS400 V7 R2  running DB2

I am facing the below message in diag log and could not start HADR.

MESSAGE : HADR primary did not establish connection with standby within timeout
          and will shut down. BY FORCE option required to start primary without
          standby. Timeout seconds =

Can you pls share some inputs.
DB2 10.5 on Windows

OK, this is killing me.  It should be a simple thing but I have hit a wall and am not sure what is going on.  On my development database, I am issuing the following command.

db2 backup db Database1 online to z:\db2backups compress without prompting

Open in new window

But after a minute or so I get this.

The requested command or operation failed because the user ID does not have the authority to perform the requested command or operation:  User ID "MyUserID"

Open in new window

MyUserID should have SYSADM rights on that database.  It is a domain user and it is an Admin on the windows server.  So I don't understand what the issue is.  I also made sure MyUserID has full privileges on the Z: drive.  

How can I be sure MyUserID is SYSADM and what might I be missing?  This is really killing me because it should not be this hard to backup my db.  What am I doing wrong?  This works fine on my production server, but it uses a different user.

Thank you.


I am running a join query using 7 tables, where I am providing the fields like FNAME,LNAME, MNAME and Mobile phone number from one table alone as input.

My result should pull the details from the rest of the tables and show below, however I am getting a very vast result as there is only one entry for my input in main table,

For example, Venkatesh sarivisetty having one entry in customer table and getting more than 1000 entries from the rest of the tables, Is it true or am I missing anything here?

I currently use Access to generate reports from MySQL and DB2 databases. I'm basically using Access as a reporting frontend using Pass Through queries. Is it possible to automate report generation from outside of Access, maybe through the use of an API?

I have reports that need to be generated as events take place in the database. For example: if an order comes into our DB then a pretty PDF copy of that order needs to be sent via email back to the customer.
Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

We had a client upgrade their AS400 OS from 5.4 to 7.1.  Their QNTC was writing to a 2003 server.  We have an AS400 routine that converts files to PDF, writes them  to a QNTC path and then the website reads them. It was working fine before the upgrade. Now it give a "file not found" error. When I open up iNavigator and use the same login as the website I cant see any QNTC paths and I was able to see them before the upgrade.  Any thoughts what might be wrong?
WITH SFPMS AS (Select ....

I am getting red Xs and no explanation.  Can you now do thius in DB2?
Hi All

I have noticed a OS error in my diag log and i also notice that HADR is not in sync during this period.

From error message, I am not completely clear which resource is unavailable ?

I suspect that Network is not good during that time.
Will there be chance for any other resources?

How to dig it further?

Its an AIX machine.

Below is the DIAG log piece.

2017-12-23- E196685A513        LEVEL: Error (OS)
PID     : 57018777              TID  : 200         PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000
EDUID   : 258                  EDUNAME: db2sysc 0
FUNCTION: DB2 UDB, oper system services, sqlorqueInternal, probe:9
MESSAGE : ZRC=0x870F0041=-2029060031=SQLO_QUE_NOT_SENT "Message Not Sent"
          DIA8557C No message was sent using the message queue.
CALLED  : OS, -, select
OSERR   : EAGAIN (11) "Resource temporarily unavailable"
I've been tasked with updating remote MS SQL database tables. I'd like best ideas for utilizing our IBM i to do this. I'm primarily and ILE-RPG, RPG Freeform programmer, but have worked using RPG-XML (which we have) and consuming  web services via a local MS SQL database. I'm also familiar with SQLLERPG to a degree.    

I have access to 'data source', 'Initial Catalog', 'User ID', and 'password' for the remote database tables.

All ideas are welcome and will rewards points accordingly.


Phil Knox
But whenever possible have a unique clustered index.  For example, on the state codes table, a unique clustered index on state_code would work just fine.
i am reading as above.
what is meaning of unique clustered index and how it is different from regular index. please let me know advantages of using it.

please advise
DB2 10.5 on Windows

I have written a stored proc that checks the condition of all my indexes and if they need to be REORGed then I do it.  Here is how I check the indexes per schema.

      CALL REORGCHK_IX_STATS('S', 'Schema1');


Open in new window

When I look at the index stats table I created, I see this.

ID       Schema       Table                       INDEX                                INDCARD   NLEAF     REORG
534    SCHEMA1   SESSION_TOKEN	SESSION_TOKEN__IDX     530320	4073	     *----

Open in new window

So I run this.

               'REORG INDEXES ALL FOR TABLE ' 
     		|| RTRIM(SUBSTR(TABLE_SCHEMA,1,11)) || '.' || RTRIM(SUBSTR(TABLE_NAME,1,31)) 
     		|| ' ALLOW WRITE ACCESS ' 
     		|| CASE 
     			WHEN SUBSTR(REORG_FLG,4,1) = '*' 
     			WHEN SUBSTR(REORG_FLG,5,1) = '*' 
     			ELSE '' END 
     		|| ';' 
     WITH UR;

Open in new window

To create and run this command.


Open in new window

It runs and seems to work, but when you check the exact same index using REORGCHK_IX_STATS five seconds later,  it has not changed at all.  All the stats are the same.  If an index has a REORG status of *----- does it not need to be REORGed?  I am somewhat confused.

Due to this, the same 50 tables are being REORGed every night.

Any help would be great.

Any Merry Christmas and Hapy Holidays to everyone!!!



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