12,969 questions
0
votes
0
answers
40
views
DB2 CLP command via subprocess.run sometimes returns empty output and sometimes SQL1024N: A database connection does not exist
I'm running a DB2 stored procedure from Python using subprocess.run.
The command works perfectly when I run it directly in my shell:
db2 'CALL GET_DBSIZE_INFO(?, ?, ?, 0);' | awk -F': *' '/Parameter ...
- 19
2
votes
1
answer
126
views
SQL: Calculating percentage of time spent in a country
I have this table in SQL:
CREATE TABLE myt (
name VARCHAR(20),
country VARCHAR(20),
date_arrived DATE,
date_left DATE
);
INSERT INTO myt (name, country, date_arrived, date_left) ...
- 6,845
-1
votes
1
answer
84
views
For multiple records in first column with distinct value in second column
I have a SQL statement which works fine for a single value of columnD, but when I need data for multiple columnD values, it's not returning any data.
This is the working SQL statement:
Select max(...
Advice
0
votes
3
replies
59
views
How to use Read Stability in db2 interactive SQL
I'm experimenting with DB2 isolation levels. I've installed DB2 12.1.1 for Linux on Ubuntu 22.04 and created a user & instance. Now I'd like to open two sessions and step-by-step type SQL commands,...
- 24.9k
-3
votes
2
answers
185
views
Using SQL to keep track of changes occurring in a table?
I start with this table in SQL on day=0 (e.g. 2025-10-29):
CREATE TABLE TABLE_0 (
NAME VARCHAR(20),
VAR1 INT,
VAR2 INT
);
INSERT INTO TABLE_0 (NAME, VAR1, VAR2) VALUES
('RED', 1, 2),
('...
- 6,845
2
votes
2
answers
153
views
Is there a SQL equivalent of the R setdiff() function?
I have two tables:
CREATE TABLE t1 (
NAME1 VARCHAR(20),
date DATE
);
INSERT INTO t1 (NAME1, date) VALUES
('RED', '2020-01-05'),
('BLUE', '2020-03-15'),
('GREEN', '2020-06-20'),
('YELLOW', '...
- 6,845
1
vote
1
answer
198
views
IIDR CDC Kafka Custom KCOP disable schema auto register
we are trying to replicate Db2 changes from z/OS to Confluent Kafka.
Everything works out fine for the production of simple JSON records in Kafka.
As our policies for Kafka Topics and Schemas are ...
- 76
0
votes
0
answers
59
views
How do I / example of adding source info in IBM MQ Header (possible MQRFH2/usr) from DB2 stored procedure
We have a stored procedure in DB2 written in an external sproc (possibly some mainframe language, possibly C++) that creates an XML structure and puts it on a queue.
I know we can add a correlation id,...
- 3,021
3
votes
1
answer
135
views
Why does Pandas not recognise my sqlalchemy connection engine?
I'm trying to connect to an IBM DB2 database from Python. I'm using Python 3.12.10, SQLAlchemy 1.4.54, and Pandas 2.3.2. This is what my code looks like:
import os
import sqlalchemy
import pandas as ...
- 521
-1
votes
3
answers
136
views
Count 3 different set of dates from two columns
I have a query with a result like this example:
Column_1
Column_2
Column_3
Text
9/1/2025
9/15/2025
Text
9/1/2025
9/15/2025
Text
9/1/2025
9/30/2025
Text
9/1/2025
9/30/2025
I want to create a nested ...
- 31
2
votes
4
answers
133
views
Multiply a Row by a Multiplier of a Column
I have a query that output a dataset that looks like this in DB2:
ID
Amount
Multiplier
001
1000
0
002
2000
0
003
3000
3
004
4000
2
I'd like to add a nested query with this condition: When Multiplier ...
- 31
0
votes
0
answers
78
views
How to downgrade the IBM driver to match the license
my previous license version is 1.6.0, and connected to the IBM db2. However I accidentally upgrade the driver version, now is 4.34.3 which is the latest version, I can't get the newest license, is ...
- 29
1
vote
1
answer
95
views
SQL JSON_OBJECT in RPGLE Character conversion is not defined
I try to make a JSON_OBJECT in my SQLRPGLE Program. But even the simplest doesnt work:
**FREE
ctl-opt dftactgrp(*no) actgrp(*caller);
dcl-s PAYLOAD varchar(1000);
EXEC SQL
SELECT CAST(
...
- 129
0
votes
1
answer
59
views
DB2/LUW - How to get group totals and sub-totals without a null label for the grand total?
I've read the answers for group totals with sub-totals. But none of them seem to address the issue of having a NULL label for the grand total. I tried both ROLLUP() and GROUPING SETS but they both ...
- 153
0
votes
3
answers
74
views
How to detect if a Display File (DSPF) needs recompilation after a Physical File (PF) change in IBM i?
I'm developing a tool program on IBM i that automates recompilation of objects (RPGLE, DSPF, CLLE) whenever changes occur. Everything works fine for RPG and CLLE, but I'm struggling with detecting ...
- 34
0
votes
2
answers
70
views
remove unwanted characters in PF on AS400
I want to remove (not replace) unwanted characters in AS400 (IBMi) tables coming from external (un-manageable) text documents uploaded into AS400.
Using RPGLE, RPGLE/SQL or CL if possible
On AS400 PF ...
- 89
0
votes
1
answer
199
views
Reading an IBM PIC S9(09) COMP data from file in python
I was given sample data by the mainframe engineer, for which the COPYBOOK looks like below:
01 CCS001-DETAIL-RECORD.
05 CCS001-REC-TYPE PIC X(01). Should be '1'
05 CCS001-...
- 323
2
votes
0
answers
125
views
Convert an integer to IBM PIC S9(09) COMP. type in python
I have the following three values in a CSV file:
1683199814
2087175640
1348771152
I need to write them into a flat file using a python program, which will be loaded into Mainframe DB2 using a COBOL ...
- 323
-1
votes
4
answers
163
views
Find out if a name has more than one distinct value in the same time period
I have this table in R:
CREATE TABLE myt
(
name VARCHAR(50),
start_date DATE,
end_date DATE,
var VARCHAR(10)
);
INSERT INTO myt (name, start_date, end_date, var) VALUES
('RED', '2020-...
- 6,845
-2
votes
1
answer
130
views
Summing between dates [closed]
I have these two tables in SQL:
CREATE TABLE myt1
(
name VARCHAR(50),
date DATE,
hours_watched DECIMAL(4,2)
);
INSERT INTO myt1 VALUES
('name1', '2024-01-15', 2.5),
('name1', '2024-03-05'...
- 6,845
0
votes
0
answers
35
views
Eclipse persistence and DB2 - JPA query needs `With UR`
I'm using eclipse persistence and have a method that generates a select query to get a list.
I need to have this query use the equivalent of with UR to prevent locking issues.
I have tried all kinds ...
- 11.5k
0
votes
2
answers
84
views
Tracking when colors appear for the first time under different conditions
I have this table in SQL (each color only appears once in each year):
CREATE TABLE myt (
color VARCHAR(20),
year INTEGER,
var INTEGER
);
INSERT INTO myt (color, year, var) VALUES
('red', ...
- 6,845
2
votes
5
answers
237
views
Count number of new colors in each year
I have this table in SQL:
CREATE TABLE myt (
color TEXT,
year INTEGER
);
INSERT INTO myt (color, year) VALUES
('red', 2000),
('blue', 2000),
('green', 2000),
('cyan', 2000),
('...
- 6,845
2
votes
0
answers
114
views
IBM DB2 stored procedure transaction has a strange behavior
I have this code into an IBM DB2 stored procedure, when I send the @action = 'I', then the INSERT statement is executed successfully, but when I send @action = 'D', the DELETE statement remains in ...
- 39
-1
votes
1
answer
88
views
Comparing transition counts to total counts in SQL?
I have a dataset in SQL like this:
CREATE TABLE hospital_visits (
name VARCHAR(50),
hospital_visit_date DATE
);
INSERT INTO hospital_visits (name, hospital_visit_date) VALUES
('patient1', '...
- 1
1
vote
1
answer
80
views
Database concurent UPDATE and SELECT with Read Uncommitted
I have this TABLE_A:
id
columnA
columnB
1
oldValueA
oldValueB
When two queries are performed at the exact same time and concurrency issues arise:
Query #1:
UPDATE TABLE_A
SET columnA = 'newValueA', ...
- 63
4
votes
4
answers
124
views
Find list of GROUPIDs that have ZERO AccountIDs on another table
Looking for SQL that returns a list of GROUPIDs (from the ACCOUNT_GROUP) table that have no ACCOUNTS that are found on the ACCOUNT_LIST table.
ACCOUNT_GROUP (table)
GROUPID
ACCOUNT
GROUP1
111111
...
- 41
0
votes
0
answers
50
views
db2 dead lock on delete with FK constraint
I have 2 tables, Movement and Movement_encoded_names, Movement_encoded_names has a FK movementId pointing to Movement table.
Deadlock happens when I call JPARepository.deleteByMovementNumber(String ...
- 767
2
votes
1
answer
149
views
Creating a SQL table function for RPGLE program
I have an existing RPGLE program on IBM I which I call from ACS "Run SQL Scripts" like this:
CALL F60PTF.COINPRQ(1, 1, 'CHAI11', '258', 1, 'LB', '', 'MAIN');
WRKOBJ OBJ(COINPRQ) returns the ...
2
votes
1
answer
59
views
SAS SQL DB2 fuzzy search on multi-word list
I have +200 terms I want to throw at a table to see if they appear in in a column. The column is a string with many values so I’m using the contains function. The code below works as intended if I ...
- 25
-2
votes
1
answer
85
views
SQL question with AND clause check multiple rows with multiple AND conditions
Can someone please advise about this SQL where clause execution with AND for checking multiple rows .
Table has two rows and I wanted to select both rows if it's meeting the specified AND condition....
- 7
0
votes
1
answer
96
views
Insert Into DB2 table with Select from SQL Server
I am backloading data from a SQL Server table into a DB2 table. I am getting the same error for all of my null date columns:
Operand type clash: int is incompatible with date
It seems to be caused ...
- 13
0
votes
0
answers
73
views
DB2 SQL Error: SQLCODE=-132, SQLSTATE=42824 after migration to spring boot 3.4.5
prior to spring boot migration (3.3.2)
```upper(concat('%', concat(:myVar, '%')))```
used to be translated into
```upper('%'||?||'%')```,
but after migration to spring 3.4.5 the same code snippet ...
- 1,711
0
votes
1
answer
105
views
SQLSTATE 42601 error on DB2 Community Edition attempting to connect by prior
We are trying to migrate from an old DB2 database to a newer DB2 Community addition and we get an error:
"ERROR: syntax error at or near "WITH";
Error while executing the query, Error 1,...
- 53
0
votes
0
answers
44
views
DB2: Best performing data type for very long number IDs?
Our system is a DB2 z/OS DB.
We have IDs which consist of long, system-wide unique, fixed 26 characters long sequences, all numbers ('29241...'). We have no choice on the incoming data-format (we can'...
- 53
0
votes
0
answers
87
views
IBM Data Studio disconnects after some time and then has to have connection deleted and created again
I'm using IBM Data Studio, and it works when connected.
The problem is that when I go back to it the next day, the connection is disconnected, and if I connect in the Administration Explorer side ...
- 37
0
votes
0
answers
31
views
IBM XA DataSource throwing resultset is closed error
Recently we have started migration from Spring , Hibernate 5.x to 6.x. We were getting below error on XA data source but non XA data source was working fine. (Jboss 8 application server)
[jcc][t4][...
- 313
-1
votes
1
answer
349
views
How to add timeout to DB2 connection string?
I'm using the following connection string format in my client to connect to a DB2 instance:
HOSTNAME=<Hostname>;DATABASE=<Database>;PORT=<Port>;UID=<Username>;PWD=<Password&...
- 758
-1
votes
2
answers
96
views
Calculating multiple percents in SQL at once? [closed]
I have this table in SQL that contains info on multiple hospital visits for each patient:
CREATE TABLE patient_visits
(
patient_id INT,
hospital_visit_date DATE,
visit_num INT,
...
- 6,845
1
vote
1
answer
234
views
How to resolve 'sqlcli1.h' file not found when using go_ibm_db on macOS?
I'm trying to use the go_ibm_db Golang driver to connect to an IBM DB2 Warehouse from my macOS machine. However, when I try to build my Go application, I get the following error:
# github.com/ibmdb/...
- 758
0
votes
1
answer
56
views
XMLEXISTS query is not giving the desired output in DB2
I am trying to query one XML column in DB2 but not getting the desired output.
I want to fetch all the records where Name='Inder'
Below is the structure of my database -
Table Name - CUSTOMER
Column ...
0
votes
0
answers
51
views
Support for DB2 database in codeigniter 4
Is there a built in support for DB2 database just like MySQL in CodeIgniter 4.
If not can we see support for DB2 database in CodeIgniter 4 in future releases?
user10704605
0
votes
1
answer
53
views
Update the parent table DB2 change primary key and error on foreign key
Request to write and explain the operation of a trigger that will first update the parent table TAB1 (update in the code) and then TAB2. I get this error:
ERROR: SQL ERROR [23504]: THE PARENT KEY IN ...
- 5
0
votes
1
answer
55
views
Dropping all Views dynamically in Db2
I want to include a drop everything section in a script which (re)generates a sample database.
I know I can find a list of views in the current schema and generate the DROP VIEW statements this way:
...
- 17k
0
votes
1
answer
82
views
Creating a DELETE trigger in Db2
I’m trying to create an DELETE trigger to archive old data. I have a sample fiddle at https://dbfiddle.uk/H_7B93WK .
I have two source tables: parents and children with a foreign key from children to ...
- 17k
1
vote
1
answer
126
views
Can a temporary table in Db2 have a primary key?
I have created temporary tables in other DBMSs (Postgresql, Oracle, MariaDB, SQLite, MSSQL with modification) like this:
CREATE TEMPORARY TABLE test(
id INT NOT NULL PRIMARY KEY,
data VARCHAR(...
- 17k
0
votes
0
answers
72
views
Query taking too long to fetch results
When I try running the stored procedure with only this query:
SELECT TAB1.COL6
FROM TAB1
INNER JOIN TAB2 ON TAB1.COL1 = TAB2.COL1
WHERE TAB1.COL2 = 10;
It takes 5 seconds to get the resultset and if ...
3
votes
2
answers
96
views
Extracting a variable part of a string
I have learned that subtracting two dates in Db2 results in a number which is to be interpreted as an interval:
SELECT current_date - '1943-02-25' FROM SYSIBM.DUAL;
-- 820020: 82 years, 0 months, ...
- 17k
0
votes
2
answers
140
views
Identifying valid dates in SQL
I have a table in SQL with a column called some_date. This table has dates in the following format: YYYY-MM-DD (e.g. 2020-01-30). I tried to show a general example below:
CREATE TABLE my_table (...
- 6,845
1
vote
1
answer
56
views
Error with TRIMBLANKS argument when loading CSV data into DB2 using ibm_db in Python
I am using Python with the ibm_db library (version 3.2.6) to load data from a CSV file into a DB2 table. The following query is used for the data insertion process:
INSERT INTO DWHD1.USERS
SELECT * ...
- 111