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

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
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
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
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
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
Free Tool: Path Explorer
LVL 12
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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
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
I am using MS SQL and have the following example setup.

Database - DB1
Table - Status
Field - Active

Database - DB2
Table - Events
Field - DateTime

Both tables have a common ID field - UID

What I am trying to achieve :

- Review all entries in Events in DB2
- Identify any entries that have a DateTime which is more than 12 hours older than the current time
- For any entries above that are older than 12 hours set the value of the Active field in the Status table in DB2 to "False"

I have found lots of HowTo info online for replicating a value from one table to another using JOIN but this is not replicating data. This is setting a value in one table in one DB based on values for an entirely different variable in another table in another DB.

I'm a t-sql novice and have been trying to work this out but with no success so far.

Any advice would be greatly appreciated.

TIA.
0
Is there any best option to export data from table to file using Db2 commands other than export command.
My table contains 46gb of data.
0
How to export 46 gb of table to file using cursor / stored procedures in Db2.?
Do we have any options exporting data to file from table other than export command.
0
Hi.... I need help on loading fixed length (NACHA format) file to DB2 table.  DO not need to process (i mean parsing or transformation) the file, but need to load it.

here is the file structure

5euqy euq 6767 CCD wqyeu
2 4545  435435 tert5656565
3 434 343 erer eerer 4545 e
4 fffds 5656 787 433 78888
6 wetwyty ghghuyuy wyeuy
7 6556 878 989 990
8 ywieyuwqyu
5euqy euq 6767 PPD wqyeu
2 4545  435435 tert5656565
3 434 343 erer eerer 4545 e
4 fffds 5656 787 433 78888
6 wetwyty ghghuyuy wyeuy
7 6556 878 989 990
8 ywieyuwqyu

file has 8 type of records, each record type has different lay out. Block 5...8 is repeating in the file.  class code (PPD, CCD) mentioned in record type 5. based on that value layout of record type 6 changes.

I need to load this file to db2 table. Please suggest how this can be done.
0
I am trying to run IBM Data Studio 4.1.2. I appear to be able to install it and have run for a day or so after which it errors out and references the attached log file. Any feedback would be appreciated.
1510240418889.log
0
Hi Experts,

If I use in SQLRPGLE the construction:

Exec SQL SET :VARF = 12345;

The RPG variable VARF will change to 12345.
Now I like to make the target-field also variable, so I tried:

SQLCMD = 'SET :'+ %TRIM(VARN) + '=' + %TRIM(VARV);
The value of SQLCMD is now : "SET :VARF=12345"

Exec SQL EXECUTE IMMEDIATE :SQLCMD;

But this will NOT assign a value to VARF,  
Why isn't that working?
0
Hello,

We have an index corruption Issue at (HADR environment ) founded into db2diag of primary caused crash , this corruption occurred on primary. I've decided to

1-takeover on standby as no page corruption there and deactivated primary to avoid this corruption moved to standby
2-backup (new primary ) and drop db on old primary
3-restore on old primary (standby) , then working successfully

but doesn't know what the root cause of this page corruption I faced this issue twice during 40 days. My suspect is maybe index not building at true bases, even db2support hadn't got particularity more details they said maybe application code

Is there any way to ensure an index is working fine or not?
As my understanding, there are many flavors of index-related corruption problems, for instance:

Unique index contains duplicates with different RID(s) or same RID(s)
Multiple index entries pointing to the same RID
Index key is out of place (wrong index key order)
Row exists, but index keys do not exist in any or some of the indexes
Index entry pointing to an empty data slot or unused data slot or RID is invalid
Incorrect previous or next index page pointers, incorrect high key or other corruptions on index pages

And if this happens again, is there any different method unless backup and restore?

I found This maybe can be a solution could you  please give me your thoughts  can i do this as a solution ???

REORG the corrupted indexes or mark the index as bad by …
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
>