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

Does anyone know the syntax that would be used to create a multimember view using SQL on the AS/400?  I have a table that has 4 members and in order to do a type of specific type of update, I need to see all the members in one view.

Any help on this would be appreciated.

Thanks.

-Anthony
0
Become a Certified Penetration Testing Engineer
LVL 13
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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
I have been using Crystal Reports to report data from DB2 tables on an iseries for a long time, both using the database wizard and writing my own SQL commands. What I would now like to do is to call a utility program on the Power 9 and retrieve the output and incorporate it into a Select statement in CR to then display in a report. I will need to pass a number of values including both strings and numbers. My first thought was that maybe there is a way crystal can use a stored procedure syntax to call an rpgle program, but I have no idea how this will work, and I have not been able to find an example or two. Can you point me in the right direction? One of my goals is to utilize programs for calculating shipping & tax charges that already exist on the power 9 box by passing in customer numbers and shipping terms and methods, and order info.
0
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
I want to create a Materialized View in IBM-DB2, which is called Materialized Query Table. I have a table "xyz" and it has say "n" columns and one of them is "id". I am given list of ids as input. I need to extract records for those input list of ids from "xyz" table using the materialized view every 15 min.

Can you please help me how to solve this. Thank you.
0
DB2 10.5 on Windows Server 2012 R2

This question came up in a conversation and I want to make sure my answer is correct.  If you have this query...

SELECT 
     ORDER_ID, 
     ORDER_ZIP, 
     ORDER_ADDRESS, 
     ORDER_LAST_NAME, 
     ORDER_NAME,
     ORDER_HASH,
     CREATED_TS
FROM
     ORDERS 
WHERE 
     CREATED_TS >= CURRENT TIMESTAMP - 6 MONTHS
     AND ORDER_HASH IS NULL 
ORDER BY 
     CREATED_TS DESC
FETCH FIRST 1000 ROWS only

Open in new window


Will DB2 get all the orders for last 6 months, sort them by CREATED_TS, and then return the first 1000 rows or will it grab the first 1000 rows  for last 6 months, order them by CREATED_TS and return them?

I would think it has to grab all the orders for last 6 months, sort them and then return the data, otherwise it is not a true sort by CREATED_TS.  

Is that correct?

Thank you!

Jim
0
Hello, I am attempting to use the QSQGNDDL api on the IBM i. I am running V7R2 and have attached the source I have written. Whenever I execute it I receive an SQL7042. I have double checked my parameters against posted code on the net and don't see any issues in that area. I tried downloading some posted code and running that and get the same result. Are there any caveats that are unpublished? I know this issue has risen in the past and I have reviewed those articles. Again, it looks like my code is correct. Any help would be appreciated!

ScottPrint Screen of a debug session showing the SQL7042 errorrtvsqlsrc.txt
0
Table A

   custid   orderid
     101      5418
     102      7408
     102      5408
     103      9547
 

 Table B

Orderid
5418
9547
5408

I have 2 tables. From table A I only need to select those custid where all the orderid's are present in
table B.

in this case, I only need to select custid 101 and 103 as one of the orderid (7408) is not present in tablel B.

can anyone please give me that SQL ? I am using IBM DB2 Database.

Many Thanks
0
Table A

ID  Parentkey   childkey
1    101                55
2    55                 102
3    102              104
4    104              106
5    201                    214      

Table B

Parentchildkey
101
55
104
201
214

Hi I have Table A and Table B.
From Table B I need to select only those parentchildkeys from table B where ALL its family members in Table A are also in Table B.

For example, if you take value 101 in table B, it has that value in Parentkey column in Table A and it also has a child 55 and since 55 is also a parent which got another child 102 and since 102 does not exist in Table B, nothing from this FAMILY in Table A should be selected in my query. But if you take value 201 from table b,you
can see that this exists in Table A a parent but its child 214 also exists in Table B. so my query can have 201 and 214 .

can anyone please give me that SQL query ?
0
Greetings, experts! After 25 years of using DB2 on midrange systems (AS/400, IBM i, etc), I’m now being tasked with being a database administrator for DB2 on the mainframe (z/OS). I apologize for the “rookie questions” here, but I’m still getting my head around the mainframe.

Currently, every morning, I manually check the status of several tablespaces using the DISPLAY command in the “DB2 Commands” screen:

e.g.
Cmd 1       ===>       -DISPLAY DB(DN*) SPACENAM(*) USE LOCKS LIMIT(*)
Cmd 2       ===>       -DISPLAY DB(DP*) SPACENAM(*) USE LOCKS LIMIT(*)

This displays several pages of output on the screen, pausing after each screen-full. If any tablespaces are in an invalid status, I can do something about it.

Ultimately, I’d like to issue several of these DISPLAY statements, directing the output to an output dataset. Then, I can either inspect that dataset manually or possibly write a program to parse the output dataset and report (or fix) any anomalies.

This is probably super easy for all you mainframe DB2 DBA’s, but how do I script these commands? Do I use REXX? If so, how?

Thanks for your help!
DaveSlash
0
CompTIA Security+
LVL 13
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

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

We had a severe error on our DB2 server and I am trying to figure out why.  The error was unstable FMP process, terminating process.  That in turn caused a lot of stored procedures to fail.  What exactly does the FMP process do?  Could removing a backup drive from the server cause this?  Looking at the dump files I see this.
“BUGCHECK_STR:  APPLICATION_FAULT_INVALID_STACK_ACCESS_INVALID_POINTER_WRITE_IN_CALL”

Open in new window


 Here is the exact error.

2019-01-03-13.20.47.449000-360 E499990F3819         LEVEL: Severe
PID     : 1728                 TID : 4200           PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000           DB   : DEPSVCS
APPHDL  : 0-46786              
AUTHID  : DB2SERVICE           HOSTNAME: DB03
EDUID   : 4200                 EDUNAME: db2agent (DEPSVCS) 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerReturnFmpToPool, probe:900
DATA #1 : String, 50 bytes
Marking fmp as unstable, fmp is forced or aborted:
DATA #2 : Hex integer, 1 bytes
0x02
DATA #3 : String, 20 bytes
Fmp entry use count:
DATA #4 : unsigned integer, 4 bytes
1
DATA #5 : String, 8 bytes
Fmp TID:
DATA #6 : Hexdump, 4 bytes
0x0000000D2462E690 : A417 0000                                  ....
DATA #7 : String, 8 bytes
Fmp row:
DATA #8 : sqlerFmpRow, PD_SQLER_TYPE_FMP_ROW, 536 bytes
fmpPid: 5884

Open in new window


Thanks!  

Jim
0
DB2 10.5 on Windows 2012 R2 Server

Can anyone tell me what this error might be?  I have looked and not really found anything except for maybe "memory exhaustion" but not sure what that might mean.

2018-12-19-13.25.53.412000-360 I416642F411          LEVEL: Severe
PID     : 1728                 TID : 8728           PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000
APPHDL  : 0-9513
HOSTNAME: DB03
EDUID   : 8728                 EDUNAME: db2agent () 0
FUNCTION: DB2 UDB, Connection Manager, sqleUCagentConnect, probe:7
MESSAGE : ZRC=0x8034006D=-2144075667=SQLEU_CABLT "CA BUILT"

Open in new window

Does not seem to be DB specific, so I am guessing server specific?

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
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
Update table in database from another database.

I have two databases, DB1(SQL Database) and DB1 (access database). I opened an empty access database and linked to DB1 and DB2, now I want to update table in DB2 using data from DB1. Can someone please help me with the query?

DB1                                                      DB2
TABLE1                                                TABLE1                        TABLE2
ID_field                                                id2_field                      id2_FIELD
property1                                           property1                    ID_field
property2                                           property2


Here the challenge is the receiving table (DB2 TABLE1) does not have the same Id_key (ID_filed)  so I have to use TABLE2.
Any help would be appreciated.
Thank you.
0
DB2 10.5 on Windows Server 2012 R2

I am trying to add an identity column to a small table (about 700 rows).  I see that DB2 will not allow you to directly add it so you have to do something like this...

ALTER TABLE tablename ADD COLUMN id INTEGER NOT NULL WITH DEFAULT 0
ALTER TABLE tablename ALTER COLUMN id GENERATED BY DEFAULT AS IDENTITY
REORG TABLE tablename
UPDATE tablename SET id = DEFAULT

Open in new window


My issue is then I run the UPDATE SET ID = DEFAULT it runs and runs and runs and runs.  Looking at LIST APPLICATIONS SHOW DETAIL it shows me the process is compiling.

DBUSER    toad.exe  Compiling  11/16/2018 14:12:41.661005   D:\DB2\NODE0000\SQL00002\MEMBER0000\

Open in new window


So my questions are:  

1. Is this the best way to add an identity column to an existing table?  Or is there a better way?
2. Why is it taking so long (as of writing this the UPDATE statement has been running for over 30 minutes).
3. What does it mean when it says COMPILING?  Is that correct?

Any help would be greatly appreciated.  Thank you!!!

Jim
0
DB2 10.5 on Windows

Our production instance of DB2 10.5 on windows crashed Friday afternoon.  It would not accept connections and it would not accept DB2STOP command.  I finally had to reboot the server and it all came back up OK but now I am trying to figure out what happened and am having a hell of a time.  I have all the trap files and bin files and diag log files but there is so much data there and none of it seems to tell me exactly what happened.

Is there a program of process that will examine the files and give me an idea of what happened?  Of what files should I concentrate on to find the root cause?

Any help would be greatly appreciated.  Thank you!

Jim
0
OWASP: Forgery and Phishing
LVL 13
OWASP: Forgery and Phishing

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

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

Greetings!!!

I am working on SQL replication set up.

I am facing the below error message when I am starting ASNAPPLY Program on my linux machine. I am using DB2 LUW 10.5 .


  The NLS msg is ASN1023E  APPLY : "AQ00" : "WorkerThread" : The Apply program cannot open the work file "" because of a system er
  ror with ERRNO "”PATH”/AQ00.000". The error code is "2".


Please kindly provide your inputs to resolve this error message.
0
DB2 10.5 on Windows

My department actually has some training budget for the upcoming year and I have been asked to submit some thoughts on what would be beneficial for me to attend.  I was looking at IDUG but it looks like it is geared towards z/OS and not windows (I did not see a single subject with windows mentioned in the description for this years IDUG).

Does anyone know of any good conferences or seminars that would be good for a SQL Server DBA who is now working with DB2 10.5 on Windows?  Maybe something near a beach?

Thank you!

Jim
0
DB2 10.5 on Windows

Is there any way to see exactly what gets written to a DB2 online incremental or delta backup file?  Not the actual data but what tables and/or table spaces are involved?  My full online database backup is 330 GB and my incremental and delta backups are in the 170 GB range.  That is half the database.  I think this is due to the number of BLOB fields in the database but I would like to be sure.

Thanks!

Jim
0
DB2 10.5 on Windows Server

If I have something like this...

START TRANSACTION
     CALL WEB SERVICE; (can take up to 8 seconds)
     UPDATE TABLE1;
     UPDATE TABLE2;
     DELETE FROM TABLE3;
COMMIT TRANSACTION;


How would DB2 handle the tables during the life of the transaction?  Would it only lock a table when the change is made or would if lock the tables at the start of the transaction?  I am not familiar with DB2 transactions and while the IBM info pages describe how they work, none of it answers this question.

I am making the assumption that they are similar to transactions in SQL Server although I do see some references to SAVEPOINTS which I am not sure I understand.  I think that might not apply to DB2 on windows.

Thank you so much for any help in advance.

Jim
0
DB2 10.5 LUW on Windows Server 2016

I have a few tables in production that have photos (as BLOBs) in the rows.  I want to archive them to an archive server and I have tried SSIS but it is very slow due to the fact that SSIS has to write the JPEG to disk and then read it in again.

So someone suggested that I could create an export file with DB2, move the file to the archive server, and then use the load utility to import it.

I have been able to get the export to work, but not the import.

Here is what I have for the export.

db2 "EXPORT TO 'C:\EXPORT\TEST.TXT' OF DELL LOBS TO 'C:\EXPORT\LOBS' MODIFIED BY LOBSINFILE SELECT * FROM Photos FETCH FIRST 1000 ROWS ONLY"

The FETCH FIRST 1000 ROWS ONLY is for testing since there are millions or records.

When I try to import it I use this.

db2 "IMPORT FROM 'c:\export\test.txt' OF DEL LOBS FROM 'c:\export\lobs\test.txt.001.lob' MODIFIED BY lobsinfile INSERT INTO PHOTO_TEST"

It runs but each row gives me the message...

SQL3229W  The field value in column 3 is invalid.  The row was rejected. Reason code: 1

The columns are
1. ID
2. PHOTO TYPE
3. BLOB of PHOTO
4. LASTTIME

Here is what the test.txt file looks like.

10000002,"JPEG","test.txt.001.lob.0.24442/","2013-10-13-15.40.55.705000"
10000004,"JPEG","test.txt.001.lob.24442.25957/","2013-10-13-15.40.58.112000"
10000006,"JPEG","test.txt.001.lob.50399.26155/","2013-10-13-15.41.00.533000"
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
>