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

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
Become a CompTIA Certified Healthcare IT Tech
LVL 13
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

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
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
Hi Experts,

After creating several tables with SQL (because of the naming I have to use), I have in my Data Library files with names as CUST_00001 and PROD_00001 (as expected),
The field names in the files from DB2 site, are numbered CDTA_00001, CDTA_00002 etc.  (also as expected),

I prefer to have the SQL field names in my SQLRPGLE program, so that I can create a readable (maintainable) program, is there a way to manage that?

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

Hi.  I am a TSQL user trying to make sense of DB2 SQL on the IBMi.  I need to populate a field with a percentage of a cost in another field, within the same table.  That would be simple enough but I need to filter on field values in 2 separate tables.  I think my problem is understanding the joins in DB2..  I put this together but I'm leery of running this code on my IBMi, even over test data.  
UPDATE wklibj.itmrvbx831
SET colt = smat * .25 WHERE exists(
    SELECT a.itnbr,a.smat,a.co1t,a.so1t,b.vndnr,c.vndnr,c.ittyp,c.b2cocd
        FROM wklibj.itmrvbx831 a                                    
        JOIN wklibj.itemblx831 b ON a.itnbr = b.itnbr               
        JOIN wklibj.itmrvax831 c ON a.itnbr = c.itnbr               
        WHERE b.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN'                                                               
        or  c.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN')    

Open in new window


This looks like it should run but examples I'm finding online lead me to believe that I need to repeat the filters after my enclosed select statement.  I also looked at using a merge statement but not sure it's a good fit here..?  thanks.
0
We have a DB2 10.5 system (on windows) that we are exporting to a SQL Server 2016 data warehouse on a daily basis using SSIS 2013.  The issue I am running into is that some of the tables in DB2 have BLOB columns (picture data) and when I use SSIS to import that to SQL Server it is very slow.  The main DB2 table has about 25,000 new records per day and it takes about 3 hours to move that from DB2 to SQL Server.

The main bottle neck is that SSIS has to write the BLOB to disk, then read it and store it in SQL Server.  I am averaging about 130 rows per second with both systems on solid state drives.  
 
My SSIS data flow is set up like so...

OLE DB Data Souce connected to DB2 10.5
Data Converison task to change DB2 BLOB datatype to Unicode Text Stream (DT_NTEXT)
Derived Column to fix DB2 date to SQL date issue
OLE DB Data Dest to SQL Server 2016

I had the SSIS BLOB storage on C:\temp at first and that was even slower (about 75 rows per second) and I read a blog about why not to do that.  So I moved the BLOB to a different drive and that about doubled the speed but at 130 rows per second, it takes about 3 hours to process 25,000.

Is there any way to speed this up?  Is there any way to use a memory location to read and write the BLOBs too?

Any suggestions would be much appreciated!

Thanks!!!
0
Hi All

My DB instance is down and is not getting started. I have checked the diag logs and found this below error.

I am seeing the message(SQL1063N  DB2START processing was successful) when i use db2start ,but immediately it is getting crashed and I am not able to see any output
for ps -ef|grep sysc.

ADM0503C  An unexpected internal processing error has occurred. All
          DB2 processes associated with this instance have been shutdown.
          Diagnostic information has been recorded. Contact IBM Support for
          further assistance.

Any good inputs please to start the DB instance.
0
Hi All

I am facing the below error when I am trying to connect to my DB2 DB .

SQL30082N  Security processing failed with reason "1" ("PASSWORD EXPIRED").
SQLSTATE=08001

This User which  I am using is a new user created by root to-day.  

I am suspecting that this error could be due to OS configuration problem not with in DB2.

Please provide inuts
0
On SQL Server 2012, I have two different databases(DB1 and DB2). I have created views on DB2 that select on tables from DB1. I have created a user on both databases that I want to be able to select on the views in DB2 but not select on the underlying tables on DB1. When I select on the view as the new user, I get a error saying I can't get access to the underlying table. The new user has connect access to DB1 and select on the views on DB2 (has select under Securables). Nothing else is set (under owned Schemas or Membership). Both databases have the same owner (SID same on both). I have tried doing the alter database DB1 set DB_Chaining on and granting the user select and/or connect but I still get the error.
0
DB10.5 LUW on Windows 2012

I have enabled a DDL audit using events on my production database.  The main purpose was to be able to see what and who might be making changes.  Looking at the activity, I see lots and logs of these commands.

ALTER BUFFERPOOL TABLESPACE1 IMMEDIATE SIZE 16162 AUTOMATIC /* db2stmm */  

Looking back 30 days I am seeing this ran for different tablespaces from 300 - 700 times per day.

The server is running Windows 2012 R2 with 32 GB of memory.

From what I have read the BUFFERPOOL is the cache memory used by a given TABLESPACE, is this correct?  Should it be doing this so often?  If the answer is no, what do you recommend?

Thank you!

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