Questions tagged [db2]
Db2 is a family of relational database management servers developed by IBM. It runs on multiple platforms, including Unix (namely AIX), Linux, IBM i (formerly OS/400), z/OS and Windows.
808
questions
0
votes
1
answer
41
views
What are "Cumulative Special Build" in fixpack download web page?
On our computers Db2 v11.5.8.0 Linux/x86-64 is installed. I would like to install new fixes as regular update, specially to deploy security fixes.
Couple of weeks ago new fixpack 11.5.9.0 was released....
- 327
1
vote
1
answer
44
views
DB2 Parallel Insert
I have something like this to insert via application into DB2 LUW,
insert into MY_TABLE (CREATION_DATE, MESSAGE, IDENTIFIER) values
(CURRENT_TIMESTAMP, '0', '1b0d7cfb-66ee-4372-913b-2283c6e3f0b5')
,(...
- 11
0
votes
0
answers
16
views
IBM Db2 - How to monitor progres of ALTER TABLE command?
On IBM Db2 v11.5.8.0 Linux/x86_64 I have to add several columns in pretty big table with ALTER TABLE command.
On test database now it takes for more then an hour, but I don't have any info how long ...
- 327
1
vote
2
answers
101
views
Db2 v11.5.9.0 installation: error while loading shared libraries: libaws-cpp-sdk-transfer.so
On Red Hat 8.8 I have Db2 v11.5.8.0 installed and database is working fine.
Today I downloaded v11.5.9.0 and unpacked the tar file.
I executed db2prereqcheck and all prerequisites are met. Successful....
- 327
0
votes
0
answers
32
views
Error importing a shapefile into DB2 spatial table using db2se import_shape
For a database (DB2 spatial is already enabled for it), I created a relation with the following attributes:
CREATE TABLE state.village (
zipcode varchar(5),
shape db2gse.st_multipolygon
);
...
- 65
1
vote
1
answer
158
views
Isolation level at the database level for IBM DB2 LUW
How do I determine the default isolation level for our 11.5 DB2 instance running on Linux?
I see there is a "special register" from this IBM documentation, however I'm not a DB2 expert so I'...
- 69.7k
0
votes
1
answer
33
views
Why cannot I create a generated column with a CASE expression? [duplicate]
I'm trying to add a generated column to a table in V7R3 of IBM DB2:
ALTER TABLE MYLIB.MYTABLE
ADD COLUMN NEW_COL VARCHAR(255) GENERATED ALWAYS AS (
COALESCE(
CASE
...
- 279
0
votes
1
answer
59
views
db2 prune command procudes "DIA8701C Access denied for resource" error
On Db2 v11.5.8.0 on Linux/x86-64 couple of mouths ago we have moved our database from one Linux to another.
Every night at the time of backup error is written in db2diag.log file.
2023-08-16-02.14.13....
- 327
0
votes
1
answer
26
views
LOAD QUERY from SQL
Is there any way to run LOAD QUERY from a pure SQL viewer (dbeaver, squirrel, etc)?
I have only seen this is possible from CLP.
https://www.ibm.com/docs/en/db2/11.5?topic=commands-load-query
- 575
-1
votes
1
answer
38
views
Query for grouping by comparing sum of value for group, stopping when quantity is reached
Trying to calculate values for a FIFO report. Is there a way using SQL to group data such that you're comparing the sum of a particular value against different values for each group, including taking ...
- 279
0
votes
1
answer
57
views
Query for grouping by comparing sum of value for group
Is there a way using SQL to group data such that you're comparing the sum of a particular value against different values for each group?
For example, consider the following schema:
Table: ITEM
Columns:...
- 279
0
votes
0
answers
229
views
Another way to Call DB2 stored procedure on data integration platform with outputs parameters
I want to call a DB2 stored procedure on one of the data integration platform, so-called as Lobster_data.
Here, it is one of the example of executing stored procedure.
create or replace procedure ...
- 101
0
votes
1
answer
50
views
db2look fails to properly order views dumps
I exported an entire DB2-Luw DB's DDLs with db2look in order to prepare for a Postgres migration, however db2look seems to write the DDLs of the view in an incorrect order, so that some vies might ...
- 1
0
votes
1
answer
146
views
PostgreSQL client on Windows to access PostgreSQL server on Linux using Active Directoy Kerberos authentication
TEST 1 - PostgreSQL with Active Directory authentication
I have installed PotgreSQL Server v15.3 on Red Hat 8.8 and integrate PostgreSQL authentication using Active Directory.
In pg_hba.conf I have ...
- 327
0
votes
1
answer
105
views
How do I fix db2prereqcheck hanging and not responding?
When I run
./db2prereqcheck -i -v 11.5.8.0
there is no answer from the server. It hangs and is not responding.
What can be the problem?
The machine runs on AIX 7.2.
There is the same effect (not ...
- 21
0
votes
0
answers
115
views
Db2 pureScale CF is periodically restarted with the "CF process group file read but process group IDs are invalid" message
Db2 11.1.4.6, AIX, 2 members + 2 CFs.
Db2 pureScale CF is periodically restarted with the following message in the CF's db2diag.log:
LEVEL: Warning
...
- 758
2
votes
0
answers
80
views
A suspicion about self dead latch
Db2 11.1.4.6, AIX
/* IBM_DSSNAP */
select
hld_application_handle
, hld_member
, hld_application_name
, hld_userid
, lock_name
, lock_object_type
, tabschema
, ...
- 758
0
votes
0
answers
54
views
How can you check if DB/2 trigger fired due to table insert/update that is part of multi-table transaction?
I have scenarios where Table A and Table B can be updated as part of a single transaction or Table B can be updated on its own.
I have a trigger on Table A for when there is an update on Table A and ...
- 101
1
vote
0
answers
280
views
Unable to connect to local DB2 instance
I have installed an Enterprise version of DB2 on my local Windows 11 pro laptop for some development tasks.
Installation proceeded without errors but, it seems I cannot connect using TCP. If I try to ...
- 143
0
votes
0
answers
81
views
Online db2 REORG TABLE with the NOTRUNCATE TABLE option requests Z-lock on a table
Db2 11.5.7, Windows.
The situation is the same on other versions and platforms like 11.1.4.6 & AIX.
Steps to reproduce:
CREATE TABLE DB2ADMIN.TEST_REORG_ONLINE (I INT NOT NULL, C CHAR (100) ...
- 758
1
vote
0
answers
59
views
IBM DMC tableindex tableindex_performance table are too big
I'm using IBM Data Management Console v. 3.1.9.0 and I monitor 29 DBs. Lately the IBMCONSOLE tables are getting too big (last month they've grown over 300% each).
table: TABLEINDEX_PERFORMANCE is >...
- 11
0
votes
1
answer
106
views
Exclude Tables from HADR in DB2
In the production environment, we have set a HADR solution for the whole database.
Now, we would like to exclude some tables from this solution (HADR) and not sync with the secondary database.
my ...
- 28
1
vote
2
answers
118
views
A huge number of the same statements in the package cache
Db2 11.1.4.6, pureScale.
Some database parameters & registry variables which could affect.
SELECT name, value
FROM SYSIBMADM.DBCFG
WHERE NAME IN ('stmt_conc', 'number_compat', 'varchar2_compat', '...
- 758
0
votes
1
answer
174
views
What is meaning of "FCM Automatic/Dynamic Resource Adjustment"?
Few times per day on Db2 v11.5.8.0 Linux/x86_64 on several databases the following warning appears in db2diag.log file:
2023-03-14-08.00.39.289489+060 I4031117E469 LEVEL: Warning
PID : ...
- 327
0
votes
3
answers
141
views
How to detect pending settings on db2set, dbm and db cfg level?
On Db2 v11.5.8.0 on Linux x86_64 I have several instances and databases.
I would like to find out if there is some pending setting on:
db2set variables
dbm cfg
dg cfg
level?
Questions:
Is there a ...
- 327
0
votes
1
answer
498
views
How to check at what level/version is Db2 database?
On Linux x86_64 I installed Db2 v11.5.8.0 and I am in the process of upgrading instances and databases from v11.1 to v11.5.
We have several Linux machines and they have at least two Db2 versions ...
- 327
0
votes
2
answers
76
views
Only zeros are returned in APPL_IDLE_TIME value from SNAPAPPL admin view
On Db2 v11.5.8.0 on Linux I would like to get how many seconds have elapsed since application issued last request in current connection.
Check monitor switches
db2 get monitor switches | grep ...
- 327
0
votes
0
answers
65
views
How to get SQLs per network consumption?
On Db2/Linux_x86 version 11.5.8.0 we have several Db2 instances.
With iptraf-ng command sysadmin has noticed that one of this Db2 instance has 20-times more output network traffic then others which is ...
- 327
1
vote
1
answer
121
views
How can I roll back a fix pack upgrade on Windows?
I have installed fix pack 11.5.8 for DB2 version 11.5 in Windows server 2016. Now this fix pack is not compatible with application. I have to downgrade to 11.5.7. How to roll back the fix pack (11.5.8)...
- 161
0
votes
0
answers
81
views
Is Multi-tenancy already supported in Db2 v11.5?
I have found document https://www.wdug.com/resources/Documents/LUW%20Presentations/June%202021/Db2%20Tenant%20May%202021-Paul%20Bird.pdf
and on page 25 there are instructions how to establish multi-...
- 327
-1
votes
1
answer
97
views
DB2 Cardinality
In my tables, I have many statistics with -1 and I want to know how can I enforce DB2 to calculate these values.
Select FIRSTKEYCARD, FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD
...
- 1
0
votes
1
answer
233
views
How to restrict the number of connections to database from particular IP address on Db2 database?
On Db2 v11.5.8.0 Linux x86_64 database every few months some application opens up a lot of connections and it free them up pretty quickly like in 10 mins.
I assume it has to be fat client application ...
- 327
0
votes
2
answers
1k
views
SQL1273N - Missing log file "S0000000.LOG" in database partition "0"
I'm getting this error when trying to connect to a DB2 Database. This error started after the OS restarted.
db2 => connect to cm
SQL1273N An operation that reads the logs in database "CM" ...
2
votes
2
answers
71
views
DB2 query not working
Customer (parent table)
Id
Charge Amt
C1
10
C2
20
C3
30
C4
40
Payment (child table)
Id
pymt amt
C1
10
C2
5
C4
40
Select Id and pending balance from customer and payment tables
Pending ...
- 21
0
votes
0
answers
37
views
DB2 Perfomance issues
I have two DB2 environments. One of it has a performance issue, but it has almost same configuration parameters. But the slower environment has not so much system buffer pools. Can it be the reason ...
- 1
2
votes
0
answers
572
views
Fixing data pivot/grouping with db2
I have a pretty straightforward sql query that I'm running on DB2 I-series 4 which is just performing some simple joins. The issue is that my materials table has it so that multiple material/color ...
- 121
0
votes
1
answer
454
views
Catalog cache too small - any method to determinate size?
On Db2/Linux_x86_64 in db2diag.log I constantly see warning about CATALOGCACHE_SZ parameter to be too small like:
2022-12-02-14.40.53.536711+060 E5979903E769 LEVEL: Warning
PID : 4508 ...
- 327
2
votes
2
answers
250
views
DROP ALIAS unable to drop existing alias?
We're unable to drop aliases.
So, I found an alias by running:
SELECT
TABLE_NAME
FROM
QSYS2.SYSTABLES
WHERE
BASE_TABLE_NAME IN ('OE0711P','OE0712P')
AND TABLE_TYPE = 'A'
AND ...
- 279
2
votes
1
answer
357
views
Difference between CHAR(n) FOR BIT DATA and BINARY(n)?
I think I have seen somewhere that there is some sort of semantic difference between CHAR(n) FOR BIT DATA and BINARY(n), but I'm unable to locate where it was. Any pointers?
I'm asking because I have ...
- 23.1k
-1
votes
1
answer
35
views
generate a report according to the value in table A columns [closed]
suppose I have to generate a report with few columns like
report:
ID
name
value
12
zee
Y
11
sew
N
table A
ID
name
oldvalue
newvalue
11
sew
Y
N
12
zee
N
Y
13
dee
N
N
14
xee
Y
Y
15
pew
N
N
I ...
-1
votes
1
answer
72
views
How to find new problems since latest Db2 fixpack is released?
A weak ago new Db2 release v11.5.8.0 was released.
There is a list of problems that were solved in this release.
But as always every new release has some new bugs. Is there some list of new discovered ...
- 327
0
votes
1
answer
501
views
Create table with EBCDIC encoding in db2 i series
I am working with AS400 system. I need to create tables with different encoding for validation task. I tested with
CREATE TABLE TESTSCH.EBCDIC_TBL (
COLUMN1 VARCHAR(100),
COLUMN2 INTEGER
) CCSID ...
- 3
0
votes
1
answer
65
views
Db2: EXP0059W in the access plan for queries using statistical view with IN or EXISTS predicate
The problem is listed as resolved in 9.7.1, but the same one exists in all recent 11.5 and 11.1 versions.
IC62904: ISSUE EXP0059W FOR STATVIEWS WITH SELECT DISTINCT.
Still the same EXP0059W in the ...
- 758
1
vote
1
answer
819
views
How to display non-English character correctly in db2cmd?
On Db2 v11.5.7 on Linux/x86_64 I have a UTF-8 database.
Executing db2 get db cfg for test1 returns:
Database code page = 1208
Database code set = UTF-8
On my Windows 10 computer in ...
- 327
-1
votes
1
answer
116
views
Does IntelliSense feature available in Data Studio?
Does IBM Data Studio have IntelliSense features or Intelligent code completion something
like below?
I would like to write an SQL query with IntelliSense type functionality.
I have IBM Data Studio 4.1....
- 101
0
votes
2
answers
100
views
Holding transactions as long as a user edits
As an application developer I'm used to using databases transactions only as a way to play in modifications after a user has clicked "save".
That's the way most database servers I'm familiar ...
- 755
0
votes
1
answer
520
views
How to limit CPU by Db2 instance?
On Linux/x86_64 I have several Db2 database instances and most of them have one database per instance.
Today I have had one "run away instance" that consumed most of the CPU resources of ...
- 327
0
votes
1
answer
408
views
How to check if Db2 database is encrypted at rest?
In Db2 v11.5.7 on Linux x86_64 I have configured keystore, updated dbm cfg and created database with "encrypt" command:
db2 "create database dbenc encrypt"
Database created ...
- 327
0
votes
1
answer
602
views
Db2 Security Bulletins points to v11.5.8 fixpack that don't yet exists, is it?
I have found three security bulletins IT40893, IT40781, IT41338 that all as solution recommends: "Upgrade to DB2 Version 11.5.8 or higher", but I can't find Db2 fixpack v11.5.8 on official ...
- 327
0
votes
1
answer
222
views
Db2 - load LOB data returns error SQL3025N A parameter specifying a filename or path is not valid
I would like to learn how to load LOB data from files. I have a table with two columns:
ID INTEGER
DATA CLOB
I can normaly export data with:
export to c:\data\data.txt of del lobs to c:\data\ ...
- 327