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.

Filter by
Sorted by
Tagged with
0 votes
1 answer
58 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 ...
Sarov's user avatar
  • 279
-1 votes
1 answer
41 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 ...
Sarov's user avatar
  • 279
0 votes
1 answer
85 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:...
Sarov's user avatar
  • 279
2 votes
2 answers
286 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 ...
Sarov's user avatar
  • 279
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 ...
Paul's user avatar
  • 1
0 votes
1 answer
79 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 ...
Sarov's user avatar
  • 279
0 votes
1 answer
67 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 ...
John Kuhns's user avatar
0 votes
2 answers
1k 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 ...
mrsorrted's user avatar
-1 votes
2 answers
880 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 ...
Jeffrey Hazeleger's user avatar
0 votes
1 answer
538 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? ...
Sarov's user avatar
  • 279
0 votes
2 answers
768 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 "...
Sarov's user avatar
  • 279
0 votes
1 answer
860 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=...
Cpt.Whale's user avatar
  • 155
1 vote
1 answer
104 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 ...
Maven BeJayjay's user avatar
0 votes
3 answers
617 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 ...
Sarov's user avatar
  • 279
0 votes
1 answer
656 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 * ...
Sarov's user avatar
  • 279
0 votes
1 answer
846 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 ...
Riccardo Francescato's user avatar
0 votes
1 answer
1k 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 ...
Sarov's user avatar
  • 279
5 votes
1 answer
495 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:...
Sarov's user avatar
  • 279
0 votes
1 answer
29 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 ...
Sarov's user avatar
  • 279
1 vote
2 answers
3k 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' ...
Sarov's user avatar
  • 279
0 votes
2 answers
628 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 ...
Sarov's user avatar
  • 279
0 votes
1 answer
374 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 => '...
Sarov's user avatar
  • 279
0 votes
2 answers
358 views

How to set column name when creating a view?

By default, when creating a view, for the name of each column, if the column is a direct selection from a table/physical file, then it will use the name of that table/physical file's column. If it's ...
Sarov's user avatar
  • 279
0 votes
1 answer
716 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, ...
Sarov's user avatar
  • 279
1 vote
1 answer
117 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 ...
Lennart - Slava Ukraini's user avatar
2 votes
2 answers
7k 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 ...
Ruslan's user avatar
  • 141
0 votes
1 answer
2k 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 ...
AndyD273's user avatar
  • 203
1 vote
1 answer
2k 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 ...
Test User's user avatar
1 vote
0 answers
362 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 ...
ChoCho's user avatar
  • 111
1 vote
1 answer
220 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, ...
Sarah Kemp's user avatar
1 vote
2 answers
5k 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 ...
Ruslan's user avatar
  • 141
4 votes
2 answers
466 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 ...
Liv Carman's user avatar
0 votes
1 answer
194 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 ...
Justin Dearing's user avatar