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

DB2 10.5 on Windows Server 2019 Standard

I am trying to set up a DB2 cluster using Windows Clustering.  I have been using a few different resources from the web to set this up.  One is ..
https://www.ibm.com/developerworks/data/library/techarticle/dm-1212mscs/index.html
and another is ...
https://docs.starquest.com/Supportdocs/techSQDRPlus/PDF/IBM_DB2_Cluster_0301nomani.pdf

Both are excellent resources but I am be missing something.

Here is an overview of what I have.

Windows Cluster with 3 drives (data, log, and Quorum) named KGSLTWICDB01 with IP address by running the db2mscs -f c:\download\db2mscs.cfg. I have tested the failover and it seems to work fine.
2 DB2 Nodes (KGSLTWICDBN01 and KGSLTWICDBN02)
Under the cluster manager I see a db2Group role and both nodes show in the Nodes section.

My main question is how the heck do I set up the second node?  The documents seem to assume I know how to do that or that setting up node1 takes care of node2, but I don't see that.

When I run DB2iList on node1 I get
DB3          c : KGSLTWICDB01

When 1 run same on node2 I get
DB2

So what am I missing?  What do I need to do to get node2 set up the way it needs to be?

Thank you!

Jim
0
Introduction to R
LVL 13
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

DB2 10.5 on Windows Server 2012 R2.

Trying to figure out how security works with DB2 and Windows.  My main concern is the Server Admin group.  Here is what is confusing me.

By default, system administrative (SYSADM) authority is granted to any valid DB2® user account that belongs to the Administrators group on the computer where the account is defined. If the account is a local account, then it must belong to the local Administrators group. If the account is a domain account, then it must belong to the Administrators group at the domain controller or the local Administrators group. (https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.qb.server.doc/doc/c0008762.html)

What does this mean?  On my server when I look at the Local Users and Groups and look at the Administrators group, I see 4 or 5 domain groups that need to be admin on the server but I don't want them to by admins in DB2.

Is this saying that everyone in the Administrator group on the server is also an admin in DB2?

Thanks.

Jim
0
LEFT JOIN and the WHERE clause, how to work around?

We are running DB2.

I am working on a query that uses LEFT JOIN and a few parmeters in the form of WHERE clauses.  I am using a LEFT JOIN because there are records that will not have a match in TABLE B and we would like to retain them in the result set.  The issue occurs when the parameters are added in the form of a WHERE clause - essentially turning my LEFT JOIN into an INNER JOIN.  What is the best way to work around this limitation?


SELECT DISTINCT evecas, everef, evetyp, evesfc, eveplc, 
trim(evetrt) || '-' || (eveprg) as PGM,
evestf,
DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)) AS EVENTDATE,
(select stfnam from staffmstr where smmnum = evestf), 
lwsys.isodates(evefyr, evefmm, evefdd) AS DOSYYMD, 
evetim,
eveend,
TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(evetim)))) AS TOTAL_MINUTE_TIME
,
(select trim (cltfst) || ' ' || (cltlst) as CLIENT from cltmstpf where cltcas = evecas), 
evecnf, evesrv, evecpt, eveamt, evepun,
eveprc, status, lwsys.isodates(evepyr, evepmm, evepdd) AS PROCYYMD,
(case
when evesrv LIKE '%90832%' AND TIMESTAMPDIFF(4,CHAR(TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), LWSYS.TIME_4(eveend))-TIMESTAMP(DATE(DIGITS(evefyr)||'-'||DIGITS(evefmm)||'-'||DIGITS(evefdd)), 

Open in new window

0
Can we use "fetch first 1 rows only" criteria within NamedQuery? I used it but it does not filter resultset. It returns all records. But, when I use the same query in NativeNamedQuery, it works. It does filter the resultset and returns only 1 record from DB2. Can anyone confirm if this is expected behavior?
0
Hello,

How can I dynamically Import data from a table on DB2 database that rows are not in the target table using a SQL Server Linked Server ?

Thank you

Best regards
0
on linux centos 7 this: https://www.midvisioncloud.com/ibm-websphere-on-microsoft-azure/ibm-bpm-microsoft-azure/
i try to install DB2 Developer https://www.ibm.com/it-it/marketplace/ibm-db2-direct-and-developer-editions

I have configure GUI for server using this web resource: https://linuxconfig.org/how-to-install-gui-gnome-on-centos-7-linux-system

I install xrdp and I started server with GUI and xrdp server
I open firewall ports 3389 and I login in server with rdp access

when i try to install DB2 Developer setup i found:

[root@OpificioBPM server_dec]# ./db2setup
DBI1190I  db2setup is preparing the DB2 Setup wizard which will guide
      you through the program setup process. Please wait.


Exception in thread "main" java.awt.HeadlessException:
No X11 DISPLAY variable was set, but this program performed an operation which requires it.
      at java.awt.GraphicsEnvironment.checkHeadless(GraphicsEnvironment.java:217)
      at java.awt.Window.<init>(Window.java:547)
      at java.awt.Frame.<init>(Frame.java:431)
      at java.awt.Frame.<init>(Frame.java:396)
      at LoadingGraphic.<init>(Unknown Source)
      at LoadingGraphic.main(Unknown Source)

The DISPLAY variable is not set properly.  Ensure that the DISPLAY variable is set properly and that permissions are set properly to open windows on the display specified, then rerun the command.

I try to set DISPLAY variable with this values:
export DISPLAY=IP SERVER:0.0
export DISPLAY=localhost:0.0
export DISPLAY=localhost:0
0
Any free tool or site like draw.me to sketch database entity relationship diagrams like many-many, one-many type of relationships between tables

please advise
0
DB2 10.5 on Windows Server 2012 R2

I have two tables that have LOB columns.  One stores a photo and one stores a receipt in HTML format.  The two tables have 200+ million rows each and make up about 1/4 of the total database size.  These tables are written to about 90% and read from about 10% of the time.

I have read that in 10.5 you can compress LOB data but I am having issues figuring out exactly how to do it.

Documents say that I can evoke compression with ALTER DATABASE statement but does not say if that will actually compress the data or if it will only compress new data going forward.  

Also, has anyone actually done this with LOB data and if so  how sucessful was it?  Didi id cause any unforseen (or forseen) issues?

Thanks!

Jim
0
DB2 10.5 on Windows Server 2012 R2

I am trying to get DB2 to work with AD groups in windows and am having some issues.  I have a active directory group named "DB2 Users".  It has 3 people in it.  I want to grant access to DB2 based on that AD group so that if new people need access I can just add them to the group and not have to worry about it.  I do this all the time in MS SQL Server and from what I have read, I should be able to do it in DB2.

I can grant access to the 3 users if I create a ROLE (ReadOnlyUsers) and then assign them to that ROLE using their network ID and assign select rights to that ROLE.

I had thought  I could create a GROUP and assign the AD group to it and grant it privileges like I did with the ROLE but that does not work.  I can create a GROUP called TESTUSERS and assign it privileges but I can't seem to figure out how to assign the AD group to the DB2 GROUP.  

What am I doing wrong?

Thanks!!!

Jim
0
DB2 10.5 on Windows Server 2012 R2

I would like to create a windows failover cluster for my DB2 10.5 instance as described here...
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.admin.ha.doc/doc/c0007402.html
But the consulting company I am working with keeps telling me it will cost 5X to set up vs a manual HA setup.

Does anyone here have any experience using Windows Clustering with DB2 for an automatic failover cluster?
0
CompTIA Cloud+
LVL 13
CompTIA Cloud+

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

Hi,

   In DB2, we can declare a temporary table in a Stored Procedure SP1 and use it in another stored procedure SP2, while calling SP1.

Is there a way to do it in Postgresql 9.7?
Please help with an example, if there's a way to it?
Thanks a lot!
0
DB2 10.5 on Windows 2012 R2

I have an alert configured to let me know when a critical or severe error is written to the diag log.  Over the last few weeks I have gotten this severe error twice.

2018-12-05-09.45.32.311000-360 I379110F439          LEVEL: Severe
PID     : 2092                 TID : 2136           PROC : db2fmp64.exe
INSTANCE: DB2                  NODE : 000
HOSTNAME: HOST3
EDUID   : 2136
FUNCTION: DB2 UDB, Health Monitor, HealthIndicator::getAlertCfg, probe:30
MESSAGE : Internal Health monitor error
DATA #1 : Hexdump, 4 bytes
0x000000EF56F8ED68 : 0100 0000                                  ....

2018-12-05-09.45.32.314000-360 I379551F526          LEVEL: Severe
PID     : 2092                 TID : 2136           PROC : db2fmp64.exe
INSTANCE: DB2                  NODE : 000
HOSTNAME: HOST3
EDUID   : 2136
FUNCTION: DB2 UDB, Health Monitor, HealthIndicator::getAlertCfg, probe:40
MESSAGE : Internal Health monitor error
DATA #1 : Hexdump, 24 bytes
0x000000EF51D7F960 : 4442 3200 2000 2D00 2030 0020 002D 0020    DB2. .-. 0. .-. 
0x000000EF51D7F970 : 4345 4E54 4B43 4E20                        DatabaseTwo 

2018-12-05-09.45.32.316000-360 I380079F522          LEVEL: Severe
PID     : 2092                 TID : 2136           PROC : db2fmp64.exe
INSTANCE: DB2                  NODE : 000
HOSTNAME: HOST3
EDUID   : 2136
FUNCTION: DB2 UDB, Health Monitor, HealthIndicator::getAlertCfg, probe:50
MESSAGE : Internal Health monitor error
DATA #1 : Hexdump, 20 bytes

Open in new window

0
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
Fundamentals of JavaScript
LVL 13
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

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

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
>