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
1
vote
1answer
22 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
31 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
33 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
116 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
72 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
87 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
19 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 ...
0
votes
2answers
284 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
96 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
63 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
32 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
103 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
74 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
217 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(...
1
vote
2answers
2k 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
553 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
529 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
108 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
100 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
374 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
52 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 ...