Improve company productivity with a Business Account.Sign Up

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

HI All

I could see in db2top report that Application Transactions are in Roll Back in Progress (30%) like this. There are around 8 Applications like this.

In Diag log, I could see lock escalation messages.

May I know what could be the reason for this?

I am seeing the following message in Diag log:

MESSAGE : ADM5501I  DB2 is performing lock escalation. The affected application
          is named "APPNME", and is associated with the workload name
          "SYSDEFAULTUSERWORKLOAD" and application ID

Any inputs pls
0
Free Tool: IP Lookup
LVL 12
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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 All

How to set the
allowNextOnExhaustedResultSet , queryCloseImplicit  and resultSetHoldability  values in db2 LUW Data Base

Can you please kindly provide inputs to change these settings.

My Application is facing the following error:

Invalid operation: result set is closed. ERRORCODE=-4470
0
SELECT COUNT ( DISTINCT cust_code ) AS "Number of employees"
FROM orders;

checking above query at
https://www.w3resource.com/sql/aggregate-functions/count-with-distinct.php

how to display those unique values of  cust_code rather than just count
please advise
0
DB2 10.5 on Windows

I am looking for a good tool (free is better) that will compare one DB2 database to another.  I want to be able to compare our production to our staging to make sure they are in sync before we do testing.  I have one named SQL Power Architect what works pretty well but it only allows you to compare one schema at a time.  I have over 30 schemas so that wouldn't be ideal.

There is one called DTM Schema Comparer that will do what I want and I can schedule it, but it has a cost associated with it and before I go begging for money I just wanted to be sure there is not a similar free tool.

So I need it to compare all schemas, not one at a time, generate a readable report and SQL change file and be run from command line.

Tall order but if you don't ask, you don't know.

Thank you!!

Jim
0
Hi ALl

I am facing the below error message while connecting to DB from one DB to another.

SQL30081N  A communication error has been detected. Communication protocol
being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location
where the error was detected: "XX.XX.XX.XX".  Communication function detecting
the error: "connect".  Protocol specific error code(s): "110", "*", "*".
SQLSTATE=08001

It has happening after the rebooting the OS.
What could be the reason ?

Pls give your inputs.
0
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!

Jim
0
I use IBM Data Studio for most DB2 query work. The following query, attempting to pick 'dynamic' string values from a CLOB, shows no errors in the
Studo query window but when run says: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1 OF SUBSTR IS INVALID. SQLCODE=-171, SQLSTATE=42815, DRIVER=3.67.28.
Is it even possible to explore a CLOB column for strings contained in other table columns dynamically ?

SELECT DISTINCT A.HP_EXR_ID
FROM db2prod.PRV_BPDE_PRV_MHG A
    ,db2prod.T401ALTC B
    ,db2prod.T401APFN C
    ,db2prod.t401ahpa D
WHERE A.HP_ID = B.HP_ID
and   A.HP_ID = C.HP_ID
and   A.HP_ID = D.HP_ID
and   substr(a.MHG_BPDE_DTA_XML_TX,1,3000) like '%c.pfn_prv_lst_name%'
and   substr(a.MHG_BPDE_DTA_XML_TX,1,3000) like '%substr(c.pfn_prv_fst_name,1,4)%'
and   substr(a.MHG_BPDE_DTA_XML_TX,1,3000) like '%substr(d.HP_ADDR_STR_ADDR,1,8)%'
and   substr(a.MHG_BPDE_DTA_XML_TX,1,3000) like '%substr(d.mn_zip_addr,1,3)%'
and   substr(a.MHG_BPDE_DTA_XML_TX,1,3000) like '%d.pol_rgn_code)%'
AND B.HP_ALRT_TYP_CD IN ('G1')
AND B.HP_ALRT_TYP_STA_CD = 'A'
AND B.ANW_TSP_GRP =
(SELECT MAX(BB.ANW_TSP_GRP)
FROM db2prod.T401ALTC BB
WHERE BB.HP_ID = B.HP_ID
AND BB.HP_ALRT_TYP_CD = B.HP_ALRT_TYP_CD);
0
Hi All

I need to take an Offline Backup as logging is not enabled on my DB.

But I am failing to take the Offline Db Backup cause Applications are not getting killed.

I have forced the Applications with force command for 10 times,  and  quiesced the DB and Instance.

But still Db Backup command failing.

Below are the Errors I have faced when I run the Backup command.

SQL2413N  Online backup is not allowed because the database is not recoverable
or a backup pending condition is in effect.
SQL2413N  Online backup is not allowed because the database is not recoverable
or a backup pending condition is in effect.
SQL1350N  The application is not in the correct state to process this request.
Reason code="1".
SQL2413N  Online backup is not allowed because the database is not recoverable
or a backup pending condition is in effect.
SQL1035N  The database is currently in use.  SQLSTATE=57019
SQL1035N  The database is currently in use.  SQLSTATE=57019

Any Inputs please with your goodness.
0
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')
2. VARCHAR_FORMAT(mytime, 'HH24MISS')
3. SUBSTR(LPAD(mytime, 6, '0'), 1, 2) ...

None of it worked
Many thanks for your support
0
what SQL code to write to Execute a block of code containing Insert and Update statements at a specific time interval in DB2.
example :-
call procedureName( start and end time)
Block of code that will execute in a specific time period else it will execute some other block of code and exit.
0
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

tsql vs regular sql syntax

https://www.w3schools.com/sql/sql_groupby.asp

i am going though above syntax


is oracle ms sql db2 etc syntax same or differnt?
0
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?

FRUSTRATED IN STL

Jim
0
DB2 10.5 On Windows

I am trying to use CommVault to backup my DB2 databases and am having permission issues.  When I run the backup through CommVault I get the following.

ERROR CODE [59:41]: Browse operation on database [xxx] could not be completed. Error [ ---- error report ---- app. message = db2GetSnapshotSize line = 783 file = Db2Browse.cpp SQLCODE = -1092 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: "CVADMIN". --- end error report --- db2GetSnapshotSize() failed: Exit.]. Please check the database configuration, user privileges and ensure that the product's services are running on client, server, MediaAgent and then resume.

From what I have read, this means that the user CVADMIN does not have SYSMON permissions.  So I did the following.

1. Check that the server has a group defined for SYSMON.  It does.   SYSMON group name     (SYSMON_GROUP) = NAGIOS
2. On windows, I created a group name NAGIOS and added CVADMIN to it.

From what I read, that should grant CVADMIN SYSMON permission but it is not working.  Still get the same error.   Is there a specific GRANT I have to issue?  IBM has a lot of information on this but none of it is clear to me.  

Thank you.

Jim
0
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
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
Free Tool: ZipGrep
LVL 12
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.

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

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
>