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

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
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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
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
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
Learn SQL Server Core 2016
LVL 12
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

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
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
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
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
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
JavaScript Best Practices
LVL 12
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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

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
>