[Webinar] Streamline your web hosting managementRegister Today

x

DB2

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

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?

SELECT deliver_by_end XVALUE, COUNT(*) YVALUE
FROM TLORDER
WHERE DELIVER_BY_END BETWEEN TIMESTAMP_ISO(CURRENT DATE) AND TIMESTAMP_ISO(CURRENT DATE + 3 DAYS)
GROUP BY Deliver_by_end ORDER BY 1

Open in new window

0
Receive 1:1 tech help
LVL 11
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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!!

Jim
0
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.
0
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.!

Jim
0
Hi All

I am facing problem while inserting XML data into DBCLOB column in my Table.

App is able to process 2 MB XML file, and it is not processing any file more than 2 MB.

We are using Db2 9.7

Any inputs to resolve this issue.
0
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

"SELECT * FROM TABLE WHERE ID = 5 WITH UR;"

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?

Thanks!

Jim
0
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?

Thanks!

Jim
0
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?
0
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
  4. RENAME MYTABLE_NEW to 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?

Jim
0
I am developing a Swift 4 application which talks to a JAVA DAO which communicates with a DB2 database using JDBC.  I am also using the Alamofire library as part of the application. When I make the call to the DAO using a POST request which passes a series of parameters with a content-type of x-www-URLEncoded in Postman the call completes successfully and a result is passed back.  But when I tried it in Swift 4 using an Alamofire request the JAVA DAO throws an error at the resultset of the DAO.  I don't see any recommendations on the net hoping someone else might have encounter something similar.
0
Get your problem seen by more experts
LVL 11
Get your problem seen by more experts

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

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
-- …
0
Hello,

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

 The DB2 version is 8.1.12.99 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 ?

Thanks.
0
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
0
Hi

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

Jim
0
i just need to pass an array to rpg from java but its not running error free.
here is my code

import com.ibm.as400.access.*;
import java.beans.PropertyVetoException;
import java.io.IOException;

public class CallingArrayPgm {
    public static void main(String[] args) throws PropertyVetoException{
      try{
                  
            AS400 sys=new AS400("192.168.111.211", "THARINDU", "GAYAN@123");            
            
                  int[] accounts = new int[5];
             
             AS400Array accountsarray = new AS400Array();
               
               
            ProgramParameter[] parmList1 = new ProgramParameter[1];
                ProgramParameter[] parmList2 = new ProgramParameter[1];
               
                parmList2[0] = new ProgramParameter(accountsarray.toBytes(accounts),5);  
               
             ProgramCall pgm = new ProgramCall(sys,"/QSYS.LIB/THALIB.LIB/ARRAYTEST.PGM",parmList2);
             
                 if (pgm.run()!=true) {
                System.out.println("executed");
            }else{
                   
                    AS400Text text = new AS400Text(30);
                     sys.disconnectService(AS400.COMMAND);
                   
            }
               
                AS400Message[] messageList = pgm.getMessageList();
                System.out.println(messageList.length + "TEst ok");
        
              sys.disconnectService(AS400.COMMAND);
              
            }catch(AS400SecurityException | ErrorCompletingRequestException | ObjectDoesNotExistException | IOException | InterruptedException e) {
                  System.out.println(e.toString());
      …
0
Hi Team,
I am able to connect and run single statements in db2 server through Linux command line but unable to run PL SQL Anonymous blocks.

First I typed the "db2 -t" then enter the db2 mode and then given below command.

db2 => BEGIN
  dbms_output.put_line( 'Hello' );
  END;db2 (cont.) => 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 "BEGIN" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<call>".  LINE NUMBER=1.
SQLSTATE=42601

Please help me how to run.

Thanks in Advance.

Hari
0
Hi All,

Could you please anyone suggest me one below requirement.
I need to write a query like
Step 1:
select query to check if my table is empty or not .
If empty  then  run step 2
EX: insert into ...
else
it has to end.
0
Hi Team

Percentage  % Comp memory at OS level reached  98 in topas report, but DB2 memory utilization is 80 percent around from
db2pd -dbptnmem.

Can you pls give some inputs to reduce the % Comp memory usage with out rebooting the Server.
0
Free Tool: SSL Checker
LVL 11
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.

Hi,

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?

Thanks
Venkatesh
0
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.
0
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?
0
Hi Team

I facing below problem with my lssam out put. I think it is TSA state issue.

Can you pls guide me to resolve this issue.

Its an AIX and Db2 9.7

Online IBM.ResourceGroup:db2_db2inst1_db2inst1_DBNAME_C-rg Nominal=Online
        |- Online IBM.Application:db2_db2inst1_db2inst1_DBNAME-rs Control=MemberInProblemState
                |- Online IBM.Application:db2_db2inst1_db2inst1_DBNAME-rs:sscdb1
                '- Offline IBM.Application:db2_db2inst1_db2inst1_DBNAME-rs:sscdb2
        '- Online IBM.ServiceIP:db2ip_10_11_72_010-rs Control=MemberInProblemState
                |- Online IBM.ServiceIP:db2ip_10_11_72_010-rs:host1
                '- Offline IBM.ServiceIP:db2ip_10_11_72_010-rs:host2
Online IBM.ResourceGroup:db2_db2inst1_db2inst1_DBNAME2-rg Nominal=Online
0
WITH SFPMS2 AS (
WITH SFPMS AS (Select ....

I am getting red Xs and no explanation.  Can you now do thius in DB2?
0
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-07.11.34.627255-360 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"
0

DB2

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
DB2
<
Monthly
>