[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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 Below is my sample data and I need to write a SQL query to derive 6 columns from the column CONTACTVALUE below and this is my CASE statement as per the requirement

SELECT
CASE WHEN CODE ='VOI' AND sub-code='PER'  THEN contactvalue END AS PersonHomeTel
,CASE WHEN CODE='VOI' AND sub-code='PRO'  THEN contactvalue END AS PersonWorkTel
,CASE WHEN CODE='VOI' AND sub-code='OTH'  THEN contactvalue END AS PersonMobileTel
,CASE WHEN CODE='EMA' AND sub-code='PER'  THEN contactvalue END AS PersonHomeEmail
,CASE WHEN CODE='EMA' AND sub-code='PRO'  THEN contactvalue END AS PersonWorkEmail
,CASE WHEN CODE='EMA' AND sub-code='OTH'  THEN contactvalue END AS PersonOtherEmail
from my table

however I need help in modifying my query so that I need to pick up the contactvalue based on the above rules
based on the LATEST date in case of more than one record. So for example for cust_id 1, for code VOI and sub-code PER has 2 records, then the one with LATEST date should only be picked up. If the LATEST date record contactvalue is NULL then the next contactvalue should be picked up based on the next highest date.

can anyone modify my query to meet my requirement ?


cust_id   code sub-code contactvalue      Date
1         VOI   PER      me@gmail.com    18/05/2014
1         EMA   PER      00447804584     18/06/2014
1         POS   OTH      00437804587     18/01/2014
1         VOI   PER      00447804687     18/06/2014
1         EMA   PER      00478025848     18/06/2014
1 …
0
OWASP: Forgery and Phishing
LVL 12
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

db2 export with delimiter set explicit
I execute the db2 command on a windows machine in the db2 command line interpreter shell
I execute a SQL command against db2 and export the result in a delimited ascii file. I want to be able to set the delimiter, default is space with fixed column length.
I start the export with the following command

db2 -tvmf \\somePath\mySQLCommandToExecute.sql > \\somePath\ExportedResults.csv
and the mySQLCommandToExecute.sql file
CONNECT to MYDB USER MYUSER USING MYPW;
With MultipleReferences as(Select distinct coulnm1 , tb1.column2, column3, tb1.column4 from schema1.table1 tb1, schema1.table2 tb2 where column2 > 1 and column3 <> column4 AND COALESCE(tb2.column1,tb2.column2) = tb1.column1 ) select * from MultipleReferences where column4 in('somefilter1', 'somefilter2');
This is only a schematic example of my SQL. As the SQL command is huge (a lot of filters given by a excel sheet) I generate the sql command via a programm and want to have it in a separate file for execution.

As my SQLToExecute is very long I want to have it in a separate file and not directly entered on the db2 command line. So any suggestions like db2 export to test.del of del select tabname from syscat.tables where tabschema ='VSC';
are NOT applicable in my case.
Being able to execute a SQLQuery that is in a separate file is mandatory. Also solution like adding the delimiter explicitly in my SQL command like select a || ';' || b from mytable is not appicable

0
Hi All

I could see that SQL Data Replication is working fine under the same instance (db2inst1) between a source and target DB .

But i have the requirement to replicate the data between two different instances(db2inst1 and db2inst2) which are running on the same DB server, having source DB in db2inst1
and target DB in db2inst2.

May I know the reason why data is not being replicated.

Please let me know if federation is needed for this requirement between db2inst1 and db2inst2 (which are already on the same DB server) , for the SQL replication to become successful.

Kindly provide your inputs.
0
Hi ALL

Good Afternoon!!!

I am facing below error in ASNCLP  while trying to create the Subscription Set.  Below are the commands used and error messages reported:

Repl > SET SERVER CONTROL TO DB DB1

====
CMD: SET SERVER CONTROL TO DB DB1;
====

Repl > SET SERVER TARGET TO DB  DB2

====
CMD: SET SERVER TARGET TO DB DB2;
====

Repl > CREATE SUBSCRIPTION SET SETNAME SET00 APPLYQUAL AQ00 ACTIVATE YES TIMING INTERVAL 1 START DATE "2018-10-10" TIME "14:35:00.000000"

====
CMD: CREATE SUBSCRIPTION SET SETNAME SET00 APPLYQUAL AQ00 ACTIVATE YES TIMING INTERVAL 1 START DATE "2018-10-10" TIME "14:35:00.000000";
====


ASN1956I  ASNCLP : The program now generates the script for action: "CREATE SUBSCRIPTION SET".

ASN1955I  ASNCLP : The program will use the following files: "replcap.sql" for the Capture SQL script, "replctl.sql" for the control SQL script, "repltrg.sql" for the target SQL script, and "replmsg.log" for the log file.

--- ASNCLP Version 10.05.07 Build date 2015-12-21 11:26:45

ASN1550E  The replication action "Create Subscription Set" ended in error. The value for the input parameter "source server information" is missing.

ASN1954E  ASNCLP : Command failed.


Do I need to have and source and target DB names(DB1 and DB2) should have same name to resolve this issue ?

Please kindly provide your inputs.

Thanks in advance!!!
0
Hi All

Which snapshot is recommended to analyze the performance issue of Procedure which is doing an update on a table.

Do i need to go for snapshot of application or snapshot of DB.

Please provide me your valuable inputs.

Thanks in advance!!!
0
Hi All

Greetings!!!

I have 2 instances ( db2inst1 and db2inst2) on the same machine. Each instance is having one DB each. I need to copy the data of a set of tables (for example 20 tables) from
instance1 (db2inst1) to instance2 (db2inst2).  This frequency of data copy should be daily once. Every time only Delta data need to be copied from source to target.

What are the possible ways of achieving this.
0
Hi All

Greetings.

I am facing the below Error message in the Application logs:

 Not able to get a Connection for db2Pool

Some few orders are failing with above message.

Below are my DBM configurations.
 Priority of agents                           (AGENTPRI) = SYSTEM                    
 Agent pool size                        (NUM_POOLAGENTS) = AUTOMATIC(100)            
 Initial number of agents in pool       (NUM_INITAGENTS) = 0                          
 Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)            
 Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

Can you please give inputs to resolve this Problem.
0
Hi All

Greetings.
Can any one help me to interpret the below Informational message which I found in my diag log file.

2018-08-25-06.03.16.668279-300 I10046620E503         LEVEL: Info
PID     : 1144                 TID : 14068592123456 PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000            DB   : SAMPLE
APPHDL  : 0-8                  APPID: *LOCAL.DB2.180531042846
HOSTNAME: hostname2
EDUID   : 80                   EDUNAME: db2redom (SAMPLE) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::HdrKickPooledAgents, probe:20
DATA #1 : <preformatted>
Waiting for last disassociation from the db

I am not able to interpret it.

Please help me to understand what is the meaning of above message
0
I need to get guidance on proper storage of an as400. I believe it is running system 38 (or 36).

Regards, George
0
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
Starting with Angular 5
LVL 12
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

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
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
OWASP Proactive Controls
LVL 12
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

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

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
>