999 questions
1
vote
1
answer
108
views
REGEX_EXTRACT() with multiple capturing groups not returning what's expected
The following code shows my problem, I need to extract the JOBD value (with in reality variable and matches \w{1, 10}
with A (N, STR) as (
values
(1, 'SBMJOB MYJOB'),
...
- 19
0
votes
1
answer
76
views
DB2 (i Series) how to write SELECT query on bit data using hex string value
I'm trying to troubleshoot some C# .NET code that interacts with a DB2 database on iSeries/AS400. These tables have several fields that are type CHAR() FOR BIT DATA which are populated as a hex byte ...
- 426
0
votes
1
answer
122
views
Add a field with sequence number based on RRN?
Question specifically for AS400, DB2
I have a file called RECIPES with no key. Example
Recipe Number/Ingredient number/Ingredient Qty/Sequence No
1000 111211 Sugar 0
...
- 89
1
vote
1
answer
97
views
IBM DB2 HTTP_POST for SOAP request, SOAPAction Header invalid
Just trying to get a response from the appropriate place, but I am getting an error related to the header "SOAPAction" (website changed);
values QSYS2.http_post(
'https://portal.website.asmx'...
- 13
1
vote
1
answer
178
views
Spring boot upgrade DB2 connection Warning
I am working on upgrading a Spring boot app from 1.5. to 3.4. The App is connected to IBM Iseries Db2 database, while upgrading running the app though Im able to connect to the database I am getting ...
- 11
1
vote
1
answer
49
views
How to recover original characters from data with broken characters?
We have a bunch of translations loaded in our Database which are showing broken characters where there should be accented latin characters like 'É'. I'm not sure where the encoding went wrong but we ...
- 629
1
vote
1
answer
50
views
I'm trying to update the sequence no of the file after deleteing a record from it in SQL DB2
WITH RowNumbers AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY G TLINE) AS row_num,
gtline, gttran
FROM
GPOTRSP
)
UPDATE b
SET b.gtline = a.row_num
FROM GPOTRSP b ...
- 11
0
votes
0
answers
286
views
Error connecting to DB2 using IBM.Data.DB2.iSeries
Trying to connect to DB2 using the Nuget pkg IBM.Data.DB2.iSeries 7.1.0 by Cristopher Gunn. The program throws an exception "The type initializer for 'IBM.Data.DB2.iSeries.iDB2Trace' threw an ...
- 50
2
votes
1
answer
380
views
List all privileges a user has for a specific table (DB2 AS400)
Using a db2 database hosted on an IBM AS400 Version:V5R4M0 I'm having trouble trying to list permissions a user has on a specific table.
Here IBM lists different ways to do so, but I get a file not ...
- 2,850
1
vote
1
answer
244
views
Connecting Jetbrains Datagrip to DB2 Version 7.5 encoding error
I am new to this so excuse and obvious issues. The only driver I can get a successful connection with is the JTOpen driver. I assume this is because our database version is so old. Anyhow, whats ...
- 25
0
votes
3
answers
183
views
System Power / AS400 - Embedded sql Cursor initial Fetch is slow
On my System Power / AS400,
i'm using embedded sql in an RPGLE Program to write a Subfile.
On a large selection the initial fetch is to slow.
When i scroll the speed is good.
Even though i only select ...
- 21
0
votes
0
answers
38
views
Get DB2 AS400 version number [duplicate]
I am doing a migration from a DB2 AS400 to SQL Server 2014 database
I want to use SSMA for DB2 client (DB2ToSQL) migration tool
I need to find out if the DB2 version supports SSMA for DB2 client (...
- 1
0
votes
1
answer
237
views
ODBC driver on Azure app service for ASP.NET Core 8 Web API
I have deployed an ASP.NET Core 8.0 Web API hosted on Azure web app service (Windows). It connects to IBM's DB2 i series database and gets the data. I am using ODBC connection, the drivers are setup ...
0
votes
1
answer
311
views
How to disable autocomit with ibm db2 for i in SqlAlchemy
How do i turn off autocommit with ibm_db_sa and sqlalchemy?
If the table has journaling on, this is not required, but this one table does not have journaling on, as such, autocommit must be OFF.
I ...
- 333
0
votes
3
answers
305
views
restore an ibm db2 from a savf file
I have a .savf file that someone created from an IBM DB2 S400 server to transmit a database to me.
I installed db2 server community edition on Ubuntu. My install works, as I was able to create a test ...
- 59
1
vote
2
answers
66
views
Error when extracting YYMMDD dates from table in AS/400
I have an AS/400 database to which I connect using the 5250 or DBeaver, when it was created more than 40 years ago, a field called BIRTHDT was created with the format YYMMDD meaning when I run the ...
- 647
0
votes
1
answer
58
views
Unable to return output variables for DB2 stored procedure using pyodbc
We have deployed the solution to return variable from a DB2 on iSeries stored procedure using the solution from question 48808714
This is the code we have deployed. The stored procedure being called ...
- 1
0
votes
1
answer
213
views
DB2/400 - How to use DBUJRN command when there are no entries returned from journal?
Longtime reader, first question ever!
I'm trying to write a stored procedure in DB2 that will periodically write journal entries to a file using a batch job, with the DBUJRN tool. The problem I'm ...
- 1
0
votes
0
answers
102
views
A cursor locks a file but doesn't release it (AS/400 COBOL + DB2)
I have a program that uses a table both in a SQL cursor and as a file (READing and WRITE-ing it).
Now, every time the program runs a OPEN CURSOR that uses the table, I see in WRKOBJLCK that another ...
- 27
-2
votes
1
answer
978
views
ODBC connections C#
Trying to do tests with ODBC connections, using microsofts documentation and nothing is working, I'm eventually going to have to get code up and running that interfaces with an AS400 using ODBC, and ...
0
votes
0
answers
52
views
DB2 and JSON, inconsistent quotation of integers
I'm using DB2 for i.
The following SQL uses the JSON_ARRAY function in order to produce a json array of three integers.
VALUES JSON_ARRAY(1,2,3)
Result:
00001
[1,2,3]
This seems just right. But when ...
- 7,713
0
votes
2
answers
75
views
DB2/400 SQL subqueries
I want to make sure that warehouses C and D are both in the same City.
Is there simpler way to do this?
Table:
City Warehouse
1 A
1 B
2 C
2 D
3 E
3 F
SQL:
WITH ...
- 89
0
votes
1
answer
74
views
MERGE INTO triggering duplicate key value specified
Table A has a simple structure:
ID
FOREIGN_ID
ORIGIN
There is a unique key over FOREIGN_ID and ORIGIN to prevent duplicates.
Table B has many fields, but of interest is
ORIGIN_ID
ORIGIN
Table B can ...
- 2,634
1
vote
1
answer
80
views
How to check active IDs from six months ago
How can I sum(ctc) in one line with a single statement that IDs from 1/1/2023 still exist on 6/1/2023? so I tried the below statement but returned 0!
SUM( CASE WHEN ID = LAG(ID) OVER (PARTITION BY GP ...
- 543
0
votes
1
answer
3k
views
Using SQL to view AS400 Journal data - Entry_Data in particular
I'm using SQL to view journal entries on my AS400. All is great except that the data in the field ENTRY_DATA is returned as
...
0
votes
1
answer
324
views
IBM.EntityFrameworkCore Error - [42815] [IBM][AS] SQL0451N
I am getting the error below when trying to run a stored procedure using IBM.EntityFrameworkCore against a Db2 database:
IBM.Data.Db2.DB2Exception (0x80004005): ERROR [42815] [IBM][AS] SQL0451N
The &...
- 1,690
2
votes
0
answers
123
views
DB2 encrypt() problem with PHP and parameterised query
I am trying to change some SQL code to use parameters instead of hard-coded data.
This code is causing difficulty:
Update mytable set passw=encrypt(?, 'mysecretkey') where ...
The passw field is ...
- 540
1
vote
1
answer
600
views
How to get a list of programs that uses a particular command in AS400
I have a library MYLIB which consists of user defined commands and I want to find the programs which are all using the CMDs in that Library.
I'm planning to write a Utility but before that i wanted to ...
0
votes
2
answers
115
views
How to calculate churn Rate (Retention) in db2?
Here are info I have: Group Name /YYYYMM / Contracts.
Now I need the last 12 months churn rate and SQL runs efficiently.
I wrote this , but not working properly:
SELECT GROUP_NAME,ID, YRMO,SUM(FLOAT(...
- 543
0
votes
1
answer
47
views
Repeat the Max count of LEAST(DENSE_RANK() OVER (PARTITION BY))
How can I rewrite the sql below to repeat the max number of count for each group Like the picture as Desired Result column?
LEAST(DENSE_RANK() OVER (PARTITION BY GP, DATEDIFF ORDER BY YRMO ASC), 12) ...
- 543
0
votes
1
answer
88
views
DB2 Distinct Count
How to Count(Distinct ID) where the Value is equal 1; possibly using
ROW_NUMBER() OVER(PARTITION BY ... ) ?
- 543
-1
votes
1
answer
260
views
wait for few seconds or minutes logic in db2 native sp
I want to implement logic to wait for a few seconds for the initial native DB2 SP to complete and then start the second native DB2 SP.
The wait logic is needed because SP1 will insert data, and then ...
- 1
0
votes
1
answer
171
views
Object difference DSPFD vs qsys2.systables
I have a strange observation, that's I'd like to share.
There's an object (it's a logical file) on my system that when displayed on 5250 shows following characteristic:
Maximum record length . . . . ....
- 45
-2
votes
1
answer
927
views
Calling RPG program from SQL with parameter
In DB2 SQL is present since 7.1 the function SQL QCMDEXC() to call external program.
I want to use it like this:
SELECT
MYFIELD,
CASE
WHEN QSYS2.QCMDEXC('CALL MYPROGRAM(**MYFIELD**)...
- 1,265
1
vote
1
answer
166
views
ACS/RSS - How current SQL environment can be overridden to instead use current *LIBL of the job nnnnnn/QUSER/QZDASOINIT?
IBM i CLI (QCMDEXC): I've a file in library DATALIB, I do CHGLIBL, RUNQRY on the file and I get file data smoothly. Because *LIB DATALIB is in the *LIBL, it works fine.
I try to follow same on ACS's ...
- 107
1
vote
1
answer
101
views
C# .Net OleDbCommand with SQL request using "substring" word not working with DB2/400 database
I'm working on Rest API that uses a DB2/400 database ( IBMi ).
I'm using this string to connect to database:
// Defaut connexion string
ConnectString = "Provider=IBMDA400;" + &...
0
votes
1
answer
265
views
RPGLE SQL query doesn't return results, but does when SQL is ran directly
Some Background info. We are using Infor XA as our ERP and I am trying to write an RPGLE program as a user action to save Purchase order information to an XML on the IFS. This will then get picked ...
1
vote
2
answers
75
views
How can I copy, modify, records and then insert them back into the same table with a unique ID?
The "fruit" table has a column for unique IDs for each row, along with other columns. I need to copy some of the rows from "fruit", and insert it back into "fruit" with ...
- 13
0
votes
1
answer
304
views
DB2/iSeries stored procedure error trapping SQL State
I have a stored procedure that is called from an unmonitored system and I need to trap all errors that occur.
Currently, I can trap any errors that occur when the data is well formed, but not when ...
0
votes
2
answers
86
views
How do I insert 2 spaces every 2 characters in a string?
i have a table with a 250 char column, with a content like:
AA1BB1CC1DD1
AA1DD1EE1
AA1XX1EE2FF1AB2CB2
it's a string used like an array of 2char + 1 char. I need to insert 2 spaces after the first 2 ...
- 17
0
votes
1
answer
130
views
Reset DENSE_RANK() OVER partition by where just one row is different
The set of data that I have has gap between dates and with Lag() function I calculated the number of months that gap exists. Then in second query with dense_rank() I count active Months for each ID. ...
- 543
0
votes
0
answers
98
views
Connect PHP in Linux to db2 in iSeries
I have a PHP application running in a Docker container on Linux RedHat for Power. This application connects to a DB2 for LUW instance.
To connect to DB2 from PHP I am currently using pdo_ibm driver ...
0
votes
1
answer
130
views
SEU - Is there a way to automate changing of these SEU defaults?
I need to often switch IBM i machines and need to set-it up for my use and this SEU change is one of them.
Is there any system command or API or SQL service that can be used to automate this process?
...
- 107
-1
votes
1
answer
143
views
CRTBNDRPG - Few clarifications regarding RPG Compiler Listing (SPLF)
Q1: On line 1, i know 5770WDS is Rational Development Studio for i and V7R3M0 is the OS version, what does 160422 and RN represents?
Q2: Why DBGVIEW(*STMT) says '(Source not available.)' and isn't ...
- 107
0
votes
2
answers
81
views
Build Timeline with overlapping records - SQL-DB2-IBM
I have a table in which I keep information related to working periods and tasks and I want to get the total amount of working time per user. But every user can work on multiple taks at the same time.
...
- 1
1
vote
1
answer
1k
views
SQL7008 - Why and how DB2 require users to journal a file even before it is created?
CREATE OR REPLACE TABLE QTEMP/TESTF0(
ID NUM(3) PRIMARY KEY,
STRID CHAR(3),
NUMSI SMALLINT,
NUMI INT,
...
- 107
0
votes
2
answers
415
views
AS400 IBM Toolbox for Java doesn't have classes to read Journal in the latest version of Jar JTOpen 400
JTOpen - The IBM Toolbox for Java: In the JT400 jar version 20.0.0 JTOpenlite (com.ibm.jtopenlite.command.program.journal) package is deprecated which has the classes to read the Journal classes like
...
- 1
1
vote
1
answer
737
views
Connection to DB2 using jaydebeapi not working
I need to create a connection to a DB2 database in an IBM i system, using Python library jaydebeapi, with the following instruction:
import sys
import jaydebeapi
import os
try:
conn = jaydebeapi....
- 11
0
votes
2
answers
216
views
Case statement where your first looking if a value exists, if not, execute rest of case statement
Im trying to create a statement where im first looking to see if a value exists in a row, particularily for a "Bill-To/type code/type 2 code" | SKU combination, if a value doesnt exist, ...
- 3
0
votes
2
answers
774
views
Retrieve IBM i column headings from SQL using pyodbc
I want to launch SQL querys from a PC to a DB2-database on IBM i.
The files were created with the old method (source file, 10-character's file and field name, and detailled column-heading)
In SQL from ...
- 1,129