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.
25
questions
0
votes
1answer
25 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? ...
0
votes
2answers
31 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 "...
0
votes
1answer
59 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=...
1
vote
1answer
27 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
3answers
34 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 ...
0
votes
1answer
54 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 * ...
0
votes
1answer
184 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
1answer
104 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 ...
5
votes
1answer
93 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:...
0
votes
1answer
24 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 ...
1
vote
2answers
529 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' ...
0
votes
2answers
155 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 ...
0
votes
1answer
102 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 => '...
0
votes
2answers
39 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 ...
0
votes
1answer
146 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, ...
1
vote
1answer
75 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 ...
1
vote
1answer
292 views
Error on Datastage when I try to write on AS400
I'm having troubles to operate an ETL process on DataStage.
I have the following error log:
#TABLE#,0: ODBC function "SQLExecute" reported: SQLSTATE = HY000: Native Error Code = -332: Msg = [IBM(...
2
votes
2answers
3k 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 ...
0
votes
1answer
699 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 ...
1
vote
1answer
658 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 ...
1
vote
0answers
123 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 ...
1
vote
1answer
107 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, ...
1
vote
2answers
3k 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 ...
4
votes
2answers
382 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 ...
0
votes
1answer
60 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 ...
