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 Expert(s),

Is there a way to write Data (eventually with SQL)  from the AS400-DB2 to a MySQL-DB or Oracle?
0
Cloud Class® Course: Amazon Web Services - Basic
LVL 12
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Hi All

Greeting!!!

in my WCS DB, some of the transactions are failing and throwing the exceptions for the following SQL statement:

INSERT INTO XORDERS (ORDERS_ID, PICKUPPERSON, PICKUPPRSNCNT, DELIVERYTYPE, GRTSTDATEINDI, SHIPPINGMETHOD, MAILSTATUSINDI, STATUS, OPTCOUNTER, LANDMARK, SELLERSTORE, STORESEQUENCENUMBER, FIELD1, FIELD2, FIELD3, ALLSHPCHRG, STORETRREQQTY, STKSHPCHRG, SPLSHPCHRG, MSIBIN) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Probably it might be due to primary or foreign key constraint errors or may be invalid data (Data with wrong data Type ) is received from Application.

My DIAG Level is 3, How to capture the whole transaction details(with actual values) in DB2 so that I can analyze the case more better.


Please kindly provide me inputs on this.
0
HI ALL

Greetings!!!

what is the best approach to copy a schema to a new schema with in same Data Base if I have LOB , CLOB and XML data Types.

I also have generated columns in my schema.

In IBM documentation, it is clear that it (ADMIN_COPY_SCHEMA utility) does not support XML Data , as below.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022035.html 
(Please refer the heading Restrictions )

How to handle this case of copying the schema.
0
Hi All

Greetings!!!

Hope you are well.

I need to copy a Schema from one machine to other machine,

Both machines have Db2 instance of same version (10.5)

But Schema is having data of LOB data type.

Which method is simple to copy the entire schema with structure and data.

I know ADMIN_COPY_Schema only supports on same machine and under same instance .

But db2move utility does not support LOB data type (I think).

Any inputs on this Please.

Thanks in Advance!!!!
0
Hi All

Greetings!!!!

Hope you are doing well.                      

I need a mail alert whenever a DML  (INSERT,UPDATE,DELETE)  statement is executed in my DB2 Data Base.

Do we have any feature in DB2,  through which I can achieve my requirement.

Thanks a lot in advance!!!!
0
TABLE1
store_ID
Relation_ID
Store_Open
Store_Close
Trans_Date


TABLE2
Relation_ID
Relation_Name

TABLE3
Store_ID
Store_Name

TABLE1 is transactional, table 2 is static as is table 3
I need to find all the stores and their names from TABLE3 where the stores opened in 2016. Also all the names where they closed in 2017. But only for a Relation_Name = "MyStore"
I have a query that, without using the transaction dates, gives results, but I'm sure its wrong.  I need to only use the Max record previous to the end of 2016.
(I know the date syntax can change from platform to platform - this is for DB2, or oracle)

The query that almost works is:
Select distinct(A.Store_ID)
,A.Store_Name
from TABLE3 A
join TABLE2 B on A.Relation_ID= B.Relation_ID
join TABLE3 C on A.Store_ID = B.Store_ID
where B.Relation_Name = "MyStore"
and C.Store_Open < '01/01/2017'
and C.Store_Close > '01/01/2017'

How do I only use the Max records of TABLE1 in 2016?
0
Hi Experts,

Is there any way to compare 2 timestamp and pull records from DB2 using query.

 Query wil be having input file which contains basic timestamp .We have to compare this timestamp with the timestamp in process_date column and have to pull all records which happend after this basic timestamp.

How to compare timestamps and pull records ...

Any help regarding this would be helpful.

Thanks in Advance.
0
Hi,

Can anyone advice what is the command to check for total user mailbox and size?

eg.
Sum up
DB1 => 500 Users Mailbox and 300GB
DB2 => 1000 Users Mailbox and 400GB

Tks

Lucky
0
Hi Experts,

Good Day

As per my work , I need to write a DB2 sql query which has to pull records after a specific timestamp..Already in the table there is a column for timestamp(transaction_date). So basically It should compare with this column value and If the transaction-date timestamp is greater than the specific time stamp , It should pull those records.

Specific timestamp will be present inside a text file(/home/user/inputpath/Extracted-date.txt).
How can I write query? input should be taken from the text file and compare with the transaction-date timestamp.

select query where 

Open in new window

Thanks in Advance.
0
I want to export data from Mainframe DB2 to Excel through VBA. I am not able to establish connection through following connection string.


Set con = CreateObject("adodb.connection")
Set mrs = CreateObject("adodb.recordset")

With Sheets("Sheet1")

con.Open "Driver={IBM DB2 ODBC DRIVER};Database=<dbname>;Hostname=<hostname>;Port=1433;Protocol=TCPIP;Uid=user;Pwd=password;"

Its giving run time error for data source not found and no default driver installed.
0
Upgrade your Question Security!
LVL 12
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

hi,

what kind of encryption DB2 is offering? what tier are they doing to protect?
0
hi,

any Hadoop to DB2 gateway/proxy scaleoutable solution for DB2?
0
Hi All

I have two tables with CLOB data type in a DB2 LUW 10.5 DB.

I want to compare all the columns and rows in the two tables and want to find any mismatches

It looks EXCEPT function does not work for CLOB data types columns.

Any suggestions on this Please.

Thanks in advance.
0
Hi All

I have found some junk characters in my table some thing like small square box.

Can you please guide me how to remove them from the table.

The column data type is CHARACTER(100).

These junk characters are in the middle of character string. I am not sure how they came in to DB.

Thanks in advance!!
0
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
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
Cloud Class® Course: CompTIA Cloud+
LVL 12
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

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
>