12,932 questions
0
votes
0
answers
4
views
Connection error between MicroStrategy and IBM DB2 Cloud
I am facing a connection error when trying to connect to IBM DB2 Cloud via MicroStrategy.
(Database error <5>: Connect failed. Error type: Odbc error. Odbc operation attempted: SQLDriverConnect. ...
0
votes
1
answer
20
views
How to bypass the 500 characters limit on query resultsets on IBM DB2 Warehouse on Cloud?
I'm running an SQL query on IBM DB2 Warehouse on Cloud, but the results return only the first 500 characters of a string that is longer than that.
Is there a way to bypass this limit?
For example, I ...
- 16
0
votes
1
answer
49
views
Using Lag and Lead functions in SQL
I have this table on medical patients:
CREATE TABLE SAMPLE_DATA (
patient_name VARCHAR(50),
year INTEGER,
gender CHAR(1),
patient_weight DECIMAL(5,2),
location ...
- 139
1
vote
2
answers
64
views
Logically backfilling rows in SQL
I have this table in SQL:
CREATE TABLE myt
(
name VARCHAR(50) NOT NULL,
year INTEGER NOT NULL,
gender CHAR(1) NOT NULL CHECK (gender IN ('M', 'F')),
country_of_birth VARCHAR(50) NOT ...
- 139
0
votes
2
answers
89
views
Writing SQL functions in R
I am using R and SQL together. I have this table in a DB2 server - I made a simpler version locally in R:
library(sqldf)
mytable <- data.frame(
name = c("Alice", "Alice", &...
- 139
-1
votes
3
answers
83
views
select users with same SSN and MAX process date DB2
I need to find all the users who have a MAX Process date. There are often multiple records for the same person but different process dates.
PROCESS_DATE | SSN | Amount | CODE | SOURCE | ...
- 33
0
votes
0
answers
52
views
Converting between calendars in SQL
I am trying to convert calendar dates (jan-jan) into dates based on the (april-april) calendar.
Basically what I am trying to do is this:
Start with a date in this format full_date = 2020-12-12
...
- 43
0
votes
0
answers
24
views
.Net 8 application on linux container trying to have Db2 server connection throwing "GSKit Error: 201" could not be loaded
I'm trying to connect to DB2 server from .Net 8 application.
hosted application on In Linux Docker container.
When trying to test connection getting getting below exception.
"ERROR [42724] [IBM] ...
0
votes
1
answer
70
views
DB2 Logging exception in table inside a trigger
I want to make some detailed output when a trigger violation occurs. In the trigger below it should be some columns of obj that should be logged.
So what will the best way to do it?, inserting into ...
- 5
0
votes
1
answer
62
views
Python ibm_db trying to resolve: Exception: Describe Param Failed: [IBM][CLI Driver] CLI0150E Driver not capable
In trying to resolve the
"Exception: Describe Param Failed: [IBM][CLI Driver] CLI0150E Driver
not capable"
error that results from my statement:
"stmt, hp_id, user_id, first_string, ...
- 1
0
votes
2
answers
56
views
Is there a way to Call a DB2 store procedure via JCL or SPUFI
I am trying to execute my store procedure below
CREATE PROCEDURE DWR000A.SELECT_PSID_RIZ(
IN P_CONN INT
,IN P_PLNN INT
) ...
- 5
0
votes
1
answer
35
views
Getting some error with my test DB2 store procedure
I am new to store procedure and trying to create a simple store procedure to read from table. I am using IBM DB2 SQL.
--#SET MAXERRORS 0
--#SET TERMINATOR ~ ...
- 5
0
votes
0
answers
40
views
Python will not connect to Db2: SQLCODE=-1042
I followed the advise given in the thread where the Python error below comes up if trying to connect to DB2 after installing ibm_db with pip:
Exception: [IBM][CLI Driver] SQL1042C An unexpected ...
- 1
0
votes
2
answers
51
views
Why would a program abend with SQL -927?
We are upgrading our COBOL version from <4.2 to 4.2. I am facing an issue where one job fails because of SQL -927.
The job involves an IMS module and at the end, it calls a DB2 Module.
The last ...
0
votes
0
answers
56
views
Executing DB2 SQL stored procedure via JCL
I am trying to execute SQL db2 stored procedure using DSNTEP2 via JCL.
// SET DATE=241122
/* ...
- 5
0
votes
2
answers
77
views
Combing two subqueries and use either of the value depends on which one is not null
There tables are there VAC_NRD, VAC_DOC, VAC_TRV.
If this query return VAC_ID empty.
SELECT VAC_ID FROM VAC_NRD WHERE VAC_NRD =?
Will use this query to get VAC_ID
2. (SELECT VAC_ID FROM VAC_DOC ...
- 1,215
0
votes
0
answers
60
views
How to setup DB2 container in Linux VM using Podman (non-root) user
I've setup Podman using non-root user in CentOS 9 machine.
The user is added to docker group. I am able to run and setup containers in the env as well. But when trying to run and setup the db2server ...
0
votes
1
answer
47
views
BasicDataSource not having property for clientProgramName
In my application we are creating connection pool as shown below
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close" ...
- 313
0
votes
2
answers
58
views
Dynamic SQL for DB2
The below SQL query is not fetching columns into v_columns. When I print v_columns it is NULL always
SET v_sql = 'SELECT LISTAGG(COLNAME , '','') WITHIN GROUP (ORDER BY COLNO) INTO :v_columns ' || '...
0
votes
1
answer
35
views
Python RDS DB2 connection: TypeError: Class com.ibm.db2.jcc.DB2Driver is not found
I'm trying to connect to a DB2 instance running in RDS using Python.
I rely on jaydebeapi for that and rely on the doc.
Despite several tries, I still facing the issue in title.
My code is below. Any ...
0
votes
0
answers
104
views
DB2 SQL inserts are very slow when table has a primary key
I have a DB2 table (map_card_number) that takes 5 minutes to insert 1M rows of data when it has a primary key, compared to 11 seconds to insert the same 1M rows of data when the primary key is removed....
- 51
0
votes
0
answers
47
views
DB2 Usage of two combined columns in 'IN' operator
I want to write a SQL statement with in operator over two columns, but I keep getting this SQL error:
[Code: -811, SQL State: 21000] The result of a scalar fullselect, SELECT INTO statement, or ...
- 5
0
votes
0
answers
38
views
Stored procedure issue in DB2 Data Studio
I'm developing a stored procedure for DB2 Database but facing the below error in IBM data studio while creating a stored procedure
Error
Error if I include ; at the end :
DB2 for Linux, UNIX, and ...
1
vote
1
answer
69
views
DB2 ODBC driver not recognized?
I installed a DB2 instance inside a Docker container. Now I'm trying to connect to it from a simple Golang app, using Windows 11 x64, but i get this error:
Error connecting to database: failed to ...
0
votes
1
answer
37
views
JBoss 7.4 DB2 Datasource with valid credentials returns -551 SQL error when accessed by application
I have a JBOSS EAP 7.4 instance set up with 2 DB2 Datasources. I have confirmed the credentials are valid and working. I changed them to invalid credentials to ensure I was not getting a false ...
0
votes
0
answers
49
views
openshift db2 ssl remote database
I have a db2 instance in openshift. I can attach to this instance from my laptop with a ssl route.
I have the following issue when I want to create a remote database (from my laptop to openshift).
The ...
- 1
0
votes
0
answers
36
views
IBM db2 - alembic migration
I am trying to run
alembic upgrade head
on my IBM cloud DB2 lite database, but getting
KeyError: ibm_db_sa
My .venv already has installed the packages ibm_db, ibm_db_sa, and ibm_db_alembi, and the ...
2
votes
1
answer
77
views
DB2 AS400 Table partitioning - execution plan for specific partition
Due to constantly increasing number of data stored in our system running on DB2 AS400 7.3 database we decided to introduce table partitioning on some tables. While creation of partitioned tables works ...
- 143
0
votes
0
answers
52
views
Setting up a Solace connection with IBM CDC using JMS
I’m working to establish a connection between IBM Change Data Capture (CDC) and Solace PubSub+ Standard using the JMS API. I placed the necessary Solace JAR files (sol-jms-10.25.1 and sol-common-10.25....
1
vote
2
answers
52
views
JTOpen Bulk configuration
I wanted to ask something related to the Java AS400 connection driver, that is, JTOpen, and it is about whether it has a URL parameter similar to that of PostgreSQL:
jdbc:postgresql://localhost:5432/...
- 11
0
votes
1
answer
64
views
DB2 : can't read data
--FP01
-- Membuat roles
CREATE ROLE admin_role;
CREATE ROLE distributor_role;
CREATE ROLE sales_role;
CREATE ROLE inventory_manager_role;
-- Membuat user admin
CREATE USER useradmin WITH PASSWORD '...
1
vote
1
answer
58
views
Any tool or command to export meta-data from iBM DB2 Webquery to file eg. XML?
Would like to export ALL META-DATA from existing iBM-i DB2 Webquery to file eg. .xml type of file for a client as they want to have their webquery reports meta-data migrated to other solutions. They ...
- 13
0
votes
0
answers
162
views
SQL Syntax Error After Upgrading Spring Boot to 3.3.4 Using Pageable with DB2
I upgraded my Spring Boot project from version 2.7.18 to 3.3.4. After the upgrade, I started encountering SQL syntax errors when using pagination (Pageable) with DB2 9.5. The issue only occurs when I ...
- 13
0
votes
0
answers
41
views
Fetching data in batches: Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
I am using JDBC driver (db2jcc4.jar) to connect to IBM DB2 database using the Python jaydebeapi package and trying to fetch table records in batches. I can retrieve all the table records, but after ...
0
votes
1
answer
68
views
DB2 to Postgres Migration: CURRENT_TIMESTAMP
I'm trying to rewrite IBM DB2 Queries to PostgreSQL.
According to DB2 UDB TO POSTGRESQL CONVERSION GUIDE the DB2 function CURRENT_TIMESTAMP can be migrated to the Postgres function CURRENT_TIMESTAMP. ...
- 135
0
votes
1
answer
54
views
pyodbc w/ iSeries Access ODBC Driver: TABLE in DATABASE not valid for operation. (-7008) (SQLExecDirectW)')
i have a problem with pyodbc, commiting an update statement in python to a as400/db2 - this is my code:
class CDP_ODBC():
def __init__(self):
self.cnxn = pyodbc.connect(
'DRIVER=...
- 143
0
votes
1
answer
72
views
Finding out all common columns in a set of tables
I have this table in SQL that contains a list of all tables and columns in these tables:
CREATE TABLE master_table (
table_name VARCHAR(50),
column_name VARCHAR(50)
);
INSERT INTO ...
- 173
1
vote
1
answer
64
views
Python: Can not connect to cataloged db2 database (ibm_db)
System
OS: Fedora 40
Kernel: 6.10.6-200
Installed DB2 client: v11.5.9_linuxx64_client.tar.gz
python 3.12.5
ibm_db 3.2.3
The environment variables are set
$DB2INSTANCE db2inst1
$DB2_HOME /...
- 147
0
votes
1
answer
77
views
Trying to connect to Informix db with Python + Ibm-db
I am trying to access my Informix database with Python via the ibm_db module.
I am running with:
Python 3.12 64bits locally
Windows 11 Home
Here my code so far:
import os
# apparently I need to add ...
- 41
0
votes
3
answers
91
views
Regex modification in SQL
when I try out this SQL in a db2 environment, it gives me correctly the result I want, the digits of the string
select
CAST
(
REGEXP_REPLACE(Word, '[^0-9]', '') AS INTEGER
) AS NUMBER_PART
from
(
...
- 150
0
votes
0
answers
56
views
Dynamic SQL select statement (DB2 LUW v11.5) has a very high log_disk_wait_time
I'm running DB2 v11.5.9.0 CSB 42449 on zLinux SLES15 SP5.
I'm tuning a dynamic SQL select statement using activity & package cache event monitor.
Looking at the performance data of these event ...
- 3
-1
votes
1
answer
29
views
Getting 'Element not defined' Error for ExtGraphNodes in OPL model
I'm working on an optimization problem using IBM's OPL (Optimization Programming Language). I have defined a set called ExtGraphNodes in my model file, but when I try to use it in my data file, I ...
0
votes
0
answers
71
views
Problem displaying DB2 data on a CICS Map
According to SPUFI DB2 has a 1047 code page and my terminal is configured for the same code page when connecting to Z/OS through TN3270.
I have developed a Cobol program to display on a SYSOUT the ...
- 1
0
votes
0
answers
32
views
how to connect IBM db2 database using loopback request node in App connect enterprise
By using loopback request node I am trying to connect with db2 but while deploying the application on server I am getting error as
The LoopBackRequest node received an error from LoopBack when ...
0
votes
0
answers
35
views
Db2 ESQL or DB2 SQL control statement how to get output
Background:
In DB2 SQL control statements, also called SQL Procedural Language (SQL PL), in v 12.X
they call them ESQL. Just started trying to program in this with DB2.
What I want to do:
BEGIN ...
0
votes
1
answer
78
views
SQL: Finding out if an event happened before or after another event
I have two tables like this:
CREATE TABLE XX
(
name VARCHAR(50),
date DATE,
a INT,
b INT,
c INT
);
INSERT INTO XX (name, date, a, b, c)
VALUES
('john', '2010-11-01', 1, 0, 0),
(...
- 233
0
votes
2
answers
39
views
how to search a text within a text from a field in DB2
I have the following data in a table as below,
Column1
Column2
ABC
pat.getString("IMAGE") =="Y"):pat.setValue("CONDITIONCODE","");
XYZ
pat.getString("...
- 397
0
votes
0
answers
80
views
Tomcat startup issue (error contains db2 and timer details)
Good morning, we have been running the server for a few weeks now without much issues but randomly while starting tomcat it throws an error, having a hard time to solve and currently stuck not being ...
- 101
0
votes
1
answer
49
views
Max number of characters in schema definiton for inline-transfer of CLOBs
I've written a TPT job script that transfers data from a DB2 table to Teradata via ODBC.
There are CLOB fields in the source table, which I also want to transfer. When I don't define a length for the ...
- 209
0
votes
1
answer
59
views
List of tables with count of records present in those tables from sysibm in DB2
Is there a way to determine the total number of records of all the persistent tables from sysibm. I tried the below query however CARD does not match the total count of that particular table.
SELECT ...
- 397