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

SELECT UPPER(COLUMN1)
FROM SCHEMA1.TABLE1

SELECT UPPER(COLUMN2)
FROM SCHEMA2.TABLE2

i have 100 records in column 1 and column 2 with roughly half capitol words(HONDA etc.) and rest of half small words(nissan etc.)

when i do UPPER character function

i expected to see 50 records with values like NISSAN


when i do LOWER character function
i expected to see 50 records with values like honda

but i got only 2 records.

can you please advise how to fix this
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.

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

what kind of encryption DB2 is offering? what tier are they doing to protect?
0
Hello, I need to mix 2 queries.
How can i do it?

1.

SELECT * FROM (
SELECT * FROM (
SELECT '0' OUTCHK, CLSMON, ZCODEP, ZCOROT, BIZMON, ZCOVVD, A.VVDSEQ, A.SVCTYP, STAPOT, STADAT, DIGITS(DEC(STATIM,4,0)) STATIM, STAKND
       , ENDPOT, ENDDAT, DIGITS(DEC(ENDTIM,4,0)) ENDTIM, ENDKND, LLPPOT, LLPDAT, LLPKND, OPRDAY, PRODAY
       , CASE WHEN TRIM(NEXVVD) = 'PO' THEN 'Phase-out'
              WHEN NEXVVD LIKE '_______Z' THEN 'B075참조'
              WHEN C.LTMOPR = 'HMM' THEN '자사운용선박'
              ELSE '슬로티지선박' END AS RMK, NEXVVD
       , CASE WHEN COALESCE(LLPKND,' ')||COALESCE(STAKND,' ')||COALESCE(ENDKND,' ') IN ('CCA','ALL','ALC','ALA','ACL','ACC','ACA') THEN 'Y' ELSE 'N' END ERRYN
       , PROPER , VVDSTA, MVPWKY AS MVPWKY
       , ROWNUMBER() OVER (PARTITION BY MVPTYP, MVPVVD, MVPTR2, MVPROT, ZCOVVD ORDER BY MVPWKY DESC) AS ROWNUM
       , '' AS INTAG
       , A.CFMTAG, ZCOBON
       , CASE WHEN STADAT IS NULL OR STADAT = '' THEN '1'
              WHEN ENDDAT IS NULL OR ENDDAT = '' THEN '1'
              WHEN LLPDAT IS NULL OR ENDDAT = '' THEN '1'
              WHEN ENDDAT < STADAT THEN '1'
              ELSE '0' END AS CHKVLD
       , 'C' AS OUTDIV
       , 'test' AS OUTTEST
  FROM PLIBCO.ZCORESVVDT A LEFT OUTER JOIN PLIBOP.MRKAVSLP B
        ON MVPTYP = 'B' and MVPLOD = 'L' and MVPKND = 'H/Q' and MVPROT = ZCOROT and MVPVVD = ZCOVVD
        and MVPTR2 = CASE WHEN ZCODEP = 'P1060' THEN 'UT'
                          WHEN ZCODEP = 'P1061' THEN…
0
hi,

for DB2, any feature/way to move all DB2 files on RAM , not just logical table / objects?
0
hi,

Automatic storage management is only logical structure only and not related to what oracle ASM can offer, it can't load balancing data based on need, right?

also any limitation on using that ?
0
Hi ALL

Good Morning.

I am facing problem with DB2 Federation. I am doing between 10.5 and 9.7 .

create server servername type db2/udb version '9.7' wrapper DRDA authid "db2inst1"  password "password" options(add dbname 'DBNAME');


create user mapping for sourceinstancename server servername options ( remote_authid 'db2inst1', remote_password 'password');

Any iputs please..
0
I need to connect to  AS400 ISeries using .NetFramework.

I have installed IBM Client Solutions to bring down the driver needed.

I am using the Oledbcommand to invoke the procedure/library in JDE.

SQL state error code is 22010 and native error -363.

This code is being used by BizTalk and perfectly working for them. I need to replace the BizTalk component with the .Net program. The only difference between BizTalk implementation and my implementation is they are using IBM i Access for Windows software to get the drivers and I am using IBM Client Solutions as IBM I Access for windows is no more supported by IBM.

Can some one please help me as this has been an issue for me for the last 2 weeks. I have tried several different ways and non of the steps I have taken worked.

If I give the incorrect library name or incorrect parameter name/incorrect number of parameters while calling, all these are giving me the errors that I am supposed to. Connection is successfully getting opened, but error is while executing the command. Please let me know if you need any other information

The IBM site has the error description as follows

SQL0363 Message Text: Extended indicator variable value for item &1 not valid. Cause Text: The value of the extended indicator variable for item &1 was not within the range of acceptable values. The value must be positive or in the range of 0 to -7 inclusive. Recovery Text: Change the value of the extended indicator variable to one which …
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
Cloud Class® Course: Microsoft Azure 2017
LVL 12
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

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

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
>