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
5K
Solutions
3K
Contributors
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.
Related Topics
Share tech news, updates, or what's on your mind.
Sign up to Post
Load Recent Content
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
Any help on this would be appreciated.
Thanks.
-Anthony
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
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
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.
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
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
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.
Can you please help me how to solve this. Thank you.
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...
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
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
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
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!
Scott
rtvsqlsrc.txt
Scott
rtvsqlsrc.txt
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
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
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 ?
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 ?
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
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
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
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
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
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
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.
Here is the exact error.
Thanks!
Jim
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”
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
Thanks!
Jim
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.
Jim
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"
Jim
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?
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?
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.
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
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.
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.
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...
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.
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
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
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\
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
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
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
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 …
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 …
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.
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.
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
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
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
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
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
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
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.0
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.
10000004,"JPEG","test.txt.
10000006,"JPEG","test.txt.
…
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.0
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.
10000004,"JPEG","test.txt.
10000006,"JPEG","test.txt.
…
DB2
5K
Solutions
3K
Contributors
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.
Related Topics
Top Experts In
DB2
-
1
Gary Patterson3,084 pointsLVL 37 -
2
Tomas Helgi Johannsson2,050 pointsLVL 26 -
3
dfke2,050 pointsLVL 16 -
4
Brian Murphy2,050 pointsLVL 15 -
5
Kent Olsen279 pointsLVL 47
- Services
- Individual | Training
- SMB | Training
- Enterprise
- Pricing
- Experts
- Certified Professionals
- Apply Now
- Company
- About
- Topics
- Careers
- Contact Us
© 1996-2019 Experts Exchange, LLC. All rights reserved. Covered by US Patent.