Questions tagged [db2-midrange]
This refers to features of DB2 that are specific to the flavor that runs on the IBMi, formerly known as the iSeries, and AS/400 before that.
30
questions
2
votes
2
answers
78
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 ...
- 275
0
votes
1
answer
26
views
why does the order of fields return 0 rows, but move the field does
I have two queries that return the same data, only the calculating field with the case is moved. I am using the Access Client Solutions "run query" to execute the query.
If I run this one
...
- 1
0
votes
1
answer
40
views
How to run merge statement based on logic of joined table?
When I try to run a query like the following:
MERGE INTO
MYTABLE_A
AS MERGE_TARGET
USING
(
SELECT
MYTABLE_A.A, MYTABLE_B.B, MYTABLE_A.C, MYTABLE_D, MYTABLE_E
FROM
MYTABLE_A
...
- 275
0
votes
1
answer
21
views
iSeries journaling performance
I am responsible for a legacy application deployed on an iSeries V7R3. The application is fairly high volume, 2 million or so transactions per month.
The database for this app spans multiple libraries ...
- 103
0
votes
2
answers
799
views
Add days to date (char column with decimal) in DB2
I have a table named INVOICE_ET which has column of below definition
INVDATE CHARACTER 10
result of select INVDATE from INVOICE_ET is as below
INVDATE
2021-07-31
In another table INVOICE_DY, I have ...
- 11
-1
votes
2
answers
379
views
Column or globale Variable not found [closed]
Error msg:
DataSource.Error: ODBC: ERROR [42S22] [IBM][System i Access
ODBC-stuurprogramma][DB2 for i5/OS]SQL0206 - Kolom of globale
variabele AADETX niet gevonden. Details:
DataSourceKind=Odbc
...
0
votes
1
answer
262
views
What's the difference between REGEXP_SUBSTR and REGEXP_EXTRACT?
What's the difference between REGEXP_SUBSTR and REGEXP_EXTRACT?
As far as I can tell, they seem to do the same thing. Is there some difference in side-effects, or how they perform for certain inputs? ...
- 275
0
votes
2
answers
505
views
How to add generated column to db2 table?
I've got a table with the following:
Columns:
---
URL [nullable, e.g. "https://dba.stackexchange.com/"]
APPFK [nullable, e.g. 654654]
LINKTYPE ["Page", "Link", or "...
- 275
0
votes
1
answer
630
views
db2cli license file not being used
I'm using the db2 driver for CLI and ODBC to configure an ODBC connection to DB2 on IBM i/OS from CentOS. I'm trying the following command:
db2cli execsql -connstring "protocol=TCPIP;database=...
- 155
1
vote
1
answer
63
views
Find what values are not in multiple columns [duplicate]
I need to find what "Codes" from column "RMS_CODE" and column "RMS_ALT1_Cde" are not being used in the Child # (starting in position 6 for a length of 4) column within the "APC_Spec" number. I tried ...
0
votes
3
answers
191
views
Convert two integers into timestamp
How do I convert two integers, one for date and one for time, into a timestamp?
Given 20200304 and 1046, return a timestamp for April 3, 2020, 10:46 AM.
Given 20200304 and 946, return a timestamp for ...
- 275
0
votes
1
answer
370
views
Is there a way to get the contnents of an RPG program using SQL?
So, I've got myself a query to list our RPG programs:
SELECT OBJNAME
FROM
(SELECT OBJNAME AS SCHEMA FROM TABLE (QSYS2.OBJECT_STATISTICS('*ALLSIMPLE', 'LIB')) S) AS SCHEMAS
,LATERAL (SELECT * ...
- 275
0
votes
1
answer
626
views
Linked server to DB2 table locks
I have a problem with a SQL linked server connected to a DB2 database on an AS400 IBM iSeries.
I configured an ODBC connection from the ODBC Data Source utility. Once configured the ODBC, i created ...
0
votes
1
answer
700
views
Unable to alter table - reason code is 10
I'm trying to run this:
ALTER TABLE AMMLIBT.SAGRPPF ADD UNIQUE (ID) ADD CHECK ( IS_BUDGETABLE IN ( 0 , 1 )) ;
Which gives me this:
SQL State: 55019
Vendor Code: -7008
Message: [SQL7008] SAGRPPF in ...
- 275
5
votes
1
answer
375
views
Does it make sense to put a unique constraint on a nullable soft-delete flag?
So we currently have the following:
MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: DELETED (INTEGER)
CHECK:...
- 275
0
votes
1
answer
27
views
How to ensure all records of a header are under the same fiscal year?
Say I have the following schema:
BUDGET_YEAR_TARGET
INTEGER ID
BUDGET_DATE_TARGET
INTEGER ID
INTEGER BUDGET_YEAR_TARGET_ID--Foreign key
DATE DATE
How can I ensure that any records ...
- 275
1
vote
2
answers
2k
views
How to journal tables in SQL?
Say I have a C# script that creates 50 tables in MYLIB.
Currently, after running the script, I have to go into System i Navigator and manually attach the MYLIB/USRJRN to each of the tables' ...
- 275
0
votes
2
answers
467
views
Pending changes from a finished job
I'm getting the following error:
Uncommitted changes pending for file MYTABLE in library MYLIB.
CPF325E
Cause . . . . . : Uncommitted changes are pending for file MYTABLE in library MYLIB for ...
- 275
0
votes
1
answer
275
views
Create View giving SQL7029
If relevant, I'm using IBM i V7R1M0.
I have some code in C#:
var generateSqlSql = $@"
CALL QSYS2.GENERATE_SQL(
'{obj.Name}',
'{obj.Library}',
'VIEW',
CREATE_OR_REPLACE_OPTION => '...
- 275
0
votes
2
answers
222
views
How to set column text when creating a view in SQL?
By default, when creating a view, for the Text of each column, if the column is a direct selection from a table/physical file then it will use the Text of that table/physical file's column. If it's ...
- 275
0
votes
1
answer
546
views
Dropping a table/column automatically drops views using it - how to warn instead?
If I have a view that uses a column, and I remove that column/table, the view is dropped automatically.
Is there a setting I can change such that it instead prevents the removal of the column/table, ...
- 275
1
vote
1
answer
108
views
db2 cross platform function/variable?
Is there a db2 function/procedure/variable that can be used to determine what platform a data source origins from? sysibm.sysdummy1 exists, so I assume that this is Db2, but some of the SQL does not ...
- 22.6k
2
votes
2
answers
6k
views
How can I lock row for reading/updating in DB2?
I am trying to resolve a race condition in my application.
I have a table that is used as a work queue and many threads and read/update it. The following query is executed from application. First it ...
- 141
0
votes
1
answer
1k
views
SQL Server - Query Over Linked Server to IBM DB2 throws an error
We have a partner that uses a IBM DB2 iAccess database, which we connect to using a linked server on our SQL Server.
It's been working great for a long time, but over the weekend they upgraded their ...
- 193
1
vote
1
answer
1k
views
Cannot drop primary key or add primary key
This db2 query:
alter table mylib.mytable add primary key(field1, field2, field3)
returns
[SQL0624] Table MYTABLE in MYLIB already has a primary key'.
However
alter table mylib.mytable drop ...
- 65
1
vote
0
answers
264
views
Database Behavior while executing UPDATE and INSERTs on the same table
My gear:
Pentaho BI Server 8.0
AS400 V7
My scenario:
I have two processes that sometimes run at same time in the same table. This table has about 600M rows, so, an update can take long time to ...
- 111
1
vote
1
answer
178
views
EXECUTE stmt USING expression
I am writing an INSTEAD OF trigger on a view. I need to split a string into 4 40 character long parts to insert into separate columns. I want to use substr() to break the variable up into these parts, ...
- 113
1
vote
2
answers
4k
views
Select all data from IBM DB2 member table
Old as400 system has this thing called 'Members'. Basically you can have multiple members on a table and then work with only selected one.
More Info
When you select from a table that has members you ...
- 141
4
votes
2
answers
451
views
IBM i Access ODBC driver returning only first letter of column names
I'm new to DB2 and am connecting to a DB2 for i (V7R1) database using unixODBC and the IBM i Access ODBC driver for Linux.
When I query the database, the results only include the first letter of the ...
- 83
0
votes
1
answer
122
views
Enumerating short versus long column names from INFORMATION_SCHEMA views on DB2 for IBM i
On the IBM i (as/400) you can have short and long column names on a table. The short names are listed in the SYSTEM_COLUMN_NAME field of QSYS2.SYSCOLUMNS and the ODBC/JDBC SQLCOLUMNS view. Is it ...
- 2,667